Përdorimi i analizës ABC në Microsoft Excel

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

Duke përdorur analizën ABC

Analiza ABC është një lloj i përmirësuar dhe përshtatur me versionin e kushteve moderne të parimit Pareto. Sipas metodologjisë së zbatimit të tij, të gjithë elementët e analizës ndahen në tre kategori sipas shkallës së rëndësisë:

• kategori A - elemente që kanë në total më shumë se 80% graviteti specifik;
• kategori B - elemente, kombinimi i të cilave është nga 5% në 15% graviteti specifik;
• kategori C - elementet e mbetura, kombinimi total i të cilave është 5% dhe graviteti më pak specifik.

Disa ndërmarrje aplikojnë teknika më të avancuara dhe zbërthejnë elementë në 3 ose 4 ose 5 grupe, por ne do të mbështetemi te skema klasike e analizës ABC.

Metoda 1: analizimi i klasifikimit

Në Excel, analiza ABC kryhet duke përdorur renditje. Të gjitha artikujt janë renditur nga më i madhi në më i vogël. Pastaj, llogaritet graviteti specifik kumulativ i secilit element, bazuar në të cilin i është caktuar një kategori e caktuar. Le të zbulojmë, duke përdorur një shembull specifik, si përdoret kjo teknikë në praktikë.

Ne kemi një tabelë me një listë të mallrave që kompania shet, dhe shumën përkatëse të të ardhurave nga shitja e tyre për një periudhë të caktuar kohore. Në fund të tabelës, të ardhurat totale për të gjithë artikujt e mallrave janë goditur. Detyra, duke përdorur analizën ABC, është që këto produkte të ndahen në grupe sipas rëndësisë së tyre për ndërmarrjen.

1. Zgjidhni tabelën me kursorin e të dhënave, duke mbajtur butonin e majtë të miut, duke përjashtuar kokën dhe rreshtin përfundimtar. Shko te skeda "Data". Klikoni në butonin. "Rendit"të vendosura në bllokun e mjeteve Rendit dhe filtro në shirit.

   

   Ju gjithashtu mund të bëni ndryshe. Zgjidhni gamën e mësipërme të tabelës, pastaj zhvendoseni në skedë "Home" dhe klikoni në butonin Rendit dhe filtrotë vendosura në bllokun e mjeteve "Editing" në shirit. Ashtë aktivizuar një listë në të cilën zgjedhim një pozicion në të. Renditja me porosi.



2. Kur aplikoni ndonjë nga veprimet e mësipërme, fillon dritarja e cilësimeve të renditjes. Ne shikojmë në mënyrë që rreth parametrit "Të dhënat e mia përmbajnë tituj" është vendosur një shenjë kontrolli. Në rast të mungesës së saj, instaloni.

   Në fushë "Kolona" tregoni emrin e kolonës që përmban të dhënat e të ardhurave.

   Në fushë "Rendit" duhet të specifikoni me cilin kriter specifik do të kryhet renditja. Ne lëmë parametrat e paracaktuara - "Vlerat".

   Në fushë "Urdhër" vendos një pozicion "Zbritje".

   Pasi të keni bërë cilësimet e specifikuara, klikoni në butonin "OK" në fund të dritares.



3. Pas kryerjes së veprimit të specifikuar, të gjithë elementët janë renditur sipas të ardhurave nga më të mëdhenjtë në më të vegjlit.



4. Tani duhet të llogarisim peshën specifike të secilit prej elementeve për totalin. Ne krijojmë një kolonë shtesë për këto qëllime, të cilat do t'i quajmë "Graviteti specifik". Në qelizën e parë të kësaj kolone, vendosni një shenjë "=", pas së cilës ne tregojmë një lidhje me qelizën në të cilën ndodhet shuma e të ardhurave nga shitja e produktit përkatës. Tjetra, vendosni shenjën e ndarjes ("/"). Pas kësaj, tregoni koordinatat e qelizës, e cila përmban shumën totale të shitjeve të mallrave në të gjithë ndërmarrjen.

   Duke pasur parasysh faktin se ne do të kopjojmë formulën e specifikuar në qelizat e tjera në kolonë "Graviteti specifik" duke përdorur shënuesin e mbushjes, atëherë duhet të rregullojmë adresën e lidhjes për elementin që përmban shumën totale të të ardhurave për ndërmarrjen. Për ta bërë këtë, bëjeni lidhjen absolute. Zgjidhni koordinatat e qelizës së specifikuar në formulë dhe shtypni tastin F4. Përpara koordinatave, siç shohim, u shfaq një shenjë dollari, që tregon se lidhja është bërë absolute. Duhet të theksohet se lidhja me vlerën e të ardhurave të artikullit të parë në listë (Produkti 3) duhet të mbetet relativ.

   Pastaj, për të bërë llogaritjet, klikoni në butonin hyj.



5. Siç mund ta shihni, përqindja e të ardhurave nga produkti i parë i listuar u shfaq në qelizën e synuar. Për të kopjuar formulën në rangun më poshtë, vendoseni kursorin në këndin e poshtëm të djathtë të qelizës. Ajo shndërrohet në një shënues mbushës që duket si një kryq i vogël. Klikoni butonin e majtë të miut dhe tërhiqni shënuesin e mbushjes poshtë në fund të kolonës.



6. Siç mund ta shihni, e gjithë kolona është e mbushur me të dhëna që karakterizojnë pjesën e të ardhurave nga shitja e secilit produkt. Por graviteti specifik shfaqet në një format numerik, dhe ne duhet ta shndërrojmë atë në përqindje. Për ta bërë këtë, zgjidhni përmbajtjen e kolonës "Graviteti specifik". Pastaj kalojmë në skedë "Home". Në shiritin në grupin e cilësimeve "Numri" Ekziston një fushë që shfaq formatin e të dhënave. Si parazgjedhje, nëse nuk keni kryer manipulime shtesë, formati duhet të vendoset atje "General". Ne klikojmë në ikonën në formën e një trekëndëshi të vendosur në të djathtë të kësaj fushe. Në listën e formateve që hapen, zgjidhni pozicionin "Interesi".



7. Siç mund ta shihni, të gjitha vlerat e kolonës u shndërruan në vlera përqindje. Siç pritej, në linjë "Total" i panjohur 100%. Përqindja e mallrave pritet të jetë në kolonë nga më e madhe në më e vogël.



8. Tani duhet të krijojmë një kolonë në të cilën do të shfaqet pjesa e grumbulluar me një total kumulativ. Kjo do të thotë, në secilën rresht, graviteti specifik i një produkti të veçantë do të shtojë peshën specifike të të gjithë atyre produkteve që gjenden në listën e mësipërme. Për artikullin e parë në listë (Produkti 3) graviteti specifik individual dhe pjesa e akumuluar do të jetë e barabartë, por për të gjitha ato vijuese, pjesa e grumbulluar e elementit të mëparshëm të listës do të duhet të shtohet në treguesin individual.

   Pra, në rreshtin e parë kalojmë te kolona Pjesë e akumuluar treguesin e kolonës "Graviteti specifik".



9. Tjetra, vendoseni kursorin në qelizën e dytë në kolonë. Pjesë e akumuluar. Këtu duhet të aplikojmë formulën. Ne vendosëm një shenjë "Barabartë" dhe shtoni përmbajtjen e qelizës "Graviteti specifik" rreshtin e njëjtë dhe përmbajtjen e qelizave Pjesë e akumuluar nga rreshti i mësipërm. Ne i lëmë të gjitha lidhjet relative, domethënë nuk i manipulojmë ato. Pas kësaj, klikoni në butonin hyj për të shfaqur rezultatin përfundimtar.



10. Tani ju duhet të kopjoni këtë formulë në qelizat e kësaj kolone, të cilat gjenden më poshtë. Për ta bërë këtë, përdorni shënuesin e mbushjes, të cilin ne tashmë iu drejtuam kur kopjoni formulën në kolonë "Graviteti specifik". Në këtë rast, rreshti "Total" nuk ka nevojë për kapje, pasi rezultati i akumuluar në 100% do të shfaqet në artikullin e fundit nga lista. Siç mund ta shihni, të gjithë elementët e kolonës sonë u mbushën pas kësaj.



11. Pas kësaj ne krijojmë një kolonë "Group". Ne do të duhet të grupojmë produktet në kategori A, B dhe C sipas pjesës së akumuluar të treguar. Siç kujtojmë, të gjithë elementët shpërndahen në grupe sipas skemës së mëposhtme:
    • A - tek 80%;
    • B - në vijim 15%;
    • C - mbetur 5%.

    Kështu, për të gjitha mallrat, pjesa e grumbulluar e gravitetit specifik të së cilës përfshihet në kufi deri në 80%caktoni kategorinë A. Mallrat me një gravitet specifik të 80% në 95% caktoni kategorinë B. Grupi i mbetur i produkteve me një vlerë më të madhe se 95% kategori specifike e akorduar e peshës C.



12. Për qartësi, ju mund t'i plotësoni këto grupe me ngjyra të ndryshme. Por kjo është opsionale.

Kështu, ne i kemi ndarë elementët në grupe sipas nivelit të rëndësisë, duke përdorur analizën ABC. Kur përdorni disa teknika të tjera, siç u përmend më lart, përdoret ndarja në një numër më të madh grupesh, por parimi i ndarjes mbetet pothuajse i pandryshuar.

mësim: Renditja dhe filtrimi i Excel-it

Metoda 2: përdorni një formulë komplekse

Sigurisht, përdorimi i klasifikimit është mënyra më e zakonshme për të kryer analizat ABC në Excel. Por në disa raste, është e nevojshme të bëhet kjo analizë pa rirregulluar rreshtat në tabelën origjinale. Në këtë rast, një formulë komplekse do të vijë në shpëtim. Si shembull, ne do të përdorim të njëjtën tabelë burimore si në rastin e parë.

1. Shtoni në tabelën origjinale që përmban emrin e mallrave dhe të ardhurat nga shitja e secilës prej tyre, një kolonë "Group". Siç mund ta shihni, në këtë rast ne nuk mund të shtojmë kolona me llogaritjen e aksioneve individuale dhe kumulative.



2. Zgjidhni qelizën e parë në kolonë "Group"dhe pastaj kliko në butonin "Vendos funksionin"të vendosura afër vijës së formulave.



3. Aktivizimi në zhvillim e sipër Magjistarët e funksionit. Ne kalojmë në kategori Referenca dhe vargje. Zgjidhni funksionin "ZGJEDHJA". Klikoni në butonin "OK".



4. Dritarja e argumentit të funksionit aktivizohet. PËRZGJEDHJA. Sintaksa e saj paraqitet si më poshtë:

   = SELECT (Index_number; Vlera1; Vlera2; ...)

   Qëllimi i këtij funksioni është të nxjerrë një nga vlerat e treguara, në varësi të numrit të indeksit. Numri i vlerave mund të arrijë në 254, por na duhen vetëm tre emra që korrespondojnë me kategoritë e analizës ABC: A, B, C. Mund të futemi menjëherë në fushë "Value1" simbol "A"në fushë "Value2" - "B"në fushë "Value3" - "C".



5. Por me një argument Numri i indeksit ju duhet të tërhiqeni plotësisht me të duke integruar disa operatorë shtesë në të. Vendoseni kursorin në fushë Numri i indeksit. Tjetra, klikoni në ikonën në formën e një trekëndëshi në të majtë të butonit "Vendos funksionin". Hapet një listë e operatorëve të përdorur kohët e fundit. Ne kemi nevojë për një funksion MATCH. Meqenëse nuk është në listë, atëherë klikoni në mbishkrim "Karakteristika të tjera ...".



6. Dritarja fillon përsëri. Magjistarët e funksionit. Përsëri kalojmë te kategoria Referenca dhe vargje. Gjeni një pozicion atje "Match", zgjidhni atë dhe klikoni në butonin "OK".



7. Hapet dritarja e Argumentit të Operatorit MATCH. Sintaksa e saj është si më poshtë:

   = SEARCH (Kërkuar_value; Shikuar_array; Ndeshje_type)

   Qëllimi i këtij funksioni është të përcaktojë numrin e pozicionit të elementit të specifikuar. Kjo është, pikërisht ajo që na nevojitet për fushën Numri i indeksit funksionet PËRZGJEDHJA.

   Në fushë Shikuar vargun Ju menjëherë mund të specifikoni shprehjen e mëposhtme:

   {0:0,8:0,95}

   Ajo duhet të jetë në kllapa kaçurrelë, si një formulë vargu. Nuk është e vështirë të hamendësosh që këta numra (0; 0,8; 0,95) tregoni kufijtë e pjesës së akumuluar midis grupeve.

   Fushë Lloji i ndeshjes opsionale dhe në këtë rast ne nuk do ta plotësojmë.

   Në fushë "Duke kërkuar vlerë" vendoseni kursorin. Pastaj përsëri përmes figurogramit të mësipërm në formën e një trekëndëshi ku kalojmë Wizard Feature.



8. Këtë herë në Magjistar i funksionit kaloni në kategori "Matematike". Zgjidhni një emër "SUMIF" dhe klikoni në butonin "OK".



9. Fillon dritarja e argumentit të funksionit SUMIF. Operatori i specifikuar përmbledh qelizat që plotësojnë një gjendje specifike. Sintaksa e saj është:

   = SUMMES (varg; kriter; sum_range)

   Në fushë "Gama" shkruani adresën e kolonës "Të ardhurat". Për këto qëllime, vendoseni kursorin në fushë, dhe pastaj, duke mbajtur butonin e majtë të miut, zgjidhni të gjitha qelizat në kolonën përkatëse, duke përjashtuar vlerën "Total". Siç mund ta shihni, adresa u shfaq menjëherë në fushë. Përveç kësaj, ne duhet ta bëjmë këtë lidhje absolute. Për ta bërë këtë, zgjidhni atë dhe shtypni butonin F4. Adresa u dallua me shenja dollari.

   Në fushë "Kriteri" duhet të vendosim një kusht. Ne hyjmë në shprehjen e mëposhtme:

   ">"&

   Pastaj menjëherë pas saj ne shkruajmë adresën e qelizës së parë të kolonës "Të ardhurat". Ne i bëjmë koordinatat horizontale në këtë adresë absolute duke i bashkangjitur një shenjë dollari nga tastiera para letrës. Ne i lëmë koordinatat vertikale relative, domethënë nuk duhet të ketë asnjë shenjë para shifrës.

   Pas kësaj, mos klikoni në butonin "OK", dhe klikoni në emrin e funksionit MATCH në shiritin e formulave.



10. Pastaj kthehemi në dritaren e argumentit të funksionit MATCH. Siç mund ta shihni, në terren "Duke kërkuar vlerë" u shfaqën të dhëna të vendosura nga operatori SUMIF. Por nuk janë të gjitha. Shkoni në këtë fushë dhe shtoni shenjën në të dhënat ekzistuese. "+" pa thonjëza. Pastaj vendosim adresën e qelizës së parë të kolonës "Të ardhurat". Dhe përsëri, ne bëjmë koordinatat horizontale të kësaj lidhjeje, dhe i lëmë ato relative.

    Tjetra, merrni të gjithë përmbajtjen e fushës "Duke kërkuar vlerë" në kllapa, pas së cilës vendosëm shenjën e ndarjes ("/"). Pas kësaj, përsëri përmes ikonës së trekëndëshit, shkoni në dritaren e zgjedhjes së funksionit.



11. Si herën e fundit në arrati Magjistar i funksionit duke kërkuar për operatorin e dëshiruar në kategori "Matematike". Këtë herë, funksioni i dëshiruar quhet "SUM". Zgjidhni atë dhe klikoni në butonin. "OK".



12. Hapet dritarja e Argumentit të Operatorit SUM. Qëllimi i tij kryesor është të përmbledh të dhënat në qeliza. Sintaksa për këtë deklaratë është mjaft e thjeshtë:

    = SUM (Numri 1; Numri2; ...)

    Për qëllimet tona është e nevojshme vetëm një fushë "Number 1". Vendosni koordinatat e diapazonit të kolonës në të. "Të ardhurat"duke përjashtuar qelizën që përmban totalet. Tashmë kemi kryer një operacion të ngjashëm në terren "Gama" funksionet SUMIF. Si në atë kohë, ne bëjmë koordinatat e intervalit absolut duke i zgjedhur ato dhe duke shtypur tastin F4.

    Pas kësaj, klikoni në butonin "OK" në fund të dritares.



13. Siç mund ta shihni, kompleksi i funksioneve të paraqitura kryente llogaritjen dhe e ktheu rezultatin në qelizën e parë të kolonës "Group". Produkti i parë u caktua një grup "A". Formula e plotë që kemi përdorur për këtë llogaritje është si më poshtë:

    = SELECT (KARRKONI ((P SRMBLEDHJE ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95 ); "A"; "B"; "C")

    Por, natyrisht, në secilin rast, koordinatat në këtë formulë do të jenë të ndryshme. Prandaj, nuk mund të konsiderohet universale. Por, duke përdorur udhëzimet e dhëna më lart, ju mund të futni koordinatat e çdo tabele dhe ta zbatoni me sukses këtë metodë në çdo situatë.



14. Sidoqoftë, kjo nuk është e gjitha. Ne kemi kryer llogaritjen vetëm për rreshtin e parë të tabelës. Në mënyrë që të mbushni plotësisht një kolonë me të dhëna "Group", ju duhet të kopjoni këtë formulë në rangun e mëposhtëm (duke përjashtuar qelizën e rreshtit "Total") duke përdorur shënuesin e mbushjes, siç kemi bërë më shumë se një herë. Pasi të jenë futur të dhënat, analiza e ABC mund të konsiderohet e përfunduar.

Siç mund ta shihni, rezultatet e marra duke përdorur opsionin duke përdorur një formulë komplekse nuk ndryshojnë aspak nga rezultatet që kemi kryer duke i renditur. Të gjitha produktet janë caktuar të njëjtat kategori, por linjat nuk e kanë ndryshuar pozicionin e tyre fillestar.

mësim: Magjistar i Karakteristikave Excel

Excel mund të lehtësojë shumë analizën ABC për përdoruesit. Kjo arrihet duke përdorur një mjet të tillë si klasifikimi. Pas kësaj, llogaritet graviteti specifik individual, pjesa e akumuluar dhe, në fakt, ndarja në grupe. Në rastet kur ndryshimi i pozicionit fillestar të rreshtave në tabelë nuk lejohet, mund të aplikoni metodën duke përdorur një formulë komplekse.