image

VLOOKUP, HLOOKUP e INDEX-MATCH sono modi famosi e comuni per cercare valori nella tabella di Excel. Ma questi non sono gli unici modi per cercare i valori in Excel. In questo articolo, impareremo come utilizzare la funzione OFFSET insieme alla funzione MATCH in Excel. Sì, hai letto bene, useremo la famigerata funzione OFFSET di Excel per cercare un determinato valore in una tabella Excel.

Formula generica

=OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

image

Leggi attentamente:

StartCell: questa è la cella iniziale della tabella di ricerca. Supponiamo che se desideri cercare nell’intervallo A2: A10, StartCell sarà A1.

RowLookupValue: questo è il valore di ricerca che desideri trovare nelle righe sotto StartCell. RowLookupRange: questo è l’intervallo in cui si desidera cercare RowLookupValue. È l’intervallo sotto StartCell (A2: A10). ColLookupValue: questo è il valore di ricerca che desideri trovare nelle colonne (intestazioni).

ColLookupRange: questo è l’intervallo in cui si desidera cercare ColLookupValue. È l’intervallo sul lato destro di StartCell (come B1: D1).

Quindi, basta con la teoria. Cominciamo con un esempio.

Esempio: ricerca delle vendite utilizzando la funzione OFFSET e CONFRONTA dalla tabella Excel Qui abbiamo una tabella di esempio delle vendite effettuate da diversi dipendenti in diversi mesi.

Ora il nostro capo ci sta chiedendo di dare le vendite fatte da Id 1004 nel mese di giugno. Ci sono molti modi per farlo, ma poiché stiamo imparando a conoscere la formula OFFSET-MATCH, li useremo per cercare il valore desiderato.

Abbiamo già la formula generica sopra. Abbiamo solo bisogno di identificare queste variabili in questa tabella.

StartCell è B1 qui. RowLookupValue è M1. RowLookupRange è B2: B1o (intervallo sotto la cella iniziale).

ColLookupValue è nella cella M2. ColLookupRange è C1: I1 (intervallo di intestazione a destra di StartCell).

Abbiamo identificato tutte le variabili. Mettiamoli in una formula Excel.

Scrivi questa formula nella cella M3 e premi il pulsante Invio.

=OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))*

Non appena premi il pulsante Invio, ottieni subito il risultato. La vendita effettuata ID 1004 nel mese di giugno è 177.

Come funziona?

Il lavoro di link: / counting-the-offset-function-in-excel [funzione OFFSET] è quello di spostarsi dalla cella iniziale data a righe e colonne date e poi restituire il valore da quella cella. Ad esempio, se scrivo OFFSET (B1,1,1), la funzione OFFSET andrà alla cella C2 (1 cella in basso, 1 cella a destra) e restituirà il valore.

Ecco, abbiamo la formula

OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))

Il primo link: / lookup-formas-excel-match-function [MATCH function] restituisce l’indice di M1 (1004) nell’intervallo B2: B10, che è 4.

Ora la formula è

OFFSET(B1,4,MATCH(M2,C1:I1,0))

La funzione Next MATCH restituisce l’indice di M2 (‘Jun’) nell’intervallo C1: I1, che I7. Ora la formula è OFFSET (B1,4,7). Ora la funzione OFFSET sposta semplicemente 4 celle verso il basso nella cella B1 che la porta a B5. Quindi la funzione OFFSET si sposta a 7 da sinistra a B5, che la porta a I5. Questo è tutto. La funzione OFFSET restituisce il valore dalla cella I5 che è 177.

Vantaggi di questa tecnica di ricerca?

Questo è veloce. L’unico vantaggio di questo metodo è che è più veloce degli altri metodi. La stessa cosa può essere eseguita utilizzando la funzione INDEX-MATCH o la funzione CERCA.VERT. Ma è bene conoscere alcune alternative.

Potrebbe tornare utile un giorno.

Ecco alcune note sull’utilizzo della formula di offset in Excel.

Note:

  1. OFFSET restituisce solo un riferimento, nessuna cella viene spostata.

  2. Sia le righe che le colonne possono essere fornite come numeri negativi per invertire la loro normale direzione di offset: colonne negative spostate a sinistra e righe negative spostate sopra.

  3. 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.

  4. OFFSET mostrerà il #REF! valore di errore se l’offset è esterno al bordo del foglio di lavoro.

  5. Quando si omette l’altezza o la larghezza, vengono utilizzate l’altezza e la larghezza di riferimento.

  6. OFFSET può essere utilizzato con qualsiasi altra funzione che prevede di ricevere un riferimento.

  7. La documentazione di Excel afferma che l’altezza e la larghezza non possono essere negative, ma i valori negativi funzionano.

Spero che questo articolo su Come utilizzare la formula di offset in Microsoft Excel sia esplicativo. Trova altri articoli sui valori di ricerca e le formule di Excel correlate qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi amici su Facebook. Puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected].

Articoli correlati:

link: / lookup-formas-use-index-and-match-to-lookup-value [Use INDEX and MATCH to Lookup Value]: La formula INDEX-MATCH viene utilizzata per cercare in modo dinamico e preciso un valore in una data tabella. Questa è un’alternativa alla funzione CERCA.VERT e supera le carenze della funzione CERCA.VERT.

link: / tips-sum-by-offset-groups-in-rows-and-columns [Somma per gruppi OFFSET in righe e colonne]: La funzione OFFSET può essere utilizzata per sommare dinamicamente gruppi di celle. Questi gruppi possono trovarsi ovunque nel foglio.

link: / lookup-formas-retrieving-every-nth-value-in-a-range [How to Retrieve Every Nth Value in a Range in Excel]: Usando la funzione OFFSET di Excel possiamo recuperare valori da righe alternate o colonne. Questa formula utilizza la funzione ROW per alternare le righe e la funzione COLUMN per alternarsi nelle colonne.

link: / counting-the-offset-function-in-excel [Come usare la funzione OFFSET in Excel]: La funzione OFFSET è una potente funzione di Excel che è sottovalutata da molti utenti. Ma gli esperti conoscono la potenza della funzione OFFSET e come possiamo utilizzare questa funzione per eseguire alcuni compiti magici nella formula di Excel. Ecco le basi della funzione OFFSET.

Articoli popolari:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività]: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.

link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT di Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da diversi intervalli e fogli .

link: / tips-countif-in-microsoft-excel [Come usare]

link: / formule-e-funzioni-introduzione-della-funzione-vlookup [Excel]

link: / tips-countif-in-microsoft-excel [COUNTIF Function]: Conta i valori con le condizioni usando questa straordinaria funzione.

Non è necessario filtrare i dati per contare valori specifici. La funzione CONTA.SE è essenziale per preparare la dashboard.

link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.