Një nga metodat kryesore të menaxhimit dhe logjistikës është analiza ABC. Me të, ju mund të klasifikoni burimet e ndërmarrjes, produkteve, konsumatorëve, etj. në mënyrë të rendësishme. Në të njëjtën kohë, sipas nivelit të rëndësisë, secila prej njësive të listuara më sipër caktohet një nga tre kategoritë: A, B ose C. Excel ka mjete në bagazhin e tij që e bëjnë më të lehtë kryerjen e këtij lloj analize. Le të shohim se si t'i përdorim ato, dhe cila është analiza ABC.
Përdorimi i analizës ABC
Analiza e ABC është një lloj i përmirësuar dhe i përshtatur ndaj varianteve të kushteve moderne të paretimit. Sipas metodologjisë së sjelljes së tij, të gjitha elementet e analizës janë të ndara në tri kategori sipas rëndësisë:
- kategori A - elemente që kanë më shumë të përbashkëta 80% pesha specifike;
- kategori B - elementë, tërësia e së cilës është nga 5% deri në 15% pesha specifike;
- kategori C - elementet e mbetura, totali i përgjithshëm i të cilave është 5% dhe peshë më pak specifike.
Disa kompani përdorin teknika më të avancuara dhe ndajnë elementet jo në 3, por në 4 ose 5 grupe, por ne do të mbështetemi në skemën klasike të analizës ABC.
Metoda 1: analiza sipas klasifikimit
Në Excel, analiza ABC kryhet duke përdorur klasifikimin. Të gjitha artikujt janë të renditura nga më të mëdhenjtë në më të vegjlit. Pastaj llogaritet pesha specifike kumulative e çdo elementi, në bazë të së cilës i caktohet një kategori e caktuar. Le të përdorim një shembull specifik për të gjetur se si zbatohet kjo teknikë në praktikë.
Ne kemi një tabelë me një listë të mallrave që shet kompania dhe shumën përkatëse të të ardhurave nga shitja e tyre për një periudhë të caktuar kohe. Në fund të tabelës, të ardhurat totale përmblidhen për të gjitha artikujt e mallrave. Detyra është që të përdorim analiza ABC për t'i ndarë këto produkte në grupe sipas rëndësisë së tyre për kompaninë.
- 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 tab "Data". Kliko në butonin. "Rendit"të vendosura në një bllok të veglave "Rendisni dhe filtroni" në kasetë.
Ju gjithashtu mund të bëni ndryshe. Përzgjidhni vargun e mësipërm të tabelës, pastaj lëvizni në tab "Home" dhe klikoni mbi butonin "Rendisni dhe filtroni"të vendosura në një bllok të veglave "Editing" në kasetë. Aktivizohet një listë në të cilën ne zgjedhim një pozicion në të. "Rendit personal".
- Kur aplikohet ndonjë nga veprimet e mësipërme, hapet dritarja e cilësimeve të renditjes. Ne shikojmë në parametër "Të dhënat e mia përmbajnë headers" tik-tak është vendosur. Në rast të mungesës së tij, instaloni.
Në fushë "Kolona" specifikoni emrin e kolonës në të cilën të dhënat mbi të ardhurat.
Në fushë "Rendit" ju duhet të specifikoni sipas asaj që kriteret specifike do të renditen. Lëreni cilësimet paraprakisht - "Vlerat".
Në fushë "Urdhër" vendosni pozicionin "Zbritje".
Pas bërë këto parametra klikoni mbi butonin "OK" në fund të dritares.
- Pas kryerjes së këtij aksioni, të gjitha artikujt u renditën sipas të ardhurave nga më e larta deri tek më e ulëta.
- Tani ne duhet të llogarisim proporcionin e secilit prej elementeve për totalin. Ne krijojmë për këto qëllime një kolonë shtesë, të cilën ne do të thërrasim "Pesha specifike". Në qelizën e parë të kësaj kolone vendosni një shenjë "="pas së cilës ne tregojmë referencën ndaj qelizës në të cilën gjendet shuma e të ardhurave nga shitja e produktit përkatës. Tjetra, vendosni shenjën e ndarjes ("/"). Pas kësaj ne tregojmë koordinatat e qelizës, e cila përmban sasinë totale të shitjeve të mallrave në të gjithë ndërmarrjen.
Duke marrë parasysh faktin se ne do të kopjojmë formulën e treguar në qeliza të tjera të kolonës "Pesha specifike" me anë të një shënuesi të mbushjes, adresën e lidhjes me elementin që përmban shumën totale të të ardhurave për ndërmarrjen, ne duhet ta rregullojmë. Për ta bërë këtë, bëni lidhjen absolute. Zgjidhni koordinatat e qelizës së specifikuar në formulë dhe shtypni butonin F4. Siç mund ta shohim, një shenjë dollarësh u shfaq para koordinatave, gjë që tregon se lidhja është bërë absolute. Duhet të theksohet se referenca në shumën e të ardhurave të artikullit të parë në listë (Pika 3) duhet të mbetet relativ.
Pastaj, për të bërë llogaritjet, klikoni mbi butonin. hyj.
- Siç mund ta shihni, përqindja e të ardhurave nga produkti i parë i listuar në listë shfaqet në qelizën e synuar. Për të bërë një kopje të formulës në rangun e mëposhtëm, vendoseni kursorin në këndin e poshtëm të djathtë të qelizës. Ajo shndërrohet në një shenjë mbushjeje që duket si një kryq i vogël. Klikoni butonin e majtë të miut dhe tërhiqni butonin e mbushjes deri në fund të kolonës.
- Siç mund ta shihni, kolona e tërë është e mbushur me të dhëna që përshkruajnë pjesën e të ardhurave nga shitja e secilit produkt. Por vlera e peshës specifike shfaqet në një format numerik dhe ne duhet ta transformojmë atë në një përqindje. Për ta bërë këtë, zgjidhni përmbajtjen e kolonës "Pesha specifike". Pastaj shkoni te skeda "Home". Në shiritin në grupin e cilësimeve "Numri" Ekziston një fushë që tregon formatin e të dhënave. By default, nëse nuk keni kryer ndonjë manipulim shtesë, formati duhet të vendoset atje. "General". Ne klikojmë në ikonën në formën e një trekëndëshi që ndodhet në të djathtë të kësaj fushe. Në listën e formateve që hapen, zgjidhni pozicionin "Interesi".
- Siç mund ta shikoni, të gjitha vlerat e kolonës u konvertuan në përqindje. Siç duhet të jetë, në linjë "Total" i panjohur 100%. Proporcioni i mallrave pritet të jetë i vendosur në kolonën nga më të mëdha në më të vogla.
- Tani duhet të krijojmë një kolonë në të cilën do të shfaqet pjesa kumulative me një total kumulativ. Kjo është, në çdo rresht, pesha specifike e të gjitha atyre mallrave, të cilat janë të vendosura në listën e mësipërme, do të shtohen në peshën individuale specifike të një produkti të caktuar. Për artikullin e parë në listë (Pika 3) pesha specifike individuale dhe pjesa e grumbulluar do të jenë të barabarta, por për të gjitha ato të mëvonshme, pjesa e akumuluar e artikullit të mëparshëm në listë duhet t'i shtohet treguesit individual.
Pra, në radhën e parë ne transferojmë në kolonën "Akumuluar Share" shkalla e kolonës "Pesha specifike".
- Tjetra, vendosni kursorin në qelizën e kolonës së dytë. "Akumuluar Share". Këtu duhet të aplikojmë formulën. Ne kemi vënë një shenjë "Barabartë" dhe deleni përmbajtjen e qelizës "Pesha specifike" të njëjtat përmbajtje rreshtesh dhe qelizash "Akumuluar Share" nga vija e mësipërme. Të gjitha lidhjet janë relative, domethënë ne nuk kryejmë ndonjë manipulim me to. Pas kësaj, kliko mbi butonin. hyj për të shfaqur rezultatin përfundimtar.
- Tani ju duhet ta kopjoni këtë formulë në qelizat e kësaj kolone, të cilat janë të vendosura më poshtë. Për ta bërë këtë, përdorni shenjën e mbushjes, për të cilën ne kemi përdorur tashmë kopjimin e formulës në kolonën "Pesha specifike". Në të njëjtën kohë, string "Total" kapja nuk është e nevojshme sepse rezultati i akumuluar në 100% do të shfaqet në artikullin e fundit nga lista. Siç mund ta shihni, të gjitha elementet e kolonës sonë pas kësaj u mbushën.
- Pas kësaj krijojmë një kolonë "Group". Ne do të duhet të grupojmë produktet në kategori A, B dhe C sipas aksionit kumulativ të treguar. Siç e kujtojmë, të gjitha elementet ndahen në grupe sipas skemës së mëposhtme:
- A - deri 80%;
- B - në vijim 15%;
- C - mbetur 5%.
Kështu, të gjitha mallrat, pjesa kumulative e peshës specifike të së cilës hyn në kufi 80%caktojë një kategori A. Mallrat me peshë specifike të akumuluar prej 80% deri në 95% caktojë një kategori B. Grupi i mbetur i produktit me një vlerë prej më shumë 95% pesha specifike e përllogaritur përcakton një kategori C.
- Për qartësi, ju mund t'i plotësoni këto grupe me ngjyra të ndryshme. Por kjo është fakultative.
Kështu, ne kemi ndarë elementet në grupe sipas nivelit të rëndësisë, duke përdorur analizën ABC. Kur përdorni disa metoda të tjera, siç u përmend më lart, aplikoni një ndarje në grupe të tjera, por parimi i ndarjes mbetet pothuajse i pandryshuar.
mësim: Renditja dhe filtrimi në Excel
Metoda 2: përdorimi i një formule komplekse
Sigurisht, përdorimi i klasifikimit është mënyra më e zakonshme për të kryer analiza ABC në Excel. Por në disa raste është e nevojshme për të kryer këtë analizë pa riorganizimin e rreshtave në tryezë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ë.
- Shtoni në tabelën origjinale që përmban emrin e mallrave dhe të ardhurat nga shitja e secilit prej tyre, në kolonën "Group". Siç mund ta shikoni, në këtë rast nuk mund të shtojmë kolona me llogaritjen e aksioneve individuale dhe kumulative.
- Zgjidhni qelizën e parë në kolonën. "Group"pastaj kliko mbi butonin. "Funksioni i futjes"të vendosura pranë shiritit të formulës.
- Aktivizimi kryhet Zotëruesit e funksioneve. Lëviz në kategorinë "Lidhjet dhe vargjet". Zgjidhni një funksion "ZGJEDHJA". Kliko në butonin. "OK".
- Aktivizohet dritarja e argumentit të funksionit. PËRZGJEDHJA. Sintaksa e saj është si më poshtë:
= SELECT (Index_number; Value1; Value2; ...)Qëllimi i këtij funksioni është të nxjerrë një nga vlerat e specifikuara, në varësi të numrit të indeksit. Numri i vlerave mund të arrijë në 254, por na nevojiten vetëm tre emra që korrespondojnë me kategoritë e analizës ABC: A, B, C. Ne mund të hyjmë menjëherë në fushë "Value1" simboli "A"në terren "Value2" - "B"në terren "Value3" - "C".
- Por me një argument "Numri i indeksit" Do të jetë e domosdoshme që të kryhet tërësisht, duke ndërtuar në të disa operatorë shtesë. Vendosni kursorin në fushë "Numri i indeksit". Tjetra, klikoni në ikonën që ka formën e një trekëndëshi në të majtë të butonit "Funksioni i futjes". Hapet një listë e operatorëve të përdorur kohët e fundit. Ne kemi nevojë për një funksion MATCH. Meqë nuk është në listë, kliko mbi titullin "Karakteristika të tjera ...".
- Rikthe dritaren përsëri. Zotëruesit e funksioneve. Përsëri shkoni në kategorinë "Lidhjet dhe vargjet". Ne gjejmë një pozicion "Match"zgjidhni atë dhe kliko mbi butonin "OK".
- Hapet dritarja e argumentit të operatorit MATCH. Sintaksa e saj është si më poshtë:
= MATCH (vlera e kërkuar, grupi i shikuar, lloj i përputhjes)Qëllimi i këtij funksioni është të përcaktojë numrin e pozicionit të elementit të specifikuar. Kjo është, vetëm ajo që na nevojitet për këtë fushë "Numri i indeksit" funksionet PËRZGJEDHJA.
Në fushë "Seti i shikuar" Mund të caktoni menjëherë shprehjen e mëposhtme:
{0:0,8:0,95}Duhet të jetë pikërisht në mbërthimet e kaçurrtave, si një formulë grupore. Nuk është e vështirë të supozohet se këto shifra (0; 0,8; 0,95) tregojnë kufijtë e pjesës së akumuluar ndërmjet grupeve.
fushë "Tipi i hartës" jo të detyrueshme dhe në këtë rast nuk do ta plotësojmë atë.
Në fushë "Vlera e kërkuar" vendosni kursorin. Pastaj përsëri, nëpërmjet ikonës së lartpërmendur në formën e një trekëndësh, ne shkojmë drejt Funksion magjistar.
- Këtë herë në Funksion magjistar shko te kategoria "Matematike". Zgjidhni një emër "SUMIF" dhe klikoni mbi butonin "OK".
- Fillon dritarja e argumentit të funksionit. SUMIF. Operatori i specifikuar i shumon qelizat që plotësojnë kushtet e specifikuara. Sintaksa e saj është:
= SUMMES (varg, kriter, varg_summing)Në fushë "Gama" futni adresën e kolonës "Të ardhurat". Për këto qëllime, ne vendosim kursorin në fushë dhe pastaj, duke e mbërthyer butonin e majtë të miut, zgjidhni të gjitha qelizat e kolonës përkatëse, duke përjashtuar vlerën "Total". Siç mund ta shihni, adresa shfaqet menjëherë në fushë. Përveç kësaj, ne duhet ta bëjmë këtë lidhje absolute. Për ta bërë këtë, bëni përzgjedhjen e saj dhe shtypni butonin F4. Adresa është e theksuar me shenja të dollarit.
Në fushë "Kriteri" ne duhet të vendosim një kusht. Futni shprehjen e mëposhtme:
">"&Pastaj menjëherë pas kësaj ne futemi në adresën e qelizës së parë të kolonës. "Të ardhurat". Ne bëjmë koordinatat horizontale në këtë adresë absolute, duke shtuar një shenjë dollarësh nga tastiera përpara letrës. Koordinatat vertikale janë relative, dmth. Nuk duhet të ketë shenja përpara numrit.
Pas kësaj, mos shtypni butonin "OK", dhe klikoni mbi emrin e funksionit MATCH në shiritin e formulës.
- Pastaj ne kthehemi në dritaren e argumentit të funksionit. MATCH. Siç mund ta shihni, në terren "Vlera e kërkuar" të dhënat u shfaqën nga operatori SUMIF. Por kjo nuk është e gjitha. Shkoni te kjo fushë dhe shtoni shenjën te të dhënat ekzistuese. "+" pa kuotat. Pastaj ne hyjmë në adresën e qelizës së parë të kolonës. "Të ardhurat". Dhe përsëri bëjmë koordinatat horizontale të kësaj lidhjeje absolute, dhe vertikalisht i lëmë të afërmit.
Tjetra, merr gjithë përmbajtjen e fushës "Vlera e kërkuar" në kllapa, pastaj vendosni shenjën e ndarjes ("/"). Pas kësaj, përsëri përmes ikonës së trekëndëshit, shkoni te dritarja e zgjedhjes së funksionit.
- Ashtu si herën e fundit në drejtimin Funksion magjistar duke kërkuar për operatorin e dëshiruar në këtë kategori "Matematike". Këtë herë, quhet funksioni i dëshiruar "SUM". Zgjidhni atë dhe kliko mbi butonin. "OK".
- Hapet dritarja e argumentit të operatorit SUM. Qëllimi i tij kryesor është përmbledhja e të dhënave në qeliza. Sintaksa e kësaj deklarate është mjaft e thjeshtë:
= SUM (numri1, numri2; ...)Për qëllimet tona ne vetëm kemi nevojë për një fushë. "Number 1". Futni koordinatat e intervalit të kolonës "Të ardhurat", duke përjashtuar qelizën që përmban totalet. Ne kemi kryer tashmë një operacion të ngjashëm në këtë fushë. "Gama" funksionet SUMIF. Si në atë kohë, ne bëjmë koordinatat absolute të diapazonit duke përzgjedhur ato dhe shtypur butonin F4.
Pas kësaj klikoni mbi çelësin "OK" në fund të dritares.
- Siç mund ta shihni, kompleksi i funksioneve të futura ka prodhuar një llogaritje dhe ka dhënë rezultatin në qelizën e parë të kolonës "Group". Pika e parë u caktua një grup. "A". Formula e plotë që kemi përdorur për këtë llogaritje është si më poshtë:
= SELECT ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} {SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ), "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 e dhënë më sipër, mund të futni koordinatat e çdo tabele dhe të aplikoni me sukses këtë metodë në çdo situatë.
- Megjithatë, kjo nuk është e gjitha. Kemi llogaritur vetëm për rreshtin e parë të tabelës. Për të mbushur plotësisht kolonën e të dhënave "Group", 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ë. Pas futjes së të dhënave, analiza ABC mund të konsiderohet e plotë.
Siç mund ta shikoni, rezultatet e fituara duke përdorur variantin me përdorimin e një formule komplekse nuk ndryshojnë aspak nga rezultatet që kemi bërë sipas klasifikimit. Të gjitha produktet u janë caktuar kategorive të njëjta, por linjat nuk kanë ndryshuar pozicionin e tyre fillestar.
mësim: Magjistar i funksionit Excel
Excel mund të lehtësojë shumë analiza ABC për një përdorues. Kjo arrihet duke përdorur një mjet të tillë si klasifikimi. Pas kësaj, llogaritet pesha individuale specifike, pjesa e grumbulluar dhe, në fakt, ndarja në grupe. Në rastet kur ndryshimi në pozicionin fillestar të rreshtave në tabelë nuk lejohet, mund të aplikoni metodën duke përdorur një formulë komplekse.
