Excel a gyakorlatban VI. – ABC elemzés

Mai írásunkban bemutatjuk, hogy miként lehet felhasználni a megtanult Excel technikákat, FKERES függvényeket, valamint relatív és abszolút hivatkozásokat a gyakorlatban. ABC elemzés segítségével kategorizáljuk termékeinket, közben pedig érintjük a tanult módszereket.

ABC elemzés

Az egyes vállalatokra jellemző termékszortiment több ezer eltérő cikket is magába foglalhat. Ezekről mind tudjuk, hogy vannak a logisztikai folyamatok szempontjából nélkülözhetetlen termékek és vannak benne olyanok is, amelyekről már rég meg is feledkeztünk. Tehát az egyes termékek szortimenten belüli relatív súlya eltérő, célszerű lenne valamilyen módszer segítségével kategorizálni azokat.

Az ABC elemzés (pareto elemzés) során a forgalmi vagy anyagfelhasználási adatok felhasználásával kategóriákba soroljuk az említett termékeket, mely csoportosítás később alapjául szolgálhat egy megfelelő beszerzési stratégia megválasztásának. Az értékelés vagy a naturáliában vagy az értékben kifejezett forgalmi adatokra épül, de azok kombinációja, együttes értelmezése is lehetséges.

Példa

Végezzük el az ABC elemzést a jelenlegi bejegyzéshez csatolt adatokra, a T0001…T0017 termékek januári [db]-ban kifejezett forgalmi adataira!

Rendezés

Első lépésként rendezzük sorba a termékeket az értékesítési mennyiség oszlop szerint csökkenő sorrendbe. Ezzel azok a termékek kerülnek előre, amelyekből a legnagyobb számban volt értékesítés, tehát amelyek fontosabbnak tűnnek a vállalat számára.

1. Ehhez jelöljük ki az „A1:C18”-as tartományt,
2. kattintsunk a Kezdőlap (fül)/Szerkesztés (csoport)/Rendezés és szűrés (gomb)/Egyéni szűrés… menüpontjára (ld. bal kép),
3. a felugró ablakban pipáljuk be „Az adatok fejlécek tartalmaznak” opciót (hiszen az első sorunk, amit kijelöltünk fejléc), majd válasszuk ki „Oszlop”-nak a második oszlopot, „Sorrend”-nek pedig „A legnagyobbtól a legkisebbig” opciót (ld. jobb kép).

Kumulált forgalmi arány

Számítsuk ki, hogy az egyes termékekből értékesített mennyiség hány százalékát adja az összegzett értékesítési mennyiségnek. (Összesen 3183 db terméket értékesítettek, a T0004 termékből 418-at, így ez a termék az össz. értékesítési mennyiség 418/3183 = 13,13%-át adja.) Ezzel együtt kumuláljuk (folyamatosan gördítve összegezzük) is ezeket az értékeket. (Ha a T0004 esetén 13,13% volt az arány, T0003 esetén pedig 374/3183 = 11,75%, akkor kumulálva ezek értéke 13,13% + 17,75% = 24,88%.) Tehát egyetlen képlettel számoljuk ki a kumulált forgalmi arányt:

1. A nevezőben (tört alján) az összes értékesítés szerepel, tehát a B oszlop értékeinek szummája. Ha lehúzzuk a képletet, akkor nem szabad, hogy ezen SZUM függvény kijelölt tartománya változzon, így le kell kötni a hivatkozást: SZUM($B$2:$B$18).
2. A tört számlálójában (felül) pedig fokozatosan görgetve összegezni kell az értékesítési mennyiségeket. A D2-es cellában a tört számlálójának 418-at kell tartalmaznia, a D3-as cellában már 418+374-et, D4-esben már 418+374+336-ot és így tovább. Ehhez újfent csak egy egyszerű SZUM függvényre van szükségünk, amely – ahogy húzzuk lefele a képletet – fokozatosan megnövelt tartományt összegez. Nincs más dolgunk, mint lekötjük a $B$2:B2 hivatkozás első felét, így amikor lefelé mozog a képlet, akkor az első B2 nem változik, de a : utáni hivatkozás – relatív mivolta miatt – folyamatosan csúszik lefele ($B$2:B3, $B$2:B4 …).

Kategorizálás

Most már csak be kell sorolni az egyes termékeket A, B, illetve C kategóriába az előzőleg kiszámolt kumulált értékesítési forgalmi arányok alapján.

1. Ehhez először készítenünk kell egy segédtáblát, amit majd az FKERES függvény fog felhasználni:
   • 0,8 alatt „A” kategóriát,
   • 0,8-nál nagyobb egyenlő esetben „B” kategóriát,
   • 0,95-nél nagyobb egyenlő esetben „C” kategóriát kap a termék.
2. Az FKERES függvény megkeresi, hogy az adott termékhez tartozó érték ebben a kis táblázatban hol található, majd visszaadja a kis tábla második oszlopában szereplő betűt eredményül (ld. lenti kép).

Ezzel meghatároztuk a kategóriákat, a megoldást tartalmazó Excel fájl itt letölthető. Kilenc termék kapott „A” kategóriát, tehát az értékesítési forgalom 80%-át a termékszortiment 9/17 = 53%-a adja. Következő bejegyzésünk januárban várható, addig is

Összefoglalás

Cikk címe

ABC elemzés

Leírás

Excel blogsorozatunk idei utolsó részében egy ABC elemzés segítségével mutatjuk be az eddigiekben megtanult Excel praktikákat.

Szerző

Innolog Solutions

Kiadó

Innolog Solutions

Kiadó logó

Címkék: abszolútExcelFKERESfüggvényhivatkozássorozat