Esempio di cerca verticale su più fogli

Esempio pratico sull’utilizzo del cerca verticale su più fogli.
Da un elenco di vini recuperiamo il prezzo unitario basandoci sul codice del vino

Partiamo da due fogli: listino prezzi e ordini.

Listino prezzi: elenco dei vini con varie informazioni, tra cui codice, nome e prezzo unitario.

listino prezzi per cerca vert

Ordini: elenco che nel tempo si popola degli ordini dei vini registrando il codice del vino, la data dell’ordine e il numero delle bottiglie

elenco ordini

Obbiettivo: nel foglio Ordini scrivere una formula che ci permetta di calcolare il prezzo totale sulla base del codice vino ordinato e del relativo prezzo unitario.

Soluzione: con il cerca vert troviamo il prezzo unitario del vino ordinato e moltiplichiamo per il numero di bottiglie.

I passaggi sono i seguenti:

 A  Ci posizioniamo sul foglio Ordini e scriviamo (o richiamiamo dall’elenco delle funzioni) il CERCA.VERT

cerca vert passaggio 1

 B  Popoliamo i parametri del cercavert specificando il valore da cercare (A4), la matrice tabella (l’elenco dei vini del foglio ‘Listino prezzi’), il numero della colonna del prezzo unitario (5) e il fatto che vogliamo una corrispondenza esatta (0)

cerca vert parametri

La formula è quindi:

=CERCA.VERT(A4;’Elenco vini con prezzi’!A$4:E$69;5;0)

Da notare che usare il cerca vert su fogli diversi non è diverso rispetto ad utilizzarlo su un unico foglio. Excel infatti nella matrice tabella scrive il nome del foglio prima dell’intervallo delle celle.

 C  Moltiplichiamo poi il risultato per il numero di bottiglie, posto nella cella accanto a quella dove stiamo inserendo la formula

prezzo per numero bottiglie

Questo quindi il risultato finale:

risultato finale

Veniamo ora ad alcune domande che potrebbero sorgerci rispetto a quanto trovato finora.

Se io mi preparo la formula nella prima cella e poi trascino per qualche decina di righe in modo da poter in futuro scrivere le informazioni del nuovo ordine e veder calcolato in automatico il prezzo ottengo #N/D nelle righe dove non ho ancora inserito ordini. Perché? Come posso evitarlo?
Nel capitolo 2 del tutorial abbiamo visto che l’errore #nd indica che il cerca vert non riesce a trovare il codice che stiamo cercando. In questo caso è come se stesse cercando un codice inesistente.

errore se codice mancante

Possiamo evitare che scriva #N/D utilizzando la funzione SE.ERRORE ed impostando il valore restituito in caso di errore uguale a stringa vuota

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);””)

se errore stringa vuota

Se io scrivessi un codice di vino non presente nel listino prezzi?
In questo caso il cerca verticale non trova il codice e mi restituisce il risultato #N/D.

errore-nd

Potremmo gestire la cosa informando dell’assenza di quel codice:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);”codice non presente nel listino”)

errore nd personalizzato

Se noi avessimo l’esigenza di attribuire un prezzo standard a tutti i vini che non sono presenti nel ‘Listino prezzi’? Questo perché magari ipotizzo che se il codice non c’è nel listino, il vino è di quelli “sfusi” che vendiamo a 5 euro a bottiglia.

Potremmo attribuire il prezzo direttamente nel parametro se_errore, scrivendo:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale (2)’!A11;’Elenco vini con prezzi’!A$4:E$69;5;0);5)

prezzo costante se codice non trovato

Se invece che scrivere un codice di vino non presente nel listino prezzi sbagliassimo a scrivere il codice? Potremmo ad esempio scrivere 59ù al posto del 59, invece che pigiare il tasto INVIO pigio il tasto acccanto… Come fare per capire che quello è un errore di digitazione?

Potremmo fare due tipi di controlli.
1. il valore è numerico? se non lo è c’è qualcosa che non va
2. il valore è numerico ma è superiore al numero del codice vino più alto?

1. il valore è numerico?
Con la funzione TIPO verifichiamo se il dato è numerico (avremmo il risulato 1) o meno. Con la funzione SE diciamo che se non è numerico il codice è errato.

=SE(TIPO(A8)>1;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

codice non numerico

2. il valore è numerico ma è superiore al numero del codice vino più alto?
Ipotizziamo che il valore più alto sia 1000. Con un SE valutiamo se il codice è superiore o meno a 1000.

=SE(A8>1000;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

codice errato se maggiore mille

Da notare che questa scrittura risolverebbe anche il controllo 1. Excel valuterebbe infatti un codice testuale come superiore a 1000 ed a qualsiasi altro numero.

Guida PDF su CERCA.VERT

Vuoi scoprire tutto sulla funzione CERCA.VERT?

Diventa un esperto con la mia guida pdf dedicata a CERCA.VERT, CERCA.X, INDICE e CONFRONTA.

Con esempi pratici e video di approfondimento.

Scopri la guida
Guida in PDF su CERCA.VERT