Innehåll
Du kanske har hört talas om Pareto-lagen eller 20/80-principen. I slutet av 19-talet upptäckte den italienske sociologen och ekonomen Vilfredo Pareto att fördelningen av välstånd i samhället är ojämn och föremål för ett visst beroende: med en ökning av välståndet minskar antalet rika människor exponentiellt med en konstant koefficient ( bland italienska hushåll fanns 80 % av inkomsterna i 20 % av familjerna). Senare utvecklades denna idé i sin bok av Richard Koch, som föreslog formuleringen av den universella "Princip 20/80" (20% av ansträngningarna ger 80% av resultatet). I praktiken uttrycks denna lag vanligtvis inte i så vackra siffror (läs "The Long Tail" av Chris Anderson), men visar tydligt den ojämna fördelningen av resurser, vinster, kostnader etc.
I affärsanalys byggs ofta ett Pareto-diagram för att representera denna ojämnhet. Den kan användas för att visuellt visa till exempel vilka produkter eller kunder som ger mest vinst. Det brukar se ut så här:
Dess huvudfunktioner:
- Varje blå kolumn i histogrammet representerar vinsten för produkten i absoluta enheter och plottas längs den vänstra axeln.
- Den orangea grafen representerar den kumulativa procentandelen av vinsten (dvs. andelen av vinsten på kumulativ basis).
- På en villkorlig gräns på 80 % dras vanligtvis en horisontell tröskellinje för tydlighetens skull. Alla varor till vänster om skärningspunkten för denna linje med grafen för ackumulerad vinst ger oss 80% av pengarna, alla varor till höger - de återstående 20%.
Låt oss se hur du bygger ett Pareto-diagram i Microsoft Excel på egen hand.
Alternativ 1. Ett enkelt Pareto-diagram baserat på färdiga data
Om källdata kom till dig i form av en liknande tabell (det vill säga redan i färdig form):
… då gör vi följande.
Sortera tabellen i fallande vinstordning (tab Data – Sortering) och lägg till en kolumn med formeln för att beräkna den ackumulerade procentandelen av vinsten:
Denna formel dividerar den totala ackumulerade vinsten från början av listan till den aktuella posten med den totala vinsten för hela tabellen. Vi lägger också till en kolumn med en konstant på 80 % för att skapa en streckad linje för horisontell tröskel i det framtida diagrammet:
Vi väljer alla data och bygger ett vanligt histogram på fliken Infoga – Histogram (Infoga – Kolumndiagram). Det borde se ut ungefär så här:
Procentserien i det resulterande diagrammet ska skickas längs den sekundära (höger) axeln. För att göra detta måste du markera raderna med musen, men det kan vara svårt, eftersom de är svåra att se mot bakgrund av stora vinstkolumner. Så det är bättre att använda rullgardinsmenyn på fliken för att markera Layout or bildad:
Högerklicka sedan på den valda raden och välj kommandot Formatera dataserier och välj alternativet i fönstret som visas På sekundäraxeln (sekundäraxel). Som ett resultat kommer vårt diagram att se ut så här:
För serierna Ackumulerad vinstandel och tröskel måste du ändra diagramtypen från kolumner till rader. För att göra detta, klicka på var och en av dessa rader och välj kommandot Ändra typ av seriekarta.
Allt som återstår är att välja den horisontella tröskelraden och formatera den så att den ser ut som en cutoff-linje snarare än data (dvs. ta bort markörerna, göra linjen streckad röd, etc.). Allt detta kan göras genom att högerklicka på raden och välja kommandot Formatera dataserier. Nu kommer diagrammet att ta sin slutgiltiga form:
Enligt den kan vi dra slutsatsen att 80 % av vinsten kommer från de första 5 varorna, och alla andra varor till höger om potatisen står för endast 20 % av vinsten.
I Excel 2013 kan du göra det ännu enklare – använd den nya inbyggda kombinationsdiagramtypen omedelbart när du plottar:
Alternativ 2: Pivottabell och Pivot Pareto-diagram
Vad ska man göra om det inte finns några färdiga data för konstruktion, utan bara den ursprungliga råinformationen? Låt oss anta att vi i början har en tabell med försäljningsdata så här:
För att bygga ett Pareto-diagram på det och ta reda på vilka produkter som säljer bäst, måste du först analysera källdata. Det enklaste sättet att göra detta är med en pivottabell. Markera valfri cell i källtabellen och använd kommandot Infoga – Pivottabell (Infoga – Pivottabell). I det mellanliggande fönstret som visas, ändra ingenting och klicka OK, och sedan i panelen som visas till höger, dra källdatafälten från de övre till de nedre områdena i layouten för den framtida pivottabellen:
Resultatet bör vara en sammanfattningstabell med de totala intäkterna för varje produkt:
Sortera den i fallande inkomstordning genom att ställa in den aktiva cellen i kolumnen Belopp i fältet Intäkt och med hjälp av sorteringsknappen От Я до А (Från Ö till A) fliken Data.
Nu behöver vi lägga till en beräknad kolumn med ackumulerade ränteintäkter. För att göra detta, dra fältet igen Omsättning till området Värden i den högra rutan för att få en dubblettkolumn i pivoten. Högerklicka sedan på den klonade kolumnen och välj kommando Ytterligare beräkningar – % av den löpande summan i fältet (Visa data som – % löpande total in). Välj fältet i fönstret som visas Namn , där procentandelen av intäkterna kommer att ackumuleras från topp till botten. Utdata ska se ut så här:
Som du kan se är detta nästan ett färdigt bord från den första delen av artikeln. Det saknas bara för fullständig lycka en kolumn med ett tröskelvärde på 80% för att konstruera en gränslinje i ett framtida diagram. En sådan kolumn kan enkelt läggas till med hjälp av ett beräknat fält. Markera valfritt nummer i sammanfattningen och klicka sedan på fliken Hem – Infoga – Beräknat fält (Hem – Infoga – Beräknat fält). I fönstret som öppnas anger du fältnamnet och dess formel (i vårt fall en konstant):
Efter att ha klickat på OK en tredje kolumn kommer att läggas till i tabellen med ett värde på 80 % i alla celler, och den kommer till slut att anta den form som krävs. Sedan kan du använda kommandot Pivotdiagram (pivotdiagram) fliken parametrar (Alternativ) or Analys (Analys) och ställ in diagrammet på exakt samma sätt som det första alternativet:
Att lyfta fram nyckelprodukter
För att markera de mest påverkande faktorerna, dvs kolumner som är placerade till vänster om skärningspunkten för den orange ackumulerade räntekurvan med den horisontella cutoff-linjen på 80 % kan markeras. För att göra detta måste du lägga till ytterligare en kolumn i tabellen med formeln:
Denna formel ger 1 om produkten är till vänster om skärningspunkten och 0 om den är till höger. Då måste du göra följande:
- Vi lägger till en ny kolumn i diagrammet – det enklaste sättet att göra detta är genom att enkelt kopiera, dvs markera kolumn bakgrundsbelysning, kopiera det (Ctrl + C), välj diagrammet och infoga (Ctrl + V).
- Välj den tillagda raden och växla den längs den sekundära axeln, enligt beskrivningen ovan.
- Seriediagramtyp bakgrundsbelysning byt till kolumner (histogram).
- Vi tar bort sidoavståndet i radens egenskaper (högerklicka på raden Belysning – Radformat – Side Gap) så att kolumnerna smälter samman till en enda helhet.
- Vi tar bort gränserna för kolumnerna och gör fyllningen genomskinlig.
Som ett resultat får vi en så fin höjdpunkt av de bästa produkterna:
PS
Från och med Excel 2016 har Pareto-diagrammet lagts till i standarduppsättningen av Excel-diagram. Nu, för att bygga det, välj bara intervallet och på fliken Insert (Föra in) välj lämplig typ:
Ett klick – och diagrammet är klart:
- Hur man bygger en rapport med en pivottabell
- Ställ in beräkningar i pivottabeller
- Vad är nytt i diagram i Excel 2013
- Wikipedia-artikel om Paretos lag