L’obiettivo è calcolare lo sconto per prodotto e per quantità utilizzando la tabella degli sconti a destra nella cartella di lavoro. Lo scopo della tabella degli sconti è quello di consentire a ciascun prodotto di avere sconti diversi a seconda della quantità acquistata. Si noti che le ciambelle hanno uno sconto diverso per una quantità di 24. Anche gli sconti degli altri prodotti possono essere personalizzati.
Potete scaricare gratuitamente il workbook utilizzato come esempio.
Questo è un classico problema di ricerca bidirezionale. La formula deve eseguire una ricerca con corrispondenza esatta sul nome del prodotto e con corrispondenza approssimativa sulla quantità. Notare anche che non tutti i prodotti elencati nella colonna B vengono visualizzati nella tabella degli sconti. Ciò significa che dobbiamo anche gestire il caso in cui il prodotto non venga trovato (ovvero il caso che un certo prodotto non preveda uno sconto quantità).
Descrizione del foglio di lavoro
Una breve descrizione del foglio di lavoro usato nell’esempio. Come vedete il foglio si compone di due tabelle: quella di sinistra contiene i prodotti (colonna B), la quantità acquistata (colonna C), il prezzo unitario (colonna D), lo sconto applicato (che verrà calcolato con la formula che vedremo, colonna E), la formula applicata per ciascuna cella della colonna sconto (colonna F), il totale per l’acquisto di ciascun prodotto considerato lo sconto (colonna G); la tabella di destra, dove vengono elencati gli sconti applicati per alcuni prodotti in base alla quantità acquistata.
Naturalmente la colonna F è stata aggiunta esclusivamente per ragioni didattiche.
Calcolo dello sconto per ciascun prodotto: la funzione CERCA.X
Come si vede in colonna F, la formula che usiamo nella cella E5 è la seguente:
=CERCA.X(B5;Prodotti;CERCA.X(C5;Quantità;Sconto;0;-1);0)
Diciamo subito che abbiamo definito alcuni nomi per gli intervalli più importanti:
Prodotti= I6:I12
Quantità= J5:M5
Sconto= J6:M12
Se non sapete come assegnare nomi a celle e intervalli di celle, potete vedere qui.
Come si vede dalla formula, la funzione CERCA.X viene usata due volte: una, internamente, serve a trovare gli sconti data una certa quantità e una, esterna, serve a individuare lo sconto tra i precedenti che corrisponde a quel prodotto. Non preoccupatevi, spiegheremo tutto passo passo.
Sintassi generale della funzione CERCA.X
Cominciamo dalla funzione CERCA.X utilizzata internamente (annidata, in gergo tecnico):
CERCA.X(C5;Quantità;Sconto;0;-1)
e ne confrontiamo gli argomenti con quelli della funzione generica CERCA.X:
=CERCA.X(valore;matrice_ricerca;matrice_restituita;[se_non_trovato];[modalità_confronto];[modalità_ricerca])
Quindi la formula annidata, svolge questi compiti:
- cerca il valore C5 nell’intervallo denominato Quantità (ovvero J5:M5)
- restituisci l’intervallo Sconto per quella quantità (poichè la quantità in C5 è 12, verrà restituito l’intervallo Sconto L6:L12, cioè la colonna degli sconti per una quantità pari a 12)
- se non trova il valore, la formula restituisce 0 (parametro [se_non_trovato])
- infine, come [modalità_confronto], imposto -1 (approssimato), cioè se il valore C5 viene trovato esattamente tra le quantità della riga J5:M5 (nel nostro caso la quantità 12 viene trovata in L5) bene altrimenti prendi il valore di quantità immediatamente inferiore. Se ad esempio i Limoni venissero richiesti con quantità 15, verrebbe comunque restituita la colonna di sconti L6:L12, poichè il valore 15 non è presente in J5:M5. Per tutte le quantità maggiori di 12 e minori di 24 (cella M5), verrebbe restituita la stessa colonna. Questo è coerente poichè io voglio applicare lo stesso sconto per tutte le quantità maggiori o uguali a 12 e minori di 24.
Risultato della formula CERCA.X annidata
Come detto, la formula CERCA.X annidata mi restituirà la colonna degli sconti corrispondenti alla quantità 12 (ovvero la cella C5):
{0,1;0,1;0,1;0,1;0,1;0,1;0,1}
Notare che la colonna contiene 7 sconti del 10%: la formula restitisce il valore decimale corrispondente a 10% cioè 0,1. Nel nostro esempio, tutti gli sconti per una quantità 12 sono fissati al 10%: nulla vieta di differenziare questi sconti in base al prodotto (ad esempio 10% per i Limoni e 20% per le Mele). La formula e i risultati restano validi comunque.
Entra in gioco la formula CERCA.X esterna
La mia formula non è completa. Il CERCA.X annidato mi ha restituito tutti gli sconti corrispondenti a un prodotto acquistato con quantità 12. Ma quello che voglio è lo sconto da applicare quando il bene acquistato (in quantità 12) è Limoni. Qui entra in gioco la seconda parte della formula: il CERCA.X esterno.
Riprendiamo la formula completa:
=CERCA.X(B5;Prodotti;CERCA.X(C5;Quantità;Sconto;0;-1);0)
Adesso il valore da cercare è B5 (Limoni), l’intervallo dove cercare è Prodotti (I6:I12). L’intervallo (o la cella) da restituire come risultato è il risultato della formula annidata che abbiamo visto in precedenza: CERCA.X(C5;Quantità;Sconto;0;-1). Ma quest’ultima, come abbiamo spiegato, restituisce l’intervallo L6:L12: quindi il CERCA.X esterno restituisce il valore, nell’intervallo L6:L12 che corrisponde al prodotto Limoni. Nel nostro caso, sarà la cella L10 (10% o meglio 0,1). Forse la figura lo spiega meglio.
Infine, anche in questo caso, mettiamo a zero il valore da restituire qualora non venisse trovato B5 nell’intervallo Prodotti. Se non trovo un prodotto significa che esso non ha sconto per quantità e quindi posso considerarlo a sconto nullo. Come vedete, infatti, nella colonna E, alcuni prodotti appaiono con sconto 0%: infatti quei prodotti non sono presenti nell’intervallo di ricerca Prodotti (che ricordiamo è I6:I12).
Ultima nota, nel CERCA.X esterno non ho indicato il parametro [modalità_confronto]: questo significa che accetto il valore di dafault, ovvero 0, che significa “esatta corrispondenza”. Perchè? Semplicemente perchè, nel caso del CERCA.X esterno, il valore da cercare è Limoni: quando cercherò questo prodotto nell’intervallo I6:I12, voglio trovarlo esattamente con quel nome. Non avrebbe senso, in questo caso, eseguire una ricerca approssimata.
Nota a margine per il calcolo del Totale
Come potete verificare dal foglio di lavoro, la formula per il calcolo del Totale dovuto per l’acquisto, ad esempio per la cella G5, è:
=C5*D5*(1-E5)
Potete notare dunque che, se il prodotto non è scontato (sconto= 0), il totale è ugualmente corretto: il prodotto verrà acquistato a prezzo pieno.
