Quando su Excel lavoriamo su più tabelle o basi dati, è inevitabile aver bisogno di individuare ed estrarre un valore specifico o i valori di un campo di una tabella. A questo scopo esistono le funzioni di ricerca e tra queste la più nota è cerca verticale. In questa guida vediamo come si usa al meglio la funzione Cerca.Vert() per unire tabelle, estrarre valori da visualizzare e altro ancora.
Le funzioni di ricerca di Excel
Nel post “Le funzioni di ricerca” abbiamo visto le funzioni di ricerca di Excel, strumenti che ci permettono di individuare valori in una tabella o matrice di dati, per poi visualizzarli in un’altra posizione, in un’altra tabella o elaborarli in una formula.
Le formule di ricerca permettono per esempio di:
- individuare un singolo valore specifico all’interno di una tabella,
- individuare il valore più prossimo a quello ricercato,
- aggiungere a una tabella un campo di un’altra tabella,
- unire due tabelle.
Queste funzioni sono notevoli strumenti che una volta impostate continuano a “lavorare” in modo dinamico, a meno di modifiche “strutturali” o nominative della tabella a cui sono collegate.
Tra queste funzioni, la più nota e utilizzata è Cerca Verticale, un notevole strumento non così complesso come appare, che offre più possibilità di quello che si pensa, ma possiede anche diversi limiti e lacune. Questa guida vuole mostrarti tutto questo che devi sapere su questo strumento per ottenerne il meglio per il tuo lavoro.
La sintassi di Cerca Verticale
La sintassi è: CERCA.VERT(valore; matrice_tabella; indice; intervallo)
I parametri della funzione sono:
valore
È il valore da cercare e può essere di qualsiasi tipo (numero, stringa, ecc). Può essere scritto direttamente nella formula, ma di solito è ottenuto da un riferimento a una cella diversa, di solito un campo diverso dello stesso record che contiene il valore, tipicamente una chiave univoca come il codice cliente o il numero fattura.
Esempio =CERCA.VERT(“Ac8473”; matrice_tabella; indice; intervallo) cerca la stringa indicata, mentre =CERCA.VERT(B27; matrice_tabella; indice; intervallo) cerca il valore contenuto nella cella B27.
matrice_tabella
È una tabella di due o più colonne o campi di dati che nella prima colonna a sinistra contiene il valore di riferimento. Per esempio, =CERCA.VERT(B27; H2:N107; indice; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107.
indice
È il numero della colonna in matrice_tabella da cui la funzione restituisce il valore cercato. La colonna si conta da sinistra a destra da 1 all’indice. Per esempio, =CERCA.VERT(B27; H2:N107; 3; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.
intervallo
Indica se vuoi fare una ricerca a corrispondenza esatta (falso o 0) o di una corrispondenza approssimativa (Vero o 1 o omesso). Per esempio =CERCA.VERT(B27; H2:N107; 3; 0) cerca il valore esatto della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.
Il cerca verticale in sintesi
In sintesi la funzione Cerca.Vert() permette di cercare nella prima colonna di una tabella (matrice_tabella) il valore di riferimento per individuare il record in cui la funzione cerca e restituisce il valore presente nella ennesima (indice) colonna verso destra.
Il valore di riferimento è indicato come riferimento a una cella (es: A7) e la matrice come intervallo di celle (es: F2:R102) puntata su un’intera tabella di dati o una porzione di essa.
L’indice deve essere fornito come numero della colonna della matrice da sinistra a destra contando a partire dalla prima colonna, quindi il valore da ricercare/estrarre dalla tabella è sempre a destra della prima colonna in cui viene cercato il valore di riferimento.
Cerca verticale può cercare un valore preciso (intervallo = Falso o 0), oppure può cercare il valore più prossimo (intervallo = Vero o 1). Nel primo caso se la funzione non trova il valore restituire un errore (#N/D!), mentre nel secondo caso restituisce il primo valore inferiore se disponibile, altrimenti restituisce sempre un errore (#N/D!).
Esempio di cerca verticale a corrispondenza esatta
Sostanzialmente la funzione cerca un valore preciso nel primo campo della tabella indicata e restituisce il valore di un altro campo del record, per esempio: =CERCA.VERT(C2;TabellaCosti;3;Falso) cerca il valore esatto della cella C2 della pagina nella prima colonna dell’intervallo nominato come “TabellaCosti” e se lo trova restituisce il valore della terza colonna della matrice.
Altrimenti restituisce un errore che deve essere gestito. Gli errori devono essere sempre gestiti, soprattutto se viene usato in formule di elaborazione, altrimenti l’errore si propagherebbe ai risultati.
In caso di un valore non univoco, quindi di valori uguali presenti più volte nella prima colonna, verrà restituito il primo valore individuato dall’alto verso in basso, a meno che non siano consecutivi.
Esempio di Cerca verticale con corrispondenza approssimata
Come già detto Cerca.vert() può anche restituire un valore prossimo a quello ricercato, impostando il parametro intervallo a Vero oppure 1. Questo restituirà il primo valore inferiore, se presente, altrimenti restituisce un errore (#N/D!). La ricerca di valori prossimi è meno utilizzata rispetto ai valori precisi, è usata in situazioni particolari come in presenza di categorizzazioni, scale di valori, oppure per analisi esplorative o confronti.
Per esempio con =CERCA.VERT(B27; H2:N107; 3; 1) cerca il valore più prossimo a quello della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J, sulla stessa riga che contiene il valore di riferimento in H.
I riferimenti del cerca verticale
Di solito il cerca verticale viene utilizzato con riferimenti per il valore e la matrice. Il valore è tipicamente contenuto nella tabella in cui stiamo inserendo la formula con cerca.vert(), in un altro campo dello stesso record. Mentre di solito la tabella in cui cercare il valore di solito punta a una tabella in una pagina diversa.
Come si scrive un riferimento che punta a un’altra pagina?
Per impostare il nome della pagina nel riferimento è sufficiente scriverlo prima del riferimento e inserire un punto esclamativo (!) tra questo e il riferimento. Per esempio =CERCA.VERT(B27; Dati!H2:N107; 3; 0) cerca il valore esatto della cella B27 nella pagina Dati, nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J, sulla stessa riga che contiene il valore di riferimento in H.
In alternativa possiamo usare il classico sistema di puntamento sulla selezione, cioè mentre scriviamo la formula selezioniamo la pagina con la tabella e poi l’intervallo di celle della stessa.
Come si scrive un riferimento a un’altra pagina di un altro foglio?
Se vogliamo impostare il riferimento perfino a un’altra pagina di un altro foglio Excel, dobbiamo inserire il nome del foglio Excel tra parentesi quadra prima del nome della pagina.
Per esempio =CERCA.VERT(B27; [Fatturato.xlsx]Dati!H2:N107; 3; 0) cerca il valore esatto della cella B27 nella pagina Dati del foglio “Fatturato.xlsx”, nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.
I riferimenti se vogliamo copiare la formula
Infine è importante sottolineare che se vogliamo propagare la formula con cerca vert in una colonna di una tabella, cioè dall’alto verso il basso, è importante fissare il riferimento premendo F4 durante la digitazione per bloccare righe e colonne. Per esempio =CERCA.VERT(B27;$H$2:$N$107; 3; 0). In caso contrario quando copieremo la formula Excel adatterà i riferimenti modificando la matrice con conseguenti errori o alterazioni dei risultati.
In modo analogo se abbiamo bisogno di copiare la formula in altri campi o in celle a fianco, da sinistra a destra, allora è opportuno fissare il riferimento del parametro “valore”, premendo F4 durante la digitazione tante volte fino a fissare la colonna, quindi la lettera. Per esempio =CERCA.VERT($B27;$H$2:$N$107; 3; 0). In caso contrario quando copieremo la formula in un’altra colonna Excel adatterà i riferimenti spostando il puntamento a un’altra colonna e non più a quella con i valori da cercare, offrendo risultati errati.
Usare i nomi con il cerca verticale
In alternativa è consigliato usare i nomi di excel per impostare un nome che si riferisce all’intervallo di celle della tabella, o alla porzione che ci interessa. Per esempio =CERCA.VERT(C2;TabellaCosti;3;Falso), usa un nome “TabellaCosti” precedentemente impostato che si riferisce in modo assoluto all’intervallo di celle della matrice.
I nomi sono comodi, facili da ricordare e vengono proposti dagli strumenti di supporto durante la digitazione delle formule in modo intuitivo. E in effetti lo consiglio. Se vuoi approfondire l’argomento dei nomi di Excel, ti invito a leggere la guida relativa che trovi in questa pagina.
Usare i caratteri jolly nel cerca.vert
Anche cerca verticale, come molte altre formule possono usare i caratteri jolly, caratteri speciali che si riferiscono in modo generico a uno o più caratteri non specificati rendendo più flessibili i parametri di ricerca.
Cioè possiamo sostituire la stringa che stiamo cercando o parte di essa, con un carattere che indica a Excel di cercare qualunque carattere o sequenza di caratteri.
I caratteri jolly sono:
- punto interrogativo (?), che sostituisce qualunque singolo carattere,
- l’asterisco (*), che sostituisce qualunque sequenza di caratteri.
Qual è lo scopo principale dei caratteri jolly?
I caratteri jolly servono per cercare stringhe e valori alfanumerici di cui non ricordiamo o conosciamo o non vogliamo scrivere l’intera stringa.
Per esempio:
=CERCA.VERT(“ACR*”;H2:N107; 3; 0) cerca il primo valore che inizia con “ACR”;
=CERCA.VERT(“*NZ5?”;H2:N107; 3; 0) cerca il primo valore che contiene “NZ5” nel penultimo, terzultimo e quartultimo carattere;
=CERCA.VERT(“CN??????”;H2:N107; 3; 0) cerca il primo valore che inizia con “CN” ed è lungo 8 caratteri.
Nota sull’uso del cerca verticale con corrispondenza approssimata
Come abbiamo visto, se il quarto parametro del cerca verticale è impostato su “vero”, la formula cerca una corrispondenza approssimativa. O meglio, prima cerca il valore esatto e se lo trova restituisce quello, se invece non la trova cerca e restituisce il primo valore più grande inferiore al valore cercato.
Questo comporta che se i valori non sono ordinati preventivamente non è detto che la funzione cerca verticale restituisce il valore effettivamente più prossimo al valore vero. Ed è anche possibile che restituisca un errore se si cerca il valore più piccolo presente nel campo.
I limiti del Cerca Verticale
Come avrai già notato Cerca.Vert() ha diversi limiti:
- cerca il valore sempre nella prima colonna della matrice,
- è probabile che restituisca errori, che devono essere gestiti;
- ricerca un valore singolo solo nella prima colonna della matrice, restituendo il primo che trova;
- l’indicazione della colonna del risultato è un numero intero, non un riferimento a un nome di campo, quindi in caso di modifiche della tabella, per esempio aggiungendo campi nella tabella a sinistra del campo indicato dall’indice, la formula necessiterà di essere corretta.
In realtà questi limiti possono essere gestiti o perfino bypassati. Vediamo come.
Gestire il campo con il valore di riferimento
Il campo con il valore di riferimento deve essere sempre a sinistra del campo dei valori da restituire e dove non è già così è necessario intervenire sulla struttura della tabella. Per esempio selezionando l’intera colonna della tabella, tagliandola (Ctrl+X) e spostando il campo a inizio tabella o a sinistra del campo con i valori da restituire.
Naturalmente queste manipolazioni della tabella possono comportare problemi, in presenza di riferimenti e formule, o con strumenti non dinamici collegati. Sconsiglio di inserire campi “copia” della colonna di riferimento a inizio tabella a sinistra per il rischio di generare formule ricorsive.
Come gestire gli errori di cerca verticale
Una funzione cerca verticale è sempre accompagnata da una funzione Se.Errore(), oppure dalla coppia Se() e Val.Errore() proprio allo scopo di intercettare il potenziale errore che la funzione cerca verticale può restituire.
Per esempio in questo modo: =SE.ERRORE( CERCA.VERT( Ricerca;A2:S100;2;FALSO); “Non trovo il valore”), oppure =SE(VAL.ERRORE( CERCA.VERT(Parametri)); ”Non presente”;CERCA.VERT(Parametri)).
Come vedi se Cerca.Vert() non trova il valore e quindi restituisce un errore, questo viene sostituito con la stringa. Attenzione però, se il valore cercato è oggetto di elaborazione ed è numerico, la presenza di una stringa può generare errori. In tal caso è possibile sostituire la stringa con 0, 1 o valori nulli (“”) a seconda dei valori del campo e delle elaborazioni.
Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.
Cercare valori multipli con cerca vert
Come abbiamo visto la funzione cerca verticale ricerca un solo valore alla volta, ma questo limite può essere scavalcato a certe condizioni. Per esempio è possibile ricercare valori diversi di campi diversi concatenandoli in un unico campo iniziale. Oppure possiamo cercare valori alternativi usando altre funzioni come SE(), E() e O() in modo gerarchico.
In pratica se vogliamo cercare due valori in due campi diversi, dobbiamo procedere in questo modo:
- aggiungere una colonna alla tabella a sinistra di tutte le altre o del campo con il valore che vogliamo sia restituito,
- impostare nella nuova colonna una formula di concatenazione, (es: =C2 & “ “ & D2) per creare un’unica chiave primaria “multipla”,
- impostare il cerca verticale in modo che il valore abbiamo la stessa struttura di concatenazione, direttamente nella formula, oppure nella cella di riferimento.
Rendere dinamico il cerca verticale
Per rendere dinamiche le formule con cerca verticale, o meglio per permettere di poter aggiungere campi alla tabella senza danneggiare le formule, è necessario sostituire l’indice con una formula in grado di restituire sempre il numero di colonna corretto.
Naturalmente mi sto riferendo all’uso della funzione Confronta(), in grado di restituire il numero della colonna usando il nome del campo, in questo modo:
=CONFRONTA(“ARTICOLO”; Articoli[#Intestazioni]; 0).
In questo caso confronta() cerca il nome del campo “ARTICOLO” nell’intervallo delle intestazioni della tabella excel di nome “Articoli” e restituisce la sua posizione. Naturalmente la formula deve essere inserita nel cerca verticale al posto dell’indice.
Per esempio CERCA.VERT( F12; Dati!A2:S100; CONFRONTA(“ARTICOLO”; Articoli[#Intestazioni]; 0) ;FALSO) ).
Questa soluzione permette di aggiungere o rimuovere campi dalla tabella, ma però ha come conseguenza il limite di non modificare il nome dei campi indicati. Se modifichiamo il nome del campo la formula verrà danneggiata e non funzionerà più. Quindi possiamo rendere più flessibile il Cerca.Vert, ma limitando le modifiche alle intestazioni dei campi.
Per approfondire la nomenclatura delle tabelle Excel ti invito a leggere la relativa guida che puoi trovare in questa pagina.
Unire tabelle con il cerca verticale
Il cerca verticale viene molto spesso usato per unire tabelle, cioè per riportare in una tabella i valori di un campo di un’altra tabella che contiene una chiave primaria e secondaria che correli le due basi dati.
Per esempio se vogliamo visualizzare nella tabella del fatturato, campi con informazioni relative ai prodotti venduti, per esempio costi, tempi di produzione o consegna, eccetera, allora il cerca verticale può farlo in modo relativamente semplice.
Possiamo farlo per un campo, o due, o in teoria anche tutti i campi, arrivando a unire due intere tabelle, ma l’uso esteso di formule con cerca verticale per molti campi e molte migliaia di record può appesantire notevolmente i nostri fogli riducendone le prestazioni e l’efficienza.
Parleremo dell’unione di tabelle in un post dedicato e vedremo anche le alternative più efficace al cerca verticale. Perché ci sono alternative migliori a questa funzione che spesso viene usata in modo eccessivo.
Note sul cerca vert
- Nelle formule Cerca.Vert(), i valori non sono case sensitive, cioè possono essere sia in maiuscolo che in minuscolo, perché considerati equivalenti.
- Se l’indice è inferiore a 1 o negativo la formula Cerca.Vert() restituirà un errore (#VALORE!). Invece se l’indice indicato è maggiore del numero di colonne presenti nella tabella indicata, la formula restituirà un errore (#RIF!).
E il cerca orizzontale?
CERCA.ORIZZ(valore; matrice_tabella; indice; intervallo)
È la funzione analoga a Cerca.vert(), ma permette di lavorare in orizzontale. Funziona nello stesso modo, ma ruotato di novanta gradi. In pratica Cerca.orizz() è usato raramente, in quanto le tabelle dei dati in Excel sono prevalentemente standard, cioè verticali. Quando lo usiamo? Viene impiegato di solito su tabelle di elaborazione o tabelle dei risultati, tabelle di formule costruite in orizzontale.
Per il resto, la funzione lavora come Cerca.vert(), ma in modo trasposto. Per esempio, CERCA.ORIZZ(“Bk836004”;B2:S14;3;FALSO) cerca il valore indicato nella prima riga della matrice e restituisce il dato contenuto nella terza riga della matrice, con corrispondenza esatta.
E la nuova funziona Cerca.X?
È l’evoluzione di cerca verticale e cerca orizzontale, resa disponibile in Office 365 per gli iscritti a Office insider a partire da agosto 2019. Questa nuova formula è molto più evoluta e semplice da usare di cerca verticale. Non solo non c’è il limite dell’orientamento, ma gestire direttamente il possibile errore per assenza di valore, può restituire più valori di più campi, può offrire le stesse possibilità di delle funzioni Indice e Confronta e altro ancora.
Parleremo di questa funzione e delle nuove funzioni di Excel pubblicate in 365 e del loro utilizzo in post dedicati. Qui puoi vedere un’anticipazione.
Conclusioni
Come hai visto Cerca.Vert() è una funzione notevole e relativamente semplice che permette di individuare un valore in una matrice di dati e restituirlo per visualizzarlo o elaborarlo.
Cerca verticale è una funzione di ricerca davvero straordinaria e utile, che ci ha semplificato la vita nell’elaborazione dei dati su Excel negli ultimi 8 anni. Una volta compreso il suo funzionamento, questa funzione diventa una delle funzioni che più usiamo, spesso anche a sproposito.
In realtà Excel offre già da molto tempo strumenti alternativi a cerca verticale, sia come funzioni che come strumento più evoluto per specifici.
In primo luogo abbiamo già a nostra disposizione una funzione di ricerca più efficace, flessibile e sicura di cerca verticale e non è altro che l’uso in tandem delle funzioni Indice() e Confronta() che abbiamo visto in altri post e che approfondiremo in una guida dedicata.
Invece per quanto riguarda l’uso di cerca verticale per l’unione di tabelle e non solo, in realtà le ultime versioni di Excel offrono lo strumento Power Query, ora chiamato Recupera e Trasforma, in inglese Get & trasform.
Power query non solo permette di impostare query per estrarre dati da database di varia natura, ma può farlo anche da più tabelle excel sullo stesso foglio o in fogli diversi. Dà la possibilità di modellare i dati estratti come preferiamo in modo intuitivo e restituisce una tabella Excel dinamica e aggiornabile con un clic, alimentata dalla query e molto più leggera di tabelle di migliaia di formule cerca verticale.
In realtà cerca verticale è già destinata a essere sostituita da una nuova funzione Cerca.X molto più efficiente e comoda. Possiamo comunque prevedere che cerca verticale rimarrà a lungo nei fogli Excel finché le vecchie versioni di Excel non verranno sostituite dalla versione 365.
PS: Se questa guida ti è piaciuta o ti è stato utile, condividila con amici e colleghi sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie.
