Come copiare formule con riempimento automatico in Excel 2016

Se hai solo bisogno di copiare una singola formula in Excel 2016, usa la funzione di riempimento automatico o i comandi Copia e Incolla. Questo tipo di copia della formula, sebbene abbastanza comune, non può essere eseguito con il trascinamento della selezione.

Non dimenticare l'opzione Totali nello strumento Analisi rapida. Puoi usarlo per creare una riga o una colonna di totali sul bordo inferiore o destro di una tabella di dati in un lampo. Seleziona semplicemente la tabella come intervallo di celle e fai clic sul pulsante Analisi rapida seguito da Totali nella sua tavolozza. Quindi fare clic sul pulsante Somma all'inizio della tavolozza per creare formule che totalizzano le colonne in una nuova riga in fondo alla tabella e/o sul pulsante Somma alla fine della tavolozza per creare formule che totalizzano le righe in una nuova colonna all'estremità destra.

Ecco come puoi utilizzare il riempimento automatico per copiare una formula in un intervallo di celle. In questa figura, puoi vedere il foglio di lavoro Mother Goose Enterprises - 2016 Sales con tutte le società, ma questa volta con un solo totale mensile nella riga 12, che è in procinto di essere copiato tramite la cella E12.

Copiare una formula in un intervallo di celle con il riempimento automatico.

La figura seguente mostra il foglio di lavoro dopo aver trascinato il quadratino di riempimento nella cella B12 per selezionare l'intervallo di celle C12: E12 (dove deve essere copiata questa formula).

Il foglio di lavoro dopo aver copiato la formula che totalizza le vendite mensili (e trimestrali).

Relativamente parlando

La figura mostra il foglio di lavoro dopo che la formula in una cella è stata copiata nell'intervallo di celle C12: E12 e la cella B12 è attiva. Nota come Excel gestisce la copia delle formule. La formula originale nella cella B12 è la seguente:

=SOMMA(B3:B11)

Quando la formula originale viene copiata nella cella C12, Excel modifica leggermente la formula in modo che assomigli a questa:

=SOMMA(C3:C11)

Excel regola il riferimento di colonna, cambiandolo da B a C, perché hai copiato da sinistra a destra attraverso le righe.

Quando si copia una formula in un intervallo di celle che si estende lungo le righe, Excel regola i numeri di riga nelle formule copiate anziché le lettere di colonna per adattarsi alla posizione di ogni copia. Ad esempio, la cella E3 nel foglio di lavoro Mother Goose Enterprises - 2016 Sales contiene la seguente formula:

=SOMMA(B3:D3)

Quando copi questa formula nella cella E4, Excel modifica la copia della formula come segue:

=SOMMA(B4:D4)

Excel regola il riferimento di riga per mantenerlo aggiornato con la nuova posizione della riga 4. Poiché Excel regola i riferimenti di cella nelle copie di una formula rispetto alla direzione della copia, i riferimenti di cella sono noti come riferimenti di cella relativi .

Alcune cose sono assolute!

Tutte le nuove formule che crei contengono naturalmente riferimenti di cella relativi, a meno che tu non dica diversamente. Poiché la maggior parte delle copie che fai delle formule richiedono aggiustamenti dei loro riferimenti di cella, raramente devi ripensare a questa disposizione. Quindi, di tanto in tanto, ti imbatti in un'eccezione che richiede di limitare quando e come i riferimenti di cella vengono regolati nelle copie.

Una delle eccezioni più comuni è quando si desidera confrontare un intervallo di valori diversi con un singolo valore. Ciò accade più spesso quando si desidera calcolare la percentuale di ciascuna parte rispetto al totale. Ad esempio, nel foglio di lavoro Mother Goose Enterprises - 2016 Sales, si verifica questa situazione durante la creazione e la copia di una formula che calcola quale percentuale ogni totale mensile (nell'intervallo di celle B14: D14) è del totale trimestrale nella cella E12.

Supponiamo di voler inserire queste formule nella riga 14 del foglio di lavoro Mother Goose Enterprises - 2016 Sales, a partire dalla cella B14. La formula nella cella B14 per calcolare la percentuale delle vendite di gennaio sul totale del primo trimestre è molto semplice:

=B12/MI12

Questa formula divide il totale delle vendite di gennaio nella cella B12 per il totale trimestrale in E12 (cosa potrebbe esserci di più facile?). Guarda, tuttavia, cosa accadrebbe se trascinassi il quadratino di riempimento di una cella a destra per copiare questa formula nella cella C14:

=DO12/FA12

La regolazione del primo riferimento di cella da B12 a C12 è proprio ciò che il medico ha ordinato. Tuttavia, la regolazione del secondo riferimento di cella da E12 a F12 è un disastro. Non solo non calcoli la percentuale delle vendite di febbraio nella cella C12 rispetto alle vendite del primo trimestre in E12, ma ti ritrovi anche con uno di quegli orribili #DIV/0! cose di errore nella cella C14.

Per impedire a Excel di modificare un riferimento di cella in una formula in qualsiasi copia effettuata, convertire il riferimento di cella da relativo ad assoluto. Lo fai premendo il tasto funzione F4, dopo aver messo Excel in modalità Modifica (F2). Excel indica che si rende assoluto il riferimento di cella posizionando i simboli del dollaro davanti alla lettera della colonna e al numero di riga. Ad esempio, in questa figura, la cella B14 contiene la formula corretta da copiare nell'intervallo di celle C14: D14:

Copia della formula per il calcolo del rapporto tra le vendite mensili e trimestrali con un riferimento di cella assoluto.

=B12/$E$12

Guarda il foglio di lavoro dopo che questa formula è stata copiata nell'intervallo C14: D14 con il quadratino di riempimento e la cella C14 è selezionata (vedi la figura seguente). Notare che la barra della formula mostra che questa cella contiene la seguente formula:

Il foglio di lavoro dopo aver copiato la formula con il riferimento di cella assoluto.

=C12/$E$12

Poiché E12 è stato modificato in $E$12 nella formula originale, tutte le copie hanno lo stesso riferimento assoluto (non modificabile).

Se sbagli e copi una formula in cui uno o più riferimenti di cella avrebbero dovuto essere assoluti ma li hai lasciati tutti relativi, modifica la formula originale come segue:

Fare doppio clic sulla cella con la formula o premere F2 per modificarla.

Posiziona il punto di inserimento da qualche parte sul riferimento che desideri convertire in assoluto.

Premi F4.

Al termine della modifica, fai clic sul pulsante Invio sulla barra della formula e quindi copia la formula nell'intervallo di celle incasinato con la maniglia di riempimento.

Assicurati di premere F4 solo una volta per modificare un riferimento di cella in completamente assoluto come descritto in precedenza. Se si preme una seconda volta il tasto funzione F4, si ottiene un cosiddetto riferimento misto, in cui solo la parte riga è assoluta e la parte colonna è relativa (come in E$12). Se quindi premi di nuovo F4, Excel esce con un altro tipo di riferimento misto, in cui la parte della colonna è assoluta e la parte della riga è relativa (come in $ E12). Se vai avanti e premi ancora F4, Excel cambia il riferimento di cella in completamente relativo (come in E12).

Dopo essere tornato al punto di partenza, puoi continuare a utilizzare F4 per scorrere di nuovo lo stesso set di modifiche di riferimento di cella.

Se stai utilizzando Excel 2016 su un dispositivo senza accesso a una tastiera fisica con tasti funzione (come un tablet touchscreen), l'unico modo per convertire gli indirizzi di cella nelle formule da relativo a assoluto o qualche forma di indirizzo misto è aprire la tastiera Touch e usala aggiungere i segni del dollaro prima della lettera della colonna e/o del numero di riga nell'indirizzo di cella appropriato sulla barra della formula.