Come utilizzare la funzione OFFSET di Excel -

Sommario

La funzione OFFSET di Excel restituisce un riferimento a un intervallo costruito con cinque input: (1) un punto di partenza, (2) un offset di riga, (3) un offset di colonna, (4) un'altezza nelle righe, (5) una larghezza in colonne. OFFSET è utile nelle formule che richiedono un intervallo dinamico.

Scopo

Crea un offset di riferimento dal punto iniziale dato

Valore di ritorno

Un riferimento di cella.

Sintassi

= OFFSET (riferimento, righe, colonne, (altezza), (larghezza))

argomenti

  • riferimento - Il punto di partenza, fornito come riferimento o intervallo di cella.
  • righe : il numero di righe di cui eseguire l'offset sotto il riferimento iniziale.
  • cols - Il numero di colonne da spostare a destra del riferimento iniziale.
  • height : (facoltativo) l'altezza in righe del riferimento restituito.
  • larghezza : (opzionale) la larghezza in colonne del riferimento restituito.

Versione

Excel 2003

Note sull'utilizzo

La funzione OFFSET di Excel restituisce un intervallo dinamico costruito con cinque input: (1) un punto di partenza, (2) un offset di riga, (3) un offset di colonna, (4) un'altezza nelle righe, (5) una larghezza nelle colonne.

Il punto di partenza (l' argomento di riferimento ) può essere una cella o un intervallo di celle. Le righe e cols argomenti sono il numero di cellule per "offset" dal punto di partenza. Gli argomenti altezza e larghezza sono facoltativi e determinano la dimensione dell'intervallo creato. Quando l' altezza e la larghezza vengono omesse, vengono impostate per impostazione predefinita sull'altezza e la larghezza di riferimento .

Ad esempio, per fare riferimento a C5 a partire da A1, il riferimento è A1, le righe sono 4 e le colonne sono 2:

=OFFSET(A1,4,2) // returns reference to C5

Per fare riferimento a C1: C5 da A1, il riferimento è A1, le righe sono 0, cols è 2, l' altezza è 5 e la larghezza è 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Nota: la larghezza potrebbe essere omessa, poiché sarà predefinita a 1.

È comune vedere OFFSET racchiuso in un'altra funzione che prevede un intervallo. Ad esempio, per SUM C1: C5, a partire da A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Lo scopo principale di OFFSET è consentire alle formule di adattarsi dinamicamente ai dati disponibili o all'input dell'utente. La funzione OFFSET può essere utilizzata per creare un intervallo denominato dinamico per grafici o tabelle pivot, per garantire che i dati di origine siano sempre aggiornati.

Nota: la documentazione di Excel afferma che l' altezza e la larghezza non possono essere negative, ma i valori negativi sembrano aver funzionato bene dall'inizio degli anni '90. La funzione OFFSET in Fogli Google non consentirà un valore negativo per gli argomenti di altezza o larghezza.

Esempi

Gli esempi seguenti mostrano come è possibile configurare OFFSET per restituire diversi tipi di intervalli. Queste schermate sono state scattate con Excel 365, quindi OFFSET restituisce una matrice dinamica quando il risultato è più di una cella. Nelle versioni precedenti di Excel, è possibile utilizzare il tasto F9 per controllare i risultati restituiti da OFFSET.

Esempio 1

Nella schermata seguente, utilizziamo OFFSET per restituire il terzo valore (marzo) nella seconda colonna (ovest). La formula in H4 è:

=OFFSET(B3,3,2) // returns D6

Esempio n. 2

Nella schermata seguente, usiamo OFFSET per restituire l'ultimo valore (giugno) nella terza colonna (Nord). La formula in H4 è:

=OFFSET(B3,6,3) // returns E9

Esempio n. 3

Di seguito, utilizziamo OFFSET per restituire tutti i valori nella terza colonna (Nord). La formula in H4 è:

=OFFSET(B3,1,3,6) // returns E4:E9

Esempio n. 4

Di seguito, utilizziamo OFFSET per restituire tutti i valori per maggio (quinta riga). La formula in H4 è:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Esempio n. 5

Di seguito, utilizziamo OFFSET per restituire i valori di aprile, maggio e giugno per la regione occidentale. La formula in H4 è:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Esempio # 6

Di seguito, utilizziamo OFFSET per restituire i valori di aprile, maggio e giugno per ovest e nord. La formula in H4 è:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Appunti

  • OFFSET restituisce solo un riferimento, nessuna cella viene spostata.
  • Entrambe le righe e colli possono essere forniti come numeri negativi per invertire la direzione di offset normale - negativi colli spostate sulla sinistra e negativi righe sfalsata.
  • OFFSET è una "funzione volatile": verrà ricalcolata ad ogni modifica del foglio di lavoro. Le funzioni volatili possono rallentare l'esecuzione di cartelle di lavoro più grandi e complesse.
  • OFFSET mostrerà il #REF! valore di errore se l'offset è esterno al bordo del foglio di lavoro.
  • Quando si omette l'altezza o la larghezza, vengono utilizzate l'altezza e la larghezza di riferimento .
  • OFFSET può essere utilizzato con qualsiasi altra funzione che prevede di ricevere un riferimento.
  • La documentazione di Excel dice che l' altezza e la larghezza non possono essere negative, ma i valori negativi funzionano.

Video collegati

Come creare un intervallo con nome dinamico con OFFSET In questo video, vedremo come creare un intervallo con nome dinamico utilizzando la funzione OFFSET, il modo più comune per creare un intervallo di nomi dinamico con una formula.

Articoli interessanti...