Funzione OFFSET di Excel (Esempio + Video)
Quando utilizzare la funzione OFFSET di Excel
La funzione OFFSET di Excel può essere utilizzata quando si desidera ottenere un riferimento che sfalsa il numero specificato di righe e colonne dal punto di partenza.
Cosa restituisce
Restituisce il riferimento a cui punta la funzione OFFSET.
Sintassi
=OFFSET(riferimento, righe, colonne, [altezza], [larghezza])
Argomenti di input
- riferimento - Il riferimento da cui si desidera eseguire l'offset. Potrebbe trattarsi di un riferimento di cella o di un intervallo di celle adiacenti.
- righe - il numero di righe da compensare. Se usi un numero positivo, viene spostato sulle righe sottostanti, e se viene utilizzato un numero negativo, viene spostato sulle righe sopra.
- col - il numero di colonne da compensare. Se usi un numero positivo, viene spostato sulle colonne a destra e se viene utilizzato un numero negativo, viene spostato sulle colonne a sinistra.
- [altezza] - questo è un numero che rappresenta il numero di righe nel riferimento restituito.
- [larghezza] - questo è un numero che rappresenta il numero di colonne nel riferimento restituito.
Comprensione delle basi della funzione OFFSET di Excel
La funzione OFFSET di Excel è forse una delle funzioni più confuse in Excel.
Prendiamo un semplice esempio di una partita a scacchi. C'è un pezzo negli scacchi chiamato Rook (noto anche come torre, marchese o rettore).
[Immagine per gentile concessione: Wikipedia meravigliosa]
Ora, come tutti gli altri pezzi degli scacchi, una Torre ha un percorso fisso su cui può muoversi sulla scacchiera. Può andare dritto (a destra/sinistra o su/giù sul tabellone), ma non può andare in diagonale.
Ad esempio, supponiamo di avere una scacchiera in Excel (come mostrato sotto), in una data casella (D5 in questo caso), la Torre può andare solo nelle quattro direzioni evidenziate.
Ora, se ti chiedo di portare la Torre dalla sua posizione attuale a quella evidenziata in giallo, cosa dirai alla Torre?
Gli chiederete di fare due passi in basso e due a destra… non è vero?
E questa è una stretta approssimazione di come funziona la funzione OFFSET.
Ora vediamo cosa significa in Excel. Voglio iniziare con la cella D5 (che è dove si trova la Torre) e poi andare due righe in basso e due colonne a destra e recuperare il valore dalla cella lì.
La formula sarebbe:
=OFFSET(da dove cominciare, quante righe in basso, quante colonne a destra)
Come puoi vedere, la formula nell'esempio sopra nella cella J1 è = OFFSET (D5,2,2).
È iniziato da D5, quindi è sceso di due righe e due colonne a destra e ha raggiunto la cella F7. Ha quindi restituito il valore nella cella F7.
Nell'esempio sopra, abbiamo esaminato la funzione OFFSET con 3 argomenti. Ma ci sono altri due argomenti facoltativi che possono essere usati.
Diamo un'occhiata a un semplice esempio qui:
Supponiamo di voler utilizzare il riferimento alla cella A1 (in giallo) e di voler fare riferimento all'intero intervallo evidenziato in blu (C2: E4) in una formula.
Come lo faresti usando una tastiera? Prima andresti alla cella C2, quindi selezioneresti tutte le celle in C2: E4.
Ora vediamo come farlo utilizzando la formula OFFSET:
=OFFSET(A1,1,2,3,3)
Se usi questa formula in una cella, restituirà un #VALORE! errore, ma se entri nella modalità di modifica e selezioni la formula e premi F9, vedrai che restituisce tutti i valori evidenziati in blu.
Ora vediamo come funziona questa formula:
=OFFSET(A1,1,2,3,3)
- Il primo argomento è la cella in cui dovrebbe iniziare.
- Il secondo argomento è 1, che indica a Excel di restituire un riferimento che è stato OFFSET di 1 riga.
- Il terzo argomento è 2, che indica a Excel di restituire un riferimento che è stato OFFSET di 2 colonne.
- Il quarto argomento è 3. Questo specifica che il riferimento deve coprire 3 righe. Questo è chiamato l'argomento altezza.
- Il quinto argomento è 3. Questo specifica che il riferimento deve coprire 3 colonne. Questo è chiamato l'argomento della larghezza.
Ora che hai il riferimento a un intervallo di celle (C2: E4), puoi usarlo all'interno di altre funzioni (come SUM, COUNT, MAX, AVERAGE).
Si spera che tu abbia una buona conoscenza di base dell'uso della funzione OFFSET di Excel. Diamo ora uno sguardo ad alcuni esempi pratici di utilizzo della funzione OFFSET.
Funzione OFFSET di Excel - Esempi
Ecco due esempi di utilizzo della funzione OFFSET di Excel.
Esempio 1 - Trovare l'ultima cella riempita nella colonna
Supponiamo di avere alcuni dati in una colonna come mostrato di seguito:
Per trovare l'ultima cella della colonna, usa la seguente formula:
=OFFSET(A1,CONTEGGIO(A:A)-1,0)
Questa formula presuppone che non ci siano valori oltre a quelli mostrati e che queste celle non contengano una cella vuota. Funziona contando il numero totale di celle che vengono riempite e compensa di conseguenza la cella A1.
Ad esempio, in questo caso, ci sono 8 valori, quindi COUNT(A:A) restituisce 8. Offset della cella A1 di 7 per ottenere l'ultimo valore.
Esempio 2 - Creazione di un menu a discesa dinamico
È possibile estendere i concetti illustrati nell'Esempio 1 per creare intervalli denominati dinamici. Questi potrebbero essere utili se si utilizzano gli intervalli denominati nelle formule o nella creazione di menu a discesa ed è probabile che si aggiungano/eliminino dati dal riferimento che crea l'intervallo denominato.
Ecco un esempio:
Si noti che il menu a discesa si regola automaticamente quando l'anno viene aggiunto o rimosso.
Ciò accade poiché la formula utilizzata per creare il menu a discesa è dinamica e identifica qualsiasi aggiunta o eliminazione e regola l'intervallo di conseguenza.
Ecco come farlo:
- Seleziona la cella in cui desideri inserire il menu a discesa.
- Vai su Dati -> Strumenti dati -> Convalida dati.
- Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco dal menu a discesa.
- Nella sorgente, inserisci la seguente formula: =OFFSET(A1,0,0,COUNT(A:A),1)
- Fare clic su OK.
Vediamo come funziona questa formula:
- I primi tre argomenti della funzione OFFSET sono A1, 0 e 0. Ciò significa essenzialmente che restituirebbe la stessa cella di riferimento (che è A1).
- Il quarto argomento è per l'altezza e qui la funzione COUNT restituisce il numero totale di elementi nell'elenco. Si presuppone che non ci siano spazi vuoti nell'elenco.
- Il quinto argomento è 1, che indica che la larghezza della colonna dovrebbe essere uno.
Note aggiuntive:
- OFFSET è una funzione volatile (usare con cautela).
- Viene ricalcolato ogni volta che la cartella di lavoro Excel è aperta o ogni volta che viene attivato un calcolo nel foglio di lavoro. Ciò potrebbe aumentare il tempo di elaborazione e rallentare la cartella di lavoro.
- Se il valore dell'altezza o della larghezza viene omesso, viene preso come quello del riferimento.
- Se righe e cols sono numeri negativi, la direzione dell'offset viene invertita.
Alternative alla funzione OFFSET di Excel
A causa di alcuni dei limiti della funzione OFFSET di Excel, molti vogliono prendere in considerazione alternative ad essa:
- Funzione INDICE: La funzione INDICE può essere utilizzata anche per restituire un riferimento di cella. Fare clic qui per vedere un esempio su come creare un intervallo denominato dinamico utilizzando la funzione INDICE.
- Tabella Excel: se si utilizzano riferimenti strutturati in Tabella Excel, non è necessario preoccuparsi dell'aggiunta di nuovi dati e della necessità di modificare le formule.
Funzione OFFSET di Excel - Esercitazione video
- Funzione CERCA.VERT di Excel.
- Funzione CERCA.ORIZZ di Excel.
- Funzione INDICE di Excel.
- Funzione INDIRETTA di Excel.
- Funzione CORRISPONDENZA di Excel.
