Excel: utilizzo delle funzione cerca verticale

In questa newsletter si parla ancora del Cerca.Vert, una delle funzioni maggiormente utilizzate.
La domanda legittima che viene posta spesso la seguente: per quale motivo si mette 0 oppure Falso come argomento finale della funzione?
Esiste un’alternativa? Cosa accade se ci si dimentica di mettere 0 oppure Falso?
Nel solco della logica binaria dell’informatica, esiste una possibilità alternativa, che consiste nel digitare 1 oppure Vero o addirittura lasciare vuoto l’argomento Intervallo.
Purtroppo la descrizione della funzione è fuorviante. Nella finestra “Argomento funzioni” l’argomento viene formattato senza grassetto, a significare che è un argomento facoltativo che può essere omesso, ma la sua omissione non è un fattore secondario.
Vediamo un esempio. Nell’immagine sottostante tramite il Cerca.Vert si stanno associando dei codici alle rispettive denominazioni dei punti vendita, per popolare una tabella molto lunga.
Uno dei codici che si sta cercando (cella B4 ovvero codice 107) non esiste nella matrice tabella ovvero nella tabella di ricerca.
L’argomento Intervallo è impostato sullo 0 e, pertanto, si cerca la corrispondenza esatta.
L’errore N/D suggerisce che il codice non è disponibile e quindi non è presente nella tabella di ricerca.



Ma cosa accade se al posto di 0 o Falso si mettono 1 oppure Vero o si omette l’argomento? La figura illustra le conseguenze di tale azione.



Come ha ragionato la funzione? Nel caso di 1/vero/argomento omesso il Cerca.Vert agisce come corrispondenza approssimativa.
Se il valore cercato non viene trovato, appena un valore presente supera quello che si sta cercando, il Cerca.Vert torna su e restituisce il valore che si trova immediatamente sopra.
In questo caso, nella tabella di ricerca il codice 107 non esiste ed appena la funzione arriva al codice 108 che è di valore superiore a 107, torna sopra e restituisce ciò che trova in corrispondenza del codice 106 ovvero il punto vendita F-Ennery 57.
La domanda posta inizialmete diventa ancora più legittima: quando può essere utile una funzione Cerca.Vert che non restituisce una corrispondenza esatta ma approssimativa.
Mai sentito parlare di Se nidificato? Croce e delizia di tanti utenti! Cosa fa il Se nidificato? Pone rimedio ad un limite del Se che accetta solo una singola condizione e due risultati.
Quando i risultati possibili sono 2 o più di due occorre nidificare la funzione Se dentro alla funzione Se, tanti quanti sono i risultati.
Nell’esempio illustrato i risultati sono 7 ovvero le 7 percentuali di detrazioni in base agli scaglioni relativi agli stipendi percepiti ed illustrati nella tabellina a sinistra.
A destra è mostrato il risultato della la funzione Se nidificata che è stata immessa nella cella relativa allo stipendio del primo dipendente per ottenere la percentuale applicata.
La funzione è riportata sotto all’immagine.



SE(B2>=45000;$G$8;SE(B2>=40000;$G$7;SE(B2>=35000;$G$6;SE(B2>=30000;$G$5;SE(B2>=25000;$G$4;SE(B2>=20000;$G$3;$G$2))))))
La funzione è molto utile per comprendere la logica della nidificazione ma risulta complessa da applicare e pertanto una soluzione più agevole sarebbe apprezzabile.
Tale soluzione è il Cerca.Vert con corrispondenza approssimativa.
La base è la tabella gialla mostrata sopra a sinistra. Quando la corrispondenza è esatta, lo stipendio del primo dipendente (€ 15.451,00) non viene trovato e la funzione restituisce errore.
Per ottenere lo stesso risultato del Se nidificato in modo più semplice occorre impostare la corrispondenza del Cerca.Vert come approssimativa ovvero 1 o Vero al posto di 0 o Falso.
Come si comporta la corrispondenza approssimativa? Cerca il valore ed appena arriva ad un valore che supera quello cercato torna immediatamente sopra e restituisce ciò che si trova in corrispondenza del valore inferiore più vicino a quello cercato.
Un esempio chiarisce più di mille parole. Prendiamo proprio lo stipendio sopra citato: € 15.451,00. Il primo valore che la funzione incontra dopo lo 0 è € 20.000.
Questo valore già supera quello cercato (20.000 è superiore a 15.451) per cui la funzione torna su e restituisce ciò che si trova accanto allo 0 ovvero 1,50%.
Guarda caso è proprio la stessa % di detrazione restituita dalla funzione Se.
Ecco come si presenta allora la tabella dopo l’immissione del Cerca.Vert:



La funzione presente a partire dalla cella D2 è la seguente: CERCA.VERT(B2;$F$2:$G$8;2;1).
Come si può notare è molto più semplice e meno lunga del Se nidificato. La funzione si interpreta nel modo seguente:

• La cella B2 contiene il valore da cercare: lo stipendio del primo dipendente.

• La tabella F2:G8 è quella di ricerca (quella colorata di giallo). L’intervallo è bloccato con riferimento assoluto perché deve rimanere fisso in tutte le celle alle quali va estesa la funzione.

• Il numero 2 indica la colonna della tabella di ricerca in cui si trova il valore che deve esssere restituito in corrispondenza di quello cercato. La tabella ha due colonne (F e G) e pertanto G è la seconda colonna.

• Il numero 1 finale indica che la corrispondenza deve essere non esatta ma approssimativa. Al posto di 1 si poteva anche scrivere Vero oppure lasciare direttamente vuoto l’ultimo argomento.

Questo esempio semplice ma funzionale ha illustrato in modo abbastanza chiaro a cosa serve la corrispondenza approssimativa nella funzione Cerca.Vert. Se ne è compresa la funzionalità e sua importanza?