Aplikácia analýzy ABC v programe Microsoft Excel

Jednou z kľúčových metód riadenia a logistiky je analýza ABC. Pomocou tejto funkcie môžete klasifikovať zdroje podniku, produktov, zákazníkov atď. podľa stupňa dôležitosti. Súčasne podľa úrovne dôležitosti je každej z vyššie uvedených jednotiek pridelená jedna z troch kategórií: A, B alebo C. Excel má vo svojich batožinových nástrojoch, ktoré uľahčujú vykonávanie takejto analýzy. Poďme zistiť, ako ich používať, a čo je analýza ABC.

Pomocou analýzy ABC

Analýza ABC je druhom vylepšeného a prispôsobeného moderným podmienkam v princípe Pareto. Podľa metodiky svojho správania sú všetky prvky analýzy rozdelené do troch kategórií z hľadiska dôležitosti:

• Kategória A - prvky s celkovou hmotnosťou viac ako 80% špecifickej hmotnosti;
• Kategória B - prvky, ktorých súčet je od 5% do 15% špecifickej hmotnosti;
• Kategória C - zvyšné prvky, ktorých celkový súčet je 5% a nižší ako špecifická hmotnosť.

Niektoré spoločnosti uplatňujú pokročilejšie techniky a rozdeľujú prvky nie do 3, ale do 4 alebo 5 skupín, ale budeme sa opierať o klasickú schému ABC analýzy.

Metóda 1: analýza triedením

V programe Excel sa ABC analýza vykonáva triedením. Všetky prvky sú zoradené z väčších na menšie. Potom sa vypočíta kumulatívna špecifická hmotnosť každého prvku, na základe ktorého je určená určitá kategória. Na konkrétnom príklade zistíme, ako sa táto technika používa v praxi.

Máme tabuľku so zoznamom tovaru, ktorý spoločnosť predáva, a zodpovedajúcu sumu tržieb z ich predaja za určité časové obdobie. V dolnej časti tabuľky sú celkové príjmy za všetky položky tovaru zasiahnuté. Stojí za to úlohou pomocou analýzy ABC rozdeliť tento tovar do skupín podľa ich významu pre podnik.

1. Vyberte stôl s daným kurzorom, podržte ľavé tlačidlo myši, s výnimkou záhlavia a posledného riadku. Prejdite na kartu "Dáta" . Kliknite na tlačidlo "Zoradiť" umiestnené v poli "Zoradiť a triediť" na karte.

   Môžete sa tiež správať inak. Zvoľte vyššie uvedený rozsah tabuľky, potom prejdite na kartu "Domov" a kliknite na tlačidlo "Zoradiť a filtrovať" umiestnené v poli "Upraviť" na karte. Zoznam je aktivovaný, v ktorom vyberieme položku "Vlastné triedenie" .

2. Ak použijete niektorú z vyššie uvedených akcií, začne sa triediace okno. Pozrime sa, že možnosť "Moje údaje obsahuje hlavičky" bola zaškrtnutá. V prípade jeho neprítomnosti zriadime.

   V poli "Stĺpec" uvádzame názov stĺpca, ktorý obsahuje údaje o príjmoch.

   V poli "Zoradiť" musíte špecifikovať konkrétne kritériá triedenia. Ponechávame prednastavené nastavenia - "Hodnoty" .

   V poli "Order" nastavíme pozíciu "Descending" .

   Po vykonaní zadaných nastavení kliknite na tlačidlo "OK" v spodnej časti okna.

3. Po vykonaní špecifikovanej akcie boli všetky prvky zoradené podľa výnosov z najväčších na menšie.
4. Teraz je potrebné vypočítať špecifickú hmotnosť každého z prvkov pre celkový súčet. Pre tieto účely vytvoríme ďalší stĺpec, ktorý nazveme "Špecifická váha" . V prvej bunke tohto stĺpca umiestnime znak "=" , po ktorom uvedieme odkaz na bunku, v ktorej sa nachádza suma výnosov z predaja príslušného produktu. Ďalej nastavíme znak rozdelenia ( "/" ). Potom zadajte súradnice bunky, ktorá obsahuje celkový objem predaja tovaru v celom podniku.

   Vzhľadom na skutočnosť, že tento vzorec skopírujeme do iných buniek v stĺpci "Špecifická hmotnosť" pomocou značky naplnenia, musíme opraviť adresu odkazu na prvok obsahujúci celkovú sumu výnosov podniku. Preto robíme referenčný absolútny. V tomto vzore vyberte súradnice zadanej bunky a stlačte F4 . Pred súradnicami, ako vidíme, sa objavil znak dolára, čo naznačuje, že odkaz sa stal absolútnym. Treba poznamenať, že odkaz na príjmy prvej položky v zozname ( Tovar 3 ) by mal zostať relatívny.

   Potom vykonajte výpočty stlačením tlačidla Enter .

5. Ako vidíte, podiel príjmov z prvého produktu uvedeného v zozname bol zobrazený v cieľovej bunke. Ak chcete skopírovať vzorec do rozsahu nižšie, umiestnite kurzor do pravého dolného rohu bunky. Premenuje sa na značku plnenia, ktorá vyzerá ako malý kríž. Kliknite na ľavé tlačidlo myši a presuňte značku plnenia smerom nadol na koniec stĺpca.
6. Ako vidíte, celý stĺpec je vyplnený údajmi charakterizujúcimi špecifickú hmotnosť výťažku z predaja každého produktu. Hodnota špecifickej váhy je však zobrazená v číselnom formáte a musíme ju premeniť na percento. Za týmto účelom vyberte obsah stĺpca "Špecifická hmotnosť" . Potom prejdite na kartu Domov . Na pásiku v skupine nastavení "Počet" je pole, ktoré zobrazuje formát údajov. Štandardne, ak ste nevykonali ďalšie manipulácie, mal by sa nastaviť formát "Všeobecné" . Klikneme na ikonu v podobe trojuholníka umiestneného vpravo od tohto poľa. V otvorenom zozname formátov vyberte položku "Záujem" .
7. Ako vidíte, všetky hodnoty stĺpcov boli prevedené na percentuálne hodnoty. Ako sa očakávalo, riadok "Celkom" znamená 100% . Špecifická hmotnosť tovaru sa očakáva v stĺpci od najväčšieho po menšie.
8. Teraz musíme vytvoriť stĺpec, v ktorom sa nahromadená akcia zobrazí s kumulatívnym súčtom. To znamená, že v každom riadku sa špecifická hmotnosť všetkých tovarov, ktoré sa nachádzajú v horeuvedenom zozname, pridá k individuálnej špecifickej hmotnosti konkrétnej komodity. Pre prvý produkt v zozname ( položka 3 ) bude jednotlivá akcia a akumulovaná akcia rovnaká, ale pre všetky nasledujúce po individuálnom ukazovateli bude potrebné pridať nahromadenú časť predchádzajúceho prvku zoznamu.

   Takže v prvom riadku presuňte do stĺpca "Akumulovaný podiel" indikátor zo stĺpca "Špecifická hmotnosť" .

9. Ďalej umiestnime kurzor do druhej bunky v stĺpci "Akumulovaný podiel" . Tu musíme použiť vzorec. Vložili sme značku "equal" a pridajte obsah článku "špecifickej váhy" rovnakého riadku a obsah bunky "Akumulované zdieľanie" z vyššie uvedeného riadku. Všetky odkazy zostávajú relatívne, to znamená, že ich nemanipulujeme. Po kliknutí na tlačidlo Enter sa zobrazí konečný výsledok.
10. Teraz musíte tento vzorec skopírovať do buniek tohto stĺpca, ktoré sú umiestnené nižšie. Za týmto účelom použijeme značku naplnenia, na ktorú sme sa už pri kopírovaní vzorky v stĺpci "Špecifická váha" uchýlili. V tomto prípade nie je potrebné zachytiť riadok "Spolu" , pretože celkový výsledok vo výške 100% sa zobrazí na poslednom produkte zo zoznamu. Ako môžete vidieť, všetky prvky nášho stĺpca boli potom vyplnené.
11. Potom vytvorte stĺpec "Skupina" . Budeme musieť zoskupiť tovar podľa kategórií A , B a C podľa kumulovanej akcie. Ako sme si spomenuli, všetky prvky sú zoskupené podľa nasledujúcej schémy:
    • A - až 80% ;
    • B - ďalších 15% ;
    • C - zvyšných 5% .

    Preto všetok tovar, ktorého kumulovaný podiel, ktorého špecifická hmotnosť vstúpi na hranicu na 80% , je zaradený do kategórie A. Tovarom s akumulovanou špecifickou hmotnosťou od 80% do 95% sa priradí kategória B. Zvyšnej skupine tovaru s hodnotou vyššou ako 95% kumulovanej špecifickej hmotnosti sa priraďuje kategória C.

12. Z dôvodu prehľadnosti môžete tieto skupiny vyplniť rôznymi farbami. Ale je to vôľa.

Preto sme elementy rozdelili do skupín podľa úrovne dôležitosti pomocou analýzy ABC. Použitie niektorých ďalších techník, ako bolo uvedené vyššie, rozdelenie na viaceré skupiny, ale princíp rozdelenia zostáva prakticky nezmenený.

Lekcia: Triedenie a filtrovanie v programe Excel

Metóda 2: Použitie zložitého vzorca

Samozrejme, používanie triedenia je najbežnejším spôsobom vykonávania analýzy ABC v programe Excel. Ale v niektorých prípadoch je táto analýza potrebná bez zmeny riadkov na miestach v zdrojovej tabuľke. V tomto prípade príde na záchranu komplexný vzorec. Napríklad použijeme rovnakú zdrojovú tabuľku ako v prvom prípade.

1. Do pôvodnej tabuľky, ktorá obsahuje názov tovaru a výťažok z predaja každého z nich, sa pridá stĺpec "Skupina" . Ako vidíte, v tomto prípade nemôžeme pridať stĺpce s výpočtom jednotlivých a akumulovaných akcií.
2. Vyberte prvú bunku v stĺpci "Skupina" a potom kliknite na tlačidlo "Vložiť funkciu" vedľa lišty vzorcov.
3. Zobrazí sa aktivácia Sprievodcu funkciami . Presťahujeme sa do kategórie "Referencie a matice" . Vyberte funkciu "SELECT" . Kliknite na tlačidlo "OK" .
4. Okno s argumentmi funkcie SELECT je aktivované. Syntax je nasledujúci:

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

   Úlohou tejto funkcie je výstup jednej z uvedených hodnôt v závislosti od indexového čísla. Počet hodnôt môže dosiahnuť 254, ale potrebujeme len tri mená, ktoré zodpovedajú kategóriám analýzy ABC: A , B , C. Do poľa "Value1" môžeme okamžite zadať do poľa "Value2" - "B" symbol "A" v poli "Value3" - "C" .

5. Ale s argumentom "Index číslo" bude musieť dôkladne dinker, po tom, čo postavil v ňom niekoľko ďalších operátorov. Nastavte kurzor do poľa "Indexové číslo" . Potom kliknite na ikonu, ktorá vyzerá ako trojuholník, vľavo od tlačidla "Vložiť funkciu" . Zobrazí sa zoznam nedávno použitých operátorov. Potrebujeme funkciu MATCH . Keďže zoznam nie je, klikneme na nápis "Ďalšie funkcie ..." .
6. Spustí sa sprievodca okna funkcií . Opäť prejdite do kategórie "Odkazy a matice". Nájdeme tam pozíciu "SEARCH" , vyberte ju a kliknite na tlačidlo "OK" .
7. Otvorí okno argumentov OPERÁTORA . Jeho syntax je nasledujúci:

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

   Účelom tejto funkcie je určiť číslo pozície určeného prvku. To znamená, čo potrebujeme pre pole "Indexové číslo" funkcie SELECT .

   V poli "Naskenované pole" môžete okamžite nastaviť nasledujúci výraz:

   {0:0,8:0,95}

   Musí to byť v kučeravých zátvorkách, ako je vzorec poľa. Nie je ťažké odhadnúť, že tieto čísla ( 0 ; 0,8 ; 0,95 ) označujú hranice nahromadenej akcie medzi skupinami.

   Pole "Typ zhody" je voliteľné a v tomto prípade ho nebudeme vyplňovať.

   V poli "Hodnota vyhľadávania" nastavíme kurzor. Potom znova pomocou trojuholníka zobrazeného vyššie prejdeme na funkčný sprievodca .

8. Tentokrát v sprievodcovi funkciami prejdeme do kategórie "Matematická" . Vyberieme meno "SUMMER" a klikneme na tlačidlo "OK" .
9. Zobrazí sa okno s argumentmi funkcie SUMIFER . Určený operátor zhrňuje bunky, ktoré spĺňajú zadané podmienky. Jeho syntax je toto:

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

   Do poľa Rozsah zadajte adresu stĺpca Výnosy . Na tento účel nastavíme kurzor v poli a potom, keď sme stlačili ľavé tlačidlo myši, vyberte všetky bunky príslušného stĺpca okrem hodnoty "Celkom" . Ako vidíte, adresa bola okamžite zobrazená v poli. Okrem toho musíme tento odkaz stať absolútnym. Za týmto účelom vykonajte jeho výber a kliknite na tlačidlo F4 . Adresa vyšla so znakmi dolára.

   V poli "Kritériá" musíme špecifikovať podmienku. Zadáme nasledujúci výraz:

   ">"&

   Potom ihneď po zadaní uvedieme adresu prvej bunky v stĺpci "Výnosy" . Horizontálne súradnice v tejto adrese robíme absolútne a do písmena pridáme znak dolára z klávesnice. Súradnice na zvislej rovine zostávajú relatívne, to znamená, že pred číslom by nemalo byť žiadne znamenie.

   Potom nekliknite na tlačidlo "OK" , ale kliknite na názov funkcie MATCH na lište vzorcov.

10. Potom sa vrátime do okna argumentov funkcie MATCH . Ako vidíte, v poli "Hodnota vyhľadávania" sa zobrazili údaje zadané operátorom SUMESELI . Ale to nie je všetko. Prejdite do tohto poľa a pridajte znamienko "+" bez úvodzoviek k existujúcim údajom. Potom zadáme adresu prvej bunky do stĺpca "Výnosy" . A opäť urobíme vodorovné súradnice tohto referenčného absolútneho a vertikálne necháme príslušné súradnice.

    Potom zoberieme celý obsah poľa "Požadovaná hodnota" v zátvorkách, po ktorom umiestnime znak rozdelenia ( "/" ). Potom opäť cez ikonu trojuholníka prejdite do okna výberu funkcií.

11. Rovnako ako v poslednom čase v sprievodcovi spustených funkcií hľadáme požadovaný operátor v kategórii "Matematický" . Tentokrát sa požadovaná funkcia nazýva "SUMM" . Vyberte ho a kliknite na tlačidlo "OK" .
12. Otvorí okno s argumentmi operátora SUM . Jeho hlavným účelom je zhrnúť údaje v bunkách. Syntax tohto operátora je veľmi jednoduchý:

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

    Na naše účely sa vyžaduje iba pole "Číslo1" . Zadajte súradnice rozsahu stĺpca "Výnosy" s výnimkou bunky, ktorá obsahuje súčty. Už sme túto operáciu vykonali v poli "Rozsah" funkcie SUMMER . Rovnako ako v tom čase sú súradnice rozsahu absolútne, výberom a stlačením klávesu F4 .

    Potom kliknite na tlačidlo "OK" v spodnej časti okna.

13. Ako môžete vidieť, množina vstupných funkcií vykonala výpočet a výsledok vyšla do prvej bunky v stĺpci "Skupina" . Prvý produkt bol zaradený do skupiny "A" . Celý vzorec, ktorý sme použili pre tento výpočet, je nasledujúci:

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

    Samozrejme, v každom konkrétnom prípade sa súradnice v tomto vzorci líšia. Preto sa nemôže považovať za univerzálne. Ale pomocou vyššie uvedenej príručky môžete vložiť súradnice každej tabuľky a úspešne použiť túto metódu v každej situácii.

14. To však nie je všetko. Vypočítali sme len pre prvý riadok tabuľky. Ak chcete úplne vyplniť stĺpec "Skupina" s údajmi, musíte túto vzorec skopírovať do nasledujúceho rozsahu (okrem bunky v riadku "Celkom" ) pomocou značky naplnenia, ako sme to urobili viackrát. Po zadaní údajov sa môže ABC analýza považovať za splnenú.

Ako vidíte, výsledky získané pomocou variantu pomocou komplexného vzorca sa nelíšia od výsledkov, ktoré sme vykonali triedením. Všetky produkty majú rovnaké kategórie, ale ich riadky nezmenili pôvodnú pozíciu.

Lekcia: Sprievodca funkciami v programe Excel

Program Excel môže výrazne uľahčiť ABC analýzu pre používateľa. To sa dosiahne pomocou nástroja, akým je triedenie. Potom sa vypočíta individuálna váha, akumulovaný podiel a v skutočnosti zoskupenie. V prípadoch, keď nie je povolená zmena počiatočnej polohy riadkov v tabuľke, môžete použiť metódu pomocou zložitého vzorca.

• ← Použitie funkcie SELECT v programe Microsoft Excel
• Nastavte deň v týždni podľa dátumu v programe Microsoft Excel →