In questa guida vediamo come creare un grafico inset plot su Excel. Questo tipo di grafico prevede un grafico principale e uno secondario che appare "inserito" in quello principale per visualizzare meglio alcuni dati che potrebbero essere molto diversi dal resto del gruppo, ad esempio valori molto più grandi o molto più piccoli.
In questo caso ho preso un database con le testate nucleari per vari Paesi, dato che si presta bene per creare un grafico di questo genere. Russia e Stati Uniti ne hanno oltre 5.000 a testa, mentre il terzo Paese, la Cina, ne ha solamente 500. Nel grafico di recap superiore noti come, se avessimo solamente la parte in azzurro chiamata WARHEADS e non avessimo la parte chiamata DETAIL in arancione, sarebbe molto difficile leggere i singoli valori dalla Cina in poi perchè si discostano molto dai primi 2.
Vediamo quindi come realizzare un grafico simile in Excel, partendo dai nostri dati iniziali.
Come strutturare i dati per realizzare un grafico inset plot su Excel
Idealmente, i dati iniziali che vuoi utilizzare per creare un grafico inset plot su Excel dovrebbero essere in un formato di tabella. In questo modo, ti sarà molto più facile compiere tutte le operazioni successive di calcolo e revisione. Nel nostro caso:
- Abbiamo una tabella chiamata TBL_NUCLEAR con 3 colonne: Nation, Population e Warheads. Abbiamo quindi il nome della nazione, la popolazione totale e le testate nucleari. Come noti, i dati sono ordinati in ordine alfabetico. Questo è un aspetto importante perchè complica parecchio le formule che troverai nella guida, ma è fondamentale per rendere il tutto dinamico
- Abbiamo un valore di cutoff, in questo caso 30% ma è variabile. Questo è il valore fondamentale per definire dove spezzare i due grafici. In sostanza, avremo tutti i nostri dati ordinati e, qualora uno dei valori si discostasse da quello successivo per una percentuale superiore a quella indicata, i dati sarebbero troncati per creare il dettaglio. Anche questo è un aspetto rilevante per rendere il file il più dinamico possibile
Ho inserito volutamente sia la popolazione sia le testate in modo da farti vedere meglio come funzioni la formula, dato che in questo modo potresti prendere un qualunque database con dei valori riassuntivi e, date le due colonne, replicare esattamente i grafici della guida.
A noi servirà che i dati escano così, in 4 colonne distinte:
- Le singole nazioni
- I valori ordinati per tutte le nazioni
- Esclusivamente i valori per le nazioni che devono far parte del secondo grafico, ma non nel primo
- Esclusivamente le etichette dati per i valori dei dati che devono risiedere nel primo grafico, ma non nel secondo
Questi passaggi si rendono necessari per avere dei colori differenziati fra le varie nazioni per evidenziarle meglio sul grafico e per leggere i valori relativi a quelle nazioni che faranno parte del primo grafico.
Come creare il grafico principale del grafico inset plot su Excel
La soluzione per creare un grafico inset plot su Excel è stata realizzata con i dynamic array di Excel 365, quindi attenzione alla compatibilità con la tua versione nel caso volessi scaricare il file o provare a replicare i singoli passaggi.
Ho fatto parecchi tentativi prima di arrivare a una soluzione soddisfacente, che trovi qui riassunta:
=LET(labels;TBL_NUCLEAR[NATION];
values;TBL_NUCLEAR[WARHEADS];
cutoff;D2;
range;STACK.ORIZ(labels;values);
labels_header;INCLUDI(SCARTO(labels;-1;0);1);
values_header;INCLUDI(SCARTO(values;-1;0);1);
range_sorted;DATI.ORDINA(range;2;-1);
values_sorted;INDICE(range_sorted;0;2);
cutoff_check;(values_sorted/INDICE(values_sorted;CONFRONTA(values_sorted;values_sorted;0)-1))<=1-cutoff; cutoff_position;MIN(CONFRONTA(VERO;cutoff_check;0)); cutoff_value;INDICE(values_sorted;cutoff_position); cutoff_values;SE(values_sorted>cutoff_value;NON.DISP();values_sorted);
chart_labels;SE(VAL.ERRORE(cutoff_values);values_sorted;"");
values_final;STACK.ORIZ(range_sorted;cutoff_values;chart_labels);
headers_final;STACK.ORIZ(labels_header;values_header;values_header;values_header);
total;STACK.VERT(headers_final;values_final);
total)
Ora - questa formula è piuttosto complessa, ma voglio comunque provare a spiegartela. La logica principale è la seguente:
=LET(labels;TBL_NUCLEAR[NATION];
values;TBL_NUCLEAR[WARHEADS];
cutoff;D2;
- Labels: voglio che l'utente selezioni le etichette che preferisca, ovvero i valori che troveremo nelle serie sul grafico
- Values: voglio che l'utente selezioni l'intervallo dei dati che vuole, dato che ci permetterà di ordinarli
- Cutoff: voglio che l'utente selezioni un valore di cutoff a sua scelta, in formato percentuale
A questo punto, vediamo i primi calcoli:
range;STACK.ORIZ(labels;values);
labels_header;INCLUDI(SCARTO(labels;-1;0);1);
values_header;INCLUDI(SCARTO(values;-1;0);1);
range_sorted;DATI.ORDINA(range;2;-1);
values_sorted;INDICE(range_sorted;0;2);
- Range: il nostro intervallo, che affianca orizzontalmente etichette e valori (labels e values) tramite STACK.ORIZ
- Labels_header & Values_header: ci servono le intestazioni per entrambe, che recupero tramite INCLUDI e SCARTO. In questo caso prendo il primo valore, con INCLUDI, che troviamo quando spostiamo in sopra di una riga, tramite SCARTO. Quindi prenderò il valore della intestazione dei miei dati, che siano etichette o valori
- Range_sorted: a questo punto posso utilizzare DATI.ORDINA per avere un ordinamento decrescente, che si basa sui valori, del mio intervallo complessivo. Questo sarà il primo intervallo inserito sul grafico finale, dato che riprende le nazioni e le testate nucleari in ordine decrescente
- Values_sorted: stacco solo i valori ordinati utilizzando INDICE. Questi dati mi serviranno per definire dove spezzare il grafico in due parti
Vediamo ora il cuore della formula:
cutoff_check;(values_sorted/INDICE(values_sorted;CONFRONTA(values_sorted;values_sorted;0)-1))<=1-cutoff; cutoff_position;MIN(CONFRONTA(VERO;cutoff_check;0));
cutoff_value;INDICE(values_sorted;cutoff_position);
cutoff_values;SE(values_sorted>cutoff_value;NON.DISP();values_sorted);
chart_labels;SE(VAL.ERRORE(cutoff_values);values_sorted;"");
- Cutoff_check: questo è un controllo chiarendo se, per ciascuno dei valori che ho ordinato, il valore sia successivo si discosti di più della percentuale che l'utente ha indicato come cutoff. In sostanza posso scoprire, con un risultato VERO o FALSO, come sia composto il mio intervallo
- Cutoff_position: qui la formula prende il valore minimo del CONFRONTA del VERO nel controllo precedente. Dato che CONFRONTA trova la posizione relativa dei valori all'interno di un intervallo, in questo caso prenderemo il primo stacco, che sarà quello che determinerà dove si spezza il grafico. Fra tutti i casi nei quali i valori si discostino di più di una certa percentuale relativa, prenderemo i valori più grandi in assoluto
- Cutoff_value: qui viene recuperato il valore che determina dove il grafico andrà spezzato, dato che INDICE ci restituisce il valore dell'intervallo ordinato precedente corrispondente alla posizione di rottura
- Cutoff_values: se i valori sono superiori al valore di cutoff, allora mettiamo un errore con NON.DISP() per non vedere il valore nel grafico. Se invece non è superiore al valore di cutoff, allora vogliamo i nostri dati. Questo sarà il secondo intervallo inserito sul grafico, corrispondente ai dati dei quali non vogliamo vedere la parte di etichette dati e che vedremo nel secondo grafico
- Chart_labels: questo sarà il nostro terzo intervallo. Dato che vogliamo esclusivamente l'etichetta dati del grafico per i valori superiori al cutoff, diciamo che se c'è un errore nei valori di cutoff precedenti, allora mettiamo il valore corrispondente dell'intervallo ordinato. Se così non è, mettiamo vuoto. Questo sarà il terzo intervallo inserito sul grafico, corrispondente ai dati superiori al cutoff_value e per i quali vogliamo vedere il valore totale
A questo punto abbiamo tutto. Vediamo come completare:
values_final;STACK.ORIZ(range_sorted;cutoff_values;chart_labels);
headers_final;STACK.ORIZ(labels_header;values_header;values_header;values_header);
total;STACK.VERT(headers_final;values_final);
total)
- Values_final: qui mettiamo assieme in uno stack di 4 colonne ben tre intervalli, cioè l'intervallo iniziale ordinato (range_sorted), i valori inferiori al cutoff (cutoff_values) e i valori superiori al cutoff (chart_labels)
- Headers_final: qui inseriamo le intestazioni. Prendiamo una volta i label, cioè l'indicazione di che cosa siano i dati testuali, mentre prendiamo tre volte l'header dei valori che si ripete uguale
- Total: qui impiliamo header e valori, per completare il grafico
A questo punto, abbiamo tutto quello che ci serve per creare il nostro grafico principale.
NB: Negli esempi che vedrai, per il momento, abbiamo inserito il valore 10% nel cutoff.
Come creare il grafico secondario del grafico inset plot su Excel
Vediamo ora come realizzare il grafico secondario del nostro grafico inset plot su Excel. Questo ci servirà per creare un grafico più piccolo che inseriremo all'interno dello spazio utilizzato dal nostro grafico principale, in modo da leggere correttamente i valori delle singole nazioni.
La formula è simile alla precedente ma la semplifica nella parte finale:
=LET(labels;TBL_NUCLEAR[NATION];
values;TBL_NUCLEAR[WARHEADS];
cutoff;D2;
range;STACK.ORIZ(labels;values);
labels_header;INCLUDI(SCARTO(labels;-1;0);1);
values_header;INCLUDI(SCARTO(values;-1;0);1);
range_sorted;DATI.ORDINA(range;2;-1);
values_sorted;INDICE(range_sorted;0;2);
cutoff_check;(values_sorted/INDICE(values_sorted;CONFRONTA(values_sorted;values_sorted;0)-1))<=1-cutoff;
cutoff_position;MIN(CONFRONTA(VERO;cutoff_check;0));
cutoff_value;INDICE(values_sorted;cutoff_position);
headers_final;STACK.ORIZ(labels_header;values_header);
values_final;FILTRO(range_sorted;values_sorted<=cutoff_value;"");
total;STACK.VERT(headers_final;values_final);
total)
Vediamo nello specifico cosa succede nella parte finale:
- Headers_final: in questo caso le intestazioni sono solo due, per indicare che cosa siano i dati testuali e i dati numerici del grafico
- Values_final: per filtrare, ci basta prendere tutti quei valori che siano almeno pari al valore di cutoff, ovvero cutoff_value
- Total: in questo caso, impiliamo gli header e i valori e abbiamo finito
Come dicevamo, il cutoff è del 10%. Dato che gli Stati Uniti hanno 5244 testate nucleari e la Russia 5889, il valore di cutoff è quello degli Stati Uniti perchè =5244/5889=89%. Quindi vedremo solamente la Russia come valore nel primo grafico, mentre nel grafico secondario di dettaglio vedremo tutti i dati dagli Stati Uniti in poi.
A questo punto, abbiamo tutto quello che ci serve per creare il nostro grafico inset plot. C'è solo un piccolo accorgimento da seguire per aiutarci nelle etichette dati del primo grafico.
Dato che non vogliamo visualizzare niente per i valori se non sono dati superiori al cutoff, possiamo inserire il formato personalizzato Standard;;; per nascondere tutti i valori che non ci interessano. Io ho inserito questo formato personalizzato in tutte le celle che fanno parte del nostro terzo intervallo, chiamato sempre WARHEADS.
Vediamo ora come creare i due grafici che completano il nostro grafico inset plot.
Come mettere insieme i due grafici del grafico inset plot su Excel
Abbiamo le due basi dei dati che ci interessano, ora non ci resta che vedere come mettere insieme i due grafici per realizzare il nostro grafico inset plot su Excel.
Clicca sul primo intervallo dei dati, quello del grafico principale, per poi andare su Inserisci > Grafico. Per una guida dettagliata sui grafici dinamici, dove ad esempio vediamo anche il tema dei nomi personalizzati, puoi andare qui.
A questo punto vedrai che ci sono tre diverse colonne di valori, scegli pure il colore che preferisci. Considera che il primo colore non si vedrà, il secondo colore sarà usato per i valori sotto al cutoff mentre sarà il terzo quello visualizzato per i valori oltre il cutoff.
Una volta creato il grafico, dovrai andare nella sezione del Formato serie di dati e, nelle opzioni serie, inserire Sovrapposizione serie pari a 100%. Questo perchè vogliamo che le serie siano una sopra l'altra, per realizzare il grafico inset plot è la soluzione più comoda in assoluto in modo da poter differenziare i colori con facilità.
Una volta inserito il 100%, vedrai unicamente due colori. Perfetto, ora siamo quasi pronti per completare il nostro grafico. Ho spostato il titolo del grafico sulla sinistra mettendo WARHEADS in maiuscolo con il colore che ho utilizzato per i valori oltre al cutoff.
Seleziona la terza serie per inserire le etichette dati, in questo caso saranno tutti uguali i nomi quindi devi selezionare l'ultima da Opzioni grafico nel Formato area grafico.
Puoi cliccare sul + accanto al grafico oppure cliccare sul grafico > Struttura grafico > Aggiungi elemento grafico > Etichette dati. Come dicevamo, vedremo solamente la Russia perchè il cutoff indicato è del 10%, quindi dagli Stati Uniti in giù c'è una distanza almeno del 10% con la Russia stessa.
A questo punto il primo grafico è completato. Non ci resta che inserire il secondo.
I passaggi sono molto più semplici in questo caso, dato che è un semplice grafico a colonne raggruppate. Pure in questo caso inseriamo le etichette dati e inserisco lo stesso colore dei valori che nel grafico precedente sono inferiori al cutoff, mettendo poi il titolo DETAIL con lo stesso colore giusto per chiarezza. Ho anche inserito un contorno, sempre dello stesso colore, per entrambi.
In conclusione, avrai un risultato come questo, che è completamente dinamico. Inserendo infatti 30% nel cutoff, come noti, avremo nella parte azzurra sia gli Stati Uniti sia la Russia - mentre la Cina, che ha 500 testate nucleari ed è molto distanziata, sarà il primo valore inserito nel nostro grafico secondario. Questo sforzo è necessario per avere un file completamente personalizzabile, che puoi applicare in qualsiasi situazione.
Questo tipo di disparità fra i dati, come vediamo, giustifica l'utilizzo di un grafico inset plot, dato che Cina / Stati Uniti = 500/5244 = 9.5%. I valori della Cina sono solo il 9.5% dei valori degli Stati Uniti, quindi sarebbero veramente difficili da leggere nel grafico principale. Non parliamo poi di valori ancora inferiori, come i 90 di Israele o i 30 della Corea del Nord.
Il grafico è piuttosto complesso, quindi sarebbe davvero molto utile avere delle funzioni personalizzate LAMBDA per ricrearlo velocemente e portarlo su tutti i nostri file. Vediamo come fare.
Come creare delle funzioni personalizzate per creare un grafico inset plot su Excel in automatico
Utilizzeremo le funzioni LAMBDA per creare un grafico inset plot su Excel in automatico. Ci basterà prendere le funzioni precedenti e fare una prova per verificare che sia tutto a posto: come logica, all'utente vogliamo far inserire labels, values e cutoff. Quindi, questi saranno i parametri che useremo nelle LAMBDA.
Proviamo a fare un test, stavolta, sulla popolazione e non più sulle testate nucleari.
Il concetto è simile a quello precedente, ma dobbiamo staccare, necessariamente, labels, values e cutoff. Questi saranno infatti inseriti dall'utente e non possono più essere parte della funzione LET. Facciamo una prova:
=LAMBDA(labels;values;cutoff;
LET(range;STACK.ORIZ(labels;values);
labels_header;INCLUDI(SCARTO(labels;-1;0);1);
values_header;INCLUDI(SCARTO(values;-1;0);1);
range_sorted;DATI.ORDINA(range;2;-1);
values_sorted;INDICE(range_sorted;0;2);
cutoff_check;(values_sorted/INDICE(values_sorted;CONFRONTA(values_sorted;values_sorted;0)-1))<=1-cutoff;
cutoff_position;MIN(CONFRONTA(VERO;cutoff_check;0));
cutoff_value;INDICE(values_sorted;cutoff_position);
cutoff_values;SE(values_sorted>cutoff_value;NON.DISP();values_sorted);
chart_labels;SE(VAL.ERRORE(cutoff_values);values_sorted;"");
values_final;STACK.ORIZ(range_sorted;cutoff_values;chart_labels);
headers_final;STACK.ORIZ(labels_header;values_header;values_header;values_header);
total;STACK.VERT(headers_final;values_final);
total))(TBL_NUCLEAR[NATION];TBL_NUCLEAR[POPULATION];D2)
I valori finali dopo total si inseriscono solo per provare la formula: TBL_NUCLEAR[NATION] diventa labels, TBL_NUCLEAR[POPULATION] diventa values e D2 diventa il nostro cutoff. In questo caso troviamo, con un cutoff del 10%, che c'è un abisso dalla Cina agli Stati Uniti ed è per questo che gli Stati Uniti diventano il nostro cutoff (1412M di persone contro 333M). Abbiamo quindi India e Cina nel primo grafico, mentre nell'altro tutti gli altri, trainati dagli Stati Uniti.
Anche in questo caso c'è un formato personalizzato per visualizzare i dati in milioni e non visualizzare i dati che non ci interessano per le etichette dei dati: lo puoi vedere dal file ma è ,,"M";;;. Se il valore è positivo, allora avremo i milioni con M, altrimenti non ci sarà niente (valore vuoto).
La formula funziona e quindi ora possiamo creare la nostra LAMBDA, che chiameremo INSET_PLOT_MAIN.
Clicchiamo su Formule > Definisci nome.
A questo punto inseriamo, come nome, INSET_PLOT_MAIN e un breve commento testuale. Nella sezione Riferito a inseriremo invece la nostra formula, togliendo tutto quello che c'è dopo total e stando bene attenti a non perderci di vista le parentesi.
A questo punto, chiunque potrebbe creare il grafico principale del nostro grafico inset plot. Ci basterà inserire =INSET_PLOT_MAIN e poi inserire i valori testuali e numerici che desideriamo, oltre al cutoff. Come noti, questa soluzione è esattamente uguale alla precedente, ma è molto più semplice da scrivere.
Lo stesso concetto si ripete poi per il grafico secondario:
=LAMBDA(labels;values;cutoff;
LET(range;STACK.ORIZ(labels;values);
labels_header;INCLUDI(SCARTO(labels;-1;0);1);
values_header;INCLUDI(SCARTO(values;-1;0);1);
range_sorted;DATI.ORDINA(range;2;-1);
values_sorted;INDICE(range_sorted;0;2);
cutoff_check;(values_sorted/INDICE(values_sorted;CONFRONTA(values_sorted;values_sorted;0)-1))<=1-cutoff;
cutoff_position;MIN(CONFRONTA(VERO;cutoff_check;0));
cutoff_value;INDICE(values_sorted;cutoff_position);
headers_final;STACK.ORIZ(labels_header;values_header);
values_final;FILTRO(range_sorted;values_sorted<=cutoff_value;"");
total;STACK.VERT(headers_final;values_final);
total))(TBL_NUCLEAR[NATION];TBL_NUCLEAR[POPULATION];D2)
Anche qui facciamo la nostra prova riprendendo i dati precedenti e in effetti la formula funziona. Creeremo quindi, seguendo lo stesso procedimento, anche una formula chiamata INSET_PLOT_DETAIL che ci permetterà di ricreare velocemente anche il grafico di dettaglio, sempre tramite una LAMBDA.
Il risultato finale, se utilizzi le LAMBDA, sarà piuttosto semplice da ottenere. Potrai quindi creare dei grafici inset plot usando le due LAMBDA per poi creare dei grafici a partire dai due intervalli, inserendoli nella stessa zona sul foglio per ottenere questo effetto zoom senza difficoltà!
Conclusioni
Il grafico inset plot è un grafico particolare, che può tornare particolarmente utile se hai dati molto diversi fra di loro. In questo caso, vedere come si realizza su Excel può esserti utile perchè da un lato puoi utilizzare dei trucchi applicabili anche su altri grafici, mentre con le LAMBDA puoi velocizzare enormemente la creazione di questi grafici complessi. Le formule di base sono piuttosto complesse, ma penso si potranno semplificare in futuro magari con nuove funzionalità di Excel.
Valuta se possa valere la pena di utilizzare un grafico simile, la prossima volta che presenti dati e ti pare di non dare abbastanza risalto a quelli che sono valori meno rilevanti dei principali!
Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel.
Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, IMI Orton, Primadonna e SISAL, oltre a PMI e startup di diverso genere.
Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.
Vuoi saperne di più? Prenota una call gratuita di 15′!
