0

Nessun prodotto nel carrello.

Excel

2
Lug

[Excel] Sommare tariffe giornaliere diverse per data usando le matrici in Excel

Partiamo con uno dei nostri più classici esercizi in Excel. Immaginiamo di avere la seguente tabella di dati, riguardanti i pernottamenti di un gruppo di clienti in un albergo.

elenco clienti albergo excel

Da un’altra parte abbiamo una tabella con le tariffe divise per mese (per comodità abbiamo i mesi già numerati).

A questo punto vogliamo calcolare quanto spenderà ogni cliente sapendo la sua data di ingresso nell’albergo e il numero di pernottamenti. 

La soluzione più banale, sebbene non perfettamente corretta, sarebbe quella di prendere come tariffa di riferimento la tariffa di ingresso e moltiplicarla per il numero di notti. Nel caso specifico nella cella D2 andrebbe la seguente formula:

=CERCA.VERT(MESE(B2);$F$2:$G$13;2;FALSO)*C2

Da questo, applicando la formula su D2:D6, otterremmo un risultato come il seguente:

Inutile ribadire come questo risultato non sia corretto, perché in molti casi al passaggio di mese cambia anche la tariffa. Per esempio Chiara Celesti, che comincia il suo soggiorno il 15 dicembre 2019, lo finirebbe l’8 gennaio 2020. Nel caso specifico pagherebbe 45€ per 17 notti e 50€ per 8 notti, per un totale di 1.165€ anziché 1.125€.

Come possiamo quindi risolvere questo problema?

Una possibile soluzione deriva dall’utilizzo delle matrici, con il seguente ragionamento.

Anzitutto spostiamoci in K1 e creiamo la seguente tabella di supporto:

Inseriamo in K2 uno 0, e in K3 la formula  =K2+1  trascinando fino a K42 in modo da ottenere come massimo valore 40. Questo sarà anche il numero massimo di pernottamenti + 1 che potrà fare una persone. Cioè calcoleremo i pernottamenti fino ad un massimo di 41 notti (partendo da 0 fino a 40 compresi). Se volessimo prendere in considerazione più pernottamenti sarebbe sufficiente allungare ulteriormente la formula.

Il ragionamento che voglio fare è il seguente: confrontare l’elenco dei giorni di pernottamento con le date e sovrapporli ad una matrice di costi. Il punto di arrivo è una MATRICE come la seguente:

Figura 1

Questa matrice è riferita ai pernottamenti di Anna Bianchi. Lei comincia la vacanza il 20 dicembre e la finisce il 3 gennaio. Significa che copre 12 giorni di dicembre e 3 di gennaio. Usando la matrice di K2:K42 sommiamo alla data di partenza da 0 fino a n giorni, ovvero da 0 a 14 giorni, arrivando così ad avere tutte le date dal 20 dicembre al 3 gennaio.

Rispetto alla matrice precedente avremmo una tabella come quella che segue:

Figura 2

Nella prima colonna abbiamo la data di ingresso ripetuta su tutta la durata, nella seconda colonna abbiamo i giorni da 0 a 14, fino cioè a 15 escluso (che è il numero di pernottamenti). Se sommiamo alla data di ingresso ciascun giorno otteniamo le relative date di pernottamento. Usando la funzione MESE() su ciascuna data otteniamo quindi la quarta colonna con la codifica del mese. Infine eguagliamo la colonna dei mesi di pernottamento con la riga di tutti i mesi, ottenendo la matrice di uguaglianze (vero e falso) come di sopra.

Fatto questo andremo a prendere una matrice di prezzi fatta nel modo seguente:

Figura 3

Sovrapponendo le due matrici, solo dove nella prima abbiamo VERO otterremo una matrice così:

Figura 4

Ai FALSO abbiamo sostituito uno 0. A questo punto vediamo che facendo un SOMMA() su questa matrice possiamo calcolare esattamente la tariffa che dovrà pagare Anna Bianchi.

Per realizzare tutto questo con una singola formula utilizzeremo le MATRICI di Excel nel modo seguente.

Anzitutto otteniamo la prima colonna dei mesi come nella Figura 1 utilizzando la seguente formula:

=MESE(B2+$K$2:$K$42)

Se premiamo F9 sulla formula evidenziata dovremmo vedere una matrice come la seguente per il primo caso (quello del 21 giugno 2019)

{6.6.6.6.6.6.6.6.6.6.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7}

Qui vediamo che vengono calcolati tutti i mesi per i prossimi 40 giorni a partire dalla data di partenza, mentre Mario Rossi rimane solo per 5 giorni. Vogliamo quindi a 0 i giorni superflui e integriamo con la seguente formula:

=SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)

Il risultato diventerà:

{6.6.6.6.6.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0}

Così abbiamo ottenuto la colonna dei mesi della Figura 1. Per ottenere la matrice la eguagliamo ai mesi in F2:F13, siccome però vogliamo ottenere una matrice rettangolare dobbiamo eguagliarli ad una matrice riga, mentre F2:F13 è una matrice colonna. A tale proposito useremo la funzione MATR.TRASPOSTA() che traspone una matrice colonna in una matrice riga.

=SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13)

Ancora F9 e otteniamo:

{FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO}

In questo ginepraio di FALSO possiamo notare alcuni VERO all’inizio, in corrispondenza con il mese in colonna e quello in riga.

A questo punto usiamo il SE nel modo per cui se nella matrice c’è un VERO allora prendi il prezzo dalla matrice dei prezzi, simile a quella della Figura 3 che creiamo usando:

=MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13))

Questa particolare operazione darebbe infatti questo risultato:

{50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45}

Uniamo le due cose nel modo seguente:

=SE(SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13);MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13));0)

Questa formula darebbe (sempre usando F9):

{0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0}

Da notare come compaiono esattamente 5 numeri 60, corrispondenti alla tariffa di giugno che deve pagare Mario Rossi.

Applichiamo a questa matrice la funzione SOMMA() e premiamo SHIFT+CTRL+INVIO per applicare il calcolo matriciale al risultato.

La formula finale sarà:

=SOMMA(SE(SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13);MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13));0))

E dovremmo vedere qualcosa di simile nel nostro foglio:

Se abbiamo fatto tutto bene il numero risultato sarà dunque:

Con i valori corretti per ogni cliente su qualunque pernottamento.

 

 

 

29
Mag

[EXCEL] MEDIA, MEDIANA E MODA IN EXCEL

Davanti ad un report stracolmo di dati, anche se ben elencati e categorizzati, non è semplice raccogliere delle informazioni. Dato ed informazione sono due concetti con numerose intersezioni, ma con molte differenze. Il dato, di per sé non è detto che rappresenti un’informazione. Potrebbe, infatti, essere rappresentativo di determinazioni non pertinenti alla mia analisi oppure non essere aggregato in una forma di sintesi idonea.

La STATISTICA DESCRITTIVA è un ottimo alleato per raggiungere il nostro scopo. Esaminiamo adesso in modo semplice  alcuni basilari indici di posizione che la Statistica descrittiva ci offre.

Continua a leggere

6
Apr

[Excel] Creare Report in Excel con INDIRETTO

La funzione INDIRETTO è una delle funzioni che si possono utilizzare nel momento in cui si devono creare report  in Excel e gestire collegamenti tra fogli in modo sicuro ed efficiente. Insieme alla funzione INDIRETTO è spesso affiancato un set di funzioni quali INDIRIZZO, RIF.RIGA e RIF.COLONNA. Vediamo con un esempio come utilizzare questo interessante mix di funzioni.

Continua a leggere

9
Feb

[Excel] Come eliminare le righe vuote in Excel

I database in Excel possono avere anche decine di migliaia di righe e molto spesso si ha la necessità di individuare e rimuovere quelle lasciate vuote. Le righe vuote, infatti, se non sono funzionali ad un criterio ben preciso, possono produrre quelli che in informatica vengono classificati in “effetti indesiderati”. Scopriamo insieme uno dei migliori metodi per eliminare le righe vuote in Excel.

Continua a leggere

3
Feb

[Excel] La funzione CERCA.VERT in Excel

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!

Continua a leggere

3
Feb

[Excel] Elimina Duplicati in Excel

Se sei alla ricerca di un metodo semplice e rapido per rimuovere le righe duplicate in Excel, allora scopri come utilizzare il comando Elimina Duplicati in Excel o Rimuovi Duplicati in Excel. Il risultato di una corretta azione di pulizia è un database di record univoci.

Continua a leggere

20
Gen

[Excel] La funzione SE in Excel

In questo tutorial vediamo come utilizzare la funzione SE in Excel. Si tratta di una delle funzioni più potenti ed utili che si possano utilizzare ed è spesso utilizzata insieme ad altre funzioni, anche annidate. Vediamo come la funzione SE in Excel permetta al foglio di calcolo prendere delle “decisioni” in base al verificarsi o meno di alcune condizioni.

Continua a leggere

29
Dic

[Excel] Come andare a capo nella stessa cella in Excel

In questo breve ma utile articolo vediamo quali sono i due principali metodi utilizzati per andare a capo nella stessa cella in Excel.

Non è sufficiente, infatti, premere INVIO sulla tastiera: in questo modo andiamo a capo, ma usciamo dalla cella in cui stiamo digitando. Noi vogliamo andare a capo restando all’interno della stessa cella. Non tutti sanno che esistono due metodi che solo all’apparenza sembrano essere uguali.

Continua a leggere

27
Dic

[EXCEL] Elenco a Discesa Condizionato in Excel

In questo articolo vediamo come realizzare un Elenco a Discesa Condizionato in Excel. Non ci accontentiamo infatti di un semplice Elenco a Discesa, ma lo vogliamo dinamico, in modo che questo cambi di contenuto in base alla scelta che facciamo. Se ad esempio in una cella scelgo la voce TOSCANA, voglio che il menu proponga una scelta tra le PROVINCIE della TOSCANA, mentre se scelgo CAMPANIA, lo stesso menu deve elencare le PROVINCIE della CAMPANIA.

Continua a leggere

logo

Siamo vicini a Piazza Dalmazia, davanti alla fermata "Vittorio Emanuele" della linea T1 della Tramvia.

+39 055 4223105 | +39 388 17 06 110

info@mummuacademy.it

Via Vittorio Emanuele II, 194 Firenze

P.IVA/C.F.: 06325480488

PEC: mummu@pec.mummu.it

REA FI -619533

Iscriviti

Resta aggiornato sugli ultimi corsi e tutte le nuove opportunità di formazione