Analiza ABC në Excel

Një nga metodat kryesore të menaxhimit dhe logjistikës është analiza ABC. Me të, ju mund të klasifikoni burimet e ndërmarrjes, mallrave, klientëve, etj. Sipas shkallës së rëndësisë. Në të njëjtën kohë, nga niveli i rëndësisë, secila nga njësia e mësipërme është caktuar një nga tre kategoritë: A, B ose C. Excel ka në mjetet e saj të bagazheve që e bëjnë më të lehtë kryerjen e këtij lloji të analizës. Le të kuptojmë se si t'i përdorim ato, dhe çfarë është analiza ABC.

Duke përdorur analizën ABC

Analiza ABC është një lloj i zgjeruar dhe i përshtatur për kushtet moderne për parimin e pareto. Sipas metodës së sjelljes së saj, të gjitha elementet e analizës ndahen në tre kategori sipas shkallës së rëndësisë:

• Kategoria A - Elementet që kanë një kombinim prej më shumë se 80% të gravitetit specifik;
• Kategoria B - Elementet kombinimi i të cilave varion nga 5% në 15% të gravitetit specifik;
• Kategoria C - Elementet e mbetura, totali i përgjithshëm i të cilave është 5% dhe më pak gravitetit specifik.

Kompanitë e ndara përdorin teknika më të avancuara dhe ndani sendet jo 3, por me 4 ose 5 grupe, por ne do të mbështetemi në skemën e analizës klasike ABC.

Metoda 1: Analiza me klasifikim

Analiza Excel ABC kryhet duke përdorur klasifikimin. Të gjitha sendet janë të renditura nga yndyrat në më pak. Atëherë pjesa akumuluese e secilit element llogaritet, në bazë të të cilave është caktuar një kategori e caktuar. Le të zbulojmë se si përdoret metodologjia e specifikuar në praktikë.

Ne kemi një tabelë me një listë të produkteve që shet kompania, dhe numri përkatës i të ardhurave nga shitja e tyre për një periudhë të caktuar kohe. Në pjesën e poshtme të tabelës, rezultati i të ardhurave në përgjithësi për të gjitha emrat e mallrave. Ekziston një detyrë duke përdorur analizën ABC, thyejnë këto mallra në grupe me rëndësinë e tyre për ndërmarrjen.

1. Ne theksojmë një tavolinë me kursorin duke mbyllur butonin e majtë të miut, duke përjashtuar kapakun dhe vargun përfundimtar. Shko te tab "Data". Ne klikoni në butonin "Sort", të vendosura në "Sort and Filter" toolbar në kasetë.

   

   Ju gjithashtu mund të bëni ndryshe. Ne shpërndajmë gamën e tabelës së mësipërme, pastaj lëvizim në skedën "Home" dhe klikoni në butonin "Sort and Filter" të vendosura në kutinë e veglave të redaktimit në kasetë. Lista është aktivizuar në të cilën është aktivizuar pozicioni "Sorting Customizable".



2. Kur aplikoni ndonjë nga veprimet e mësipërme, lançon dritarja e cilësimeve të klasifikimit. Ne shikojmë në mënyrë që "të dhënat e mia të përmbajnë parametrin e kokës" u vendosën në një shenjë kontrolli. Në rastin e mungesës së saj, instaloni.

   Në fushën "kolona", specifikoni emrin e kolonës në të cilën të dhënat e të ardhurave janë të përfshira.

   Në fushën "Sort", ju duhet të specifikoni, në të cilën do të zgjidhen kritere specifike. Lëreni cilësimet e paracaktuara - "Vlerat".

   Në fushën "rendi", shfaqin pozicionin "zbritës".

   Pas produktit të cilësimeve të specifikuara, klikoni në butonin "OK" në pjesën e poshtme të dritares.



3. Pas kryerjes së veprimit të specifikuar, të gjitha sendet u renditën nga të ardhurat nga më shumë në një më të vogël.



4. Tani duhet të llogarisim përqindjen e secilit prej elementeve për një rezultat të përgjithshëm. Krijo një kolonë shtesë për këto qëllime, të cilat ne e quajmë "peshë të aksioneve". Në qelinë e parë të kësaj kolone, ne vendosim shenjën "=", pas së cilës ju specifikoni një lidhje me qelizën në të cilën është shuma e të ardhurave nga zbatimi i produktit përkatës. Tjetra, vendosni shenjën e ndarjes ("/"). Pas kësaj, ne tregojmë koordinatat e qelizës, e cila përmban shumën totale të shitjes së mallrave në të gjithë ndërmarrjen.

   Duke pasur parasysh faktin se formula e specifikuar ne do të kopjojmë në qelizat e tjera të kolonës së "aksioneve" nga shënuesi i mbushjes, adresa e lidhjes me elementin që përmban shumën përfundimtare të të ardhurave në ndërmarrje, ne duhet të rregullojmë. Për ta bërë këtë, bëni një lidhje absolute. Zgjidhni koordinatat e qelizës së specifikuar në formulën dhe shtypni butonin F4. Para koordinatave, siç e shohim, shfaqet një shenjë dollarësh, e cila tregon se lidhja është bërë absolute. Duhet të theksohet se referimi ndaj shumës së të ardhurave të së parës në listën e mallrave (produkti 3) duhet të mbetet i afërm.

   Pastaj, për të bërë llogaritjet, shtypni butonin Enter.



5. Siç e shohim, përqindja e të ardhurave nga produkti i parë i specifikuar në listë u shfaq në qelinë e synuar. Për të kopjuar formulën në rangun e mëposhtëm, ne e vendosim kursorin në këndin e poshtëm të djathtë të qelizës. Transformimi i saj në shënuesin e mbushjes, duke pasur një vështrim të një kryq të vogël. Ne klikoni butonin e majtë të miut dhe terhiq shënuesin e mbushur deri në fund të kolonës.



6. Siç mund ta shihni, e gjithë kolona është e mbushur me të dhëna që karakterizojnë përqindjen e të ardhurave nga zbatimi i çdo produkti. Por vlera e gravitetit specifik shfaqet në formatin numerik, dhe ne duhet ta transformojmë atë në përqindje. Për ta bërë këtë, të nxjerrë në pah përmbajtjen e kolonës "Pesha specifike". Pastaj shkojmë në skedën "në shtëpi". Në kasetë në grupin e grupeve të grupit ka një fushë që shfaq formatin e të dhënave. By default, nëse nuk keni prodhuar manipulime shtesë, formati "i përgjithshëm" duhet të instalohet atje. Klikoni mbi ikonën në formën e një trekëndëshi të vendosur në të djathtë të kësaj fushe. Në listën e formateve, zgjidhni pozicionin "përqind".



7. Siç e shohim, të gjitha vlerat e kolonës u shndërruan në vlera përqindjeje. Siç duhet të jetë, 100% tregohen në vargun e "totalit". Pjesa e mallrave pritet në kolonën nga një më e madhe në një më të vogël.



8. Tani ne duhet të krijojmë një kolonë në të cilën do të shfaqet akumulimi me një rezultat në rritje. Kjo është, në çdo rresht me peshën specifike individuale të një produkti të caktuar do të shtohet përqindja e të gjitha këtyre produkteve që janë të vendosura në listën e mësipërme. Për mallrat e para në listë (produkti 3), proporcioni individual dhe pjesa e akumuluar do të jenë të barabarta, por të gjithë pas treguesit individual do të duhet të shtojnë pjesën e akumuluar të elementit të mëparshëm të listës.

   Pra, në rreshtin e parë ne jemi transferuar në kolonën "akumuluar akumuluar" treguesin e kolonës "specifike".



9. Tjetra, vendosni kursorin në qelizën e dytë të kolonës "akumuluar akumuluar". Këtu duhet të aplikojmë formulën. Ne vendosim shenjën "të barabartë" dhe hedhin përmbajtjen e qelizës "proporcioni" i së njëjtës linjë dhe përmbajtjen e qelizës "akumuluar akumuluar" nga vargu i mësipërm. Të gjitha referencat janë të rezervuara, domethënë, ne nuk prodhojmë manipulime me ta. Pas kësaj, kryeni një klikim në butonin ENTER për të shfaqur rezultatin përfundimtar.



10. Tani ju duhet të kopjoni këtë formulë në qelizat e kësaj kolone, të cilat janë vendosur më poshtë. Për ta bërë këtë, ne përdorim shënuesin e mbushjes për të cilën ne kemi përdorur tashmë kur kopjojmë formulën në kolonën e aksioneve. Në të njëjtën kohë, string "total" nuk është i nevojshëm, pasi rezultati i akumuluar është 100% do të shfaqet në produktin e fundit nga lista. Siç mund ta shihni, të gjitha elementet e kolonës sonë pas kësaj u mbushën.



11. Pas kësaj, krijoni një kolonë "grup". Ne do të duhet të grupojmë mallra sipas kategorisë A, B dhe C sipas pjesës së specifikuar të akumuluar. Siç kujtojmë, të gjitha elementet shpërndahen nga grupet sipas skemës së mëposhtme:
    • A - deri në 80%;
    • B - 15% e mëposhtme;
    • C - 5% e mbetur.

    Kështu, të gjitha mallrat që akumulohen pjesë të peshës specifike të të cilave janë brenda kufirit deri në 80%, ne caktojmë kategorinë A. Mallrat me peshën specifike të akumuluar nga 80% në 95% janë caktuar në kategorinë B. Pjesa e mbetur Grupi i mallrave me vlerë prej më shumë se 95% të gravitetit specifik të akumuluar ne caktojmë një kategori C.



12. Për qartësi, ju mund të plotësoni grupet e specifikuara me ngjyra të ndryshme. Por është në vullnet.

Kështu, ne thyen elementet në nivelin e rëndësisë, duke përdorur analizën ABC. Kur përdorni disa teknika të tjera, siç u përmend më lart, përdoret për të thyer në një numër më të madh grupesh, por parimi i thyerjes në këtë rast mbetet pothuajse i pandryshuar.

Mësim: Renditja dhe filtrimi në Excel

Metoda 2: Përdorimi i një formule komplekse

Natyrisht, përdorimi i klasifikimit është mënyra më e zakonshme për të kryer analizën ABC në excele. Por në disa raste është e nevojshme të kryhet kjo analizë pa rirregullimin e linjave në vende në tabelën burimore. Në këtë rast, një formulë komplekse do të vijë në shpëtim. Për shembull, ne do të përdorim të njëjtën tabelë burimi si në rastin e parë.

1. Ne shtojmë tabelën burimore që përmbajnë emrin e mallrave dhe të ardhurat nga shitja e secilit prej tyre, kolona "Grupi". Siç mund ta shihni, në këtë rast, ne nuk mund të shtojmë kolona me llogaritjen e fraksioneve individuale dhe akumuluese.



2. Ne prodhojmë shpërndarjen e parë të qelizës në kolonën e grupit, pas së cilës ju kryeni një klikim në butonin "Funksioni i futur" që ndodhet pranë rreshtit të formulës.



3. Master aktivizimi i funksioneve. Ne shkojmë në kategorinë "Lidhjet dhe vargjet". Zgjidhni funksionin "zgjedhje". Ne bëjmë një klikim në butonin "OK".



4. Dritarja e argumentit të lojës është aktivizuar. Sintaksa paraqitet si më poshtë:

   = Përzgjedhja (numri_intex; vlerë1; vlera2; ...)

   Detyra e kësaj funksioni është tërheqja e një prej vlerave të specifikuara, në varësi të numrit të indeksit. Numri i vlerave mund të arrijë 254, por ne do të duhet vetëm tre emra që korrespondojnë me kategoritë ABC-analiza: A, B, C. ne mund të hyjmë menjëherë në "një" simbol në fushën "B" në "B" "fushë, fushë" vlerë3 "-" c ".



5. Por me argumentin "numri i indeksit" do të duhet të jetë tërësisht i shqetësuar duke ndërtuar disa operatorë shtesë. Instaloni kursorin në fushën e "Numrit të Indeksit". Tjetra, klikoni mbi ikonën me një pamje të trekëndëshit, në të majtë të butonit "Funksioni i futur". Hapet një listë e operatorëve të sapo përdorur. Ne kemi nevojë për një funksion të kërkimit. Meqë nuk është në listë, atëherë ne klikojmë në mbishkrimin "Funksione të tjera ...".



6. Dritarja e dritares së Wizard Funksionet fillon përsëri. Përsëri, shkoni në kategorinë "Lidhjet dhe vargjet". Ne gjejmë pozitën e "Bordit të Kërkimit", të ndajë atë dhe të bëjmë një klikim në butonin "OK".



7. Argumentet e argumenteve të operatorit të kërkimit hapen. Sintaksa e tij ka formën e mëposhtme:

   = Bordi i Kërkimit (Search_NAME; shikimi__massive; tip_station)

   Qëllimi i këtij funksioni është përcaktimi i numrit të pozicionit të sendit të specifikuar. Kjo është, vetëm ajo që na nevojitet për fushën "Numri i Indeksit" të shfaqë një zgjedhje.

   Në fushën "Array", ju mund të kërkoni menjëherë shprehjen e mëposhtme:

   {0: 0.8: 0,95}

   Duhet të jetë pikërisht në kllapa kaçurrelë, si një formulë e grupit. Nuk është e vështirë të mendosh se këto numra (0; 0.8; 0.95) tregojnë kufijtë e pjesës së akumuluar midis grupeve.

   Fusha "Lloji i krahasimit" nuk është i detyrueshëm dhe në këtë rast nuk do ta plotësojmë.

   Në fushën "Vlera e Dytë", vendosni kursorin. Tjetra përsëri përmes ikonës së përshkruar më sipër në formën e një trekëndëshi, ne kalojmë në magjistarin e funksioneve.



8. Këtë herë në funksionet e funksioneve, ne bëjmë lëvizjen në kategorinë "matematikore". Zgjidhni emrin "Silent" dhe shtypni butonin "OK".



9. Do të lançohet dritarja e argumenteve të funksioneve. Operatori i specifikuar përmbledh qelizat që plotësojnë gjendjen e caktuar. Sintaksa e saj është:

   = Heshtur (varg; kriter; varg_suming)

   Në fushën "varg", futni adresën e kolonës së "të ardhurave". Për këto qëllime, ne vendosim kursorin në fushë, dhe pastaj duke e bërë klipin e butonit të majtë të miut, zgjidhni të gjitha qelizat e kolonës përkatëse, duke përjashtuar vlerën "total". Siç mund ta shihni, adresa menjëherë u shfaq në fushë. Përveç kësaj, ne duhet ta bëjmë këtë lidhje absolute. Për ta bërë këtë, prodhoni ndarjen e saj dhe klikoni në çelësin F4. Adresa u lirua me shenja të dollarit.

   Në fushën "Kriteri", ne duhet të vendosim një gjendje. Shkruani shprehjen e mëposhtme:

   ">"&

   Pastaj menjëherë pas saj ne hyjmë në adresën e qelizës së parë të kolonës së "të ardhurave". Ne bëjmë koordinatat horizontalisht në këtë adresë absolute, duke shtuar shenjën e dollarit nga tastiera në frontin e letrës. Koordinatat vertikale lënë të afërm, domethënë, nuk duhet të ketë asnjë shenjë para shifrës.

   Pas kësaj, ne nuk shtypim butonin "OK" dhe klikoni mbi emrin e funksionit të kërkimit në vargun e formulës.



10. Pastaj ne kthehemi në dritaren e argumenteve të bordit të kërkimit. Siç e shohim, në fushën e "kuptimit të mjegullt", të dhënat e përcaktuara nga operatori ishte i heshtur. Por kjo nuk është e gjitha. Shko në këtë fushë dhe tashmë i shton të dhënave në dispozicion për të shtuar një shenjë "+" pa kuotat. Pastaj ne prezantojmë adresën e qelizës së parë të kolonës së "të ardhurave". Dhe përsëri bëjmë koordinatat horizontalisht me të afërm absolut dhe vertikalisht.

    Tjetra, ne marrim të gjitha përmbajtjet e fushës "vlerën e dëshiruar" në kllapa, pas së cilës kemi vënë shenjën e ndarjes ("/"). Pas kësaj, përsëri përmes ikonës së trekëndëshit, shkoni në funksion të përzgjedhjes së funksioneve.



11. Ashtu si herën e fundit në funksionin e funksionit të funksionit, ne jemi duke kërkuar për operatorin e nevojshëm në kategorinë "matematik". Këtë herë funksioni i dëshiruar quhet "shuma". Ne e nxjerrim në pah atë dhe shtypni butonin "OK".



12. Hapet dritarja e argumenteve të operatorit. Qëllimi i tij kryesor është përmbledhja e të dhënave në qeliza. Sintaksa e këtij operatori është mjaft e thjeshtë:

    = Shuma (numri1; numër2; ...)

    Për qëllimet tona, vetëm fusha "numër1" do të jetë e nevojshme. Ne prezantojmë në të koordinatat e kolonës së "të ardhurave", duke eliminuar qelizën që përmban rezultatet. Ne kemi kryer tashmë një operacion të tillë në fushën "varg" të funksionit. Ashtu si në atë kohë, koordinatat e vargut bëjnë absolute, duke i theksuar ato dhe duke shtypur butonin F4.

    Pas kësaj, klikoni në çelësin "OK" në pjesën e poshtme të dritares.



13. Siç mund ta shihni, kompleksi i funksioneve të dhëna bëri një llogaritje dhe lëshoi ​​rezultatin në qelizën e parë të kolonës "Grupi". Produkti i parë u caktua një grup "a". Formula e plotë e aplikuar nga ne për këtë llogaritje është si më poshtë:

    = Zgjedhja (Bordi i Kërkimit ((i heshtur ($ b 2: $ b $ 27; ">" & $ b2) + $ b2) / Shumat ($ b $ 2: $ b $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")

    Por, natyrisht, në çdo rast, koordinatat në këtë formulë do të jenë të ndryshme. Prandaj, nuk mund të konsiderohet universale. Por, duke përdorur manualin që është dhënë më lart, ju mund të futni koordinatat e çdo tabele dhe të aplikoni me sukses këtë metodë në çdo situatë.



14. Megjithatë, kjo nuk është e gjitha. Ne llogaritet vetëm për rreshtin e parë të tabelës. Në mënyrë që të plotësoni plotësisht kolonën "Grupi", ju duhet të kopjoni këtë formulë në rangun e mëposhtëm (duke eliminuar qelizën e "totalit") duke përdorur shënuesin e mbushjes, siç kemi bërë tashmë më shumë se një herë. Pas marrjes së të dhënave, analiza ABC mund të konsiderohet e kryer.

Siç e shohim, rezultatet e fituara duke përdorur opsionin duke përdorur një formulë komplekse nuk janë të ndryshme nga ato rezultate që ne jemi kryer duke renditur. Të gjitha mallrat u caktohen të njëjtat kategori, vetëm në të njëjtën kohë rreshtat nuk ndryshuan pozicionin e tyre fillestar.

Mësim: Master i Funksioneve në Excele

Programi Excel është në gjendje të lehtësojë ndjeshëm analizën ABC për përdoruesin. Kjo arrihet duke përdorur një mjet të tillë si klasifikimi. Pas kësaj, graviteti individual specifik, akumulimi i akumuluar dhe, në të vërtetë, ndarja në grupe llogaritet. Në rastet kur ndryshimi në pozicionin fillestar të rreshtave në tabelë nuk lejohet, mund të aplikoni metodën duke përdorur një formulë komplekse.