In questo tutorial esaminiamo il funzionamento della funzione CERCA.VERT e cerchiamo di farci un’idea delle sue possibili applicazioni. Con questo strumento ordiniamo ad Excel di ricercare “in verticale” un valore indicato dall’utente (una variabile) all’interno della prima colonna di una tabella. Al valore trovato corrisponde una riga, e dalla riga così selezionata possiamo estrapolare un campo indicato con la variabile indice. Il risultato restituito dalla formula sarà dunque il contenuto della colonna indice sulla riga selezionata dal valore inserito. Sintassi della funzione CERCA.VERT
=CERCA.VERT(valore;matrice_tabella;indice;intervallo)
Figura 1. Finestra degli argomenti della funzione
=> Applicazioni aziendali della funzione FREQUENZA di Excel
- L’argomento “valore” è il valore da ricercare nella “prima” colonna della matrice (tabella); esso può essere un numero ma anche una etichetta o una data.
- L’argomento “matrice_tabella” è l’intervallo di celle contenente la tabella di riferimento.
- L’argomento “indice” indica la colonna della tabella_matrice dalla quale deve essere restituito il valore della cella che si trova sulla stessa riga di “valore” (se uguale a 1 restituisce il valore nella prima colonna di tabella_matrice; se uguale a 2 restituisce il valore nella seconda colonna di tabella_matrice e così via).
- Infine, l’argomento “intervallo” è un valore logico: se è omesso oppure è pari a VERO verrà restituita una corrispondenza approssimativa nel caso in cui l’argomento “valore” non è presente nella prima colonna della tabella (restituisce il valore più grande che sia minore di “valore”); se è FALSO (normalmente si utilizza quest’ultimo per i prospetti di tipo aziendalistico) restituirà un valore solo se c’è una corrispondenza esatta.
Cerchiamo di chiarire meglio come il funzionamento di CERCA.VERT con un qualche esempio pratico.
Listino prezzi
Supponiamo di avere la tabella sottostante (il listino prezzi di una pizzeria, in una versione semplificata) riprodotta in un foglio di calcolo.
Figura 2. Listino prezzi di una pizzeria
CERCA.VERT automatizza il campo “Descrizione” nelle 4 righe. Osserviamone il comportamento.
- =CERCA.VERT(100;A2:C5;2;FALSO)restituisce, nella cella l’etichetta “Coperto”;
- =CERCA.VERT(100;A2:C5;3;FALSO) restituisce il valore 2,00;
- =CERCA.VERT(250;A2:C5;3;VERO) o =CERCA.VERT(250;A2:C5;3) restituiscono il valore 7,00 (non essendo stato trovato il valore 250 nella prima colonna della tabella viene restituito l’indice corrispondente al valore più grande, minore di 250, ovvero 200);
- =CERCA.VERT(250;A2:C5;3;FALSO) non restituisce alcun valore, o meglio restituisce il “messaggio di errore” #N/D in quanto non è stato trovato il valore 250 nella prima colonna della tabella_matrice.
Costruire un prospetto
Chiarito il funzionamento, vediamo ora dove collocare tale funzione all’interno di un prospetto. Poiché è buona regola modificare le formule di un prospetto solo in casi eccezionali, l’argomento “valore” della funzione CERCA.VERT deve fare riferimento ad una cella “esterna”, nella quale inserire, di volta in volta, il codice necessario, mentre la funzione va inserita dove si intende visualizzare il dato estrapolato dalla tabella di origine. Ma, ancora una volta, chiariamo i concetti teorici con un esempio pratico. Supponiamo di voler costruire la parte tabellare del prospetto riprodotto in figura estrapolando i dati dal Listino Prezzi utilizzato nell’esempio precedente e collocato all’interno della stessa cartella su di un foglio di calcolo rinominato “Prodotti”.
Figura 3. Prospetto dei prodotti
Le celle della colonna A dovranno contenere i codici dei prodotti e, pertanto, dovranno far parte dell’argomento “valore” della funzione CERCA.VERT che collocheremo nelle celle della colonna C e D.
Ad una prima semplice analisi sembrerebbe sufficiente utilizzare:
- in C2 la formula =CERCA.VERT(A2;Prodotti!$A$1:$C$5;2;FALSO)
- in D2 la formula =CERCA.VERT(A2;Prodotti!$A$1:$C$5;3;FALSO)
Con queste formule ordiniamo ad Excel di trovare il codice, che digiteremo nella cella A2 del prospetto, all’interno della prima colonna della tabella presente nella scheda Prodotti e di restituirci (argomento “indice”) il valore inserito nella stessa riga ma nella seconda colonna (Descrizione) o il valore (seconda formula) presente nella terza colonna (Prezzo). L’utilizzo di FALSO come “intervallo” consente, nel caso in cui il “codice prodotto” digitato non fosse presente in archivio, di restituire il messaggio di errore #N/D! e quindi il suo utilizzo è un utile accorgimento per evitare di visualizzare un dato in sostituzione di un altro! L’utilizzo dei riferimenti assoluti (Prodotti!$A$1:$C$5 e non Prodotti!A1:C5) consente, invece, di copiare le formule verso il basso senza doverle trascrivere nuovamente.
Utilizzare la funzione “SE”
Spesso accade che non tutte le righe di un prospetto vengano “riempite”, dobbiamo superare un piccolo problema. Quando una cella della colonna A è vuota, viene restituito il messaggio di errore #N/D! nella rispettiva cella della colonna C e D, ma anche nella colonna E collegata alla colonna D dalla formula che consente di calcolare l’Importo per riga. Il messaggio di errore indica appunto che Excel non è stato in grado di trovare alcun valore nella tabella della scheda Prodotti e, d’altronde, non poteva essere altrimenti, visto che non è stato inserito nulla nella cella della colonna A!). Questo inconveniente, che tra l’altro non ci permetterebbe neanche di calcolare il totale Importo attraverso la formula =SOMMA(E2:E5), può essere facilmente superato inserendo la funzione CERCA.VERT all’interno di una funzione SE, ovvero scrivendo:
- in C2 la formula
=SE(A2=””;””;CERCA.VERT(A2;Prodotti!$A$1:$C$5;2;FALSO)) - in D2 la formula
=SE(A2=””;0;CERCA.VERT(A2;Prodotti!$A$1:$C$5;3;FALSO))
Attraverso le funzioni SE si chiede ad Excel di verificare se la cella della colonna A è vuota (test) e, nel caso affermativo (se la condizione è vera) di restituire una etichetta vuota nella colonna Descrizione o (seconda formula) di restituire uno 0 (zero) nella colonna Prezzo, altrimenti (se la condizione è falsa) di restituire il risultato della funzione CERCA.VERT.
Figura 4. Usare la funzione “SE” con CERCA.VERT
L’utilizzo combinato della funzione CERCA.VERT con la funzione SE è necessario solo nei casi in cui l’argomento “valore” della prima funzione potrebbe rimanere vuoto quindi, principalmente, nella parte tabellare dei prospetti quantitativi. In alcuni casi la funzione CERCA.VERT può essere utilizzata anche singolarmente. Supponiamo, ad esempio, di voler estrapolare da un database-clienti i dati per creare in automatico (al solo inserimento del codice di riferimento) l’indirizzo del destinatario all’interno di un prospetto fattura; in questo caso, poiché il codice cliente non potrà essere vuoto (o meglio, la fattura dovrà essere indirizzata obbligatoriamente ad un cliente) potrebbe risultare superfluo racchiudere la funzione CERCA.VERT all’interno della funzione SE.
Gli ambiti di applicazione
Per concludere, evidenziamo alcuni degli ambiti di applicazione della funzione CERCA.VERT. La funzione permette di automatizzare molte operazioni di inserimento dati: si potrebbero, ad esempio, creare delle fatture completamente automatizzate dove sarà sufficiente inserire solo i codici (cliente, banca di appoggio, prodotti, etc.) e le quantità vendute oppure realizzare dei prospetti per la busta paga dei dipendenti, delle note di accredito, delle note di addebito, e tutti quei prospetti quantitativi che traggono la maggior parte dei dati da database già presenti in azienda. Non solo. La funzione potrebbe essere utilizzata anche per estrapolare dei dati da tabelle create appositamente e che variano in continuazione. Si pensi, ad esempio, ad un prospetto per il calcolo dell’IRPEF: si potrebbe creare una tabella contenente gli scaglioni IRPEF con le relative percentuali di imposta (da aggiornare annualmente in base a quanto stabilito dalla Legge Finanziaria) e collegare tale prospetto attraverso la funzione CERCA.VERT al prospetto principale.
