Ako vytvoriť Paretovu tabuľku v programe Excel

Tento tutoriál vám ukáže, ako vytvoriť Paretov diagram vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.

Paretov diagram je hybrid stĺpcového grafu a čiarového grafu, ktorý naznačuje relatívnu dôležitosť faktorov alebo položiek v danej množine údajov spolu s ich kumulatívnymi percentami. Za svoj názov graf vďačí Paretovmu princípu, známemu tiež ako zákon niekoľkých životne dôležitých, ktorý uvádza, že k 80% následkov prispieva približne 20% príčin.

Cieľom grafu je rozpoznať kritické faktory v súbore údajov a odlíšiť ich od nepodstatných.

Paretov graf sa stal natívnym typom grafu v Exceli 2016, ale pre používateľov Excelu 2013 alebo starších verzií je jedinou možnosťou ručné zostavenie grafu od začiatku.

V tomto návode sa naučíte, ako vytvoriť prispôsobiteľný Paretov diagram ručne aj pomocou vstavaného nástroja na vytváranie grafov v programe Excel.

Začíname

Aby sme vám ukázali laná, potrebujeme niekoľko prvotných údajov, s ktorými budeme pracovať. Z tohto dôvodu predpokladajme, že ste sa rozhodli analyzovať rozdelenie položiek vrátených do obchodu s odevmi a pokúsiť sa zistiť, ktoré kategórie produktov spôsobujú najväčšie problémy.

S ohľadom na to zvážte nasledujúce údaje:

Poďme sa teda ponoriť priamo dovnútra.

Ako vytvoriť Paretovu tabuľku v programe Excel 2016+

Najprv vám ukážem, ako nakresliť Paretov diagram pomocou zodpovedajúceho vstavaného typu grafu programu Excel zavedeného v programe Excel 2016.

Pri použití tejto metódy trvá zostavenie grafu iba niekoľko kliknutí, ale prídete o niektoré funkcie prispôsobenia-napríklad do riadka Pareto nemôžete pridávať ani štítky s údajmi, ani značky.

Ak teda potrebujete ďalšiu úroveň prispôsobenia, má zmysel zostaviť graf od začiatku. Najprv však uvádzame algoritmus pre tých, ktorí chcú zvoliť jednoduchšiu cestu.

Krok č. 1: Vytvorte Paretov diagram.

Opäť platí, že ak používate Excel 2016 alebo novší, Excel vám umožní vytvoriť jednoduchý Paretov diagram a sotva zdvihnúť prst:

1. Zvýraznite svoje skutočné údaje (A1: B11).
2. Choďte do Vložiť tab.
3. Kliknite na „Vložiť štatistickú tabuľku.”
4. Vyberte „Pareto.”

Magicky sa Paretov diagram okamžite objaví:

Technicky to môžeme nazvať dňom, ale aby graf pomohol rozpovedať príbeh, budete si musieť dať asi prácu navyše.

Krok č. 2: Pridajte štítky s údajmi.

Začnite pridaním štítkov údajov do grafu. Kliknite pravým tlačidlom myši na ktorýkoľvek zo stĺpcov a zvoľte „Pridajte štítky s údajmi.”

Prispôsobte farbu, písmo a veľkosť štítkov, aby vynikli (Domov > Písmo).

Krok č. 3: Pridajte názvy osí.

Ako čerešnička na torte poskytujú názvy osí ďalší kontext k tomu, o čom graf je.

1. Vyberte oblasť grafu.
2. Prepnúť na Dizajn tab.
3. Kliknite na „Pridať prvok grafu”Tlačidlo.
4. Vyzdvihnúť "Názvy osí”Z rozbaľovacej ponuky.
5. Vyberte oboje “Primárne horizontálne“A„Primárne vertikálne.”

Krok č. 4: Pridajte posledné úpravy.

Keďže dolaďujeme graf, teraz môžete odstrániť mriežky, ktoré v žiadnom prípade neslúžia prakticky. Pravým tlačidlom myši kliknite na mriežku a kliknite na „Vymazať“Ich vymazať.

Nakoniec zmeňte názvy osí a grafov a aktualizovaný graf Pareto je pripravený!

Ako vytvoriť Paretovu tabuľku v programe Excel 2007, 2010 a 2013

To bolo dosť rýchle. Používatelia verzií starších ako Excel 2016 však nemajú inú možnosť, ako vykonať všetku prácu sami. Existuje však strieborná podšívka: týmto spôsobom získate oveľa viac priestoru na prispôsobenie.

Napriek tomu, že všetky snímky obrazovky boli urobené v Exceli 2022, metóda, ktorú sa chystáte naučiť, zostáva kompatibilná s verziami späť do Excelu 2007. Postupujte podľa nich a podľa toho sa upravte.

Krok č. 1: Zoradenie údajov zostupne.

Na začiatok zoraďte údaje podľa počtu vrátených položiek (stĺpec B) v zostupnom poradí.

1. Zvýraznite svoje skutočné údaje (A1: B11).
2. Choďte do Údaje tab.
3. Kliknite na „Zoradiť”Tlačidlo.
4. V Zoradiť V dialógovom okne postupujte takto:
   1. Pre “Stĺpec, “Vyberte„Položky vrátené.”
   2. Pre “Zoradiť podľa"Vybrať"Hodnoty buniek.”
   3. Pre “objednať," vyzdvihnúť "Od najväčšieho po najmenší.”

Krok č. 2: Vypočítajte kumulatívne percentá.

Našim ďalším krokom je vytvorenie pomocného stĺpca s názvom „Kumulatívne %“ ktorý sčíta relatívne podiely kategórií produktov v súbore údajov, ktoré sa majú použiť ako hodnoty na vytváranie grafov v Paretovom riadku.

Ak chcete nájsť hodnoty, zadajte do nich nasledujúci vzorec C2 a skopírujte ho nadol potiahnutím úchytky v pravom dolnom rohu bunky nadol do bunky C11:

1	= SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 11)

Nezabudnite formátovať výstup vzorca v percentách (Domov > Číselná skupina > Percentuálny štýl).

Krok č. 3: Vytvorte klastrovaný stĺpcový graf.

Keď sú údaje grafu správne usporiadané, nakreslite jednoduchý klastrovaný stĺpcový graf.

1. Zvýraznite všetky údaje grafu (A1: C11).
2. Choďte do Vložiť tab.
3. Kliknite na „Vložiť stĺpcový alebo stĺpcový graf.”
4. Vyberte „Zhlukované stĺpce.”

Keď tam budete, budúci Paretov diagram by mal vyzerať takto:

Krok č. 4: Vytvorte kombinovaný graf.

Teraz je načase obrátiť sériu údajov znázorňujúcu kumulatívne percentá (Séria „Kumulatívne %“) do čiarového grafu.

Kliknite pravým tlačidlom myši na ktorýkoľvek z malých oranžových pruhov, ktoré predstavujú Séria „Kumulatívne %“ a vyberte „Zmeniť typ grafu série”Zo zobrazenej ponuky.

V Zmeňte typ grafu dialógové okno, transformujte zoskupený stĺpcový graf na kombinovaný graf:

1. Prepnúť na Combo tab.
2. Pre Séria „Kumulatívne %“ zmeniť “Typ grafu“Až„Riadok so značkami“A skontrolujte„Sekundárna os”Box.

Excel 2010 alebo staršie verzie: V Zmeňte typ grafu Prejdite na kartu Riadok kartu a vyberte „Riadok so značkami. ” Potom kliknite pravým tlačidlom myši na riadok, ktorý sa zobrazí, vyberte Formátovať rad údajov, a v Možnosti série vyberte kartu „Sekundárna os.”

Keď to urobíte, váš kombinovaný graf by mal vyzerať takto:

Krok č. 5: Upravte mierku sekundárnej vertikálnej osi.

V Paretovom grafe nemôžu hodnoty čiarového grafu nikdy presiahnuť sto percent, takže podľa toho upravme rozsahy sekundárnej vertikálnej osi.

Kliknite pravým tlačidlom myši na sekundárnu zvislú os (čísla pozdĺž pravej strany) a zvoľte „Formát osi.”

Raz Formát osi zobrazí sa panel úloh, postupujte takto:

1. Choďte do Možnosti osi
2. Zmeniť Maximálne hranice na “1.”

Krok č. 6: Zmeňte šírku medzery v stĺpcoch.

Rozšírte stĺpce tak, že doladíte šírku medzery zodpovedajúceho radu údajov (Séria „Položky vrátené“).

Ak to chcete urobiť, kliknite pravým tlačidlom myši na ktorýkoľvek z modrých stĺpcov, ktoré predstavujú Séria „Položky vrátené“ a kliknite na „Formátovať rad údajov.”

Potom postupujte podľa niekoľkých jednoduchých krokov:

1. Prejdite na Možnosti série tab.
2. Zmeniť „Šírka medzery“Až„3%.”

Krok č. 7: Pridajte štítky s údajmi

Je načase pridať štítky údajov pre obe dátové rady (Kliknite pravým tlačidlom myši > Pridajte štítky s údajmi).

Ako ste si mohli všimnúť, štítky pre linku Pareto pôsobia trochu chaoticky, umiestnime ich teda priamo nad riadok. Ak chcete zmeniť umiestnenie štítkov, kliknite pravým tlačidlom na štítky s údajmi pre Séria „Kumulatívne %“ a zvoľte „Formátovanie dátových štítkov.”

V Formátovanie dátových štítkov na paneli úloh zmeňte polohu štítkov takto:

1. Choďte do Možnosti štítku tab.
2. V časti „Poloha štítku,"Vybrať"Hore.”

Môžete tiež zmeniť farbu a veľkosť štítkov, aby ste ich od seba navzájom odlíšili.

Krok č. 8: Vyčistite graf.

Nakoniec, keď dokončujeme leštenie grafu, zbavte sa legendy a mriežky grafu. Nezabudnite pridať názvy osí podľa vyššie uvedených krokov (Dizajn > Pridať prvok grafu > Názvy osí > Primárne horizontálne + Primárne vertikálne).

Váš vylepšený Paretov graf je pripravený!