Excel OFFSET Funzione

L'Excel Funzione OFFSET restituisce un riferimento a una cella o un intervallo di celle sfalsato rispetto a una cella specifica di un determinato numero di righe e colonne.

Sintassi

=OFFSET (reference, rows, cols, [height], [width])

argomenti

Valore di ritorno

La funzione OFFSET restituirà un offset di riferimento di cella da un dato punto di partenza.

Note sulle funzioni

1. #VALORE! il valore di errore verrà restituito quando il riferimento fa riferimento a un intervallo di celle discontinue.
2. #REF! il valore di errore verrà restituito quando le righe e le colonne sfalsano il riferimento sul bordo del foglio di lavoro.

Esempi

Esempio 1: utilizzo di base per la funzione OFFSET

Restituisce un riferimento a una cella con la formula seguente:
=OFFSET(B2,3,1)

In questo caso, B2 è il punto di partenza, i numeri 3 e 1 indicano che per spostare 3 righe in basso e 1 colonna a destra dalla cella B2, e restituire infine il valore in C5. Vedi screenshot:

Restituisce un riferimento a un intervallo di celle con la formula seguente:
=OFFSET(B2,3,1,2,2)

In questo caso, otterrai i risultati dell'intervallo 2 x 2 che si trova 3 righe sotto e 1 colonna a destra della cella B2.

Note:: #VALUE! Si verificherà un errore quando selezioni solo una cella per applicare la funzione OFFSET per restituire un intervallo di celle. È necessario selezionare un intervallo 2 x 2 (dice 4 celle vuote), inserire la formula e premere il tasto Ctrl + Shift + entrare tasti per ottenere i risultati.

Esempio 2: utilizzare la funzione OFFSET per sommare un intervallo di valori

Come accennato in precedenza, se provi a utilizzare la funzione OFFSET come =OFFSET(B2,3,1,2,2) da solo in una singola cella, restituirà un #VALORE! Errore. Tuttavia, se si combinano le funzioni SUM e OFFSET come mostrato nell'immagine sottostante, restituirà direttamente la somma dei valori nell'intervallo C5: D6.

1. Seleziona una cella vuota, copia la formula sottostante e premi il tasto entrare chiave per ottenere il risultato.
=SUM(OFFSET(B2,3,1,2,2)))

Esempio 3: somma delle colonne in base a determinati criteri

Come mostrato nell'immagine sottostante, come ottenere le vendite totali di Mango da Sun a Tue? Si prega di provare come di seguito.

1. Seleziona una cella vuota, copia la formula sottostante e premi il tasto entrare chiave per ottenere il risultato.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Note:

1. Nella formula sopra, MATCH(G4,B3:B8) sta cercando Mango e sta tornando alla sua posizione nel raggio d'azione B3: B8. Vediamo, Mango individua 5 righe sotto la cella B2, quindi restituisce il numero 5;
2. Poiché il risultato MATCH è 5 e l'ultima virgola qui rappresenta 0, la funzione OFFSET ora viene visualizzata come OFFSET(C2:E2,5,0), il che significa che l'offset parte dall'intervallo C2: E2, sposta 5 righe in basso e 0 colonna a destra per ottenere il riferimento all'intervallo C7: E7;
3. Quindi la funzione SOMMA viene visualizzata come SUM(C7:E7)e infine restituisce la somma dei valori nell'intervallo C7: E7.

Altri esempi

Come sommare ogni n righe in Excel?

Come creare un intervallo denominato dinamico in Excel?

Come restituire il valore della cella ogni cinque o ennesima riga in Excel?