Corso
Introduzione alla statistica in Google Sheets
4 ore
48.3K
Di solito, quando si impara a usare Excel per la prima volta, è probabile che VLOOKUP venga presentata come il metodo principale per fare data wrangling. VLOOKUP è infatti una funzione potente che può gestire molte attività legate alla manipolazione dei dati, come illustrato in Data Wrangling with VLOOKUP in Spreadsheets. Nello stesso tutorial, abbiamo anche trattato le basi della combinazione INDEX-MATCH, un altro metodo per effettuare ricerche verticali nelle tabelle che offre diversi vantaggi rispetto a VLOOKUP, al prezzo di una formula più complicata.
Questi vantaggi includono funzionalità come:
- Riferimenti di colonna dinamici: ti permettono di inserire ed eliminare colonne dal dataset senza influenzare la funzione di ricerca.
- Maggiore velocità di elaborazione su dataset ampi: se le tue tabelle sono estese, noterai probabilmente che INDEX-MATCH è molto più veloce di VLOOKUP perché INDEX-MATCH si concentra solo sulla colonna da recuperare e sulla colonna di ricerca.
- Posizione dei valori di ricerca: VLOOKUP non può cercare valori situati a sinistra della prima colonna selezionata nell'intervallo di tabella, mentre INDEX-MATCH può guardare anche orizzontalmente, quindi non ha questa limitazione.
- Dimensione limitata del valore di ricerca: VLOOKUP non può cercare valori più lunghi di 255 caratteri, ma INDEX-MATCH sì.
Inoltre, INDEX-MATCH permette anche l'abbinamento basato sul valore di più colonne e l'abbinamento case-sensitive, molto utile quando devi gestire certi ID che possono differire per lettere maiuscole e minuscole. Ecco perché questo tutorial punta ad approfondire le funzionalità avanzate della combinazione INDEX-MATCH e a mostrare esempi di come queste funzionalità possano tornare utili.
Le basi di INDEX-MATCH
Nella sua forma più semplice, INDEX-MATCH può essere usata allo stesso modo di VLOOKUP, per eseguire semplici ricerche verticali in tabella basate su una chiave comune. La struttura di base della formula è la seguente:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: l'intervallo della colonna di cui vuoi recuperare il valore.
- lookup_value: il valore che vuoi cercare (la chiave).
- lookup_column_range: l'intervallo della colonna che contiene le chiavi che vuoi abbinare.
- match_type: 0 o 1. 0 indica una corrispondenza esatta e 1 indica una corrispondenza approssimata. È analogo al flag FALSE/TRUE in VLOOKUP.
Vediamo ora come funziona INDEX-MATCH usando un paio di tabelle di esempio. La prima tabella contiene alcuni nomi e alcune chiavi uniche. La seconda tabella contiene le stesse chiavi (non necessariamente nello stesso ordine) e alcune descrizioni. Il compito è quindi unire le descrizioni della seconda tabella con i nomi nella prima tabella usando INDEX-MATCH. Le tabelle sono qui sotto:
 |
 |
Come puoi vedere, alla prima tabella manca la descrizione. Procediamo ora a unire il nome con la sua descrizione usando INDEX-MATCH:
Semplice, vero? Come vedi nel video, il primo passo è selezionare un column_range che contenga i dati che vogliamo recuperare dentro la funzione INDEX. In questo caso è Description (M2:M8). Il secondo passo è selezionare il lookup_value dentro la funzione MATCH, cioè la cella B2. Infine, aggiungiamo il lookup_column_range (L2:L8) dentro la funzione MATCH e selezioniamo 0 come match_type perché vogliamo una corrispondenza esatta del lookup_value. Il resto è premere INVIO e trascinare la formula verso il basso per unire finalmente il nome con le descrizioni.
INDEX-MATCH può anche essere usata per unire tabelle su fogli separati, proprio come VLOOKUP. Un video di esempio è qui sotto:
Nota: avrai notato che gli intervalli di colonna che seleziono nei video sono spesso circondati da simboli $. Questo serve a selezionare intervalli fissi che non vengono alterati quando trascini le formule in verticale o in orizzontale. Continuerò a usare questa notazione in tutto il tutorial.
Finora tutto questo si comporta molto come un semplice VLOOKUP, ma con un po' più di lavoro. Tuttavia, come accennato all'inizio, ci sono vari vantaggi se scegli la strada di INDEX-MATCH. Uno dei più utili è rappresentato dai riferimenti di colonna dinamici: ti permettono di aggiungere colonne all'intervallo di tabella in cui effettui la ricerca senza influenzare la ricerca stessa.
Se aggiungessimo una nuova colonna nel nostro esempio e stessimo usando VLOOKUP, dovremmo riscrivere la formula nella tabella di sinistra per fare riferimento alla descrizione, che diventerebbe la colonna 3. Se invece usi INDEX-MATCH, che ha riferimenti di colonna dinamici, questo non è necessario. Il confronto è mostrato nel video qui sotto:
L'esempio sopra illustra come la colonna che eseguiva la ricerca con INDEX-MATCH si sia adattata quando è stata aggiunta la nuova colonna con i tipi di astronave, mentre la colonna con VLOOKUP no. Questo vantaggio di INDEX-MATCH su VLOOKUP è particolarmente evidente in attività che coinvolgono ricerche in grandi tabelle di Excel, come un report delle vendite aziendali. In altre parole, immagina di avere diverse dashboard aziendali costruite in Excel che fanno tutte riferimento a un'unica tabella con tutti i dati. Se usi VLOOKUP e un giorno diventa necessario inserire una colonna in mezzo per aggiungere un'altra caratteristica, dovresti modificare le chiamate a VLOOKUP in tutte le dashboard che dipendono da quella tabella. Se invece hai costruito le dashboard usando INDEX-MATCH come metodo di lookup, non dovrai affrontare questo problema.
INDEX-MATCH avanzato: corrispondenza case-sensitive
I riferimenti di colonna dinamici sono molto utili, ma non sono l'unico vantaggio offerto da INDEX-MATCH. Ad esempio, è possibile potenziare la sintassi base di INDEX-MATCH per eseguire ricerche case-sensitive. In particolare, aggiungeremo una funzione EXACT dentro la funzione MATCH per ottenere questo risultato. La sintassi dettagliata è la seguente:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: l'intervallo di tabella di interesse che include la colonna di ricerca e la colonna desiderata da recuperare.
- lookup_value: il valore che vuoi cercare (la chiave)
- lookup_column_range: l'intervallo della colonna che contiene le chiavi da abbinare
- match_type: 0 o 1. 0 indica una corrispondenza esatta e 1 indica una corrispondenza approssimata. È analogo al flag FALSE/TRUE in VLOOKUP
- desired_column_number: uguale al numero dell'indice di colonna in VLOOKUP. È l'indice della colonna che contiene i dati che vuoi ottenere.
La corrispondenza case-sensitive con questa variante di INDEX-MATCH può essere molto utile quando si abbinano chiavi che contengono lettere e che si distinguono per il loro caso (ad esempio se hai due chiavi diverse, una è "00567UUp" e l'altra è "00567UUP"). Queste situazioni si possono incontrare, per esempio, all'interno del diffuso CRM Salesforce quando si cerca di abbinare gli ID degli Account o gli ID delle Opportunità di vendita. Per ora, però, non useremo dataset Salesforce, ma una versione modificata delle nostre tabelle di esempio nel video qui sotto per illustrare come funziona in pratica la formula sopra.
Nota: la combinazione di funzioni sopra è racchiusa tra parentesi graffe. Questo significa che è una formula matrice e deve essere inserita premendo Ctrl+Shift+Invio.
Come puoi vedere, la sintassi di questa formula può essere lunga da scrivere, ma i risultati sono proprio quelli attesi. Cerca i valori come una normale INDEX-MATCH, ma tenendo conto del caso dei caratteri. La "magia" qui è fatta dalla funzione EXACT, che restituisce una matrice di TRUE e FALSE. Ovunque ci sia TRUE in questa matrice, significa che esiste un valore con lo stesso caso esatto. Dopodiché, la funzione MATCH si occupa di ricavare la posizione della prima occorrenza di TRUE.
Tieni presente che è fondamentale premere Ctrl+Shift+Invio quando termini di scrivere la formula, altrimenti otterrai un errore #N/A che può essere difficile da diagnosticare.
INDEX-MATCH avanzato: corrispondenza basata su più criteri
Le ricerche case-sensitive in Excel sono piuttosto interessanti, ma c'è una funzionalità ancora migliore: le ricerche che tengono conto di più criteri. Considerare numerosi criteri è qualcosa che si fa comunemente con funzioni condizionali di conteggio o somma come COUNTIFS o SUMIFS, ma è più raro vederlo come parte delle funzioni di lookup in Excel. Tuttavia, può essere vantaggioso in certe situazioni. Per esempio, potrebbe essere il modo giusto se, diciamo, hai un file HR in Excel e vuoi cercare lo stipendio di un dipendente in base al suo nome E cognome. Se consideri solo una delle due colonne, è possibile ottenere lo stipendio della persona sbagliata, dato che possono esserci molti con lo stesso nome o cognome. È invece molto più probabile individuare la persona corretta se recuperi il valore in base sia al nome sia al cognome.
Detto ciò, vediamo ora la struttura di INDEX-MATCH che tiene conto di più caratteristiche:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
Per la maggior parte, la formula ha gli stessi identici componenti della normale INDEX-MATCH, ma ora può considerare $n$ valori di ricerca con il rispettivo numero di intervalli di ricerca. Se ti serve un ripasso di cosa siano questi componenti, puoi tornare alla sintassi base di INDEX-MATCH. Il motivo per cui abbiamo un "1" nella funzione MATCH è che questa formula crea matrici di 1 e 0 che rappresentano le corrispondenze di ciascuno dei criteri desiderati. Usa poi la funzione MATCH per recuperare la posizione del primo 1 trovato.
Come per la formula case-sensitive, anche questa è racchiusa tra parentesi graffe, il che significa che devi premere Ctrl+Shift+Invio quando finisci di scriverla, poiché è una formula matrice.
Vediamo un esempio di come funziona questa formula provando a selezionare astronavi che appartengono a una certa razza e che hanno una certa velocità massima di warp nel video qui sotto:
Voilà! Ora hai gli strumenti per abbinare argomenti basandoti su più chiavi. Tieni presente che in questo piccolo esempio ho usato solo due criteri, la chiave e la velocità massima di warp, ma in teoria potresti continuare ad aggiungerne altri se lo desideri. Tornando all'esempio HR, potresti abbinare in base a nome e cognome, oltre al titolo di lavoro, per ridurre al minimo la possibilità di ottenere lo stipendio del dipendente sbagliato.
Conclusione
Congratulazioni! Ora conosci INDEX-MATCH e la varietà di cose che puoi farci oltre una semplice ricerca verticale. Se a un certo punto ti viene affidata la gestione di report Excel complessi, questa combinazione di funzioni probabilmente diventerà la tua migliore amica. Come molti, a scuola ho imparato VLOOKUP, ma quando ho iniziato a usare seriamente Excel per attività come le ricerche case-sensitive degli ID degli account Salesforce, ho capito che INDEX-MATCH era un'opzione molto migliore nel lungo periodo, nonostante la seccatura e la propensione all'errore nello scrivere la formula. Ti invito a provarla la prossima volta che dovrai fare una ricerca verticale nei tuoi lavori in Excel. Chissà, magari ti piacerà quanto è piaciuta a me.
Se hai voglia di imparare ancora di più, dai un'occhiata al corso di DataCamp Data Analysis with Spreadsheets Course.
Argomenti
