tartalom
Talán hallott már a Pareto törvényről vagy a 20/80 elvről. A 19. század végén Vilfredo Pareto olasz szociológus és közgazdász felfedezte, hogy a vagyon eloszlása a társadalomban egyenetlen és bizonyos függőségnek van kitéve: a vagyon növekedésével a gazdagok száma exponenciálisan csökken, állandó együtthatóval ( az olasz háztartások körében a jövedelem 80%-a a családok 20%-ában volt). Később ezt az ötletet Richard Koch dolgozta ki könyvében, aki az egyetemes „20/80-as elv” megfogalmazását javasolta (az erőfeszítések 20%-a az eredmény 80%-át adja). A gyakorlatban ez a törvény általában nem ilyen szép számokban fejeződik ki (olvasd el Chris Anderson „The Long Tail”-ját), hanem egyértelműen mutatja az erőforrások, nyereségek, költségek stb. egyenetlen eloszlását.
Az üzleti elemzés során gyakran egy Pareto-diagramot készítenek ennek az egyenetlenségnek az ábrázolására. Segítségével vizuálisan meg lehet mutatni például, hogy mely termékek vagy vásárlók hozzák a legtöbb profitot. Általában így néz ki:
Fő jellemzői:
- A hisztogram minden kék oszlopa a szorzat nyereségét jelenti abszolút egységekben, és a bal tengely mentén van ábrázolva.
- A narancssárga grafikon a nyereség kumulált százalékos arányát mutatja (azaz a nyereség kumulatív arányát).
- A 80%-os feltételes határon általában egy vízszintes küszöbvonalat húznak az érthetőség kedvéért. Ennek a vonalnak a metszéspontjától balra lévő összes áru a felhalmozott nyereség grafikonjával hozza a pénz 80%-át, az összes áru jobbra – a maradék 20%.
Nézzük meg, hogyan készíthet önállóan Pareto-diagramot a Microsoft Excel programban.
1. lehetőség. Egy egyszerű Pareto-diagram, amely kész adatokon alapul
Ha a forrásadatok hasonló táblázat formájában (vagyis már kész formában) érkeztek hozzád:
… akkor a következőket tesszük.
Rendezze a táblázatot a nyereség csökkenő sorrendjében (tab Adatok – Rendezés), és adjon hozzá egy oszlopot a felhalmozott nyereség százalékos kiszámításához szükséges képlettel:
Ez a képlet elosztja a teljes felhalmozott nyereséget a lista elejétől az aktuális tételig a teljes tábla teljes nyereségével. Egy 80%-os konstans oszlopot is hozzáadunk, hogy egy vízszintes küszöbértéket szaggatott vonalat hozzunk létre a jövőbeli diagramon:
Kijelöljük az összes adatot, és felállítunk egy szabályos hisztogramot a lapon Beszúrás – Hisztogram (Beszúrás – Oszlopdiagram). Valahogy így kell kinéznie:
A kapott diagram százalékos sorozatát a másodlagos (jobb) tengely mentén kell elküldeni. Ehhez ki kell jelölni a sorokat az egérrel, de ez nehéz lehet, mivel a nagy profitoszlopok hátterében nehezen láthatóak. Ezért jobb, ha a lapon található legördülő listát használja a kiemeléshez elrendezés or kialakított:
Ezután kattintson a jobb gombbal a kiválasztott sorra, és válassza ki a parancsot Formátum adatsor és a megjelenő ablakban válassza ki a lehetőséget A másodlagos tengelyen (másodlagos tengely). Ennek eredményeként a diagramunk így fog kinézni:
A Felhalmozott nyereségrészesedés és a Küszöb sorozat esetén a diagram típusát oszlopokról sorokra kell módosítania. Ehhez kattintson ezekre a sorokra, és válassza ki a parancsot A sorozatdiagram típusának módosítása.
Már csak a Küszöb vízszintes sort kell kiválasztani, és úgy formázni, hogy az adatok helyett levágási vonalnak tűnjön (azaz távolítsa el a jelölőket, tegye a szaggatott vonalat pirosra stb.). Mindez úgy tehető meg, hogy jobb gombbal kattintunk a sorra, és kiválasztjuk a parancsot Formátum adatsor. A diagram most felveszi végleges formáját:
Eszerint arra a következtetésre juthatunk, hogy a haszon 80%-át az első 5 áru hozza, és a krumplitól jobbra lévő összes többi áru csak a haszon 20%-át teszi ki.
Az Excel 2013-ban ezt még könnyebben megteheti – az új, beépített kombinált diagramtípust azonnal használja az ábrázolás során:
2. lehetőség: PivotTable és Pivot Pareto diagram
Mi a teendő, ha nincs kész adat az építkezéshez, csak az eredeti nyers információ? Tegyük fel, hogy az elején van egy táblázatunk az alábbi értékesítési adatokkal:
Ahhoz, hogy egy Pareto-diagramot építsen rá, és megtudja, mely termékek fogynak a legjobban, először elemeznie kell a forrásadatokat. Ennek legegyszerűbb módja egy pivot tábla. Jelölje ki a forrástábla bármelyik celláját, és használja a parancsot Beszúrás – Kimutatás (Insert – Pivot Table). A megjelenő köztes ablakban ne változtasson semmit, és kattintson OK, majd a jobb oldalon megjelenő panelen húzza át a forrásadat-mezőket felülről a jövőbeli kimutatástábla elrendezésének alsó területére:
Az eredmény egy összefoglaló táblázat az egyes termékek teljes bevételével:
Rendezze a bevétel csökkenő sorrendjében úgy, hogy az aktív cellát az oszlopra állítja Összeg a Bevétel mezőben és a rendezés gomb segítségével От Я до А (Z-től A-ig) lap dátum.
Most hozzá kell adnunk egy számított oszlopot a felhalmozott kamatbevételekkel. Ehhez húzza újra a mezőt Jövedelem a területre Értékek a jobb oldali ablaktáblában, hogy duplikált oszlopot kapjon a pivotban. Ezután kattintson a jobb gombbal a klónozott oszlopra, és válassza a parancsot Kiegészítő számítások – a mezőben futó összesítés %-a (Adatok megjelenítése mint – Befutási összmennyiség %-a). A megjelenő ablakban válassza ki a mezőt Név, amelyen felülről lefelé halmozódik fel a bevétel százalékos aránya. A kimenetnek így kell kinéznie a következő táblázatban:
Mint látható, ez szinte egy kész táblázat a cikk első részéből. A teljes boldogsághoz csak egy 80%-os küszöbértékkel rendelkező oszlop hiányzik egy jövőbeli diagramban egy vágási vonal felépítéséhez. Egy ilyen oszlop könnyen hozzáadható egy számított mező segítségével. Jelöljön ki egy számot az összefoglalóban, majd kattintson a fülre Kezdőlap – Beszúrás – Számított mező (Kezdőlap – Beszúrás – Számított mező). A megnyíló ablakban írja be a mező nevét és képletét (esetünkben egy állandót):
Miután rákattintott OK egy harmadik oszlop kerül a táblázatba 80%-os értékkel minden cellában, és végül a szükséges formát ölti. Ezután használhatja a parancsot Pivot diagram (Pivot diagram) lap paraméterek (Lehetőségek) or Elemzés (Elemzés) és állítsa be a diagramot pontosan ugyanúgy, mint az első opciónál:
A legfontosabb termékek kiemelése
A leginkább befolyásoló tényezők kiemelésére a narancssárga halmozott kamatgörbe metszéspontjától balra elhelyezkedő oszlopok kiemelhetők a 80%-os vízszintes határvonallal. Ehhez egy másik oszlopot kell hozzáadnia a táblázathoz a következő képlettel:
Ez a képlet 1-et ad ki, ha a szorzat a metszésponttól balra van, és 0-t, ha jobbra. Ezután a következőket kell tennie:
- Új oszlopot adunk a diagramhoz – ezt legegyszerűbben egyszerű másolással, azaz oszlopkiemeléssel tudjuk megtenni háttérvilágítás, másold ki (Ctrl + C), válassza ki a diagramot és szúrja be (Ctrl + V).
- Válassza ki a hozzáadott sort, és váltsa át a másodlagos tengely mentén a fent leírtak szerint.
- Sorozatdiagram típusa háttérvilágítás váltás oszlopokra (hisztogram).
- A sor tulajdonságainál eltávolítjuk az oldalsó hézagot (jobb gombbal kattintson a sorra Megvilágítás – Sorformátum – Oldalrés), így az oszlopok egyetlen egésszé olvadnak össze.
- Eltávolítjuk az oszlopok szegélyét, és áttetszővé tesszük a kitöltést.
Ennek eredményeként ilyen szép kiemelést kapunk a legjobb termékek közül:
PS
Az Excel 2016-tól kezdve a Pareto-diagram hozzáadásra került az Excel-diagramok standard készletéhez. Most, hogy felépíteni, csak válassza ki a tartományt, és a lapon betétlap (Beszúrás) válassza ki a megfelelő típust:
Egy kattintás – és kész is a diagram:
- Hogyan készítsünk jelentést pivot tábla segítségével
- Számítások beállítása a kimutatásokban
- Az Excel 2013 diagramjainak újdonságai
- Wikipédia-cikk Pareto törvényéről