Viena no galvenajām vadības un loģistikas metodēm ir ABC analīze. Ar to jūs varat klasificēt uzņēmuma, preču, klientu utt. Saskaņā ar svarīguma pakāpi. Tajā pašā laikā, pēc svarīguma līmeņa, katrai no iepriekš minētajām vienībām ir piešķirta viena no trim kategorijām: A, B vai C. Excel programma ir bagāžas instrumentus, kas atvieglo šāda veida analīzes veikšanu. Apdomēsim, kā tos izmantot, un kāda ir ABC analīze.
Izmantojot ABC analīzi
ABC analīze ir sava veida pastiprināta un pielāgota mūsdienu nosacījumiem pareto principam. Saskaņā ar tās rīcības metodi visi analīzes elementi ir sadalīti trīs kategorijās atbilstoši svarīguma pakāpei:- A kategorija - elementi, kuru apvienojums ir vairāk nekā 80% no konkrētā smaguma;
- B kategorija - elementi, kuru kombinācija svārstās no 5% līdz 15% no konkrētā smaguma;
- C kategorija - atlikušie elementi, kuru kopējais kopsavilkums ir 5% un mazāk specifisks smagums.
Atsevišķi uzņēmumi izmanto vairāk uzlabotas metodes un sadalīt vienumus ne 3, bet 4 vai 5 grupās, bet mēs paļauties uz klasisko ABC analīzes shēmu.
1. metode: analīze ar šķirošanu
Excel ABC analīze tiek veikta, izmantojot šķirošanu. Visi priekšmeti ir sakārtoti no taukainiem līdz mazākiem. Tad katra elementa uzkrājamā daļa tiek aprēķināta, pamatojoties uz kuriem tā ir piešķirta noteikta kategorija. Let's uzzināt, kā noteiktā metodoloģija tiek izmantota praksē.
Mums ir tabula ar produktu sarakstu, kurus uzņēmums pārdod, un atbilstošo ieņēmumu skaitu no pārdošanas uz noteiktu laiku. Galda apakšdaļā ieņēmumu iznākums kopumā par visiem preču nosaukumiem. Ir uzdevums, izmantojot ABC analīzi, lauzt šīs preces grupās pēc to nozīmīgumiem uzņēmumam.
- Mēs izcelt tabulu ar kursoru, aizverot kreiso peles pogu, izņemot vāciņu un galīgo virkni. Dodieties uz cilni "Dati". Mēs noklikšķinām uz pogas "Kārtot", kas atrodas lentes rīkjoslā "Kārtot un filtrēt".
Jūs varat arī darīt atšķirīgi. Mēs piešķiram iepriekš minēto tabulu diapazonu, tad pāriet uz cilni "Mājas" un noklikšķiniet uz pogas "Kārtot un filtrēt", kas atrodas lentes rediģēšanas rīklodziņā. Šis saraksts ir aktivizēts, kurā ir aktivizēta pozīcija "pielāgojama šķirošana".
- Piemērojot kādu no iepriekš minētajām darbībām, tiek uzsākta šķirošanas iestatījumu logs. Mēs izskatām, ka "Mani dati satur galvenes" parametru tika iestatīts uz atzīmi. Ja tā nav, instalēt.
Laukā "Kolonnas" norādiet kolonnas nosaukumu, kurā ir ietverti ieņēmumu dati.
Laukā "Kārtot" ir jānorāda, uz kura tiks sakārtoti konkrēti kritēriji. Atstājiet iepriekš iestatījumus - "vērtības".
Laukā "Pasūtījums" piemīt pozīcija "dilstošā secībā".
Pēc norādīto uzstādījumu produkta loga apakšā noklikšķiniet uz pogas "OK".
- Pēc norādītās darbības veikšanas visi priekšmeti tika sakārtoti pēc ieņēmumiem no vairākiem līdz mazākiem.
- Tagad mums būtu jāaprēķina katra elementa īpatsvars vispārējam rezultātam. Izveidojiet papildu kolonnu šiem mērķiem, kurus mēs saucam par "akciju svaru". Šīs kolonnas pirmajā šūnā mēs ievietojam zīmi "=", pēc kura jūs norādāt saiti uz šūnu, kurā ieņēmumi no attiecīgā produkta īstenošanas ir. Tālāk noteiciet nodaļas zīmi ("/"). Pēc tam mēs norādām šūnas koordinātas, kas satur kopējo preču pārdošanas apjomu visā uzņēmumā.
Ņemot vērā to, ka norādītā formula mēs kopēt uz citām šūnām "Share" kolonnā ar uzpildes marķieri, adresi saiti uz elementu, kas satur galīgo summu ieņēmumu uz uzņēmumu, mums ir nepieciešams noteikt. Lai to izdarītu, veiciet saiti absolūti. Atlasiet norādītās šūnas koordinātas formulā un nospiediet taustiņu F4. Pirms koordinātēm, kā redzams, parādījās dolāra zīme, kas norāda, ka saite ir kļuvusi absolūta. Jāatzīmē, ka atsauce uz ieņēmumu summu pirmajā preču sarakstā (produkts 3), būtu jāpaliek relatīvi.
Pēc tam, lai veiktu aprēķinus, nospiediet taustiņu ENTER.
- Kā redzams, ieņēmumu īpatsvars no sarakstā norādītais pirmais produkts parādījās mērķa šūnā. Lai kopētu formulu zemāk esošajā diapazonā, mēs likst kursoru uz apakšējā labajā stūrī šūnas. Tās transformācija uzpildes marķieri, kura izskats ir neliels krusts. Mēs noklikšķinām pa kreiso peles pogu un velciet piepildījuma marķieri līdz kolonnas beigām.
- Kā redzat, visa kolonna ir piepildīta ar datiem, kas raksturo ieņēmumu īpatsvaru no katra produkta ieviešanas. Taču īpatnējā smaguma vērtība tiek parādīta skaitliskā formātā, un mums tas ir jāpārveido procentos. Lai to izdarītu, iezīmējiet kolonnas saturu "Īpašais svars". Tad mēs pāriet uz cilni "Mājas". Uz lentes grupas iestatījumu grupā ir lauka attēlošanas datu formāts. Pēc noklusējuma, ja jūs neradāt papildu manipulācijas, tur ir jāinstalē "General" formāts. Noklikšķiniet uz ikonas trijstūra formā, kas atrodas labajā pusē šajā laukā. Formātu sarakstā atlasiet pozīciju "procentos".
- Kā redzam, visas kolonnas vērtības tika pārveidotas par procentuālo vērtību vērtībām. Tā kā tam vajadzētu būt 100%, kas norādīts "Total" virknē. Preču īpatsvars ir sagaidāms kolonnā no lielāka līdz mazāka.
- Tagad mums vajadzētu izveidot kolonnu, kurā tiks parādīts uzkrātais īpatsvars ar pieaugošu rezultātu. Tas ir, katrā rindā uz konkrēta produkta individuālo konkrēto svaru pievienos visu to produktu īpatsvaru, kas atrodas iepriekš minētajā sarakstā. Pirmajām precēm sarakstā (produkts 3) individuālais īpatsvars un uzkrātais īpatsvars būs vienāds, bet visiem pēc individuālā rādītāja būs nepieciešams pievienot uzkrāto daļu no iepriekšējā saraksta elementa.
Tātad, pirmajā rindā mēs pārsūtām uz kolonnas "uzkrāto akciju" indikatoru kolonnas "specifika".
- Tālāk, iestatiet kursoru uz otro šūnu "uzkrāto akciju" kolonnu. Šeit mums ir jāpiemēro formula. Mēs ievietojam zīmi "EQUAL" un nolokām saturu šūnu "proporcija" no vienas līnijas un saturu šūnu "uzkrāto share" no virknes iepriekš. Visas atsauces ir rezervētas radinieks, tas ir, mēs neražojam nekādas manipulācijas ar viņiem. Pēc tam veiciet noklikšķiniet uz pogas Enter, lai parādītu galīgo rezultātu.
- Tagad jums ir nepieciešams, lai kopētu šo formulu šūnās šīs kolonnas, kas ir ievietotas zemāk. Lai to izdarītu, mēs izmantojam uzpildes marķieri, kuru mēs jau esam izmantojuši, kopējot formulu akciju kolonnā. Tajā pašā laikā, virkne "kopā" nav nepieciešama, jo uzkrātais rezultāts ir 100% tiks parādīts uz pēdējā produkta no saraksta. Kā jūs varat redzēt, visi mūsu kolonnas elementi pēc tam tika aizpildīti.
- Pēc tam izveidojiet kolonnu "Group". Mums būs nepieciešams grupēt preces pēc A, B un C kategorijas saskaņā ar norādīto uzkrāto akciju. Kā mēs atceramies, visi elementi tiek izplatīti pa grupām saskaņā ar šādu shēmu:
- A - līdz 80%;
- B - šādi 15%;
- C - atlikušie 5%.
Tādējādi visas preces, kas uzkrājās īpatsvars no konkrētā svara robežās līdz 80%, mēs piešķiram A kategorijas preces ar uzkrāto īpašo svaru no 80% līdz 95% tiek piešķirti B kategorijai. Atlikušo preču grupa, kuru vērtība ir vairāk nekā 95% no uzkrātās īpatnējā smaguma, mēs piešķiram C kategoriju.
- Skaidrību jūs varat aizpildīt norādītās grupas ar dažādām krāsām. Bet tas ir pēc vēlēšanās.
Tādējādi mēs lauza elementus svarīguma līmenī, izmantojot ABC analīzi. Lietojot dažus citus paņēmienus, kā minēts iepriekš, to izmanto, lai ielauztu lielāku skaitu grupu, bet šajā gadījumā pārkāpjot pārkāpumu.
Nodarbība: Šķirošana un filtrēšana programmā Excel
2. metode: kompleksa formula
Protams, šķirošanas izmantošana ir visizplatītākais veids, kā veikt ABC analīzi Excele. Bet dažos gadījumos ir nepieciešams veikt šo analīzi, nepārkārtojot līnijas vietās avota tabulā. Šajā gadījumā glābšanas laikā notiks sarežģīta formula. Piemēram, mēs izmantosim to pašu avota tabulu kā pirmajā gadījumā.
- Mēs pievienojam avota tabulu, kurā ir preču nosaukums un ieņēmumi no katra no tām pārdošanas, kolonnas "Group". Kā jūs varat redzēt, šajā gadījumā mēs nevaram pievienot kolonnas, aprēķinot individuālas un uzkrātās frakcijas.
- Mēs ražojam pirmo šūnu piešķiršanu grupas kolonnā, pēc tam jūs veicat klikšķi uz pogas "Ievietot funkciju", kas atrodas netālu no formulas rindas.
- Masters funkciju aktivizēšana. Mēs pāriet uz kategoriju "Saites un masīvi". Izvēlieties funkciju "Choice". Mēs veicam klikšķi uz pogas "OK".
- Spēles argumenta logs ir aktivizēts. Sintakse ir iesniegta šādi:
= Izvēle (numurs_intex; vērtība1; vērtība2; ...)
Šīs funkcijas uzdevums ir viena no norādītajām vērtībām atkarībā no indeksa numura. Vērtību skaits var sasniegt 254, bet mums būs nepieciešami tikai trīs vārdi, kas atbilst ABC analīzes kategorijām: A, B, C. Mēs varam nekavējoties ievadīt "A" simbolu "B" laukā "B "Lauks, lauks" vērtība3 "-" C ".
- Bet ar argumentu "indeksa numurs" būs rūpīgi jāpārbauda, veidojot vairākus papildu operatorus. Uzstādiet kursoru laukā "Index numurs". Pēc tam noklikšķiniet uz ikonas ar skatu uz trīsstūri, pa kreisi no "Ievietot funkciju" pogu. Atveras jaunizveidoto operatoru saraksts. Mums ir nepieciešama meklēšanas funkcija. Tā kā tas nav sarakstā, tad mēs noklikšķināsim uz uzrakstu "Citas funkcijas ...".
- Funkciju logs Wizard logs tiek atvērts vēlreiz. Atkal, dodieties uz kategoriju "Saites un masīvi". Mēs atrodam "meklēšanas kuģa" pozīciju, piešķiriet to un veiciet klikšķi uz pogas "OK".
- Atveras meklēšanas operatora argumentu argumenti. Tā sintakse ir šāda forma:
= Search Board (Search_name; Viewing__massive; type_station)
Šīs funkcijas mērķis ir definēt norādītā objekta pozīcijas numuru. Tas ir, tieši tas, kas mums ir nepieciešams, lai "indeksa numurs" ir izvēle.
Laukā "Listing masīvs", jūs varat uzreiz uzdot šādu izteiksmi:
{0: 0.8: 0,95}
Tam jābūt precīzāk cirtainiem kronšteiniem kā masīva formula. Nav grūti uzminēt, ka šie skaitļi (0; 0.8; 0.95) apzīmē uzkrāto daļu robežas starp grupām.
"Salīdzināšanas veids" lauks nav obligāts un šajā gadījumā mēs to neaizpildīsim.
Laukā "Otrā vērtība", iestatiet kursoru. Tālāk atkal cauri ikonai, kas aprakstīta iepriekš trijstūra veidā, mēs pāriet uz funkciju vedni.
- Šoreiz Funkciju vednī mēs pārejam uz kategoriju "Matemātiskā". Izvēlieties nosaukumu "Kluss" un nospiediet pogu "OK".
- Funkciju argumentu logs tiks uzsākts. Norādītais operators apkopo šūnas, kas atbilst noteiktā stāvoklī. Tā sintakse ir:
= Kluss (diapazons; kritērijs; Range_suming)
Laukā "Range" ievadiet kolonnas "ieņēmumu" adresi. Šim nolūkam mēs uzstādām kursoru laukā, un pēc tam, padarot klipu kreisās peles pogas, atlasiet visas šūnas atbilstošo kolonnu, izņemot vērtību "Kopā". Kā redzat, adrese nekavējoties parādījās laukā. Turklāt mums ir nepieciešams, lai padarītu šo saiti absolūtu. Lai to izdarītu, sagatavojiet tās piešķiršanu un noklikšķiniet uz F4 taustiņa. Adrese tika atbrīvota ar dolāru zīmēm.
Jo "kritērijs" laukā, mums ir jānosaka stāvoklis. Ievadiet šādu izteiksmi:
">"&
Tad tūlīt pēc tam, kad mēs ievadām "ieņēmumu" pirmās šūnas adresi. Mēs saskaņojam horizontāli šajā adresē absolūtā, pievienojot dolāra zīmi no tastatūras priekšā burtu. Vertikālās koordinātas atstāj relatīvo, tas ir, nav jābūt pierakstīšanās priekšā ciparu.
Pēc tam mēs nenospiežam pogu "OK" un noklikšķiniet uz meklēšanas funkcijas nosaukuma formulas virknē.
- Tad mēs atgriežam meklēšanas pārvaldes argumentu logu. Kā redzams, "miglas nozīmes" laukā, operatora noteiktie dati klusēja. Bet tas nav viss. Iet uz šo lauku un jau pievienojiet pieejamajiem datiem, lai pievienotu "+" zīmi bez pēdiņām. Tad mēs ieviest "ieņēmumu" kolonnas pirmās šūnas adresi. Un atkal mēs horizontāli saskaņojam ar absolūtu un vertikāli atstāj relatīvu.
Tālāk mēs veicam visu lauka "vēlamo vērtību" saturu iekavās, pēc kura mēs nodot dalījuma zīmi ("/"). Pēc tam atkal cauri trijstūra ikonai dodieties uz funkciju funkciju.
- Tāpat kā pēdējā reize braukšanas funkcijas vednī, mēs meklējam nepieciešamo operatoru "matemātiskā" kategorijā. Šoreiz vēlamo funkciju sauc par "summām". Mēs to iezīmējam un nospiediet pogu "OK".
- Tiek atvērts logs Operatora argumenti. Tās galvenais mērķis ir datu apkopojums šūnās. Šī operatora sintakse ir diezgan vienkārša:
= Summas (numurs1; numurs2; ...)
Mūsu vajadzībām būs nepieciešams tikai "numurs1" lauks. To ieviešot to kolonnas "ieņēmumu" koordinātas, novēršot šūnu, kas satur rezultātus. Mēs jau esam izpildījuši šādu darbību funkcijas "diapazona" laukā. Tāpat kā tajā laikā, diapazona koordinātas dara absolūti, izceļot tos un nospiežot F4 taustiņu.
Pēc tam, noklikšķiniet uz "OK" taustiņu apakšā loga.
- Kā redzat, ievadīto funkciju komplekss veica aprēķinu un izdeva rezultātu pirmajā ailes "Group" šūnā. Pirmajam produktam tika piešķirta grupa "A". Pilna formula, ko mēs pielietojām šim aprēķinam, ir šāds:
= Izvēle (meklēšanas padome ((kluss ($ B $ 2: $ B $ 27; ">" "& $ B2) + $ B2) / Summas ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")
Bet, protams, katrā gadījumā koordinātas šajā formulā būs atšķirīga. Tāpēc to nevar uzskatīt par universālu. Bet, izmantojot iepriekš norādīto rokasgrāmatu, jūs varat ievietot jebkuras tabulas koordinātas un veiksmīgi piemērot šo metodi jebkurā situācijā.
- Tomēr tas nav viss. Mēs aprēķinājām tikai pirmajā tabulas rindā. Lai pilnībā aizpildītu grupu "Group" kolonnu, jums ir nepieciešams, lai kopētu šo formulu diapazonā (novēršot šūnu virknes "kopā"), izmantojot uzpildes marķieri, kā mēs jau esam darījuši vairāk nekā vienu reizi. Pēc datu veikšanas ABC analīzi var uzskatīt par izpildītu.
Kā mēs redzam, rezultāti, kas iegūti, izmantojot iespēju, izmantojot kompleksu formulu, neatšķiras no šiem rezultātiem, ko mēs veicam, šķirojot. Visām precēm tiek piešķirtas tādas pašas kategorijas, tikai tajā pašā laikā rindas nemainīja savu sākotnējo pozīciju.
Nodarbība: Funkciju maģistrs Excele
Excel programma var ievērojami atvieglot ABC analīzi lietotājam. Tas tiek panākts, izmantojot šādu rīku kā šķirošanu. Pēc tam aprēķina individuālo īpatnējo smagumu, uzkrāto daļu un faktiski, sadalot grupās. Gadījumos, kad izmaiņas sākotnējā pozīcijā tabulā nav atļauta, jūs varat piemērot metodi, izmantojot sarežģītu formulu.