Qualche anno fa dovevo verificare le giacenze di magazzino di qualche migliaio di prodotti per un’azienda cliente.
Gli articoli e le informazioni sui prodotti erano in una tabella Excel, le quantità teoriche in un altro file. La conta fisica delle rimanenze che avevo stoicamente portato a termine dopo quasi 6 ore di sporco lavoro (contavo tonnellate di legname in un magazzino sudicio, sporco davvero) l’avevo riportata in un ulteriore file.
Contento del mio lavoro mi apprestavo a mettere insieme questi dati.
Fu lì che conobbi la funzione CERCA.VERT di Excel (VLOOKUP per chi la usa in inglese).
Fu amore a prima vista.
In 3 minuti misi in fila tutto ciò che mi serviva a partire da una informazione univoca: il codice prodotto.
La funzione CERCA.VERT (VLOOKUP) è in Excel fin dalla sua nascita; era presente addirittura in Excel 1.0 nel lontano 1985. Stiamo parlando della funzione più utilizzata al mondo dopo SOMMA (SUM) e MEDIA (AVERAGE).
Sono qui a raccontarvi una storia che sta per finire.
Sì, perché il CERCA VERT sta per essere soppiantato da Microsoft con l’arrivo della nuovissima funzione CERCA.X
Prima che ti accasci a terra per il dolore, sappi che CERCA.VERT e CERCA.ORIZZ saranno ancora utilizzabili in futuro e pienamente supportate.
Indice del post
I limiti del CERCA.VETT
Il cerca verticale è una funzione Excel che restituisce un valore obiettivo (output) a partire da uno dato (input). Quella volta del magazzino di legname, ho dato in pasto al CERCA.VERT ciascun codice prodotto (input) presente in una tabella, chiedendogli di restituirmi il numero di pezzi in giacenza presenti in un’altra tabella.
Puoi trovare guide sul cerca verticale in ogni dove sul web, ti consiglio quella ufficiale di Microsoft.
Qui trovi anche un breve video che la spiega in italiano.
CERCA.VERT ha sempre avuto però dei limiti con cui ci siamo dovuti confrontare (non sempre serenamente!):
1. Non può cercare verso sinistra
Utilizzando la funzione CERCA.VERT cerchiamo all’interno di una tabella il valore-obiettivo (output) dovendo specificare ad Excel “quante colonne a destra” si trova rispetto al valore di partenza (input).
Se nella tabella il valore che cerchiamo è a sinistra rispetto a quello di partenza, il CERCA.VERT non funziona e siamo costretti a rimettere mano alla composizione e struttura della tabella.
2. CERCA.VERT esegue ricerche approssimate se non specifichiamo il quarto argomento facoltativo della funzione
Molti di noi hanno conosciuto la sintassi della funzione imparando a memoria che nel quarto argomento “devi mettere FALSO”. In pochi hanno scoperto il perché… 😊
Il quarto argomento, se non specificato, è VERO di default: quel VERO sta lì ad indicare ad Excel “trova una corrispondenza approssimativa”, non esatta. Se invece specifichiamo FALSO, CERCA.VERT funzionerà come ci aspettiamo, restituendo un errore se non trova il valore esatto.
Considerato che il valore FALSO nel quarto argomento della funzione è lo standard nel 90% delle ricerche, avere un VERO come default e costringerci a specificare il suo opposto… è decisamente molto macchinoso
3. CERCA.VERT è sensibile agli inserimenti ed eliminazioni di colonne
Se si elimina o si inserisce una colonna nella tabella in cui stiamo cercando il valore obiettivo con un VERT già funzionante, questo perderà i suoi riferimenti restituendo un errore.
Inserire una colonna o eliminarla ha cambiato la numerosità delle colonne nella tabella di ricerca e di conseguenza anche il numero della colonna verso destra in cui la funzione si aspettava di trovare il valore cercato.
4. Il Cerca Verticale trova soltanto il primo valore dall’alto
Se il valore di partenza (input) non è univoco nella tabella in cui andremo a cercare, il CERCA.VERT si fermerà al primo valore trovato scorrendo la tabella dall’alto verso il basso.
È vero che utilizzare un CERCA.VERT in una tabella attraverso un dato non univoco non è il massimo della vita ma in alcuni casi avremmo trovato un aiuto nel poter cercare dal basso all’alto. Questo non è possibile con il cerca verticale.
Per sanare questi limiti ognuno ha messo in atto le proprie strategie, arrovellandosi il cervello alla ricerca di una soluzione definitiva che in realtà definitiva non era.
La migliore alternativa è da sempre stata costituita dalla combinazione di due funzioni: la funzione INDICE e la funzione CONFRONTA. Microsoft ha anche rilasciato una guida rispetto a questa soluzione.
Con l’arrivo di CERCA X (la sintassi corretta sarà CERCA.X mentre per le versioni inglesi XLOOKUP) non avremo più nessuno degli inconvenienti elencati sopra.
CERCA.X è la nuova funzionalità di Excel che soppianterà (non eliminandole!) CERCA.VERT e CERCA.ORIZZ
L’ho provata in anteprima in qualità di Excel Insider e di seguito ve ne presento una guida all’uso.
Se siete utenti Office 365 fra qualche settimana (mese?) l’avrete anche voi attraverso un aggiornamento. Iniziate intanto a familiarizzare con la funzione, è una vera bomba!
Come funziona il CERCA X
La funzione CERCA.X può essere usata per trovare elementi in una tabella o in un intervallo per riga.
CERCA.X semplifica enormemente le cose rispetto al vecchio CERCA.VERT: con questa nuova funzione è possibile cercare un termine di ricerca in una colonna e ottenere un risultato nella stessa riga ma in un’altra colonna, indipendentemente dal lato in cui si trova la colonna del risultato.
Conosciamone la sintassi, gli argomenti obbligatori sono 3:
=CERCA.X(valore;matrice_ricerca;matrice_restituita)
Che possiamo leggere così:
=CERCA.X(valore da cui partire; celle in cui trovarlo; celle dalle quali voglio il mio valore obiettivo)
Come se dicessimo ad Excel: “Cerca questo valore, in questo intervallo di celle e, quando l’hai trovato, spostati nella stessa riga di quest’altro intervallo e dammi il valore che trovi”
Nell’esempio sopra vengono cercate informazioni sui prodotti in base al loro codice.
In particolare, CERCA.X parte con il valore “HJN-41676” [argomento 1], scorre le celle da C8 a C17 [argomento 2] trovando “HJN-41676” in cella C11, si sposta poi nelle celle da A8 a A17 e restituisce il valore di A11 (che è la stessa riga di C11 dove aveva trovato il valore originario).
In questo esempio si può vedere come CERCA.X superi uno dei limiti del CERCA.VERT, ovvero la possibilità di cercare verso sinistra.
Ecco un breve video prodotto da Microsoft che ci spiega il funzionamento di CERCA.X (XLOOKUP):
I due argomenti facoltativi
Ai tre argomenti obbligatori appena visti se ne aggiungono due facoltativi che potranno decisamente tornarci utili:
- Argomento 4: Tipo di corrispondenza
- Argomento 5: Tipo di ricerca
Con tutti i suoi argomenti presenti CERCA.X si presenterà quindi così:
=CERCA.X(valore; matrice_ricerca; matrice_restituita; tipo_di_corrispondenza; tipo_di_ricerca)
L’argomento 4 “Tipo di corrispondenza” può assumere quattro tipologie di valori:
- 0 (che è l’impostazione di default, sempre attiva se l’argomento non è specificato) con la quale si chiede ad Excel una ricerca esatta e puntuale del primo argomento nel secondo.
Stiamo dicendo ad Excel “Cerca esattamente questo valore in queste celle, se non lo trovi, lascia perdere”. Se non trova esatta corrispondenza, restituirà un errore - -1 Anche in questo caso Excel cercherà una corrispondenza esatta. A differenza di prima in questo caso chiediamo ad Excel in seconda battuta di trovare il primo valore più piccolo.
Gli stiamo dicendo “Cerca esattamente questo valore in queste celle, se non lo trovi, accontentati del simile più piccolo” - 2 Anche in questo caso Excel cercherà una corrispondenza esatta. A differenza di prima in questo caso chiediamo ad Excel in seconda battuta di trovare il primo valore più grande.
Gli stiamo dicendo “Cerca esattamente questo valore in queste celle, se non lo trovi, accontentati del simile più grande” - Caratteri jolly speciali (non di interesse per questa guida, puoi approfondire qui)
Nota anche qui la differenza con il vecchio CERCA.VERT in cui il valore di default del quarto argomento facoltativo era “VERO” (o 1), obbligandoci ad inserire manualmente “FALSO” (o 0) ogni volta (cioè sempre!) che cercavamo corrispondenza esatta.
Con il nuovo CERCA.X questo non sarà più necessario!
L’argomento 5 “Tipo di ricerca” può assumere quattro tipologie di valori:
- 1 (che è l’impostazione di default, sempre attiva se l’argomento non è specificato). La ricerca del valore di partenza [argomento 1] nell’intervallo celle in cui cercarlo [argomento 2] avverrà dall’alto verso il basso
- -1 La ricerca del valore di partenza [argomento 1] nell’intervallo celle in cui cercarlo [argomento 2] avverrà dal basso verso l’alto
- 2 e -2 (ricerche binarie su colonne classificate, non di interesse per questa guida)
CERCA X: esempi pratici di utilizzo
Cercare il CAP di una città in una lista:
In questo esempio si usa una semplice funzione CERCA.X per cercare una città inserita in cella E4 e restituire il suo CAP.
Include solo gli argomenti valore (cella E4), matrice_ricerca (intervallo A2:B11) e matrice_restituita (intervallo B2:B11). Non ho bisogno degli argomenti facoltativi in quanto cerco una corrispondenza esatta nella ricerca (“Udine” = “Udine”), l’approssimazione non mi è utile.
Trovare l’aliquota % corretta in base a scaglioni di reddito:
Qui invece cerchiamo nella colonna B il reddito immesso nella cella D4 e viene trovata un’aliquota di imposta corrispondente nella colonna A. L’argomento facoltativo “modalità_ricerca” è impostato su 1, quindi la funzione cerca una corrispondenza esatta e, se non riesce a trovarne una, restituirà l’elemento successivo più grande (38%).
