Kako ustvariti ploskev stebel in listov v Excelu

Ta vadnica bo pokazala, kako ustvariti ploskev iz stebel in listov v vseh različicah Excela: 2007, 2010, 2013, 2016 in 2022.

Prikaz stebla in lista (znan tudi kot stemplot) je diagram, ki vam omogoča hitro oceno porazdelitve danega nabora podatkov. V bistvu ploskev razdeli dvomestna števila na polovico:

• Stebla - Prva številka
• Listi - Druga številka

Za primer poglejte spodnjo tabelo. Tabela prikazuje starostno razvrstitev majhnega prebivalstva. Steblo (črno, os y) prikazuje prvo številko starosti, rdeče podatkovne točke pa drugo številko.

Hitro lahko vidite, da je pri dvajsetih šest ljudi, kar je druga najbolj naseljena starostna skupina.

Vendar grafikon ni podprt v Excelu, kar pomeni, da ga boste morali ročno zgraditi od začetka. Oglejte si naš dodatek za ustvarjanje grafikonov, orodje, ki vam omogoča, da z nekaj kliki sestavite impresivne napredne grafikone Excel.

V tej vadnici po korakih se boste naučili, kako ustvariti dinamično ploskev iz stebel in listov v Excelu od začetka.

Začetek

Za ilustracijo, predpostavimo, da imate 24 podatkovnih točk, ki vsebujejo starost vaših strank. Če želite vizualizirati, katere starostne skupine izstopajo iz množice, ste se odločili zgraditi stemplot:

Tehnika, ki se jo boste naučili, je izvedljiva, tudi če ima vaš nabor podatkov na stotine vrednosti. Če pa želite to narediti, morate najprej postaviti temelje.

Torej, potopimo se takoj.

1. korak: Razvrstite vrednosti v naraščajočem vrstnem redu.

Za začetek razvrstite svoje dejanske podatke v naraščajočem vrstnem redu.

1. Izberite katero koli celico v obsegu nabora podatkov (A2: A25).
2. Pojdite na Podatki zavihek.
3. Kliknite na “Razvrsti”Gumb.
4. V vsakem spustnem meniju razvrstite po naslednjem:
   1. Za "Stolpec,"Izberite"Starost strank«(Stolpec A).
   2. Za "Razvrsti na,"Izberite"Vrednosti « /» Vrednosti celic.”
   3. Za "Naročilo,"Izberite"Najmanjši do največji.”

2. korak: Nastavite pomožno mizo.

Ko je stolpec podatkov razvrščen, nastavite ločeno pomožno tabelo za shranjevanje vseh podatkov grafikona na naslednji način:

Nekaj ​​besed o vsakem elementu tabele:

• Steblo (stolpec C) - Ta bo vseboval prvo številko vseh starosti.
• List (stolpec D) - Ta bo vseboval drugo številko vseh starosti.
• Položaj listov (stolpec E) - Ta pomožni stolpec bo pomagal postaviti liste na grafikonu.

3. korak: Poiščite vrednosti stebla.

Najprej izračunajte Steblo vrednote (Stolpec C) z uporabo funkcij LEFT in VALUE. Funkcija LEFT-ki vrne določeno število znakov od začetka celice-nam bo pomagala izvleči prvo številko iz vsake vrednosti, medtem ko funkcija VALUE oblikuje izhod formule kot številko (to je ključnega pomena).

To formulo vnesite v celico C2:

1	= VREDNOST (LEVO (A2,1))

Ko najdete prvo vrednost stebla, povlecite ročico za polnjenje na dno stolpca, da izvedete formulo za preostale celice (C3: C25).

4. korak: Poiščite vrednosti listov.

Naš naslednji korak je iskanje vrednosti za List stolpec (Stolpec D) tako, da povlečete zadnjo številko vsake številke iz stolpca izvirnih podatkov (stolpec A). Na srečo lahko funkcija DESNO opravi umazano delo namesto vas.

V celico vnesite naslednjo funkcijo D2:

1	= DESNO (A2,1)

Ko imate formulo v celici, jo povlecite čez preostale celice (D3: D25).

5. korak: Poiščite vrednosti položaja lista.

Ker bo za gradnjo prikaza stebla in listov uporabljena razpršena ploskev, morate vsakemu listu dodeliti številko, ki označuje njegov položaj na grafikonu, s pomočjo funkcije COUNTIF.

Vnesite to formulo v celico E2:

1	= COUNTIF ($ C $ 2: C2, C2)

V preprosti angleščini formula primerja vsako posamezno vrednost v stolpcu Steblo (Stolpec C) med seboj, da bi zaznali in označili podvojene dogodke ter učinkovito pripisali edinstvene identifikatorje listom, ki imajo skupno steblo.

Ponovno kopirajte formulo v preostale celice (E3: E25).

Korak #6: Zgradite razpršeno XY ploskev.

Zdaj ste zbrali vse koščke sestavljanke, potrebne za ustvarjanje razpršene ploskve. Sestavimo jih skupaj.

1. Označite vse vrednosti v stolpcih Steblo in Položaj listov tako, da iz stolpca C izberete podatkovne celice in nato pridržite tipko Nadzor tipko, ko izberete podatkovne celice iz stolpca E, pri tem pa pustite celice vrstice glave (C2: C25 in E2: E25). OPOMBA: Trenutno ne izbirate stolpca D.
2. Pojdite na Vstavi zavihek.
3. Kliknite na “Vstavite razpršilec (X, Y) ali mehurček”.
4. Izberite “Razpršilo.”

Korak #7: spremenite vrednosti X in Y.

Zdaj postavite vodoravno os, ki je odgovorna za prikaz stebel navpično. Z desno miškino tipko kliknite grafikon in izberite »Izberite Podatki”Iz menija, ki se prikaže.

Nato kliknite »Uredi”Gumb.

Ko ste tam, morate ročno spremeniti vrednosti X in Y:

1. Za "Vrednosti serije X,”Izberite vse vrednosti iz stolpca Položaj listov (E2: E25).
2. Za "Vrednosti serije Y,”Označite vse vrednosti iz stolpca Steblo (C2: C25).
3. Kliknite »v redu”Za pogovorno okno Urejanje serije.
4. Kliknite »v redu”Za pogovorno okno Izbira vira podatkov.

Korak #8: Spremenite navpično os.

Ko ste grafikon preuredili, ga morate znova obrniti, da stebla razvrstite po naraščajočem vrstnem redu. Z desno miškino tipko kliknite navpično os in izberite »Oblika osi.”

Ko ste tam, sledite tem preprostim korakom:

1. Pomaknite se do Možnosti osi zavihek.
2. Spremenite Največje meje vrednost na "6”, Ker je največje število v naboru podatkov 60.
3. Nastavite Glavne enote vrednost na "1.”
4. Preverite »Vrednosti v obratnem vrstnem redu" škatla.

9. korak: Dodajte in spremenite oznake osi.

Oznake, nameščene vzdolž navpične osi, bodo uporabljene kot ločilo med stebli in listi. Brez zapiranja »Oblika osi”, Se pomaknite navzdol do Označite oznake razdelku in zraven »Glavna vrsta, "Izberite"V notranjosti.”

Označite kljukice tako, da spremenite njihovo barvo in širino.

1. Pomaknite se do Fill & Line zavihek.
2. Pod vrstico kliknite »Barva polnila”, Da odprete barvno paleto in izberete črno.
3. Nastavite Premer do “3 pt.”

Zdaj lahko odstranite vodoravno os in mrežne črte. Z desno miškino tipko kliknite vsak element in izberite »Izbriši.”Prav tako povečajte pisavo številk stebel in jih naredite krepke, da jih boste lažje videli (izberite navpično os in se pomaknite do Domov > Pisava).

10. korak: Dodajte podatkovne oznake.

Ko se približate ciljni črti, dodajte liste v grafikon. Če želite to narediti, z desno miškino tipko kliknite katero koli piko, ki predstavlja Serija "Serija 1" in izberite "Dodajte podatkovne oznake.”

11. korak: Prilagodite podatkovne oznake.

Ko ste tam, se znebite privzetih oznak in dodajte vrednosti iz stolpca List (Stolpec D) namesto tega. Z desno miškino tipko kliknite katero koli oznako podatkov in izberite »Oblikujte podatkovne oznake.”

Ko se prikaže podokno opravil, sledite nekaj preprostim korakom:

1. Preklopite na Možnosti oznak zavihek.
2. Preverite »Vrednost iz celic" škatla.
3. Označite vse vrednosti v stolpcu List (D2: D25).
4. Kliknite »v redu.”
5. Počistite polje »Y vrednost" škatla.
6. Počistite polje »Pokaži vodilne vrstice" škatla.

Spremenite barvo in velikost pisave listov, da se razlikujejo od stebel-in ne pozabite jih popestrili (Domov > Pisava).

12. korak: Skrijte označevalce podatkov.

Podatkovni označevalci (pike) so vam dobro služili, vendar jih ne potrebujete več, razen kot sredstvo za postavitev podatkovnih oznak. Zato jih naredimo pregledne.

Z desno miškino tipko kliknite katero koli piko, ki ponazarja Serija "Serija 1" in izberite »Oblikujte niz podatkov.”

Ko se prikaže podokno opravil, naredite naslednje:

1. Kliknite na “Fill & Line”.
2. Preklopite na Marker zavihek.
3. Spodaj "Možnosti označevalcev,"Izberite"Nobena.”

In ne pozabite spremeniti naslova grafikona.

Korak #13: Dodajte naslove osi.

Z naslovi osi označite oba elementa grafikona.

1. Izberite grafikon grafikona.
2. Pojdite na Oblikovanje zavihek.
3. Kliknite »Dodaj element grafikona.”
4. Izberite »Naslovi osi.”
5. Izberite “Primarno vodoravno"In"Primarna navpična.”

Kot lahko vidite, se naslovi osi prekrivajo na grafikonu. Če želite odpraviti težavo, izberite grafikon grafikona in prilagodite ročice, da spremenite velikost območja grafikona. Zdaj lahko spremenite naslove osi.

Korak 14: Dodajte polje z besedilom.

Na koncu dodajte besedilno polje s ključem, da boste lažje brali stemplot.

1. Izberite grafikon grafikona.
2. Pojdite na Vstavi zavihek.
3. V Besedilo skupina, izberite »Besedilno polje.”

V besedilnem polju navedite primer iz svojih podatkov, ki pojasnjuje, kako graf deluje, in vse je pripravljeno!