Tag: excel matrici

5 Febbraio 2020 Nessun commento

[excel] Calcolare tutte le possibili combinazioni con ripetizione usando le matrici in Excel

Obiettivo: dato un insieme di elementi distinti n vogliamo calcolare tutte le possibili combinazioni che si possono formare con k degli n elementi dati, dove k \in \mathbb{N}

Secondo il calcolo combinatorio il numero di combinazioni con ripetizione che possiamo ottenere si calcola con la seguente formula:

C'_{(n,k)}=\left(\begin{matrix} n+k-1 \\ k \end{matrix} \right)=\frac{(n+k-1)!}{k!(n-1)!}

Con Excel vogliamo ottenere una matrice contenente tutti gli elementi, dove ogni elemento può essere testuale o numerico. A tale scopo utilizzeremo le caratteristiche sul calcolo matriciale introdotte recentemente in Office365 e le seguenti funzioni: INDICE, INDIRETTO, MATR.TRASPOSTA, RIPETI, UNICI, STRINGA.ESTRAI, FILTRO

Prima di procedere ricordiamo che UNICI e FILTRO sono per ora disponibili solo con Office365. Inoltre useremo il # (cancelletto) nella costruzione dei riferimenti, per prendere gli intervalli dinamici generati dalle matrici.

Definiamo quindi anzitutto gli elementi n e il numero di ripetizioni k in questo modo:

Nella riga 1 potremo aggiungere altri elementi a piacere, mentre in B2 inseriremo il numero di ripetizioni desiderate. Per praticità ho chiamato gli elementi A, B e C.

Cominciamo allora calcolando anzitutto il numero di elemento n nella cella B3:

Adesso vogliamo determinare il numero di disposizioni con ripetizione possibili, ovvero tutti i modi in cui possiamo disporre gli n elementi presi a gruppi di k, dove conta l’ordine.

Per chi non fosse pratico dell’argomento se abbiamo i suddetti 3 elementi, allora nelle combinazioni con ripetizione ABC e ACB sono la stessa combinazione (contengono ciascuna tutti e 3 gli elementi), mentre nelle disposizioni con ripetizione ABC e ACB sono elementi distinti.

Il numero totale di disposizioni con ripetizione sarà dato dalla formula:

D'_{(n,k)}=n^k

Che tradotta in Excel possiamo scrivere in B5 come:

Date queste informazioni calcoliamo tutti gli elementi possibili con la seguente formula in B11:

Questa genererà un risultato come il seguente:

L’idea è fondamentalmente quella di generare una matrice di riferimenti agli elementi dati in modo ordinato. Con 3 elementi presi 3 per volta avremo in tutto 27 disposizioni con ripetizione. Generiamo allora una matrice colonna con valori [1…27]. Nella prima colonna da [1…9] avremo il primo elemento, da [10…18] avremo il secondo elemento e così via. Siccome il primo elemento corrisponde a 1 e il secondo a 2, questo significa che presi i valori da [1…27] li dovremo dividere in 3 parti di 9 elementi ciascuna.

Questo lo potremmo ottenere scrivendo:

In questo modo generiamo [1…27] e dividiamo per 9, poi arrotondiamo per eccesso il risultato. Ne seguirà che, arrotondati per eccesso, 1/9 ⇒ 1, 9/9 ⇒ 1, 10/9 ⇒ 2, 18/9 ⇒ 2, 19/9 ⇒ 3, 27/9 ⇒ 3. Avremo insomma tre intervalli fatti, rispettivamente, di [1…1], [2…] e [3…3], ciascuno di 9 sottoelementi.

Nell’immagine di seguito vediamo la schematizzazione di ciò che vogliamo ottenere:

Per ciascun gruppo della prima colonna vorremo ottenere, nella seconda colonna, altri 3 sottogruppi, per ciascuno dei quali, nella terza colonna, vorremo ottenere altri 3 sottogruppi (questa volta di dimensione 1). Quindi i gruppi saranno 1, 3 e 9. Questi sono anche i valori per i quali dobbiamo sezionare gli intervalli delle colonne.

Per generare questi valori usiamo:

La matrice trasposta ci serve per metterli in riga, anziché in colonna. Dividiamo infine la prima colonna per questa riga.

Fatto questo spostiamoci in I10 e digitiamo:

In questo modo riportiamo semplicemente gli elementi scritti da B1 in poi.

In I11 calcoliamo quante volte appare ciascun elemento della matrice in B11#:

NB: grazie al cancelletto selezioniamo tutta la matrice generata da B11.

Il risultato che avremo sarà:

Nella prima riga A è presente 3 volte, gli altri due elementi 0 volte. Nella seconda riga abbiamo 2 A e 1 B, ecc.

In questo caso stiamo semplicemente usando la funzione CONTA.PIÙ.SE su righe successive, per ciascuno dei valori immessi.

A questo punto, sapendo quanti sono i singoli valori, possiamo usare la funzione RIPETI per riscriverli. Tale funzione ripete un dato valore per n volte. Quindi in P11 scriviamo:

Otterremo un risultato come il seguente:

Adesso facciamo una mossa un po’ astrusa. Siccome sappiamo che per ogni riga ci sono k elementi, ovvero 3 elementi, concateniamo tutta la matrice in una singola stringa e la spezziamo in pezzi da 3 elementi per volta. Verrà quindi una lunghissima stringa che comincerà con AAAAABAACAABABBABCAAC… e che verrà spezzata in AAA, AAB, AAC, AAB, ABB ecc.

Per farlo in W11 digitiamo:

La parte di formula con:

Genera una matrice colonna tipo:

La usiamo per spezzare la stringa in parti da 3 a partire dal 1° carattere, il 4° carattere, il 7° carattere ecc.

Il risultato che otterremo saranno tutte le disposizioni con ripetizione di cui parlavamo prima:

Per ottenere le combinazioni con ripetizione che stavamo cercando possiamo fare in due modi.

1. Utilizzando la funzione UNICI

In Z11 scriviamo:

E ricaviamo immediatamente l’elenco ridotto delle combinazioni in questo modo:

Questa è la maniera più semplice e veloce.

2. Utilizzando la funzione FILTRO

Questo è un modo un pizzico più complicato. Per ciascuno dei valori in W11# vogliamo calcolare la presenza del valore stesso nelle righe precedenti. Per farlo utilizzeremo la funzione SCARTO, aumentando la altezza della colonna della matrice di controllo via via che scendiamo. A questa applicheremo il CONTA.SE per vedere se il valore attuale, cioè ciascun valore della matrice, è presente nella sottomatrice precedente calcolata con SCARTO. Confrontando questo valore con 1, ovvero che debba comparire una sola volta, costruiremo la matrice colonna per il filtro da applicare alla funzione FILTRO.

La formula quindi sarà:

In entrambi i casi il risultato sarà identico:

Dal seguente link è possibile scaricare il file Excel con tutti i calcoli già fatti: combinazioni semplici

Vedi articolo
1 Febbraio 2020 3 commenti

[excel] Sommare tariffe giornaliere diverse in base alla data utilizzando le matrici in Excel

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:

Questo genererà un vettore simile a questo:

Adesso possiamo sommare a questo vettore la data di partenza:

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.

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:

In questo modo otterremo un vettore contenente la tariffa per ogni singolo giorno. Non ci resta che usare la SOMMA.

La formula diventerà quindi:

A questo punto possiamo espandere la formula da G2 a G6. Se abbiamo fatto tutto correttamente otterremo i seguenti risultati:

Vedi articolo
23 Gennaio 2020 Nessun commento

[excel] Creare un grafico variabile che si adatti alla lunghezza della serie, sfruttando le matrici

Obiettivo: voglio realizzare un grafico che prenda in input n valori x e y, dove n è un valore arbitrario. Questo significa che se imposto n = 7 allora avrò 7 valori di x e y opportunamente calcolati. Per esempio potrebbe trattarsi del valore annuale delle rate di un mutuo calcolato su n anni; quindi se metto 10 anni mi aspetto un grafico con 10 colonne, se metto 20 anni mi aspetto un grafico con 20 colonne, ecc.

Nel caso specifico voglio realizzare qualcosa che dia il seguente risultato.

Per una simulazione con n = 25 anni

Per una simulazione con n = 30 anni

Faccio notare che il grafico è rimasto lo stesso, non ho dovuto rifarlo, ma è aumentato il numero di valori sull’asse delle ascisse in base al numero di anni prescelto.

Per l’esercizio voglio anche sfruttare le nuove possibilità offerte dal recente aggiornamento di Office 365 in merito al calcolo e alla visualizzazione delle matrici.

La versione di Excel che ho utilizzato è la Versione 1912 (build 12325.20298) e non tutte le funzionalità potrebbero essere disponibili nelle versioni precedenti.

Detto tutto questo cominciamo.

Anzitutto quello che voglio realizzare, a titolo di esercizio, è un simulatore semplificato di investimento con mutuo. Vogliamo cioè costruire il business plan per la realizzazione di un qualche progetto che avrà un rendimento annuale e per il quale vogliamo chiedere un finanziamento. I dati di cui avremo bisogno in input saranno quindi

  1. Valore complessivo del progetto (es. 250.000€)
  2. Valore del mutuo per il finanziamento iniziale (mutuo calcolato a tasso fisso su una percentuale del valore del progetto, per esempio l’80% al 3% in 15 anni con rate mensili)
  3. Entrate annuali per il progetto operativo (es. 30.000€ / anno)
  4. Durata del progetto (es. 30 anni)
  5. Tasso di inflazione (es. 2%)

Predisponiamo quindi i valori in input a partire dalla cella C4 nel modo seguente:

Di tutti i valori l’unico calcolato è il valore della rata, dove abbiamo la seguente formula:

Rinominiamo il foglio su cui stiamo lavorando in MUTUO, questo ci servirà dopo:

Adesso calcoliamo i valori di gestione disponendoli nel modo seguente a partire dalla cella H4.

Calcoliamo gli anni che partiranno da 0 fino all’anno indicato in D10. Se in D10 ci fosse 30 il numero totale di anni sarebbe quindi 31. Il numero di anni sarà quindi sempre n + 1. Dal punto di vista teorico l’anno 0 è quello degli investimenti pre-operativi; non è indispensabile ai fini dell’esercizio ma, sebbene stiamo facendo un esempio semplificato, facciamo le cose per bene.

Voglio che gli anni siano generati, come tutto il resto, in modo dinamico senza dover predisporre un numero massimo di valori possibili. Per farlo sfruttiamo le funzioni sulle matrici di excel nel modo seguente:

Inserendo questo valore in H5 nelle celle sottostanti verranno automaticamente completati i dati conseguenti. Selezionando la cella H5 ci verrà mostrata una selezione blu sottostante:

I dati evidenziati non sono inseriti nelle celle, ma generati automaticamente in quanto il risultato in H5 è una matrice colonna contenente i valori calcolati.

Procediamo in modo analogo per le colonne seguenti. Dal momento che vogliamo che ogni colonna sia generata per intero e non debba fare affidamento sulla colonna a fianco, procederemo col calcolo matriciale. Per le rate inseriremo in I5 la seguente formula:

Nel caso specifico dobbiamo usare un doppio se, perché la funzione =E() non è compatibile col calcolo matriciale. Con il primo SE verifichiamo che l’anno sia maggiore di 0, con il secondo SE verifichiamo che l’anno sia minore o uguale all’anno finale del mutuo, in tal caso inseriamo il valore della rata in D12, altrimenti lasciamo a 0.

Per la colonna dei costi, che, come nell’immagine precedente, avrà in realtà un unico valore e il resto a 0, inseriamo la seguente formula:

In pratica l’unico costo iniziale che abbiamo è la parte del progetto non coperta dal mutuo, calcolata con la formula -D4*(1-D7)

Nella colonna delle entrate inseriamo la seguente formula:

In modo analogo a prima quando l’anno è maggiore di 0 allora mettiamo il valore delle entrate annuali in D9. Il risultato lo dividiamo per (1+2%)^anno, dove il 2% è il tasso di inflazione e l’anno è l’anno corrente. Potremmo discutere se mettere al primo anno come anno 1 oppure 0, ma non è questa la sede. Nel mio caso lascerò il valore a 1.

A questo punto scriviamo la colonna della cassa. Questa è la più semplice da realizzare perché è semplicemente la somma delle precedenti tre formule, messe in fila una dopo l’altra.

Adesso vogliamo calcolare il cumulativo di cassa. Qui le cose si complicano perché lo vogliamo fare sulle matrici.

Per intendersi ipotizziamo di avere i seguenti valori: 1, 2, 3, 4, 5

Il valore cumulativo sarebbe dunque: 1, 3, 6, 10, 15

Ovvero la somma di tutti i valori precedenti al valore corrente.

Adesso noi sappiamo che l’istruzione RIF.RIGA(INDIRETTO("1:"&(D10+1))) ci dà dei valori che vanno da 1 a n+1. I valori della cassa si troveranno via via in L5:L[x] dove L[x] sarà in sequenza: L5, L6, L7 ecc.

Quindi possiamo sfruttare l’istruzione precedente sommandole un +4 per trovare tutti i valori di L[x] di cui abbiamo bisogno.

In questo modo verrà generata una serie di matrici contenenti ciascuna i valori da sommare. Per capirsi, riprendendo la sequenza numerica di prima, il risultato sarebbe simile a questo:

La funzione =SOMMA() applicata alla seconda matrice darebbe purtroppo un UNICO risultato. Per ottenere una matrice colonna con la somma delle singole righe della matrice quadrata dobbiamo usare la funzione =SOMMA.SE(). La condizione di somma sarà banalmente <>0

La formula che quindi andremo ad inserire sarà:

Fatte tutte queste belle cose avremo costruito la tabella come mostrato prima.

Questa tabella cambierà automaticamente di dimensione in base al numero di anni. Se per esempio mettessimo gli anni del mutuo a 8 e l’operatività complessiva a 15 passeremmo a qualcosa come questo:

A questo punto andiamo a realizzare il grafico. Per farlo sfrutteremo la definizione dei nomi.

Anzitutto andiamo in Formule > Gestione nomi

Da qui definiremo i nomi nel modo seguente:

Attribuiamo un nome e creiamo un riferimento. In tutti i casi sfrutteremo la funzione scarto che partirà sempre dalla riga 5 e si estenderà per una altezza di n – 1 valori, dal momento che la colonna possiede un titolo. Creiamo quindi i seguenti nomi:

CASSA

CUMULATIVO

ANNI

Andiamo adesso ad inserire un istogramma da Inserisci > Grafici

In seleziona dati aggiungiamo due voci di serie, nello specifico CASSA e CUMULATIVO

Nel nome della serie scriviamo via via la descrizione, per esempio Cassa, mentre nei valori scriviamo:

Ricordiamoci che MUTUO è il nome del foglio. E’ molto importante anteporre alla definizione del nome il nome del foglio, altrimenti non funzionerà.

Alla voce etichette inseriamo:

Il risultato che otterremo sarà simile a questo:

Fatto questo otterremo un grafico simile al seguente:

Per trasformare il flusso cumulativo in una linea, anziché nell’istogramma, clicchiamo col destro su una delle colonne arancioni e scegliamo Cambia tipo di grafico serie…

Nella finestra che si aprirà selezioniamo, dal menu a tendina, la voce Linee

Se abbiamo fatto tutto bene otterremo un grafico come il seguente:

Il grafico cambierà dimensione al variare dei valori in input.

Il vantaggio di questa soluzione è che potremmo inserire valori assolutamente arbitrari per la lunghezza complessiva dei dati, senza doverla predisporre in anticipo. Se per esempio scegliessimo la durata del progetto su 500 anni, andrebbe comunque bene.

Vedi articolo