Obiettivo: Immaginiamo di voler calcolare il costo dei pernottamenti in un albergo dove le tariffe variano in base alla stagione (nello specifico in base ai mesi) e si ha una data di ingresso e il numero di notti.
Una soluzione a questo esercizio l’ho già proposta sul blog di Mummu Academy, giocando un po’ con le matrici: [EXCEL] SOMMARE TARIFFE GIORNALIERE DIVERSE PER DATA USANDO LE MATRICI IN EXCEL
La soluzione che propongo qui è più semplice e sintetica e fa uso sempre del calcolo matriciale in Excel.
Come nel suddetto esercizio la nostra base di partenza è un foglio in Excel contenente i seguenti dati:
I dati che vogliamo generare sono quelli dell’intervallo G2:G6.
Anzitutto vogliamo ottenere una matrice contenente tutte le date di pernottamento. Per farlo prenderemo ciascuna data di partenza e sommeremo i giorni corrispondenti. Se dobbiamo sommare 5 notti allora alla data di partenza dovremo sommare il vettore [0, 1, 2, 3, 4]. Faccio notare come il vettore parta da 0. Questo perché, nel caso di Mario Rossi, i pernottamenti li avremo nei giorni [21/06/2019, 22/06/2019, 23/06/2019, 24/06/2019, 25/06/2019]. La prima data deve essere anche quella di arrivo.
Generiamo quindi anzitutto il vettore dei giorni come detto prima, sfruttando RIF.RIGA e INDIRETTO nel modo seguente:
|
1 |
=RIF.RIGA(INDIRETTO("1:"&F2))-1 |
Questo genererà un vettore simile a questo:
Adesso possiamo sommare a questo vettore la data di partenza:
|
1 |
=E2+RIF.RIGA(INDIRETTO("1:"&F2))-1 |
Il vettore che potremo vedere a schermo (grazie anche alle nuove funzionalità di espansione di Excel) sarà come il seguente:
Dall’elenco delle date vogliamo adesso estrarre il mese corrispondente utilizzando la funzione MESE.
|
1 |
=MESE(E2+RIF.RIGA(INDIRETTO("1:"&F2))-1) |
Il risultato dell’operazione sarà simile a questo:
Il numero 6 rappresenta il mese di giugno. Se applicassimo questa formula a Chiara Celesti vedremmo un risultato simile a questo:
Faccio notare come alla fine del mese di dicembre si passi da 12 a 1, ovvero a gennaio.
A questo punto possiamo usare il CERCA.VERT su ciascuno di questi valori per ricavare la tariffa che ci interessa. La formula diventerà dunque:
|
1 |
=CERCA.VERT(MESE(E2+RIF.RIGA(INDIRETTO("1:"&F2))-1);$A$2:$B$13;2;FALSO) |
In questo modo otterremo un vettore contenente la tariffa per ogni singolo giorno. Non ci resta che usare la SOMMA.
La formula diventerà quindi:
|
1 |
=SOMMA(CERCA.VERT(MESE(E2+RIF.RIGA(INDIRETTO("1:"&F2))-1);$A$2:$B$13;2;FALSO)) |
A questo punto possiamo espandere la formula da G2 a G6. Se abbiamo fatto tutto correttamente otterremo i seguenti risultati:
Buongiorno, ho provato a inserirlo nel mio foglio excel ma il calcolo dei giorni di permanenza non funziona….
Ciao Loredana, probabilmente stai utilizzando una versione antiquata di Excel. Prova con un Excel 2016 o superiore (io l’ho eseguito sul 365). Altrimenti fai riferimento alla formula nell’articolo precedente: https://www.mummuacademy.it/2019/07/02/excel-sommare-tariffe-giornaliere-diverse-per-data-usando-le-matrici-in-excel/
Fammi sapere come va.
Ciao, grazie mille ho provato a farlo e funziona anche se l’ho adattato ad un periodo di soli due mesi. Tuttavia riscontro un problema quando estendo la formula. Praticamente invece di mantenere l’intervallo “$A$2:$B$13” lo trasforma in numeri consecutivamente maggiori. sono sicura che sto sbagliando qualcosa ma non so cosa…