Come applicare la Funzione Cerca Verticale (CERCA.VERT) di Excel
La Funzione Cerca Verticale consente di cercare un valore (testuale o numerico)
nella prima colonna di una tabella-matrice e restituisce il contenuto delle celle
presenti a destra sulla stessa riga.
Cerca Verticale (CERCA.VERT) è sicuramente una delle
Funzioni più importanti e maggiormente applicate in Excel
e appartiene alla categoria delle Funzioni di Ricerca e
riferimento.
Contenuti del tutorial: La Funzione CERCA VERTICALE - CERCA.VERT
[1] - La Funzione Cerca Verticale - elementi
introduttivi
[2] - La sintassi della Funzione Cerca Verticale - CERCA.VERT
[3] - La Funzione CERCA.VERT - esempio di applicazione
[4] - La Funzione CERCA.VERT - esempio di ricerca con risultato per approssimazione
[5] - Semplificare la Funzione definendo la matrice con un nome definito
(etichetta) - esempio di ricerca
[6] - La Funzione Cerca Verticale spiegata passo per passo con un video
[7] -
Impostare Argomenti Funzione (Fx):
La Finestra di dialogo della Funzione CERCA VERT
[8] - Considerazioni finali e osservazioni su alcuni limiti della Funzione CERCA.VERT
[9] - Download file esempio di impostazione della Funzione
Cerca Verticale
Nota. Gli esempi riportati nel tutorial sono applicati alla Versione Excel 365, ma sono ugualmente riferibili anche alle versioni Excel 2007-2019
La Funzione Cerca Verticale
Cerca Verticale
cerca un valore nella prima colonna di una tabella-matrice e restituisce il contenuto delle celle presenti a destra sulla stessa riga.
Cerchiamo di
semplificare questa Funzione di Excel definendo in primo luogo che cos'è una
matrice-tabella.
La matrice non è altro che una tabella che Excel trattata come un'unica identità
(senza
interruzioni di riga o colonna e senza intestazione).
All'interno di questa Matrice CERCA.VERT può ricercare
i valori solo sulla prima colonna, e
restituire i dati che sono presenti sulla stessa riga.
:: IMPORTANTE. Una volta trovato
un valore
nella prima colonna della matrice, CERCA VERTICALE può restituire i dati della stessa riga
solo
a destra della cella
trovata. Va pertanto tenuto presente che
il valore da ricercare deve trovarsi
sempre nella prima colonna
dell'intervallo.
Ad esempio, se il valore di ricerca
fosse nella colonna C del foglio di
lavoro, (vedi immagine
sottostante) la matrice dovrà iniziare
con C come prima colonna escludendo la colonna
B dove si trova il Codice. Ovviamente in
questo caso non sarebbe possibile avere
la restituzione dei dai dati presenti in
colonna 1.
La sintassi della Funzione Cerca Verticale: CERCA.VERT
La sintassi della Funzione Cerca verticale (nome in inglese della Funzione: VLOOKUP) è la seguente:
=CERCA.VERT(valore;tabella_matrice;indice;[intervallo])
Nella quale:
valore: è il dato che deve essere ricercato nella matrice.
tabella_matrice: è l'area del Foglio di lavoro che contiene i
dati (entro i quali ricercare il valore).
indice: è il riferimento al numero della colonna
della quale si richiede la restituzione del valore.
Esempio: Indice uguale a 2 restituisce il valore presente nella seconda colonna
della tabella_matrice, indice uguale a 3 restituisce il valore della
terza colonna e così via.
intervallo: argomento opzionale. Per questo nella sintassi viene indicato fra parentesi quadrata.
Si tratta di un valore logico che consente di specificare se CERCA.VERT deve trovare una corrispondenza esatta o
per approssimazione. L'intervallo
potrà pertanto essere VERO (ricerca per approssimazione) o FALSO
(ricerca esatta).
Se nell'impostazione della funzione
l'argomento viene omesso Excel presuppone che esso sia impostato come VERO.
:: NOTA. In alternativa a VERO o FALSO è possibile inserire
rispettivamente 1 (uno) oppure 0 (zero)
L'argomento intervallo della Funzione cerca verticale seppur opzionale è
molto importante.
Per chiarire la corretta impostazione di questo argomento nella successiva
parte del tutorial verranno presentati alcuni esempi che spiegheranno come inserire correttamente una
ricerca per corrispondenza esatta o per approssimazione.
La Funzione CERCA.VERT - esempio di applicazione
Esempio. Nella matrice-tabella illustrata nel sottostante immagine, si vuole applicare la Funzione Cerca verticale per cercare i valori Categoria prodotto e Prezzo unitario riferiti ad uno specifico codice prodotto. (Nell'esempio il codice 5 inserito nella cella B13)
Ripartiamo dalla sintassi della Funzione
applicandola all'esempio illustrato nell'immagine.
=CERCA.VERT(valore;tabella_matrice;indice;[intervallo])
valore; è il dato che deve essere ricercato, nell'esempio
proposto è la cella B13 che contiene il codice del prodotto.
tabella_matrice; è la parte del
foglio che contiene i dati,
B3:D11
Indice;
Gli
indici sono le colonne. Nel momento in
cui la Funzione Cerca verticale troverà il codice
di riferimento nella prima colonna della matrice, se verrà indicato
indice 2, verrà
restituita la categoria del prodotto, se verrà indicato
3 verrà restituito il prezzo.
Intervallo; L'intervallo è un valore logico pertanto può essere VERO o FALSO.
Se nella funzione viene omesso Excel presuppone che sia VERO!
Nel caso dell'esempio proposto è fondamentale che vi sia corrispondenza
esatta, quindi andrà definito come FALSO.
Pertanto la Funzione sarà la seguente.
=CERCA.VERT(B15;$B$3:$D$11;2;falso)
Nella Funzione CERCA.VERT, se viene inserito un valore da
ricercare inesistente nella prima colonna della matrice dati, e se come argomento intervallo è impostato
FALSO, la Funzione restituisce il codice di errore #N/D.
(ND = Non Disponibile ovvero la
Funzione non riesce a trovare
il valore richiesto).
Il messaggio di errore #/ND viene restituito anche quando la cella che
contiene il codice da ricercare fosse momentaneamente vuota.
ATTENZIONE: la presenza del
codice di errore #/ND,
può diventare un problema per eventuali operazioni di calcolo.
Per la gestione del codice di Errore #N/D si rimanda al tutoria:
Gestire il
messaggio di errore #N/D
CERCA.VERT: Esempio di una ricerca con risultato per approssimazione
Se il quarto argomento della Funzione CERCA.VERT non
viene definito oppure viene impostato: VERO, e
nella prima colonna della matrice non fosse presente una corrispondenza esatta del
valore ricercato il risultato restituito dalla ricerca sarebbe per corrispondenza approssimativa.
Nella ricerca per approssimazione
Cerca verticale restituisce come risultato il valore più vicino (minore) al codice ricercato.
E' molto importante porre attenzione
a questo argomento!
Per meglio spiegare il funzionamento di una ricerca per approssimazione
si propone, nella successiva immagine, l'esempio di una serie di valori
relativi ad una analisi e un commento di essi descritto
nelle seconda colonna.
L'obiettivo è quello di cercare un risultato nella prima colonna per
avere la restituzione del commento presente in colonna (indice) 2.
Mentre i valori contenuti nella prima colonna della matrice sono dei numeri
interi i valori da ricercare possono avere una parte decimale. Nell'esempio il valore inserito nella cella E3 è uguale a 6,3.
Nella Cella F3 è stata inserita la Funzione:>
=CERCA.VERT(E3;B3:C9;2;VERO)
Il valore che viene restituito dalla Funzione Cerca verticale è quello più
vicino (minore) ossia il risultato riferito al valore 6 = MOLTO BUONO
::Osservazione. Se fosse stata impostata una ricerca esatta (quarto argomento FALSO oppure 0 "zero") il risultato restituito sarebbe stato #N/D
Semplificare la Funzione definendo la matrice con un nome definito (etichetta)
Per semplificare la sintassi della Funzione è consigliabile identificare l'area della matrice (nella quale ricercare le informazioni) attribuendole un nome definito (etichetta).
Costruire una etichetta: una volta selezionata la matrice nella casella nome (presente a sinistra della barra della formula) digitare il nome dell'etichetta (nel caso dell'esempio: listino) e successivamente confermare l'inserimento premendo invio.
Questa operazione equivale al comando Definisci nome presente nella scheda Formule della Barra multifunzione.
Il risultato del'aver creato un
intervallo denominato (etichetta) per
l'area dati della matrice, è che ora
nella sintassi anzichè fare riferimento
alla matrice attraverso la definizione
di una area sarà possibile utilizzare il
nome dell'etichetta. (vedi immagine
sottostante)
Anzichè descrivere la Funzione =CERCA.VERT(B15;B3:D11;2;FALSO)
sarà possibile scrivere
=CERCA.VERT(B15;listino;2;falso)
::Consiglio. Nell'argomento matrice di un Funzione è buona regola utilizzare sempre o un nome definito oppure i riferimenti di cella assoluti. In questo modo l’intervallo di ricerca rimane sempre uguale qualora si trascinasse la Funzione
nelle celle sottostanti.
:: Per un approfondimento su come creare un intervallo denominato (etichetta) si rimanda
al tutorial:
Effettuare ricerche dati con gli Intervalli denominati (Nomi definiti)
La Funzione Cerca Verticale spiegata con un video
Approfondimento: Impostare Argomenti Funzione | La Finestra di dialogo della Funzione CERCA.VERT
Se imputare la Funzione Cerca Verticale può sembrare troppo complicato, rimane sempre
la possibilità di utilizzare la Finestra di dialogo
Argomenti Funzione.
Per accedervi è sufficiente cliccare sul pulsante Fx della Barra della Formula e selezionare la Funzione CERCA.VERT fra le Funzioni Ricerca e Riferimento o fra le Funzioni recenti.
Una volta individuata e selezionata la Funzione CERCA.VERT si aprirà la finestra di dialogo Argomenti funzione.
Nella finestra di dialogo Argomenti Funzione, è sufficiente compilare i campi relativi al Valore; Matrice_tabella; Indice e Intervallo, e automaticamente nella Barra della Formula si comporrà la Funzione CERCA.VERT nella sua corretta sintassi.
Nella finestra di dialogo Argomenti Funzione si devono solo inserire i valori nei campi corrispondenti. Compilare i dati nella finestra di dialogo equivale pertanto a scrivere la corretta sintassi della Funzione =CERCA.VERT(B15;listino;2;falso)
Considerazioni finali e osservazioni su alcuni limiti della Funzione CERCA.VERT
Sicuramente CERCA.VERT è la Funzione di
Ricerca e Riferimento maggiormente
utilizzata in Excel. Si tratta di una
Funzione non semplicissima da applicare
e frequentemente viene impostata in
maniera errata soprattutto nel quarto
argomento (VERO-FALSO).
Oltre a queste difficoltà interpretative
la Funzione Cerca Verticale ha una serie
di limiti.
Il primo è che necessita che il valore
da ricercare sia nella prima colonna
della matrice. Qualora questa
impostazione non fosse possibile bisogna
utilizzare in maniera combinata le due
Funzioni INDICE e CONFRONTA, oppure, se
si dispone di una versione Microsoft 365
della nuova e più completa Funzione CERCA.X
Il secondo limite del CERCA.VERT è che
richiede che i valori nella prima
colonna siano univoci. Se ci fossero due
codici uguali Cerca verticale quando
incontra il primo codice che soddisfa la
ricerca si ferma e restituisce il valore
di colonna
richiesto. Il programma non restituisce
errori che avvertano che esiste un
secondo valore che potrebbe soddisfare la ricerca.
Con il CERCA.VERT è pertanto possibile
effettuare ricerche su valori (normalmente
codici) fra i quali non siano presenti
valori doppi (omocodie). Sarebbe
impensabile applicare il Cerca verticale
su un cognome all'interno di una
anagrafica poichè non sarebbe così remota la
possibilità che vi siano più persone con
lo stesso cognome.
Un terzo limite del CERCA.VERT è nelle
ricerche per approssimazioni, nelle
quali la Funzione restituisce sempre e
solo il valore più piccolo prossimo al
valore di ricerca.
Alcuni di questi limiti sono risolvibili
con l'applicazione combinata di più
Funzioni di Excel (ad. esempio INDICE e
CONFRONTA ma non solo)
Come alternativa al Cerca verticale,
molto interessante è la
nuova Funzione CERCA.X
per un
approfondimento su questa Funzione si
rimanda a:
La nuova funzione CERCA.X destinata a sostituire Cerca Verticale
Link utili di approfondimento Funzioni di Ricerca e riferimento
Novità
!
Con Microsoft365 da marzo 2020 è
disponibile la nuova Funzione CERCA.X questa funziona corregge alcuni
limiti della Funzione CERCA.VERT
Per info si rimanda
alla pagina:
La nuova funzione CERCA.X destinata a sostituire la Funzione Cerca Verticale
01 - Ricerca e Riferimento:
La Funzione CERCA
02 - Ricerca e Riferimento: Gestire il messaggio di errore #N/D
restituito dalla Funzione CERCA.VERT
03 - Ricerca e Riferimento: La Nuova Funzione CERCA.X
04 - Ricerca e Riferimento: La Funzione Cerca orizzontale CERCA.ORIZZ
05 - Ricerca e Riferimento: Le Funzioni CONFRONTA e CONFRONTA.X
06 - Ricerca e Riferimento: Applicare le Funzioni CONFRONTA e INDICE
07 - Ricerca e Riferimento: La Funzione SCEGLI
08 - Ricerca e Riferimento: La Funzione SCARTO
09 - Ricerca e Riferimento:
Le Funzioni RIF.COLONNA e RIF.RIGA
Download file esempio - Impostazione CERCA.VERT
|
|
Indice Generale Argomenti: EXCEL
[Valter Borsato: ottobre - 2014 | Ultimo aggiornamento 09/03/2021]