Trucchi con i grafici excel

Ricordo che i miei lunedì mattina lavorativi erano monopolizzati, negli anni più recenti, dalla generazione e analisi dei report sul traffico.
Il ciclo prevedeva: aggiornamento dei dati in vari file Excel, sistemazione delle inevitabili paturnie dei grafici Excel, trasferimento dei grafici stessi in documenti Power Point. A seguire: analisi, considerazioni e predisposizione delle azioni conseguenti.
Da un lato, la prima parte doveva essere fatta con precisione e rigore, ma tutto sommato richiedeva solo manualità. La seconda parte, invece, era quella che esigeva pensiero, ma, per forza di cose, aveva a disposizione il tempo lasciato dalla prima.
Ovvio, quindi, che provassi ad automatizzare per quanto possibile l’aggiornamento dei file Excel. Oltre che una necessità, era in effetti anche un divertimento, perché Excel offre una quantità di trucchi sorprendenti per semplificare il lavoro con i grafici, e non solo.
Di seguito riporto un paio di trucchi con i grafici Excel, utili, in alcune occasioni, ad automatizzare e, perché no, a migliorare l’aspetto della rappresentazione grafica dei dati.


Un esempio banale

Per fissare le idee, immaginiamo di voler generare un report sulla spesa familiare su base mensile: classificando le spese in categorie, riportandone l’andamento in un classico grafico a colonne, aggiungendo poi le considerazioni del caso. Per inciso, è proprio la presenza di queste ultime che giustifica la produzione del report.

Con numeri a caso, supponiamo che i dati raccolti siano come nella tabella:

Una caratteristica tipica di una tabella del genere è quella di non essere ordinata, semplicemente perché ogni tanto si aggiungerà o modificherà una categoria, e gli importi varieranno di mese in mese. Sarebbe desiderabile, senza perdere tempo a ordinare la tabella, ottenere un grafico come quello che segue, che elenca le categorie in ordine decrescente di importi, evidenziando gli importi stessi.Un paio di trucchi con i grafici excel: il grafico desiderato

Per raggiungere lo scopo saranno necessari due passi:

  1. ricavare, dalla tabella dei dati, una seconda tabella su cui generare il grafico, che sistemi le categorie di spesa in ordine decrescente di importo;
  2. applicare qualche modifica al grafico standard di Excel / LibreOffice, per renderlo più sexy.

La descrizione che segue può sembrare complessa, ma lo è in effetti solo apparentemente. Le formule e i grafici utilizzati in questo esercizio sono comunque nel file scaricabile nel formato ods di LibreOffice Calc, da qui.


Un paio di trucchi con i grafici Excel: il primo

Per il primo passo occorre ricavare, per ogni riga, la posizione che occupa l’importo nell’ordine decrescente, dal massimo al minimo. Qui viene in aiuto la formula del conta.se: basterà infatti contare, per ogni importo, di quanti altri importi della tabella risulta minore, ed aggiungere 1.
L’elemento massimo sarà minore di zero elementi, quindi la sua posizione sarà 1. Il secondo importo per grandezza avrà un solo elemento di cui è inferiore, quindi la sua posizione sarà 2.

Tutto bene? Purtroppo no. Se ci sono due importi uguali, il metodo fallisce:

tabella con indicatore di posizione, ma ambiguo

Pulizia casa e Luce e gas hanno lo stesso importo e quindi si troverebbero nella stessa posizione (la 4a), mentre la 5a posizione è vuota.

Sistemiamo il conflitto

Un modo semplice per sistemare il conflitto è quello di calcolare la posizione di ogni riga non sugli importi originari, ma su una loro versione leggermente variata in modo pseudo casuale. Molto semplicemente, si aggiunge una colonna che copia gli importi, moltiplicandoli per una quantità casuale, molto vicina a 1:

importo+  =  importo * ( 1 + casuale()*0,0001)    [1]

La tabella che segue mostra l’effetto della modifica:

tabella con indice di posizione, non ambigua

Il conflitto è stato risolto: a Pulizia casa è stata assegnata la 4a posizione e a Luce e gas la 5a. Gli importi relativi sono uguali, quindi è irrilevante che l’assegnazione sia questa e non l’altra possibile. L’importante è che siano state assegnate due posizioni diverse.


L’efficacia del metodo non è garantita in assoluto, è sempre possibile che la formula [1] assegni alle due categorie lo stesso numero casuale(), compreso tra 0 e 1. Finora non sono incappato in questo remoto caso, pur avendo applicato varie volte il metodo.


Costruiamo la tabella su cui generare il grafico

A questo punto ci sono tutti gli elementi per costruire la tabella su cui generare il grafico. Basterà inserire in una colonna le posizioni da 1 al numero di categorie (8, nel nostro esempio) poi ricercare i dati di categoria e importo dalla tabella originaria, in base alla posizione. Si applica cioè ripetutamente la formula cerca.vert(elemento;matrice;colonna;0).
Ed ecco materializzarsi la tabella che si aggiorna automaticamente conservando l’ordine decrescente degli importi.

tabella ordinata

 

Il grafico standard

Il classico grafico a colonne si porta dietro assi, griglie ed altri elementi che non servono in questo caso, mentre ne mancano altri, come i valori puntuali delle varie categorie. Con LibreOffice Calc (l’equivalente open source e gratuito di Excel), si avrebbe un grafico così:

trucchi con i grafici excel: grafico standard

Non c’è nulla che non vada, ci sono solo, come detto, degli elementi che distraggono (assi, griglia) e manca l’evidenza dei valori puntuali degli importi.

Un paio di trucchi con i grafici Excel: il secondo

Non è complicato intervenire sul grafico, occorre solo giocare un po’ con le impostazioni.
Doppio click sul grafico, per entrare nella modalità di modifica, e poi effettuare le operazioni indicate di seguito, sempre accertandosi di restare in modalità modifica:

  1. spostiamo le categorie all’interno delle colonne:
    • doppio click sull’asse x;
    • nel menu che si apre, click su Posizionamento, alla voce Posiziona didascalie scegliere l’opzione Vicino all’asse (altro lato);
    • digitando ok si chiuderebbe l’operazione, ma i caratteri avrebbero un colore nero su blu, con problemi di leggibilità;
  2. modifichiamo il colore dei caratteri delle categorie:
    • prima da dare ok, scegliere la voce Effetti carattere, e modificare il colore dei caratteri, esempio con il giallo che contrasta bene con il blu;
  3. rendiamo invisibile la linea dell’asse x:
    • prima di dare l’ok, scegliere la voce Linea dal menu, poi all’opzione Stile scegliere l’opzione Nessuno;
    • finalmente diamo l’ok e vediamo l’effetto delle modifiche finora apportate;
  4. aumentiamo la larghezza delle colonne, in modo che le categorie siano tutte dentro le rispettive colonne:
    • selezioniamo la serie con un click su una qualunque colonna, poi click con il destro e selezioniamo la voce Formatta serie di dati;
    • dal menu che si apre, selezionare la voce Opzioni; modificare il valore della Spaziatura da 100% (valore di default) al valore 20%; dare ok;
    • le colonne si allargano, riducendo gli spazi vuoti;
  5. eliminiamo l’asse y:
    • click con il destro del mouse all’interno del grafico, nei pressi del bordo destro, e selezionare la voce Inserisci / elimina assi; togliere la spunta solo sull’asse y (l’asse x deve rimanere, per poter visualizzare le categorie);
  6. inseriamo i valori puntuali degli importi:
    • selezionare la serie di colonne con un click su una colonna, poi click con il destro su una qualunque colonna e selezionare la voce Inserisci etichetta dati; poi click su ok;
    • selezionare la serie di etichette con un click su una etichetta, poi click con il destro su una qualunque etichetta e scegliere la voce Formatta etichetta dati;
      • dal menu che si apre selezionare la voce Tipo di carattere e scegliere il grassetto; poi selezionare la voce Effetti carattere e scegliere il colore voluto (nel mio esempio, ho scelto il rosso); quindi click su ok;
  7. ultimo passo, cancellare la griglia orizzontale:
    • portare il mouse su una delle righe della griglia, click con il destro, selezionare la voce: Elimina griglia principale;
  8. click fuori dal grafico, per uscire dalla modalità modifica e il lavoro è finito!

Un paio di trucchi con i grafici Excel: variante grafica

Rimane da segnalare solo una possibile variante grafica, che si può inserire anche solo a questo punto.
Entrare in modalità modifica con un doppio click sull’immagine, poi click con il destro all’interno del grafico, vicino al bordo destro. Dal menu che compare, scegliere la voce Tipo di grafico e poi selezionare il grafico a barre, invece di quello a colonne. Il risultato:

trucchi con i grafici excel: variante grafico a barre

Se non dovesse piacere l’ordinamento dal minore al maggiore, basterebbe infine cambiare, nell’ultima tabella, i valori nella colonna Posizione, inserendo i valori da 8 a 1, invece che da 1 a 8. Dati e grafico si aggiornano all’istante!

Immagine di apertura del post di 200 Degrees da Pixabay.

Scritto da:

Pasquale

Mi chiamo Pasquale Petrosino, radici campane, da alcuni anni sulle rive del lago di Lecco, dopo aver lungamente vissuto a Ivrea.
Ho attraversato 40 anni di tecnologia informatica, da quando progettavo hardware maneggiando i primi microprocessori, la memoria si misurava in kByte, e Ethernet era una novità fresca fresca, fino alla comparsa ed esplosione di Internet.
Tre passioni: la Tecnologia, la Matematica per diletto e le mie tre donne: la piccola Luna, Orsella e Valentina.
Potete contattarmi scrivendo a: p.petrosino@inchiostrovirtuale.it