Korištenje ABC analize u Microsoft Excel-u

Jedna od ključnih metoda upravljanja i logistike je ABC analiza. Uz njegovu pomoć možete klasificirati resurse preduzeća, robe, kupaca itd. prema stepenu važnosti. Istovremeno, prema razini važnosti, svakoj od navedenih jedinica dodijeljena je jedna od tri kategorije: A, B ili C. Excel u svom alatu za prtljag omogućava lakše provođenje ove vrste analiza. Let's shvatiti kako ih koristiti i što čini ABC analiza.

Korištenje ABC analize

ABC analiza je vrsta poboljšane i prilagođene modernim uslovima verzije Pareto principa. Prema metodologiji njegove provedbe, svi elementi analize podijeljeni su u tri kategorije prema stupnju važnosti:

• Kategorija A - elemenata koji imaju ukupno više od 80% specifična gravitacija;
• Kategorija B - elemente iz kojih je kombinacija 5% prije 15% specifična gravitacija;
• Kategorija C - preostali elementi, čija je ukupna kombinacija 5% i manje specifične gravitacije.

Neke tvrtke primjenjuju naprednije tehnike i raščlanjuju elemente u 3 ili 4 ili 5 grupa, ali mi ćemo se osloniti na klasičnu shemu ABC analize.

Metoda 1: analiza sortiranja

U Excelu se ABC analiza vrši pomoću sortiranja. Svi su predmeti sortirani od najvećih do najmanjih. Zatim se izračunava kumulativna specifična težina svakog elementa na osnovu koje mu je dodijeljena određena kategorija. Otkrijmo, koristeći konkretan primjer, kako se ova tehnika primjenjuje u praksi.

Imamo tabelu sa spiskom robe koju kompanija prodaje i odgovarajućim iznosom prihoda od njihove prodaje u određenom vremenskom periodu. Na dnu tablice pogađa ukupni prihod za sve artikle robe. Zadatak je pomoću ABC analize podijeliti ove proizvode u grupe prema njihovoj važnosti za preduzeće.

1. Odaberite tablicu s pokazivačem podataka, držeći lijevu tipku miša, isključujući zaglavlje i završni redak. Idite na karticu "Podaci". Kliknite na dugme. "Sortiraj"koja se nalazi u bloku alata Poredaj i filtriraj na vrpci.

   

   Možete i drugačije. Odaberite gornji raspon tablice, a zatim pređite na karticu "Početna" i kliknite na dugme Poredaj i filtrirajkoja se nalazi u bloku alata "Uređivanje" na vrpci. Aktivira se lista u kojoj biramo poziciju u njoj. Prilagođena vrsta.



2. Prilikom primjene bilo kojeg od gore navedenih radnji pokreće se prozor postavki sortiranja. Izgledamo tako oko parametra "Moji podaci sadrže zaglavlja" postavljena je kvačica. U slučaju nepostojanja, instalirajte.

   U polju Stupac navedite naziv stupca koji sadrži podatke o prihodima.

   U polju "Sortiraj" trebate odrediti po kojem će se konkretnom kriteriju sortiranje izvršiti. Predodređene postavke ostavljamo - "Vrijednosti".

   U polju "Naruči" postaviti položaj "Silazno".

   Nakon što odredite podešavanja, kliknite na dugme "OK" pri dnu prozora.



3. Nakon izvođenja navedene radnje, svi elementi su sortirani po prihodima od najvećih do najmanjih.



4. Sada bi trebali izračunati specifičnu težinu svakog od elemenata za ukupno. U te svrhe stvaramo dodatnu kolonu koju ćemo nazvati "Specifična gravitacija". U prvu ćeliju ovog stupca stavite znak "=", nakon čega označavamo vezu do ćelije u kojoj se nalazi iznos prihoda od prodaje odgovarajućeg proizvoda. Zatim postavite znak podjele ("/") Nakon toga navedite koordinate ćelije, koja sadrži ukupni iznos prodaje robe u cijelom preduzeću.

   S obzirom na činjenicu da ćemo navedenu formulu kopirati u druge ćelije u stupcu "Specifična gravitacija" pomoću oznake ispunjavanja, trebamo popraviti adresu veze do elementa koji sadrži ukupni iznos prihoda za poduzeće. Da biste to učinili, napravite vezu apsolutnom. Odaberite koordinate određene ćelije u formuli i pritisnite tipku F4. Ispred koordinata, kao što vidimo, pojavio se znak dolara, što upućuje na to da je veza postala apsolutna. Treba napomenuti da je veza za vrijednost prihoda prve stavke na popisu (Proizvod 3) moraju ostati relativni.

   Zatim, za izračun, kliknite na gumb Unesite.



5. Kao što vidite, udio prihoda od prvog proizvoda navedenog na popisu prikazan je u ciljanoj ćeliji. Da biste kopirali formulu u niže raspon, stavite kursor u donji desni ugao ćelije. Pretvara se u marker za ispunu koji izgleda kao mali križ. Kliknite lijevi gumb miša i povucite marker za ispunu do kraja stupca.



6. Kao što vidite, cijeli je stupac ispunjen podacima koji karakteriziraju udio prihoda od prodaje svakog proizvoda. Ali specifična gravitacija je prikazana u numeričkom formatu i moramo je transformirati u postotak. Da biste to učinili, odaberite sadržaj stupca "Specifična gravitacija". Zatim prelazimo na karticu "Početna". Na vrpci u grupi postavki "Broj" Postoji polje koje prikazuje format podataka. Ako niste izvršili dodatne manipulacije, format bi trebao biti postavljen tamo "General". Kliknemo na ikonu u obliku trokuta koji se nalazi desno od ovog polja. Na popisu formata koji se otvori odaberite poziciju "Kamata".



7. Kao što vidite, sve vrijednosti stupaca pretvorene su u postotne vrijednosti. Kao što se i očekivalo, u skladu "Ukupno" naznačeno 100%. Očekuje se da će udio robe biti u stupcu od većeg prema manjem.



8. Sada bismo trebali stvoriti stupac u kojem bi se prikazao akumulirani udio sa kumulativnim ukupnim iznosom. To je da će u svakom redu specifična težina određenog proizvoda dodati specifičnu težinu svih onih proizvoda koji se nalaze na gore navedenom popisu. Za prvu stavku na popisu (Proizvod 3) pojedinačna specifična težina i akumulirani udio bit će jednaki, ali za sve naredne, akumulirani udio prethodnog elementa liste treba dodati pojedinačnom pokazatelju.

   Dakle, u prvom redu prelazimo na kolonu Akumulirana udela indikator stupaca "Specifična gravitacija".



9. Zatim postavite kursor na drugu ćeliju u koloni. Akumulirana udela. Ovdje moramo primijeniti formulu. Stavili smo znak jednako i dodajte sadržaj ćelije "Specifična gravitacija" isti red i sadržaj ćelije Akumulirana udela sa linije iznad. Sve veze ostavljamo relativnima, odnosno ne manipuliramo njima. Nakon toga kliknite na gumb Unesite za prikaz konačnog rezultata.



10. Sada morate kopirati ovu formulu u ćelije ovog stupca koje se nalaze ispod. Da biste to učinili, upotrijebite marker za popunjavanje, kojem smo već pribjegli prilikom kopiranja formule u stupac "Specifična gravitacija". U ovom slučaju, linija "Ukupno" nema potrebe za hvatanjem, jer se akumulirani rezultat u 100% će se prikazati na zadnjoj stavci s popisa. Kao što vidite, svi elementi naše kolumne bili su ispunjeni nakon toga.



11. Nakon toga stvaramo kolonu "Grupa". Trebat ćemo grupirati proizvode u kategorije A, B i C prema naznačenom akumuliranom udjelu. Kao što se sjećamo, svi elementi su podijeljeni u grupe prema sljedećoj shemi:
    • A - do 80%;
    • B - sledeće 15%;
    • Sa - preostalo 5%.

    Dakle, za svu robu, čiji je akumulirani udio specifične gravitacije uključen u granicu do 80%dodijeliti kategoriju A. Roba specifične težine od 80% prije 95% dodijeliti kategoriju B. Preostala grupa proizvoda čija je vrijednost veća od 95% akumulirana kategorija dodijele specifične težine C.



12. Radi jasnoće možete ove grupe ispuniti različitim bojama. Ali to nije obavezno.

Dakle, podijelili smo elemente u grupe prema stupnju važnosti, koristeći ABC analizu. Kada se koriste neke druge tehnike, kao što je već spomenuto, koristi se cijepanje na veći broj grupa, ali princip cijepanja ostaje gotovo nepromijenjen.

Lekcija: Razvrstavanje i filtriranje u Excelu

2. metoda: koristite složenu formulu

Naravno da je upotreba sortiranja najčešći način za izvršavanje ABC analize u Excelu. No u nekim je slučajevima potrebno provesti ovu analizu bez preuređivanja redaka u izvornoj tablici. U ovom će slučaju složena formula doći do pomoći. Kao primjer, upotrijebit ćemo istu izvornu tablicu kao u prvom slučaju.

1. U originalnu tablicu koja sadrži naziv robe i prihod od prodaje svake od njih, stupac "Grupa". Kao što vidite, u ovom slučaju ne možemo dodati stupce s računanjem pojedinačnih i kumulativnih dionica.



2. Odaberite prvu ćeliju u koloni "Grupa"a zatim kliknite na dugme "Umetanje funkcije"koji se nalazi blizu linije formula.



3. Aktivacija u toku Čarobnjaci za funkcije. Prelazimo na kategoriju Upućivanja i nizovi. Odaberite funkciju "IZBOR". Kliknite na dugme "OK".



4. Prozor argumenta funkcije je aktiviran. ODABIR. Njegova sintaksa predstavljena je na sljedeći način:

   = SELECT (Index_number; Value1; Value2; ...)

   Cilj ove funkcije je ispisati jednu od navedenih vrijednosti, ovisno o broju indeksa. Broj vrijednosti može doseći 254, ali potrebna su nam samo tri imena koja odgovaraju kategorijama ABC analize: A, B, Sa. Na terenu možemo odmah ući "Vrijednost1" simbol "A"u polju "Value2" - "B"u polju "Value3" - "C".



5. Ali s argumentom Indeksni broj morate s njom temeljito poprilično integrirati integrirajući nekoliko dodatnih operatora u njega. Postavite kursor u polje Indeksni broj. Zatim kliknite na ikonu u obliku trokuta s lijeve strane gumba "Umetanje funkcije". Otvara se popis nedavno korištenih operatera. Treba nam funkcija PRETRAŽI. Pošto ga nema na popisu, kliknite na natpis "Ostale karakteristike ...".



6. Prozor se ponovo pokreće. Čarobnjaci za funkcije. Opet prelazimo na kategoriju Upućivanja i nizovi. Pronađite položaj tamo "PRETRAŽI", odaberite ga i kliknite na gumb "OK".



7. Otvara se prozor argumenata operatora PRETRAŽI. Njegova sintaksa je sljedeća:

   = PRETRAŽI (Traženo_vredno; Pregledano_arije; Tip podudaranja)

   Svrha ove funkcije je odrediti broj položaja navedenog elementa. To je upravo ono što nam treba za teren Indeksni broj funkcije ODABIR.

   U polju Gledano polje Možete odmah navesti sljedeći izraz:

   {0:0,8:0,95}

   Trebao bi biti u kovrčavim zagradama, kao formula niza. Nije teško pretpostaviti da su ovi brojevi (0; 0,8; 0,95) navesti granice akumuliranog udjela između grupa.

   Polje Vrsta podudaranja izborno i u ovom slučaju ga nećemo popuniti.

   U polju "Tražim vrijednost" podesite kursor. Zatim ponovo kroz gornji piktogram u obliku trokuta na koji se krećemo Čarobnjak za funkcije.



8. Ovaj put u Čarobnjak za funkcije prelazak na kategoriju "Matematički". Odaberite ime SUMMS i kliknite na dugme "OK".



9. Pokreće se prozor argumenta funkcije SUMMES. Navedeni operator zbraja ćelije koje ispunjavaju specifičan uvjet. Njegova sintaksa je:

   = SUMMES (raspon; kriterij; sum_range)

   U polju "Domet" unesite adresu stupca "Prihod". U tu svrhu postavite kursor u polje, a zatim, držeći lijevu tipku miša, odaberite sve ćelije u odgovarajućem stupcu, isključujući vrijednost "Ukupno". Kao što vidite, adresa je odmah prikazana u polju. Osim toga, ovu vezu moramo učiniti apsolutnom. Da biste to učinili, odaberite ga i pritisnite tipku F4. Adresa se isticala znakovima dolara.

   U polju "Kriterij" moramo postaviti uslov. Unosimo sljedeći izraz:

   ">"&

   Zatim odmah nakon njega unosimo adresu prve ćelije stupca "Prihod". Horizontalne koordinate na ovoj adresi pravimo apsolutnim dodavanjem znaka dolara sa tipkovnice ispred slova. Okomite koordinate ostavljamo relativnima, tj. Ispred cifre ne smije biti nikakvog znaka.

   Nakon toga ne klikajte na gumb "OK", i kliknite na ime funkcije PRETRAŽI u traci sa formulama.



10. Zatim se vraćamo na prozor argumenta funkcije PRETRAŽI. Kao što vidite, na terenu "Tražim vrijednost" pojavio se skup podataka od strane operatera SUMMES. Ali to nije sve. Idite u ovo polje i dodajte znak postojećim podacima. "+" bez citata Zatim unosimo adresu prve ćelije stupca "Prihod". I opet, horizontalne koordinate ove veze ćemo učiniti apsolutnima i ostaviti ih vertikalnim relativnim.

    Zatim uzmite cijeli sadržaj polja "Tražim vrijednost" u zagradama, nakon čega stavljamo znak podele ("/") Nakon toga, ponovo kroz ikonu trokuta, idite na prozor za odabir funkcija.



11. Kao i prošli put Čarobnjak za funkcije tražite željenog operatera u kategoriji "Matematički". Ovog puta se poziva željena funkcija SUM. Odaberite ga i kliknite na gumb. "OK".



12. Otvara se prozor argumenata operatora SUM. Njegova glavna svrha je sažeti podatke u ćelijama. Sintaksa ove izjave prilično je jednostavna:

    = SUM (Broj1; Broj2; ...)

    Za naše potrebe potrebno je samo polje "Broj1". Unesite koordinate raspona stupca u njega. "Prihod"isključujući ćeliju koja sadrži ukupne iznose. Već smo izveli sličnu operaciju na terenu "Domet" funkcije SUMMES. Kao i tada, koordinate raspona pravimo apsolutnim odabirom i pritiskom na tipku F4.

    Nakon toga kliknite na gumb "OK" pri dnu prozora.



13. Kao što možete vidjeti, kompleks uvedenih funkcija izvršio je proračun i rezultat vratio u prvu ćeliju stupca "Grupa". Prvom proizvodu dodijeljena je grupa "A". Potpuna formula koju smo koristili za ovo izračunavanje je sljedeća:

    = ODABIR (PRETRAŽIVANJE ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")

    Ali, naravno, u svakom će slučaju koordinate u ovoj formuli biti različite. Stoga se ne može smatrati univerzalnim. Ali, pomoću gore navedenih smjernica, možete umetnuti koordinate bilo koje tablice i uspješno primijeniti ovu metodu u bilo kojoj situaciji.



14. Međutim, to nije sve. Proračun smo izvršili samo za prvi red tabele. Da biste u potpunosti napunili stupac s podacima "Grupa", trebate kopirati ovu formulu u niže raspon (isključujući ćeliju redaka "Ukupno") pomoću markera ispunjavanja, kao što smo učinili više puta. Nakon unosa podataka, analiza ABC može se smatrati dovršenom.

Kao što vidite, rezultati dobiveni upotrebom opcije pomoću složene formule uopće se ne razlikuju od rezultata koje smo izveli sortiranjem. Svi su proizvodi dodijeljeni istim kategorijama, ali linije nisu promijenile početni položaj.

Lekcija: Čarobnjak za funkcije u Excelu

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