Formula Excel SUM e OFFSET

Se il foglio di lavoro di Excel include calcoli basati su un intervallo di celle modificabile, l'uso delle funzioni SUM e OFFSET insieme in una formula SUM OFFSET semplifica il compito di mantenere aggiornati i calcoli.

Creare un intervallo dinamico con le funzioni SUM e OFFSET

Se si utilizzano calcoli per un periodo di tempo che varia continuamente, ad esempio il totale delle vendite per il mese, la funzione OFFSET in Excel consente di impostare un intervallo dinamico che cambia quando vengono aggiunti i dati di vendita di ogni giorno.

Di per sé, la funzione SUM può generalmente ospitare l'inserimento di nuove celle di dati nell'intervallo che viene sommato.

Un'eccezione si verifica quando i dati vengono inseriti nella cella in cui si trova attualmente la funzione.

Nell'immagine di esempio sopra, le nuove cifre di vendita per ogni giorno vengono aggiunte in fondo all'elenco, costringendo il totale a spostarsi continuamente verso il basso di una cella ogni volta quando vengono aggiunti i nuovi dati.

Se la funzione SUM fosse utilizzata da sola per sommare i dati, sarebbe necessario modificare l'intervallo di celle utilizzato come argomento della funzione ogni volta che venivano aggiunti nuovi dati.

Usando insieme le funzioni SUM e OFFSET, tuttavia, l'intervallo che viene sommato diventa dinamico. In altre parole, cambia per accogliere nuove celle di dati. L'aggiunta di nuove celle di dati non causa problemi poiché l'intervallo continua a essere modificato man mano che ogni nuova cella viene aggiunta.

Sintassi e argomenti

Fare riferimento all'immagine qui sopra per seguire insieme a questo tutorial.

In questa formula, la funzione SOMMA viene utilizzata per sommare l'intervallo di dati forniti come argomento. Il punto di partenza per questo intervallo è statico ed è identificato come il riferimento di cella al primo numero da sommare per la formula.

La funzione OFFSET è nidificata all'interno della funzione SUM e viene utilizzata per creare un endpoint dinamico nell'intervallo di dati sommati 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 gamma: OFFSET (riferimento, righe, colonne))

• Inizio gamma: È necessario il punto di partenza per l'intervallo di celle che verrà sommato dalla funzione SUM. Nell'immagine di esempio, questa è la cella B2.
• Riferimento: Il riferimento di cella richiesto utilizzato per il calcolo dell'endpoint dell'intervallo si trova a molte righe e colonne. Nell'immagine di esempio, l'argomento Riferimento è il riferimento di cella per la formula stessa poiché si desidera sempre che l'intervallo termini una cella al di sopra della formula.
• Righe: È richiesto il numero di righe sopra o sotto l'argomento di riferimento utilizzato nel calcolo dell'offset. Questo valore può essere positivo, negativo o impostato su zero. Se la posizione dell'offset è al di sopra del Riferimento argomento, il valore è negativo. Se è sotto, l'argomento Rows è positivo. Se l'offset si trova nella stessa riga, l'argomento è zero. In questo esempio, l'offset inizia una riga sopra l'argomento Reference, quindi il valore per questo argomento è uno negativo (-1).
• Cols: È richiesto il numero di colonne a sinistra oa destra dell'argomento Riferimento utilizzato nel calcolo dell'offset. Questo valore può essere positivo, negativo o impostato su zero. Se la posizione dell'offset è a sinistra dell'argomento di riferimento, questo valore è negativo. Se a destra, l'argomento Cols è positivo. In questo esempio, i dati che vengono sommati si trovano nella stessa colonna della formula, quindi il valore per questo argomento è zero.

Utilizzo della formula SUM OFFSET sui dati di vendita totali

Questo esempio utilizza una formula SUM OFFSET per restituire il totale per le cifre di vendita giornaliere elencate nella colonna B del foglio di lavoro.

Inizialmente, la formula è stata inserita nella cella B6 e ha totalizzato i dati di vendita per quattro giorni.

Il prossimo passo è 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 in basso fino alla riga 7.

Come risultato dello spostamento, Excel aggiorna automaticamente l'argomento Riferimento su cellaB7 e aggiunge cellaB6 all'intervallo sommato dalla formula.

Inserimento della formula SUM OFFSET

1. Fare clic sulla cella B6, che è la posizione in cui verranno inizialmente visualizzati i risultati della formula.
2. Clicca sulformule scheda del menu della barra multifunzione.
3. ScegliereMath e Trigdalla barra multifunzione per aprire l'elenco a discesa della funzione.
4. Clicca suSOMMA nell'elenco per visualizzare la finestra di dialogo della funzione.
5. Nella finestra di dialogo, fare clic suNumero 1linea.
6. Clicca sulla cella B2 per inserire questo riferimento di cella nella finestra di dialogo. Questa posizione è l'endpoint statico per la formula.
7. Nella finestra di dialogo, fare clic suNumero 2linea.
8. Immettere la seguente funzione OFFSET: OFFSET (B6, -1,0) per formare l'endpoint dinamico per la formula.
9. Clic ok per completare la funzione e chiudere la finestra di dialogo.

Il totale appare nella cella B7.

Aggiunta dei dati di vendita del giorno successivo

Per aggiungere i dati di vendita del giorno successivo:

1. Fare clic con il tasto destro del mouse sull'intestazione della riga per la riga 6 per aprire il menu di scelta rapida.
2. Nel menu, fare clic suInserire inserire una nuova riga nel foglio di lavoro.
3. Di conseguenza, la formula SUM OFFSET si sposta sulla cella B7 e la riga 6 è vuota.
4. Clicca sulla cella A6.
5. Inserisci il numero5 per indicare che è stato inserito il totale delle vendite per il quinto giorno.
6. Clicca sulla cella B6.
7. Digita il numero$1458.25 e premere ilaccedere tasto sulla tastiera.

Aggiornamenti della cella B7 al nuovo totale di$7137.40.

Quando fai clic sulla cella B7, la formula aggiornata= SUM (B2: offset (B7, -1,0)) appare nella barra della formula.

La funzione OFFSET ha due argomenti opzionali: Altezza e Larghezza , che non sono stati utilizzati in questo esempio.

Questi argomenti possono essere usati per dire alla funzione OFFSET la forma dell'output in termini di così tante righe alte e così tante colonne di larghezza.

Omettendo questi argomenti, la funzione, per impostazione predefinita, utilizza invece l'altezza e la larghezza dell'argomento Riferimento, che in questo esempio è una riga alta e una colonna di larghezza.