Aplikace analýzy ABC v aplikaci Microsoft Excel

Jednou z klíčových metod řízení a logistiky je analýza ABC. Pomocí této funkce můžete klasifikovat zdroje podniku, produktů, zákazníků atd. podle stupně důležitosti. Současně je podle úrovně důležitosti každá z výše uvedených jednotek přiřazena jedna ze tří kategorií: A, B nebo C. Excel má ve svých zavazadlech nástroje, které usnadňují provádění tohoto druhu analýzy. Zjistíme, jak je používat, a co je ABC analýza.

Pomocí analýzy ABC

ABC analýza je druh vylepšeného a přizpůsobeného moderním podmínkám varianta principu Pareto. Podle metodiky jejího chování jsou všechny prvky analýzy rozděleny do tří kategorií z hlediska významu:

• Kategorie A - prvky mající v celkovém součtu více než 80% specifické hmotnosti;
• Kategorie B - prvky, jejichž souhrn je od 5% do 15% měrné hmotnosti;
• Kategorie C - zbývající prvky, jejichž celkový součet je 5% a menší než specifická hmotnost.

Některé společnosti používají pokročilejší techniky a rozdělí elementy do 3, ale do 4 nebo 5 skupin, ale budeme se spoléhat na klasickou schéma analýzy ABC.

Metoda 1: analýza tříděním

V aplikaci Excel se ABC analýza provádí tříděním. Všechny prvky jsou seřazeny z většího na menší. Pak se počítá kumulativní specifická hmotnost každého prvku, na základě kterého je jí přiřazena určitá kategorie. Podívejme se na konkrétním příkladu, jak se tato technika používá v praxi.

Máme tabulku se seznamem zboží, které společnost prodává, a odpovídající částku tržeb z jejich prodeje po určitou dobu. Ve spodní části tabulky je dosaženo celkového příjmu všech položek zboží. Stojí za úkol, pomocí ABC analýzy, rozdělit tyto zboží do skupin podle jejich významu pro podnik.

1. Vyberte tabulku s daným kurzorem podržením levého tlačítka myši bez záhlaví a konečného řádku. Přejděte na kartu "Data" . Klikněte na tlačítko "Seřadit" , které se nachází v políčku "Seřadit a filtrovat" na pásu karet.

   Můžete se také chovat jinak. Vyberte výše uvedený rozsah tabulky, přejděte na kartu "Domov" a klikněte na tlačítko "Seřadit a filtr" umístěné v poli "Upravit" na pásu karet. Je aktivován seznam, ve kterém vybereme položku "Vlastní řazení" .

2. Pokud použijete některou z výše uvedených akcí, spustí se třídící okno. Uvidíme, že možnost "Moje údaje obsahuje záhlaví" byla zaškrtnuta. V případě jeho nepřítomnosti stanovíme.

   V poli "Sloupec" uvedeme název sloupce, který obsahuje údaje o výnosech.

   V poli "Seřadit" je třeba zadat konkrétní kritéria pro třídění. Ponecháme přednastavená nastavení - "Hodnoty" .

   V poli "Objednat" nastavíme pozici "Descending" .

   Po provedení zadaných nastavení klikněte na tlačítko "OK" ve spodní části okna.

3. Po provedení provedené akce byly všechny prvky seřazeny podle výnosů z největších na menší.
4. Nyní musíme vypočítat specifickou hmotnost každého prvku pro celkový součet. Pro tyto účely vytvoříme další sloupec, který budeme nazývat "Specifická váha" . V první buňce tohoto sloupce zadáme znaménko "=" , po níž uvedeme odkaz na buňku, ve které se nachází výnos z prodeje odpovídajícího produktu. Dále nastavíme značku rozdělení ( "/" ). Poté určete souřadnice buňky, která obsahuje celkový objem prodeje zboží v celém podniku.

   Vzhledem k tomu, že tento vzorec zkopírujeme do jiných buněk ve sloupci "Specifická hmotnost" pomocí značky pro vyplnění, musíme adresu linky upravit na prvek, který obsahuje celkovou částku výnosů podniku. Pro to uděláme absolutní referenci. Ve vzorci vyberte souřadnice zadané buňky a stiskněte klávesu F4 . Před souřadnicemi, jak vidíme, se objevil znak dolaru, který naznačuje, že spojení je absolutní. Je třeba poznamenat, že odkaz na příjmy prvního bodu v seznamu ( Tovar 3 ) by měl zůstat relativní.

   Potom proveďte výpočty stisknutím tlačítka Enter .

5. Jak můžete vidět, podíl výnosů z prvního produktu uvedeného v seznamu byl zobrazen v cílové buňce. Chcete-li zkopírovat vzorec do níže uvedeného rozsahu, vložte kurzor do pravého dolního rohu buňky. Je přeměněna na značku výplně, která vypadá jako malý kříž. Klepněte na levé tlačítko myši a přetáhněte značku výplně dolů na konec sloupce.
6. Jak vidíte, celý sloupec je vyplněn údaji, které charakterizují specifickou váhu výtěžku z prodeje každého produktu. Hodnota měrné hmotnosti je však zobrazena v číselném formátu a musíme ji změnit na procentní hodnotu. Chcete-li to provést, vyberte obsah sloupce "Specifická hmotnost" . Potom přejděte na kartu Domov . Na pásu karet v skupině nastavení "Počet" je pole, které zobrazuje formát dat. Ve výchozím nastavení byste neměli provádět další manipulace, měl by být nastaven formát "Obecné" . Klikneme na ikonu ve tvaru trojúhelníku, který se nachází napravo od tohoto pole. V otevřeném seznamu formátů vyberte položku "Zájem" .
7. Jak vidíte, všechny hodnoty sloupců byly převedeny na procentní hodnoty. Jak je očekáváno, řádek "Celkem" znamená 100% . Zvláštní hmotnost zboží se očekává ve sloupci od největšího k menšímu.
8. Nyní je třeba vytvořit sloupec, ve kterém se zobrazí souhrnná částka s kumulativním celkem. To znamená, že v každém řádku se specifická váha všech výrobků, které se nacházejí na výše uvedeném seznamu, přidá k individuální specifické hmotnosti určité komodity. U prvního produktu v seznamu ( položka 3 ) bude jednotlivá akcie a akumulovaná akcie stejná, ale pro všechny po jednotlivém ukazateli bude nutné přidat kumulovanou část předchozího prvku seznamu.

   Takže v prvním řádku přeneste do sloupce "Akumulovaný podíl" ukazatel ze sloupce "Specifická váha" .

9. Dále umístíme kurzor do druhé buňky sloupce "Akumulované sdílení" . Zde musíme použít tento vzorec. Vložíme značku "equal" a přidáme obsah článku "Specifická hmotnost" stejného řádku a obsah článku "Akumulované sdílení" z výše uvedeného řádku. Všechny odkazy jsou ponechány relativně, to znamená, že je nemanipulujeme. Poté klikneme na tlačítko Enter a zobrazíme konečný výsledek.
10. Nyní musíte tento vzorec zkopírovat do buněk tohoto sloupce, které jsou umístěny níže. K tomu použijeme značku naplnění, na kterou jsme se již při kopírování vzoru v sloupci "Specifická váha" uchýlili. V tomto případě není zapotřebí zachytit řádek "Celkem" , protože na posledním produktu ze seznamu se zobrazí souhrnný výsledek ve výši 100% . Jak vidíte, všechny elementy našeho sloupce byly poté vyplněny.
11. Poté vytvořte sloupec "Skupina" . Budeme potřebovat seskupovat zboží podle kategorií A , B a C podle nahromaděného podílu. Jak připomínáme, všechny prvky jsou seskupeny podle následujícího schématu:
    • A - až 80% ;
    • B - dalších 15% ;
    • C - zbývajících 5% .

    Veškeré zboží, jehož kumulativní podíl, jehož specifická hmotnost vstoupí na hranici na 80% , je tedy zařazen do kategorie A. Zboží s kumulovanou specifickou hmotností od 80% do 95% je přiřazeno do kategorie B. Zbývající skupina zboží s hodnotou vyšší než 95% nahromaděné specifické hmotnosti je zařazena do kategorie C.

12. Pro přehlednost můžete tyto skupiny vyplnit různými barvami. Ale to je podle vás.

Rozdělili jsme tedy prvky do skupin podle úrovně důležitosti pomocí analýzy ABC. Použití některých dalších technik, jak bylo uvedeno výše, rozdělení na více skupin platí, ale princip rozdělení zůstává prakticky nezměněn.

Lekce: Třídění a filtrování v aplikaci Excel

Metoda 2: Použití složitého vzorce

Samozřejmě, že třídění je nejběžnějším způsobem, jak provádět analýzu ABC v aplikaci Excel. Ale v některých případech je tato analýza vyžadována bez změny řádků v místech zdrojové tabulky. V tomto případě přijde na záchranu komplexní vzorec. Například použijeme stejnou zdrojovou tabulku jako v prvním případě.

1. Přidáme k počáteční tabulce, která obsahuje název zboží a výtěžek z prodeje každého z nich, sloupec "Skupina" . Jak vidíte, v tomto případě nemůžeme přidat sloupce s výpočtem jednotlivých a akumulačních akcií.
2. Vyberte první buňku ve sloupci "Skupina" a poté klikněte na tlačítko "Vložit funkci" vedle řádku vzorců.
3. Aktivuje se Průvodce funkcí . Přejdeme do kategorie "Odkazy a pole" . Zvolte funkci "SELECT" . Klikněte na tlačítko "OK" .
4. Okno argumentu funkce SELECT je aktivováno. Syntaxe je následující:

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

   Úkolem této funkce je výstup jedné ze zadaných hodnot v závislosti na indexovém čísle. Počet hodnot může dosáhnout 254, ale potřebujeme pouze tři jména, která odpovídají kategoriím analýzy ABC: A , B , C. Do pole "Value1" můžeme v poli "Value3" - "C" v poli "Value2" - "B" okamžitě zadat symbol "A " .

5. Ale s argumentem "Index číslo" bude muset důkladně dinker, mít vestavěl to několik dalších operátorů. Nastavte kurzor na pole "Indexové číslo" . Dále klikněte na ikonu, která vypadá jako trojúhelník, nalevo od tlačítka "Vložit funkci" . Zobrazí se seznam posledních operátorů. Potřebujeme funkci MATCH . Vzhledem k tomu, že seznam nezobrazuje, klikneme na nápis "Další funkce ..." .
6. Spustí se okno Průvodce z funkcí . Opět přejděte do kategorie "Odkazy a pole". Najdeme tam polohu "SEARCH" , vyberte jej a klikněte na tlačítko "OK" .
7. Otevře okno OPERÁTORŮ . Jeho syntaxe je následující:

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

   Účelem této funkce je určit číslo pozice zadaného prvku. To znamená, co potřebujeme pro pole "Indexové číslo" funkce SELECT .

   V poli "Scanned array" můžete okamžitě nastavit následující výraz:

   {0:0,8:0,95}

   Musí to být v kudrných příchytkách, jako je vzorec pole. Není těžké odhadnout, že tato čísla ( 0 ; 0,8 ; 0,95 ) označují hranice nahromaděného podílu mezi skupinami.

   Pole "Typ shody" je volitelné a v tomto případě jej nenaplníme.

   V poli "Hodnota vyhledávání" nastavíme kurzor. Poté znovu přes trojúhelník zobrazený nahoře přejdeme k Průvodci funkcí .

8. Tentokrát se v průvodci funkcí přesuneme do kategorie "Matematická" . Vybíráme název "SUMMER" a klikneme na tlačítko "OK" .
9. Zobrazí se okno s argumenty funkce SUMIFER . Uvedený operátor shrnuje buňky, které splňují zadané podmínky. Jeho syntax je následující:

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

   Do pole "Rozsah" zadejte adresu sloupce "Příjmy" . Pro tento účel nastavíme kurzor v poli a poté, když stiskneme levou klávesu myši, vybereme všechny buňky příslušného sloupce, s výjimkou hodnoty "Total" . Jak vidíte, adresa byla okamžitě zobrazena v poli. Kromě toho musíme tento odkaz považovat za absolutní. Chcete-li to provést, proveďte jeho výběr a klikněte na tlačítko F4 . Adresa vyčnívala dolarovými znaky.

   V poli "Kritéria" musíme určit podmínku. Zadejte následující výraz:

   ">"&

   Pak ihned po zadání adresy první buňky ve sloupci "Příjmy" . Horizontální souřadnice v této adrese děláme absolutní, přidáváme znak dolaru z klávesnice před písmenem. Souřadnice na svislé čáře zůstávají relativní, to znamená, že před číslem by neměla být žádná značka.

   Potom klikněte na tlačítko "OK" , ale klikněte na název funkce MATCH ve vzorové liště.

10. Pak se vrátíme do okna argumentů funkce MATCH . Jak vidíte, v poli "Hodnota hledání" se objevily údaje zadané provozovatelem SUMESELI . Ale to není všechno. Přejděte do tohoto pole a přidejte znak "+" bez uvozovek do existujících dat. Poté zadáme adresu první buňky do sloupce "Příjmy" . A znovu provedeme horizontální souřadnice tohoto referenčního absolutního a svisle opustíme relativní.

    Dále vezměme celý obsah pole "Požadovaná hodnota" v závorkách, po které uložíme znak rozdělení ( "/" ). Pak opět přes ikonu trojúhelníku přejděte do okna výběru funkcí.

11. Stejně jako v poslední době v spuštěné funkci průvodce hledáme požadovaný operátor v kategorii "Matematický" . Tentokrát se požadovaná funkce nazývá "SUMM" . Vyberte jej a klikněte na tlačítko "OK" .
12. Otevře okno argumentů operátora SUM . Jeho hlavním účelem je shrnutí dat v buňkách. Syntaxe tohoto operátora je poměrně jednoduchá:

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

    Pro naše účely je zapotřebí pouze pole "Number1" . Zadejte souřadnice rozsahu sloupce "Výnos" , vyjma buňky, která obsahuje součty. Tuto operaci jsme již provedli v poli "Rozsah" funkce SUMMER . Stejně jako v té době jsou souřadnice rozsahu absolutní, jejich výběr a stisknutí klávesy F4 .

    Poté klikněte na tlačítko "OK" ve spodní části okna.

13. Jak můžete vidět, sada vstupních funkcí provedla výpočet a výsledek vyvedla do první buňky sloupce "Skupina" . První produkt byl zařazen do skupiny "A" . Celý vzorec, který jsme použili pro tento výpočet, je následující:

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

    Samozřejmě, v každém konkrétním případě se souřadnice v tomto vzorci liší. Proto nelze považovat za univerzální. Ale pomocí výše uvedené příručky můžete vložit libovolnou tabulku a úspěšně použít tuto metodu v libovolné situaci.

14. Nicméně to není vše. Vypočítali jsme pouze pro první řádek tabulky. Chcete-li sloupec "Skupina" vyplnit úplně s údaji, musíte tento vzorec zkopírovat do níže uvedeného rozsahu (s výjimkou buňky řádku "Celkem" ) pomocí značky pro vyplnění, jak jsme to udělali více než jednou. Po zadání dat lze ABC analýzu považovat za splněnou.

Jak můžete vidět, výsledky získané pomocí varianty pomocí komplexního vzorce se neliší od výsledků, které jsme provedli tříděním. Všechny produkty mají stejné kategorie, ale jejich řádky nezměnily svou počáteční pozici.

Lekce: Průvodce funkcemi v aplikaci Excel

Program Excel může výrazně usnadnit ABC analýzu pro uživatele. Toho lze dosáhnout pomocí nástroje, jako je třídění. Poté se vypočítá individuální váha, akumulovaný podíl a ve skutečnosti seskupení. V případech, kdy není změna výchozí pozice řádků v tabulce povolena, můžete použít metodu pomocí složitého vzorce.

• ← Použití funkce SELECT v aplikaci Microsoft Excel
• Nastavte den v týdnu podle data v aplikaci Microsoft Excel →