Creare grafico dinamico con Excel

In questa guida vedremo come creare un grafico dinamico con Excel. In particolare, saremo in grado di creare un grafico con una base dati variabile che si aggiorna in tempo reale prendendo spunto dall'utilizzo delle funzioni di Excel 365 che ci permettono di restituire numerosi valori a partire da una sola cella tramite l'utilizzo del # (Dynamic Array) quali FILTRO e CERCA.X. Il file, purtroppo, non è compatibile con versioni precedenti di Excel ma puoi utilizzarlo anche su Excel online (puoi registrarti gratuitamente sul sito ufficiale Microsoft).

Questa procedura è spiegata in dettaglio in uno dei video (il corso dura oltre 8 ore) del corso avanzato di Excel e Power Query, puoi trovare tutti i dettagli qui!

Puoi scaricare il file finale cliccando sul pulsante seguente:

Queste sono le parti mobili necessarie per la creazione del grafico dinamico, le vdremo in dettaglio nel corso della guida:

• Tabella iniziale di partenza
• Tabella di appoggio per utilizzare lo slicer (filtro dati)
• Riepilogo delle informazioni da indicare sul grafico con FILTRO, CERCA.X etc
• Creazione del titolo in maniera dinamica da associare al grafico
• Creazione del grafico
• Definizione del nome per il primo array (i valori per singolo agente)
• Definizione del secondo nome per il secondo array (gli anni)
• Inserimento delle condizioni di formattazione condizionale

Cominciamo!

Tabella di partenza per creare un grafico dinamico su Excel

In questo caso abbiamo una tabella piuttosto semplice dove troviamo:

• AGENTE: questi sono i nomi degli agenti che stiamo considerando per l'analisi
• 2018, 2019, 2020, 2021, 2022: questi sono gli anni con i singoli fatturati

Come notiamo, alcuni degli agenti sono entrati in organico più avanti (Bonomi ad esempio, entrato nel 2020) oppure sono usciti dall'azienda (Berardi, che nel 2022 non è presente). Questo è importante per spiegare alcune delle modifiche che dovremo considerare nella parte finale di creazione del grafico.

Creare la tabella di appoggio per il grafico dinamico su Excel

Per il momento abbiamo soltanto la nostra tabella di partenza, chiamata TBL_VENDITE. Creiamo però un'altra tabella chiamata TBL_APPOGGIO che rispecchi quanto segue:

• AGENTE: questi sono i nomi degli agenti indicati in precedenza
• SUBTOTALE: questa è un colonna di supporto che ci servirà per trovare correttamente il valore da inserire poi sul grafico

La funzione SUBTOTALE permette di effettuare calcoli considerando esclusivamente le celle visibili, quindi è perfetta per fare calcoli in questo caso dato che dovremo filtrare i risultati della tabella.

In questo caso infatti, come vediamo, la utilizziamo su tutte le righe per la colonna SUBTOTALE come segue:

=SUBTOTALE(3;[@AGENTE])

SUBTOTALE restituisce varie funzioni a seconda di quello che ci serve, in questo caso prendiamo il 3 iniziale perchè conterà i valori per noi per ognuno degli agenti. Al momento il calcolo che fa è prendere ogni agente e restituire 1 dato che nessuno di loro risulta per più di una volta presente nella tabella, ma quando filtreremo con lo slicer ci permetterà di individuare quale sia l'agente che ci interessa. Questo trucco è stato ispirato da Carlos Barboza, che ti consiglio caldamente di seguire su LinkedIn qualora tu stia scoprendo come creare visualizzazioni particolarmente complesse su Excel.

NB: la tabella di appoggio è allineata alla precedente unicamente per un motivo di semplicità di spiegazione, ti consiglio di inserirla sotto alla tabella di partenza per evitare visualizzazioni "strane" successivamente

Creare la struttura di supporto per ottenere le informazioni da inserire sul grafico Excel

Passiamo ora alla creazione delle singole parti che ci interessano. In particolare dovremo trovare:

• Nome dell'agente che stiamo considerando
• Valori di fatturato per l'agente che stiamo considerando
• Anni di riferimento per l'agente che stiamo considerando
• Creare un titolo dinamico che riutilizzeremo per il grafico

Come dicevamo sopra, gli agenti hanno anni di attività diversi, quindi dovremo tenerlo in considerazione nelle formule.

Vediamo come trovare ognuno di loro tramite l'utilizzo di formule:

Come trovare il nome dell'agente dalla tabella Excel

In questo caso utilizzeremo la funzione CERCA.X per trovare il valore di riferimento. In particolare:

=CERCA.X(1;TBL_APPOGGIO[SUBTOTALE];TBL_APPOGGIO[AGENTE])

La funzione cerca il valore 1 all'interno della tabella di appoggio nella colonna SUBTOTALE per poi restituire il valore AGENTE. Se non ci sono filtri, restituirà ROSSI dato che è il primo risultato che si trova sulla tabella. Lo stesso risultato può essere replicato (pre-Excel 365) con la combinazione INDICE CONFRONTA.

Come trovare gli anni di attività per il singolo agente

In questo caso utilizziamo le formule offerte da 365 per trovare quali siano gli anni che ci interessano:

=FILTRO(TBL_VENDITE[[#Intestazioni];[2018]:[2022]];INDICE(TBL_VENDITE[[2018]:[2022]];CONFRONTA(I5;TBL_VENDITE[AGENTE];0);CONFRONTA(TBL_VENDITE[[#Intestazioni];[2018]:[2022]];TBL_VENDITE[[#Intestazioni];[2018]:[2022]];0))>0)

Questa funzione è decisamente più articolata della precedente, in sostanza però la funzione filtra gli anni restituendo unicamente i casi nei quali una combinazione fra gli anni e il singolo agente restituisca un valore maggiore di 0. Vediamola nel dettaglio:

• FILTRO ci permette di filtrare i risultati ottenuti, infatti alla fine della funzione troviamo un >0. Questo significa che filtreremo i risultati del calcolo successivo di ricerca in tabella con INDICE CONFRONTA CONFRONTA, recuperando solamente gli anni dove il fatturato generato è maggiore di zero
• INDICE CONFRONTA CONFRONTA ci permette di cercare in tabella. Quindi quando noi scriviamo INDICE(TBL_VENDITE[[2018]:[2022]] stiamo dicendo a Excel che vogliamo ottenere come risultato i singoli anni, a patto che CONFRONTA(I5;TBL_VENDITE[AGENTE];0); (l'agente sia l'agente indicato in I5, in questo caso Rossi) e che CONFRONTA(TBL_VENDITE[[#Intestazioni];[2018]:[2022]];TBL_VENDITE[[#Intestazioni];[2018]:[2022]];0)) (gli anni siano quelli indicati nella tabella di partenza). Excel fa quindi una ricerca incrociando le righe e le colonne, restituendoci esclusivamente i valori della riga corrispondente a Rossi per tutti gli anni presenti nella tabella di partenza. Al termine, come dicevamo, FILTRO toglierà i valori inferiori a zero, permettendoci di lavorare esclusivamente con gli anni di attività per singolo agente

Questa funzione ci permette di avere 5 risultati per Rossi, 3 per Mazza, 4 per Berardi e così via. La formula è inserita in J3.

Come trovare i valori di fatturato per singolo agente

Chiaramente vogliamo popolare la tabella con i valori riferiti al singolo agente. Questo ci è possibile sempre utilizzando la funzione FILTRO, in questo caso in combinazione con CERCA.X:

=FILTRO(CERCA.X(I5;TBL_VENDITE[AGENTE];TBL_VENDITE[[2018]:[2022]]); CERCA.X(I5;TBL_VENDITE[AGENTE];TBL_VENDITE[[2018]:[2022]])<>"")

Come prima, le due formule svolgono un compito preciso:

• FILTRO filtra i risultati, volendo avere soltanto i casi nei quali il risultato di CERCA.X sia diverso dal valore vuoto. Questo perchè consideriamo i valori vuoti come anni di inattività del singolo agente
• CERCA.X cerca il valore di I5 (in questo caso Rossi) all'interno della tabella di partenza e ci restituisce tutti i valori delle colonne da 2018 a 2022, a patto che (qui interviene FILTRO) il fatturato sia diverso dal valore vuoto

Come vediamo, in questo caso la formula restituisce 5 valori, ma ne restituirà di meno per gli agenti che non sono stati attivi per tutti e 5 gli anni. La formula è inserita in J5.

Come creare un titolo dinamico per un grafico Excel

La nostra intenzione è quella di aggiungere un titolo dinamico al nostro grafico Excel. Per questo motivo, utilizziamo una combinazione di alcune formule per restituire il testo desiderato, includendo sia parti statiche sia dinamiche:

=CONCAT("Il totale per ";I5;" nei ";CONTA.VALORI(J5#);" anni considerati è pari a ";TESTO(SOMMA(J5#);"#'### €"))

In questo caso i passaggi sono i seguenti:

• CONCAT ci permette di concatenare diversi valori testuali in una singola cella
• CONTA.VALORI conta i valori inseriti nell'array J5#. Se non hai mai visto il #, questo indica le formule chiamate Dynamic Array (FILTRO, CERCA.X, UNICI...) create con Excel 365 che riempiono diverse celle a partire da una soltanto
• TESTO migliora la formattazione finale del titolo, permettendoci di aggiungere il simbolo dell'Euro e anche quello delle migliaia
• SOMMA prende il totale del risultato dei valori di fatturato precedenti, sempre utilizzando il cancelletto dell'array J5#

A questo punto, non ci resta che creare lo slicer (filtro dati), creare il grafico e agganciare tutto quanto creato fino ad ora per creare la dashboard.

Inserire filtro dati nella tabella di appoggio Excel

Torniamo adesso alla tabella di appoggio. Clicchiamo all'interno dove preferiamo e andiamo su Struttura Tabella > Inserisci filtro dei dati per creare lo slicer, che sarà il nostro pulsante dinamico per la dashboard. Chiaramente la voce che ci interessa è AGENTE e non SUBTOTALE, dato che SUBTOTALE lo utilizziamo esclusivamente per i calcoli.

NB: come detto in precedenza, la tabella di appoggio è consigliabile spostarla in basso sul foglio tanto non ci è utile a livello di visualizzazione, come puoi notare infatti ora comincia dalla cella B29. Per tagliare e incollare la tabella, basta fare CTRL + X e poi CTRL + V nel punto desiderato

Il risultato dovrebbe essere, al netto del formato (che puoi modificare dagli stili del filtro dati), una visualizzazione come questa:

Se vuoi modificare il numero di colonne, puoi cliccare sul filtro dati e poi andare nel menu contestuale in alto su Filtro dei dati > Pulsanti.

Creare grafico dinamico con Excel

A questo punto non ci resta che creare il grafico. Prendiamo i riferimenti da J3 in poi (per gli anni) e da J5 in poi (per i valori di fatturato) e andiamo su Inserisci > Grafici consigliati.

Dovremmo, a questo punto, avere un grafico standard come questo:

Dato che stiamo ragionando su un orizzonte di diversi anni, possiamo selezionare il grafico per poi cliccare su Struttura grafico > Cambia tipo di grafico e selezionare un grafico a linee (scegli tu, è solo per una questione di migliore visualizzazione). Da qui, ci manca solo l'inserimento del titolo, che avviene in questo modo:

In sostanza devi selezionare la forma del titolo del grafico > non scrivere niente al posto di titolo del grafico > selezionare la barra della formula e inserire =(cella_dove_si_trova_il_titolo). Nel mio caso specifico avevo spostato il risultato nella cella P13 del mio foglio che si chiama DB, quindi risulta =DB!$P$13. Se invece il tuo foglio si chiamasse DB_VENDITE e la cella fosse Q7, il risultato sarebbe =DB_VENDITE!$Q$7. In questo modo, avremo un titolo dinamico che varia a seconda di quanto scegliamo dallo slicer.

Come vediamo, il titolo è ora esattamente uguale alla cella P13. Siamo quindi in grado di cambiare in maniera dinamica il titolo, aggiungendo flessibilità al nostro modello.

Come modificare la base dati in maniera dinamica su Excel

Ci manca solo un punto fondamentale. Al momento il nostro grafico prende tutti i valori che siano da J3 in poi, così come da J5 in poi. Quindi se avessimo selezionato Mazza e un grafico a linee, il risultato sarà simile al seguente:

Come vediamo, in questo caso il grafico sembra storto perchè due anni erano vuoti. Per sopperire a questo problema, dobbiamo quindi eseguire ancora qualche passaggio finale prima di concludere e trarremo massimo avantaggio dall'utilizzo degli array precedenti.

Dobbiamo trasformare i nostri array perchè abbiano dei nomi definiti. Clicca sulla cella J3 e vai poi su Formule > Definisci nome:

A questo punto seleziona pure il nome che preferisci, in questo caso io ho inserito chartYR che indica gli anni da inserire sul grafico. Attenzione a inserire però, in Riferito a, l'intero array inserendo un # alla fine, ad esempio con =DB!$J$3# anzichè =DB!$J$3 altrimenti il riferimento non funzionerà.

Ripetiamo poi la procedura anche per pescare i valori di fatturato, pure in questo caso scegli pure la parola che preferisci, io ho inserito chartSRC che indica i valori di fatturato collegati all'array J5#:

A questo punto, se vogliamo, modifichiamo il grafico finale con le seguenti specifiche, prima di continuare:

• Struttura grafico > Cambia tipo di grafico > Selezioniamo il grafico a linee
• Struttura grafico > Aggiungi elemento Grafico > Etichette dati (in alto)
• Mettiamo il titolo in grassetto
• Struttura grafico > Aggiungi elemento Grafico > Linee > Linee di proiezione

Selezioniamo sempre il grafico e ora modifichiamo da Struttura grafico > Seleziona dati:

A questo punto clicca su Modifica nella parte dell'asse orizzontale, dove inseriremo i valori degli anni:

Sostituiamo i valori inseriti e colleghiamo l'array che abbiamo creato in precedenza con gli anni, nel mio caso chartYR, in modo da renderlo dinamico. Attenzione a inserire anche DB!$ o comunque la specifica del foglio che state considerando, altrimenti non funzionerà:

Ripetiamo poi l'operazione cliccando su Modifica in Voci legenda (serie):

Per verificare se abbiamo seguito tutti i passaggi correttamente possiamo cliccare sui dati interni al grafico. Vediamo come la SERIE di origine sia ora correttamente indicata con chartYR e chartSRC:

A questo punto, abbiamo quasi terminato! Possiamo spostare lo slicer sopra i nostri calcoli precedenti (tanto non ci sono utili per la nostra visualizzazione) e spostare il grafico in basso. Il risultato finale sarà simile a questo:

Per aggiungere un ultimo tocco alla nostra dashboard, potremmo voler evidenziare la riga di riferimento del singolo agente con la formattazione condizionale.

Evidenziare l'intera riga con la formattazione condizionale su Excel

In questo caso ci serve un piccolo trucco per evidenziare l'intera riga, in particolare utilizzeremo la formattazione condizionale con l'utilizzo di formule in modo da risolvere velocemente e migliorare la visualizzazione. Ricordiamoci che in questo caso il nostro agente è nella cella I5.

Selezioniamo l'intera tabella (a parte le intestazioni di colonna) e clicchiamo su Formattazione condizionale > Nuova regola:

A questo punto possiamo impostare la regola per la quale se il valore nella colonna B ($B4 perchè è la prima cella dell'intervallo parte della tabella) è pari a I5, allora tutta la riga deve essere riempita con lo stesso colore:

A questo punto abbiamo terminato! Abbiamo creato una dashboard dinamica che prende un'origine di dati variabile, il grafico si aggiorna in tempo reale con un solo pulsante e in più vedremo in tempo reale, sul lato di sinistra, i dati indicati sul grafico!

Puoi scaricare il file finale cliccando sul pulsante seguente: