Jak vytvořit Paretův graf v aplikaci Excel

Tento tutoriál předvede, jak vytvořit Paretův graf ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Paretův graf je hybridem sloupcového grafu a spojnicového grafu, který udává relativní důležitost faktorů nebo položek v dané datové sadě spolu s jejich kumulativními procenty. Za svůj název graf vděčí Paretovu principu, známému také jako zákon několika životně důležitých, který uvádí, že přibližně 80% příčin přispívá k 80% následků.

Účelem grafu je rozeznat kritické faktory v datové sadě a odlišit je od nevýznamných.

Paretův graf se stal nativním typem grafu v aplikaci Excel 2016, ale pro uživatele aplikace Excel 2013 nebo starších verzí je jedinou možností, jak graf vytvořit ručně, od začátku.

V tomto kurzu se naučíte, jak vytvořit přizpůsobitelný Paretův diagram ručně i pomocí integrovaného nástroje pro tvorbu grafů v aplikaci Excel.

Začínáme

Abychom vám ukázali lana, potřebujeme zpracovat některá nezpracovaná data. Z tohoto důvodu předpokládejme, že jste se rozhodli analyzovat rozpis položek vrácených do obchodu s oděvy a pokusit se zjistit, které kategorie produktů způsobují největší potíže.

S ohledem na to zvažte následující data:

Pojďme se tedy ponořit přímo dovnitř.

Jak vytvořit Paretův graf v aplikaci Excel 2016+

Nejprve mi dovolte ukázat vám, jak vykreslit Paretův graf pomocí odpovídajícího předdefinovaného typu grafu Excelu zavedeného v Excelu 2016.

S touto metodou nastavení grafu zabere jen několik kliknutí, ale přijdete o některé funkce přizpůsobení-například do řádku Pareto nemůžete přidat ani popisky dat, ani značky.

Pokud tedy potřebujete další úroveň přizpůsobení, má smysl sestavit graf od základů. Ale nejprve je zde algoritmus pro ty, kteří chtějí jít jednodušší cestou.

Krok č. 1: Vykreslení Paretova diagramu.

Opět platí, že pokud používáte Excel 2016 nebo novější, Excel vám umožní vytvořit jednoduchý Paretův graf a přitom sotva zvednout prst:

1. Zvýrazněte svá skutečná data (A1: B11).
2. Jděte na Vložit tab.
3. Klikněte na „Vložit statistický graf.”
4. Vybrat "Pareto.”

Magicky se okamžitě objeví Paretův graf:

Technicky tomu můžeme říkat den, ale aby graf pomohl vyprávět příběh, možná budete muset dát nějakou práci navíc.

Krok č. 2: Přidejte štítky s údaji.

Začněte přidáním štítků dat do grafu. Klikněte pravým tlačítkem na některý ze sloupců a vyberte „Přidejte datové štítky.”

Přizpůsobte barvu, písmo a velikost štítků, aby vynikly (Domov > Písmo).

Krok č. 3: Přidejte názvy os.

Jako třešnička na dortu poskytují názvy os další kontext k tomu, o čem graf je.

1. Vyberte oblast grafu.
2. Přepnout na Design tab.
3. Udeř "Přidat prvek grafu" knoflík.
4. Vybrat “Tituly os”Z rozevírací nabídky.
5. Vyberte obojí “Primární horizontální" a "Primární vertikální.”

Krok č. 4: Přidejte poslední úpravy.

Vzhledem k tomu, že graf nadále dolaďujeme, můžete nyní odstranit mřížky, které neslouží žádnému praktickému účelu. Klikněte pravým tlačítkem na mřížku a klikněte na „Vymazat“Vymazat je.

Nakonec změňte názvy os a grafů a upgradovaný graf Pareto je připraven!

Jak vytvořit Paretův graf v Excelu 2007, 2010 a 2013

To bylo docela rychlé. Uživatelé verzí starších než Excel 2016 však nemají jinou možnost, než si veškerou práci udělat sami. Existuje však stříbrná podšívka: tímto způsobem získáte mnohem více prostoru pro přizpůsobení.

Přestože všechny snímky obrazovky byly pořízeny v Excelu 2022, metoda, kterou se chystáte naučit, zůstává kompatibilní s verzemi zpět do Excelu 2007. Postupujte podle pokynů a upravte je.

Krok č. 1: Seřaďte data sestupně.

Nejprve seřaďte data podle počtu vrácených položek (sloupec B) v sestupném pořadí.

1. Zvýrazněte svá skutečná data (A1: B11).
2. Jděte na Data tab.
3. Klikněte na „Seřadit" knoflík.
4. V Seřadit v dialogovém okně proveďte následující:
   1. Pro "Sloupec"Vyberte"Položky vrácené.”
   2. Pro "Seřadit dál," Vybrat "Hodnoty buněk.”
   3. Pro "Objednat„Vybrat“Největší až nejmenší.”

Krok č. 2: Vypočítejte kumulativní procenta.

Naším dalším krokem je vytvoření pomocného sloupce s názvem „Kumulativní %“ které sečtou relativní podíly kategorií produktů v datové sadě, které mají být použity jako hodnoty pro grafování linie Pareto.

Chcete -li zjistit hodnoty, zadejte do něj následující vzorec C2 a zkopírujte jej přetažením úchytu v pravém dolním rohu buňky dolů do buňky C11:

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

Nezapomeňte formátovat výstup vzorce jako procenta (Domov > Skupina čísel > Procentní styl).

Krok č. 3: Vytvořte seskupený sloupcový graf.

Jakmile jsou data grafu správně uspořádána, vykreslete jednoduchý seskupený sloupcový graf.

1. Zvýrazněte všechna data grafu (A1: C11).
2. Jděte na Vložit tab.
3. Klikněte na „Vložte sloupcový nebo sloupcový graf.”
4. Vyberte „Seskupené sloupce.”

Až tam bude, budoucí Paretův graf by měl vypadat takto:

Krok č. 4: Vytvořte kombinovaný graf.

Nyní je čas obrátit datové řady ilustrující kumulativní procenta (Řada „Kumulativní %“) do spojnicového grafu.

Klikněte pravým tlačítkem na kterýkoli z malých oranžových pruhů, které představují Řada „Kumulativní %“ a vyberte „Změnit typ grafu řady”Ze zobrazené nabídky.

V Změnit typ grafu dialogové okno, transformujte seskupený sloupcový graf na kombinovaný graf:

1. Přepnout na Combo tab.
2. Pro Řada „Kumulativní %“ změna "Typ grafu“Až„Řádek se značkami“A zkontrolujte„Sekundární osa" krabice.

Excel 2010 nebo starší verze: V Změnit typ grafu přejděte na kartu Čára kartu a vyberte „Řádek se značkami. ” Poté klikněte pravým tlačítkem na zobrazený řádek, vyberte Formát datové řady, a v Možnosti řady na kartě vyberte „Sekundární osa.”

Když to uděláte, váš kombinovaný graf by měl vypadat takto:

Krok č. 5: Upravte měřítko sekundární svislé osy.

V Paretově grafu nesmí hodnoty čárového grafu nikdy překročit sto procent, upravme proto odpovídajícím způsobem rozsahy sekundární svislé osy.

Klikněte pravým tlačítkem na sekundární svislou osu (čísla podél pravé strany) a vyberte „Formátovat osu.”

Jednou Formátovat osu Zobrazí se podokno úloh, proveďte následující:

1. Jděte na Možnosti osy
2. Změň Maximální hranice na “1.”

Krok č. 6: Změňte šířku mezery sloupců.

Zvětšete sloupce vyladěním šířky mezery odpovídající datové řady (Série „Položky vrácené“).

Chcete-li to provést, klikněte pravým tlačítkem na některý z modrých sloupců, které představují Série „Položky vrácené“ a klikněte na „Formát datové řady.”

Poté proveďte několik jednoduchých kroků:

1. Přejděte na Možnosti řady tab.
2. Změna "Šířka mezery“Až„3%.”

Krok č. 7: Přidejte štítky s údaji

Je čas přidat štítky dat pro obě datové řady (Klikněte pravým tlačítkem myši > Přidejte datové štítky).

Jak jste si možná všimli, štítky pro řadu Pareto vypadají trochu chaoticky, takže je umístíme přímo nad řádek. Chcete-li přemístit popisky, klikněte pravým tlačítkem na popisky dat pro Řada „Kumulativní %“ a vyberte „Formátovat datové štítky.”

V Formátovat datové štítky v podokně úloh změňte polohu popisků následujícím způsobem:

1. Jděte na Možnosti štítku tab.
2. Pod „Pozice etikety," Vybrat "Výše.”

Můžete také změnit barvu a velikost štítků, abyste je od sebe odlišili.

Krok č. 8: Vyčistěte graf.

Nakonec, když dokončujeme leštění grafu, nezapomeňte se zbavit legendy grafu a mřížky. Nezapomeňte přidat názvy os podle výše uvedených kroků (Design > Přidat prvek grafu > Tituly os > Primární horizontální + Primární vertikální).

Váš vylepšený Paretův graf je připraven!