Een stengel-en-bladplot maken in Excel

Deze zelfstudie laat zien hoe u een stengel-en-bladplot maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Een stam-en-bladweergave (ook bekend als een stemplot) is een diagram dat is ontworpen om u in staat te stellen snel de distributie van een bepaalde dataset te beoordelen. Kortom, de plot splitst tweecijferige getallen in tweeën:

• stengels - Het eerste cijfer
• Bladeren - Het tweede cijfer

Kijk als voorbeeld naar de onderstaande grafiek. De grafiek toont de leeftijdsopbouw van een kleine populatie. De steel (zwart, y-as) toont het eerste cijfer van de leeftijd, terwijl de rode gegevenspunten het tweede cijfer tonen.

Je kunt snel zien dat er zes twintigers zijn, wat de op één na dichtstbevolkte leeftijdsgroep is.

De grafiek wordt echter niet ondersteund in Excel, wat betekent dat u deze handmatig vanaf de grond moet opbouwen. Bekijk onze Chart Creator Add-In, een tool waarmee u met slechts een paar klikken indrukwekkende geavanceerde Excel-diagrammen kunt samenstellen.

In deze stapsgewijze zelfstudie leert u hoe u vanuit het niets een dynamische stam-en-bladplot in Excel kunt maken.

Beginnen

Stel dat u ter illustratie 24 gegevenspunten heeft met de leeftijden van uw klanten. Om te visualiseren welke leeftijdsgroepen zich onderscheiden van de rest, ging je een stemplot bouwen:

De techniek die u gaat leren is levensvatbaar, zelfs als uw dataset honderden waarden bevat. Maar om het voor elkaar te krijgen, moet u eerst de basis leggen.

Dus laten we er meteen in duiken.

Stap 1: Sorteer de waarden in oplopende volgorde.

Sorteer om te beginnen uw actuele gegevens in oplopende volgorde.

1. Selecteer een cel binnen het gegevenssetbereik (A2:A25).
2. Ga naar de Gegevens tabblad.
3. Klik op de "Soort" knop.
4. Sorteer in elk vervolgkeuzemenu op het volgende:
   1. Voor "Kolom," selecteer "Leeftijd van de klant:” (kolom A).
   2. Voor "Sorteer op," selecteer "Waarden" / "Celwaarden.”
   3. Voor "Volgorde," selecteer "Kleinste naar grootste.”

Stap 2: Stel een helpertafel in.

Nadat de kolom met gegevens is gesorteerd, stelt u als volgt een afzonderlijke hulptabel in voor het opslaan van alle grafiekgegevens:

Een paar woorden over elk element van de tabel:

• Stuurpen (kolom C) - Dit bevat het eerste cijfer van alle leeftijden.
• Blad (kolom D) - Dit bevat het tweede cijfer van alle leeftijden.
• Bladpositie (kolom E) - Deze hulpkolom helpt de bladeren op de kaart te positioneren.

Stap 3: Zoek de stamwaarden.

Bereken eerst de Stang waarden (Kolom C) met de functies LEFT en VALUE. De LEFT-functie - die het opgegeven aantal tekens vanaf het begin van een cel retourneert - helpt ons het eerste cijfer uit elke waarde te extraheren, terwijl de VALUE-functie de formule-uitvoer opmaakt als een getal (dat is cruciaal).

Voer deze formule in cel in C2:

1	=WAARDE(LINKS(A2,1))

Zodra u uw eerste Stem-waarde hebt gevonden, sleept u de vulgreep naar de onderkant van de kolom om de formule voor de resterende cellen uit te voeren (C3:C25).

Stap 4: Zoek de Leaf-waarden.

Onze volgende stap is het vinden van de waarden voor de Blad kolom (Kolom D) door het laatste cijfer van elk nummer uit de oorspronkelijke gegevenskolom te trekken (kolom A). Gelukkig kan de RIGHT-functie het vuile werk voor je doen.

Typ de volgende functie in cel: D2:

1	=RECHTS(A2,1)

Zodra u de formule in de cel hebt, sleept u deze over de rest van de cellen (D3:D25).

Stap #5: Zoek de waarden van de bladpositie.

Omdat een spreidingsplot zal worden gebruikt voor het bouwen van de stengel-en-bladweergave, om alles op zijn plaats te laten vallen, moet u aan elk blad een nummer toewijzen dat de positie op de kaart aangeeft met behulp van de AANTAL.ALS-functie.

Voer deze formule in cel in E2:

1	=AANTAL.ALS($C$2:C2,C2)

In gewoon Engels vergelijkt de formule elke afzonderlijke waarde in de kolom Stang (Kolom C) met elkaar om dubbele voorvallen op te sporen en te markeren, waardoor op effectieve wijze unieke identifiers worden toegekend aan de bladeren die een gemeenschappelijke stengel delen.

Kopieer de formule opnieuw naar de rest van de cellen (E3:E25).

Stap #6: Bouw een scatter XY-plot.

Je hebt nu alle puzzelstukjes verzameld die nodig zijn om een ​​scatterplot te maken. Laten we ze samenvoegen.

1. Markeer alle waarden in kolommen Stang en Bladpositie door de gegevenscellen in kolom C te selecteren en vervolgens de . ingedrukt te houden Controle toets terwijl u de gegevenscellen uit kolom E selecteert, waarbij u de cellen van de koprij weglaat (C2:C25 en E2:E25). OPMERKING: u selecteert op dit moment geen kolom D.
2. Ga naar de Invoegen tabblad.
3. Klik op de "Spreidingsdiagram (X, Y) of bellendiagram invoegen" icoon.
4. Kiezen "Verstrooien.”

Stap #7: Wijzig de X- en Y-waarden.

Plaats nu de horizontale as die verantwoordelijk is voor het verticaal weergeven van de stelen. Klik met de rechtermuisknop op de grafiekplot en kies “Selecteer gegevens” in het menu dat verschijnt.

Klik vervolgens op de "Bewerking" knop.

Eenmaal daar moet u de X- en Y-waarden handmatig wijzigen:

1. Voor "Serie X-waarden,"selecteer alle waarden uit de kolom" Bladpositie (E2:E25).
2. Voor "Serie Y-waarden,” markeer alle waarden uit de kolom Stang (C2:C25).
3. Klik "Oke” voor het dialoogvenster Serie bewerken.
4. Klik "Oke” voor het dialoogvenster Gegevensbron selecteren.

Stap #8: Wijzig de verticale as.

Nadat u de grafiek opnieuw hebt gerangschikt, moet u deze opnieuw omdraaien om de stelen in oplopende volgorde te sorteren. Klik met de rechtermuisknop op de verticale as en kies "Formaat as.”

Eenmaal daar, volg deze eenvoudige stappen:

1. Navigeer naar de Asopties tabblad.
2. Verander de Maximale grenzen waarde aan “6” omdat het grootste getal in de dataset 60 is.
3. Stel de grote eenheden waarde aan “1.”
4. Controleer de "Waarden in omgekeerde volgorde" doos.

Stap #9: Voeg de markeringen voor de as toe en wijzig ze.

De maatstreepjes langs de verticale as worden gebruikt als scheidingsteken tussen de stengels en de bladeren. Zonder de "Formaat as” taakvenster, scrol omlaag naar de Vinkjes sectie en naast "hoofdtype," Kiezen "Binnenkant.”

Laat de maatstreepjes opvallen door hun kleur en breedte te veranderen.

1. Navigeer naar de Vulling & lijn tabblad.
2. Klik onder Lijn op de "Opvulkleur” pictogram om het kleurenpalet te openen en zwart te kiezen.
3. Stel de Breedte tot "3 pt.”

U kunt nu de horizontale as en rasterlijnen verwijderen. Klik met de rechtermuisknop op elk element en kies "Verwijderen.Vergroot ook het lettertype van de stamnummers en maak ze vet zodat ze gemakkelijker te zien zijn (selecteer de verticale as en navigeer naar Huis > Lettertype).

Stap #10: Voeg gegevenslabels toe.

Laten we, terwijl je naar de finish gaat, de bladeren aan de kaart toevoegen. Om dat te doen, klikt u met de rechtermuisknop op een punt dat staat voor Serie "Serie 1" en kies "Voeg gegevenslabels toe.”

Stap #11: Pas gegevenslabels aan.

Eenmaal daar, verwijder de standaardlabels en voeg de waarden uit de kolom toe Blad (Kolom D) in plaats daarvan. Klik met de rechtermuisknop op een gegevenslabel en selecteer "Gegevenslabels opmaken.”

Wanneer het taakvenster verschijnt, volgt u een paar eenvoudige stappen:

1. Schakel over naar de Labelopties tabblad.
2. Controleer de "Waarde uit cellen" doos.
3. Markeer alle waarden in kolom Blad (D2:D25).
4. Klik "Oke.”
5. Haal het vinkje weg bij "Y-waarde" doos.
6. Haal het vinkje weg bij "Toon aanhaallijnen" doos.

Verander de kleur en lettergrootte van de bladeren om ze te onderscheiden van de stengels - en vergeet niet om ze ook vet te maken (Huis > Lettertype).

Stap #12: Verberg de gegevensmarkeringen.

De gegevensmarkeringen (de stippen) hebben u goed gediend, maar u hebt ze niet meer nodig, behalve als middel om de gegevenslabels te positioneren. Laten we ze dus transparant maken.

Klik met de rechtermuisknop op een punt ter illustratie Serie "Serie 1" en selecteer "Gegevensreeks opmaken.”

Zodra het taakvenster verschijnt, doet u het volgende:

1. Klik op de "Vulling & lijn" icoon.
2. Schakel over naar de Markeerstift tabblad.
3. Onder "Markeringsopties," Kiezen "Geen.”

En vergeet niet de titel van de grafiek te wijzigen.

Stap #13: Voeg de astitels toe.

Gebruik de astitels om beide elementen van het diagram te labelen.

1. Selecteer de grafiekplot.
2. Ga naar de Ontwerp tabblad.
3. Klik "Voeg grafiekelement toe.”
4. Selecteer "As titels.”
5. Kiezen "Primair horizontaal" en "Primair verticaal.”

Zoals u wellicht ziet, overlappen de astitels de grafiekplot. Om het probleem op te lossen, selecteert u de kaartplot en past u de handvatten aan om de grootte van het plotgebied te wijzigen. U kunt nu de astitels wijzigen.

Stap #14: Voeg een tekstvak toe.

Voeg ten slotte een tekstvak met een sleutel toe om het lezen van de stemplot gemakkelijker te maken.

1. Selecteer de grafiekplot.
2. Ga naar de Invoegen tabblad.
3. In de Tekst groep, kies "Tekstveld.”

Geef in het tekstvak een voorbeeld van uw gegevens waarin wordt uitgelegd hoe de grafiek werkt, en u bent helemaal klaar!