Podívejte se na video - Jak vytvořit Pareto graf v aplikaci Excel
Pareto Chart je založen na Paretově principu (také známém jako pravidlo 80/20), což je v projektovém řízení známý koncept.
Podle tohoto principu lze ~ 80% problémů připsat přibližně ~ 20% problémů (nebo ~ 80% vašich výsledků může být přímým výsledkem ~ 20% vašeho úsilí atd.).
Procentní hodnota 80/20 se může lišit, ale myšlenka je, že ze všech problémů/úsilí existuje několik, které vedou k maximálnímu dopadu.
Toto je široce používaný koncept v projektovém řízení k upřednostnění práce.
Vytvoření Paretova diagramu v aplikaci Excel
V tomto tutoriálu vám ukážu, jak vytvořit:
- Jednoduchý (statický) Paretův graf v aplikaci Excel.
- Dynamický (interaktivní) Paretův graf v aplikaci Excel.
Vytvoření Paretova diagramu v aplikaci Excel je velmi snadné.
Všechny triky jsou skryty v tom, jak uspořádáte data v backendu.
Vezměme si příklad hotelu, pro který by údaje o stížnostech mohly vypadat nějak takto:
POZNÁMKA: Chcete -li vytvořit Paretův graf v aplikaci Excel, musíte mít data uspořádaná sestupně.
Vytvoření jednoduchého (statického) Paretova grafu v aplikaci Excel
Zde jsou kroky k vytvoření Paretova grafu v aplikaci Excel:
- Nastavte data podle níže uvedeného obrázku.
- Vypočítejte kumulativní % ve sloupci C. Použijte následující vzorec: = SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 1)
- Vyberte celou sadu dat (A1: C10), přejděte na Vložit -> Grafy -> 2 -D sloupec -> Seskupený sloupec. Tím se vloží sloupcový graf se 2 řadami dat (počet stížností a kumulativní procento).
- Klikněte pravým tlačítkem na některý z pruhů a vyberte Změnit typ grafu řady.
- V dialogovém okně Změnit typ grafu vyberte v levém podokně Kombinace.
- Proveďte následující změny:
- Počet stížností: Seskupený sloupec.
- Kumulativní %: Řádek (také zaškrtněte políčko Sekundární osa).
[Pokud používáte Excel 2010 nebo 2007, bude to dvoustupňové proces. Nejprve změňte typ grafu na spojnicový graf. Potom klikněte pravým tlačítkem na spojnicový graf a vyberte Formátovat datové řady a v možnostech řady vyberte Sekundární osa]
- Paretův graf v Excelu je připraven. Upravte hodnoty svislé osy a název grafu.
Jak interpretovat tento Paretův graf v aplikaci Excel
Tento Paretův graf zdůrazňuje hlavní problémy, na které by se hotel měl zaměřit, aby vyřešil maximální počet stížností. Například cílení na první 3 problémy by se automaticky postaralo o ~ 80% stížností.
Například cílení na první 3 problémy by se automaticky postaralo o ~ 80% stížností.
Vytvoření dynamického (interaktivního) Paretova grafu v aplikaci Excel
Nyní, když máme v Excelu statický/jednoduchý Paretův graf, pojďme to udělat o krok dále a udělat to trochu interaktivní.
Něco, jak je uvedeno níže:
V takovém případě může uživatel určit % stížností, které je třeba řešit (pomocí posuvníku Excelu), a graf automaticky zvýrazní problémy, které by měly být prozkoumány.
Cílem je mít 2 různé pruhy.
Červená se zvýrazní, když se kumulativní procentuální hodnota blíží cílové hodnotě.
Zde jsou kroky k vytvoření tohoto interaktivního Paretova grafu v aplikaci Excel:
- V buňce B14 mám cílovou hodnotu, která je spojena s posuvníkem (jehož hodnota se pohybuje od 0 do 100).
- V buňce B12 jsem použil vzorec = B14/100. Jelikož nemůžete pro posuvník zadat procentuální hodnotu, jednoduše hodnotu posuvníku rozdělíme (v B14) na 100, abychom získali procentuální hodnotu.
- Do buňky B13 zadejte následující kombinaci funkcí INDEX, MATCH a IFERROR:
= IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1), 0) +1), 1)
Tento vzorec vrací kumulativní hodnotu, která by pokryla cílovou hodnotu. Pokud například máte cílovou hodnotu 70%, vrátí 77%, což znamená, že byste se měli pokusit vyřešit první tři problémy.
- Do buňky D2 zadejte následující vzorec (a přetáhněte nebo zkopírujte pro všechny buňky - D2: D10):
= IF ($ B $ 13> = C2, B2, NA ()) - Do buňky E2 zadejte následující vzorec (a přetáhněte nebo zkopírujte pro všechny buňky - E2: E10):
= IF ($ B $ 13<> - Vyberte data ve sloupci A, C, D & E (stiskněte ovládací prvek a vyberte pomocí myši).
- Přejděte na Vložit -> Grafy -> 2 -D sloupec -> Seskupený sloupec. Tím se vloží sloupcový graf se 3 řadami dat (kumulativní procento, pruhy, které mají být zvýrazněny, aby splnily cíl, a zbývající všechny ostatní pruhy)
- Klikněte pravým tlačítkem na některý z pruhů a vyberte Změnit typ grafu řady.
- V dialogovém okně Změnit typ grafu vyberte v levém podokně Combo a proveďte následující změny:
- Kumulativní %: Řádek (také zaškrtněte políčko Sekundární osa).
- Zvýrazněné pruhy: Seskupený sloupec.
- Zbývající tyče: Seskupený sloupec.
- Klikněte pravým tlačítkem na některý ze zvýrazněných pruhů a změňte barvu na červenou.
A je to!
V aplikaci Excel jste vytvořili interaktivní Paretův graf.
Nyní, když změníte cíl pomocí posuvníku, Paretův graf by se odpovídajícím způsobem aktualizoval.
Používáte Paretův graf v Excelu?
Rád bych slyšel váš názor na tuto techniku a jak jste ji použili. Zanechejte své stopy v sekci komentářů 🙂
- Analýza stížností na restaurace pomocí Pareto Chart.
- Vytvoření Ganttova diagramu v aplikaci Excel.
- Vytvoření milníkového grafu v Excelu.
- Vytvoření histogramu v aplikaci Excel.
- Šablona kalkulačky časového rozvrhu aplikace Excel.
- Zaměstnanec Nechte šablonu sledovače.
- Výpočet váženého průměru v aplikaci Excel.
- Vytvoření Bellovy křivky v Excelu.
- Pokročilé grafy aplikace Excel
- Jak přidat sekundární osu do grafů aplikace Excel.
