Funzione CERCA.VERT di Excel

due liste una freccia che relaziona i campi a signifcare il funzionamento della funzione Excel CERCA.VERT

 

CERCA.VERT (Cerca verticale) è la funzione Excel che, più di altre, è in grado di incrementare la "produttività personale", ovvero permette di risparmiare tempo, lavorare senza errori e, nel caso specifico di CERCA.VERT, evitare il fastidioso fenomeno degli "occhi incrociati".

Questa funzione, infatti, è in grado di cercare un valore, contenuto in una lista, in un'altra lista, ma non è tutto, la funzione CERCA.VERT è anche in grado di restituire i valori nelle celle a destra del valore cercato.

 

Se non hai capito, non preoccuparti, con un esempio sarà tutto più semplice

esempio funzionamento CERCA.VERT

Nell'esempio sono presenti due liste,
- la prima (lista.A) che indica, per ogni cliente, la città della sede legale
- la seconda (lista.B) con tutti i comuni d'Italia e il loro codice zona numerico

Ora, per ogni cliente presente nella lista.A devi cercare il comune nella lista.B e riportare il codice trovato sulla lista.A. Un centinaio di clienti, 8000 circa i comuni italiani, insomma un lavoraccio che possiamo far eseguire ad Excel in modo preciso e in pochi secondi.

La funzione che dobbiamo adoperare è:

 

CERCA.VERT(valore;tabella_matrice;indice;intervallo)

Che vogliono dire i parametri?

 

  • valore E' il valore della lista.A che devo cercare nella lista.B.
    • nel nostro esempio è Brescia, il comune della sede legale del cliente, basta quindi inserire il riferimento di cella (C3) al valore da cercare

 

  • tabella_matrice Sono l'insieme delle celle dove sono presenti tutti i possibili valori da cercare ovvero, nel nostro caso, tutti gli 8000 comuni italiani.
  • Ecco le regole che devi conoscere:
    • Il valore che stai cercando (chiave ovvero Brescia) deve trovarsi sempre nella prima colonna di quest'insieme
    • La tabella_matrice non corrisponde per forza al foglio Excel, può essere una parte di esso (nel nostro esempio le colonne da "F" a "G")
    • La tabella_matrice può essere su un diverso foglio di Excel ma sempre nella stessa cartella di lavoro
  • esempio di dati omogenei nelle colonneLe informazioni contenute nelle colonne successive alla prima devono fare riferimento al valore della prima colonna ed essere omogenee per colonna

 

 

 

 

 

 

La funzione CERCA.VERT agisce su una specifica area di ricerca chiamata tabella_matrice come fai a definire il perimetro di quest'area?

- Riferimento area tabella_matrice fissa. >>> B3:D9 <<<

in questo modo definisci staticamente l'area della tabella_matrice. Il perimetro comprende dunque un area fissa, formata da sette righe (dalla 3 alla 9) e tre colonne (dalla B alla D).
La funziona CERCA.VERT limiterà la ricerca dei valori alla sola colonna "B" dalla riga 3 alla 9. Questo tipo di utilizzo è poco flessibile infatti se aggiungi dei valori dovrai correggere tutte le funzioni CERCA.VERT che puntano alla tabella_matrice aggiornata.

Definire staticamente un area può risultare, in casi specifici, vantaggioso. Limitare un area di ricerca evita che una ricerca per colonne coinvolga valori non desiderati. In questo caso è preferibile utilizzare la forma
>>> $B$3:$D$9 <<<
infatti la copia della formula, da una cella ad un'altra, dovrà mantenere immutati i riferimenti alla tabella_matrice.  


- Riferimento area tabella_matrice a colonne. >>> B:D <<<


E' il metodo più utilizzato, in questo modo puoi aggiungere qualsiasi valore, rispettando l'omogeneità dei contenuti della colonna, senza dover aggiornare in continuazione la formula CERCA.VERT con l'ultima riga utilizzata.
Anche in questo caso è una buona idea utilizzare il segno "$" per bloccare i riferimenti di colonna durante la copia di una formula.

 

- Riferimento area tabella_matrice per "Nome".

L'ultimo modo, il più esotico e meno conosciuto, per riferirci ad una specifica area è definire un "nome" da utilizzare come riferimento alla tabella_matrice.

E' un metodo poco adoperato, è il "Nome" stesso a definire l'ampiezza dell'area della tabella_matrice. Se qualcuno fosse interessato può chiedermi direttamente delucidazioni.

 

Nella figura sotto ho utilizzato l'opzione Excel che mi permette di visualizzare le formule al posto dei risultati per mostrarti l'applicazione pratica del CERCA.VERT.

formule cerca.vert

  • Cliente 1 è stata utilizzata una funzione CERCA.VERT con riferimento a colonne
  • Cliente 2 è stata utilizzata una funzione CERCA.VERT con riferimento fisso
  • Cliente 3 è stata utilizzata una funzione CERCA.VERT con riferimento a un "Nome"
  • Cliente 4 è stata utilizzata una funzione CERCA.VERT con riferimento a colonne
  • Cliente 5 è stata utilizzata una funzione CERCA.VERT con riferimento a un "Nome"

 

La tabella_matrice può stare anche su un diverso foglio Excel (ma sempre nella stessa cartella di lavoro)

L'insieme di tutti i possibili valori che un campo può assumere si chiama dominio, è frequente che il dominio si trovi su un altro foglio della stessa cartella; in questo caso il parametro tabella_matrice della funzione CERCA.VERT deve fare riferimento ad una pagina diversa.

Ho spostato i comuni del precedente esempio su un nuovo foglio che ho chiamato "Comuni" e ho modificato le formule per puntare alla nuova tabella_matrice. Come puoi vedere dalle formule CERCA.VERT dell'esempio basta anteporre alla  tabella_matrice il nome del foglio che quindi diventerà
=CERCA.VERT(B3;Comuni!$A:$B;2;FALSO)  


Indice
Il parametro indice stabilisce quale valore, a destra del dato trovato, deve essere restituito e visualizzato dalla funzione CERCA.VERT.
Con "1" restituirebbe il valore stesso, con "2" il valore della cella a destra, con "3" il valore di due celle a destra e così via.


Quindi il parametro indice non indica il riferimento numerico della colonna

ma quante colonne a destra del dato cercato (e trovato)

si trova il risultato da visualizzare



Intervallo
è l'ultimo parametro da indicare, metti FALSO.
Penso di avere adoperato VERO una sola volta in vita mia; VERO serve per trovare il valore più vicino a quello del valore di ricerca, se ti serve capire come usarlo lascia un commento che ti risponderò.


Approfondimento sulla funzione CERCA.VERT (CERCA VERTICALE)

Al termine di questo articolo due brevi argomenti correlati in grado di massimizzare la tua produttività personale legata a questa funzione Excel.

Gestione delle chiavi multiple con CERCA.VERT
E' abbastanza frequente che un solo valore non sia sufficiente ad identificare univocamente un elemento, si parla allora di chiavi multiple.

Pensa ad un condominio di 8 piani su ogni piano sono presenti 4 appartamenti. Se dovessi censire le abitazioni non ti basterebbe dire l'appartamento al 4 piano, dovresti aggiungere un ulteriore elemento che identifica l'abitazione all'interno del piano stesso, dovresti quindi dire:

"appartamento piano 4 lato Nord"



ora sì che hai esattamente identificato l'appartamento.
La funzione CERCA.VERT non è in grado di gestire la ricerca di valori chiavi in due o più celle,  è necessario ricorrere a qualche "trucco" per aggirare questo ostacolo.


esempio cerca.vert con chiavi valore delle chiavi composto da più campi

  • Nella colonna "C" definisci la chiave univoca che ottieni concatenando il piano al lato (ovvero unisci le chiavi), fai la stessa cosa nella cella H6. In questo modo hai in un'unica cella il valore che identifica l'appartamento.
  • Ora che hai ricondotto una chiave a valori multipli in una chiave formata da un unico valore la puoi utilizzare come parametro valore della funzione CERCA.VERT.


L'esempio illustra visivamente le relazioni tra le formule; modificando i valori delle celle H4 e H5 con le combinazioni piano/lato il foglio calcolerà l'importo da pagare in relazione ai millesimi di occupazione del singolo appartamento.

 

Approfondimento sulla funzione VAL.NON.DISP con cerca.vert

Excel fornisce un insieme di funzioni per testare le condizioni di errore, la funzione VAL.NON.DISP è una di queste e si occupa di verificare la condizione di "valore non disponibile".
Nel caso in cui CERCA.VERT non trovi il valore nella prima colonna della tabella_matrice restituirà l'errore #N/D (non disponibile) che può essere esaminato dalla funzione VAL.NON.DISP che lo trasformerà in una condizione vero/falso.

Guarda in pratica:

Con questa funzione, nel caso il valore cercato con la funzione CERCA.VERT non sia presente, visualizzo "NON Trovato" altrimenti se presente "Trovato"
=SE(VAL.NON.DISP(CERCA.VERT(A3;B3:B10;FALSO));"NON trovato";"Trovato";FALSO)

oppure nel caso (frequente) in cui vorrai mostrare il risultato solo se hai trovato una corrispondenza
=SE(VAL.NON.DISP(CERCA.VERT(A3;$C:$E;FALSO));"";"Famiglia " & CERCA.VERT(A3;$C:$E;FALSO))

Attachments:
Download this file (CercaVert.xls)Esempi di cerca-vert.xls[Esempi pratici della funzione Excel CERCA.VERT]41 Kb
Download this file (CercaVerticaleIndiceComposto.xls)CercaVerticaleIndiceComposto.xls[Esempio quesito di Milessio con indice multiplo impostato attraverso una formula]19 Kb
Download this file (CercaVerticaleNomeIntervalloDinamico.xls)CercaVerticaleNomeIntervalloDinamico.xls[Risoluzione del quesito di Cristina per rendere dinamico l'intervallo del CERCA.VERT]25 Kb

Commenti  

 
0 #36 Alessandro 2014-04-18 13:04
Citazione barbara:
salve, io ho un'altro problema e non so piu dove sbattere la testa. ho provato varie formule ma senza alcun risultato.
Vi spiego:
Ho due colonne: colonna B = "anno" e colonna C = "codice". Alcune celle sono vuote.
Vorrei tramite una formula sapere per es. nell'anno 2009 quante volte si ripete il codice 295.
Sono gg che ci provo con diverse formule ma non trovo la soluzione.
Grazie anticipatamente per l'aiuto ;-)


Con l'indicatore $ davanti alla lettera di collonna e al numero di riga.
Citazione
 
 
0 #35 Alessandro 2014-04-18 13:03
Citazione barbara:
salve, io ho un'altro problema e non so piu dove sbattere la testa. ho provato varie formule ma senza alcun risultato.
Vi spiego:
Ho due colonne: colonna B = "anno" e colonna C = "codice". Alcune celle sono vuote.
Vorrei tramite una formula sapere per es. nell'anno 2009 quante volte si ripete il codice 295.
Sono gg che ci provo con diverse formule ma non trovo la soluzione.
Grazie anticipatamente per l'aiuto ;-)


Con la funzione CONTA.PIU.SE puoi specificare i parametri per la colonna B e C
Citazione
 
 
0 #34 barbara 2014-03-18 10:46
salve, io ho un'altro problema e non so piu dove sbattere la testa. ho provato varie formule ma senza alcun risultato.
Vi spiego:
Ho due colonne: colonna B = "anno" e colonna C = "codice". Alcune celle sono vuote.
Vorrei tramite una formula sapere per es. nell'anno 2009 quante volte si ripete il codice 295.
Sono gg che ci provo con diverse formule ma non trovo la soluzione.
Grazie anticipatamente per l'aiuto ;-)
Citazione
 
 
0 #33 Marco 2014-03-13 16:12
Buonasera,
come faccio con cerca vert, nel copiare la formula, a bloccare i riferimenti della matrice tabella lasciando avanzare solo il valore?
ringraziandovi in anticipo vi porgo distinti saluti.
Citazione
 
 
+3 #32 LORENZO 2014-01-27 17:12
salve io ho un problema diverso...
ho un elenco di autovetture e i loro prezzi di vendita e io vorrei che il programma digitando il nome del veicolo mi ricercasse tutti i mezzi con lo stesso nome e mi dicesse anche il loro prezzo.
grazie.
Citazione
 
 
+2 #31 nicola 2014-01-09 11:52
Salve,
in una colonna excel ho 22.000 numeri telefonici di clienti. Devo eliminarne 3200 circa perchè errati o sisattivati.
In un altro foglio excel ho questi 3200 numeri errati.
Che formula posso utilizzare per eliminare questi numeri tutti in
una volta?
Grazie
Citazione
 
 
+1 #30 Emilio Novelli 2013-12-30 17:12
Citazione Roberto:
Se in una matrice ho 2 valori di riferimento identici ad es:
A1= data B1= Compleanno
A2= data B2= Onomastico
con A1=A2= data
con il criterio di ricerca "data" in CERCA.VERT mi darà solo il risultato "Compleanno".
Come faccio ad arrivare al risultato "Onomastico" ?

Grazie, Roberto

Ciao. Non si può. Nella cella ci sta un solo valore e allora xls quale dovrebbe mettere? Compleanno o onomastico?
Qualche tempo fa ho gestito una problematica simile utilizzando una funzione utente e concatenavo i due - o più -risultati separandoli con ;
Citazione
 
 
0 #29 Roberto 2013-12-30 16:11
Se in una matrice ho 2 valori di riferimento identici ad es:
A1= data B1= Compleanno
A2= data B2= Onomastico
con A1=A2= data
con il criterio di ricerca "data" in CERCA.VERT mi darà solo il risultato "Compleanno".
Come faccio ad arrivare al risultato "Onomastico" ?

Grazie, Roberto
Citazione
 
 
+7 #28 chiara 2013-10-01 09:29
Ciao, scusate il disturbo ma sono veramente disperata, non capisco dove sbaglioooo!!! :oops: :sad: :cry:

allora, vi spiego:
ho 2 elenchi dove nell'elenco A ci sono tutti i miei clienti e nell'elenco B ci sono le aziende della zona.

come faccio ad eliminare i miei clienti dall'elenco B???
ho provato con il cerca vert ma non ci riesco.. :cry: :cry: :cry:
Citazione
 
 
0 #27 icaruslayer 2013-06-18 14:16
Grazie!!! :lol: :lol: :lol: :lol: :lol: :lol: :lol:
Citazione