Quante volte hai utilizzato un listino per convertire il tuo codice prodotto in un prezzo? Quante volte hai cercato cercato di convertire il tuo peso corporeo in gocce di un medicinale? Sicuramente molte, e certamente avrei scorso il tuo dito indice tra pagine e pagine di righe e colonne… La funzione CERCA.VERT in Excel ti permette di automatizzare questa conversione indipendentemente da quante migliaia di righe sia composto il tuo listino, o, più genericamente, la tua matrice di conversione. Il tutto in un click!
Andiamo alla scoperta del CERCA.VERT con un semplice ed intuitivo esempio. Realizziamo, infatti, un foglio di calcolo in grado di convertire i codici prodotto nei relativi prezzi, usando una matrice di conversione. Per fini didattici concentreremo tutti gli elementi su di un unico foglio anche se sarebbe opportuno avere la matrice di conversione in un apposito e riservato foglio di Excel.
Funzione Cerca.Vert in Excel – Mummu Academy
Dobbiamo ipotizzare di inserire gli articoli acquistati tra quelli disponibili in colonna B e che Excel usi la funzione Cerca.Vert per convertirli in un prezzo tramite la matrice di conversione I2:J5.
Possiamo usare la CONVALIDA DATI già vista in altro articolo per blindare l’inserimento dati in modo che nella colonna dei prodotti possano essere immessi soltanto i valori effettivamente convertibili. Se ci pensate, sarebbe un bel problema cercare di vendere un prodotto senza sapere quale possa essere il suo prezzo.
Mediante la convalida dati facciamo apparire un menù a tendina che permette la scelta di uno dei quattro prodotti disponibili, attingendo all’intervallo I2:I5. Per chi non conoscesse la “convalida dati”, può tranquillamente inserire manualmente in B2:B11 dei valori casuali scegliendo tra Alfa, Beta, Gamma ed Omega per ottenere il risultato seguente.
ATTENZIONE AGLI SPAZI!
Nell’immissione dei dati si deve stare particolarmente attenti a non inserire degli spazi alla fine di quanto digitato. Si ricorda, infatti che gli spazi sono caratteri come tutti gli altri, che, in modo silente, aumentano il numero dei caratteri presenti in una cella. Una cella che contiene una stringa di quattro caratteri non potrà mai essere uguale ad una che ne contiene un numero diverso. Questa precisazione è fondamentale per capire meglio alcuni messaggi di errore #N/D apparentemente inspiegabili. Ancora una volta la CONVALIDA DATI diventa un prezioso alleato per impedire errori di digitazione.
Funzione Cerca.Vert in Excel – Mummu Academy
La funzione CERCA.VERT dovrà trasformare i valori presenti in B2:B11 nei prezzi disponibili in J2:J5.
SINTASSI LOGICA DELLA FUNZIONE CERCA.VERT IN EXCEL
Immaginiamo di dover fare la conversione manualmente, usando carta e penna sulla stampa della figura precedente: prendiamo il valore presente in B2 e lo andiamo a cercare nella prima colonna a sinistra della matrice di conversione I2:J5. Una volta ottenuta trovato il matching, prendiamo come valore di conversione il valore che si trova sulla stessa riga di matching, ma sulla colonna numero 2 della matrice di conversione (MDC). In assenza di matching (impossibile se si usa la convalida dati nel modo corretto) vogliamo forzare la funzione CERCA.VERT a restituire un valore di errore #N/D.
Dal paragrafo precedente emerge che la funzione ha bisogno di quattro parametri:
- Valore per il quale desidero la conversione in altro valore
- Coordinate della MDC
- Numero di colonna che nella MDC ospita i valori di conversione
- Parametro relativo all’approssimazione, che attiva o meno il risultato #N/D in caso di conversione impossibile
I quattro punti precedenti meritano due approfondimenti in particolare.
- Le coordinate della MDC devono essere prese considerando che la prima colonna a sinistra della MDC deve essere quella in cui si scorre alla ricerca del matching. Nel nostro esempio ciò significa che la prima colonna a sinistra della MDC deve essere quella in cui sono elencati i prodotti vendibili.
- Il “parametro di approssimazione” attiva #N/D se impostato su FALSO (numericamente lo 0). Se si immette il parametro VERO (numericamente 1) la funzione restituisce una conversione approssimata a quella più vicina, e precedente, da un punto di vista ordinale a quella desiderata. In altre parole, nel nostro esempio, se cercassimo di convertire il valore Lambda, il risultato della conversione sarebbe 55 in quanto il valore disponibile, in prima colonna a sinistra di MDC, immediatamente precedente a quello desiderato sarebbe quello relativo a GAMMA.
La sintassi della funzione, da un punto di vista logico-umano è la seguente:
=CERCA.VERT(cosa voglio convertire; coordinate di MDC da usare; n° di colonna che in MDC ospita la conversione; parametro di approssimazione)
Sostituendo punto punto le parole “umane” con i valori “digeribili” da Excel si ha quanto segue:
=CERCA.VERT(B2;$I$2:$J$5;2;FALSO)
Funzione Cerca.Vert in Excel – Mummu Academy
Copiando verso il basso la formula, si otterranno tutti i valori di conversione.
Funzione Cerca.Vert in Excel – Mummu Academy
Con pochi altri passaggi l’esercizio può essere completato.
LA FUNZIONE CERCA.VERT IN EXCEL ED IL PARAMETRO DI APPROSSIMAZIONE
Se provassimo ad inserire LAMBDA come valore in B2, il risultato della conversione sarebbe #N/D
Funzione Cerca.Vert in Excel – Mummu Academy
Se provassimo ad inserire LAMBDA come valore in B2, ed a cambiare il parametro di approssimazione in VERO, il risultato sarebbe il seguente:
Funzione Cerca.Vert in Excel – Mummu Academy
Notiamo che 55 è il valore di conversione di GAMMA ed un risultato approssimato, secondo quanto visto prima, e riferito ad una voce non convertibile direttamente.
Se ti è piaciuto questo articolo, ne troverai altri al seguente link.
Se sei uno studente del corso base od avanzato di Excel di Mummu Academy accedi alla tua zona di E-Learning, potrai disporre di utili approfondimenti, esercizi e materiali di studio.
