Innhold
Du har kanskje hørt om Pareto-loven eller 20/80-prinsippet. På slutten av 19-tallet oppdaget den italienske sosiologen og økonomen Vilfredo Pareto at fordelingen av rikdom i samfunnet er ujevn og underlagt en viss avhengighet: med en økning i rikdom synker antallet rike mennesker eksponentielt med en konstant koeffisient ( blant italienske husholdninger var 80 % av inntekten i 20 % av familiene). Senere ble denne ideen utviklet i boken hans av Richard Koch, som foreslo formuleringen av det universelle "Prinsipp 20/80" (20% av innsatsen gir 80% av resultatet). I praksis er denne loven vanligvis ikke uttrykt i slike vakre tall (les "The Long Tail" av Chris Anderson), men viser tydelig den ujevn fordeling av ressurser, fortjeneste, kostnader, etc.
I forretningsanalyse bygges ofte et Pareto-diagram for å representere denne ujevnheten. Den kan brukes til å visuelt vise for eksempel hvilke produkter eller kunder som gir mest fortjeneste. Det ser vanligvis slik ut:
Hovedtrekkene:
- Hver blå kolonne i histogrammet representerer fortjenesten for produktet i absolutte enheter og er plottet langs venstre akse.
- Den oransje grafen representerer den kumulative prosentandelen av fortjeneste (dvs. andelen av fortjenesten på kumulativ basis).
- På en betinget grense på 80 % tegnes vanligvis en horisontal terskellinje for klarhetens skyld. Alle varer til venstre for skjæringspunktet for denne linjen med grafen for akkumulert fortjeneste gir oss 80% av pengene, alle varer til høyre - de resterende 20%.
La oss se hvordan du bygger et Pareto-diagram i Microsoft Excel på egen hånd.
Alternativ 1. Et enkelt Pareto-diagram basert på ferdige data
Hvis kildedataene kom til deg i form av en lignende tabell (det vil si allerede i ferdig form):
… så gjør vi følgende.
Sorter tabellen i synkende rekkefølge etter fortjeneste (tab Data – Sortering) og legg til en kolonne med formelen for å beregne den akkumulerte prosentandelen av profitt:
Denne formelen deler den totale akkumulerte fortjenesten fra begynnelsen av listen til den gjeldende varen med den totale fortjenesten for hele tabellen. Vi legger også til en kolonne med en konstant på 80 % for å lage en horisontal terskel stiplet linje i det fremtidige diagrammet:
Vi velger alle dataene og bygger et vanlig histogram på fanen Sett inn – Histogram (Sett inn – Kolonnediagram). Det skal se omtrent slik ut:
Prosentserien i det resulterende diagrammet skal sendes langs den sekundære (høyre) aksen. For å gjøre dette må du velge radene med musen, men dette kan være vanskelig, siden de er vanskelig å se på bakgrunn av store fortjenestekolonner. Så det er bedre å bruke rullegardinlisten på fanen for å markere Layout or dannet:
Høyreklikk deretter på den valgte raden og velg kommandoen Formater dataserier og velg alternativet i vinduet som vises På sekundæraksen (sekundæraksen). Som et resultat vil diagrammet vårt se slik ut:
For seriene Akkumulert fortjenesteandel og terskel må du endre diagramtypen fra kolonner til linjer. For å gjøre dette, klikk på hver av disse radene og velg kommandoen Endre seriediagramtype.
Alt som gjenstår er å velge den horisontale terskelen og formatere den slik at den ser ut som en avskjæringslinje i stedet for data (dvs. fjerne markørene, gjøre linjen stiplet rød, osv.). Alt dette kan gjøres ved å høyreklikke på raden og velge kommandoen Formater dataserier. Nå vil diagrammet ta sin endelige form:
I følge den kan vi konkludere med at 80 % av overskuddet kommer av de første 5 varene, og alle andre varer til høyre for poteten utgjør kun 20 % av overskuddet.
I Excel 2013 kan du gjøre det enda enklere – bruk den nye innebygde kombinasjonsdiagramtypen umiddelbart når du plotter:
Alternativ 2: Pivottabell og Pivot Pareto-diagram
Hva skal jeg gjøre hvis det ikke er ferdige data for konstruksjon, men bare den originale råinformasjonen? La oss anta at vi i begynnelsen har en tabell med salgsdata som dette:
For å bygge et Pareto-diagram på det og finne ut hvilke produkter som selger best, må du først analysere kildedataene. Den enkleste måten å gjøre dette på er med en pivottabell. Velg en hvilken som helst celle i kildetabellen og bruk kommandoen Sett inn – Pivottabell (Sett inn – Pivottabell). I det mellomliggende vinduet som vises, ikke endre noe og klikk OK, og dra deretter kildedatafeltene fra toppen til bunnen av oppsettet til den fremtidige pivottabellen i panelet som vises til høyre:
Resultatet skal være en oppsummeringstabell med den totale inntekten for hvert produkt:
Sorter den i synkende inntektsrekkefølge ved å sette den aktive cellen til kolonnen Beløp i feltet Inntekt og bruke sorteringsknappen От Я до А (fra Å til A) tab Data.
Nå må vi legge til en beregnet kolonne med akkumulert renteinntekt. For å gjøre dette, dra feltet på nytt Inntekter til området Verdier i høyre rute for å få en duplikatkolonne i pivoten. Høyreklikk deretter på den klonede kolonnen og velg kommando Ytterligere beregninger – % av løpende total i feltet (Vis data som – % løpende total inn). Velg feltet i vinduet som vises Navn, hvor prosentandelen av inntektene vil akkumuleres fra topp til bunn. Utgangen skal se ut som denne tabellen:
Som du kan se er dette nesten et ferdig bord fra første del av artikkelen. Det mangler bare for fullstendig lykke en kolonne med en terskelverdi på 80 % for å konstruere en grenselinje i et fremtidig diagram. En slik kolonne kan enkelt legges til ved hjelp av et beregnet felt. Marker et hvilket som helst tall i sammendraget og klikk deretter på fanen Hjem – Sett inn – Kalkulert felt (Hjem – Sett inn – Kalkulert felt). I vinduet som åpnes, skriv inn feltnavnet og formelen (i vårt tilfelle en konstant):
Etter å ha klikket på OK en tredje kolonne vil bli lagt til i tabellen med en verdi på 80 % i alle celler, og den vil til slutt ha den nødvendige formen. Deretter kan du bruke kommandoen Pivotdiagram (Pivotdiagram) tab parametere (Alternativer) or Analyse (Analyse) og sett opp diagrammet på nøyaktig samme måte som det første alternativet:
Fremheve nøkkelprodukter
For å fremheve de mest påvirkende faktorene, dvs. kolonner plassert til venstre for skjæringspunktet til den oransje akkumulerte rentekurven med den horisontale grenselinjen på 80 % kan utheves. For å gjøre dette, må du legge til en annen kolonne i tabellen med formelen:
Denne formelen gir 1 hvis produktet er til venstre for skjæringspunktet og 0 hvis det er til høyre. Da må du gjøre følgende:
- Vi legger til en ny kolonne i diagrammet – den enkleste måten å gjøre dette på er ved enkel kopiering, dvs. markere kolonne bakgrunnsbelysning, kopier det (Ctrl + C), velg diagrammet og sett inn (Ctrl + V).
- Velg den tilføyde raden og bytt den langs sekundæraksen, som beskrevet ovenfor.
- Seriediagramtype bakgrunnsbelysning endre til kolonner (histogram).
- Vi fjerner sideklaringen i egenskapene til raden (høyreklikk på raden Belysning – Radformat – Sidegap) slik at kolonnene smelter sammen til en enkelt helhet.
- Vi fjerner grensene til kolonnene, og gjør fyllingen gjennomskinnelig.
Som et resultat får vi et så fint høydepunkt av de beste produktene:
PS
Fra og med Excel 2016 er Pareto-diagrammet lagt til standardsettet med Excel-diagrammer. Nå, for å bygge den, velg bare rekkevidden og på fanen innfelt (Sett inn) velg riktig type:
Ett klikk – og diagrammet er klart:
- Hvordan bygge en rapport ved hjelp av en pivottabell
- Sett opp beregninger i pivottabeller
- Hva er nytt i diagrammer i Excel 2013
- Wikipedia-artikkel om Paretos lov