ABC analiza u Excelu

Jedna od ključnih metoda upravljanja i logistike je ABC analiza. S njom možete klasificirati resurse preduzeća, robe, kupaca itd. Prema stepenu važnosti. Istovremeno, razinom važnosti, svaka od gore navedene jedinice dodjeljuje se jedna od tri kategorije: A, B ili C. Excel program ima u svojim alatima za prtljag koji olakšavaju obavljanje ove vrste analize. Radimo da ih koristimo, a koja je ABC analiza.

Korištenje ABC analize

ANALIZA ABC-a je vrsta poboljšane i prilagođene modernim uvjetima za princip Pareto. Prema metodi svog ponašanja, svi elementi analize podijeljeni su u tri kategorije prema stupnju važnosti:

• Kategorija A - elementi koji imaju kombinaciju više od 80% specifične težine;
• Kategorija B - elementi čija se kombinacija kreće od 5% do 15% specifične težine;
• Kategorija C - Preostali elementi, ukupni agregat od kojih je 5% i manje specifična težina.

Odvojene kompanije koriste naprednije tehnike i dijele stavke ne na 3, već 4 ili 5 grupa, ali mi ćemo se oslanjati na klasičnu analizu Analiza.

Metoda 1: Analiza sa sortiranjem

Excel ABC analiza vrši se pomoću sortiranja. Svi su predmeti sortirani od masnog na manje. Tada se izračunava akumulativni udio svakog elementa na osnovu kojeg je dodijeljena određena kategorija. Otkrijmo kako se u praksi koristi navedena metodologija.

Imamo tablicu sa spiskom proizvoda koji kompanija prodaje i odgovarajući broj prihoda od njihove prodaje tokom određenog vremenskog perioda. Na dnu tablice, ishod prihoda uopšte na svim imenima robe. Postoji zadatak koji koristi ABC analizu, prekrši ovu robu u grupe po njihovoj važnosti za preduzeće.

1. Izdvajamo tablicu s kursorom zatvaranjem lijeve tipke miša, isključujući poklopac i završni niz. Idite na karticu "Data". Kliknite na gumb "Sortiraj", smješten u alatnoj traci "Sortiraj i filtriranje" na vrpci.

   

   Možete i drugačije. Raspodjelimo gornji raspon tablice, a zatim pređite na karticu "Početna" i kliknite na gumb "Sortiraj i filtriranje" smještenog u okviru za uređivanje na traci. Lista se aktivira u kojoj se aktivira položaj "prilagodljivo sortiranje".



2. Prilikom primjene bilo koje od gore navedenih radnji pokreće se prozor postavki sortiranja. Izgledamo tako da "Moji podaci sadrže glave" postavljen je na ček oznaku. U slučaju njegovog odsustva, instalirajte.

   U polju "Stupac" odredite naziv stupca u kojem se podaci prihoda sadrži.

   U polju "Sortiraj" morate odrediti, na kojem će se sortirati posebni kriteriji. Ostavite unaprijed postavljene postavke - "vrijednosti".

   U polju "narudžbe" izložite položaj "silazno".

   Nakon proizvoda navedenih postavki, kliknite na gumb "OK" na dnu prozora.



3. Nakon obavljanja navedenog djelovanja, svi su predmeti sortirani po prihodima iz više na manje.



4. Sada bismo trebali izračunati dio svakog od elemenata za opći rezultat. Stvorite dodatni stupac za ove svrhe, koje nazivamo "Težina podele". U prvoj ćeliji ovog stupca stavljamo znak "=", nakon čega navedete vezu na ćeliju u kojoj je iznos prihoda od implementacije relevantnog proizvoda. Dalje, postavite znak divizije ("/"). Nakon toga ukazujemo na koordinate ćelije, koje sadrže ukupni iznos prodaje robe u cijelom poduzeću.

   S obzirom na činjenicu da ćemo navedenu formulu kopirati u druge ćelije stupca "Dijeljenje" putem markera za punjenje, adresu veze do elementa koji sadrži konačni iznos prihoda u preduzeću, moramo popraviti. Da biste to učinili, napravite vezu apsolutni. Odaberite koordinate navedene ćelije u formuli i pritisnite tipku F4. Prije koordinata, kako vidimo, pojavio se znak dolara, što ukazuje da veza postaje apsolutna. Treba napomenuti da se referenca na iznos prihoda prvog na popisu robe (proizvod 3) treba ostati u rođaku.

   Zatim, da biste napravili izračune, pritisnite tipku ENTER.



5. Kao što vidimo, udio prihoda iz prvog proizvoda navedenog na popisu pojavio se u ciljnoj ćeliji. Da biste kopirali formulu u domenu u donjoj domenu, stavili smo kursor u donji desni ugao ćelije. Njegova transformacija u markeru za punjenje, izgled malog križa. Kliknite lijevu tipku miša i povucite marker za punjenje do kraja stupca.



6. Kao što vidite, cijeli stupac ispunjen je podacima koji karakteriziraju udio prihoda od implementacije svakog proizvoda. Ali vrijednost specifične težine prikazana je u numeričkom formatu, a mi ga moramo transformirati u procenat. Da biste to učinili, istaknite sadržaj stupca "Specifična težina". Zatim prelazimo na karticu "Početna". Na vrpci u grupi Grupa postavki nalazi se polje za prikazivanje podataka. Po defaultu, ako niste proizveli dodatne manipulacije, tamo se mora instalirati "opći" format. Kliknite na ikonu u obliku trokuta koji se nalazi na desnoj strani ovog polja. Na popisu formata odaberite položaj "postotni".



7. Kao što vidimo, sve su vrijednosti stupca pretvorene u procentne vrijednosti. Kao što bi trebalo biti, 100% naznačeno u nizu "ukupnog". Udio robe očekuje se u koloni od većeg do manjih.



8. Sada bismo trebali stvoriti stupac u kojem će biti prikazan akumulirani udio s rastućim ishodom. To je, u svakom retku do pojedinačne specifične težine određenog proizvoda bit će dodan udio svih tih proizvoda koji se nalaze na listi iznad. Za prvu robu na popisu (proizvod 3), individualni udio i akumulirani udio bit će jednak, ali će svi sljedeći individualni pokazatelj trebati dodati akumulirani udio u prethodnom elementu liste.

   Dakle, u prvom redu prenose se u kolonu "Akumulirani dijeljenje" pokazatelj stupca "specifičan".



9. Zatim postavite kursor na drugu ćeliju stupca "akumulirane dionice". Ovdje moramo primijeniti formulu. Stavili smo znak "jednak" i preklopimo sadržaj ćelije "udio" iste linije i sadržaj ćelije "akumulirani udio" iz niza iznad. Sve su reference rezervirane relativne, odnosno ne proizvodimo nikakve manipulacije s njima. Nakon toga izvršite klik na gumb Enter da biste prikazali konačni rezultat.



10. Sada morate kopirati ovu formulu u ćelije ovog stupca koji su postavljeni u nastavku. Da bismo to učinili, koristimo marker za punjenje na koji smo već pribjećimo prilikom kopiranja formule u stupcu Share. Istovremeno, niz "ukupno" nije potreban, jer je akumulirani rezultat 100% prikazat će se na zadnjem proizvodu sa liste. Kao što vidite, svi elementi naše kolone nakon toga su bili ispunjeni.



11. Nakon toga stvorite stupac "Grupe". Trebat će nam grupirati robu po kategoriji A, B i C prema navedenom akumuliranom dijelu. Kao što se sećamo, svi elementi distribuiraju grupe prema sljedećoj shemi:
    • A - do 80%;
    • B - sledeći 15%;
    • C - preostalih 5%.

    Stoga je sva roba koja je akumulirana udio u određenoj težini nalazi se na granici do 80%, dodjeljujemo kategoriju A. Roba sa akumuliranom specifičnom težinom od 80% na 95% dodijeljena je kategoriji B. Preostali Grupa robe s vrijednošću više od 95% akumulirane specifične težine dodjeljujemo kategoriju C.



12. Za jasnoću možete popuniti navedene grupe različitim bojama. Ali to je u volji.

Stoga smo prekinuli elemente na nivou važnosti, koristeći ABC analizu. Kada koristite neke druge tehnike, kao što je već spomenuto, koristi se za probijanje u većem broju grupa, ali princip probijanja u ovom slučaju ostaje gotovo nepromijenjen.

Lekcija: Razvrstavanje i filtriranje u Excelu

Metoda 2: Korištenje složene formule

Naravno, upotreba sortiranja je najčešći način provođenja ABC analize u Exceleu. Ali u nekim je slučajevima potrebno izvršiti ovu analizu bez preuređenja linija na mjestima u izvornom stolu. U ovom slučaju, složena formula će doći do spašavanja. Na primjer, koristit ćemo isti izvorni tablicu kao u prvom slučaju.

1. Dodajemo izvornom stolu koji sadrži naziv robe i prihod od prodaje svakog od njih, kolona "grupe". Kao što vidite, u ovom slučaju ne možemo dodati stupce izračunavanjem pojedinačnih i akumulativnih frakcija.



2. Izrađujemo prvu raspodjelu ćelija u stupcu grupe, nakon čega nastupite klik na gumb "Umetanje funkcije" koji se nalazi u blizini reda Formule.



3. Majstori aktiviranje funkcija. Prelazimo na kategoriju "Linkovi i nizovi". Odaberite funkciju "Choice". Napravimo klik na gumb "OK".



4. Aktivira se prozor argumenta za igre. Sintaksa je predstavljena na sledeći način:

   = Izbor (broj_intex; vrijednost1; vrijednost2; ...)

   Zadatak ove funkcije je povlačenje jedne od navedenih vrijednosti, ovisno o broju indeksa. Broj vrijednosti može doći 254, ali trebat će nam samo tri imena koja odgovaraju ABC-analizi kategorijama: A, B, C. Možemo odmah ući u "A" simbol u polju "B" u "B" u "B" "Polje, polje" VALUE3 "-" C ".



5. Ali uz argument "Indeks broj" morat će se temeljito premjestiti izgradnjom nekoliko dodatnih operatera. Ugradite kursor u polje "Index". Zatim kliknite na ikonu s pogledom na trokut, lijevo od gumba "Umetanje funkcije". Otvara se popis novo korištenih operatora. Treba nam funkcija pretraživanja. Budući da nije na popisu, tada kliknemo na natpis "Ostale funkcije ...".



6. Pokrenut je prozor Wizard prozora funkcija ponovo. Opet idite na kategoriju "Linkovi i nizovi". Pronalazimo položaj "Ploča za pretraživanje", dodijelite ga i kliknite na gumb "OK".



7. Otvaraju se argumenti argumenata pretraživanja. Sintaksa ima sljedeći obrazac:

   = Pretraživanje ploče (Search_name; gledanje__massive; type_station)

   Svrha ove funkcije je definiranje broja položaja navedene stavke. To jest, upravo ono što nam je potrebno za polje "Index" sadrži izbor.

   U polju "Popis araja" možete odmah postaviti sljedeći izraz:

   {0: 0,8: 0,95}

   To bi trebalo biti precizno u kovrčavim zagradama, kao niz matrice. Nije teško pogoditi da ovi brojevi (0; 0,8; 0,95) označavaju granice akumuliranog dijeljenja između grupa.

   Polje "Tip poređenja" nije obavezan i u ovom slučaju nećemo ga ispuniti.

   U polju "Druga vrijednost" postavite kursor. Sledeći ponovo kroz gore opisanu ikonu u obliku trougla, prelazimo na čarobnjaka funkcija.



8. Ovog puta u čarobnjaku funkcija se krećemo u kategoriju "matematički". Odaberite ime "Tiho" i pritisnite tipku "OK".



9. Pokrenut će se prozor funkcije argumenti. Navedeni operater sažima ćelije koje ispunjavaju definitivno stanje. Njegova sintaksa je:

   = Tiho (raspon; kriterij; raspon_suming)

   U polju "Raspon" unesite adresu stupca "Prihod". U ove svrhe postavljamo kursor na terenu, a zatim izrađujući snimak lijeve miša, odaberite sve ćelije odgovarajućeg stupca, isključujući vrijednost "Ukupno". Kao što vidite, adresa se odmah pojavila na terenu. Pored toga, moramo napraviti ovaj link apsolutno. Da biste to učinili, proizvedite njegovu raspodjelu i kliknite na tipku F4. Adresa je puštena na znakove dolara.

   U polju "kriterij" moramo postaviti stanje. Unesite sljedeći izraz:

   ">"&

   Zatim odmah nakon njega unosemo adresu prve ćelije stupca "prihoda". Mi horizontalno činemo u ovoj adresi apsolutno, dodajući znak dolara s tastature ispred slova. Vertikalne koordinate ostavljaju relativno, odnosno ne bi trebalo biti prijave ispred cifre.

   Nakon toga ne pritisnemo tipku "OK" i kliknemo na naziv funkcije pretraživanja u nizu Formule.



10. Zatim se vraćamo na prozor argumenata o pretraživačkoj ploči. Kao što vidimo, u polju "Fogular znači", podaci koje je postavio operator su tihi. Ali to nije sve. Idite na ovo polje i već dodaje dostupnim podacima da biste dodali "+" znak bez navodnika. Zatim uvodimo adresu prve ćelije stupca "Prihod". I opet se horizontalno koordovno vršimo sa apsolutnom i vertikalno ostavljamo relativno.

    Zatim, sav sadržaj polja preuzimamo "željenu vrijednost" u zagradama, nakon čega smo stavili znak divizije ("/"). Nakon toga, ponovo kroz ikonu trokuta, idite na funkciju odabira funkcija.



11. Kao i posljednji put u čarobnjaku za pokretanje funkcije, tražimo potreban operater u kategoriji "Matematička". Ovaj put se željena funkcija naziva "zbroj". Izdvajamo ga i pritisnite tipku "OK".



12. Otvara se prozor za argumente operatera. Njegova glavna svrha je zbroj podataka u ćelijama. Sintaksa ovog operatera je sasvim jednostavna:

    = Sumi (broj1; broj2; ...)

    U naše svrhe bit će potrebno samo polje "broj1". Uložimo se u to koordinate stupca "prihoda", eliminirajući ćeliju koja sadrži rezultate. Već smo izvršili takvu operaciju u polju "Range" funkcije. Kao u to vrijeme, raspon koordinata radi apsolutno, ističući ih i pritiskom na tipku F4.

    Nakon toga kliknite na tipku "OK" na dnu prozora.



13. Kao što vidite, kompleks unesenih funkcija napravio je izračun i donio rezultat u prvoj ćeliji stupca "Grupe". Prvi proizvod je dodijeljen grupu "A". Potpuna formula koju je primijenio za ovaj izračun je sljedeći:

    = Izbor (pretraga ((tih (tih ($ b $ 2: $ b $ 27; ">" & $ b2) + $ b2) / $ b2) / iznosi ($ b $ 2: $ 27); {0: 0,8: 0 , 95}); "A"; "B"; "C")

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



14. Međutim, ovo nije sve. Izračunali smo samo za prvi red tablice. Da biste u potpunosti ispunili stupac grupe "Grupe", morate kopirati ovu formulu u dolje (eliminirajući ćeliju niza "ukupno") pomoću markera za punjenje, kao što smo već radili više puta. Nakon izvršenja podataka, ABC analiza se može razmotriti.

Kao što vidimo, rezultati dobiveni korištenjem opcije koristeći složenu formulu ne razlikuju se od tih rezultata koje smo izvedeni sortiranjem. Sva roba dodjeljuju se iste kategorije, samo u isto vrijeme redovi nisu promijenili svoj početni položaj.

Lekcija: Magistar funkcija u Exceleu

Excel program može značajno ublažiti ABC analizu za korisnika. To se postiže korištenjem takvog alata kao sortiranje. Nakon toga izračunava se pojedinačna specifična težina, akumulirani udio i, zapravo, pregrada u grupe. U slučajevima kada promjena početnog položaja redaka u tablici nije dopuštena, možete primijeniti metodu pomoću složene formule.