Formula OFFSET di Excel (sommario)

  • Che cos'è la formula OFFSET in Excel?
  • Come utilizzare la formula OFFSET in Excel?

Che cos'è la formula OFFSET in Excel?

OFFSET Formula in Excel fornisce una cella o un intervallo di celle rettangolari dinamiche come output che è un sottoinsieme dell'intervallo dato totale con un numero specificato di righe, colonne, altezza e larghezza.

Sintassi della formula OFFSET in Excel

Spiegazione della funzione OFFSET in Excel:

  • riferimento: il punto di partenza per OFFSET o possiamo dire un intervallo di celle che può essere considerato come base per OFFSET.
  • righe : il numero di righe da OFFSET sotto o sopra il riferimento di base.
  • cols: il numero di colonne da OFFSET a destra o a sinistra del riferimento di base.
  • altezza: numero di righe nel riferimento restituito.
  • larghezza: numero di colonne nel riferimento restituito.

L'altezza e la larghezza sono i parametri opzionali per questa funzione e possono essere utilizzati per determinare la dimensione del riferimento creato.

Come utilizzare la formula OFFSET in Excel?

Impareremo ora come scrivere la formula di offset per ottenere il valore di ricerca. Comprendiamo la formula di offset con alcuni esempi in Excel.

Puoi scaricare questo OFFSET Formula Excel Template qui - OFFSET Formula Excel Template

Excel OFFSET Formula - Esempio n. 1

Supponiamo di avere dati come indicato di seguito.

  • Nella cella E2, inserisci la seguente formula:

= OFFSET (A1, 11, 1)

  • Una volta terminato con la formula, premere Invio e vedere l'output.

Ciò che questa formula ha fatto è che ha preso riferimento dalla cella A1 (Anno) e si è spostato verso il basso di 11 righe dopo A1 (ovvero la riga n. 12) e quindi ha ottenuto il valore che è coordinato dell'11a riga dopo A1 e la prima colonna ( colonna A = 0 colonna B = 1 in questo caso. Come sappiamo, possiamo andare sia a destra che a sinistra). Il che è $ 141, 27 milioni.

Excel OFFSET Formula - Esempio n. 2

Ora vedremo un altro esempio di cella di riferimento per un anno specifico. Inserisci la seguente formula nella cella E3:

= OFFSET (A1, 11, 0)

  • Una volta terminato con la formula, premere Invio e vedere l'output.

Se hai visto la formula, è la stessa della precedente, l'unica modifica è nel riferimento di colonna. Il riferimento di colonna 0 (Zero) indica che il valore verrà acquisito dalla colonna A per l' 11a riga dopo A1 (che è il 2010).

Excel OFFSET Formula - Esempio n. 3

In questo esempio, calcoleremo la somma delle ultime N righe utilizzando la funzione OFFSET di Excel. La formula generale per riassumere gli ultimi N numeri è:

= SOMMA (OFFSET (A1, COUNT (A: A), 0, -N))

Per riassumere le ultime N osservazioni dai dati, è possibile utilizzare la funzione SUM insieme a OFFSET e COUNT insieme. In questo esempio, riassumeremo gli ultimi 5 punti dati. Si noti che quando diciamo N, è una generalizzazione di qualsiasi valore intero. Quindi, puoi calcolare per gli ultimi 3, gli ultimi 4, gli ultimi 5, ecc. A seconda del tuo set di dati e delle tue esigenze.

Facciamolo per il nostro set di dati:

  • Inserisci la seguente formula nella cella E2 del file Excel.

= SOMMA (OFFSET (B1, COUNT (B: B), 0, -5))

  • Fai clic su Invio per vedere l'output.

Come abbiamo già visto, la funzione OFFSET di Excel genera intervalli dinamici rettangolari quando viene fornito un riferimento iniziale.

  • In questo caso, il riferimento iniziale viene fornito in termini di B1 (come primo argomento della funzione OFFSET).
  • Abbiamo usato la funzione COUNT perché abbiamo bisogno della somma delle osservazioni dall'ultimo valore. La funzione COUNT aiuta OFFSET a determinare quante osservazioni (righe) ci sono nella colonna B. Conta tutti i valori numerici nella colonna B (19 è il conteggio).
  • Con un conteggio di 19, la formula OFFSET diventa:

= OFFSET (B1, 19, 0, -5)

  • Questa formula inizia la compensazione su B1 con 19 righe e la stessa colonna (0). Quindi il parametro altezza -5 aiuta questa funzione ad estendere l'intervallo nella direzione all'indietro di 5 file (vedere il segno negativo associato a 5).
  • L'intervallo dinamico delle ultime cinque osservazioni viene fornito come input per la funzione di somma. Che produce nel sommare quelli dalla colonna B.

Excel OFFSET Formula - Esempio n. 4

In questo esempio, calcoleremo le vendite mediane degli ultimi 3 anni. La funzione OFFSET può essere utilizzata come argomento per calcolare lo stesso.

  • Inserisci la seguente formula nella cella E2:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Premi Invio per vedere l'output.

Questa formula funziona su linee simili alla precedente.

  • OFFSET Crea un intervallo dinamico delle ultime tre osservazioni dalla colonna B con l'aiuto della funzione COUNT (= OFFSET (A1, 19, 0, -3)).
  • Questo può essere dato come output alla funzione MEDIAN. Che quindi ordina le osservazioni in ordine crescente o decrescente e quindi seleziona il valore più medio come vedi nell'output ($ 212, 07).

Excel OFFSET Formula - Esempio n. 5

Qui calcoleremo l'anno mediano usando la funzione MEDIANTE e OFFSET.

  • Utilizzare la seguente formula per lo stesso nella cella E3:

= MEDIANA (OFFSET (A1, COUNT (A: A), 0, -3))

  • Fai clic su Invio per vedere l'output.

La stessa cosa è successa qui. La formula ha assunto un intervallo dinamico creato dalla funzione OFFSET con l'aiuto di COUNT. E lo ha usato come argomento per una funzione MEDIANA che poi ha ordinato i valori dell'anno in ordine crescente o decrescente e quindi ha dato l'output come 2017.

L'uso della formula OFFSET è nella creazione degli intervalli dinamici. È sempre meglio lavorare su intervalli dinamici anziché statici. Poiché potresti avere dei dati che vengono aggiunti di tanto in tanto. Ad esempio, Employee Master. I dati anagrafici dei tuoi dipendenti verranno sempre aumentati di tanto in tanto in quanto ci devono essere alcuni nuovi dipendenti che si uniscono. Ogni volta che usare la stessa formula non ha alcun senso. Invece, crea un intervallo dinamico utilizzando la funzione OFFSET e osserva la magia. Ti farà sicuramente risparmiare un po 'di tempo.

Cose da ricordare

  • La funzione OFFSET restituisce solo un riferimento a un particolare intervallo di celle in Excel. Non sposta le celle dalla loro posizione originale.
  • Per impostazione predefinita, questa funzione è attiva nella parte superiore (in termini di righe) e sul lato destro (in termini di colonne). Tuttavia, può essere fornito con argomenti negativi per invertire la funzionalità predefinita. L'argomento riga negativo simile aiuta a compensare i dati dal basso verso l'alto e l'argomento col negativo aiuta a compensare i dati a sinistra.
  • La formula OFFSET può rallentare la funzionalità di Excel in quanto è una formula che viene ricalcolata ogni volta che si verifica una modifica nel foglio di lavoro.
  • L'errore #REF verrà generato se l'intervallo non rientra nell'intervallo della cartella di lavoro.
  • Qualsiasi altra funzione che prevede un riferimento è in grado di utilizzare la funzione OFFSET al suo interno.

Articoli consigliati

Questa è una guida alla formula OFFSET in Excel. Qui discutiamo come usare la funzione OFFSET in Excel insieme ad esempi pratici e template Excel scaricabili. Puoi anche consultare i nostri altri articoli suggeriti:

  1. Come avvolgere il testo in Excel?
  2. Guida semplice all'ordinamento in Excel
  3. Funzione LOOKUP in Excel
  4. Come usare la funzione FIND in Excel?

Categoria:

Suggerimenti Excel