Come creare un grafico a stelo e foglia in Excel

Questa esercitazione dimostrerà come creare un grafico gambo e foglia in tutte le versioni di Excel: 2007, 2010, 2013, 2016 e 2022.

Un display stem-and-leaf (noto anche come stemplot) è un diagramma progettato per consentire di valutare rapidamente la distribuzione di un determinato set di dati. Fondamentalmente, la trama divide a metà i numeri a due cifre:

• gambi - La prima cifra
• Fogliame - La seconda cifra

Ad esempio, guarda il grafico qui sotto. Il grafico mostra la ripartizione per età di una piccola popolazione. Lo stelo (nero, asse y) mostra la prima cifra dell'età, mentre i punti dati rossi mostrano la seconda cifra.

Puoi vedere rapidamente che ci sono sei persone sui vent'anni, che è la seconda fascia d'età più popolosa.

Tuttavia, il grafico non è supportato in Excel, il che significa che dovrai crearlo manualmente da zero. Dai un'occhiata al nostro componente aggiuntivo Chart Creator, uno strumento che ti consente di creare grafici Excel avanzati in pochi clic.

In questo tutorial passo passo, imparerai come creare da zero un grafico dinamico di foglie e steli in Excel.

Iniziare

A scopo illustrativo, supponi di disporre di 24 punti dati contenenti le età dei tuoi clienti. Per visualizzare quali gruppi di età si distinguono dalla massa, hai deciso di costruire una trama:

La tecnica che stai per imparare è praticabile anche se il tuo set di dati contiene centinaia di valori. Ma per farcela, devi prima gettare le basi.

Quindi, tuffiamoci dentro.

Passaggio 1: ordina i valori in ordine crescente.

Per cominciare, ordina i tuoi dati effettivi in ​​ordine crescente.

1. Seleziona qualsiasi cella all'interno dell'intervallo del set di dati (A2: A25).
2. Vai al Dati scheda.
3. Clicca il "Ordinarepulsante ".
4. In ogni menu a discesa, ordina in base a quanto segue:
   1. Per "Colonna," Selezionare "Età del cliente” (Colonna A).
   2. Per "Ordina su," Selezionare "Valori” / “Valori cella.”
   3. Per "Ordine," Selezionare "Dal più piccolo al più grande.”

Passaggio 2: imposta una tabella di supporto.

Una volta che la colonna di dati è stata ordinata, imposta una tabella di supporto separata per memorizzare tutti i dati del grafico come segue:

Qualche parola su ogni elemento della tabella:

• Stelo (colonna C) - Questo conterrà la prima cifra di tutte le età.
• Foglia (Colonna D) - Questo conterrà la seconda cifra di tutte le età.
• Posizione anta (colonna E) - Questa colonna di supporto aiuterà a posizionare le foglie sul grafico.

Passaggio 3: trova i valori staminali.

Per prima cosa, calcola il Stelo valori (Colonna C) utilizzando le funzioni LEFT e VALUE. La funzione SINISTRA, che restituisce il numero specificato di caratteri dall'inizio di una cella, ci aiuterà a estrarre la prima cifra da ciascun valore mentre la funzione VALORE formatta l'output della formula come un numero (questo è cruciale).

Inserisci questa formula nella cella do2:

1	=VALORE(SINISTRA(A2,1))

Una volta trovato il tuo primo valore Stem, trascina il quadratino di riempimento in fondo alla colonna per eseguire la formula per le celle rimanenti (C3: C25).

Passaggio 4: trova i valori delle foglie.

Il nostro prossimo passo è trovare i valori per il Foglia colonna (Colonna D) estraendo l'ultima cifra di ogni numero dalla colonna dei dati originali (colonna A). Fortunatamente, la funzione DESTRA può fare il lavoro sporco per te.

Digita la seguente funzione nella cella re2:

1	=DESTRA(A2,1)

Una volta che hai la formula nella cella, trascinala sul resto delle celle (D3: D25).

Passaggio 5: trova i valori della posizione foglia.

Poiché verrà utilizzato un grafico a dispersione per la costruzione della visualizzazione gambo e foglia, per far cadere tutto al suo posto, è necessario assegnare a ciascuna foglia un numero che indica la sua posizione sul grafico con l'aiuto della funzione CONTA.SE.

Inserisci questa formula nella cella E2:

1	=CONTA.SE($C$2:C2,C2)

In parole povere, la formula confronta ogni singolo valore nella colonna Stelo (Colonna C) tra loro per individuare e contrassegnare occorrenze duplicate, attribuendo efficacemente identificatori univoci alle foglie che condividono un gambo comune.

Di nuovo, copia la formula nel resto delle celle (E3:E25).

Passaggio n. 6: crea un grafico a dispersione XY.

Ora hai raccolto tutti i pezzi del puzzle necessari per creare un grafico a dispersione. Mettiamoli insieme.

1. Evidenzia tutti i valori nelle colonne Stelo e Posizione foglia selezionando le celle di dati dalla colonna C e tenendo premuto il tasto Controllo mentre selezioni le celle di dati dalla colonna E, tralasciando le celle della riga di intestazione (DO2: DO25 e MI2: MI25). NOTA: non si seleziona la colonna D in questo momento.
2. Vai al Inserire scheda.
3. Clicca il "Inserisci grafico a dispersione (X, Y) o a bolle" icona.
4. Scegliere "Dispersione.”

Passaggio 7: modifica i valori X e Y.

Ora, posiziona l'asse orizzontale responsabile della visualizzazione degli steli verticalmente. Fare clic con il pulsante destro del mouse sul grafico e selezionare "Seleziona dati” dal menu che appare.

Quindi, fai clic su "Modificarepulsante ".

Una volta lì, devi modificare manualmente i valori X e Y:

1. Per "Valori della serie X,"seleziona tutti i valori dalla colonna" Posizione foglia (MI2: MI25).
2. Per "Valori della serie Y,"evidenzia tutti i valori dalla colonna Stelo (C2: C25).
3. Fare clic su "ok” per la finestra di dialogo Modifica serie.
4. Fare clic su "ok” per la finestra di dialogo Seleziona origine dati.

Passaggio n. 8: modifica l'asse verticale.

Dopo aver riorganizzato il grafico, devi capovolgerlo di nuovo per ordinare i gambi in ordine crescente. Fare clic con il pulsante destro del mouse sull'asse verticale e scegliere "Asse formato.”

Una volta lì, segui questi semplici passaggi:

1. Vai a Opzioni dell'asse scheda.
2. Cambiare il Limiti massimi valore a "6” perché il numero più grande nel set di dati è 60.
3. Impostare il Unità principali valore a "1.”
4. Controlla il "Valori in ordine inverso" scatola.

Passaggio n. 9: aggiungere e modificare i segni di graduazione dell'asse.

I segni di graduazione posti lungo l'asse verticale verranno utilizzati come separatore tra i gambi e le foglie. Senza chiudere il “Asse formato" Riquadro attività, scorrere verso il basso fino a Segni di spunta sezione e accanto a "tipo maggiore," scegliere "Dentro.”

Fai risaltare i segni di graduazione cambiandone il colore e la larghezza.

1. Vai a Riempi e riga scheda.
2. In Linea, fai clic su "Colore di riempimento” per aprire la tavolozza dei colori e scegliere il nero.
3. Impostare il Larghezza a "3 punti”

Ora puoi rimuovere l'asse orizzontale e le linee della griglia. Basta fare clic con il pulsante destro del mouse su ciascun elemento e scegliere "Elimina.” Inoltre, aumenta il carattere dei numeri del gambo e rendili in grassetto in modo che siano più facili da vedere (seleziona l'asse verticale e vai a Casa > Font).

Passaggio 10: aggiungi etichette dati.

Mentre ti avvicini al traguardo, aggiungiamo le foglie al grafico. Per farlo, fai clic con il pulsante destro del mouse su qualsiasi punto che rappresenta Serie “Serie 1” e scegli "Aggiungi etichette dati.”

Passaggio 11: personalizza le etichette dei dati.

Una volta lì, elimina le etichette predefinite e aggiungi i valori dalla colonna Foglia (Colonna D) Invece. Fare clic con il pulsante destro del mouse su qualsiasi etichetta di dati e selezionare "Formatta etichette dati.”

Quando viene visualizzato il riquadro attività, seguire alcuni semplici passaggi:

1. Passa a Opzioni etichetta scheda.
2. Controlla il "Valore dalle celle" scatola.
3. Evidenzia tutti i valori in colonna Foglia (D2: D25).
4. Fare clic su "ok.”
5. Deseleziona il "Valore Y" scatola.
6. Deseleziona il "Mostra linee guida" scatola.

Cambia il colore e la dimensione del carattere delle foglie per differenziarle dagli steli e non dimenticare di renderle anche in grassetto (Casa > Font).

Passaggio 12: Nascondi i marcatori di dati.

I marcatori di dati (i punti) ti sono serviti bene, ma non ti servono più, se non come mezzo per posizionare le etichette dei dati. Quindi rendiamoli trasparenti.

Fare clic con il pulsante destro del mouse su qualsiasi punto che illustra Serie “Serie 1” e seleziona "Formatta serie di dati.”

Una volta visualizzato il riquadro attività, eseguire le seguenti operazioni:

1. Clicca il "Riempi e riga" icona.
2. Passa a marcatore scheda.
3. Sotto "Opzioni marcatore," scegliere "Nessuno.”

E non dimenticare di cambiare il titolo del grafico.

Passaggio 13: aggiungi i titoli degli assi.

Usa i titoli degli assi per etichettare entrambi gli elementi del grafico.

1. Seleziona il grafico.
2. Vai al Design scheda.
3. Fare clic su "Aggiungi elemento grafico.”
4. Selezionare "Titoli degli assi.”
5. Scegliere "Orizzontale primario" e "Verticale primario.”

Come puoi vedere, i titoli degli assi si sovrappongono al grafico. Per risolvere il problema, seleziona il grafico e regola le maniglie per ridimensionare l'area del grafico. Ora puoi cambiare i titoli degli assi.

Passaggio 14: aggiungi una casella di testo.

Infine, aggiungi una casella di testo con una chiave per rendere più facile la lettura del grafico.

1. Seleziona il grafico.
2. Vai al Inserire scheda.
3. Nel Testo gruppo, scegli "Casella di testo.”

Nella casella di testo, dai un esempio dai tuoi dati che spiega come funziona il grafico e sei pronto!