Cerca vert

samuele agnolin
  • 22 maggio 2017
  •   4 minuti
cerca vert

La funzione cerca vert permette di cercare un determinato valore nella prima colonna di un elenco e di restituire il contenuto di una delle celle della stessa riga del valore trovato. 

Vediamo subito un esempio pratico sul cerca verticale.

Abbiamo due elenchi. Il primo è un elenco di clienti che appartengono ad una specifica area geografica. Il secondo è un elenco delle aree geografiche e dei venditori che seguono ogni specifica area.

valori da ricercare

Il risultato finale del cerca verticale dovrà essere il seguente:risultato finale cerca vertL’area del cliente1 (Sud) viene cercata nella prima colonna del secondo elenco, quando l’area viene trovata il cerca vert deve restituire il venditore associato all’area cercata.

Notiamo il messaggio di errore #N/D: ci dice che la voce “Isole” non è stata trovata nella matrice tabella. Nel secondo paragrafo vedremo come scrivere “informazione mancante” al posto del messaggio #N/D.




1. Come scrivere la funzione cerca.vert?

Ci si posiziona nella cella nella quale si vuole ottenere il risultato. Si richiama l’elenco delle funzioni. Si individua il CERCA.VERT nella categoria RICERCA E RIFERIMENTO (oppure da “Usate di recente” se abbiamo da poco usato la funzione o dalla categoria “Tutte”, dove le funzioni di Excel sono elencate in ordine alfabetico).funzioni ricerca riferimento

Popoliamo i parametri:

 1  il valore da cercare: nel nostro caso C3, la cella in cui è scritta l’area
valore da cercare

 2  la tabella sulla cui prima colonna cercare il valore, chiamata matrice_tabella: nell’esempio è l’area H3:I6

matrice tabella

 3  il numero della colonna della tabella da cui estrarre il dato, chiamato indice: nel nostro caso è 2, poichè 1 indica la prima colonna della matrice tabella (Area), 2 indica la seconda colonna (Venditore)

indice

 4  Il quarto parametro va solitamente posto a zero (o FALSO), questo fa capire ad Excel che vogliamo che venga cercato esattamente il valore che stiamo cercando e che, in caso di assenza di tale valore, ci venga restituito il risultato “non disponibile” (#N/D). Si parla di ricerca con “corrispondenza esatta”.

intervallo

La nostra funzione sarà quindi:

=CERCA.VERT(C3;H3:I6;2;0)

e il risultato sarà il seguente:

risultato cerca vert

Se proviamo a trascinare il nostro risultato nelle celle successive otteniamo però degli errori:

trascinamento-errori

Il problema sta nel fatto che trascinando verso il basso cambia il riferimento alla matrice tabella

problema matrice tabella

Risolviamo bloccando con il dollaro il riferimento alle righe della matrice tabella

La formula corretta è quindi =CERCA.VERT(C3;H$3:I$6;2;0)

Se hai bisogno di dare una “rinfrescatina” alle tue conoscenze sul dollaro puoi leggere il tutorial su come usare il dollaro, corredato di video YouTube.

dollaro blocca matrice tabella

Possiamo quindi trascinare verso il basso ed ottenere il risultato corretto.

risultato finale cerca vert

Questo il video che mostra i passaggi appena descritti:






Riassumendo ciò che è stato fatto nell’esempio precedente, possiamo dire che
il “Valore”, primo parametro, viene ricercato nelle celle della prima colonna della “Matrice_tabella”;
– una volta trovata la prima riga contenente il valore cercato viene restituita l’informazione specificata dal parametro “Indice”, il quale rappresenta il numero della colonna da cui prelevare il dato.
– Il parametro facoltativo “Intervallo” va posto a zero quando, situazione molto frequente, si chiede di cercare esattamente il “valore” e di mostrare il messaggio “non disponibile” (#N/D) nel caso esso non venga individuato.

Vediamo ora come personalizzare il messaggio #N/D, con il quale Excel ci dice che non ha trovato quel particolare valore nella prima colonna della matrice tabella.



2. Gestire l’errore #N/D

#N/D, valore non disponibile. Questo il messaggio che otteniamo quando il cerca.vert non riesce a trovare il valore che noi stiamo cercando.

Come evitare di vedere la scritta #N/D? Come personalizzare il messaggio di valore non trovato?

L’errore #N/D nel nostro esempio non dipende da errori nella formula (come ad esempio quello visto in precedenza legato all’assenza dei dollari sulla matrice tabella) ma dal fatto che la voce Isole non trova corrispondenza nella matrice tabella.

Vogliamo scrivere “informazione mancante” al posto di #N/D. In questo modo sarebbe più comprensibile, anche agli occhi di altre persone a cui dovessimo eventualmente girare il nostro file.

risultato completo se errore

La funzione che ci aiuta è il SE.ERRORE.

La funzione logica SE.ERRORE analizza il risultato di una cella o di una espressione e, nel caso questa ritorni un errore, consente di specificare che cosa visualizzare nella cella al posto dell’errore.

La funzione prevede quindi due parametri: la cella (o l’espressione) da valutare e il contenuto da scrivere nel caso di errore.

La sintassi della funzione è:

=SE.ERRORE(valore;valore_se_errore)

Nel nostro caso scriveremo

=SE.ERRORE(D3;”informazione mancante”)

oppure richiamiamo la funzione dalla tipologia Logiche
funzioni logiche se errore

La funzione legge il contenuto di D3. Se D3 è testo/numero/data/cella-vuota la funzione restituisce il contenuto di D3. Se D3 è un errore la funzione restituisce la scritta “informazione mancante”.

Questi i parametri della funzione:

parametri se errore

Questo il risultato sulla prima cella:

risultato-se-errore-su-prima-cella

E questo il risultato dopo aver riportato la formula anche nelle celle sottostanti:

risultato completo se errore

 

Capito l’utilizzo del SE.ERRORE possiamo annidare CERCA.VERT e SE.ERRORE sulla stessa cella.

Ci posizioniamo su D3. Dopo l’uguale e prima del cerca vert scriviamo SE.ERRORE(

se errore nel cerca vert

Punto e virgola dopo la chiusura del cerca vert. Scriviamo il secondo parametro del SE.ERRORE: “informazione mancante” e chiudiamo la parentesi

secondo-parametro dopo cerca vert

Trasciniamo la formula nelle celle sottostanti ed otteniamo il risultato

risultato se errore con cerca vert

Possiamo ora cancellare le elaborazioni fatte nella colonna E ed allargare la colonna D

colonna e cancellata






Una breve considerazione utile per chi ha una versione di Excel inferiore alla 2007.

La funzione SE.ERRORE è stata introdotta con Excel 2007. Nelle versioni precedenti lo stesso risultato poteva essere raggiunto annidando le funzioni VAL.ERRORE e SE:

=SE(VAL.ERRORE(D3);”informazione mancante”;D3)

Se la cella D3 è un errore allora scrivi “informazione mancante” altrimenti restituisci D3.

Chi utilizza le ultimissime versioni di Excel troverà anche la funzione SE.NON.DISP. Molto simile concettualmente al SE.ERRORE. Con la differenza che SE.NON.DISP valuta solamente l’errore #N/D.

3. Come usare il carattere jolly *

Non sempre il valore che stiamo cercando è scritto esattamente nello stesso modo nella prima colonna della matrice tabella. A volte il valore è una parte del contenuto della cella.

Esempio pratico. Nell’immagine sottostante possiamo vedere cosa succederebbe se nella matrice tabella ci fosse scritto “Area Nord-ovest” e “Area Nord-est” al posto di “Nord-ovest”e “Nord-est”.

matrice colonna modificata

Il cerca vert non troverebbe i valori relativi a quelle due etichette. E il SE.ERRORE ci restituirebbe “informazione mancante”.

Come sistemare? Potremmo modificare la matrice tabella togliendo “Area ” dalle prime due celle. Ma questo metodo ci costringerebbe ad utilizzare funzioni, tipo SINISTRA o STRINGA.ESTRAI, e non sarebbe molto “elastico”.

Risolviamo con il carattere jolly “*”.

Trasformiamo il nostro cerca vert iniziale da

CERCA.VERT(C3;H$3:I$6;2;0)

a

CERCA.VERT(“*”&C3;H$3:I$6;2;0)

asterisco nel valore

Cosa vuol dire quel “*”&C3 che abbiamo messo nel parametro valore?

Stiamo concatenando (con il simbolo &) il carattere * ed il contenuto di C3 (Sud): il risultato di questa concatenazione, quel che Excel legge, è “*Sud”

L’asterisco sta per “un qualsiasi numeri di caratteri”, da zero in su.

Quindi scrivere “*Sud” vuol dire che Excel si fermerà dove è scritto solo “Sud” oppure dove “Sud” è la parte finale del contenuto della cella: quindi “Area Sud” verrà trovato dal cerca vert, così come qualsiasi altra scritta che termini con “Sud”, ad esempio “Zona Sud”, “Area geografica Sud”, spazi seguiti da “Sud”.

Trascinando otteniamo il risultato corretto:

correzione con carattere jolly asterisco

Scrivendo “*”&C3&”*” diciamo ad Excel che ci va bene che trovi o le celle in cui è scritto solo “Sud” oppure quelle in cui “Sud” è contenuto. L’asterisco finale quindi fa capire ad Excel che anche dopo “Sud” potrebbe esserci altro, spazi compresi.

 

Lo conosci il trucchetto per verificare se Excel esegue la concatenazione “*”&C3 nel modo da noi atteso???

Nella formula selezioniamo “*”&C3

selezione valore

Tasto F9, otteniamo il risultato della concatenazione che Excel effettuerà: “*Sud”

tasto F9 risultato

ATTENZIONE: se non vogliamo perdere parte della nostra formula, dopo aver verificato con il tasto F9 dobbiamo tornare indietro con CTRL+Z, altrimenti all’interno del cerca vert resterà scritto “*Sud” invece che “*”&C3

4. Perché FALSO il quarto parametro?

Perché quasi sempre poniamo a 0 o FALSO il quarto parametro? La risposta che diamo è, spesso, “perché ci hanno sempre detto di fare così”. Ok ma cerchiamo di capire perché è così importante farlo.

Se riuscirai ad arrivare sano e salvo alla fine di questo paragrafo un po’ complicato le tue competenze sul cerca vert saranno già superiori a quelle della gran parte degli utenti che mettono 0 o FALSO perchè qualcuno ha “sempre detto di fare così”. :-)

Iniziamo. Quando nel quarto parametro scriviamo 0 o FALSO stiamo chiedendo ad Excel una “corrispondenza esatta”.

Vuol dire che se stiamo chiedendo di cercare il valore “Sud” vogliamo che Excel cerchi nella prima colonna della matrice tabella la prima cella il cui contenuto è “Sud”. Eventuamente giocando con l’asterisco come detto nel precedente paragrafo.

Se stiamo cercando il codice AZ980 vogliamo che Excel cerchi esattamente quel codice e che ci restituica #N/D nel caso il codice non sia presente.

Quando lasciamo vuoto il quarto parametro oppure lo poniamo uguale a 1 oppure a VERO stiamo cercando una corrispondenza più simile. Excel ci dice che per poter applicare una corrispondenza più simile i dati della prima colonna della matrice tabella devono essere ordinati.

E se i dati non sono ordinati cosa succede?

Succede che i risultati possono essere errati!

Ecco cosa succede nel nostro esempio. Togliamo il quarto parametro dal cerca vert iniziale, quello senza il se.errore:

quarto parametro omesso

Questo il risultato. In rosso i risultati errati.

errori ricerca piu simile

E se ordiniamo la prima colonna della matrice tabella?

matrice tabella ordine crescente

Succede che i dati in rosso, quelli che prima erano sbagliati, ora sono corretti. Ma adesso c’è un altro problema: l’area Isole è associata al venditore 3 ma noi invece dovremmo avere il messaggio #N/D.

risultato con matrice tabella ordinata

Perché Isole è ora associato a venditore 3? Perché stiamo attuando una ricerca più simile, il che vuol dire che se Excel non trova il valore che stiamo cercando (Isole) è libero di individuare nella matrice tabella un valore “simile” a quello cercato. “Simile” in termini di vicinanza alfabetica in questo caso.

Riassumendo, lasciando vuoto il quarto parametro pare che i problemi siano 2:

se la prima colonna non è ordinata il rischio è di ottenere risultati non corretti

se la prima colonna è ordinata il rischio è di ottenere risultati anche per quei valori che in realtà nella matrice tabella non compaiono

Ma allora il quarto parametro non serve a niente??? Eh no, se c’è vuol dire che in qualche situazione ha senso che venga utilizzato. Giusto.

Ecco la situazione: il quarto parametro va lasciato vuoto quando la matrice tabella è composta da intervalli.

Ecco un esempio pratico sul cerca vert con corrispondenza più simile.

Nella tabella dell’immagine seguente abbiamo una colonna di punteggi conseguiti e dobbiamo associare ad ogni punteggio il relativo risultato (scarso, sufficiente, buono, ottimo).

tabella con range

Possiamo usare il cerca vert nel modo classico, con il quarto parametro posto a FALSO? NO!!!, perché, come abbiamo visto, mettere FALSO (o zero) significa chiedere una corrispondenza esatta: cerca esattamente il punteggio (il primo ad esempio è 120) nella prima colonna della matrice tabella. Se non lo trovi restituisci #N/D. In questo caso il 120 e tutti gli altri punteggi non verrebbero mai trovati ed avremmo #N/D in ogni riga.

Ma allora ci creiamo una matrice tabella che abbia tutti i singoli possibili punteggi, da 1 a 300, ed accanto ad ogni punteggio scriviamo il risultato, no? Tecnicamente è possibile, ma sai quanto tempo perdiamo per fare una cosa del genere :-(

Facciamo una cosa più furba: riscriviamo la matrice tabella semplicemente riportando in ogni cella il valore iniziale del singolo intervallo. Come nell’immagine seguente.

matrice tabella range

Sulla matrice così creata andiamo ad applicare un CERCA.VERT lasciando vuoto il quarto parametro. Questa la nostra funzione:

=CERCA.VERT(B5;F$11:G$14;2)

cerca vert quarto parametro vuoto

Il che equivale, nella finestra dell’inserimento funzione, a questo:

finestra parametri cerca verticale

Perché funziona? Come ragiona Excel se lasciamo vuoto (o VERO o 1) il quarto parametro?

La descrizione nella funzione ci dice che usare così il quarto parametro significa “trovare la corrispondenza più simile nella prima colonna (in ordine ascendente)“.

Ci dice che la prima colonna deve essere oridinata in maniera ascendente. Perché? Perché l’ordine è funzionale al modo di “ragionare” di Excel. Il singolo punteggio, ad esempio il primo, che è 120, verrà cercato nella prima colonna ed Excel si fermerà sul 120 se questo è uno dei valori oppure sul più grande tra i minori di 120, in questo caso nel valore 101.

Nel dettaglio, Excel legge il primo valore della matrice tabella, che qui è 0, e prosegue verso il basso. Legge il secondo, 50, vede che il non è il valore che sta cercando (120) e che è inferiore. Legge quindi il terzo valore, il 101, e prosegue perché non è 120 ed è ancora inferiore allo stesso 120. Legge il quarto parametro, 201, capisce di aver già superato il 120, questo proprio perché i valori della prima colonna della matrice devono essere in ordine ascendente. Dato che ha superato il 120 Excel fa un passo indietro e si ferma al valore precedente, il 101, prendendo così il più grande tra i minori di 120.

A questo punto abbiamo capito perché ci hanno sempre detto di impostare il quarto parametro a FALSO (o zero): perché quasi sempre noi NON abbiamo una matrice tabella che rappresenta intervalli ma, al contrario, dobbiamo cercare un particolare valore all’interno di una matrice tabella e saper con certezza se quel valore è presente o no.

Se, facendo una ricerca esatta, ci dimenticassimo di impostare a FALSO il quarto parametro rischieremmo di ottenere una risultato assolutamente errato, in quando Excel andrebbe ad applicare la logica poco sopra descritta, ipotizzando una prima colonna in ordine ascendente e restituendoci probabilmente un valore simile a quello da noi cercato.

Ma è ben diverso dire che l’azienda X ha fatto acquisti per 100.000 euro quando magari l’azienda X non è affatto presente nel nostro elenco ma il nostro cerca vert, avendo il quarto parametro vuoto, ha cercato una corrispondenza più simile e si è fermato ad un’azienda dal nome simile a quello da noi cercato!!! :-)

5. Un trucco per poter aggiungere colonne

Che succede se nella matrice tabella aggiungiamo colonne prima della colonna da cui estraiamo il dato?

Il cerca vert non restituisce più il dato corretto.

Esempio pratico. Riprendiamo la tabella dei primi paragrafi ed aggiungiamo una colonna all’interno della matrice tabella.

Il cerca vert CERCA.VERT(C3;H$3:J$6;2;0) restituisce il dato della colonna 2, come specificato nel terzo parametro, quindi 0. Zero perché trova cella vuota nella colonna 2

colonna nuova errore

Come risolvere? Utilizziamo la funzione CONFRONTA.

CONFRONTA ci restituisce in maniera dinamica la posizione della cella “Venditore” all’interno delle celle delle intestazioni.

Si scrive così: CONFRONTA(D2;H2:J2;0)

funzione-confronta-sintassi

Il risultato è 3, significa che la colonna “Venditore” è al terzo posto tra le celle delle intestazioni. A noi quindi nel cerca vert serve restituire l’informazione della colonna 3.

risultato funzione confronta

Proviamo ora ad annidare CONFRONTA e CERCA.VERT

Doppio clic sulla cella del CONFRONTA. Selezioniamo tutto eccetto l’uguale. Premiamo ESC.

seleziona confronta

Doppio clic sulla cella del CERCA.VERT. Selezioniamo il terzo parametro

cerca-vert selezione terzo parametro

Incolliamo il CONFRONTA precedentemente copiato

confronta nel terzo parametro

Otteniamo il risultato finale

risultato finale confronta

Se hai un minuto fammi sapere con un commento che cosa ne pensi, grazie! :-)

12 Comments

You can post comments in this post.


  • questa guida è fantastica! stavo avendo dei problemi nel mantenere fissa la matrice della tabella e non riuscivo a capire il perché..e poi ho trovato nel tuo articolo la soluzione, grazie e complimenti! :)

    Antonella 5 mesi ago Reply


    • Grazie mille Antonella! Mi fa proprio piacere!!! :-)

      samuele 5 mesi ago Reply


  • Ciao Samuele,
    sei super!!! Ho acquisito un sacco di DRITTE dal tuo sito.
    Ho un quesito da porti, sul comando CERCA.VERT:
    ho un file con vari fogli, e quindi varie Matrice_tabella, vorrei impostare un foglio principale con il comando CERCA.VERT, ma con una casella a parte per definire il foglio Matrice_tabella.
    Ho provato in modo banale inserendo =J5 (identificativo casella) nella formula, ma non funzia!!! ovviamente nella casella J5 ho digitato il nome del foglio.
    Riesci a risolvere anche questa?
    Grazie e complimenti per il sito .
    Andrea

    Andrea Izzo 4 mesi ago Reply


    • Ciao Andrea, grazie! :-)

      Per impostare una matrice tabella variabile nel Cerca vert potresti utilizzare la funzione INDIRETTO e il concatena (&).

      Ipotizzando che l’intervallo della matrice tabella di ciascun foglio sia A1:B3 potresti provare così:

      =CERCA.VERT(valore_da_cercare;INDIRETTO(J5&”!”&”A1:B3″);colonna;intervallo)

      La funzione INDIRETTO legge quel che tu concateni al suo interno come un intervallo a cui far riferimento. Quindi la funzione INDIRETTO legge “nome_del_foglio_scritto_in_J5!A1:B3” e si aspetta che questo sia un riferimento ad una o più celle.

      Se il tuo intervallo non è sempre lo stesso, non è quindi sempre A1:B3 del mio esempio, nella cella J5 scriverai il riferimento completo e lo darai sempre in pasto ad INDIRETTO. Su J5 quindi scriverai ad esempio nome_del_foglio!A1:C100 e, nel secondo parametro del cerca vert, INDIRETTO(J5).

      Prova ad adattare l’idea alla tua situazione e fammi sapere, se ti va.

      Ciao!

      samuele 4 mesi ago Reply


  • Ciao Samuele,
    complimenti per il sito e per le precise spiegazioni che dai! Eccezionale!
    Avrei un’osservazione relativamente all’ultimo paragrafo.
    Nella funzione Confronta, non dovremmo bloccare i valori in questo modo?=CONFRONTA(D$2;H$2:J$2;0)
    Altrimenti il risultato è errato se non blocchiamo le celle, corretto?
    Ti ringrazio per l’aiuto
    Eleonora

    Eleonora 4 mesi ago Reply


    • Hai perfettamente ragione Eleonora. Grazie mille per la segnalazione!

      samuele 4 mesi ago Reply


  • Ti ringrazio, ho risolto!
    Ho notato anche che il nome del foglio non deve contenere caratteri speciali, con il – non funziona!!!

    Ciao

    Andrea 4 mesi ago Reply


  • ottima guida!!! complimenti per il sito. la tua spiegazione sul cerca.vert mi ha salvato dal dover cercare ogni volta ad occhio i singoli valori da un database di dati. Particolarmente utile la funzione jolly che non ne sapevo proprio dell’esistenza.

    Bravo

    Francesco 1 mese ago Reply


    • Ciao Samuele

      avrei un quesito da porti sul CERCA.VERT. Quando utilizzo il cerca vert per estrarre un valore in una matrice di dati come faccio nel caso in cui ci sono due valore molto simili da ricercare. Ad esempio utilizzo la formula =CERCA.VERT(“*”& “società x” & “*”; A3:D340; 4;0) i due valore da ricercare sono Società X (gruppo) e Società X (capogruppo), in questo caso il CERCA.VERT mi dà solamente il valore della Società X (gruppo), ovvero della società inserita prima in successione. Come faccio ad avere restituito il valore dell’altra società, Società X (capogruppo)?

      Grazie mille

      Francesco 1 mese ago Reply


      • Ciao Francesco. La vedo dura. Il cerca.vert infatti restituisce solo il primo valore.

        Nel caso specifico potresti forse inserire (capogruppo) dopo il secondo asterisco.
        Quindi modificare la tua formula da
        =CERCA.VERT(“*”& “società x” & “*”; A3:D340; 4;0)
        a
        =CERCA.VERT(“*”& “società x” & “*(capogruppo)”; A3:D340; 4;0)

        Però dubito che questo ragionamento possa poi essere applicato anche alle altre tue ricerche.

        samuele 1 mese ago Reply


        • Ti ringrazio. in questo specifico caso ho risolto!! =)

          Francesco 4 settimane ago Reply


    • Grazie mille Francesco. Mi fa proprio piacere!

      samuele 1 mese ago Reply


Inserisci un commento