Indhold
Du har måske hørt om Pareto-loven eller 20/80-princippet. I slutningen af det 19. århundrede opdagede den italienske sociolog og økonom Vilfredo Pareto, at fordelingen af velstand i samfundet er ujævn og underlagt en vis afhængighed: med en stigning i velstand falder antallet af rige mennesker eksponentielt med en konstant koefficient ( blandt italienske husholdninger var 80 % af indkomsten i 20 % af familierne). Senere blev denne idé udviklet i sin bog af Richard Koch, som foreslog formuleringen af det universelle "Princip 20/80" (20% af indsatsen giver 80% af resultatet). I praksis er denne lov normalt ikke udtrykt i så smukke tal (læs "The Long Tail" af Chris Anderson), men viser tydeligt den ujævne fordeling af ressourcer, overskud, omkostninger osv.
I forretningsanalyse er et Pareto-diagram ofte bygget til at repræsentere denne ujævnhed. Det kan bruges til visuelt at vise for eksempel, hvilke produkter eller kunder, der giver mest overskud. Det ser normalt sådan ud:
Dens hovedtræk:
- Hver blå kolonne i histogrammet repræsenterer fortjenesten for produktet i absolutte enheder og er plottet langs venstre akse.
- Den orange graf repræsenterer den kumulative procentdel af overskud (dvs. andelen af overskud på kumulativ basis).
- På en betinget grænse på 80 % tegnes normalt en vandret tærskellinje for tydelighedens skyld. Alle varer til venstre for skæringspunktet for denne linje med grafen for akkumuleret profit bringer os 80% af pengene, alle varer til højre - de resterende 20%.
Lad os se, hvordan du opbygger et Pareto-diagram i Microsoft Excel på egen hånd.
Mulighed 1. Et simpelt Pareto-diagram baseret på færdige data
Hvis kildedataene kom til dig i form af en lignende tabel (det vil sige allerede i færdig form):
… så gør vi følgende.
Sorter tabellen i faldende overskudsrækkefølge (faneblad Data – Sortering) og tilføj en kolonne med formlen til beregning af den akkumulerede procentdel af profit:
Denne formel dividerer den samlede akkumulerede fortjeneste fra begyndelsen af listen til den aktuelle vare med den samlede fortjeneste for hele tabellen. Vi tilføjer også en kolonne med en konstant på 80 % for at skabe en vandret stiplet tærskellinje i det fremtidige diagram:
Vi udvælger alle data og bygger et almindeligt histogram på fanen Indsæt – Histogram (Indsæt – Kolonnediagram). Det skal se sådan ud:
Procentserien i det resulterende diagram skal sendes langs den sekundære (højre) akse. For at gøre dette skal du vælge rækkerne med musen, men det kan være svært, da de er svære at se på baggrund af store overskudskolonner. Så det er bedre at bruge rullelisten på fanen til at fremhæve Layout or dannet:
Højreklik derefter på den valgte række og vælg kommandoen Formater dataserier og vælg indstillingen i det vindue, der vises På den sekundære akse (sekundær akse). Som et resultat vil vores diagram se sådan ud:
For serierne Akkumuleret overskudsandel og Tærskel skal du ændre diagramtypen fra kolonner til linjer. For at gøre dette skal du klikke på hver af disse rækker og vælge kommandoen Skift seriekorttype.
Tilbage er blot at vælge den vandrette tærskelrække og formatere den, så den ligner en afskæringslinje i stedet for data (dvs. fjern markeringerne, gør linjen stiplet rød osv.). Alt dette kan gøres ved at højreklikke på rækken og vælge kommandoen Formater dataserier. Nu vil diagrammet antage sin endelige form:
Ifølge den kan vi konkludere, at 80% af overskuddet kommer fra de første 5 varer, og alle andre varer til højre for kartoflen tegner sig kun for 20% af fortjenesten.
I Excel 2013 kan du gøre det endnu nemmere - brug den nye indbyggede kombinationsdiagramtype med det samme, når du plotter:
Mulighed 2: Pivottabel og Pivot Pareto-diagram
Hvad skal man gøre, hvis der ikke er nogen færdige data til byggeri, men kun den originale rå information? Lad os antage, at vi i begyndelsen har en tabel med salgsdata som denne:
For at bygge et Pareto-diagram på det og finde ud af, hvilke produkter der sælger bedst, skal du først analysere kildedataene. Den nemmeste måde at gøre dette på er med en pivottabel. Vælg en hvilken som helst celle i kildetabellen, og brug kommandoen Indsæt – Pivottabel (Indsæt – Pivottabel). I det mellemliggende vindue, der vises, skal du ikke ændre noget og klikke OK, og træk derefter kildedatafelterne fra toppen til bunden af layoutet af den fremtidige pivottabel i panelet, der vises til højre:
Resultatet skal være en oversigtstabel med den samlede omsætning for hvert produkt:
Sorter den i faldende omsætningsrækkefølge ved at indstille den aktive celle til kolonnen Beløb i feltet Omsætning og ved at bruge sorteringsknappen От Я до А (Fra Z til A) fanen data.
Nu skal vi tilføje en beregnet kolonne med akkumuleret renteindtjening. For at gøre dette skal du trække feltet igen Indtægter til området Værdier i højre rude for at få en dublet kolonne i pivoten. Højreklik derefter på den klonede kolonne og vælg kommando Yderligere beregninger – % af den løbende total i feltet (Vis data som – % Running Total In). Vælg feltet i det vindue, der vises Navn, hvorpå procentdelen af omsætningen vil akkumulere fra top til bund. Outputtet skal se ud som denne tabel:
Som du kan se, er dette nærmest et færdiglavet bord fra første del af artiklen. Det mangler kun for fuldstændig lykke en kolonne med en tærskelværdi på 80% for at konstruere en afskæringslinje i et fremtidigt diagram. En sådan kolonne kan nemt tilføjes ved hjælp af et beregnet felt. Fremhæv et vilkårligt tal i oversigten, og klik derefter på fanen Hjem – Indsæt – Beregnet felt (Hjem – Indsæt – Beregnet felt). I vinduet, der åbnes, skal du indtaste feltnavnet og dets formel (i vores tilfælde en konstant):
Efter at have klikket på OK en tredje kolonne vil blive tilføjet til tabellen med en værdi på 80% i alle celler, og den vil endelig antage den nødvendige form. Så kan du bruge kommandoen Pivot-diagram (pivotdiagram) fanen parametre (Muligheder) or Analyse (Analyse) og opsæt diagrammet på nøjagtig samme måde som den første mulighed:
Fremhæv nøgleprodukter
For at fremhæve de mest påvirkende faktorer, dvs. kolonner placeret til venstre for skæringspunktet for den orange akkumulerede rentekurve med den vandrette afskæringslinje på 80 % kan fremhæves. For at gøre dette skal du tilføje en anden kolonne til tabellen med formlen:
Denne formel udsender 1, hvis produktet er til venstre for skæringspunktet og 0, hvis det er til højre. Så skal du gøre følgende:
- Vi tilføjer en ny kolonne til diagrammet – den nemmeste måde at gøre dette på er ved simpel kopiering, dvs. baggrundslys, kopier det (Ctrl + C), vælg diagrammet og indsæt (Ctrl + V).
- Vælg den tilføjede række, og skift den langs den sekundære akse, som beskrevet ovenfor.
- Seriediagramtype baggrundslys skift til kolonner (histogram).
- Vi fjerner sideafstanden i rækkens egenskaber (højreklik på rækken Belysning – Rækkeformat – Sidegab), så kolonnerne smelter sammen til en enkelt helhed.
- Vi fjerner kolonnernes grænser og gør fyldningen gennemskinnelig.
Som et resultat får vi sådan et flot højdepunkt af de bedste produkter:
PS
Fra og med Excel 2016 er Pareto-diagrammet blevet tilføjet til standardsættet af Excel-diagrammer. Nu, for at bygge det, skal du bare vælge området og på fanen indsatte (Indsæt) vælg den passende type:
Et klik – og diagrammet er klar:
- Sådan opbygger du en rapport ved hjælp af en pivottabel
- Opsæt beregninger i pivottabeller
- Hvad er nyt i diagrammer i Excel 2013
- Wikipedia-artikel om Paretos lov