Vytvoření Paretova grafu v aplikaci Excel (statický a dynamický)

Přehrát video – Jak vytvořit Paretův graf v aplikaci Excel

Paretův graf je založen na Paretově principu (známém také jako pravidlo 80/20), což je dobře známý koncept v projektovém řízení.

The 80 / 20 procentní hodnota se může lišit, ale myšlenka je, že u všech problémů / snah existuje několik, které vedou k maximálnímu dopadu.

Toto je široce používaný koncept v projektovém managementu 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 grafu v aplikaci Excel je velmi snadné.

Všechny triky jsou skryty v jak uspořádáte data v backendu.

Vezměme si příklad hotelu, u kterého mohou údaje o stížnostech vypadat nějak, jak je uvedeno níže:

POZNÁMKA: Chcete-li vytvořit Paretův graf v aplikaci Excel, musíte mít data uspořádaná v sestupném pořadí.

Stáhněte si šablonu Paretova grafu aplikace Excel

Vytvoření jednoduchého (statického) Paretova grafu v aplikaci Excel

Zde jsou kroky k vytvoření Paretova grafu v aplikaci Excel:

1. Nastavte svá data, jak je uvedeno níže.
2. Vypočítejte kumulativní% ve sloupci C. Použijte následující vzorec: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. 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).
4. Klikněte pravým tlačítkem na některý z pruhů a vyberte Změnit Typ grafu řady.
5. V dialogovém okně Změnit typ grafu vyberte v levém podokně možnost Kombinovat.
6. Proveďte následující změny:
   • Počet stížností: Seskupený sloupec.
   • Kumulativní%: Řádek (zaškrtněte také políčko Sekundární osa).
7. Váš Paretův graf v aplikaci Excel je připraven. Upravte hodnoty svislé osy a název grafu.

Jak interpretovat tento Paretův graf v aplikaci Excel

Tento Pareto graf zdůrazňuje hlavní problémy, na které by se měl hotel zaměřit, aby seřídil 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 statický / jednoduchý Paretův graf v aplikaci Excel, pojďme o krok dále a udělejte to trochu interaktivní.

Něco, co je uvedeno níže:

V tomto případě může uživatel určit% stížností, které je třeba řešit (pomocí posuvníku Excel sloupec) a graf automaticky zvýrazní problémy, na které byste se měli podívat.

Myšlenkou je mít 2 různé pruhy.

Červený je zvýrazněn, když je kumulativní procento hodnota se blíží cílové hodnotě.

Zde jsou kroky, jak vytvořit tento interaktivní Paretův graf v aplikaci Excel:

1. V buňce B14 mám cílovou hodnotu, která je propojena na posuvník (jehož hodnota se pohybuje od 0 do 100).
2. V buňce B12 jsem použil vzorec = B14 / 100. Vzhledem k tomu, že pro posuvnou lištu nemůžete určit procentní hodnotu, jednoduše rozdělíme hodnotu posuvného pruhu (v B14) na 100, abychom získali procentuální hodnotu.
3. V buňce 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. Například pokud máte cílovou hodnotu 70%, vrátí se 77%, což znamená, že byste se měli pokusit vyřešit první tři problémy.

1. V buňce D2 zadejte následující vzorec (a přetáhněte nebo zkopírujte pro celou buňku – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. V buňce E2 zadejte následující vzorec (a přetáhněte nebo zkopírujte celou buňku – E2: E10):
   = IF ( $ B $ 13 < C2, B2, NA ())
3. Vyberte data ve sloupci A, C, D & E (stiskněte ovládací prvek a vyberte pomocí myši).
4. Přejít na Vložit – > Grafy – > 2-D Column – > Seskupený sloupec.Tím se vloží sloupcový graf se 3 řadami dat (kumulativní procento, pruhy, které mají být zvýrazněny, aby splňovaly cíl, a zbývající všechny ostatní pruhy)
5. Klikněte pravým tlačítkem na kterýkoli z pruhů a vyberte Změnit graf řady Zadejte.
6. V dialogovém okně Změnit typ grafu vyberte v levém podokně Combo a proveďte následující změny:
   • Kumulativní%: Řádek (zaškrtněte také políčko Sekundární osa).
   • Zvýrazněné pruhy: Seskupený sloupec.
   • Zbývající pruhy: Seskupený sloupec.
7. Klikněte pravým tlačítkem na některý ze zvýrazněných pruhů a změňte barvu na červenou.

To je ono!

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.

Stáhněte si šablonu Paretova grafu Excel

Používáte Paretův graf v aplikaci Excel?

Rád bych slyšel vaše myšlenky na tuto techniku a na to, jak použil jsem to. Nechte své stopy v sekci komentářů 🙂

Související návody pro správu projektů a mapování:

• Analýza stížností na restaurace pomocí Paretova diagramu.
• Vytvoření Ganttova diagramu Graf v aplikaci Excel.
• Vytvoření milníkového grafu v aplikaci Excel.
• Vytvoření histogramu v aplikaci Excel.
• Šablona kalkulačky časového rozvrhu aplikace Excel.
• Šablona pro sledování odchodu zaměstnanců.
• Výpočet váženého průměru v aplikaci Excel.
• Vytvoření Bell Curve v aplikaci Excel.
• Pokročilé grafy aplikace Excel
• Jak přidat sekundární osu do grafů aplikace Excel.