Vienas iš pagrindinių valdymo ir logistikos metodų yra ABC analizė. Su juo galite klasifikuoti įmonės, prekių, klientų ir kt. Išteklių išteklius. Pagal svarbą. Tuo pačiu metu, pagal svarbų lygį, kiekvienas iš pirmiau minėto vieneto priskiriamas viena iš trijų kategorijų: A, B arba C. Excel programa turi savo bagažo įrankius, kurie leidžia lengviau atlikti šią analizę. Išsiaiškinkime, kaip juos naudoti, ir kas yra ABC analizė.
Naudojant ABC analizę
ABC analizė yra sustiprintas ir pritaikytas prie modernių sąlygų pareto principui. Remiantis jo elgesio metodu, visi analizės elementai yra suskirstyti į tris kategorijas pagal svarbų laipsnį:- A kategorija - elementai, turintys daugiau kaip 80% specifinio sunkumo;
- B kategorijos elementai, kurių derinys svyruoja nuo 5% iki 15% specifinio sunkio;
- C kategorija - likusieji elementai, kurių bendra suvestinė yra 5% ir mažiau specifiška gravitacija.
Atskiros įmonės naudoja pažangesnius metodus ir padalinkite elementus ne 3, bet 4 ar 5 grupėmis, tačiau mes pasitiksime klasikine ABC analizės schema.
1 metodas: analizė su rūšiavimu
"Excel ABC" analizė atliekama naudojant rūšiavimą. Visi elementai yra rūšiuojami nuo riebios iki mažiau. Tada apskaičiuojama kiekvieno elemento kaupiamoji dalis, pagal kurią ji priskiriama tam tikra kategorija. Sužinokite, kaip praktikoje naudojama nurodyta metodika.
Turime lentelę su produktų, kuriuos bendrovė parduoda, sąrašą ir atitinkamą pajamų skaičių iš jų pardavimo tam tikrą laiką. Lentelės apačioje, apskritai dėl visų prekių pavadinimų rezultatus. Užduotis naudojant ABC analizę, pertrauka šias prekes į grupes pagal jų svarbą įmonei.
- Mes pabrėžiame lentelę su žymekliu uždarydami kairįjį pelės mygtuką, išskyrus dangtelį ir galutinę eilutę. Eikite į skirtuką "Data". Spausdami mygtuką "Rūšiuoti", esančiame "Rūšiuoti ir filtru" įrankių juostoje esančioje juostoje.
Taip pat galite padaryti skirtingai. Mes skiriame aukščiau pateiktą lentelės diapazoną, tada persikelkite į skirtuką "Namai" ir spustelėkite mygtuką "Rūšiuoti ir filtruoti", esantį "Redage" įrankių rinkinyje esančioje juostoje. Sąrašas yra įjungtas, kuriame įjungta pozicija "pritaikoma rūšiavimas".
- Taikant bet kurį iš pirmiau minėtų veiksmų, langų paleidimo nustatymai. Mes atrodome taip, kad "mano duomenys yra antraštės" parametras buvo nustatytas į žymės. Jei nėra jo nebuvimo, įdiegti.
Lauke "stulpelyje" nurodykite stulpelio pavadinimą, kuriame pateikiami pajamų duomenys.
Lauke "Rūšiuoti" jums reikia nurodyti, kokiu būdu bus surūšiuoti konkretūs kriterijai. Palikite iš anksto nustatytus nustatymus - "reikšmes".
Lauke "užsakymo" eksponuoti poziciją "mažėjančia".
Po nustatytų nustatymų produkto spustelėkite mygtuką "OK" lango apačioje.
- Atlikus nurodytus veiksmus, visi elementai buvo surūšiuoti pagal pajamas iš daugiau iki mažesnio.
- Dabar turėtume apskaičiuoti kiekvieno elemento dalį bendram rezultatui. Sukurkite papildomą stulpelį šiems tikslams, kuriuos vadiname "Akcijų svoriu". Pirmajame šio stulpelio ląstelėje mes įdėjome ženklą "=", po kurio nurodote nuorodą į ląstelę, kurioje pajamų suma iš atitinkamo produkto įgyvendinimo yra. Toliau nustatykite padalijimo ženklą ("/"). Po to nurodome ląstelės koordinates, kuriose yra bendra prekių pardavimo suma visoje įmonėje.
Atsižvelgiant į tai, kad nurodyta formulė mes nukopijuojame į kitas ląstelių "Share" stulpelio užpildymo žymeklį, nuorodos adresą į elementą, kuriame yra galutinio dydžio pajamų įmonei, turime pataisyti. Norėdami tai padaryti, padaryti nuorodą Absoliutus. Pasirinkite nurodytos ląstelės koordinates formulėje ir paspauskite klavišą F4. Prieš koordinates, kaip matome, pasirodė dolerio ženklas, kuris rodo, kad nuoroda tapo absoliuti. Pažymėtina, kad nuoroda į pirmojo įplaukų sumą prekių sąraše (3 produktas) turėtų išlikti santykinis.
Tada, jei norite atlikti skaičiavimus, paspauskite ENTER mygtuką.
- Kaip matome, pajamų iš pirmojo produkto, nurodyto sąraše, dalis pasirodė tikslinėje ląstelėje. Norėdami nukopijuoti formulę žemiau esančiame diapazone, mes įdėjome žymeklį į apatiniame dešiniajame langelio kampe. Jos transformacija į pildymo žymeklį, turintis mažo kryžiaus išvaizdą. Spustelėjome kairįjį pelės mygtuką ir vilkite užpildymo žymeklį iki stulpelio galo.
- Kaip matote, visas stulpelis yra užpildytas duomenimis, apibūdinančiais pajamų dalį nuo kiekvieno produkto įgyvendinimo. Tačiau konkretaus sunkio vertė rodoma skaitmeniniu formatu, ir mes turime jį paversti procentais. Norėdami tai padaryti, atkreipkite dėmesį į stulpelio "specifinio svorio" turinį. Tada mes perkeliame į skirtuką "Namai". Apie juostos grupės grupėje yra lauko rodymo duomenų formatu. Pagal nutylėjimą, jei nesukūrėte papildomų manipuliacijų, ten turi būti įdiegta "bendras" formatu. Spustelėkite piktogramą į trikampį, esantį šioje srityje dešinėje. Į formatų sąrašą pasirinkite "procentais" poziciją.
- Kaip matome, visos stulpelių vertės buvo transformuotos į procentines vertes. Kadangi turėtų būti, 100% nurodyta "viso" eilutėje. Prekių dalis tikimasi stulpelyje nuo didesnio iki mažesnio.
- Dabar turėtume sukurti stulpelį, kuriame būtų rodoma sukaupta dalis su augančiu rezultatais. Tai yra, kiekvienoje eilutėje į individualų konkretų svorį konkretaus produkto bus pridėta visų šių produktų, kurie yra aukščiau esančiame sąraše, dalis. Pirmųjų prekių sąraše (3 produktas), individuali dalis ir sukaupta dalis bus lygi, tačiau visi po individualaus rodiklio turės pridėti sukauptą ankstesnio sąrašo elemento dalį.
Taigi, pirmojoje eilutėje mes perkeliami į kolonėlę "sukaupta dalis" stulpelio "specifinio" rodiklis.
- Toliau nustatykite žymeklį į antrą "sukauptos dalies" stulpelio langelį. Čia turime taikyti formulę. Mes įdėjome ženklą "lygi" ir sulenkite ląstelių "tos pačios linijos proporciją" ir ląstelių "sukauptos dalies turinį iš aukščiau esančios eilutės turinį. Visos nuorodos yra rezervuotos santykinės, ty mes nesukuriame jokių manipuliacijų su jais. Po to atlikite paspaudę mygtuką Enter, kad būtų rodomas galutinis rezultatas.
- Dabar jūs turite nukopijuoti šią formulę šio stulpelio ląstelėse, kurios yra žemiau. Norėdami tai padaryti, mes naudojame pildymo žymeklį, į kurį jau naudojame, kai kopijuojate formulę akcijų kolonėlėje. Tuo pačiu metu eilutė "Iš viso" nėra būtina, nes sukauptas rezultatas yra 100% bus rodomas paskutiniame produkte iš sąrašo. Kaip matote, visi mūsų stulpelio elementai po to buvo užpildyta.
- Po to sukurkite stulpelį "Grupė". Turėsime grupuoti prekes pagal A, B ir C kategorijos pagal nurodytą sukauptą akciją. Prisimename, visi elementai yra platinami pagal grupes pagal šią schemą:
- A - iki 80%;
- B - taip 15%;
- C - Likusi 5%.
Taigi, visos prekės, kurios sukaupė konkretaus svorio dalį, yra iki 80% sienos, priskirsime A kategoriją. Prekės su sukauptu konkrečiu svoriu nuo 80% iki 95% priskiriami B kategorijai. Likusi Prekių grupė, kurių vertė yra didesnė nei 95% sukaupto specifinio sunkio, mes priskiriame C kategoriją.
- Siekiant aiškumo, galite užpildyti nurodytas grupes su skirtingomis spalvomis. Bet tai yra valia.
Taigi, mes sumušėme svarbų lygį, naudojant ABC analizę. Naudojant kai kuriuos kitus metodus, kaip minėta pirmiau, jis naudojamas įsilaužti į didesnį grupių skaičių, tačiau šioje byloje pažeidimo principas išlieka beveik nepakitęs.
Pamoka: Rūšiavimas ir filtravimas Excel
2 metodas: naudojant sudėtingą formulę
Žinoma, rūšiavimo naudojimas yra labiausiai paplitęs būdas atlikti ABC analizę "Excele". Tačiau kai kuriais atvejais būtina atlikti šią analizę be pertvarkymo linijų vietose šaltinio lentelėje. Šiuo atveju, sudėtinga formulė ateis į gelbėjimo. Pavyzdžiui, mes naudosime tą patį šaltinio lentelę kaip pirmuoju atveju.
- Mes įtraukiame į šaltinio lentelę, kurioje yra prekių pavadinimas ir pajamos iš kiekvieno iš jų pardavimo, stulpelyje "Grupė". Kaip matote, šiuo atveju, mes negali pridėti stulpelių su individualių ir kaupiamų frakcijų skaičiavimo.
- Gaminame pirmąjį ląstelių paskirstymą grupės stulpelyje, po to atliksite paspaudę mygtuką "Įterpti funkciją", esantį šalia formulės eilutės.
- Magistro funkcijų aktyvavimas. Perkeliame į "Nuorodos ir matricos" kategoriją. Pasirinkite funkciją "pasirinkimas". Paspauskite mygtuką "OK".
- Įjungtas žaidimo argumento langas. Sintaksė pateikiama taip:
= Pasirinkimas (numeris_intex; vertė1; vertė2; ...)
Šios funkcijos užduotis yra vienos iš nurodytų verčių panaikinimas, priklausomai nuo indekso numerio. Vertybių skaičius gali pasiekti 254, tačiau mums reikės tik trys pavadinimai, atitinkantys ABC analizės kategorijas: A, B, C. Mes galime nedelsiant įvesti simbolį "B" lauke "B". "Laukas, laukas" Value3 "-" C ".
- Tačiau su argumentu "indekso numeris" turės būti kruopščiai tinkami kuriant kelis papildomus ūkio subjektus. Įdėkite žymeklį į lauką "Indekso numeris". Toliau spustelėkite piktogramą su trikampiu, į kairę nuo mygtuko "Įterpti funkcija". Atsidaro naujai naudojamų operatorių sąrašas. Mums reikia paieškos funkcijos. Kadangi tai nėra sąraše, tada spustelėjome užrašą "Kitos funkcijos ...".
- Dar kartą pradėtas funkcijų vedlio lango langas. Vėlgi, eikite į "Nuorodos ir matricos" kategoriją. Mes randame "paieškos tarybos" poziciją, paspauskite jį ir paspauskite mygtuką "OK".
- Atsidaro paieškos operatoriaus argumentų argumentai. Jos sintaksė turi tokią formą:
= Paieškos taryba (Search_name; žiūrėti__Massing; Type_Station)
Šios funkcijos tikslas yra apibrėžti nurodyto elemento pozicijos numerį. Tai yra tik tai, ko mums reikia "indekso numeriu" lauko funkcija.
Lauke "Array" lauke, galite nedelsiant užduoti šią išraišką:
{0: 0.8: 0,95}
Tai turėtų būti būtent garbanotiems skliausteliuose, kaip masyvo formulė. Sunku atspėti, kad šie skaičiai (0; 0,8; 0,95) žymi sukauptos grupės ribas.
"Palyginimų tipas" laukas nėra privalomas ir šiuo atveju mes to nepildysime.
Lauke "antroji vertė" nustatykite žymeklį. Toliau vėl per pirmiau aprašytą piktogramą trikampio pavidalu, mes pereiname prie funkcijų vedlio.
- Šį kartą funkcijos vedlys, mes pereiname prie kategorijos "Matematinis". Pasirinkite pavadinimą "Silent" ir paspauskite mygtuką "OK".
- Bus pradėtas funkcijų argumentų langas. Nurodytas operatorius apibendrina ląsteles, kurios atitinka tam tikrą sąlygą. Jos sintaksė yra:
= Tylus (diapazonas; kriterijus; diapazonas_suming)
Lauke "Range" įveskite stulpelio "pajamų" adresą. Šiems tikslams nustatykite žymeklį lauke, o tada paspaudę kairįjį pelės mygtuką, pasirinkite visus atitinkamos stulpelio ląsteles, išskyrus vertę "Iš viso". Kaip matote, adresas nedelsiant pasirodė lauke. Be to, mes turime padaryti šį nuorodą absoliutus. Norėdami tai padaryti, gaminti savo paskirstymą ir spustelėkite F4 klavišą. Adresas buvo išleistas dolerio ženklais.
Lauke "kriterijus" turime nustatyti sąlygą. Įveskite šią išraišką:
">"&
Tada po to įvesime pirmosios "pajamų" kolonėlės pirmosios ląstelės adresą. Šiame adreso absoliučiame adresu koordinuoja horizontaliai, pridedame dolerio ženklą nuo klaviatūros priešais laišką. Vertikalios koordinatės palieka santykinį, tai yra, prieš skaitmenį neturėtų būti jokio ženklo.
Po to mes nespaudžiame "OK" mygtuko ir spustelėkite paieškos funkcijos pavadinimą formulės eilutėje.
- Tada grįšime prie paieškos lentos argumentų. Kaip matome, lauke "rūko reikšmė", operatoriaus nustatyti duomenys tylėjo. Bet tai ne viskas. Eikite į šį lauką ir jau prideda prie turimų duomenų, kad pridėtumėte "+" ženklą be kabučių. Tada pristatome pirmosios "pajamų" kolonėlės "pajamų" adresą. Ir vėl mes darome koordinates horizontaliai su absoliučiu ir vertikaliai palikti santykinį.
Be to, mes paimame visą lauko "norimą vertę" turinį skliausteliuose, po kurio mes įdėti iš padalijimo ženklą ("/"). Po to vėl per trikampio piktogramą eikite į funkcijų pasirinkimo funkciją.
- Kaip ir paskutinį kartą važiavimo funkcijos vedlyje, mes ieškome reikiamo operatoriaus "matematinėje" kategorijoje. Šį kartą norima funkcija vadinama "sumas". Išryškiname ir paspauskite mygtuką "OK".
- Operatoriaus argumentai Atsidaro langas. Jo pagrindinis tikslas yra duomenų apibendrinimas ląstelėse. Šio operatoriaus sintaksė yra gana paprasta:
= Sumos (numeris1; Number2; ...)
Mūsų tikslais reikės tik "numerio1" laukas. Į jį įdiegiame kolonėlės "pajamų" koordinates, pašalinant ląstelę, kurioje yra rezultatai. Mes jau atlikome tokią operaciją "diapazono" funkcijos srityje. Kaip ir tuo metu, diapazono koordinatės padaryti absoliutus, pabrėžiant juos ir paspaudus F4 klavišą.
Po to lango apačioje spustelėkite mygtuką "Gerai".
- Kaip matote, įvestų funkcijų kompleksas padarė skaičiavimus ir išleido rezultatus pirmajame stulpelio "Group" ląstelėje. Pirmasis produktas buvo priskirtas grupei "A". Visa JAV taikoma formulė šiam skaičiavimui yra tokia:
= Pasirinkimas (paieškos lenta ((tylent ($ b $ 2: $ b $ 27; "& $ b2) + $ b2) / Sumos ($ b $ 2: $ b $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")
Tačiau, žinoma, kiekvienu atveju šios formulės koordinatės bus kitoks. Todėl jis negali būti laikomas universaliu. Tačiau, naudojant pirmiau pateiktą vadovą, galite įterpti bet kurios lentelės koordinates ir sėkmingai taikyti šį metodą bet kurioje situacijoje.
- Tačiau tai ne viskas. Apskaičiavome tik pirmojoje lentelės eilutėje. Norint visiškai užpildyti grupę "Group" stulpelį, turite nukopijuoti šią formulę žemiau esančiame diapazone (pašalinant eilutės "Iš viso" ląstelė), naudojant užpildymo žymeklį, nes jau padarėme daugiau nei vieną kartą. Atlikus duomenis, ABC analizė gali būti laikoma atlikta.
Kaip matome, rezultatai, gauti naudojant parinktį naudojant sudėtingą formulę, nesiskiria nuo tų rezultatų, kuriuos mes atlikome surūšiuoti. Visos prekės priskiriamos toms pačioms kategorijoms, tik tuo pačiu metu eilutės nepakeitė pradinės pozicijos.
Pamoka: "Excele" funkcijų magistras
"Excel" programa gali žymiai sušvelninti naudotojui ABC analizę. Tai pasiekiama naudojant tokią priemonę kaip rūšiavimą. Po to apskaičiuojamas individualus specifinis sunkumas, sukaupta dalis ir iš tikrųjų padalijimas į grupes. Tais atvejais, kai stalo eilučių pradinės pozicijos pokytis neleidžiamas, galite taikyti metodą naudodami sudėtingą formulę.