Excel ha molte funzioni - circa 450+ di esse.
E molti di questi sono semplicemente fantastici. La quantità di lavoro che puoi fare con poche formule mi sorprende ancora (anche dopo aver usato Excel per più di 10 anni).
E tra tutte queste incredibili funzioni, spicca la combinazione di funzioni INDEX MATCH.
Sono un grande fan della combo INDEX MATCH e l'ho detto abbastanza chiaramente molte volte.
Ho anche scritto un articolo su Index Match Vs VLOOKUP che ha suscitato un po' di dibattito (puoi controllare la sezione commenti per alcuni fuochi d'artificio).
E oggi sto scrivendo questo articolo incentrato esclusivamente su Index Match per mostrarti alcuni scenari semplici e avanzati in cui puoi utilizzare questa potente combinazione di formule e portare a termine il lavoro.
Nota: Esistono altre formule di ricerca in Excel, come VLOOKUP e HLOOKUP e queste sono fantastiche. Molte persone trovano che CERCA.VERT sia più facile da usare (e anche questo è vero). Credo che INDEX MATCH sia un'opzione migliore in molti casi. Ma poiché le persone lo trovano difficile, viene utilizzato di meno. Quindi sto cercando di semplificarlo usando questo tutorial.Ora, prima di mostrarti come la combinazione di INDEX MATCH sta cambiando il mondo degli analisti e dei data scientist, lascia che ti presenti prima le singole parti: le funzioni INDEX e MATCH.
Funzione INDICE: trova il valore in base alle coordinate
Il modo più semplice per capire come funziona la funzione Index è immaginarlo come un satellite GPS.
Non appena comunicherai al satellite le coordinate di latitudine e longitudine, saprà esattamente dove andare e trovare quella posizione.
Quindi, nonostante abbia un numero sbalorditivo di combinazioni lat-long, il satellite saprebbe esattamente dove guardare.
Ho fatto rapidamente una ricerca per la mia posizione di lavoro e questo è quello che ho ottenuto.
Comunque, basta con la geografia.
Proprio come un satellite ha bisogno di coordinate di latitudine e longitudine, la funzione INDICE in Excel richiederebbe il numero di riga e colonna per sapere a quale cella ti riferisci.
E questa è la funzione INDICE di Excel in poche parole.
Quindi lascia che lo definisca in parole semplici per te.
La funzione INDICE utilizzerà il numero di riga e il numero di colonna per trovare una cella nell'intervallo specificato e restituire il valore in essa contenuto.
Da solo, INDEX è una funzione molto semplice, senza alcuna utilità. Dopotutto, nella maggior parte dei casi, non è probabile che tu conosca i numeri di riga e colonna.
Ma…
Il fatto che tu possa usarlo con altre funzioni (suggerimento: CONFRONTA) che possono trovare il numero di riga e il numero di colonna rende INDEX una funzione di Excel estremamente potente.
Di seguito è riportata la sintassi della funzione INDICE:
=INDICE (matrice, num_riga, [num_col]) =INDICE (matrice, num_riga, [num_col], [num_area])
- Vettore - un intervallo di celle o una costante di matrice.
- numero_riga - il numero di riga da cui deve essere prelevato il valore.
- [num_colo] - il numero di colonna da cui deve essere prelevato il valore. Sebbene questo sia un argomento facoltativo, ma se row_num non è fornito, deve essere fornito.
- [numero_area] - (Facoltativo) Se l'argomento dell'array è costituito da più intervalli, questo numero verrà utilizzato per selezionare il riferimento da tutti gli intervalli.
La funzione INDEX ha 2 sintassi (solo FYI).
Il primo viene utilizzato nella maggior parte dei casi. Il secondo è usato solo in casi avanzati (come fare una ricerca a tre vie) che tratteremo in uno degli esempi più avanti in questo tutorial.
Ma se sei nuovo in questa funzione, ricorda solo la prima sintassi.
Di seguito un video che spiega come utilizzare la funzione INDICE
Funzione MATCH: trova la posizione basata su un valore di ricerca
Tornando al mio precedente esempio di longitudine e latitudine, CONFRONTA è la funzione che può trovare queste posizioni (nel mondo dei fogli di calcolo di Excel).
In un linguaggio semplice, la funzione CONFRONTA di Excel può trovare la posizione di una cella in un intervallo.
E su quale base troverebbe la posizione di una cellula?
In base al valore di ricerca.
Ad esempio, se si dispone di un elenco come mostrato di seguito e si desidera trovare la posizione del nome "Segna" al suo interno, è possibile utilizzare la funzione CONFRONTA.
La funzione restituisce 3, poiché questa è la posizione della cella con il nome Mark in essa.
La funzione MATCH inizia a cercare dall'alto verso il basso il valore di ricerca (che è "Mark") nell'intervallo specificato (che è A1: A9 in questo esempio). Non appena trova il nome, restituisce la posizione in quell'intervallo specifico.
Di seguito è riportata la sintassi della funzione CONFRONTA in Excel.
=CONFRONTA(valore_ricerca, matrice_ricerca, [tipo_corrispondenza])
- valore di ricerca - Il valore per il quale stai cercando una corrispondenza in lookup_array.
- array_di ricerca - L'intervallo di celle in cui stai cercando il lookup_value.
- [match_type] - (Facoltativo) Specifica come excel dovrebbe cercare un valore corrispondente. Può assumere tre valori -1, 0 o 1.
Comprensione dell'argomento del tipo di corrispondenza nella funzione CONFRONTA
C'è un'altra cosa che devi sapere sulla funzione CONFRONTA, e riguarda il modo in cui passa attraverso i dati e trova la posizione della cella.
Il terzo argomento della funzione CONFRONTA può essere 0, 1 o -1.
Di seguito è riportata una spiegazione di come funzionano questi argomenti:
- 0 - questo cercherà una corrispondenza esatta del valore. Se viene trovata una corrispondenza esatta, la funzione CONFRONTA restituirà la posizione della cella. Altrimenti, restituirà un errore.
- 1 - trova il valore più grande minore o uguale al valore di ricerca. Affinché ciò funzioni, l'intervallo di dati deve essere ordinato in ordine crescente.
- -1 - trova il valore più piccolo maggiore o uguale al valore di ricerca. Affinché ciò funzioni, l'intervallo di dati deve essere ordinato in ordine decrescente.
Di seguito è riportato un video che spiega come utilizzare la funzione CONFRONTA (insieme all'argomento del tipo di corrispondenza)
Per riassumere e metterlo in parole semplici:
- INDICE necessita della posizione della cella (numero di riga e colonna) e fornisce il valore della cella.
- CONFRONTA trova la posizione utilizzando un valore di ricerca.
Combiniamoli per creare una centrale elettrica (INDICE + CORRISPONDENZA)
Ora che hai una conoscenza di base di come funzionano individualmente le funzioni INDICE e CONFRONTA, combiniamo queste due cose e impariamo a conoscere tutte le cose meravigliose che può fare.
Per capirlo meglio, ho alcuni esempi che usano la combinazione INDICE MATCH.
Inizierò con un semplice esempio e poi ti mostrerò anche alcuni casi d'uso avanzati.
Clicca qui per scaricare il file di esempio
Esempio 1: una semplice ricerca utilizzando INDEX MATCH Combo
Facciamo una semplice ricerca con INDEX/MATCH.
Di seguito c'è una tabella dove ho i voti per dieci studenti.
Da questa tabella, voglio trovare i voti per Jim.
Di seguito è riportata la formula che può facilmente farlo:
=INDICE($A$2:$B$11,CONFRONTA("Jim",$A$2:$A$11,0),2)
Ora, se stai pensando che questo possa essere fatto facilmente usando una funzione CERCA.VERT, hai ragione! Questo non è il miglior uso della meraviglia di INDEX MATCH. Nonostante io sia un fan di INDEX MATCH, è un po' più difficile di CERCA.VERT. Se il recupero dei dati da una colonna a destra è tutto ciò che vuoi fare, ti consiglio di utilizzare CERCA.VERT.
Il motivo per cui ho mostrato questo esempio, che può essere facilmente eseguito anche con CERCA.VERT, è mostrarti come funziona INDEX MATCH in un'impostazione semplice.
Ora lascia che ti mostri un vantaggio di INDEX MATCH.
Supponiamo di avere gli stessi dati, ma invece di averli in colonne, li hai in righe (come mostrato di seguito).
Sai una cosa, puoi ancora usare la combo INDEX MATCH per ottenere i voti di Jim.
Di seguito è la formula che ti darà il risultato:
=INDICE($B$1:$K$2,2,CONFRONTA(“Jim”,$B$1:$K$1,0))
Si noti che è necessario modificare l'intervallo e cambiare le parti di riga/colonna per far funzionare questa formula anche per i dati orizzontali.
Questo non può essere fatto con CERCA.VERT, ma puoi comunque farlo facilmente con CERCA.ORIZZ.
La combinazione INDEX MATCH può gestire facilmente dati orizzontali e verticali.Clicca qui per scaricare il file di esempio
Esempio 2: ricerca a sinistra
È più comune di quanto pensi.
Molte volte, potrebbe essere necessario recuperare i dati da una colonna che si trova a sinistra della colonna che ha il valore di ricerca.
Qualcosa come mostrato di seguito:
Per scoprire le vendite di Michael, dovrai fare una ricerca a sinistra.
Se stai pensando CERCA.VERT, lascia che ti fermi qui.
VLOOKUP non è fatto per cercare e recuperare i valori a sinistra.
Puoi ancora farlo usando VLOOKUP?
Si, puoi!
Ma questo può trasformarsi in una formula lunga e brutta.
Quindi, se vuoi fare una ricerca e recuperare i dati dalle colonne a sinistra, è meglio usare la combinazione INDEX MATCH.
Di seguito è riportata la formula che otterrà il numero di vendita di Michael:
=INDICE($A$2:$C$11, CONFRONTA("Michele",C2:C11,0),2)
Esempio 3: ricerca bidirezionale
Finora, abbiamo visto gli esempi in cui volevamo recuperare i dati dalla colonna adiacente alla colonna che ha il valore di ricerca.
Ma nella vita reale, i dati spesso si estendono su più colonne.
INDEX MATCH può gestire facilmente una ricerca bidirezionale.
Di seguito è riportato un set di dati dei voti dello studente in tre diverse materie.
Se vuoi ottenere rapidamente i voti di uno studente in tutte e tre le materie, puoi farlo con INDEX MATCH.
La formula seguente ti darà i segni per Jim per tutti e tre i soggetti (copia e incolla in una cella e trascina per riempire altre celle o copia e incolla su altre celle).
=INDICE($B$2:$D$11,CONFRONTA($F$3,$A$2:$A$11,0),CONFRONTA(V$2,$B$1:$D$1,0))
Permettetemi di spiegare rapidamente anche questa formula.
La formula INDICE utilizza B2:D11 come intervallo.
Il primo MATCH utilizza il nome (Jim nella cella F3) e ne recupera la posizione nella colonna dei nomi (A2:A11). Questo diventa il numero di riga da cui devono essere recuperati i dati.
La seconda formula CONFRONTA utilizza il nome dell'oggetto (nella cella G2) per ottenere la posizione di quel nome specifico dell'oggetto in B1:D1. Ad esempio, Matematica è 1, Fisica è 2 e Chimica è 3.
Poiché queste posizioni CONFRONTA vengono inserite nella funzione INDICE, restituisce il punteggio in base al nome dello studente e al nome della materia.
Questa formula è dinamica, il che significa che se cambi il nome dello studente oi nomi delle materie, funzionerà comunque e recupererà i dati corretti.
Una cosa grandiosa dell'uso di INDEX/MATCH è che anche se si scambiano i nomi dei soggetti, continuerà a darti il risultato corretto.Esempio 4: valore di ricerca da più colonne/criteri
Supponiamo di avere un set di dati come mostrato di seguito e di voler recuperare i segni per "Segna lungo".
Poiché i dati sono in due colonne, non posso cercare Mark e ottenere i dati.
Se lo faccio in questo modo, otterrò i dati dei segni per Mark Frost e non per Mark Long (perché la funzione MATCH mi darà il risultato per il MARK che incontra).
Un modo per farlo è creare una colonna di supporto e combinare i nomi. Una volta che hai la colonna helper, puoi usare VLOOKUP e ottenere i dati dei segni.
Se sei interessato a imparare come farlo, leggi questo tutorial sull'utilizzo di CERCA.VERT con più criteri.
Ma con la combinazione INDEX/MATCH, non hai bisogno di una colonna helper. Puoi creare una formula che gestisca più criteri nella formula stessa.
La formula seguente darà il risultato.
=INDICE($C$2:$C$11,CONFRONTA($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))
Lascia che ti spieghi rapidamente cosa fa questa formula.
La parte CONFRONTA della formula combina il valore di ricerca (Mark e Long) e l'intero array di ricerca. Quando $A$2:A11&”|”&$B$2:$B$11 viene utilizzato come array di ricerca, in realtà controlla il valore di ricerca rispetto alla stringa combinata di nome e cognome (separata dal simbolo della barra verticale).
Ciò garantisce di ottenere il risultato corretto senza utilizzare colonne di supporto.
Puoi eseguire questo tipo di ricerca (dove sono presenti più colonne/criteri) anche con CERCA.VERT, ma devi utilizzare una colonna di supporto. La combinazione INDEX MATCH rende leggermente più facile farlo senza colonne di supporto.Esempio 5: ottenere valori da un'intera riga/colonna
Negli esempi sopra, abbiamo usato la funzione INDICE per ottenere il valore da una cella specifica. Fornisci il numero di riga e colonna e restituisce il valore in quella cella specifica.
Ma puoi fare di più.
Puoi anche usare la funzione INDICE per ottenere i valori da un'intera riga o colonna.
E come può esserti utile chiedi!
Supponiamo di voler conoscere il punteggio totale di Jim in tutte e tre le materie.
Puoi usare la funzione INDICE per ottenere prima tutti i voti di Jim e poi usare la funzione SOMMA per ottenere un totale.
Vediamo come farlo.
Di seguito ho i punteggi di tutti gli studenti in tre materie.
La formula seguente mi darà il punteggio totale di Jim in tutte e tre le materie.
=SOMMA(INDICE($B$2:$D$11, CONFRONTA($F$4,$A$2:$A$11,0),0))
Lascia che ti spieghi come funziona questa formula.
Il trucco qui è usare 0 come numero di colonna.
Quando usi 0 come numero di colonna nella funzione INDEX, restituirà tutti i valori di riga. Allo stesso modo, se usi 0 come numero di riga, restituirà tutti i valori nella colonna.
Quindi la parte sottostante della formula restituisce una matrice di valori - {97, 70, 73}
INDICE($B$2:$D$11,CONFRONTA($F$4,$A$2:$A$11,0),0)
Se inserisci questa formula sopra in una cella in Excel e premi invio, vedrai un #VALORE! errore. Questo perché non restituisce un singolo valore, ma un array di valori.
Ma non preoccuparti, l'array di valori è ancora lì. Puoi verificarlo selezionando la formula e premendo il tasto F9. Ti mostrerà il risultato della formula che in questo caso è una matrice di tre valori - {97, 70, 73}
Ora, se avvolgi questa formula INDICE nella funzione SOMMA, ti darà la somma di tutti i voti segnati da Jim.
Puoi anche usare lo stesso per ottenere i voti più alti, più bassi e medi di Jim.
Proprio come abbiamo fatto per uno studente, puoi farlo anche per una materia. Ad esempio, se desideri il punteggio medio in un argomento, puoi mantenere il numero di riga come 0 nella formula INDICE e ti darà tutti i valori di colonna di quell'argomento.
Clicca qui per scaricare il file di esempio
Esempio 6: trovare il voto dello studente (tecnica di corrispondenza approssimativa)
Finora abbiamo utilizzato la formula MATCH per ottenere la corrispondenza esatta del valore di ricerca.
Ma puoi anche usarlo per fare una corrispondenza approssimativa.
Ora, che diavolo è la corrispondenza approssimativa?
Lasciatemi spiegare.
Quando cerchi elementi come nomi o ID, stai cercando una corrispondenza esatta. Ma a volte, è necessario conoscere l'intervallo in cui si trovano i valori di ricerca. Questo è di solito il caso dei numeri.
Ad esempio, come insegnante di classe, potresti voler sapere qual è il voto di ogni studente in una materia e il voto viene deciso in base al punteggio.
Di seguito è riportato un esempio, in cui voglio il voto per tutti gli studenti e il voto viene deciso in base alla tabella a destra.
Quindi se uno studente ottiene meno di 33, il voto è F e se ottiene meno di 50 ma più di 33, è E, e così via.
Di seguito è la formula che farà questo.
=INDICE($F$3:$F$8,CONFRONTA(B2,$E$3:$E$8,1),1)
Lascia che ti spieghi come funziona questa formula.
Nella funzione MATCH, abbiamo usato 1 come argomento [match_type]. Questo argomento restituirà il valore più grande minore o uguale al valore di ricerca.
Ciò significa che la formula CONFRONTA attraversa l'intervallo di segni e non appena trova un intervallo di segni uguale o inferiore al valore dei segni di ricerca, si fermerà lì e restituirà la sua posizione.
Quindi, se il valore del segno di ricerca è 20, la funzione CONFRONTA restituirà 1 e se è 85, restituirà 5.
E la funzione INDICE utilizza questo valore di posizione per ottenere il voto.
IMPORTANTE: affinché funzioni, i dati devono essere ordinati in ordine crescente. In caso contrario, puoi ottenere risultati errati.Nota che quanto sopra può essere fatto anche usando la formula VLOOKUP sotto:
=CERCA.VERT(B2,$E$3:$F$8,2,VERO)
Ma la funzione MATCH può fare un ulteriore passo avanti quando si tratta di una corrispondenza approssimativa.
Puoi anche avere un dato decrescente e puoi usare la combinazione INDEX MATCH per trovare il risultato. Ad esempio, se cambio l'ordine della tabella dei voti (come mostrato di seguito), posso ancora trovare i voti degli studenti.
Per fare ciò, tutto ciò che devo fare è cambiare l'argomento [match_type] in -1.
Di seguito è la formula che ho usato:
=INDICE($F$3:$F$8,CONFRONTA(B2,$E$3:$E$8,-1),1)CERCA.VERT può anche eseguire una corrispondenza approssimativa ma solo quando i dati sono ordinati in ordine crescente (ma non funziona se i dati sono ordinati in ordine decrescente).
Esempio 7: ricerche con distinzione tra maiuscole e minuscole
Finora tutte le ricerche che abbiamo fatto sono state senza distinzione tra maiuscole e minuscole.
Ciò significa che se il valore di ricerca era Jim o JIM o jim, non importava. Otterrai lo stesso risultato.
Ma cosa succede se si desidera che la ricerca faccia distinzione tra maiuscole e minuscole.
Questo è di solito il caso quando si dispone di set di dati di grandi dimensioni e la possibilità di ripetizione o nomi/id distinti (con l'unica differenza nel caso)
Ad esempio, supponiamo di avere il seguente set di dati di studenti in cui ci sono due studenti con il nome Jim (l'unica differenza è che uno è inserito come Jim e l'altro come jim).
Nota che ci sono due studenti con lo stesso nome: Jim (cella A2 e A5).
Poiché una ricerca normale non funzionerebbe, è necessario eseguire una ricerca con distinzione tra maiuscole e minuscole.
Di seguito è riportata la formula che ti darà il giusto risultato. Poiché si tratta di una formula di matrice, è necessario utilizzare Ctrl + Maiusc + Invio.
=INDICE($B$2:$B$11,CONFRONTA(VERO,ESATTO(D3,A2:A11),0),1)
Lascia che ti spieghi come funziona questa formula.
La funzione EXACT verifica la corrispondenza esatta del valore di ricerca (che in questo caso è "jim"). Passa attraverso tutti i nomi e restituisce FALSE se non è una corrispondenza e TRUE se è una corrispondenza.
Quindi l'output della funzione ESATTA in questo esempio è - {FALSO; FALSO; FALSO; VERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}
Nota che c'è solo un TRUE, che è quando la funzione EXACT ha trovato una corrispondenza perfetta.
La funzione MATCH trova quindi la posizione di TRUE nell'array restituito dalla funzione EXACT, che in questo esempio è 4.
Una volta che abbiamo la posizione, la funzione INDEX la usa per trovare i segni.
Esempio 8: trova la corrispondenza più vicina
Andiamo un po' avanti ora.
Supponiamo di avere un set di dati in cui si desidera trovare la persona che ha l'esperienza lavorativa più vicina all'esperienza richiesta (menzionata nella cella D2).
Sebbene le formule di ricerca non siano fatte per farlo, puoi combinarlo con altre funzioni (come MIN e ABS) per farlo.
Di seguito la formula che troverà la persona con l'esperienza più vicina a quella richiesta e restituirà il nome della persona. Nota che l'esperienza deve essere la più vicina (che può essere minore o maggiore).
=INDICE($A$2:$A$15,CONFRONTA(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Poiché si tratta di una formula di matrice, è necessario utilizzare Ctrl + Maiusc + Invio.
Il trucco in questa formula consiste nel modificare il valore di ricerca e l'array di ricerca per trovare la differenza minima di esperienza nei valori richiesti e effettivi.
Prima di spiegare la formula, capiamo come lo faresti manualmente.
Passerai attraverso ogni cella nella colonna B e troverai la differenza nell'esperienza tra ciò che è richiesto e quello che ha una persona. Una volta che hai tutte le differenze, troverai quella minima e prendi il nome di quella persona.
Questo è esattamente ciò che stiamo facendo con questa formula.
Lasciatemi spiegare.
Il valore di ricerca nella formula CONFRONTA è MIN(ABS(D2-B2:B15)).
Questa parte ti dà la differenza minima tra l'esperienza data (che è 2,5 anni) e tutte le altre esperienze. In questo esempio, restituisce 0.3
Nota che ho usato l'ABS per assicurarmi di cercare il più vicino (che può essere più o meno dell'esperienza data).
Ora, questo valore minimo diventa il nostro valore di ricerca.
L'array di ricerca nella funzione MATCH è ABS(D2-$B$2:$B$15).
Questo ci dà una serie di numeri da cui è stato sottratto 2,5 (l'esperienza richiesta).
Quindi ora abbiamo un valore di ricerca (0,3) e un array di ricerca ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})
La funzione CONFRONTA trova la posizione di 0.3 in questa matrice, che è anche la posizione del nome della persona che ha l'esperienza più vicina.
Questo numero di posizione viene quindi utilizzato dalla funzione INDICE per restituire il nome della persona.
Leggi correlate: trova la corrispondenza più vicina in Excel (esempi utilizzando le formule di ricerca)
Clicca qui per scaricare il file di esempio
Esempio 9: utilizzare INDEX MATCH con caratteri jolly
Se vuoi cercare un valore quando c'è una corrispondenza parziale, devi usare i caratteri jolly.
Ad esempio, di seguito è riportato un set di dati del nome dell'azienda e delle relative capitalizzazioni di mercato e si desidera ottenere la capitalizzazione di mercato. dati per le tre società sulla destra.
Poiché queste non sono corrispondenze esatte, in questo caso non puoi eseguire una ricerca regolare.
Ma puoi ancora ottenere i dati giusti usando un asterisco (*), che è un carattere jolly.
Di seguito è riportata la formula che ti fornirà i dati abbinando i nomi delle società dalla colonna principale e recuperando la cifra della capitalizzazione di mercato per essa.
=INDICE($B$2:$B$10,CONFRONTA(D2&”*”,$A$2:$A$10,0),1)
Lascia che ti spieghi come funziona questa formula.
Poiché non esiste una corrispondenza esatta dei valori di ricerca, ho usato D2&”*” come valore di ricerca nella funzione CONFRONTA.
Un asterisco è un carattere jolly che rappresenta un numero qualsiasi di caratteri. Ciò significa che Apple* nella formula significherebbe qualsiasi stringa di testo che inizia con la parola Apple e può avere un numero qualsiasi di caratteri dopo di essa.
Cosi quando Mela* viene utilizzato come valore di ricerca e la formula CONFRONTA lo cerca nella colonna A, restituisce la posizione di "Apple Inc.", poiché inizia con la parola Apple.
È inoltre possibile utilizzare caratteri jolly per trovare stringhe di testo in cui si trova il valore di ricerca. Ad esempio, se usi *Apple* come valore di ricerca, troverà qualsiasi stringa che contenga la parola mela ovunque al suo interno.
Nota: questa tecnica funziona bene quando hai solo un'istanza di corrispondenza. Ma se hai più istanze di corrispondenza (ad esempio Apple Inc e Apple Corporation, la funzione CONFRONTA restituirà solo la posizione della prima istanza di corrispondenza.
Esempio 10: ricerca a tre vie
Questo è un uso avanzato di INDEX MATCH, ma lo tratterò comunque per mostrarti la potenza di questa combinazione.
Ricorda che ho detto che la funzione INDICE ha due sintassi:
=INDICE (matrice, num_riga, [num_col]) =INDICE (matrice, num_riga, [num_col], [num_area])
Finora in tutti i nostri esempi abbiamo utilizzato solo il primo.
Ma per una ricerca a tre vie, è necessario utilizzare la seconda sintassi.
Lasciatemi prima spiegare cosa significa uno sguardo a tre vie.
In una ricerca bidirezionale, usiamo la formula INDEX MATCH per ottenere i voti quando abbiamo il nome dello studente e il nome della materia. Ad esempio, recuperare i voti di Jim in Math è una ricerca bidirezionale.
Uno sguardo a tre vie aggiungerebbe un'altra dimensione. Ad esempio, supponiamo di disporre di un set di dati come mostrato di seguito e di voler conoscere il punteggio di Jim in Math nell'esame di metà semestre, questa sarebbe una ricerca a tre vie.
Di seguito è la formula che darà il risultato.
=INDICE(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),CONFRONTA($F$5,$A$3:$A$7,0),CONFRONTA(V$4 ,$B$2:$D$2,0),(IF(G$3="Unit Test",1,IF(G$3="Mid Term",2,3))))
La formula sopra ha verificato tre cose: il nome dello studente, la materia e l'esame. Dopo aver trovato il valore corretto, lo restituisce nella cella.
Lascia che ti spieghi come funziona questa formula scomponendo la formula in parti.
- array - ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23): invece di utilizzare un singolo array, in questo caso, ho utilizzato tre array tra parentesi.
- num_riga - CONFRONTA($F$5,$A$3:$A$7,0): La funzione CONFRONTA viene utilizzata per trovare la posizione del nome dello studente nella cella $F$5 nell'elenco del nome dello studente.
- col_num - CONFRONTA(G$4,$B$2:$D$2,0): la funzione CONFRONTA viene utilizzata per trovare la posizione del nome del soggetto nella cella $B$2 nell'elenco dei nomi del soggetto.
- [area_num] - SE(V$3=”Unit Test”,1,SE(V$3=”Mid Term”,2,3)): Il valore del numero dell'area indica alla funzione INDEX quale dei tre array utilizzare per recuperare il valore. Se l'esame è Unit Term, la funzione SE restituirà 1 e la funzione INDICE utilizzerà il primo array per recuperare il valore. Se l'esame è intermedio, la formula IF restituisce 2, altrimenti restituisce 3.
Questo è un esempio avanzato di utilizzo di INDEX MATCH ed è improbabile che tu trovi una situazione in cui devi usarlo. Ma è comunque utile sapere cosa possono fare le formule di Excel.
Clicca qui per scaricare il file di esempio
Perché INDEX/MATCH è migliore di CERCA.VERT?
O è?
Sì, lo è - nella maggior parte dei casi.
Presenterò il mio caso tra un po'.
Ma prima di farlo, lasciami dire questo… CERCA.VERT è una funzione estremamente utile e la adoro. Può fare molte cose in Excel e lo uso ogni tanto da solo. Detto questo, non significa che non ci possa essere niente di meglio, e INDEX/MATCH (con più flessibilità e funzionalità) è meglio.
Quindi, se vuoi fare una ricerca di base, è meglio usare VLOOKUP.
INDEX/MATCH è VLOOKUP su steroidi. E una volta che impari INDEX/MATCH, potresti sempre preferire usarlo (soprattutto per la flessibilità che ha).
Senza allungarlo troppo, lascia che ti dia rapidamente i motivi per cui INDEX/MATCH è migliore di VLOOKUP.
INDEX/MATCH può guardare a sinistra (oltre che a destra) del valore di ricerca
L'ho coperto in uno degli esempi sopra.
Se hai un valore che si trova a sinistra del valore di ricerca, non puoi farlo con CERCA.VERT
Almeno non solo con VLOOKUP.
Sì, puoi combinare CERCA.VERT con altre formule e farlo, ma diventa complicato e disordinato.
INDEX/MATCH, d'altra parte, è fatto per cercare ovunque (sia a sinistra, a destra, in alto o in basso)
INDEX/MATCH può funzionare con intervalli verticali e orizzontali
Ancora una volta, nel pieno rispetto di VLOOKUP, non è fatto per farlo.
Dopotutto, la V in VLOOKUP sta per verticale.
CERCA.VERT può passare solo attraverso i dati che sono verticali, mentre INDEX/MATCH può passare attraverso i dati verticalmente e orizzontalmente.
Naturalmente, c'è la funzione CERCA.VERT per occuparsi della ricerca orizzontale, ma non è CERCA.VERT allora… giusto?
Mi piace il fatto che la combinazione INDEX MATCH sia abbastanza flessibile da funzionare con dati sia verticali che orizzontali.
CERCA.VERT non può funzionare con dati decrescenti
Quando si tratta della corrispondenza approssimativa, CERCA.VERT e INDICE/MATCH sono allo stesso livello.
Ma INDEX MATCH prende il punto in quanto può anche gestire dati in ordine decrescente.
Lo mostro in uno degli esempi in questo tutorial in cui dobbiamo trovare il voto degli studenti in base alla tabella di valutazione. Se la tabella è ordinata in ordine decrescente, CERCA.VERT non funzionerebbe (ma INDEX MATCH funzionerebbe).
INDEX/MATCH può essere leggermente più veloce
sarò sincero. Non ho eseguito personalmente questo test.
Mi affido alla saggezza di un maestro di Excel: Charley Kyd.
La differenza di velocità in VLOOKUP e INDEX/MATCH è appena percettibile quando si dispone di piccoli set di dati. Ma se hai migliaia di righe e molte colonne, questo può essere un fattore decisivo.
Nel suo articolo, Charley Kyd afferma:
“Nel peggiore dei casi, il metodo INDEX-MATCH è veloce quanto VLOOKUP; al suo meglio, è molto più veloce."
INDEX/MATCH è indipendente dalla posizione effettiva della colonna
Se hai un set di dati come mostrato di seguito mentre stai recuperando il punteggio di Jim in Fisica, puoi farlo usando CERCA.VERT.
E per farlo, puoi specificare il numero di colonna come 3 in CERCA.VERT.
Va tutto bene.
Ma cosa succede se elimino la colonna Math.
In tal caso, la formula CERCA.VERT verrà interrotta.
Come mai? - Perché è stato codificato per utilizzare la terza colonna e quando elimino una colonna in mezzo, la terza colonna diventa la seconda colonna.
Usare INDEX/MATCH, in questo caso, è meglio in quanto puoi rendere dinamico il numero di colonna usando MATCH. Quindi, invece di un numero di colonna, controlla il nome del soggetto e lo usa per restituire il numero di colonna.
Sicuramente puoi farlo combinando VLOOKUP con MATCH, ma se combini comunque, perché non farlo con INDEX che è molto più flessibile.
Quando si utilizza INDEX/MATCH, è possibile inserire/eliminare in sicurezza colonne nel set di dati.
Nonostante tutti questi fattori, c'è un motivo per cui CERCA.VERT è così popolare.
Ed è un grande motivo.
CERCA.VERT è più facile da usare
CERCA.VERT accetta solo un massimo di quattro argomenti. Se riesci a avvolgere la testa intorno a questi quattro, sei a posto.
E poiché la maggior parte dei casi di ricerca di base sono gestiti anche da CERCA.VERT, è diventata rapidamente la funzione di Excel più popolare.
Lo chiamo le funzioni King of Excel.
INDEX/MATCH, d'altra parte, è un po' più difficile da usare. Potresti avere un blocco se inizi a usarlo, ma per un principiante, VLOOKUP è molto più facile da spiegare e imparare.
E questo non è un gioco a somma zero.
Quindi, se sei nuovo nel mondo della ricerca e non sai come usare CERCA.VERT, è meglio impararlo.
Ho una guida dettagliata sull'utilizzo di CERCA.VERT in Excel (con molti esempi)
Il mio intento in questo articolo non è quello di lanciare due fantastiche funzioni l'una contro l'altra. Volevo mostrarti la potenza della combo INDEX MATCH e tutte le grandi cose che può fare.
Spero che tu abbia trovato utile questo articolo.
Fammi sapere i tuoi pensieri nella sezione commenti e, nel caso in cui trovi errori in questo tutorial, per favore fammi sapere.
