Jedna od ključnih metoda upravljanja i logistike je ABC analiza. Pomoću njega možete klasifikovati resurse preduzeća, proizvoda, kupaca itd. po redu važnosti. Istovremeno, prema nivou važnosti, svakoj od gore navedenih jedinica dodijeljena je jedna od tri kategorije: A, B, ili C. Excel ima alate u prtljagu koji olakšavaju provođenje ove vrste analiza. Da vidimo kako da ih koristimo i šta je ABC analiza.
Koristeći ABC analizu
ABC analiza je svojevrsna poboljšana i prilagođena suvremenim uvjetima varijanta Pareto principa. Prema metodologiji njenog ponašanja, svi elementi analize su podijeljeni u tri kategorije prema važnosti:
- Kategorija A - elementi koji imaju više zajedničkog 80% specifična težina;
- Kategorija B - elementi od kojih je ukupnost 5% do 15% specifična težina;
- Kategorija C - preostalih elemenata, čiji je ukupan zbir 5% i manje specifične težine.
Neke kompanije koriste naprednije tehnike i dijele elemente ne u 3, već u 4 ili 5 grupa, ali ćemo se osloniti na klasičnu shemu ABC analize.
Metoda 1: analiza sortiranjem
U Excelu se ABC analiza vrši pomoću sortiranja. Sve stavke su sortirane od najvećeg do najmanjeg. Tada se izračunava kumulativna specifična težina svakog elementa, na osnovu koje mu je dodeljena određena kategorija. Hajde da upotrebimo specifičan primer kako bismo saznali kako se ova tehnika primenjuje u praksi.
Imamo tabelu sa listom robe koju kompanija prodaje i odgovarajući iznos prihoda od prodaje za određeni vremenski period. Na dnu tabele, ukupni prihodi su sumirani za sve stavke robe. Zadatak je koristiti ABC-analizu za podjelu tih proizvoda u grupe prema njihovoj važnosti za kompaniju.
- Izaberite tabelu sa kursorom podataka, držeći levi taster miša, isključujući zaglavlje i poslednji red. Idite na karticu "Data". Kliknite na dugme. "Sortiraj"nalazi se u bloku alata "Sortiraj i filtriraj" na kaseti.
Takođe možete raditi drugačije. Izaberite gornji raspon tabele, a zatim pređite na karticu "Home" i kliknite na dugme "Sortiraj i filtriraj"nalazi se u bloku alata Uređivanje na kaseti. Aktivira se lista u kojoj biramo poziciju u njoj. "Prilagođeno sortiranje".
- Kada primenite bilo koju od gore navedenih radnji, pokreće se prozor za podešavanje sortiranja. Pogledamo parametar "Moji podaci sadrže zaglavlja" kvačica je postavljena. U slučaju odsutnosti, instalirajte.
Na terenu "Kolona" navedite naziv kolone u kojoj su podaci o prihodima.
Na terenu "Sortiraj" trebate navesti koji će kriteriji biti razvrstani. Ostavljamo podešene postavke - "Vrijednosti".
Na terenu "Red" postavite poziciju "Descending".
Nakon što izvršite ova podešavanja kliknite na dugme "OK" na dnu prozora.
- Nakon izvršenja ove akcije, sve stavke su razvrstane po prihodima od najviše do najniže.
- Sada treba da izračunamo proporciju svakog od elemenata za ukupan broj. U te svrhe kreiramo dodatnu kolonu, koju ćemo nazvati "Share". U prvu ćeliju ove kolone stavite znak "="nakon čega se navodi referenca na ćeliju u kojoj se nalazi suma prihoda od prodaje relevantnog proizvoda. Zatim, podesite znak podele ("/"). Nakon toga se navode koordinate ćelije koje sadrže ukupan iznos prodaje robe u celom preduzeću.
Imajući u vidu činjenicu da ćemo kopirati navedenu formulu u druge ćelije kolone "Share" Koristeći marker za popunjavanje, adresu veze do elementa koji sadrži ukupan iznos prihoda za preduzeće, moramo popraviti. Da biste to uradili, učinite link apsolutnim. Odaberite koordinate navedene ćelije u formuli i pritisnite tipku F4. Kao što vidimo, ispred koordinata se pojavio znak za dolar, što ukazuje da je veza postala apsolutna. Treba napomenuti da se referenca na iznos prihoda prve stavke na listi (Tačka 3) mora ostati relativna.
Zatim, za izračunavanje, kliknite na dugme. Enter.
- Kao što možete vidjeti, udio prihoda od prvog proizvoda navedenog u popisu prikazuje se u ciljnoj ćeliji. Da biste napravili kopiju formule u donjem opsegu, postavite kursor u donji desni ugao ćelije. Pretvara se u marker za ispunu koji izgleda kao mali križ. Kliknite levim tasterom miša i povucite ručicu za punjenje do kraja kolone.
- Kao što vidite, cela kolona je popunjena podacima koji opisuju udio prihoda od prodaje svakog proizvoda. No, vrijednost specifične težine je prikazana u numeričkom formatu i trebamo je pretvoriti u postotak. Da biste to uradili, izaberite sadržaj kolone "Share". Zatim pređite na karticu "Home". Na vrpci u grupi postavki "Broj" Postoji polje koje prikazuje format podataka. Po defaultu, ako niste izvršili dodatne manipulacije, format bi trebao biti postavljen tamo. "General". Klikom na ikonu u obliku trokuta nalazi se desno od ovog polja. Na listi formata koji se otvara, izaberite poziciju "Interesovanje".
- Kao što možete vidjeti, sve vrijednosti stupaca su pretvorene u postotke. Kao što bi trebalo da bude, u redu "Ukupno" naznačeno 100%. Udeo robe za koji se očekuje da će se nalaziti u koloni od većih do manjih.
- Sada bismo trebali kreirati kolonu u kojoj bi se prikazao kumulativni dio s kumulativnim ukupnim iznosom. To jest, u svakom redu, specifična težina svih roba koje se nalaze na gornjoj listi biće dodata pojedinačnoj specifičnoj težini određenog proizvoda. Za prvu stavku na popisu (Tačka 3) individualna specifična težina i akumulirano učešće će biti jednaki, ali za sve naredne, akumulirano učešće prethodne stavke u listi će se morati dodati pojedinačnom indikatoru.
Dakle, u prvom redu prelazimo u kolonu "Akumulirano dijeljenje" stopa stupca "Share".
- Zatim postavite kursor u ćeliju druge kolone. "Akumulirano dijeljenje". Ovde moramo da primenimo formulu. Stavili smo znak jednak i preklopite sadržaj ćelije "Share" isti redak i sadržaj ćelije "Akumulirano dijeljenje" iz gornje linije. Svi linkovi su relativni, odnosno ne vršimo nikakve manipulacije s njima. Nakon toga kliknite na dugme. Enter za prikaz konačnog rezultata.
- Sada morate kopirati ovu formulu u ćelije ove kolone, koje se nalaze ispod. Da biste to uradili, koristite marker za popunjavanje, na koji smo već pribjegli kopiranju formule u koloni "Share". U isto vreme, string "Ukupno" hvatanje nije neophodno jer je akumulirani rezultat 100% će se prikazati na posljednjoj stavci s popisa. Kao što vidite, svi elementi naše kolone su popunjeni.
- Nakon toga kreiramo kolonu "Grupa". Morat ćemo grupirati proizvode u kategorije A, B i C prema navedenom kumulativnom udjelu. Kao što se sjećamo, svi elementi su podijeljeni u grupe prema sljedećoj shemi:
- A - do 80%;
- B - sljedeće 15%;
- Sa - preostalo 5%.
Dakle, sva roba, čiji kumulativni udeo specifične težine ulazi u granicu prema 80%dodijeliti kategoriju A. Roba sa akumuliranom specifičnom težinom od 80% do 95% dodijeliti kategoriju B. Preostala grupa proizvoda sa vrednošću više 95% određena specifična težina dodijeliti kategoriju C.
- Radi jasnoće, možete popuniti ove grupe u različitim bojama. Ali ovo je opcionalno.
Prema tome, podelili smo elemente u grupe prema nivou važnosti, koristeći ABC analizu. Kada se koriste neke druge metode, kao što je gore navedeno, primijenite podjelu na više grupa, ali princip particioniranja ostaje gotovo nepromijenjen.
Lekcija: Sortiranje i filtriranje u Excelu
Metod 2: korišćenje složene formule
Naravno, upotreba sortiranja je najčešći način provođenja ABC analize u Excelu. Međutim, u nekim slučajevima je potrebno provesti ovu analizu bez preraspodjele redova u izvornoj tablici. U ovom slučaju, spašava se kompleksna formula. Na primer, koristićemo istu izvornu tabelu kao u prvom slučaju.
- U originalnu tabelu dodajte naziv robe i prihode od prodaje svake od njih, kolonu "Grupa". Kao što možete vidjeti, u ovom slučaju ne možemo dodati kolone s izračunom pojedinačnih i kumulativnih dionica.
- Izaberite prvu ćeliju u koloni. "Grupa"zatim kliknite na dugme. "Umetni funkciju"nalazi se u blizini formule.
- Aktivacija se vrši Funkcioneri. Premesti u kategoriju "Linkovi i nizovi". Izaberite funkciju "SELECT". Kliknite na dugme. "OK".
- Aktivira se prozor argumenta funkcije. SELECTION. Njegova sintaksa je sledeća:
= SELECT (indeks_broj; vrijednost1; vrijednost2; ...)Cilj ove funkcije je da prikaže jednu od specificiranih vrijednosti, ovisno o broju indeksa. Broj vrijednosti može doseći 254, ali su nam potrebna samo tri imena koja odgovaraju kategorijama ABC analize: A, B, Sa. Možemo odmah ući na teren "Value1" simbol "A"na terenu "Value2" - "B"na terenu "Value3" - "C".
- Ali sa argumentom "Broj indeksa" Bit će potrebno temeljno popraviti, ugrađivati neke dodatne operatore. Postavite kursor u polje "Broj indeksa". Zatim kliknite na ikonu koja ima oblik trougla lijevo od gumba "Umetni funkciju". Otvara se lista nedavno korišćenih operatora. Treba nam funkcija MATCH. Pošto nije na listi, kliknite na naslov "Ostale karakteristike ...".
- Ponovo pokreće prozor. Funkcioneri. Opet idite u kategoriju "Linkovi i nizovi". Tamo nalazimo poziciju "MATCH"izaberite ga i kliknite na dugme "OK".
- Otvara se prozor argumenta operatora MATCH. Njegova sintaksa je sledeća:
= MATCH (Searched value; Viewed array; Match_type)Svrha ove funkcije je da odredi broj pozicije navedenog elementa. To je upravo ono što nam je potrebno za polje "Broj indeksa" funkcije SELECTION.
Na terenu "Pogledano polje" Možete odmah postaviti sljedeći izraz:
{0:0,8:0,95}Trebalo bi da bude tačno u vitičastim zagradama, kao matrica. Nije teško pogoditi da su ti brojevi (0; 0,8; 0,95) označava granice akumuliranog udjela između grupa.
Field "Vrsta mapiranja" nije obavezan iu ovom slučaju nećemo ga popuniti.
Na terenu "Tražena vrijednost" postavite kursor. Onda opet, kroz gore opisanu ikonu u obliku trougla, prelazimo na Čarobnjak za funkcije.
- Ovaj put u Čarobnjak za funkcije premjesti u kategoriju "Matematički". Izaberite ime "SUMMESLI" i kliknite na dugme "OK".
- Počinje prozor argumenta funkcije. Sums. Navedeni operator zbraja ćelije koje zadovoljavaju specificirani uvjet. Njegova sintaksa je:
= SUMMES (raspon; kriterij; range_summing)Na terenu "Range" unesite adresu kolone "Prihodi". U te svrhe postavljamo kursor u polje, a zatim, nakon što ste pritisnuli lijevu tipku miša, odaberite sve ćelije odgovarajućeg stupca, isključujući vrijednost "Ukupno". Kao što vidite, adresa se odmah prikazuje u polju. Pored toga, moramo napraviti ovaj link apsolutnim. Da biste to uradili, napravite njegov izbor i pritisnite taster F4. Adresa je označena znakovima za dolar.
Na terenu "Kriterijum" moramo da postavimo uslov. Unesite sljedeći izraz:
">"&Zatim odmah nakon toga unosimo adresu prve ćelije kolone. "Prihodi". Horizontalne koordinate u ovoj adresi smo apsolutne, dodajući znak za dolar sa tastature ispred slova. Vertikalne koordinate su relativne, tj. Ispred broja ne smije biti nikakvog znaka.
Nakon toga, nemojte pritisnuti dugme "OK", i kliknite na ime funkcije MATCH u formuli bar.
- Zatim se vraćamo na prozor argumenta funkcije. MATCH. Kao što vidite, na terenu "Tražena vrijednost" podaci su dati od strane operatora Sums. Ali to nije sve. Idite na ovo polje i dodajte znak postojećim podacima. "+" bez navodnika. Zatim unosimo adresu prve ćelije kolone. "Prihodi". I opet ćemo napraviti apsolutne horizontalne koordinate ove veze, a vertikalno ostaviti relativnu.
Zatim uzmite ceo sadržaj polja "Tražena vrijednost" u zagradama, zatim stavite znak raspodjele ("/"). Nakon toga, ponovo kroz ikonu trokuta, idite na prozor za izbor funkcije.
- Kao prošli put u trčanju Čarobnjak za funkcije Tražite željenog operatera u kategoriji "Matematički". Ovog puta se poziva željena funkcija "SUMM". Izaberite je i kliknite na dugme. "OK".
- Otvara se prozor argumenta operatora SUM. Njegova glavna svrha je sumiranje podataka u ćelijama. Sintaksa ove izjave je vrlo jednostavna:
= SUM (Broj1; Broj2; ...)Za naše potrebe potrebna nam je samo polje. "Number1". Unesite koordinate opsega stupaca "Prihodi", isključujući ćeliju koja sadrži ukupne vrijednosti. Već smo obavili sličnu operaciju na terenu. "Range" funkcije Sums. Kao i tada, pravimo apsolutne koordinate raspona odabirom njih i pritiskom na tipku F4.
Nakon toga kliknite na tipku "OK" na dnu prozora.
- Kao što možete vidjeti, kompleks unesenih funkcija proizveo je izračun i dao rezultat u prvoj ćeliji stupca "Grupa". Prvoj stavci dodijeljena je grupa. "A". Kompletna formula koju smo koristili za ovaj izračun je sljedeća:
= SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} "A"; "B"; "C");Ali, naravno, u svakom slučaju, koordinate u ovoj formuli će biti različite. Stoga se ne može smatrati univerzalnim. Ali, koristeći priručnik koji je gore naveden, možete umetnuti koordinate bilo koje tabele i uspješno primijeniti ovu metodu u bilo kojoj situaciji.
- Međutim, to nije sve. Mi smo izračunali samo za prvi red tabele. Potpuno popunite kolonu podataka "Grupa", trebate kopirati ovu formulu u raspon ispod (isključujući ćeliju retka "Ukupno") koristeći marker ispunjavanja, kao što smo to učinili više od jednom. Nakon unosa podataka, ABC analiza se može smatrati kompletnom.
Kao što možete videti, rezultati dobijeni korišćenjem varijante sa upotrebom složene formule se uopšte ne razlikuju od rezultata koje smo izvršili sortiranjem. Svi proizvodi su dodijeljeni istim kategorijama, ali linije nisu promijenile svoju početnu poziciju.
Lekcija: Čarobnjak funkcije Excel
Excel može znatno olakšati ABC analizu za korisnika. To se postiže pomoću alata kao što je sortiranje. Nakon toga se izračunava individualna specifična težina, akumulirano učešće i, zapravo, podjela na grupe. U slučajevima kada promena početne pozicije redova u tabeli nije dozvoljena, možete primeniti metod koristeći kompleksnu formulu.
