[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.