Grafico di dispersione in excel per gruppo

I grafici a dispersione sono grafici eccellenti per mostrare una relazione tra due variabili numeriche attraverso una serie di osservazioni uniche. Di tanto in tanto li vediamo nelle comunicazioni aziendali, anche se sono molto più comunemente usati nella parte di "esplorazione" del processo, quando stiamo ancora cercando di comprendere i nostri dati e trovare le informazioni importanti. 

Se non hai familiarità con i grafici a dispersione, i loro casi d'uso comuni o i loro vantaggi e svantaggi in una serie di scenari, Dai un'occhiata a Che cos'è un grafico a dispersione? articolo nella nostra SWD Chart Guide. In questo articolo, esploriamo alcune delle nozioni di base dei grafici a dispersione tramite un esempio, condividiamo suggerimenti per progettarli in modo più efficace e discutiamo le variazioni comuni (grafici a bolle, grafici a dispersione collegati e altro ancora).

In questo articolo cammineremo attraverso i passaggi per la creazione di un grafico a dispersione in Microsoft Excel. Utilizzeremo un piccolo set di dati per:

• creare un semplice grafico a dispersione con una singola serie di dati;

• modificare quel grafico per mostrare più serie di dati in un unico grafico a dispersione;  

• Scopri come aggiungere elementi contestuali alla nostra vista (come medie, linee del quadrante e linee di tendenza ),

• aggiungere etichette dati a tutti o solo ad alcuni punti del nostro grafico e

• creare etichette personalizzate utilizzando altri campi del nostro set di dati.

Quattro anni fa, la nostra organizzazione voleva trovare un modo per rendere gli analisti junior appena assunti più efficaci ed efficaci. Abbiamo lanciato un piccolo programma pilota competitivo che avrebbe avviato i nuovi dipendenti con un anno intero di formazione dedicata e completa. Tutti gli altri I giovani assunti continueranno a ricevere l'esperienza di apprendimento sul posto di lavoro, basata su corsi e ad hoc che abbiamo tradizionalmente fornito. 

Attualmente abbiamo 20 persone che hanno completato il programma. Ogni mese, le loro prestazioni e tutti gli analisti dell'organizzazione vengono valutate come inferiori alla media, medie o superiori alla media rispetto ai loro colleghi nelle nostre tre sedi globali.

La tabella seguente mostra i diplomati del nostro programma pilota, il numero di mesi in cui sono stati con l'organizzazione e quanti mesi le loro prestazioni sono state valutate "sopra la media".

Utilizzeremo questo set di dati per creare e perfezionare i nostri grafici a dispersione, rimodellandoli e aggiungendoli secondo necessità.

Il modo più semplice per creare un grafico a dispersione in Excel consiste nell'evidenziare le celle nelle due colonne che contengono le due variabili numeriche, in questo caso il " MESI DI LAVORO " e " MESI CON PRESTAZIONI SUPERIORI ALLA MEDIA ". 

Quindi, vai alla scheda " Inserisci " della barra dei menu di Excel e fai clic sull'icona del grafico a dispersione nell'area " Grafici consigliati " della barra multifunzione.

Excel creerà automaticamente un grafico a dispersione nello stesso foglio dei dati, utilizzando la prima colonna del set di dati come asse orizzontale (X) e la seconda colonna come asse verticale (Y). 

Una breve nota qui: nella creazione di grafici a dispersione, una pratica comune è quella di rendere l'asse orizzontale la "variabile indipendente" e l'asse verticale la "variabile dipendente" (ovvero, il numero che è probabile che cambi in base al valore della nostra variabile indipendente).

Per il nostro scenario, è più probabile che il numero di mesi in cui una persona è stata impiegata influenzi il numero di valutazioni "sopra la media" che riceve, piuttosto che viceversa. Ecco perché la nostra variabile indipendente, i mesi di lavoro, si trova nella colonna di sinistra della tabella dei dati e la nostra variabile dipendente si trova nella colonna di destra.

Questo grafico generato automaticamente potrebbe richiedere un po' di formattazione e pulizia. Prendersi del tempo per rafforzare lo scheletro del grafico, ovvero tutto ciò che non è costituito dai punti dati effettivi, contribuirà a far risaltare le intuizioni e le informazioni.

Dopo aver rimosso le linee non necessarie e ripulito i nostri assi e titoli, il nostro grafico si presenta così:

Ora abbiamo un grafico a dispersione a serie singola ben formattato che utilizza un cerchio nero identico come marcatore per ciascuno dei nostri punti dati univoci. Da qui, possiamo continuare ad apportare modifiche e Perfezionamenti al nostro grafico.

Nel grafico a dispersione che abbiamo appena creato, c'è solo una serie di dati, costituita dall'intero gruppo di partecipanti al programma pilota. La durata dell'impiego di ciascun partecipante è tracciata sull'asse orizzontale e il totale delle valutazioni mensili superiori alla media è sull'asse verticale.

Supponiamo di voler suddividere questa serie di dati in più serie. Ad esempio, i nostri partecipanti sono assegnati a tre diversi uffici in tutto il mondo (Stati Uniti, Canada e Giappone); E se volessimo colorare i nostri marcatori di dati per rappresentare la posizione di quella persona?

In Excel, la creazione di un grafico a dispersione con più serie di dati può essere eseguita in diversi modi. Il modo più semplice consiste nell'avere una singola colonna nei dati contenente i valori X per tutte le serie di dati e quindi avere una colonna separata per i valori Y di ogni singola serie di dati.

Diamo un'occhiata a come potremmo modificare la nostra tabella di dati esistente per fare ciò.

In questa tabella, "OFFICE" è una colonna a sé stante e contiene tre valori univoci: Stati Uniti; Giappone; e Canada. Invece, aggiungi tre nuove colonne a destra della tabella esistente e imposta ogni valore OFFICE come il nome di una delle colonne:

Nelle celle di quelle colonne, scriveremo una formula che dice "Se il valore di [OFFICE] in questa riga specifica corrisponde all'intestazione di questa colonna, assegna a questa cella lo stesso valore della colonna [MESI CON PRESTAZIONI SUPERIORI ALLA MEDIA]; in caso contrario, assegnargli un valore di #N/A".

Quando propaghiamo questa formula nelle nuove colonne e in tutte le righe, la tabella sarà simile alla seguente:

Come si può notare, la colonna "Stati Uniti" contiene valori numerici solo se il valore della colonna "OFFICE" in tale riga è "Stati Uniti".  

Quando fai clic sul grafico a dispersione esistente, vedrai l'evidenziazione viola e blu attorno alle colonne X e Y che Excel sta attualmente visualizzando in quel grafico.

Vorremmo che questo grafico mostrasse i valori Y nelle tre nuove colonne che abbiamo appena creato. Per fare ciò, tieni il cursore sul bordo del rettangolo blu finché non diventa una mano, quindi trascina quel rettangolo a destra di una singola colonna , in modo che evidenzi i dati sotto "Stati Uniti".

Potresti notare che ora mancano molti dei tuoi punti dati! Questo perché ora Excel è solo utilizzando la colonna "Stati Uniti" per l'asse Y ed Excel non disegnerà un punto dati se è presente un valore "#N/A" come valore Y.

Non c'è da preoccuparsi, però: ora recupereremo tutti i nostri punti dati, facendo clic sull'angolo in basso a destra di quel rettangolo blu e trascinandolo verso destra, in modo che il rettangolo copra tutte e tre le nuove colonne che abbiamo creato.

Tutti i nostri dati sono tornati, evviva! E, come puoi vedere, Excel ora utilizza un colore diverso per ciascuna delle nostre serie di dati. 

Aggiungiamo una legenda in modo che i nostri spettatori sappiano cosa rappresentano questi diversi colori. Innanzitutto, daremo un nome a ciascuna delle nostre serie di dati: fai clic con il pulsante destro del mouse sul grafico, scegli "Seleziona dati " e aggiungi manualmente i nomi delle serie di dati nella finestra pop-up.

Quindi, puoi mettere a punto l'aspetto del tuo grafico, magari aggiungendo una legenda come sottotitolo al tuo titolo e scegliendo colori specifici per la tua serie, e il tuo grafico a dispersione multi-serie è pronto per l'uso.

Ogni volta che presentiamo i dati a un pubblico che potrebbe non averne familiarità, è una buona idea includere informazioni contestuali per facilitarne la comprensione. In un grafico a dispersione, possiamo  aggiungere un contesto come:

• Qual è stato il valore medio X? 

• Qual è stato il valore medio Y?

• C'era un obiettivo per entrambe le variabili?

• C'è una tendenza che vale la pena sottolineare?

A seconda delle informazioni dettagliate o dei dati stessi, è possibile utilizzare una (o più) di queste tecniche, quindi esaminiamole una alla volta.

Come si fa Aggiungere un punto medio a un grafico a dispersione

Inizieremo aggiungendo un'altra riga in fondo alla nostra tabella di dati, dove calcoleremo quali sono stati i valori medi X e Y. Per semplicità, esamineremo solo la media di TUTTI i nostri partecipanti, piuttosto che medie separate per ogni ufficio (anche se si applicherebbero le stesse tecniche). 

Poiché le colonne E e F contengono i nostri valori X e Y, scriveremo formule per calcolare la media dei valori in ciascuna di queste colonne.

Quindi, faremo clic con il pulsante destro del mouse sul nostro grafico, sceglieremo "Seleziona dati..." dal menu che si apre e aggiungeremo un'altra serie di dati solo per i nostri valori medi. Fare clic sul pulsante "+" sotto la finestra "Voci legenda (Serie):" per aggiungere una nuova serie, che è quindi possibile rinominare e impostare l'intervallo di valori X e Y per in quel pop-up.

Questo metterà un singolo punto sul nostro grafico che segna le nostre medie X e Y, come puoi vedere nel grafico aggiornato qui sotto.

Ho eliminato la nuova serie "media" dalla legenda (facendo clic una volta per evidenziare la legenda, poi una seconda volta per evidenziare la media e premendo " elimina "), e formattato il singolo punto in modo che sia grigio con un contorno nero.

Come aggiungere linee di riferimento e creare quadranti in un grafico a dispersione

Ora che abbiamo un punto medio, possiamo suddividere visivamente il nostro grafico a dispersione in quattro quadranti: 

1. sopra la media in entrambe le variabili X e Y;  

2. al di sotto della media in entrambi;

3. X alto ma Y basso; e 

4. X basso ma Y alto.

Useremo il nostro punto medio come base per disegnare le linee che definiscono quei quadranti. In particolare, aggiungeremo un elemento del grafico a quel punto, e forse non quello che ti aspetteresti immediatamente: le barre di errore.

Le barre di errore hanno in genere lo scopo di mostrare un contesto statistico aggiuntivo intorno a un punto dati. Invece, li useremo per disegnare sia una linea verticale che una orizzontale, ognuna delle quali collegherà i nostri assi ai bordi del nostro grafico, passando direttamente attraverso il nostro punto medio.

Per prima cosa, avremo bisogno di sapere per quanto tempo creare queste barre di errore.

• Sappiamo che ciascuno dei nostri assi ha un valore massimo di 50, quindi faremo in modo che le nostre barre di errore coprano quella distanza totale. 

• Una barra dalla linea di base al punto medio e una barra dal punto medio alla parte superiore del grafico. 

• Orizzontalmente, disegneremo una barra dall'asse Y al punto medio e poi una barra dal punto medio al bordo destro del grafico.

Per definire la lunghezza esatta di questi segmenti della barra di errore, aggiungeremo un'altra riga di dati alla nostra tabella. Sotto la riga MEDIA, aggiungeremo una riga chiamata "Limite superiore ". Il valore in queste celle è calcolato come " 50-[valore medio] ".

Ora, per aggiungere le nostre barre di errore al grafico: fai clic sul punto dati "medio" nel grafico, quindi vai all'opzione Progettazione grafico > Aggiungi elemento grafico nella barra multifunzione e seleziona "Barre di errore > altre opzioni di barre di errore..."

Si aprirà un riquadro Formatta barre di errore sul lato destro dello schermo. (A questo punto, Excel ti fa formattare per impostazione predefinita le barre di errore verticali; arriveremo alle barre orizzontali tra un minuto.) Vedrai le opzioni per " Direzione " (seleziona Entrambi), " Stile finale " (seleziona Nessun limite) e " Quantità di errore " (seleziona Personalizzato). 

Selezionando un'opzione personalizzata in Quantità errore, dovrai quindi fare clic su " Specifica valore ". In quella finestra popup, dì a Excel fino a che punto sopra e sotto il tuo punto "medio" vuoi che venga disegnata la tua barra di errore verticale. Ecco perché abbiamo creato quella riga di dati "limite superiore" in precedenza. Per il "Valore di errore positivo " nella finestra popup, utilizzare il valore della riga " limite superiore " e per il " Valore di errore negativo ", utilizzare il valore della riga " MEDIA ".

Quindi, fai clic direttamente sulle barre di errore orizzontali nel grafico e segui gli stessi passaggi per modificare quelle barre, utilizzando le righe MEDIA e Limite superiore per la colonna MESI DI OCCUPAZIONE. (Nota: queste barre potrebbero essere molto piccole per impostazione predefinita; se non puoi fare clic su di esse, fai clic in un punto qualsiasi del grafico, quindi vai al menu "Formato" accanto a "Design grafico" 

", e nel menu a discesa all'estrema sinistra della barra multifunzione, selezionare " Serie 'media' X Barre di errore ".)

Una volta fatto, puoi modificare il formato delle linee e il punto medio come preferisci. Preferisco spingere visivamente queste linee di riferimento verso lo sfondo e rendere il punto medio stesso invisibile disattivando il suo indicatore di dati. (Attento! Non eliminare completamente l'indicatore di dati , perché ciò farà scomparire anche le barre di errore.)

Disegnando i quadranti, posso vedere subito che abbiamo due dipendenti dell'ufficio giapponese in alto a sinistra che sono stati spesso al di sopra della media in termini di prestazioni, anche se sono tra il 50% più recente dei partecipanti al programma. Al contrario, il segno giallo in basso a destra mi mostra che un partecipante è qui da molto tempo, ma è molto al di sotto del 50° percentile in termini di periodi di performance superiori alla media. Non so se questa sia una storia interessante, o l'intuizione più importante, ma il semplice disegno dei quadranti sul grafico mi rende più facile analizzare e parlare di alcuni dei punti dati al suo interno.

Come aggiungere linee di tendenza a un scatter grafico

A differenza del disegno delle linee del quadrante, il disegno delle linee di tendenza in Excel è abbastanza semplice. Forse è TROPPO semplice, in realtà; È terribilmente facile inserire una linea di tendenza in un grafico che non ha alcuna base particolare nella realtà, o non descrive la tendenza effettiva in un modo che sarebbe utile per stabilire le aspettative future. Le linee di tendenza possono anche attirare l'attenzione, aggiungendo disordine visivo a un tipo di grafico che è già difficile da interpretare con precisione per alcuni spettatori. In genere, se c'è una tendenza che vale la pena evidenziare, è già visibile nei dati senza disegnare una linea aggiuntiva sul grafico stesso.

A parte queste preoccupazioni, una linea di tendenza può fornire un contesto utile in determinate situazioni. Il grafico che stiamo guardando, ad esempio, potrebbe beneficiare di una guida visiva che ci mostri quanto bene dovremmo aspettarci che i nostri quadri del programma pilota si comportino, in base alla loro esperienza in azienda. Una linea di tendenza lineare potrebbe Utilizza i dati storici per fornire alcune di queste informazioni.

Per creare una linea di tendenza che utilizzi tutti i nostri punti dati, creeremo una quinta serie di dati oltre a "Stati Uniti", "Canada",  "Giappone" e "media". Fare clic con il pulsante destro del mouse sul grafico e scegliere "Seleziona dati..."

Nella finestra popup, aggiungi un'altra serie di dati che utilizza le colonne E e F (i nostri dati originali a serie singola) come valori X e Y. L'ho chiamato "per la creazione di linee di tendenza".

Questa serie di dati verrà tracciata sopra tutte le serie esistenti, utilizzando le impostazioni predefinite di Excel per dimensioni, colore e così via. Nella vista qui sotto, ho reso i marcatori della serie "per la creazione di timeline" più grandi e viola solo così puoi vederli più chiaramente.

Che ci crediate o no, abbiamo quasi finito. Tutto quello che devi fare è fare clic con il pulsante destro del mouse su uno di questi e quindi selezionare " Aggiungi linea di tendenza... " Excel aggiungerà automaticamente una linea di tendenza lineare al grafico e ti mostrerà il menu Formato linea di tendenza sul lato destro dello schermo. 

Se sai cosa stai facendo con le linee di tendenza, ecco dove dovresti apportare le tue regolazioni preferite; per i nostri scopi di oggi, tutto ciò che farò è disattivare il colore della linea, e quindi disattiva le impostazioni di riempimento e bordo per i miei indicatori di dati "per la creazione di linee di tendenza".

È fantastico che abbiamo inserito i nostri dati nel grafico... Ma cosa rappresentano effettivamente questi dati? Se non ci interessa solo la distribuzione complessiva dei punti dati, dovremmo aggiungere le etichette dei dati ad alcuni, se non a tutti, i nostri punti. 

Per aggiungere le etichette dei dati a un grafico a dispersione, è sufficiente fare clic con il pulsante destro del mouse su qualsiasi punto dei dati serie a cui si desidera aggiungere etichette, quindi selezionare " Aggiungi etichette dati... " Excel aprirà il riquadro " Formatta etichette dati " e applicherà le sue impostazioni predefinite, che sono quelle di mostrare il valore Y corrente come etichetta. (Attiverà "Mostra linee guida", che di solito disattivo.)