Obsah
Možná jste slyšeli o Paretově zákonu nebo o principu 20/80. Na konci 19. století italský sociolog a ekonom Vilfredo Pareto zjistil, že rozdělení bohatství ve společnosti je nerovnoměrné a podléhá určité závislosti: s růstem bohatství exponenciálně klesá počet bohatých s konstantním koeficientem ( mezi italskými domácnostmi bylo 80 % příjmu ve 20 % rodin). Později tuto myšlenku rozvinul ve své knize Richard Koch, který navrhl formulaci univerzálního „Principu 20/80“ (20 % úsilí dává 80 % výsledku). V praxi se tento zákon obvykle nevyjadřuje tak krásnými čísly (čtěte „Dlouhý ocas“ od Chrise Andersona), ale jasně ukazuje nerovnoměrné rozdělení zdrojů, zisků, nákladů atd.
V obchodní analýze se často vytváří Paretův diagram, který tuto nerovnoměrnost reprezentuje. S jeho pomocí lze například vizuálně ukázat, které produkty nebo zákazníci přinášejí největší zisk. Obvykle to vypadá takto:
Jeho hlavní rysy:
- Každý modrý sloupec histogramu představuje zisk za produkt v absolutních jednotkách a je vykreslen podél levé osy.
- Oranžový graf představuje kumulativní procento zisku (tj. podíl na zisku na kumulativní bázi).
- Na podmíněné hranici 80 % se pro přehlednost obvykle nakreslí prahová vodorovná čára. Veškeré zboží nalevo od průsečíku této čáry s grafem kumulovaného zisku nám přináší 80 % peněz, veškeré zboží napravo – zbývajících 20 %.
Podívejme se, jak si sami vytvořit Paretův graf v aplikaci Microsoft Excel.
Možnost 1. Jednoduchý Paretův diagram založený na hotových datech
Pokud vám zdrojová data přišla ve formě podobné tabulky (tedy již v hotové podobě):
… pak uděláme následující.
Seřaďte tabulku v sestupném pořadí podle zisku (tab Data – řazení) a přidejte sloupec se vzorcem pro výpočet kumulovaného procenta zisku:
Tento vzorec vydělí celkový kumulovaný zisk od začátku seznamu po aktuální položku celkovým ziskem za celou tabulku. Přidáme také sloupec s konstantou 80 %, abychom v budoucím grafu vytvořili horizontální prahovou přerušovanou čáru:
Vybereme všechna data a na záložce sestavíme pravidelný histogram Vložit – Histogram (Vložit – Sloupcový graf). Mělo by to vypadat nějak takto:
Procentuální řada ve výsledném grafu by měla být odeslána podél sekundární (pravé) osy. Chcete-li to provést, musíte vybrat řádky pomocí myši, ale to může být obtížné, protože jsou špatně viditelné na pozadí velkých sloupců zisku. Pro zvýraznění je tedy lepší použít rozevírací seznam na kartě Nákres or Formát:
Poté klikněte pravým tlačítkem myši na vybraný řádek a vyberte příkaz Formátovat datové řady a v okně, které se zobrazí, vyberte možnost Na sekundární ose (sekundární osa). V důsledku toho bude náš diagram vypadat takto:
U řady Podíl na akumulovaném zisku a Práh je třeba změnit typ grafu ze sloupců na řádky. Chcete-li to provést, klikněte na každý z těchto řádků a vyberte příkaz Změnit typ grafu řady.
Zbývá pouze vybrat vodorovnou řadu Threshold a naformátovat ji tak, aby vypadala spíše jako ořezová čára než jako data (tj. odstranit značky, přeškrtnout čáru červeně atd.). To vše lze provést kliknutím pravým tlačítkem myši na řádek a výběrem příkazu Formátovat datové řady. Nyní bude mít diagram konečnou podobu:
Podle ní můžeme usoudit, že 80 % zisku přináší prvních 5 zboží a veškeré ostatní zboží napravo od brambor tvoří pouze 20 % zisku.
V Excelu 2013 to můžete udělat ještě jednodušeji – použijte nový integrovaný typ kombinovaného grafu ihned při vykreslování:
Možnost 2: Kontingenční tabulka a kontingenční Paretův graf
Co dělat, když neexistují žádná hotová data pro konstrukci, ale pouze původní nezpracované informace? Předpokládejme, že na začátku máme tabulku s údaji o prodeji, jako je tato:
Chcete-li na něm sestavit Paretův graf a zjistit, které produkty se prodávají nejlépe, budete muset nejprve analyzovat zdrojová data. Nejjednodušší způsob, jak toho dosáhnout, je pomocí kontingenční tabulky. Vyberte libovolnou buňku ve zdrojové tabulce a použijte příkaz Vložit – kontingenční tabulka (Vložit – kontingenční tabulka). V zobrazeném meziokně nic neměňte a klikněte OKa poté na panelu, který se zobrazí vpravo, přetáhněte zdrojová datová pole shora do spodních oblastí rozvržení budoucí kontingenční tabulky:
Výsledkem by měla být souhrnná tabulka s celkovými příjmy pro každý produkt:
Seřaďte jej v sestupném pořadí podle příjmů nastavením aktivní buňky na sloupec Částka v poli Výnosy a pomocí tlačítka řazení От Я до А (Od Z do A) Karta Data.
Nyní musíme přidat vypočítaný sloupec s akumulovanými úrokovými výnosy. Chcete-li to provést, přetáhněte pole znovu Příjem do oblasti Hodnoty v pravém podokně, abyste získali duplicitní sloupec v pivotu. Poté klikněte pravým tlačítkem myši na klonovaný sloupec a vyberte příkaz Další výpočty – % průběžného součtu v poli (Zobrazit data jako – % průběžného součtu v). V zobrazeném okně vyberte pole Příjmení, na kterém se bude kumulovat procento příjmů shora dolů. Výstup by měl vypadat takto:
Jak vidíte, jedná se téměř o hotovou tabulku z první části článku. K úplné spokojenosti mu chybí pouze sloupec s prahovou hodnotou 80 % pro konstrukci hraniční čáry v budoucím diagramu. Takový sloupec lze snadno přidat pomocí vypočítaného pole. Zvýrazněte libovolné číslo v souhrnu a poté klikněte na záložku Domů – Vložit – Vypočítané pole (Domů – Vložit – Vypočítané pole). V okně, které se otevře, zadejte název pole a jeho vzorec (v našem případě konstantu):
Po kliknutí na tlačítko OK do tabulky přibude třetí sloupec s hodnotou 80 % ve všech buňkách a ta konečně získá požadovanou podobu. Poté můžete použít příkaz Kontingenční graf (Kontingenční graf) Karta parametry (Možnosti) or Analýza (Analýza) a nastavte graf přesně stejným způsobem jako první možnost:
Zvýraznění klíčových produktů
Pro zvýraznění nejvíce ovlivňujících faktorů lze zvýraznit sloupce umístěné nalevo od průsečíku oranžové kumulované zájmové křivky s horizontální hranicí 80 %. Chcete-li to provést, budete muset do tabulky přidat další sloupec se vzorcem:
Výstupem tohoto vzorce je 1, pokud je součin nalevo od průsečíku, a 0, pokud je napravo. Poté musíte provést následující:
- Do grafu přidáme nový sloupec – nejsnáze to uděláme prostým zkopírováním, tedy zvýrazněním sloupce podsvícení, zkopíruj to (Ctrl + C), vyberte diagram a vložte (Ctrl + V).
- Vyberte přidaný řádek a přepněte jej podél vedlejší osy, jak je popsáno výše.
- Typ grafu řady podsvícení změnit na sloupce (histogram).
- Ve vlastnostech řádku odstraníme boční vůli (klikněte pravým tlačítkem na řádek Osvětlení – Formát řádku – Postranní mezera), aby se sloupce spojily v jeden celek.
- Odstraníme okraje sloupců a výplň uděláme průsvitnou.
V důsledku toho získáváme takový pěkný zvýraznění nejlepších produktů:
PS
Počínaje Excelem 2016 byl Paretův graf přidán do standardní sady Excelových grafů. Nyní jej sestavíte tak, že vyberete rozsah a na záložce Vložit (Vložit) vyberte vhodný typ:
Jedno kliknutí – a diagram je připraven:
- Jak vytvořit sestavu pomocí kontingenční tabulky
- Nastavte výpočty v kontingenčních tabulkách
- Co je nového v grafech v Excelu 2013
- Článek na Wikipedii o Paretově zákonu