=SCARTO(A1;3;2) restituisce la cella 3 righe più in basso e 2 colonne più a destra di A1, cioè C4. Dagli anche un'altezza e una larghezza e restituisce un intero intervallo, ed è soprattutto per questo che si usa SCARTO: totali e medie su un intervallo che si sposta o cresce. SCARTO si chiama OFFSET in Excel inglese, ed è così che la mostra la tabella; puoi scrivere le formule anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=SCARTO(A1;E2;F2)3 righe in basso e 2 a destra da A1 si arriva a C4, il prezzo di Carrot, $0.80. Imposta Cols a 0 per il nome Carrot, oppure Rows a 5 per la riga di Milk. Righe e colonne possono essere negative per salire o tornare indietro, e uno spostamento oltre il bordo superiore o laterale del foglio dà #RIF! (in inglese #REF!).
Sintassi di SCARTO
=OFFSET(reference, rows, cols, [height], [width])
reference(rif): la cella (o l'intervallo) di partenza.rows(righe),cols(colonne): di quanto spostarsi. 0 significa restare.height(altezza),width(largh): la dimensione dell'intervallo da restituire, contata dalla cella di arrivo. Se omesse, sono la dimensione direference.
Da sola in una cella, una SCARTO che restituisce più celle si espande in Excel 365; le versioni precedenti di solito mostrano #VALORE! (in inglese #VALUE!). Dentro SOMMA, MEDIA, CONTA.NUMERI o MAX funziona come un intervallo.
Sommare le ultime N righe
Il lavoro classico di SCARTO: un totale che copre sempre le righe più recenti, per quante ne siano state aggiunte. CONTA.NUMERI trova quanti valori ci sono, SCARTO scende fino al primo degli ultimi N, e l'altezza prende N righe.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SOMMA(SCARTO(B1;CONTA.NUMERI(B2:B13)-E2+1;0;E2;1))Ci sono 7 valori, quindi SCARTO parte 7-3+1, 5 righe sotto B1, da B6, e prende 3 righe: da May a Jul, 14,900. In Excel italiano la formula è =SOMMA(SCARTO(B1;CONTA.NUMERI(B2:B13)-E2+1;0;E2;1)). Scrivi 4900 in B9 (agosto) e il totale si sposta su Jun, Jul e Aug, perché ora CONTA.NUMERI ne trova 8. L'intervallo B2:B13 lascia spazio per il resto dell'anno. La colonna non deve avere celle vuote in mezzo, altrimenti CONTA.NUMERI conta meno valori e la finestra finisce nel posto sbagliato.
Una media mobile
Copiata lungo una colonna, SCARTO con uno spostamento di riga negativo dà a ogni riga una finestra sulle righe sopra di essa: qui la media del mese corrente e dei due precedenti.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=MEDIA(SCARTO(B4;-2;0;3;1))C4 fa la media di B2:B4 (da Jan a Mar), 4,300. Ogni riga sotto sposta la finestra di una riga in basso. Cambia il 3 in 6 e il -2 in -5 per una media a sei mesi (facendo partire la formula dalla riga 7). Questo caso particolare non ha affatto bisogno di SCARTO: =MEDIA(B2:B4) copiata verso il basso da C4 fa lo stesso, perché i riferimenti relativi si spostano già. SCARTO si guadagna il suo posto quando la dimensione della finestra viene da una cella.
Perché INDICE è spesso la scelta migliore
SCARTO è volatile: Excel ricalcola ogni SCARTO dopo qualsiasi modifica in qualsiasi punto della cartella di lavoro, perché non può sapere in anticipo a quali celle punterà. Un foglio con migliaia di queste formule diventa lento. Anche INDICE restituisce un riferimento, e un intervallo scritto come inizio:INDICE(...) cresce allo stesso modo senza essere volatile:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
In Excel italiano: =SOMMA(SCARTO(B2;0;0;E2;1)) e =SOMMA(B2:INDICE(B2:B13;E2)). Entrambe leggono le prime E2 righe della colonna. SCARTO è anche più difficile da controllare: Individua precedenti e i contorni colorati che Excel disegna mentre modifichi la formula mostrano la cella di partenza e gli argomenti, non l'intervallo che SCARTO finisce per restituire. Usa SCARTO per un modello veloce o per l'intervallo di un grafico; nelle cartelle di lavoro grandi preferisci INDICE. INDICE spiega meglio come restituire intervalli, e INDIRETTO è l'altra funzione di riferimento volatile.
Esercizio: il totale dei primi N mesi
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Tocca a te: In F2, usa SCARTO dentro SOMMA per sommare i primi N mesi, dove N è in E2.
Domande frequenti
Cosa fa SCARTO in Excel?
Restituisce un riferimento che si trova a un certo numero di righe e colonne da una cella di partenza, eventualmente ridimensionato. =SCARTO(A1;3;2) è la cella 3 righe più in basso e 2 colonne più a destra di A1, cioè C4.
Come si sommano le ultime N righe in Excel?
Parti dall'intestazione e scendi fino al primo degli ultimi N valori: =SOMMA(SCARTO(B1;CONTA.NUMERI(B2:B100)-N+1;0;N;1)). CONTA.NUMERI trova quanti valori ci sono, e l'altezza N prende altrettante righe. Funziona solo quando la colonna non ha buchi.
Perché SCARTO è volatile?
Excel ricalcola ogni SCARTO dopo qualsiasi modifica nella cartella di lavoro, perché le celle a cui punta si conoscono solo dopo che è stata calcolata. Nelle cartelle di lavoro grandi questo rallenta tutto. Un intervallo costruito con INDICE, come B2:INDICE(B2:B100;N), fa lo stesso lavoro senza essere volatile.
Quali sono gli argomenti di SCARTO?
SCARTO(rif; righe; colonne; [altezza]; [largh]): la cella di partenza, di quante righe scendere (negativo è verso l'alto), di quante colonne spostarsi (negativo torna indietro), e facoltativamente la dimensione dell'intervallo da restituire.
