Primjena ABC analize u Microsoft Excelu

Jedna od ključnih metoda upravljanja i logistike je ABC-analiza. Pomoću nje možete klasificirati resurse poduzeća, proizvoda, kupaca itd. po stupnju važnosti. Istodobno, prema razini važnosti, svaku od gore navedenih jedinica dodjeljuje se jedna od tri kategorije: A, B ili C. Excel ima svoje alate za prtljagu koji olakšavaju provedbu takve analize. Let's shvatiti kako ih koristiti, i što je ABC analiza.

Upotreba ABC analize

ABC-analiza je neka vrsta poboljšana i prilagođena suvremenim uvjetima varijanta Pareto principa. Prema metodologiji njenog ponašanja, svi elementi analize podijeljeni su u tri kategorije po značaju:

• Kategorija A - elementi koji u ukupnom sastavu imaju više od 80% specifične težine;
• Kategorija B - elementi čiji je agregat od 5% do 15% specifične težine;
• Kategorija C - preostali elementi, ukupni agregat od 5% i manji od specifične težine.

Neke tvrtke primjenjuju naprednije tehnike i razbiju elemente koji nisu u 3, već u 4 ili 5 skupina, ali ćemo se oslanjati na klasičnu shemu ABC analize.

Metoda 1: analiza sortiranjem

U programu Excel ABC analiza izvodi se sortiranjem. Svi elementi su razvrstani od većih do manjih. Zatim se izračunava kumulativna specifična težina svakog elementa, na temelju koje se dodjeljuje određena kategorija. Naučimo konkretan primjer kako se ova tehnika primjenjuje u praksi.

Imamo tablicu s popisom dobara koju tvrtka prodaje, a odgovarajući iznos prihoda od njihove prodaje za određeni vremenski period. Na dnu tablice ostvaruje se ukupni prihod za sve nazive proizvoda. Vrijedno je zadaću, koristeći ABC-analizu, razbiti te robe u skupine po njihovoj važnosti za poduzeće.

1. Odaberite tablicu s danim pokazivačem, držite lijevu tipku miša, isključujući zaglavlje i završnu liniju. Idite na karticu "Podaci" . Kliknite gumb "Sortiraj" koji se nalazi na vrpci "Alati i filtriraj" .

   Također možete postupati drugačije. Odaberite gore navedeni raspon tablice, a zatim prijeđite na karticu "Dom" i kliknite gumb "Sortiraj i filtriraj" koji se nalazi u okviru alata "Uredi" na vrpci. Popis je aktiviran u kojem odabire stavku "Prilagođena vrsta" .

2. Kada primijenite bilo koju od gore navedenih radnji, pokrećete se prozor za sortiranje. Pogledajmo da je označena opcija "Moji podaci sadrže zaglavlja" . U slučaju njegove odsutnosti, mi ustanovljavamo.

   U polju "Stupac" naznačujemo naziv stupca koji sadrži podatke o prihodu.

   U polju "Razvrstavanje" morate navesti određene kriterije za sortiranje. Ostavljamo prethodno postavljene postavke - "Vrijednosti" .

   U polju "Narudžba" postavili smo poziciju "silazno" .

   Nakon izrade navedenih postavki kliknite gumb "U redu" pri dnu prozora.

3. Nakon što je izvršena određena radnja, svi su elementi sortirani po prihodima od najvećih do manjih.
4. Sada moramo izračunati specifičnu težinu svakog od elemenata za ukupni zbroj. Izrađujemo dodatni stupac u ove svrhe, koji ćemo zvati "Specific weight" . U prvoj ćeliji ovog stupca stavili smo znak "=" , nakon čega naznačavamo vezu na ćeliju u kojoj se nalazi iznos prihoda od prodaje odgovarajućeg proizvoda. Zatim postavimo znak podjele ( "/" ). Nakon toga, navedite koordinate ćelije, koja sadrži ukupnu količinu prodaje robe u cijelom poduzeću.

   Uzevši u obzir činjenicu da ćemo ovu formulu kopirati na druge ćelije u stupcu "Specific weight" pomoću oznake za popunjavanje, trebamo utvrditi adresu veze na element koji sadrži ukupni iznos prihoda od strane poduzeća. Zbog toga smo referentni apsolutni. Odaberite koordinate navedene ćelije u formuli i pritisnite F4 . Prije nego što koordinate, kao što vidimo, pojavio se znak dolara, što znači da je veza postala apsolutna. Treba napomenuti da bi upućivanje na prihod prve stavke na popisu ( roba 3 ) trebalo ostati relativno.

   Zatim, za izračunavanje, pritisnite tipku Enter .

5. Kao što vidite, udio prihoda od prvog proizvoda navedenog na popisu prikazan je u ciljanoj ćeliji. Da biste kopirali formulu u donji raspon, stavite pokazivač u donji desni kut ćelije. Pretvara se u oznaku za ispunu koja izgleda kao mali križ. Pritisnite lijevu tipku miša i povucite oznaku za ispunjavanje do kraja stupca.
6. Kao što vidite, cijeli je stup ispunjen podacima koji karakteriziraju specifičnu težinu prihoda od prodaje svakog proizvoda. No, vrijednost specifične težine prikazana je u numeričkom formatu, a mi ga moramo pretvoriti u postotak. Da biste to učinili, odaberite sadržaj stupca "Specific weight" . Zatim prijeđite na karticu Početna . Na vrpci u grupi postavki "Broj" nalazi se polje koje prikazuje format podataka. Prema zadanim postavkama, ako niste izvršili dodatne manipulacije, trebao bi biti postavljen format "Općenito" . Kliknemo na ikonu u obliku trokuta koji se nalazi desno od ovog polja. U otvorenom popisu formata odaberite stavku "Interesi" .
7. Kao što možete vidjeti, sve vrijednosti stupca pretvorene su u postotne vrijednosti. Kao što se očekivalo, linija "Ukupno" označava 100% . Očekuje se da će specifična težina robe biti u stupcu od najvećeg do manjeg.
8. Sada trebamo izraditi stupac u kojem se prikazuje zbirni udio s ukupnim zbrojem. To jest, u svakoj liniji, specifična težina svih onih dobara koja se nalaze na gore navedenom popisu bit će dodana pojedinačnoj specifičnoj težini određene robe. Za prvi proizvod na popisu ( točka 3 ), pojedinačni udio i akumulirani udio bit će jednaki, ali za sve nakon pojedinog pokazatelja bit će potrebno dodati akumulirani udio prethodnog elementa popisa.

   Dakle, u prvom retku prenesite stupac "Akumulirani udio" indikator iz stupca "Specific weight" .

9. Zatim stavljamo pokazivač u drugu ćeliju stupca "akumulirani udio" . Ovdje moramo primijeniti formulu. Stavili smo znak "jednak" i dodali sadržaj ćelije "Specific weight" u istom retku i sadržaj ćelije "Akumulirani udio" iz gornje linije. Sve reference ostaju relativne, tj. Ne manipuliramo njima. Nakon toga kliknite na gumb Enter da biste prikazali konačni rezultat.
10. Sada morate kopirati ovu formulu u ćelije ovog stupca, koji su smješteni ispod. Da biste to učinili, primijenit ćemo oznaku za ispunjavanje, na koji smo već primili pri kopiranju formule u stupac "Specific weight" . U tom slučaju, crta "Total" nije potrebna za snimanje, jer će akumulirani rezultat od 100% biti prikazan na posljednjem proizvodu s popisa. Kao što možete vidjeti, svi elementi našeg stupca bili su ispunjeni nakon toga.
11. Nakon toga stvorite stupac "Grupa" . Morat ćemo grupirati robu po kategorijama A , B i C prema akumuliranom udjelu. Kao što se sjećamo, svi su elementi grupirani prema sljedećoj shemi:
    • A - do 80% ;
    • B - sljedećih 15% ;
    • C - preostalih 5% .

    Dakle, kategoriju A dodjeljujemo svim dobrima, akumulirani udio specifične težine koja prelazi granicu na 80% . Robe s akumuliranom specifičnom težinom od 80% do 95% dodjeljuju se kategorija B. Preostalu skupinu robe s vrijednosti više od 95% akumulirane specifične težine dodjeljuje se kategorija C.

12. Radi jasnoće ove skupine možete ispuniti različitim bojama. Ali ovo je po volji.

Tako smo podijelili elemente u skupine prema razini važnosti, koristeći ABC analizu. Korištenje nekih drugih tehnika, kao što je gore spomenuto, primjenjuje particioniranje na veći broj skupina, ali načelo razdiobe ostaje praktički nepromijenjeno.

Pouka: Razvrstavanje i filtriranje u Excelu

Metoda 2: Korištenje složene formule

Naravno, upotreba sortiranja je najčešći način izrade ABC analize u Excelu. No, u nekim je slučajevima potrebna analiza bez mijenjanja redaka na mjestima u izvornoj tablici. U ovom slučaju, složena formula dolazi do spašavanja. Na primjer, koristit ćemo istu izvornu tablicu kao u prvom slučaju.

1. Dodamo u početnu tablicu, koja sadrži naziv robe i prihod od prodaje svakog od njih, stupac "Grupa" . Kao što vidite, u ovom slučaju ne možemo dodati stupce izračunom pojedinačnih i akumulativnih dionica.
2. Odaberite prvu ćeliju u stupcu "Grupa" , a zatim kliknite gumb "Umetni funkciju" pored trake s formulama.
3. Pokreće se Čarobnjak za funkcije . Prelazimo u kategoriju "Reference i polja" . Odaberite funkciju "SELECT" . Kliknite gumb "U redu" .
4. Aktiviran je argumentni prozor SELECT funkcije. Sintaksa je sljedeća:

   =ВЫБОР(Номер_индекса;Значение1;Значение2;…)

   Zadatak ove funkcije je izlazak jedne od navedenih vrijednosti, ovisno o broju indeksa. Broj vrijednosti može doseći 254, ali trebamo samo tri imena koja odgovaraju kategorijama ABC-analize: A , B , C. Na polju "Value3" - "C" možemo odmah unijeti u polje "Value1" simbol "A" u polju "Value2" - "B " .

5. Ali s argumentom "Indeksni broj" morat će se temeljito prevariti, budući da je u njemu izgrađen nekoliko dodatnih operatera. Postavite pokazivač u polje "Indeksni broj" . Zatim kliknite ikonu koja izgleda kao trokut, s lijeve strane gumba "Umetni funkciju" . Prikazan je popis nedavno korištenih operatora. Trebamo funkciju MATCH . Budući da popis ne, kliknite na natpis "Ostale funkcije ..." .
6. Čarobnjak prozora funkcija ponovno je pokrenut. Opet, idite na kategoriju "Reference i polja". Tamo smo pronašli položaj "SEARCH" , odaberite ga i kliknite na gumb "OK" .
7. Otvara prozor OPERATOROVA argumenata. Sintaksa je sljedeća:

   =ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)

   Svrha ove funkcije je odrediti broj mjesta navedenog elementa. To je upravo ono što nam je potrebno za polje "Index broj" u funkciji SELECT .

   U polju "Skenirani niz" odmah možete postaviti sljedeći izraz:

   {0:0,8:0,95}

   Mora biti u kovrčavim zagradama, poput formule polja. Nije teško pogoditi da ovi brojevi ( 0 , 0,8 , 0,95 ) označavaju granice akumuliranog udjela između skupina.

   Polje "Podudaranje" nije obavezno i ​​u ovom slučaju ga nećemo ispuniti.

   Na polju "Vrijednost pretraživanja" postavili smo pokazivač. Onda opet preko trokuta na slici gore, mi se preselimo u Čarobnjak za funkcije .

8. Ovaj put u čarobnjaku za funkciju prelazimo u kategoriju "Matematički" . Odaberemo naziv "LJETO" i kliknite na gumb "OK" .
9. Pokreće se prozor argumenata SUMIFER funkcije. Navedeni operator sažima stanice koje zadovoljavaju navedeno stanje. Njegova sintaksa je ovo:

   =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

   U polju "Raspon" unesite adresu stupca "Prihod" . U tu svrhu postavljamo pokazivač u polje, a zatim, nakon što ste stisnuli lijevu tipku miša, odaberite sve ćelije odgovarajućeg stupca, isključujući vrijednost "Total" . Kao što možete vidjeti, adresa je odmah bila prikazana u polju. Osim toga, moramo ovu vezu učiniti apsolutnim. Da biste to učinili, izvršite odabir i kliknite tipku F4 . Adresa se istaknula dolarskim znakovima.

   U polju "Kriteriji" moramo navesti uvjet. Unosimo sljedeći izraz:

   ">"&

   Tada odmah nakon toga unosimo adresu prve ćelije u stupac "Prihod" . Horizontalne koordinate u toj adresi čine apsolutno, dodajući znak dolara s tipkovnice prije pisma. Koordinate na okomici ostaju relativno, tj. Prije lik ne treba biti nikakvih znakova.

   Nakon toga nemojte kliknuti gumb "OK" , ali kliknite naziv funkcije MATCH u traci s formulama.

10. Zatim se vraćamo u prozor argumenata funkcije MATCH . Kao što možete vidjeti, u polju "Vrijednost pretraživanja" pojavljuju se podaci koje je unio operator SUMESELI . Ali to nije sve. Idite na ovo polje i dodajte znak "+" bez navodnika postojećih podataka. Zatim unosimo adresu prve ćelije u stupac "Prihod" . I opet vodimo horizontalne koordinate ovog referentnog apsolutnog, a okomito ostavimo relativno.

    Dalje, u zagradama uzimamo cijeli sadržaj polja "Željena vrijednost" , nakon čega stavljamo znak podjele ( "/" ). Zatim ponovno kroz ikonu trokuta idite na prozor za odabir funkcija.

11. Kao i posljednji put u čarobnjaku pokrenute funkcije, tražimo traženi operator u kategoriji "Matematički" . Ovaj put se željena funkcija zove "SUMM" . Odaberite ga i kliknite gumb "U redu" .
12. Otvara prozor argumente operatora SUM . Njegova je glavna svrha sažeti podatke u stanicama. Sintaksa ovog operatera je vrlo jednostavna:

    =СУММ(Число1;Число2;…)

    U naše svrhe potreban je samo polje "Broj1" . Unesite koordinate raspona stupca "Prihod" , isključujući ćeliju koja sadrži zbrojeve. Već smo izvršili takvu operaciju u polju "Range" u SUMMER funkciji. Kao iu to vrijeme, koordinate raspona su apsolutne, odabirete ih i pritiskom tipke F4 .

    Nakon toga kliknite gumb "U redu" pri dnu prozora.

13. Kao što možete vidjeti, skup funkcija unosa izvršio je izračun i izlazio rezultat prve ćelije stupca "Grupa" . Prvi je proizvod dodijeljen grupi "A" . Cijela formula koju smo podnijeli za ovaj izračun je kako slijedi:

    =ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")

    Ali, naravno, u svakom pojedinom slučaju, koordinate u ovoj formuli bit će različite. Stoga se ne može smatrati univerzalnim. No, upotrebom gore navedenog priručnika možete umetnuti koordinate bilo koje tablice i uspješno primijeniti ovu metodu u bilo kojoj situaciji.

14. Međutim, to nije sve. Izračunate smo samo za prvi redak tablice. Da biste potpuno ispunili stupac "Grupa" s podacima, morate upotrijebiti oznaku za ispunjavanje, kao što smo to učinili više puta, da biste ovu formulu kopirali u donji raspon (isključujući ćeliju "Ukupno" ). Nakon unosa podataka ABC analiza može se smatrati ispunjenom.

Kao što vidite, rezultati dobiveni uz pomoć varijante pomoću složene formule ne razlikuju se od rezultata koji smo izvršili sortiranjem. Svi proizvodi imaju iste kategorije, ali redovi nisu promijenili početno mjesto.

Pouka: Čarobnjak za funkcije u Excelu

Excel program može uvelike olakšati ABC-analizu za korisnika. To se postiže pomoću alata kao što je sortiranje. Nakon toga izračunava se pojedinačna težina, akumulirani udio i, u stvari, grupiranje. U slučajevima kada promjena početnog položaja redaka u tablici nije dopuštena, možete primijeniti metodu pomoću složene formule.

• ← Korištenje funkcije SELECT u programu Microsoft Excel
• Postavite dan u tjednu po datumu u programu Microsoft Excel →