Sisu
Võib-olla olete kuulnud Pareto seadusest või 20/80 põhimõttest. 19. sajandi lõpus avastas itaalia sotsioloog ja majandusteadlane Vilfredo Pareto, et rikkuse jaotus ühiskonnas on ebaühtlane ja allub teatud sõltuvusele: jõukuse suurenemisega väheneb rikaste inimeste arv plahvatuslikult konstantse koefitsiendiga ( Itaalia leibkondade hulgas oli 80% sissetulekust 20% peredest). Hiljem arendas seda ideed oma raamatus Richard Koch, kes pakkus välja universaalse “Põhimõte 20/80” sõnastuse (20% jõupingutustest annab 80% tulemusest). Praktikas ei väljendu see seadus tavaliselt nii ilusates numbrites (loe Chris Andersoni “The Long Tail”), vaid näitab selgelt ressursside, kasumite, kulude jms ebaühtlast jaotumist.
Ärianalüüsis koostatakse sageli Pareto diagramm, mis kajastab seda ebaühtlust. Selle abil saab näiteks visuaalselt näidata, millised tooted või kliendid toovad kõige rohkem kasumit. Tavaliselt näeb see välja selline:
Selle peamised omadused:
- Histogrammi iga sinine veerg tähistab toote kasumit absoluutühikutes ja on kantud piki vasakut telge.
- Oranž graafik kujutab kasumi kumulatiivset protsenti (st kasumi osakaalu kumulatiivsel alusel).
- Tingimuslikul piiril 80% tõmmatakse tavaliselt selguse huvides horisontaalne lävijoon. Kõik selle sirge ja kogunenud kasumi graafiku lõikepunktist vasakul olevad kaubad toovad meile 80% rahast, kõik paremal olevad kaubad – ülejäänud 20%.
Vaatame, kuidas iseseisvalt Microsoft Excelis Pareto diagrammi koostada.
Variant 1. Lihtne Pareto diagramm, mis põhineb valmisandmetel
Kui lähteandmed jõudsid teile sarnase tabeli kujul (st juba valmis kujul):
… siis teeme järgmist.
Sorteeri tabel kasumi kahanevas järjekorras (tab Andmed – sorteerimine) ja lisage veerg kogunenud kasumiprotsendi arvutamise valemiga:
See valem jagab kogu akumuleeritud kasumi loendi algusest praeguse üksuseni kogu tabeli kogukasumiga. Lisame ka 80% konstandiga veeru, et luua tulevases diagrammis horisontaalne läve katkendjoon:
Valime kõik andmed ja koostame vahekaardile tavalise histogrammi Sisesta – histogramm (sisesta – veergdiagramm). See peaks välja nägema umbes selline:
Saadud diagrammi protsendiseeria tuleks saata piki teisest (paremat) telge. Selleks peate valima hiirega read, kuid see võib olla keeruline, kuna suurte kasumiveergude taustal on neid raske näha. Seega on parem esiletõstmiseks kasutada vahekaardil olevat ripploendit Layout or vorming:
Seejärel paremklõpsake valitud real ja valige käsk Andmeseeria vormindamine ja valige kuvatavas aknas suvand Sekundaarsel teljel (sekundaarne telg). Selle tulemusena näeb meie diagramm välja järgmine:
Sarjade Akumuleeritud kasumi osakaal ja Lävi puhul peate diagrammi tüübi muutma veergudest ridadele. Selleks klõpsake kõigil neil ridadel ja valige käsk Muutke seeriadiagrammi tüüpi.
Jääb vaid valida horisontaalne rida Threshold ja vormindada see nii, et see näeks välja pigem lõikejoon kui andmed (st eemaldage markerid, muutke kriipsjoon punaseks jne). Kõike seda saab teha paremklõpsates real ja valides käsu Andmeseeria vormindamine. Nüüd võtab diagramm lõpliku kuju:
Selle järgi võime järeldada, et 80% kasumist toovad esimesed 5 kaupa ja kõik muud kartulist paremale jäävad kaubad moodustavad vaid 20% kasumist.
Programmis Excel 2013 saate seda teha veelgi lihtsamalt – kasutage joonistamisel kohe uut sisseehitatud liitdiagrammi tüüpi:
2. valik: PivotTable ja Pivot Pareto diagramm
Mida teha, kui ehitamiseks pole valmisandmeid, vaid on ainult algne alginfo? Oletame, et alguses on meil selline müügiandmetega tabel:
Sellele Pareto diagrammi koostamiseks ja kõige paremini müüvate toodete väljaselgitamiseks peate esmalt analüüsima lähteandmeid. Lihtsaim viis seda teha on pöördetabeli abil. Valige lähtetabelis suvaline lahter ja kasutage käsku Sisestamine – Pivot Table (Sisestamine – Pivot Table). Ilmuvas vaheaknas ärge muutke midagi ja klõpsake OK, seejärel lohistage paremal ilmuval paneelil lähteandmete väljad tulevase liigendtabeli paigutuse ülaosast alaossa.
Tulemuseks peaks olema kokkuvõtlik tabel iga toote kogutuluga:
Sorteerige see tulude kahanevas järjekorras, määrates aktiivse lahtri veergu Summa väljal Tulu ja kasutades sortimisnuppu От Я до А (Z-st A-ni) tab kuupäev.
Nüüd peame lisama arvutatud veeru kogunenud intressituludega. Selleks lohistage välja uuesti Tulu piirkonda Väärtused paremal paanil, et saada pivoti duplikaatveerg. Seejärel paremklõpsake kloonitud veerul ja valige käsk Täiendavad arvutused – % jooksva kogusummast väljal (näita andmeid kui – % jooksvat kogusummat). Ilmuvas aknas valige väli Nimi, millele koguneb tulu protsent ülalt alla. Väljund peaks välja nägema selline tabel:
Nagu näete, on see peaaegu valmis tabel artikli esimesest osast. Täielikuks õnneks puudub vaid veerg, mille läviväärtus on 80%, et rajada tulevases diagrammis piirjoon. Sellist veergu saab hõlpsasti lisada arvutatud välja abil. Tõstke kokkuvõttes esile suvaline number ja seejärel klõpsake vahekaarti Avaleht – Sisesta – Arvutatud väli (Kodu – Sisesta – Arvutatud väli). Sisestage avanevas aknas välja nimi ja selle valem (meie puhul konstant):
Pärast klõpsamist nupul OK tabelisse lisatakse kolmas veerg väärtusega 80% kõigis lahtrites ja see võtab lõpuks vajaliku kuju. Seejärel saate kasutada käsku Pivot diagramm (Pöörddiagramm) tab parameetrid (Valikud) or analüüs (Analüüs) ja seadistage diagramm täpselt samamoodi nagu esimene valik:
Võtmetoodete esiletõstmine
Enim mõjutavate tegurite esiletõstmiseks võib esile tuua veerud, mis asuvad oranži akumuleeritud intressikõvera lõikepunktist vasakul, mille horisontaalne piirjoon on 80%. Selleks peate tabelisse lisama järgmise veeru järgmise valemiga:
See valem väljastab 1, kui korrutis asub ristumispunktist vasakul, ja 0, kui see asub paremal. Seejärel peate tegema järgmist.
- Lisame diagrammile uue veeru – kõige lihtsam on seda teha lihtsa kopeerimise ehk veergu esiletõstmise teel taustvalgus, kopeeri see (Ctrl + C), valige diagramm ja sisestage (Ctrl + V).
- Valige lisatud rida ja lülitage see mööda sekundaarset telge, nagu ülalpool kirjeldatud.
- Sarja diagrammi tüüp taustvalgus muuta veergudeks (histogramm).
- Eemaldame rea atribuutidest küljevahe (paremklõpsake real Valgustus – reavorming – küljevahe), et veerud ühineksid üheks tervikuks.
- Eemaldame veergude äärised ja muudame täidise poolläbipaistvaks.
Selle tulemusel saame parimatest toodetest nii kena esiletõstmise:
PS
Alates Excel 2016-st on Exceli diagrammide standardkomplekti lisatud Pareto diagramm. Nüüd selle koostamiseks valige lihtsalt vahemik ja vahekaardilt Sisesta (Sisesta) vali sobiv tüüp:
Üks klõps – ja diagramm on valmis:
- Kuidas koostada aruannet liigendtabeli abil
- Seadistage PivotTable-liigendtabelites arvutused
- Mis on uut Excel 2013 diagrammides?
- Wikipedia artikkel Pareto seadusest