Se il foglio di lavoro di Excel include calcoli basati su un intervallo di celle variabile, utilizzare le funzioni SOMMA e OFFSET insieme in una formula SOMMA OFFSET per semplificare il compito di mantenere aggiornati i calcoli.
Le istruzioni in questo articolo si applicano a Excel per Microsoft 365, Excel 2019, Excel 2016, Excel 2013 ed Excel 2010.
Crea una gamma dinamica con le funzioni SUM e OFFSET
Se utilizzi i calcoli per un periodo di tempo che cambia continuamente, come la determinazione delle vendite per il mese, usa la funzione OFFSET in Excel per impostare un intervallo dinamico che cambia man mano che vengono aggiunti i dati delle vendite di ogni giorno.
Di per sé, la funzione SOMMA può solitamente ospitare l'inserimento di nuove celle di dati nell'intervallo da sommare. Un'eccezione si verifica quando i dati vengono inseriti nella cella in cui si trova attualmente la funzione.
Nell'esempio seguente, i nuovi dati sulle vendite per ogni giorno vengono aggiunti in fondo all'elenco, costringendo il totale a spostarsi continuamente verso il basso di una cella ogni volta che vengono aggiunti i nuovi dati.
Per seguire questo tutorial, apri un foglio di lavoro Excel vuoto e inserisci i dati di esempio. Non è necessario formattare il foglio di lavoro come nell'esempio, ma assicurati di inserire i dati nelle stesse celle.
Se viene utilizzata solo la funzione SOMMA per sommare i dati, l'intervallo di celle utilizzato come argomento della funzione dovrebbe essere modificato ogni volta che vengono aggiunti nuovi dati.
Utilizzando insieme le funzioni SUM e OFFSET, l'intervallo che viene sommato diventa dinamico e cambia per accogliere nuove celle di dati. L'aggiunta di nuove celle di dati non causa problemi perché l'intervallo continua a modificarsi man mano che viene aggiunta ogni nuova cella.
Sintassi e argomenti
In questa formula, la funzione SOMMA viene utilizzata per sommare l'intervallo di dati fornito come argomento. Il punto iniziale di questo intervallo è statico ed è identificato come il riferimento di cella al primo numero da sommare con la formula.
La funzione OFFSET è nidificata all'interno della funzione SUM e crea un punto finale dinamico per l'intervallo di dati sommato dalla formula. Ciò si ottiene impostando il punto finale dell'intervallo su una cella sopra la posizione della formula.
La sintassi della formula è:
=SUM(Inizio intervallo:OFFSET(Riferimento, righe, colonne))
Gli argomenti sono:
- Inizio intervallo: il punto iniziale per l'intervallo di celle che verrà sommato dalla funzione SOMMA. In questo esempio, il punto di partenza è la cella B2.
- Reference: il riferimento di cella richiesto utilizzato per calcolare l'endpoint dell'intervallo. Nell'esempio, l'argomento Riferimento è il riferimento di cella per la formula perché l'intervallo termina una cella sopra la formula.
- Righe: È richiesto il numero di righe sopra o sotto l'argomento Riferimento utilizzato per calcolare l'offset. Questo valore può essere positivo, negativo o impostato su zero. Se la posizione dell'offset è al di sopra dell'argomento Riferimento, il valore è negativo. Se l'offset è inferiore, l'argomento Righe è positivo. Se l'offset si trova nella stessa riga, l'argomento è zero. In questo esempio, l'offset inizia una riga sopra l'argomento Riferimento, quindi il valore per l'argomento è negativo (-1).
- Cols: Il numero di colonne a sinistra oa destra dell'argomento Riferimento utilizzato per calcolare l'offset. Questo valore può essere positivo, negativo o impostato su zero. Se la posizione dell'offset è a sinistra dell'argomento Riferimento, questo valore è negativo. Se l'offset è a destra, l'argomento Cols è positivo. In questo esempio, i dati da sommare si trovano nella stessa colonna della formula, quindi il valore per questo argomento è zero.
Utilizza la formula SUM OFFSET per i dati di vendita totali
Questo esempio utilizza una formula SUM OFFSET per restituire il totale delle cifre delle vendite giornaliere elencate nella colonna B del foglio di lavoro. Inizialmente, la formula è stata inserita nella cella B6 e sommato i dati di vendita per quattro giorni.
Il passaggio successivo consiste nello spostare la formula SUM OFFSET in basso di una riga per fare spazio al totale delle vendite del quinto giorno. Ciò si ottiene inserendo una nuova riga 6, che sposta la formula alla riga 7.
Come risultato dello spostamento, Excel aggiorna automaticamente l'argomento Riferimento nella cella B7 e aggiunge la cella B6 all'intervallo sommato dalla formula.
- Seleziona la cella B6, che è la posizione in cui verranno inizialmente visualizzati i risultati della formula.
-
Seleziona la scheda Formule della barra multifunzione.
Image -
Scegli Matematica e Trig.
Image -
Seleziona SOMMA.
Image -
Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo Numero1.
-
Nel foglio di lavoro, seleziona la cella B2 per inserire questo riferimento di cella nella finestra di dialogo. Questa posizione è l'endpoint statico per la formula.
Image - Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo Numero2.
-
Inserisci OFFSET(B6, -1, 0). Questa funzione OFFSET costituisce l'endpoint dinamico per la formula.
Image -
Seleziona OK per completare la funzione e chiudere la finestra di dialogo. Il totale appare nella cella B6.
Image
Aggiungi i dati di vendita del giorno successivo
Per aggiungere i dati sulle vendite del giorno successivo:
- Fai clic con il pulsante destro del mouse sull'intestazione della riga per la riga 6.
-
Seleziona Inserisci per inserire una nuova riga nel foglio di lavoro. La formula SUM OFFSET si sposta in basso di una riga fino alla cella B7 e la riga 6 è ora vuota.
Image - Seleziona la cella A6 e inserisci il numero 5 per indicare che si sta inserendo il totale delle vendite per il quinto giorno.
-
Seleziona la cella B6, inserisci $1458.25, quindi premi Invio.
Image - Cell B7 si aggiorna al nuovo totale di $7137,40.
Quando selezioni la cella B7, la formula aggiornata appare nella barra della formula.
=SOMMA(B2:OFFSET(B7, -1, 0))
La funzione OFFSET ha due argomenti opzionali: Altezza e Larghezza, che non sono stati utilizzati in questo esempio. Questi argomenti indicano alla funzione OFFSET la forma dell'output in termini di numero di righe e colonne.
Omettendo questi argomenti, la funzione utilizza invece l' altezza e la larghezza dell'argomento Riferimento, che, in questo esempio, è alto una riga e largo una colonna.
