Analizzare i dati da Excel e MySQL utilizzando Power BI (per esordienti totali)

Procuriamoci anzitutto il necessario per poter lavorare, in questo esempio utilizzerò un database MySQL, un file Excel con ulteriori dati da integrare e Power BI.

In ordine avremo quindi bisogno di:

  1. XAMPP per server MySQL e PHPMyAdmin (non è necessario utilizzare XAMPP, si può usare un qualunque database MySQL ovviamente)
  2. Un database con dei dati di esempio che puoi scaricare da qui (questo database è già stato utilizzato nell’esercizio [mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali))
  3. Un file excel con integrazioni dei dati per clienti e agenti che puoi scaricare da qui
  4. Power BI che possiamo ottenere gratuitamente a questo link
  5. MySQL Connector/NET 8.0.20 che si può scaricare da questo link (se non lo installiamo in anticipo sarà Power BI stesso ad invitarci di farlo nel momento in cui vorremo collegare un database MySQL)

Preparato tutto questo possiamo cominciare a lavorare con Power BI.

1. Importazione dati da MySQL

Avviamo Power BI e scegliamo Recupera dati dalla maschera iniziale.

Nel campo di ricerca cerchiamo mysql:

Selezioniamo il database MySQL. Se non abbiamo installato il connettore, come indicato prima, ci verrà chiesta l’installazione. In caso contrario procediamo configurando il database. Inseriamo, se stiamo usando XAMPP, come server 127.0.0.1 e il nome del database, nel mio caso powerbi.

Procediamo premendo OK ed impostiamo nome utente del database come root e lasciamo il campo password vuoto.

All’avviso sul supporto crittografia premiamo OK.

A questo punto selezioniamo tutte le tabelle che intendiamo caricare. Nel caso specifico selezioneremo tutte le tabelle eccetto storico_vendite.

Premiamo sul tasto carica.

2. Elaborazione dati

A questo punto elaboriamo le connessioni tra le tabelle. Sulla sinistra spostiamoci su modello:

Dovremmo vedere qualcosa di simile a questo.

Spostandoci su una qualsiasi delle relazioni notiamo come siano stati associati automaticamente gli ID. Purtroppo queste associazioni non vanno bene, perché i nomi delle colonne, tra le varie tabelle non corrispondono. A vendite.idAgente corrisponde infatti agenti.id, mentre secondo il sistema automatico vendite.id corrisponde a agenti.id. Possiamo ricostruire le associazioni trascinando i campi gli uni sugli altri, oppure andando ad intervenire da Gestisci relazioni, nella scheda Home > Relazioni.

Cliccando su Gestisci relazioni si aprirà una schermata come la seguente:

Modifichiamo opportunamente tutte le relazioni. Per esempio quella tra vendite ed agenti nel modo seguente:

Se abbiamo fatto tutto correttamente dovremmo vedere qualcosa di simile a questo:

Faccio notare che in tutti i casi ho impostato direzione filtro incrociato su Entrambi. L’utilizzo dell’opzione è finalizzato specificatamente alla semplificazione dell’uso dei filtri. La stessa Microsoft lo spiega nell’articolo Abilitare il filtro incrociato bidirezionale con DirectQuery in Power BI Desktop

Con il filtro incrociato bidirezionale, gli autori di report e modelli di dati hanno ora un maggiore controllo sulla modalità di applicazione dei filtri durante l’uso di tabelle correlate. Il filtro incrociato bidirezionale consente loro di applicare filtri su entrambi i lati di una relazione tra tabelle. Per ottenere questo risultato, il contesto di filtro viene propagato a una seconda tabella correlata sull’altro lato della relazione.

Fatto tutto questo lo schema che visualizzeremo sarà come il seguente:

3. Creiamo la nostra prima tabella nel report

Adesso mettiamo alla prova il sistema creato e proviamo a visualizzare il totale venduto dagli agenti.

Modifichiamo anzitutto il nome della pagina in Vendite:

Dalle visualizzazioni selezioniamo Tabella:

Poi afferriamo il nome dell’agente e trasciniamolo nella tabella. Stessa cosa per importo dalla tabella vendite:

Vedremo un risultato simile a questo:

Questo succede perché nelle vendite c’è un idAgente che non corrisponde ad alcun agente nella tabella agenti. Quello che vorremmo ottenere è una tabella di agenti LEFT JOIN vendite. Per farlo andiamo su Query e clicchiamo su Trasforma dati.

Clicchiamo col destro nel riquadro Query e selezioniamo Nuova query > Combina > Unisci query come nuova:

Configuriamo una LEFT OUTER JOIN nella maniera seguente:

A questo punto vedremo una tabella come la seguente:

Noi vogliamo visualizzare i dati aggregati delle vendite, quindi clicchiamo sul pulsante in alto a destra di powerbi vendite.

Selezioniamo l’opzione di aggregazione nella maniera seguente, scegliendo quantità e importo.

Dovremmo ottenere qualcosa di simile a questo:

Sulla destra clicchiamo sulla nuova tabella col destro e rinominiamola in vendite_x_agente. A titolo di esempio vogliamo anche aggiungere una colonna che calcoli l’importo medio di vendita per quantità di venduto.

Creiamo la colonna nella maniera seguente:

La formula che usiamo è: = Table.AddColumn(#"Colonne powerbi vendite aggregate", "media", each [Somma di powerbi vendite.importo] / [Somma di powerbi vendite.qta])

Fatto tutto questo salviamo e scegliamo di applicare i dati dal prompt che ci apparirà sullo schermo. Chiudiamo e torniamo a Power BI.

Sotto la voce Campi clicchiamo col destro su vendite_x_agente e aggiungiamo un’altra colonna (lo potevamo fare anche prima, ma per esercizio voglio farlo da questa posizione diversa): Nel campo formula digitiamo: nominativo = vendite_x_agente[nome]& " " & vendite_x_agente[cognome]

In questo modo abbiamo concatenato il nome e il cognome di ciascun agente aggiungendo in mezzo uno spazio.

A questo punto rinominiamo le colonne aggiunte prima, in modo da ottenere la seguente situazione:

Reimpostiamo la tabella nella maniera seguente:

4. Integriamo i dati da un foglio Excel

Adesso procediamo integrando i dati da un foglio Excel, per calcolare le provvigioni per ciascun agente.

Importiamo anzitutto i dati andando su Home > Dati > Recupera dati. Selezioniamo Excel. (c’è anche il collegamento veloce sotto Dati)

Selezioniamo il file excel anagrafica_clienti.xlsx.

Selezioniamo entrambe le tabelle e clicchiamo su Carica.

Andando su Modello vedremo che Power BI avrà collegato tutte le tabelle totalmente a caso.

Clicchiamo col destro su una delle due, poi andiamo su Gestisci relazioni, usando SHIFT e il mouse selezioniamo le relazioni che interessano Compensi agenti e Anagrafica Clienti (nella maniera mostrata nell’immagine) e clicchiamo su Elimina per tutte le relazioni selezionate (e create).

Ricreiamo le relazioni nella maniera seguente:

Fatto questo torniamo sulle tabelle e andiamo su vendite_x_agenti, dove abbiamo aggiunto prima i dati di vendita.

Clicchiamo col destro su vendite_x_agenti e selezioniamo Modifica query. Dalla finestra di modifica andiamo su Home > Combina > Merge query.

Impostiamo l’unione nella maniera seguente:

Clicchiamo sul pulsante in alto a destra della nuova colonna aggregata:

Selezioniamo Compenso e Fisso nella maniera seguente:

Salviamo il tutto e chiudiamo. Clicchiamo col destro sulla tabella e aggiungiamo una Nuova colonna. Inseriamo la seguente formula:

provvigione = vendite_x_agente[totale_importo]*vendite_x_agente[Compensi agenti.Compenso]

In questo modo calcoliamo il compenso in percentuale sul totale venduto. Torniamo sul report e aggiungiamo alla tabella anche la provvigione. Dovremo arrivare a vedere qualcosa di simile:

5. Formattazione valori

A questo punto vogliamo formattare i valori delle vendite nel formato valuta. Essendo dati aggregati in una tabella generata da JOIN, possiamo anzitutto formattare i valori all’origine, nelle tabelle di partenza. Questo vuol dire che ci spostiamo su vendite.

Selezioniamo la colonna desiderata ed inseriamo il formato Valuta. Possiamo modificare il tipo di valuta cliccando sull’opportuno pulsante come nell’immagine precedente.

Spostiamoci adesso su vendite_x_agente. Clicchiamo sulla colonna totale_importo, da Tipo dati selezioniamo Numero decimale, da Formato selezioniamo Valuta come prima. Stesso discorso per la provvigione.

Fatto questo possiamo tornare sulla visualizzazione dei dati. Dove vedremo qualcosa di simile a questo:

6. Creazione di Drill-through

Adesso vogliamo creare una analisi dettagliata per il totale venduto da ogni agente.

Aggiungiamo anzitutto una seconda pagina che chiameremo VENDITE AGENTE

Aggiungiamo una scheda per il titolo dell’agente analizzato:

Impostiamo una scheda che mostri anche il totale venduto nella maniera seguente:

Adesso aggiungiamo l’importo del venduto per ciascun agente per ciascuna città, procedendo nella maniera seguente:

Fatto questo attiviamo il campo Drill-thorugh sul nominativo dell’agente. Per farlo procediamo nel modo seguente:

Fatto tutto questo andiamo sulla pagina VENDITE. Clicchiamo col destro su un nominativo agente e scegliamo Drill-thought > VENDITE AGENTE.

Visualizzeremo i dati dell’agente nel report VENDITE AGENTE creato in precedenza.

Fatto questo abbiamo creato il collegamento Drill-through che volevamo.

Vedi articolo

[vba] Classe per leggere e scrivere su un database MySQL

A seguito del precedente articolo [excel] Aggiungere all’origine dati delle tabelle da un database MySQL ho deciso di integrare l’esercizio creando una classe per leggere e scrivere tramite VBA su un database MySQL.

Affinché la classe funzioni ricordiamoci di installare il connettore recuperabile sul sito ufficiale (Connector/ODBC 8.0.19). Inoltre ricordiamoci anche che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti. In questo modo:

Detto tutto questo possiamo creare una classe MySQL con le seguenti istruzioni all’interno:

Possiamo usare la classe nel modo seguente:

Faccio notare che con il metodo clear possiamo aggiungere gli slash agli apici, presenti nel cognome.

Vedi articolo

[excel] Aggiungere all’origine dati delle tabelle da un database MySQL

Vediamo come collegare su Excel una tabella da un database MySQL. Per il mio esempio utilizzerò un database MySQL installato in locale grazie a XAMPP.

Chiameremo il database in questione torregatti e lo struttureremo nel modo seguente, con due tabelle: clienti e agenti

Il file di creazione del database si trova al seguente link: torregatti.zip

A questo punto dobbiamo procurarci l’opportuno Driver ODBC, se non lo abbiamo già installato. In particolare per il MySQL il driver è recuperabile sul sito ufficiale (Connector/ODBC 8.0.19).

Una volta installato e scaricato andiamo a verificare la stringa di connessione.

Premiamo il tasto WIN + R. Nella finestra Esegui digitiamo %systemdrive%\Windows\SysWoW64\odbcad32

In questo modo apriremo l’Amministrazione origine dati ODBC (32 bit). Nel caso si debba aprire quella a 64 bit sarà sufficiente digitare odbcad32 oppure %systemdrive%\Windows\System32\odbcad32

Nella scheda Driver individuiamo il driver che ci interessa, in questo modo:

Nel mio caso è il MySQL ODBC 8.0 ANSI Driver

Fatto questo apriamo Excel e andiamo Dati > Recupera dati > Da altre origini > Da ODBC

Nella finestra Da ODBC selezioniamo Nome origine dati (DNS) come Nessuno e apriamo le Opzioni avanzate. Alla Stringa di connessione inseriamo la seguente stringa: DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;

Qualora il nostro database non si trovasse in locale sostituiamo localhost con l’indirizzo del database. Fatto tutto questo premiamo su OK. A questo punto, se è la prima volta che effettuiamo la connessione, ci verranno chiesto nome utente e password. Selezionare la voce Database e scegliere come username root e lasciare il campo password vuoto. Queste impostazioni dipendono, nel mio caso, dal fatto che sto utilizzando XAMPP. In altre circostanze dovrò inserire nome utente e password appropriati.

Fatto questo possiamo selezionare la tabella alla quale ci vogliamo connettere.

Nel mio caso voglio prelevare la tabella agenti:

Una volta selezionata la tabella che ci interessa premere su Carica.

Possiamo caricare anche delle viste in MySQL, come nel mio caso la vista clienti_agenti. Se tutto è andato bene vedremo un risultato simile a questo:

Vedi articolo

[VBA] Tema musicale di Tetris eseguito da una macro in VBA su Excel

Ispirato da un articolo che ho trovato online sull’argomento (Tetris Theme Song Using Processing) ho deciso di riprodurre il medesimo risultato in VBA su Excel, riproducendo quindi il tema musicale di Tetris tramite una macro per Excel.

Anzitutto importiamo dal kernel32 di Windows la funzione appropriata per eseguire un Beep, riproducendo quindi un suono, nel modo seguente:

La funzione accetta due argomenti: frequenza in Hz e durata in ms. Quindi per eseguire un 1 beep a 800Hz di 0,5 secondi, dovremo scrivere: Beep 800, 500

Detto questo voglio anzitutto riconvertire le funzioni dell’esempio nel link in VBA, creando un vettore di frequenze ed uno dei tempi, nel modo seguente:

Faccio notare che per la conversione dello spartito (che si può trovare al seguente link) è stata usata una tabella di conversione simile a questa.

Detto questo voglio riportare la medesima tabella di conversione in Excel, in un foglio che chiameremo NOTE. Il risultato che otterremo sarà simile a questo:

A questo punto voglio creare un altro foglio, chiamato SPARTITO, dove creeremo tre colonne, una nella quale scegliere le note e l’altra dove inserire i tempi come ottavi di nota.

A questo punto associamo al pulsante Suona la seguente macro:

Le frequenze vengono recuperate dal foglio NOTE.

Da qui si può scaricare il file excel comprensivo di macro: excel VBA – note musicali – tema tetris

Vedi articolo

[excel] Generare una password casuale di lunghezza arbitraria usando il calcolo matriciale

Questa soluzione si ispira ad un precedente articolo a cui rimando per chi fosse curioso di approfondire: [excel] Come creare una password casuale di lunghezza arbitraria con un’unica formula

Il risultato che voglio ottenere sarà simile a questo:

Avendo una cella in input F2 indichiamo la lunghezza della password che desideriamo ottenere, nella cella sottostante una singola formula genera una password pseudo-casuale.

Prima di procedere oltre raccomando per password importanti l’uso di software più affidabile, con algoritmi di generazione di valori casuali superiori ad Excel, come per esempio KeePass.

Anzitutto voglio creare un set di caratteri da cui estrarre quelli della password. Immaginiamo di voler scrivere automaticamente una stringa contenente tutte le lettere maiuscole.

Come illustrato nell’articolo precedente partiamo dal presupposto di voler ricavare i caratteri da una tabella ASCII come la seguente:

Le lettere maiuscole vanno dal carattere 65 al carattere 90. Per ottenere l’intervallo di numeri desiderato usiamo:

Questo produrrà una matrice come questa:

Se volessimo trasformare ciascun numero in un carattere dovremmo usare la funzione CARATT.UNI nel modo seguente:

Il risultato sarà simile a questo:

Applichiamo ora CONCAT alla matrice così ottenuta e avremo tutti i caratteri in una singola riga.

Ripetiamo l’operazione per gli intervalli 97:122 e 33:47 e concateniamo il tutto ai numeri da 0 a 9, nella seguente unica formula:

Tutto questo produrrà una riga come la seguente:

La riga ha in tutto 77 caratteri. Adesso, dopo averla unita, vogliamo spezzarla di nuovo in ogni singolo carattere, usando, come nell’esercizio precedente, la funzione STRINGA.ESTRAI:

Infine usiamo INDICE sulla matrice così creata ed estraiamo per n volte un valore a caso tra 1 e 77. Concateniamo il tutto per ottenere una unica stringa di testo contenente la password.

Affinché la funzione CASUALE.TRA ricalcoli il valore per ogni singolo carattere desiderato, utilizziamo un piccolo trucchetto, inserendo una matrice all’interno della funzione CASUALE.TRA in questo modo:

Grazie al SE sommo sempre 0, per qualunque valore generato da RIF.RIGA(INDIRETTO("1:"&F2)) che produrrà un vettore di dimensione n quanto è la lunghezza desiderata della password.

Vedi articolo

[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

[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

[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

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

Per chiunque fosse interessato a lavorare sulla matrici in Excel ho pubblicato un simpatico esercizio sul sito di Mummu Academy al seguente link:

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

Vedi articolo

[excel] Come creare una password casuale di lunghezza arbitraria con un’unica formula

Mi sono chiesto se ci fosse un modo, utilizzando Excel, di generare una password casuale di lunghezza arbitraria con una singola formula.

Naturalmente utilizzando VBA sarebbe semplicissimo, si potrebbe creare una propria funziona ed utilizzarla all’interno della formula. In alternativa si potrebbe creare una tabella di riferimento e generare la password a partire da essa (qui c’è un esempio interessante).

Però mi sono impuntato a voler creare una singola formula, utilizzando le funzioni disponibili nativamente in Excel, per generare una password di lunghezza arbitraria.

Soluzione veloce

Per chi non avesse voglia di proseguire nell’analisi della questione la soluzione è la seguente (per 1 singolo carattere preso tra maiuscole, minuscole, numeri e caratteri speciali):

Per generare una password di 2 caratteri basta concatenare la formula precedente per 2 volte, di 3 caratteri per 3 volte ecc (di seguito l’esempio per 2 caratteri):

Spiegazione

Anzitutto partiamo dalla seguente idea. Se avessi un vettore di 3 caratteri, potrei estrarre casualmente uno dei tre caratteri?

Per farlo mi è sufficiente scrivere:

Ad ogni ricalcolo del foglio questa formula pescherà casualmente un valore tra 10, 20 e 30. Tra le parentesi graffe {} ho inserito una matrice (nello specifico un vettore colonna) di 3 valori. Il punto fa da separatore di riga, altrimenti il backslash farebbe da separatore di colonna.

In ogni caso la presente formula sarebbe equivalente alla seguente situazione:

In A4 in questo caso vedrei un valore casuale preso tra A1:A3.

Seconda idea. Sarebbe possibile generare, al posto dei suddetti valori, dei valori casuali che escano fuori da una funzione? Purtroppo Excel non consente di scrivere esplicitamente una matrice contenente a sua volta delle funzioni, senza passare dall’utilizzo delle celle.

Allora mi sono chiesto: ci sarà un modo per scrivere una stringa e trasformarla in un vettore mediante un separatore di stringa (una specie di str_split per intendersi nel PHP)? Se la stringa è composta di singoli caratteri che dovranno diventare elementi del vettore è effettivamente possibile.

Possiamo cioè trasformare il nome Noctis nel vettore {“N”.”o”.”c”.”t”.”i”.”s”}

Per farlo è sufficiente utilizzare:

Faccio notare che INDIRETTO prendere le righe dalla 1 alla 6. Con RIF.RIGA generiamo un vettore di riferimenti equivalente a {1.2.3.4.5.6}. Per inciso la funzione sarebbe potuta anche essere così:

Dal momento però che mi fa fatica scrivere tutto il vettore da 1 a 6 (più che altro se fosse stato più lungo sarebbe risultato abbastanza impensabile) preferisco generarlo con questo semplice trucchetto.

A questo punto abbiamo capito che possiamo spezzare una stringa arbitraria nel vettore dei caratteri che la compongono.

Adesso prendiamo in considerazione la seguente tabella ASCII.

Grazie alla funzione CARATT.UNI posso estrarre un carattere a partire dal suo riferimento numerico nella suddetta tabella (nello specifico qualunque carattere UNI-CODE, ma non è il momento di approfondire la cosa).

Per esempio se digito:

Ottengo la lettera p minuscola.

Quindi posso generare caratteri casuali mischiando questa funzione con CASUALE.TRA nel modo seguente.

Caratteri maiuscoli:

Caratteri minuscoli:

Caratteri speciali:

Per generare invece i numeri da 0 a 9 mi basta utilizzare:

Quindi usando la formula:

Posso generare una stringa di 4 caratteri contenente un numero casuale, una maiuscola casuale, una minuscola casuale e un carattere speciale casuale.

Inserendo questa dentro la suddetta formula con STRINGA.ESTRAI posso estrarre i quattro caratteri dalla stringa in un vettore di caratteri casuali con la seguente formula:

A questo punto inserendo il tutto dentro a INDICE ottengo la precedente formula e il gioco è fatto.

Per generare delle password casuali di 16 caratteri come queste:

!#lKTHLb0nx2Z(0H
7&x(128’#L#(t6B3
,77YJ”lu35%LKYVm
svkA.ur1Y””+#r*t
15I35t1Z”(/Lz#0a

Ci sarebbe sufficiente utilizzare la formula un po’ più lunga e ripetuta come detto all’inizio.

 

Vedi articolo