Excel

Uporaba ABC-analize v programu Microsoft Excel

Eden ključnih metod upravljanja in logistike je ABC-analiza. Z njim lahko razvrstite vire podjetja, izdelkov, kupcev itd. po stopnji pomembnosti. Hkrati se glede na pomembnost vsake zgoraj navedene enote dodeli ena od treh kategorij: A, B ali C. Excel ima v svojih orodjih za prtljago, ki olajšajo izvajanje takšne analize. Ugotovimo, kako jih uporabiti in kaj je ABC analiza.

Uporaba ABC analize

ABC-analiza je neke vrste izboljšana in prilagojena sodobnemu pogoju različice načela Pareto. Po metodologiji njenega ravnanja so vsi elementi analize glede na pomembnost razdeljeni v tri kategorije:

  • Kategorija A - elementi, ki imajo skupaj več kot 80% specifične teže;
  • Kategorija B - elementi, katerih skupna vrednost je od 5% do 15% specifične teže;
  • Kategorija C - preostali elementi, katerih skupni sestavni del je 5% in manj od specifične teže.

Nekatera podjetja uporabljajo bolj napredne tehnike in razčlenijo elemente ne v 3, ampak v 4 ali 5 skupin, vendar se bomo zanašali na klasično shemo ABC-analize.

Metoda 1: analiza z razvrščanjem

V Excelu se analiza ABC izvaja s sortiranjem. Vsi elementi so razvrščeni iz večjega v manjši. Nato se izračuna kumulativna specifična teža vsakega elementa, na podlagi katere se ji dodeli določena kategorija. Poglejmo na konkretnem primeru, kako se ta tehnika uporablja v praksi.

Imamo tabelo s seznamom blaga, ki ga podjetje prodaja, in ustreznim zneskom prihodkov od njihove prodaje za določeno časovno obdobje. Na dnu tabele je bil dosežen skupni prihodek za vse blago. Za opravljanje ABC-analize je vredno opraviti nalogo, da to blago razdelimo v skupine po pomembnosti za podjetje.

Tabela prihodkov družbe po blagu v programu Microsoft Excel

  1. Izberite tabelo z dani kazalnik, držite levi gumb miške, brez glave in zadnje vrstice. Pojdite na kartico »Podatki «. Kliknite gumb »Razvrsti«, ki se nahaja v orodni vrstici »Razvrsti in filtri« na traku.

    Preklopite na razvrščanje v programu Microsoft Excel

    Prav tako lahko deluje drugače. Izberite zgornjo tabelo, nato pa se premaknite na zavihek »Domov « in kliknite gumb »Razvrsti in filtriraj«, ki se nahaja v orodni vrstici »Uredi« na traku. Aktivira se seznam, v katerem izberemo element »Razvrščanje po meri« v njem .

    2. Pojdite v okno za razvrščanje prek zavihka Domov v programu Microsoft Excel

  2. Ko uporabite katero od zgornjih dejanj, se sproži okno za razvrščanje. Oglejmo si, da je možnost »Moje podatke vsebuje glave« izbrana. V primeru njegove odsotnosti ugotavljamo.

    V polju »Stolpec« navajamo ime stolpca, ki vsebuje podatke o prihodkih.

    V polju »Razvrsti« morate določiti posebna merila za razvrščanje. Zapustimo nastavljene nastavitve - »Vrednosti« .

    V polju "Vrstni red" smo nastavili položaj "Padajoče" .

    Po določenih nastavitvah kliknite na gumb »V redu« na dnu okna.

    3. Razvrsti okno v programu Microsoft Excel

  3. Po izvedbi določenega ukrepa so bili vsi elementi razvrščeni po prihodkih od največjih do manjših.

    4. Blago razvrščeno po prihodkih v programu Microsoft Excel

  4. Zdaj moramo izračunati specifično težo vsakega od elementov za skupno vsoto. Za te namene ustvarjamo dodaten stolpec, ki ga bomo imenovali »specifična teža« . V prvi celici tega stolpca smo postavili znak "=" , po katerem označujemo povezavo do celice, v kateri se nahaja znesek prihodka od prodaje ustreznega izdelka. Nato smo nastavili znak delitve ( "/" ). Po tem navedite koordinate celice, ki vsebuje skupni obseg prodaje blaga po celotnem podjetju.

    Ob upoštevanju dejstva, da bomo to formulo kopirali v druge celice v stolpcu "Specific weight" s pomočjo označevalnika polnila, moramo določiti naslov povezave do elementa, ki vsebuje podjetje celoten znesek prihodkov. Za to izdelamo referenčni absolut. V formuli izberite koordinate določene celice in pritisnite F4 . Pred koordinatami, kot smo videli, se je pojavil znak za dolar, kar pomeni, da je povezava postala absolutna. Treba je opozoriti, da bi morala biti referenca o prihodkih prve postavke na seznamu ( blago 3 ) relativna.

    Nato za izračune pritisnite gumb Enter .

    5. Specifična teža prve črte v programu Microsoft Excel

  5. Kot vidite, je bil v ciljni celici prikazan delež prihodkov iz prvega izdelka, navedenega na seznamu. Če želite formulo kopirati v spodnji obseg, postavite kazalko v spodnji desni kot celice. Preoblikuje se v oznako za polnjenje, ki izgleda kot majhen križ. Kliknite levi gumb miške in povlecite marker za polnjenje do konca stolpca.

    6. Polnilo v programu Microsoft Excel

  6. Kot lahko vidite, je celoten stolpec napolnjen s podatki, ki označujejo specifično težo izkupička od prodaje vsakega izdelka. Toda vrednost specifične teže je prikazana v številčnem formatu in jo moramo preoblikovati v odstotek. Če želite to narediti, izberite vsebino stolpca »Specifična teža« . Nato se pomaknite na zavihek Domov . Na traku v skupini nastavitev "Številka" je polje, ki prikazuje obliko podatkov. Če niste izvedli dodatnih manipulacij, privzeto nastavite format »Splošno« . Kliknemo na ikono v obliki trikotnika, ki se nahaja desno od tega polja. V odprtem seznamu formatov izberite element »Obresti« .

    7. Namestitev obrazca podatkov o odstotkih v programu Microsoft Excel

  7. Kot vidite, so bile vse vrednosti stolpcev pretvorjene v odstotne vrednosti. Kot je bilo pričakovano, vrstica "Total" označuje 100% . Pričakuje se, da bo specifična teža blaga v stolpcu od največje do manjše.

    8. Format odstotka je nastavljen v Microsoft Excelu

  8. Zdaj moramo ustvariti stolpec, v katerem je prikazan skupni delež prikazan s kumulativnim imenom. To pomeni, da se v vsaki vrstici specifična teža vsega blaga, ki se nahaja na zgornjem seznamu, dodaja posamezni specifični teži določenega blaga. Za prvi izdelek na seznamu ( postavka 3 ) je posamezna delnica in nakopičeni delež enaka, za vse po posameznem kazalu pa bo treba dodati nakopičeni delež prejšnjega elementa seznama.

    Torej, v prvi vrstici prenesite v stolpec »Skupna dionica« indikator iz stolpca »Specifična teža« .

    9. Kumulativni odstotek prvega elementa na seznamu v programu Microsoft Excel

  9. Nato postavimo kazalko v drugo celico stolpca »Skupna delnica« . Tukaj moramo uporabiti formulo. Postavili smo "enak" znak in dodali vsebino celice "specifična teža" iste črte in vsebino celice "Akumulirana delitev" iz zgornje vrstice. Vse reference so relativne, kar pomeni, da jih ne manipuliraramo. Po tem kliknite gumb Enter, da prikažemo končni rezultat.

    10. Skupni delež druge postavke na seznamu v programu Microsoft Excel

  10. Zdaj morate to formulo kopirati v celice tega stolpca, ki so postavljene spodaj. Če želite to narediti, uporabimo oznako za polnjenje, na katero smo že uporabljali kopiranje formule v stolpcu »Specific weight« . V tem primeru črta »Skupaj« ni potrebna za zajemanje, ker bo na zadnji izdelek s seznama prikazan nakopičeni rezultat v 100% . Kot vidite, so bili vsi elementi našega stolpca napolnjeni po tem.

    11. Podatki so napolnjeni z oznako v programu Microsoft Excel

  11. Po tem ustvarite stolpec »Skupina« . Bomo morali združiti blago po kategorijah A , B in C glede na nakopičeni delež. Kot smo spomnili, so vsi elementi razvrščeni v skladu z naslednjo shemo:
    • A - do 80% ;
    • B - naslednjih 15% ;
    • C - preostalih 5% .

    Tako se celotno blago, katerega skupni delež specifične teže vstopi na mejo na 80% , dodeli kategoriji A. Blago z nakopičeno specifično maso od 80% do 95% se dodeli kategoriji B. Preostali skupini blaga, katerih vrednost presega 95% skupne specifične teže, se dodeli kategoriji C.

    12. Razdeljevanje izdelkov v skupine v programu Microsoft Excel

  12. Za jasnost lahko te skupine izpolnite z različnimi barvami. Toda to je po volji.

Polnjenje skupin z različnimi barvami v programu Microsoft Excel

Tako smo elemente razdelili v skupine glede na stopnjo pomembnosti z analizo ABC. Z uporabo nekaterih drugih tehnik, kot je bilo omenjeno zgoraj, razdelimo na več skupin, vendar pa je načelo delitve praktično nespremenjeno.

Lekcija: Razvrščanje in filtriranje v Excelu

Metoda 2: Uporaba kompleksne formule

Seveda je uporaba sortiranja najpogostejši način analize ABC v Excelu. Toda v nekaterih primerih je ta analiza potrebna brez spreminjanja vrstic v mestih v izvorni tabeli. V tem primeru bo rešena kompleksna formula. Na primer, bomo uporabili isto izvorno tabelo kot v prvem primeru.

  1. Dodamo v začetno tabelo, ki vsebuje ime blaga in prihodke od prodaje vsakega od njih, stolpec "Skupina" . Kot lahko vidite, v tem primeru ne moremo dodati stolpcev z izračunom posameznih in nabranih delnic.

    2. Dodajanje stolpca skupine v Microsoft Excel

  2. Izberite prvo celico v stolpcu »Skupina« in nato poleg vrstice s formulo kliknite gumb »Vstavi funkcijo« .

    3. Odprite čarovnika za funkcije v programu Microsoft Excel

  3. Aktivira se čarovnik za funkcije . Premaknemo se v kategorijo "Reference in nizi" . Izberite funkcijo "SELECT" . Kliknite gumb »V redu« .

    4. Prehod na argumente funkcije SELECT v programu Microsoft Excel

  4. Vključeno je okno argumentov funkcije SELECT . Sintaksa je naslednja:

    =ВЫБОР(Номер_индекса;Значение1;Значение2;…)

    Naloga te funkcije je, da prikaže eno od navedenih vrednosti, odvisno od številke indeksa. Število vrednosti lahko doseže 254, vendar potrebujemo le tri imena, ki ustrezajo kategorijam ABC-analize: A , B , C. V polje "Vrednost1 " lahko v polje "Value3" - "C" v polje "Value2" - "B" takoj vnesemo simbol "A " .

    5. Okno argumenta funkcije SELECT v programu Microsoft Excel

  5. Ampak s trditvijo "indeksna številka" bo morala temeljito potegniti, potem ko je vanj zgradila nekaj dodatnih operaterjev. Kazalko postavite v polje »Številka indeksa« . Nato kliknite ikono, ki je videti kot trikotnik, levo od gumba »Vstavi funkcijo« . Prikaže se seznam nedavno uporabljenih operaterjev. Potrebujemo funkcijo MATCH . Ker na seznamu ni, kliknite na napis "Druge funkcije ..." .

    6. Preklop na druge funkcije v programu Microsoft Excel

  6. Ponovno se zažene čarovnik okna funkcij . Še enkrat pojdite na kategorijo "Reference in nizi". Tam najdemo položaj »SEARCH« , ga izberite in kliknite gumb »OK« .

    7. Odprite okno argumentov funkcij MQL v programu Microsoft Excel

  7. Odpre okno OPERATORJA . Sintaksa je naslednja:

    =ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)

    Namen te funkcije je določiti številko položaja določenega elementa. To pomeni, kar potrebujemo za polje "Številka indeksa" funkcije SELECT .

    V polju »Skenirana matrika« lahko takoj nastavite naslednji izraz:

    {0:0,8:0,95}

    Biti mora v zavihkih, kot je formula matrike. Ni težko uganiti, da te številke ( 0 ; 0,8 ; 0,95 ) označujejo meje skupnega deleža med skupinami.

    Polje »Vrsta ujemanja« je neobvezno in v tem primeru ga ne bomo izpolnjevali.

    V polju »Search value« smo nastavili kazalec. Potem spet skozi trikotnik na sliki zgoraj premaknemo v čarovnik za funkcije .

    8. Okno argumenta funkcije MATCH v programu Microsoft Excel

  8. Tokrat v čarovniku za funkcijo premaknemo v kategorijo "Matematični" . Izberemo ime »SUMMER« in kliknite gumb »V redu« .

    9. Pojdite v okno argumentov funkcije SUMIFER v programu Microsoft Excel

  9. Začne se okno argumentov funkcije SUMIFER . Navedeni operater povzame celice, ki ustrezajo določenemu pogoju. Njena sintaksa je naslednja:

    =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

    V polju »Območje« vnesite naslov stolpca »Prihodki« . V ta namen smo postavili kazalko v polje, nato pa, potem ko smo z levim gumbom miške pritiskali, izberemo vse celice ustreznega stolpca, razen vrednosti »Skupaj« . Kot vidite, je bil v polju takoj prikazan naslov. Poleg tega moramo to povezavo narediti absolutno. Če želite to narediti, izberite in kliknite na tipko F4 . Naslov je izstopal z znaki dolarja.

    V polju "Kriteriji" moramo določiti stanje. Vnesemo naslednji izraz:

    ">"&

    Nato takoj po njem vnesemo naslov prve celice v stolpec »Prihodki« . Horizontalne koordinate v tem naslovu naredimo absolutno, pri čemer pred znak dodamo znak za dolar s tipkovnice. Koordinate na navpičnici so prepuščene relativno, torej pred številko ne sme biti nobenega znaka.

    Nato kliknite na gumb "V redu" , vendar kliknite na ime funkcije MATCH v vrstici s formulo.

    10. Okno argumenta funkcije SUMIFER v programu Microsoft Excel

  10. Nato se vrnemo v okno argumentov funkcije MATCH . Kot vidite, v polju "Vrednost iskanja" so se pojavili podatki, ki jih je vnesel operater SUMESELI . Toda to ni vse. Pojdite na to polje in dodajte znak "+" brez narekovanja obstoječim podatkom. Nato v stolpec »Prihodki« vnesemo naslov prve celice. In še enkrat smo horizontalne koordinate tega referenčnega absolutnega in vertikalno zapustili relativno.

    Nato v oklepaju vzamemo celotno vsebino polja "Želena vrednost" , po kateri smo dali znak za delitev ( "/" ). Potem spet skozi ikono trikotnika pojdite v okno izbiranja funkcij.

    11. Okno argumenta funkcije MATCH v programu Microsoft Excel

  11. Kot v zadnjem času v zagnanem funkcijskem čarovniku iščemo zahtevanega operaterja v kategoriji »Matematični« . Tokrat se želena funkcija imenuje "SUMM" . Izberite in kliknite gumb »V redu« .

    12. Pojdite v okno argumentov funkcije SUM v programu Microsoft Excel

  12. Odpre okno argumentov operaterja SUM . Njen glavni namen je povzeti podatke v celicah. Sintaksa tega operaterja je precej preprosta:

    =СУММ(Число1;Число2;…)

    Za naše potrebe je potrebno samo polje "Number1" . Vnesite koordinate območja stolpca »Prihodki« , razen celice, ki vsebuje vsote. Takšno operacijo smo že izvedli v polju "Range" funkcije SUMMER . Tako kot takrat so koordinate območja absolutne, jih izberete in pritisnete tipko F4 .

    Nato kliknite na gumb "OK" na dnu okna.

    13. Okno argumenta funkcije SUM v programu Microsoft Excel

  13. Kot lahko vidite, je nabor vhodnih funkcij izvedel izračun in rezultat prenašal v prvo celico stolpca »Skupina« . Prvi izdelek je bil dodeljen skupini "A" . Celotna formula, ki smo jo uporabili za ta izračun, je naslednja:

    =ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")

    Toda v vsakem posameznem primeru se bodo koordinate v tej formuli razlikovale. Zato ga ni mogoče šteti za univerzalno. Ampak, z uporabo zgornjega priročnika, lahko vstavite koordinate katere koli tabele in uspešno uporabite to metodo v vseh situacijah.

    14. Izračunska formula za kategorijo v programu Microsoft Excel

  14. Vendar to ni vse. Izračunali smo le za prvo vrstico tabele. Za popolno izpolnjevanje stolpca »Skupina« s podatki morate to formulo kopirati v spodnji obseg (razen celice linije »Skupaj« ) z oznako polnila, kot smo naredili več kot enkrat. Po vnosu podatkov se lahko šteje, da je analiza ABC izpolnjena.

Uporaba polnilnika v programu Microsoft Excel

Kot lahko vidite, rezultati, dobljeni s pomočjo variante z uporabo kompleksne formule, se ne razlikujejo od rezultatov, ki smo jih izvedli s sortiranjem. Vsi izdelki so dodeljeni istim kategorijam, vendar vrstice niso spremenile svojega začetnega položaja.

Podatki v stolpcu Skupina so izračunani v Microsoft Excelu

Lekcija: Čarovnik za funkcije v Excelu

Program Excel lahko v veliki meri olajša analizo ABC za uporabnika. To se doseže z orodjem, kot je sortiranje. Po tem se izračuna posamezna teža, nabrani delež in dejansko skupina. V primerih, ko sprememba začetnega položaja vrstic v tabeli ni dovoljena, lahko metodo uporabite z uporabo zapletene formule.