VLOOKUP, HLOOKUP e INDEX-MATCH sono modi famosi e comuni per cercare un valore nella tabella di Excel. Ma questi non sono gli unici modi per cercare valori in Excel. In questo articolo impareremo come utilizzare la funzione OFFSET insieme alla funzione CONFRONTA 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(CellaInizio, CONFRONTA(ValoreRicerca,IntervalloRicerca,0), CONFRONTA(ValoreRicerca Col,IntervalloRicerca,0)) |
Leggi attentamente questo:
Cella iniziale:Questa è la cella iniziale della tabella di ricerca. Diciamo che se vuoi cercare nell'intervallo A2: A10, StartCell sarà A1.
RowLookupValue: Questo è il valore di ricerca che vuoi trovare nelle righe sotto ilStartCell.
Intervallo di ricerca riga:Questo è l'intervallo in cui si desidera cercare RowLookupValue. È la gamma sotto StartCell (A2:A10).
ColLookupValue: Questo è il valore di ricerca che vuoi 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 vendite utilizzando la funzione OFFSET e MATCH da tabella Excel
Qui abbiamo una tabella di esempio delle vendite effettuate da diversi dipendenti in diversi mesi.
Ora il nostro capo ci chiede di fornire le vendite effettuate 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. RigaRicercaIntervallo è B2:B1o (intervallo al di sotto della cella di partenza).
ColLookupValue è nella cella M2. ColLookupRange è C1:I1 (Header Range a destra della StartCell) .
Abbiamo identificato tutte le variabili. Mettiamoli in una formula di Excel.
Scrivi questa formula nella cella M3 e premi il pulsante Invio.
| =OFFSET(B1,CONFRONTA(M1,B2:B10,0),CONFRONTA(M2,C1:I1,0)) |
Quando premi il pulsante Invio, ottieni subito il risultato. La vendita effettuata con ID 1004 nel mese di giugno è 177.
Come funziona?
Il lavoro della funzione OFFSET consiste nell'allontanarsi dalla data cella iniziale a una determinata riga e colonne e quindi 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 ne restituirà il valore.
Ecco, abbiamo la formulaOFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)).
La prima funzione CONFRONTA restituisce l'indice di M1 (1004) nell'intervallo B2: B10, che è 4. Ora la formula è,OFFSET(B1,4,CONFRONTA(M2,C1:I1,0)).
La funzione MATCH successiva restituisce l'indice di M2 ('Jun') nell'intervallo C1: I1, che i 7. Ora la formula èOFFSET(B1,4,7).
Ora la funzione OFFSET sposta semplicemente 4 celle in basso nella cella B1 che la porta a B5. Quindi la funzione OFFSET si sposta su 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 INDICE-MATCH o la funzione VLOOKUP. Ma è bene conoscere alcune alternative. Potrebbe tornare utile un giorno.
Quindi sì ragazzi, ecco come potete usare le funzioni OFFSET e MATCH per CERCARE i valori nelle tabelle di Excel. Spero di essere stato abbastanza esplicativo. Se hai dei dubbi su questo articolo o su qualsiasi altro argomento relativo a Excel/VBA, chiedimelo nella sezione commenti qui sotto.
Usa INDEX e MATCH per cercare il valore:La formula INDEX-MATCH viene utilizzata per cercare in modo dinamico e preciso un valore in una determinata tabella. Questa è un'alternativa alla funzione CERCA.VERT e supera le carenze della funzione CERCA.VERT.
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.
Come recuperare ogni ennesimo valore in un intervallo in Excel: Utilizzando la funzione OFFSET di Excel possiamo recuperare valori da righe o colonne alternate. Questa formula richiede l'aiuto della funzione RIGA per alternare le righe e la funzione COLONNA per alternare le colonne.
Come utilizzare la funzione OFFSET in Excel: La funzione OFFSET è una potente funzione di Excel sottovalutata da molti utenti. Ma gli esperti conoscono la potenza della funzione OFFSET e come possiamo usare questa funzione per eseguire alcune attività magiche nella formula di Excel. Ecco le basi della funzione OFFSET.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione COUNTIF è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.