Creare grafico progressivo Excel

In questa guida scopriremo come creare un grafico progressivo su Excel. Al premere di un pulsante, potremo selezionare gli anni di riferimento dal primo (2010-11) e vedere il grafico aggiornarsi in automatico.

Il file è compatibile unicamente con Excel 365 ed Excel online (gratuito). Scaricalo se ti interessa averlo sempre con te:

Il caso specifico riguarda i campioni del titolo NBA dal 2010-11 al 2020-21, lo stesso procedimento può essere sicuramente utile se lavori con analisi di bilancio, fatturato o performance che richiedono di essere valutate su un orizzonte temporale di vari anni.

Tabella di origine per creare il grafico progressivo su Excel

La tabella di origine (TBL_DATA) è fondamentale per creare il grafico progressivo. Le colonne sono:

• Stagione di riferimento
• Squadra vincitrice per la singola stagione
• Numero della stagione considerata (partendo da 2010-11)
• Indicazione, per singola squadra, del numero di vittorie (bonus: in verde, se hanno vinto il campionato quell'anno)

L'unica formula che ci serve è quella relativa alle stagioni, dato che stiamo utilizzando una tabella dobbiamo utilizzare un trucco per rendere il numero progressivo:

=RIF.RIGA([@STAGIONE])-7

RIF.RIGA restituisce il numero della cella desiderata, ad esempio per A3 sarebbe 3, per C12 sarebbe 12 e così via. In questo caso, partiamo dal riferimento di riga per la singola stagione togliendo 7 perchè la prima riga della tabella è nella riga 8. Il risultato quindi sarà, per B8 = 8 - 7 = 1 / per B9 sarà = 9 - 7 = 2 e così via.

A questo punto, ci serve anche la tabella di supporto (TBL_SUPPORTO). In questo caso ci bastano due colonne:

• Stagione: qui metteremo tutti i nomi delle stagioni inserite in precedenza
• Subtotale: qui inseriremo un valore calcolato che ci permette di segmentare la tabella

La formula per il subtotale sarà:

=SUBTOTALE(3;[@STAGIONE])

SUBTOTALE si rende necessaria perchè permette di calcolare i dati in una selezione filtrata. In questo caso, ci serve perchè al selezionare un valore sul filtro dati, la tabella si filtrerà in automatico. Se vuoi capirci di più, puoi fare riferimento a questa guida dove il procedimento è spiegato più nel dettaglio.

A questo punto, creiamo un filtro dati a partire dalla tabella (Selezione Tabella > Struttura Tabella > Inserisci Filtro dei Dati > Stagione) che utilizzeremo per i nostri calcoli successivi.

Ora possiamo selezionare la stagione finale e la squadra campione per l'anno desiderato. Nello specifico, nella cella B37 inseriamo una formula per trovare la stagione selezionata dallo slicer / filtro dati:

=CERCA.X(1;TBL_SUPPORTO[SUBTOTALE];TBL_SUPPORTO[STAGIONE])

In questo caso, troverà 2020-21 perchè è stata l'unica selezione dello slicer. L'1 fa riferimento alla formula del subtotale della tabella precedente.

Per trovare la squadra vincitrice del titolo NBA, basta ripetere un CERCA.X che in questo caso va a cercare la stagione (2020-21) nella tabella di origine, restituendo la squadra campione:

=CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[SQUADRA VINCITRICE])

Come aggiungere un titolo dinamico al grafico progressivo su Excel

Vogliamo che il nostro grafico progressivo abbia un titolo dinamico che indichi quanti titoli sono stati vinti dalle singole squadre, mettendole in ordine dalla più alla meno vincente. Iniziamo mettendo nella cella B41 la seguente formula:

=UNICI(FILTRO(TBL_DATA[SQUADRA VINCITRICE];TBL_DATA[NUMERO STAGIONE]<=CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE])))

In sostanza la funzione estrae i valori univoci delle singole squadre, prendendo solo quelle che abbiano vinto un titolo fino alla stagione considerata dal filtro. Si rende necessario l'utilizzo della colonna con il numero della stagione dato che 2020-21 è un testo e non un numero, in questo caso tutte le stagioni diventano un numero sequenziale (2010-11 sarà 1, 2011-12 sarà 2 e così via).

A questo punto, possiamo contare il numero di vittorie per singola squadra nella cella C41:

=CONTA.PIÙ.SE(TBL_DATA[SQUADRA VINCITRICE];B41#;TBL_DATA[NUMERO STAGIONE];"<="&CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE]))

La formula conta quante volte le squadre considerate abbiano vinto il titolo, prendendo tutti i valori univoci tramite l'utilizzo del # dato che UNICI crea un dynamic array.

A questo punto, possiamo ordinare le squadre dalla più alla meno vincente in D41:

=DATI.ORDINA.PER(B41#;C41#;-1)

La formula ordina le squadre (B41#) rispetto alle vittorie (C41#), prendendo dalla più alla meno vincente (-1).

Non ci resta che indicare ogni vittoria con il simbolo del trofeo in E41:

=RIPETI(CARATT.UNI(127942);CERCA.X(D41#;B41#;C41#))

La funzione RIPETI ripete il carattere unicode 127942 (che è la coppa), prendendo poi il numero di vittorie per ciascuna squadra dalla divisione precedente.

A questo punto, non ci resta che unire i due valori (nome squadra e trofei) nella cella F41:

=CONCATENA(E41#;" ";D41#)

Curiosamente, la stessa formula con CONCAT non funziona - ricordati di usare CONCATENA in questo caso.

In definitiva, il titolo del grafico in B49 prende tutti questi dati in un colpo solo:

=TESTO.UNISCI(" / ";VERO;F41#)

TESTO.UNISCI prenderà tutti i valori delle squadre e delle relative vittorie in un colpo solo, aggiornandosi in automatico alla selezione di una specifica stagione dallo slicer desiderato.

Inseriremo questo titolo in cima al grafico, permettendo di leggere più facilmente i risultati complessivi.

NB: saremmo potuti arrivare allo stesso risultato minimizzando i passaggi, ma le formule sarebbero state di difficile comprensione.

Come creare la base dati per il grafico progressivo su Excel

Non ci resta che inserire le singole squadre nelle celle da N7 a T7 (volendo si potrebbe fare con UNICI). A questo punto, in M8 definiamo quante siano le stagioni che ci servono per il grafico:

=FILTRO(TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE]<=CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE]))

In questo caso stiamo filtrando le stagioni prendendo solo quelle con un numero inferiore al valore relativo della stagione (2010-11 vale 1, 2011-12 vale 2 e così via).

Per ogni squadra, dovremo selezionare la colonna corretta per fare il matching con l'intestazione, tipo:

=FILTRO(TBL_DATA[NOME SQUADRA];TBL_DATA[NUMERO STAGIONE]<=CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE]))

Vediamo il caso dei Cleveland Cavaliers:

=FILTRO(TBL_DATA[CLEVELAND CAVALIERS];TBL_DATA[NUMERO STAGIONE]<=CERCA.X(B37;TBL_DATA[STAGIONE];TBL_DATA[NUMERO STAGIONE]))

La formula si ripete uguale per tutte le singole squadre. A questo punto dobbiamo definire un nome per ognuna delle squadre, che utilizzeremo per il grafico:

Scegli pure il nome che preferisci, l'importante è che prenda il valore relativo alla squadra giusta. In questo caso CAVS_1 prenderà tutti i valori creati da N8#, ovvero i valori delle vittorie dei Cleveland Cavaliers, che utilizzeremo poi per il grafico.

Se ti stai chiedendo come mai quasi tutte le celle siano vuote, il motivo è per la migliore visualizzazione sul grafico. Una volta inseriti tutti i valori, ho modificato il formato (Home > Numeri > Personalizzato) per le stagioni nelle quali la singola squadra non ha poi vinto il titolo NBA con ;;; che indica il valore vuoto. In questo caso, pur avendo dei numeri, non si visualizza nulla nella singola cella.

Per le stagioni dove invece la squadra è diventata campione NBA, ho inserito sempre da personalizzato i seguenti valori:

Per ognuna delle squadre, ho selezionato il valore che mi interessava (ad esempio Heat per i Miami Heat) e inserito il simbolo del trofeo. Quindi sarebbe "Heat" 🏆 per Miami Heat, "Cavs" 🏆per i Cleveland Cavaliers e così via. Per il simbolo del trofeo, ti è sufficiente fare un copia incolla del simbolo oppure cliccare sul tasto Windows (accanto ad ALT) e il . per far apparire le emoji su Excel.

Come creare il grafico finale progressivo su Excel

A questo punto, creiamo il grafico a Linee con indicatori e come origine dei dati inseriremo i singoli nomi inseriti in precedenza. Per esempio, per i Cleveland Cavaliers i valori della serie saranno =CREARE_GRAFICO_PROGRESSIVO_EXCEL.xlsx!CAVS_1 e così via per tutti gli altri. Dovremo aggiungere tutte le serie indipendentemente per creare il grafico.

Il titolo grande in cima indica unicamente quali siano le stagioni considerate, indicando solo una stagione se selezioniamo 2010-11 nello slicer:

=SE(B37="2010–11";"NBA CHAMPIONS IN 2010-11";CONCAT("NBA CHAMPIONS FROM 2010-11 TO ";B37))

Se invece sono selezionate più stagioni, il valore prenderà anche il valore finale dello slicer che si trova in B37.

Il grafico è stato modificato notevolmente e puoi prendere spunto dal file finale per scoprire meglio come siano composte le singole parti, ti segnalo solo che il logo della squadra campione è stato creato con un nome:

In sostanza LOGOCHECK si origina tramite un foglio nascosto chiamato LOGO sul quale sono presenti tutti i loghi, è un'immagine collegata al nome LOGOCHECK che prende semplicemente i singoli loghi alla selezione della squadra in C37. C37 indica la squadra vincitrice di quell'anno, quindi se selezioniamo 2020-21 prenderà il logo dei Bucks, se selezioniamo 2010-11 il logo dei Miami Heat e così via.

Spero che la guida ti sia stata utile, qui puoi scaricare il file definitivo per averlo sempre con te!