Scarica la cartella di lavoro di esempio
Questo tutorial mostra come usare il Funzione OFFSET di Excel in Excel per creare un offset di riferimento da una cella iniziale.
Panoramica della funzione OFFSET
La funzione OFFSET Inizia con un riferimento di cella definito e restituisce un riferimento di cella un numero specifico di righe e colonne offset dal riferimento originale. I riferimenti possono essere una cella o un intervallo di celle. Offset consente inoltre di ridimensionare il riferimento di un determinato numero di righe/colonne.
(Notare come appaiono gli input della formula)
Sintassi e input della funzione IFERROR:
| 1 | =OFFSET(riferimento,righe,colonne,altezza,larghezza) |
riferimento - Il riferimento di cella iniziale da cui si desidera eseguire l'offset.
righe - Il numero di righe da compensare.
cols - Il numero di colonne da compensare.
altezza - FACOLTATIVO: regolare il numero di righe nel riferimento.
larghezza - FACOLTATIVO: regolare il numero di colonne nel riferimento.
Che cos'è la funzione OFFSET?
La funzione OFFSET è una delle più potenti funzioni del foglio di calcolo in quanto può essere abbastanza versatile in ciò che crea. Offre all'utente la possibilità di definire una cella o un intervallo in una varietà di posizioni e dimensioni.
ATTENZIONE: La funzione OFFSET è una delle funzioni volatili. La maggior parte delle volte quando lavori nel tuo foglio di calcolo, il computer ricalcolerà una formula solo se gli input hanno cambiato i loro valori. Una funzione volatile, tuttavia, ricalcola ogni volta che apporti una modifica a qualsiasi cella. È necessario prestare attenzione per assicurarsi di non causare un tempo di ricalcolo elevato a causa dell'uso eccessivo della funzione volatile o della presenza di molte celle dipendenti dal risultato di una funzione volatile.
Esempi di righe di base
In ogni utilizzo della funzione OFFSET, è necessario fornire un punto di partenza, o ancora. Diamo un'occhiata a questa tabella per capire questo:
Useremo "Bob" nella cella B3 come punto di ancoraggio. Se volessimo prendere il valore appena sotto (Charlie), diremmo che vogliamo spostare la riga di 1. La nostra formula sarebbe simile
| 1 | =OFFSET(B3, 1) |
Se volessimo aumentare, sarebbe un cambiamento negativo. Puoi pensare a questo come il numero di riga sta diminuendo, quindi dobbiamo sottrarre. Quindi, per ottenere il valore sopra (Adam), scriveremmo
| 1 | =OFFSET(B2, -1) |
Esempi di colonne di base
Continuando l'idea dell'esempio precedente, aggiungeremo un'altra colonna alla nostra tabella.
Se volessimo prendere l'insegnante per Bob, potremmo usare la formula
| 1 | =OFFSET(B2, 0, 1) |
In questo caso, abbiamo detto che vogliamo compensare zero righe (ovvero rimanere sulla stessa riga) ma vogliamo compensare 1 colonna. Per le colonne, un numero positivo indica l'offset a destra e i numeri negativi indicano l'offset a sinistra.
OFFSET e MATCH
Supponiamo di avere diverse colonne di dati e di voler dare all'utente la possibilità di scegliere da quale colonna recuperare i risultati. Puoi usare la funzione INDEX, oppure puoi usare OFFSET. Poiché CONFRONTA restituirà la posizione relativa di un valore, dovremo assicurarci che il punto di ancoraggio sia a sinistra del nostro primo valore possibile. Considera il seguente layout:
In B2, scriveremo questa formula:
| 1 | =OFFSET(B2, 0, CONFRONTA(LA2, $C$1:$F$1, 0)) |
Il MATCH cercherà "Feb" nell'intervallo C1: F1 e lo troverà nel 2ns cellula. L'OFFSET si sposterà quindi di 1 colonna a destra di B2 e acquisirà il valore desiderato di 9. Notare che OFFSET non ha problemi a utilizzare la stessa cella che contiene la formula come punto di ancoraggio.
NOTA: questa tecnica può essere utilizzata in sostituzione di CERCA.VERT o CERCA.ORIZZ quando si desidera restituire un valore dalla sinistra/sopra l'intervallo di ricerca. Questo perché OFFSET può eseguire offset negativi.
OFFSET per ottenere un intervallo
Puoi usare il 4ns e 5ns argomenti nella funzione OFFSET per restituire un intervallo anziché solo una singola cella. Supponiamo di voler sommare 3 colonne in questa tabella.
| 1 | =MEDIA(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
In F2, abbiamo selezionato il nome di uno studente per il quale vogliamo recuperare i punteggi medi dei test. Per fare questo, useremo la formula
| 1 | =MEDIA(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
Il MATCH cercherà nella colonna A il nostro nome e restituirà la posizione relativa, che è 3 nel nostro esempio. Vediamo come questo verrà valutato. Innanzitutto, l'OFFSET sta per andare fuori uso 3 righe da A1 e 1 colonna a Giusto dall'A1. Questo ci colloca nella cella B3.
| 1 | =MEDIA(OFFSET(A1, 3, 1, 1, 3)) |
Successivamente, ridimensioneremo l'intervallo. Il nuovo intervallo avrà B3 come cella in alto a sinistra. Sarà alto 1 riga e alto 3 colonne, dandoci l'intervallo B4: D4.
| 1 | =MEDIA(OFFSET(A1,3, 1, 1, 3)) |
Nota che mentre puoi legittimamente inserire valori negativi negli argomenti di offset, puoi utilizzare solo valori non negativi negli argomenti di dimensionamento.
Alla fine, la nostra funzione MEDIA vede:
| 1 | =MEDIA(B4:D4) |
Quindi, otteniamo la nostra soluzione di 86.67
OFFSET con SOMMA dinamica
Poiché OFFSET viene utilizzato per trovare un riferimento, anziché puntare direttamente alla cella, è molto utile quando si tratta di dati a cui sono state aggiunte o eliminate righe. Considera la seguente tabella con un totale in basso
| 1 | =SOMMA(B2:B4) |
Se avessimo usato una formula SUM di base qui di "=SUM(B2:B4)" e quindi avessimo inserito una nuova riga per aggiungere un record per Bill, avremmo avuto la risposta sbagliata
Invece, pensiamo a come risolverlo dal punto di vista di Total. Vogliamo davvero prendere tutto dalla cella B2 alla cella appena sopra il nostro totale. Il modo in cui possiamo scriverlo in una formula è eseguire un offset di riga di -1. Quindi, usiamo questo come formula per il nostro totale nella cella B5:
| 1 | =SOMMA(B2:OFFSET(B5,-1,0)) |
Questa formula fa ciò che abbiamo appena descritto: inizia da B2 e vai a 1 cella sopra la nostra cella totale. Puoi vedere come dopo aver aggiunto i dati di Bill, il nostro totale viene aggiornato correttamente.
OFFSET per ottenere gli ultimi N elementi
Supponiamo che tu stia registrando le vendite mensili ma desideri essere in grado di guardare gli ultimi 3 mesi. Invece di dover aggiornare manualmente le formule per continuare a regolare man mano che vengono aggiunti nuovi dati, puoi utilizzare la funzione OFFSET con COUNT.
Abbiamo già mostrato come puoi usare OFFSET per prendere un intervallo di celle. Per determinare quante celle dobbiamo spostare, useremo COUNT per trovare quante numeri sono nella colonna B. Diamo un'occhiata alla nostra tabella di esempio.
| 1 | =SOMMA(OFFSET($B$1,CONTEGGIO(B:B)-$E$1+1,0,$E$1,1)) |
Se iniziassimo da B1 e sfalsiamo 4 righe (il conteggio dei numeri nella colonna B), finiremmo in fondo al nostro intervallo, B5. Tuttavia, poiché OFFSET non può essere ridimensionato con un valore negativo, dobbiamo apportare alcune modifiche in modo da finire in B3. L'equazione generale per questo sarà fare
| 1 | CONTA(… ) - N + 1 |
Prendiamo il conteggio dell'intera colonna, sottraiamo tutti quelli che vogliamo restituire (poiché li ridimensioneremo per prenderli) e quindi aggiungiamo 1 (dato che essenzialmente stiamo iniziando il nostro offset dalla posizione zero).
Qui puoi vedere che abbiamo impostato un intervallo per ottenere la somma, la media e il massimo degli ultimi N mesi. In E1, abbiamo inserito il valore 3. In E2, la nostra formula è
| 1 | =SOMMA(OFFSET($B$1,CONTEGGIO(B:B)-$E$1+1,0,$E$1,1)) |
La sezione evidenziata è la nostra equazione generale che abbiamo appena discusso. Non è necessario eseguire l'offset di alcuna colonna. Ridimensioneremo quindi l'intervallo in modo che sia alto 3 celle (determinato dal valore in E1) e largo 1 colonna. La nostra SOMMA prende quindi questo intervallo e ci dà il risultato di $ 1.850. Abbiamo anche mostrato che puoi calcolare la media del massimo di questo stesso intervallo semplicemente cambiando la funzione esterna da SOMMA a qualunque cosa la situazione richieda.
Elenchi di convalida dinamica OFFSET
Utilizzando la tecnica mostrata nell'ultimo esempio, possiamo anche creare intervalli denominati che potrebbero essere utilizzati nella convalida dei dati o nei grafici. Questo può essere utile quando si desidera impostare un foglio di calcolo ma si prevede che i nostri elenchi/dati cambino dimensione. Diciamo che il nostro negozio sta iniziando a vendere frutta e attualmente abbiamo 3 scelte.
Per creare un menu a discesa di convalida dei dati che possiamo utilizzare altrove, definiremo l'intervallo denominato MyFruit come
| 1 | =$A$2:OFFSET($A$1, COUNTA($A:$A)-1, 0) |
Invece di COUNT, stiamo usando COUNTA poiché abbiamo a che fare con valori di testo. Per questo motivo, tuttavia, il nostro COUNTA sarà uno più alto poiché conterà la cella di intestazione in A1 e darà un valore di 4. Se sfalsiamo di 4 righe, finiremmo nella cella A5 che è vuota. Per aggiustare questo quindi, sottraiamo 1.
Ora che abbiamo impostato il nostro intervallo denominato, possiamo impostare una convalida dei dati nella cella C4 utilizzando un tipo di elenco, con origine:
| 1 | =Il mio frutto |
Nota che il menu a discesa mostra solo i nostri tre elementi attuali. Se poi aggiungiamo più elementi al nostro elenco e torniamo al menu a discesa, l'elenco mostra tutti i nuovi elementi senza che dobbiamo modificare nessuna delle formule.
Precauzioni con l'uso di OFFSET
Come accennato all'inizio di questo articolo, OFFSET è una funzione volatile. Non te ne accorgi se lo stai utilizzando in poche celle, ma se inizi a coinvolgerlo in centinaia di calcoli e noterai rapidamente che il tuo computer impiega una notevole quantità di tempo a ricalcolare ogni volta che apporti modifiche .
Inoltre, poiché OFFSET non nomina direttamente le celle che sta guardando, è più difficile per gli altri utenti venire in seguito e modificare le formule se necessario.
Sarebbe invece consigliabile utilizzare le Tabelle (introdotte in Office 2007) che consentono riferimenti strutturali. Questi hanno aiutato gli utenti a essere in grado di fornire un singolo riferimento che si adattava automaticamente alle dimensioni man mano che i nuovi dati venivano aggiunti o eliminati.
L'altra opzione da utilizzare al posto di OFFSET è la potente funzione INDEX. INDEX ti consente di creare tutti gli intervalli dinamici che abbiamo visto in questo articolo senza il problema di essere una funzione volatile.
Note aggiuntive
Utilizzare la funzione OFFSET per restituire un valore di cella (o un intervallo di celle) compensando un determinato numero di righe e colonne da un riferimento iniziale. Quando si cerca solo una singola cella, le formule OFFSET raggiungono lo stesso scopo delle formule INDEX, utilizzando una tecnica leggermente diversa. Il vero potere della funzione OFFSET risiede nella sua capacità di selezionare un intervallo di celle da utilizzare in un'altra formula.
Quando si utilizza la funzione OFFSET, si definisce una cella iniziale o un intervallo di celle di partenza. Quindi indichi il numero di righe e colonne di cui eseguire l'offset da quella cella iniziale. Puoi anche ridimensionare l'intervallo; aggiungere o sottrarre righe o colonne.
Torna all'elenco di tutte le funzioni in Excel
OFFSET in Fogli Google
La funzione OFFSET funziona esattamente allo stesso modo in Fogli Google come in Excel:
