In questo tutorial sul CERCA.VERT, cercherò di partire dalle basi in un linguaggio molto semplice per rendere la curva di apprendimento il più semplice possibile, anche per utenti inesperti. Esploreremo anche alcuni esempi di formule che affrontano gli utilizzi più comuni del CERCA.VERT in Excel.
La funzione CERCA.VERT di Excel – Informazioni di base e sintassi
Allora, cos’è il CERCA.VERT? Per cominciare, si tratta di una funzione di Excel 🙂 Che cosa fa? Cerca il valore specificato e restituisce un valore corrispondente da un’altra colonna. Più tecnicamente, la funzione CERCA.VERT cerca un valore nella prima colonna dell’intervallo specificato di celle, e restituisce il valore di un’altra colonna della stessa riga.
Nel suo uso comune, CERCA.VERT cerca attraverso le vostre liste di dati in base ad un identificatore univoco e vi restituisce un’informazione associata a tale identificativo univoco.
La stringa “VERT” di CERCA.VERT sta per “verticale”. E’ utilizzata per differenziare CERCA.VERT dalla funzione CERCA.ORIZZ che cerca un valore nella riga superiore di una matrice.
La funzione CERCA.VERT è disponibile in tutte le versioni di Excel dal 2000 al 2013.
Sintassi della funzione CERCA.VERT di Excel
La sintassi della funzione CERCA.VERT è la seguente:
=CERCA.VERT(valore; matrice_tabella; indice; [intervallo])
Come potete vedere, la funzione CERCA.VERT di Microsoft Excel ha 4 parametri, o argomenti. I primi tre parametri sono obbligatori, l’ultimo è facoltativo.
- valore – il valore da cercare.Questo può essere un valore (numero, data o testo), o un riferimento di cella (riferimento a una cella contenente un valore di ricerca), o il valore restituito da qualche altra funzione di Excel. Ad esempio, la formula =CERCA.VERT(40; A2:B11; 2) cercherà il valore 40.ATTENZIONESe il valore di ricerca è inferiore al valore minimo presente nella prima colonna della matrice di ricerca, la funzione CERCA.VERT restituisce l’errore #N/D.
- matrice_tabella – due o più colonne di dati.Tenete a mente che la funzione CERCA.VERT cerca sempre il valore di ricerca nella prima colonna di matrice_tabella. La vostra matrice_tabella può contenere valori diversi, come testo, date, numeri o valori logici. I valori sono case-insensitive, il che significa che testo maiuscolo e minuscolo è trattato come fosse identico.Quindi, la nostra formula =CERCA.VERT(40; A2:B11; 2) cercherà “40” nelle celle da A2 a A11 perché A è la prima colonna della matrice_tabella A2:B11. Speriamo che tutto sia chiaro finora 🙂
- indice – il numero di colonna in matrice_tabella da cui il valore nella riga corrispondente deve essere restituito.La colonna più a sinistra della specificata matrice_tabella ha indice 1, la seconda colonna ha indice 2, la terza colonna 3 e così via.Bene, ora potete leggere l’intera formula =CERCA.VERT(40; A2:B11; 2). Le formula cerca “40” nelle celle da A2 a A11 e restituisce il valore corrispondente della colonna B (perché B è la seconda colonna della specificata matrice_tabella A2:B11).
INFOSe il vostro argomento “indice” è inferiore a 1, la formula CERCA.VERT restituirà un errore #VALORE!. Nel caso sia maggiore del numero di colonne presenti in matrice_tabella, la funzione restituirà invece un errore #RIF!. - intervallo – specifica se siete alla ricerca di una corrispondenza esatta (FALSO) o di una corrispondenza approssimativa (VERO o omesso). Questo ultimo parametro è opzionale ma molto importante. Più avanti in questo tutorial sul CERCA.VERT vi fornirò alcuni esempi che spiegano come inserire correttamente formule per una corrispondenza esatta o per una approssimativa.
Esempi di CERCA.VERT in Excel
Mi auguro che la funzione CERCA.VERT stia cominciando ad esservi un po’ più familiare. Cerchiamo ora di capire alcuni esempi di utilizzo delle formule CERCA.VERT su dati reali.
Come fare CERCA.VERT da un altro foglio di lavoro
Nella pratica, le formule CERCA.VERT sono raramente utilizzate per trovare dati nello stesso foglio di lavoro. Il più delle volte dovrete cercare e recuperare dati da un foglio diverso.
Per fare un CERCA.VERT da un foglio di Excel diverso, è necessario immettere il nome del foglio di lavoro e un punto esclamativo nell’argomento matrice_tabella prima dell’intervallo di celle, ad esempio, =CERCA.VERT(40; Foglio2!A2:B11; 2). La formula indica che l’intervallo di ricerca A2: B11 si trova in Foglio2.
Naturalmente, non è necessario digitare il nome del foglio manualmente. Basta iniziare a digitare la formula e, quando si arriva all’argomento matrice_tabella, passare al foglio di lavoro di ricerca e selezionare l’intervallo utilizzando il mouse.
La formula che vedete nello screenshot qui sotto ricerca il testo “Prodotto 1” nella colonna A (1^ colonna dell’intervallo di ricerca A2:B9) nel foglio di lavoro “Prezzi”:
=CERCA.VERT("Prodotto 1"; Prezzi!$A$2:$B$9; 2; FALSO)
Prestate attenzione al fatto di racchiudere il valore testuale da cercare tra virgolette (””), come si fa di solito nelle formule di Excel.
Come fare CERCA.VERT da un altra cartella di lavoro
Per eseguire un CERCA.VERT tra due diverse cartelle di lavoro di Excel, è necessario fornire il nome della cartella di lavoro tra parentesi quadre prima del nome del foglio di lavoro.
Ad esempio, la formula seguente cercherà il valore “40” nel Foglio2 della cartella di lavoro Animali.xlsx:
=CERCA.VERT(40;[Animali.xlsx]Foglio2!A2:B15;2)
Il modo più semplice per creare una formula CERCA.VERT che faccia riferimento ad un’altra cartella di lavoro è il seguente:
- Aprite entrambe le cartelle di lavoro. Questo passaggio non è obbligatorio, ma rende più facile inserire la formula, poiché non sarà necessario digitare il nome della cartella di lavoro manualmente. Inoltre, farà in modo che le formule non presentino errori accidentali
- Iniziate a digitare la formula CERCA.VERT, e per l’argomento matrice_tabella, passate all’altra cartella di lavoro e selezionate l’intervallo di ricerca
Nella formula che vedete nello screenshot qui sotto, la ricerca cartella di lavoro è Elenco prezzi.xlsx e il foglio di lavoro è Prezzi.
Una volta che avrete chiuso la cartella di lavoro con la vostra tabella di ricerca, la formula CERCA.VERT funzionerà comunque, ma visualizzerà il percorso completo per la cartella di lavoro di ricerca, come mostrato di seguito:
Come utilizzare intervalli denominati o tabelle nelle formule CERCA.VERT
Se pensate di utilizzare lo stesso intervallo di ricerca in diverse formule CERCA.VERT, è possibile creare un intervallo denominato e digitare il suo nome direttamente nella formula anziché l’intervallo di ricerca (argomento matrice_tabella).
Per creare un intervallo denominato, è sufficiente selezionare le celle e digitare un nome nella “Casella del nome”, a sinistra della barra della formula.
E ora potrete scrivere la seguente formula CERCA.VERT per ottenere il prezzo di Prodotto 1:
=CERCA.VERT("Prodotto 1";Prodotti;2)
La maggior parte degli intervalli denominati in Excel valgono per l’intera cartella di lavoro, quindi non è necessario specificare il nome del foglio di lavoro nell’argomento matrice_tabella, anche se l’intervallo si trova in un foglio di lavoro diverso. Se si trova in un’altra cartella di lavoro, occorre inserire il nome della cartella di lavoro prima dell’intervallo denominato, ad esempio:
=CERCA.VERT("Prodotto 1";'Elenco prezzi.xlsx'!Prodotti;2)
Queste formule sono molto più comprensibili, non è vero? Inoltre, utilizzare intervalli denominati può essere una buona alternativa ai riferimenti di cella assoluti. Dal momento che un intervallo denominato non cambia quando una formula viene copiata in altre celle, potete essere sicuri che il vostro intervallo di ricerca rimarrà sempre corretto.
Se avete convertito un intervallo di celle in una tabella vera e propria di Excel (scheda INSERISCI > Tabella), selezionando l’intervallo di ricerca utilizzando il mouse, Microsoft Excel aggiungerà automaticamente alla formula i nomi delle colonne (o il nome della tabella nel caso in cui abbiate selezionato l’intera tabella):
La formula completa potrebbe essere simile a questa:
=CERCA.VERT("Prodotto 1";Tabella3[[Prodotto]:[Prezzo]];2)
o anche
=CERCA.VERT("Prodotto 1";Tabella3;2)
Come gli intervalli denominati, anche i nomi delle colonne o delle tabelle sono costanti e i vostri riferimenti di cella non cambieranno, non importa dove la formula CERCA.VERT si copi all’interno della stessa cartella di lavoro.
Utilizzare caratteri jolly nelle formule CERCA.VERT
Così come in molte altre formule, è possibile utilizzare i seguenti caratteri jolly nella funzione CERCA.VERT di Excel:
- Punto interrogativo (?) – Sostituisce qualunque carattere (singolo)
- Asterisco (*) – Sostituisce qualunque sequenza di caratteri
Utilizzare i caratteri jolly nelle formule CERCA.VERT può rivelarsi davvero utile in molti casi:
- Se non ricordate il testo esatto che state cercando
- Se si vuole trovare una parola che è solo una parte del contenuto della cella. Siate consapevoli del fatto che la funzione CERCA.VERT cerca l’intero contenuto della cella, come se aveste selezionato l’opzione “Confronta intero contenuto della cella” nella finestra standard “trova e sostituisci” di Excel
- Quando una colonna di ricerca contenga degli spazi aggiuntivi in testa, in mezzo o in coda al testo che cerchiamo. In questo caso, potreste sbattere la testa cercando di capire il motivo per cui la normale formula non funziona
Esempio 1. Cercare testo che inizia o termina con determinati caratteri
Supponiamo che vogliate trovare un determinato cliente nel database. Non riuscite a ricordare il suo cognome, ma sapete che inizia con “col”. La seguente formula CERCA.VERT funzionerà a meraviglia:
=CERCA.VERT("col*";$A$2:$C$25;1;FALSO)
Una volta che siete sicuri di aver trovato il nome corretto, è possibile utilizzare una formula CERCA.VERT simile per ottenere la somma pagata da quel cliente. Basta cambiare il terzo parametro nella formula inserendo il numero di colonna appropriato, colonna C (3) nel nostro caso:
=CERCA.VERT("col*";$A$2:$C$25;3;FALSO)
Ecco alcuni altri esempi di formule CERCA.VERT con caratteri jolly:
=CERCA.VERT(“*rio”;$A$2:$C$25;1;FALSO) – per trovare un nome che termina in “rio”
=CERCA.VERT(“co*de”;$A$2:$C$25;1;FALSO) – per trovare un nome che inizia con “co” e finisce con “de”
=CERCA.VERT(“???????????”;$A$2:$C$25;1;FALSO) – trovare un nome lungo 11 caratteri
Esempio 2. Formule CERCA.VERT con caratteri jolly basate sul valore di una cella
E ora parliamo del caso un po’ più complesso di come fare un CERCA.VERT in base al valore di una cella. Supponiamo che abbiate un elenco di chiavi di licenza in colonna A e i nomi degli assegnatori in colonna B. Avete una porzione (qualche carattere) di un certo codice di licenza in cella D2 e volete trovare a chi è stato assegnato.
Questo può essere fatto utilizzando una formula CERCA.VERT come questa:
=CERCA.VERT("*"&D2&"*";Tabella2;2;FALSO)
Questa formula cerca il valore presente in D2 nell’intervallo specificato e restituisce il valore corrispondente trovato in colonna B. Vi invitiamo a prestare attenzione alla “e commerciale” (&) prima e dopo del riferimento di cella nel 1° parametro al fine di concatenare le stringhe di testo.
Come potete vedere nello screenshot qui sotto, la mia funzione CERCA.VERT restituisce “DE LUCA VALENTINA” perché la sua chiave di licenza contiene il gruppo di caratteri indicati nella cella D2:
Prestate inoltre attenzione all’argomento matrice_tabella. Esso contiene il nome della tabella (“Tabella2”) invece di un intervallo di celle, come abbiamo visto nel precedente esempio.
Usare le formule CERCA.VERT con corrispondenza esatta o approssimativa
E, infine, diamo uno sguardo più da vicino all’ultimo argomento della funzione CERCA.VERT di Excel: intervallo. Come già accennato all’inizio di questo tutorial, questo parametro è molto importante perché la stessa formula restituirà risultati diversi a seconda dell’inserimento di VERO o FALSO.
Prima di tutto, vediamo cosa significa in realtà “corrispondenza esatta” o “corrispondenza approssimativa” per Microsoft Excel .
- Se intervallo è impostato su FALSO, la formula cercherà una corrispondenza esatta, vale a dire il valore di ricerca esattamente come lo avete inserito nel primo parametro (valore). Se nella prima colonna di matrice_tabella ci sono due o più valori che corrispondono al valore di ricerca, viene restituito il primo valore trovato. Se non viene trovata nessuna corrispondenza esatta, viene restituito un errore #N/D
- Se intervallo è impostato su VERO o omesso, la formula cerca una corrispondenza approssimativa. Più precisamente, la formula CERCA.VERT cercherà prima una corrispondenza esatta e, se non viene trovata una corrispondenza esatta, restituirà una corrispondenza approssimativa. Una corrispondenza approssimativa è il valore più grande inferiore al parametro “valore”.
Per esplorare ulteriormente l’importanza di specificare VERO o FALSO, proviamo un paio di formule CERCA.VERT e vediamo quali risultati vengono restituiti.
Esempio 1. Come fare un CERCA.VERT con corrispondenza esatta in Excel
Come ricorderete, per ricercare una corrispondenza esatta, occorre mettere FALSO come argomento finale della funzione CERCA.VERT.
Prendiamo nuovamente la tabella “Velocità animali” trattata nel primo esempio e scopriamo quale animale corre a 48 Km/h. Credo che non avrete alcuna difficoltà con la formula:
=CERCA.VERT(48;$A$2:$B$13;2;FALSO)
Vi invito a notare che il nostro intervallo di ricerca (colonna A) contiene due valori “48” nelle celle A7 e A8; e la formula restituisce il valore dalla cella B7. Come mai? Perché la funzione CERCA.VERT con corrispondenza esatta restituisce il 1° valore trovato che corrisponde al valore di ricerca.
Esempio 2. Utilizzare un CERCA.VERT con corrispondenza approssimativa in Excel
Quando si utilizzano formule CERCA.VERT con corrispondenza approssimativa, cioè con “intervallo” impostato su VERO o omesso, la prima cosa che dovete fare è ordinare la prima colonna del vostro intervallo di ricerca in ordine crescente.
Questo è molto importante perché la formula CERCA.VERT troverà il valore immediatamente più grande del valore di ricerca specificato e quindi interromperà la ricerca restituendo il valore precedente in elenco. Se si trascura di ordinare correttamente i dati, si finirà per avere risultati strani o un errore di #N/D.
Ed ora potete utilizzare una delle seguenti formule:
-
=CERCA.VERT(48;$A$2:$B$13;2;VERO) oppure =CERCA.VERT(48;$A$2:$B$13;2)
-
=CERCA.VERT(100;$A$2:$B$13;2;VERO) oppure =CERCA.VERT(100;$A$2:$B$13;2)
Come potete vedere, stiamo cercando di trovare l’animale la cui velocità è più vicino a 48 km/h e quello che si avvicina di più a 100 km/h. Ed ecco cosa restituiscono le nostre formule CERCA.VERT:
Come vedete, la prima formula, che ricerca il valore “48” come nell’esempio fatto per la corrispondenza esatta, questa volta restituisce “Orso Grizzly” piuttosto che “Cervo”: questo perché Excel trova il valore immediatamente superiore al valore di ricerca (57) e restituisce il valore che lo precede in elenco.
La seconda formula restituisce invece “Struzzo” la cui velocità è 69 km/h, mentre abbiamo anche “Ghepardo” che corre 101 km/h, e 101 è molto più vicino a 100 che 69, non è vero? Allora, perché? Perché la funzione CERCA.VERT con corrispondenza approssimativa restituisce il valore più grande immediatamente inferiore al valore di ricerca.
Speriamo che questi esempi abbiano fatto luce sull’utilizzo del CERCA.VERT in Excel e che questa funzione non vi sia più estranea 🙂
Ora può essere una buona idea riassumere gli elementi essenziali che avete imparato per ricordare meglio i punti chiave.
CERCA.VERT di Excel – Cose da ricordare!
- Funzione di Excel CERCA.VERT non può cercare alla sua sinistra. Cerca sempre il valore di ricerca (valore) nella colonna più a sinistra dell’intervallo di ricerca (matrice_tabella)
- Nelle formule CERCA.VERT, tutti i valori possono essere sia in maiuscolo che in minuscolo, il che significa che i caratteri maiuscoli e minuscoli sono considerati equivalenti
- Se il valore di ricerca è inferiore al valore minimo presente nella prima colonna dell’intervallo di ricerca, la funzione CERCA.VERT restituisce l’errore #N/D
- Se il terzo parametro (indice) è inferiore a 1, la formula CERCA.VERT restituirà l’errore #VALORE!. Nel caso in cui sia maggiore del numero di colonne presenti nell’intervallo di ricerca (matrice_tabella), la formula restituirà il valore di errore #RIF!
- Nelle formule CERCA.VERT, utilizzate i riferimenti di cella assoluti nell’argomento matrice_tabella, per avere l’intervallo di ricerca corretto quando doveste copiare le formule. Come alternativa, considerate l’utilizzo di intervalli denominati oppure di tabelle
- Durante la ricerca con corrispondenza approssimativa (intervallo impostato su VERO o omesso), sistemate sempre i dati della prima colonna del vostro intervallo di ricerca in ordine crescente
- E, infine, ricordate l’importanza del parametro finale. Inserite VERO o FALSO in modo appropriato ed eviterete un sacco di mal di testa 🙂
Per approfondire ulteriormente i possibili scenari di utilizzo di questa potente funzione di Excel, è disponibile in questa pagina la sua scheda tecnica completa.
Nella prossima parte del nostro tutorial sul CERCA.VERT di Excel, esploreremo esempi più avanzati, come l’esecuzione di vari calcoli con CERCA.VERT, l’estrazione di valori da diverse colonne e altro ancora. Vi ringrazio per la lettura e spero di vedervi la prossima settimana!
