User‎ > ‎

Grafici a Dispersione Excel - Griglie, Zoom, Indicatori e Intersezioni

I grafici a dispersione offrono molte possibilità di personalizzazione. Si può letteralmente giocare a disegnare assi e griglie aggiungendo serie create ad hoc.
In questo articolo affronterò questo aspetto utilizzando, come serie, matrici generate da nomi definiti.
L'obiettivo è quello di consentire facilmente a chi dispone di un grafico a dispersione già fatto di migliorarne l'aspetto senza dover aggiungere dati al proprio foglio di lavoro.
 
A questo riguardo nelle versioni di Excel 2007 e 2010 la procedura da seguire è più complicata. Mentre un nome che definisce un range è accettato, in queste versioni non è possibile utilizzare un nome definito che restituisce una matrice per definire i Valori della Serie. Almeno non direttamente. Infatti è possibile superare il problema e ingannare i controlli.
La procedura da seguire è la seguente:
1) definire un nome che restituisce un range (ad esempio nome rng =Foglio1!$A$1)
2) utilizzare il nome appena definito per specificare i valori della Serie (il nome va usato con percorso completo es. in Valori Y =Foglio1!rng)
3) modificare il nome in modo che restituisca ciò che si desidera, anche una matrice ad esempio modificando il nome rng =RIF.RIGA($A$1:$A$10)
 
Con Excel 2003 tutto è più semplice e nel resto dell'articolo la descrizione delle procedure si riferirà a questa versione.
Da qualcosa bisogna partire ... ipotizziamo una situazione, molto semplice, come mostrato nell'immagine
 
 
Uno degli aspetti più interessanti dei grafici è la possibilità di renderli dinamici all'accodamento di nuovi dati.
Nel grafico a dispersione che trovate nel file 1.xls, Serie1 si riferisce all'intervallo statico A2:B5
 
 
Esistono diverse possibili soluzioni per definire nomi al fine di rendere dinamici gli intervalli e tutte presentano vantaggi e svantaggi. Non è mia intenzione, ora, approfondire questo aspetto.
Il primo passo però consiste proprio in questo quindi utilizziamo una delle tante soluzioni e definiamo 2 nomi da usare nel grafico come Valori delle X e delle Y:
rng_x=SCARTO($A$1;1;;CONTA.SE($A:$A;"<>")-1)
rng_y=SCARTO(rng_x;;1)
 
Versione inglese
rng_x=OFFSET($A$1,1,,COUNTIF($A:$A,"<>")-1)
rng_y=OFFSET(rng_x;;1)
 
La formula che definisce rng_x non ha bisogno di molte spiegazioni. Per ogni dubbio consultate la guida della funzione SCARTO.
In sostanza ridimensiona l'intervallo A1 di tante righe -1 quante sono quelle valorizzate nella colonna A, spostandolo poi di una cella in basso (per escludere l'intestazione). rng_y è semplicemente lo stesso range spostato di una colonna a destra.
Una volta definiti i nomi sarà possibile sostituirli agli intervalli statici nella dialogs Dati di origine.
 
 
E' da notare che la colonna A deve essere tenuta libera da altri dati onde evitare che il CONTA.SE restituisca un errato numero di righe.
Sarà ora possibile aggiungere nuovi dati senza bisogno di aggiornare gli intervalli della serie (vedere il file 2.xls).

Passo successivo, il più complicato, è quello di sostituire la Griglia con una serie che unirà i punti agli assi.
In sostanza si vuole ottenere quanto mostrato nell'immagine qui sotto:
 
 
Le linee tratteggiate sono ottenute da una sola serie. I punti formeranno una sorta di spirale, che inizia nell'origine degli assi, segue l'asse delle ordinate, passa per il punto della serie dati, scende sull'asse delle ascisse per tornare nell'origine e continuare con analogo percorso al punto successivo della serie.
Questa nuova serie verrà colorata a piacimento utilizzando un motivo linea idoneo:
 
 
Per ogni punto della serie dati occorrono 4 punti per disegnare la nuova griglia.
Un esempio forse chiarirà ulteriormente. Consideriamo i due punti con coordinate:
(2,4)
(5,6)
per disegnare la griglia saranno necessari 8 punti:
(0,0)
(0,4)
(2,4)
(2,0)
(0,0)
(0,6)
(5,6)
(5,0)


Le linee che seguono il percorso degli assi verranno da questi nascoste. A questo riguardo sarà necessario utilizzare per le linee degli assi un motivo linea dello stesso colore della griglia e non tratteggiato.
In alternativa, come vedremo, si potrà disegnare nuove serie che funzioneranno in vece degli assi stessi.

Vediamo però come definire i due nomi che danno origine a questa spirale di tratteggi che sostituisce la griglia:
 
linee_x=MATR.PRODOTTO(--(((PARI(RESTO(RIF.RIGA(SCARTO($A$1;;;RIGHE(rng_x)*4))-1;4))=2)*(INT((RIF.RIGA(SCARTO($A$1;;;RIGHE(rng_x)*4))-1)/4)+1))=RIF.COLONNA(SCARTO($A$1;;;;RIGHE(rng_x))));rng_x)

linee_y=MATR.PRODOTTO(--(((DISPARI(RESTO(RIF.RIGA(SCARTO($A$1;;;RIGHE(rng_y)*4))-1;4))=3)*(INT((RIF.RIGA(SCARTO($A$1;;;RIGHE(rng_y)*4))-1)/4)+1))=RIF.COLONNA(SCARTO($A$1;;;;RIGHE(rng_x))));rng_y)

Versione inglese:
linee_x=MMULT(--(((EVEN(MOD(ROW(OFFSET($A$1,,,ROWS(rng_x)*4))-1,4))=2)*(INT((ROW(OFFSET($A$1,,,ROWS(rng_x)*4))-1)/4)+1))=COLUMN(OFFSET($A$1,,,,ROWS(rng_x)))),rng_x)

linee_y=MMULT(--(((ODD(MOD(ROW(OFFSET($A$1,,,ROWS(rng_y)*5))-1,4))=3)*(INT((ROW(OFFSET($A$1,,,ROWS(rng_y)*4))-1)/4)+1))=COLUMN(OFFSET($A$1,,,,ROWS(rng_x)))),rng_y)

La formula ha il difficile compito di trasformare una serie di n valori in una idonea di n*4 valori. In sostanza usata in un nome crea un vetore con tutti i valori delle x o delle y.
Però la serie non ha bisogno di tanti sforzi ... perchè per passare i valori al grafico è possibile utilizzare una matrice rettangolare. Excel pensa da solo a raddrizzarla per ottenere una serie progressiva di valori ... così molto più semplicemente si può definire i nomi con:
linee_x=rng_x*{0;1;1;0}
linee_y=rng_y*{0;0;1;1}
Me ne sono accorto dopo essere diventato matto a cercare di raddrizzarla ... meglio tardi che mai :-)
Non è questo il solo aspetto strano dell'uso di matrici come serie dati, quindi apriamo una parentesi.
 
Intanto è bene specificare in che modo la matrice viene *raddrizzata*. Probabilmente sembrà ovvio, per me a questo punto nulla lo è più. L'ordine con cui i valori vengono usati è dettato da un movimento prima sulla riga da sinistra a destra e poi sulla colonna. Più semplicemente direi che l'ordine rispecchia quello di un ciclo for each ... e probabilmente è ciò che avviene. Dico questo perchè è possibile passare ad una serie come valori delle X e delle Y matrici dimensionate diversamente.
Ad esempio sarebbe del tutto lecito passare queste due matrici:
{1;2;3;4\2;4;6;8\3;6;9;12} (3 righe x 4 colonne)
{1;2;3;4;5;6\2;4;6;8;10;12} (2 righe x 6 colonne)
Ottenendo una serie di 12 punti.
Inoltre è possibile passare matrici con un numero totale di elementi diversi. Ad esempio queste due matrici:
{1;2;3;4\2;4;6;8} (2 righe x 4 colonne 8 elementi totali)
{1;2;3;4;5;6\2;4;6;8;10;12} (2 righe x 6 colonne 12 elementi totali)
Restituiscono una serie di 8 punti.
Queste ultime due osservazioni possono essere estese anche ai range.
Però ... In un grafico la serie usata come valori delle Y accetta solo range di una colonna o una riga. Per i valori delle X non esiste questo problema, è possibile passare un range rettangolare.
Come usare invece un range rettangolare per passare i valori delle Y alla luce di quanto detto in precedenza?
Semplicemente trasformando il range in una matrice ... e per farlo basta definire un nome come prodotto tra il range e il valore 1. Così ad esempio volendo riferire i valori delle Y al range A1:B4 basterà definire un nome come:
=Foglio1!$A$1:$B$4*1
Il nome così definito verrà accettato nella serie come valori delle Y...
A onor di cronaca, queste particolarità, che sono per me una *novità e in gran parte personali scoperte*, erano però già state precedentemente discusse ... un po' mi rode ... ma ne ho dovuto prendere atto :-) ... e con questo chiudo la parentesi.

Una volta definiti i due nomi linee_x e linee_y (che si riferiscono a rng_x e rng_y precedentemente definiti) sarà sufficiente aggiungere una serie al grafico che li richiama come valori di X e Y.


Ultimo passo, è quello di definire due serie che sostiuiranno gli assi e consentiranno un uso delle etichette per definire le scale. Occorrono 4 nuovi nomi:
asse_x_x=SE(FREQUENZA(rng_x;rng_x);rng_x;0)
asse_x_y=asse_x_x*0
asse_y_x=asse_y_y*0
asse_y_y=SE(FREQUENZA(rng_y;rng_y);rng_y;0)

Versione inglese:
asse_x_x=IF(FREQUENCY(rng_x,rng_x),rng_x,0)
asse_x_y=asse_x_x*0
asse_y_x=asse_y_y*0
asse_y_y=IF(FREQUENCY(rng_y,rng_y),rng_y,0)

Per disegnare ogni asse sono necessari n+1 punti, dove n è il numero di punti della serie dati. E' infatti necessario avere un punto sull'asse per ogni punto della serie dati più l'origine degli assi (0,0).
La funzione frequenza fa esattamente al nostro caso. Restituisce infatti sempre una matrice di n+1 elementi rispetto al secondo argomento matrice_classi.

Definiti i nomi terminiamo aggiungendo le serie.


Gli assi andranno nascosti (deselezionandoli dalle Opzioni grafico) e le nuove serie che li sostituiscono colorate e dettagliate con cura. Aggiungeremo le etichette (Valore X per la serie asse x e Valore Y per la serie asse y) e sfrutteremo le barre di errore per visualizzare i segni di gradazione.
Il risultato finale è mostrato qui sotto:


Nel file dispersione_griglie.xls troverete il grafico così ottenuto. Sarà possibile aggiungere nuovi dati senza dover riadattare nessuna serie.

Stesso risultato è ancora più semplicemente ottenibile utilizzando le barre di errore.
Una volta aggiunta la serie sarà sufficiente utilizzare le barre di errore con un intervallo di errore personalizzato. Basterà quindi aggiungere nel valore negativo delle barre di errore X il riferimento al nome dei valori delle X e nelle barre di errore delle Y il riferimento al nome dei valori delle Y.
Sarà poi possibile personalizzare il formato delle linee.
L'esempio nel file dispersione_griglie_con_barre_errore.xls
Per altri esempi e soluzioni con le barre di errore segui il link.

E' possibile anche creare una griglia completa che passa dai punti come mostrato qui sotto e come è possibile vedere nel file dispersione_griglie_complete.xls
 
 
In questo caso è necessario aggiungere due nomi e una serie, che verranno usati insieme ai nomi precedentemente definiti.
 
Sorge un problema al momento in cui i dati che compongono la serie sono molti. La visualizzazione delle etichette sugli assi rischia di diventare confusa e disordinata. Come risolvere?
Basta giocare ancora un po'! :-)


Spostandosi di qualche punto ...



Nel file dispersione_con_monitor.xls  viene visualizzata una serie di dati molto lunga con un sistema che permette di focalizzare un intervallo ridotto consentendone una vista dettagliata. Il dettaglio si sposta cliccando sulla barra ed è possibile definire la grandezza come numero di punti visualizzati. Il file contiene solo una piccola macro il cui unico compito è aggiornare i valori minimo e massimo della scala dell'asse x nel grafico che funziona da monitor. Per la definizione delle numerose serie sono utilizzati solo nomi definiti. Il grafico è dinamico consente quindi d'accodare nuovi dati.

Nel file Indicatori_grafico_dispersione.xls e Indicatori_grafico_dispersione2.xls ancora una volta vengono usati solo nomi definiti per aggiungere serie. In questo caso la serie è utilizzata per disegnare degli indicatori attorno ai punti del grafico. Gli indicatori hanno una forma a numero di lati variabile, variabili sono anche le dimensioni  della figura, l'angolo di rotazione e lo stile. Vengono utilizzate delle macro per nascondere le linee che uniscono gli indicatori. Quest'ultimo è più un gioco che una vera utilità e serve ancora ad evidenziare come sia veramente ampio lo spettro di personalizzazione di questi grafici.
 

Il file Intersezioni_xy.xls contiene un esempio su come evidenziare i punti di intersezione tra serie diverse in un grafico a dispersione. Le serie devono condividere le ascisse. Nell'esempio sono usate 3 serie e vengono evidenziate con indicatori diverse le 3 possibili intersezioni.
Il file Intersezioni_linee.xls è lo stesso esempio adattato su un grafico a linee.


Per soluzioni con serie costituite da numero diverso di punti e per ottenere linee che si colorano intrcettando altre curve vi rimando a soluizioni che utilizzano funzioni definite dall'utente.


Ĉ
1.xls
(18k)
roberto mensa,
Jan 5, 2011, 8:47 AM
Ĉ
2.xls
(18k)
roberto mensa,
Jan 5, 2011, 8:49 AM
Ĉ
roberto mensa,
Jan 7, 2011, 7:43 AM
Ĉ
roberto mensa,
Jan 7, 2011, 4:10 PM
Ĉ
roberto mensa,
Jan 9, 2011, 3:51 PM
Ĉ
roberto mensa,
Jan 9, 2011, 3:24 PM
Ĉ
roberto mensa,
Jan 11, 2011, 9:24 AM
Ĉ
roberto mensa,
Jan 11, 2011, 9:17 AM
Ĉ
roberto mensa,
Jan 11, 2011, 9:19 AM
Ĉ
roberto mensa,
Feb 20, 2011, 3:39 PM
Comments