ABC analýza v aplikaci Excel

Jedním z klíčových metod řízení a logistiky je ABC analýza. S tím můžete klasifikovat zdroje podniku, zboží, zákazníků atd. Podle míry důležitosti. Současně podle stupně důležitosti je každá z výše uvedená jednotka přiřazena jedna ze tří kategorií: A, B nebo C. Excel Program má ve svých nástrojích zavazadel, které usnadňují provádění tohoto druhu analýzy. Podívejme se, jak je používat, a co je analýza ABC.

Pomocí ABC analýzy

ABC analýza je druhem zvýšeného a přizpůsobeného moderním podmínkám pro princip Pareto. Podle způsobu jeho chování jsou všechny prvky analýzy rozděleny do tří kategorií podle stupně důležitosti:

• Kategorie A - prvky mají kombinaci více než 80% specifické gravitace;
• Kategorie B - prvky, jejichž kombinace se pohybuje od 5% do 15% specifické gravitace;
• Kategorie C - zbývající prvky, jejichž celkový agregát je 5% a méně specifická gravitace.

Samostatné podniky používají pokročilejší techniky a rozdělit položky ne na 3, ale 4 nebo 5 skupin, ale budeme spoléhat na klasickou analýzu ABC.

Metoda 1: Analýza s třídícím

Excel ABC analýza se provádí pomocí třídění. Všechny položky jsou seřazeny z mastného na méně. Pak se vypočítá akumulační podíl každého prvku, na jejichž základě je přidělena určitou kategorii. Zjistíme, jak se v praxi používá specifikovaná metodika.

Máme tabulku se seznamem produktů, které společnost prodává, a odpovídající počet příjmů z jejich prodeje po určitou dobu. V dolní části tabulky, výsledek příjmů obecně na všech názvech zboží. Existuje úkol pomocí ABC analýzy, porušit tyto zboží do skupin podle jejich důležitosti pro podnik.

1. Zvýrazňujeme tabulku s kurzorem zavřením levého tlačítka myši, s výjimkou víčko a finálního řetězce. Přejděte na kartu "Data". Klikáváme na tlačítko "Seřadit", umístěný v panelu nástrojů "Seřadit a filtr" na pásku.

   

   Můžete také udělat jinak. Přidělujeme výše uvedený rozsah tabulky, pak se přesuňte na kartu "Domovská stránka" a klikněte na tlačítko "Seřadit a filtr" umístěný v nástroji pro úpravy nástrojů na pásku. Seznam je aktivován, ve kterém je aktivována poloha "Přizpůsobitelné třídění".



2. Při použití některého z výše uvedených akcí se spouští okno nastavení třídění. Díváme se tak, že "Moje data obsahuje parametr záhlaví" byl nastaven na zaškrtnutí. V případě jeho nepřítomnosti instalujte.

   V poli "Sloupec" zadejte název sloupce, ve kterém jsou data příjmů obsažena.

   V poli "Seřadit" musíte určit, na které budou seřazeny konkrétní kritéria. Nechte přednastavené nastavení - "Hodnoty".

   V poli "Objednat" vykazují polohu "Descending".

   Po produktu určených nastavení klikněte na tlačítko "OK" v dolní části okna.



3. Po provedení zadané akce byly všechny položky seřazeny podle příjmů od více na menší.



4. Nyní bychom měli vypočítat podíl každého z prvků pro obecný výsledek. Vytvořit další sloupec pro tyto účely, které nazýváme "sdílení váhy". V první buňce tohoto sloupce jsme dali znamení "=", po které zadáte odkaz na buňku, ve které je výši příjmů z provádění příslušného produktu. Dále nastavte znaménko divize ("/"). Poté uvádíme souřadnice buňky, která obsahuje celkovou částku prodeje zboží v celém podniku.

   Vzhledem k tomu, že zadaný vzorec, budeme kopírovat do jiných buněk sloupec "sdílení" sloupec plnicí marker, adresa vazby na prvek, který obsahuje konečné množství příjmů podniku, musíme opravit. Chcete-li to udělat, proveďte absolutní. Vyberte souřadnice zadané buňky ve vzorci a stiskněte klávesu F4. Před souřadnicemi, jak vidíme, objevil se znak dolaru, což naznačuje, že odkaz se stal absolutním. Je třeba poznamenat, že odkaz na výši příjmů z prvního v seznamu zboží (výrobek 3) by měl zůstat relativní.

   Poté proveďte výpočty, stiskněte tlačítko ENTER.



5. Jak vidíme, podíl příjmů z prvního produktu uvedeného v seznamu se objevil v cílové buňce. Chcete-li zkopírovat vzorec v níže uvedeném rozsahu, vložíme kurzor do pravého dolního rohu buňky. Jeho transformace v plnicím markeru, která má vzhled malého kříže. Klikneme 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 plný údajů charakterizujících podíl příjmů z realizace každého produktu. Ale hodnota specifické gravitace je zobrazena v numerickém formátu a musíme ji transformovat do procentu. Chcete-li to provést, zvýrazněte obsah sloupce "specifická váha". Pak se přesuneme na kartu "Domovská stránka". Na pásku ve skupině Settings Settings existuje pole zobrazující formát dat. Ve výchozím nastavení, pokud jste neprodukujete další manipulace, musí být zde instalován formát "General". Klikněte na ikonu ve formě trojúhelníku umístěné vpravo od tohoto pole. V seznamu formátů vyberte pozici "Procento".



7. Jak vidíme, všechny hodnoty sloupců byly transformovány na procentní hodnoty. Jak by mělo být, 100% uvedeno v řetězci "Celkem". Podíl zboží se očekává ve sloupci od většího na menší.



8. Nyní bychom měli vytvořit sloupec, ve kterém by byl zobrazen akumulovaný podíl s rostoucím výsledkem. To znamená, že v každém vedení k individuální specifické hmotnosti konkrétního produktu bude přidán podíl všech těchto produktů, které jsou umístěny v seznamu výše. Pro první zboží v seznamu (výrobek 3) bude individuální poměr a akumulovaný podíl stejný, ale všechny po jednotlivém ukazateli bude muset přidat akumulovaný podíl předchozího prvku seznamu.

   Takže v prvním řádku jsme převedeni do sloupce "Akumulovaný sdílení" indikátor sloupce "specifický".



9. Dále nastavte kurzor do druhé buňky sloupce "akumulované sdílené složky". Zde musíme aplikovat vzorec. Dali jsme znak "EQUAL" a složit obsah buňky "Podíl" stejné linky a obsahu buňky "akumulovaný sdílení" z řetězce výše. Všechny odkazy jsou rezervovány relativní, to znamená, že s nimi neprodukujeme žádné manipulace s nimi. Poté proveďte kliknutím na tlačítko Enter, abyste zobrazili konečný výsledek.



10. Nyní musíte tento vzorec zkopírovat v buňkách tohoto sloupce, které jsou umístěny níže. K tomu použijeme plnicí marker, ke kterému jsme již uchýlili při kopírování vzorce v sloupci Share. Současně není nutný řetězec "Celkem", protože akumulovaný výsledek je 100% zobrazen na posledním produktu ze seznamu. Jak vidíte, všechny prvky našeho sloupu poté byly vyplněny.



11. Poté vytvořte sloupec "Group". Budeme muset skupinovat zboží podle kategorie A, B a C podle specifikovaného akumulovaného podílu. Jak si pamatujeme, všechny prvky jsou distribuovány skupinami podle následujícího schématu:
    • A - až 80%;
    • B - následující 15%;
    • C - zbývající 5%.

    Veškeré zboží, které nahromadil podíl konkrétní hmotnosti, jehož je v rámci hranice až 80%, přidělíme kategorii A. Zboží s akumulovanou specifickou hmotností z 80% na 95% je přiřazeno do kategorie B. Zbývající Skupina zboží s hodnotou více než 95% akumulované specifické gravitace přiřazujeme kategorii C.



12. Pro přehlednost můžete vyplnit zadané skupiny s různými barvami. Ale je to vůle.

Proto jsme porušili prvky na úrovni důležitosti pomocí ABC analýzy. Při použití některých dalších technik, jak je uvedeno výše, slouží k rozdělení většího počtu skupin, ale zásada lámání v tomto případě zůstává téměř beze změny.

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

Metoda 2: Použití komplexního vzorce

Samozřejmě použití třídění je nejčastější způsob, jak provádět ABC analýzu v Excelu. V některých případech je však nutné tuto analýzu provádět bez přestavných linek v místech ve zdrojové tabulce. V tomto případě bude komplexní vzorec přijde na záchranu. Například použijeme stejnou zdrojovou tabulku jako v prvním případě.

1. Přidáme do zdrojové tabulky obsahující název zboží a příjmy 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 frakcí.



2. Vyrábíme první alokaci buněk ve sloupci skupiny, po které provedete kliknutím na tlačítko "Insert Function" umístěné v blízkosti řádku vzorce.



3. Masters aktivace funkcí. Přesuňte se do kategorie "Odkazy a pole". Vyberte funkci "Choice". Proveďte kliknutím na tlačítko "OK".



4. Je aktivováno okno argumenty hry. Syntaxe je prezentována následovně:

   = Výběr (number_intex; hodnota 1; ...)

   Úkolem této funkce je výběr jedné ze zadaných hodnot v závislosti na čísle indexu. Počet hodnot může dosáhnout 254, ale budeme potřebovat pouze tři názvy, které odpovídají kategoriím ABC-analýzy: A, B, C. Můžeme okamžitě zadat symbol "A" v poli "B" v "B" "Pole, pole" Value3 "-" C ".



5. Ale s argumentem "indexové číslo" bude muset být důkladně vypuštěn budováním několika dalších operátorů. Nainstalujte kurzor do pole "Indexové číslo". Dále klikněte na ikonu s pohledem na trojúhelník, vlevo od tlačítka "Insert Function". Otevře se seznam nově použitých operátorů. Potřebujeme funkci vyhledávání. Protože není v seznamu, pak klikneme na nápis "Ostatní funkce ...".



6. Okno okna Průvodce funkcím se spustí znovu. Znovu přejděte do kategorie "Odkazy a pole". Našli jsme pozici "vyhledávací desky", přidělujeme ji a proveďte kliknutím na tlačítko "OK".



7. Otevře se argumenty argumentů pro vyhledávání. Syntaxe má následující formulář:

   = Vyhledávací deska (hledání_name; prohlížení_masivní; typ_stace)

   Účelem této funkce je definovat číslo pozice zadané položky. To znamená, že to, co potřebujeme pro pole "Index číslo", funkce.

   V poli "Výpis pole" můžete okamžitě zeptat následující výraz:

   {0: 0.8: 0,95}

   Mělo by to být právě v kudrnatých závorkách, jako vzorec pole. Není těžké odhadnout, že tato čísla (0; 0,8; 0,95) označují hranice akumulovaného podílu mezi skupinami.

   Pole "Typ porovnání" není povinné a v tomto případě ho nevyplníme.

   V poli "Druhá hodnota" nastavte kurzor. Další opět ikonou popsanou výše ve formě trojúhelníku se přesuneme do průvodce funkcí.



8. Tentokrát v Průvodci funkcím se pohybujeme do kategorie "Matematical". Vyberte název "tichý" a stiskněte tlačítko "OK".



9. Bude spuštěno okno argumenty funkce. Zadaný operátor shrnuje buňky, které splňují určitý stav. Jeho syntaxe je:

   = Tichý (rozsah; kritérium; rozsah_suming)

   Do pole "Range" zadejte adresu sloupce "Příjmy". Pro tyto účely nastavíme kurzor v poli, a pak vytvořením klip levého tlačítka myši vyberte všechny buňky odpovídajícího sloupce, s výjimkou hodnoty "Celkem". Jak vidíte, adresa se okamžitě objevila v poli. Kromě toho musíme tento odkaz absolutně učinit. Chcete-li to provést, produkujte jeho přidělení a klikněte na klávesu F4. Adresa byla vydána znaky dolaru.

   V poli "Kritérium" musíme nastavit stav. Zadejte následující výraz:

   ">"&

   Pak ihned po něm zadáme adresu první buňky sloupce "Příjmy". Děláme vodorovně souřadnice v této adrese absolutně, přidáním znaku dolaru z klávesnice před dopisem. Vertikální souřadnice opouštějí relativní, to znamená, že před číslic by neměly být žádné znamení.

   Poté nestiskneme tlačítko "OK" a klikněte na název funkce vyhledávání v řetězci vzorce.



10. Pak se vrátíme do okna argumenty vyhledávací desky. Jak vidíme, v poli "fogulární význam", data stanovená operátorem mlčel. Ale to není všechno. Přejděte do tohoto pole a již přidává dostupná data přidat znak "+" bez uvozovek. Pak zavádíme adresu první buňky sloupce "Příjmy". A opět vytvářejí horizontálně s absolutním a svisle opouštět příbuzným.

    Dále vezmeme veškerý obsah pole "požadovanou hodnotu" v závorkách, po kterém jsme dali znamení divize ("/"). Poté, znovu přes ikonu trojúhelníku, přejděte do funkce výběru funkcí.



11. Stejně jako naposledy v Průvodci spuštěním funkce hledáme potřebný provozovatel v kategorii "Matematická". Tentokrát se požadovaná funkce nazývá "SUMS". Zvýrazňujeme to a stiskněte tlačítko "OK".



12. Otevře se okno Argumenty operátora. Hlavním účelem je součtem dat v buňkách. Syntaxe tohoto operátora je poměrně jednoduchá:

    = Částky (číslo1; číslo2;)

    Pro naše účely bude zapotřebí pouze pole "číslo11". Zavedeme do ní souřadnice sloupce "příjmy", což eliminuje buňku, která obsahuje výsledky. Takovou operaci jsme již provedli v poli "Rozsah" funkce. Stejně jako v té době, souřadnice rozsahu absolutně zvýrazní a stisknete klávesu F4.

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



13. Jak vidíte, komplex zadaných funkcí provedl výpočet a vydal výsledek v první buňce sloupce "Group". První produkt byl přiřazen skupinu "A". Úplný vzorec aplikovaný USA pro tento výpočet je následující:

    = Volba (vyhledávací deska ((tichý (tichý ($ b $ 2: $ b $ 27; ">" & $ b2) + $ b2) / částky ($ B $ 2: $ b $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")

    Ale samozřejmě, v každém případě budou souřadnice v tomto vzorci odlišné. Proto nemůže být považován za univerzální. Použití návodu, který byl uveden výše, můžete vložit souřadnice jakékoli tabulky a úspěšně aplikovat tuto metodu v jakékoliv situaci.



14. To však není vše. Vypočítali jsme se pouze pro první řadu tabulky. Aby bylo možné plně vyplnit skupinu "Group" sloupec, musíte tento vzorec zkopírovat v níže uvedeném rozsahu (eliminovat buňku řetězce "Celkem") pomocí plnicího markeru, jak jsme již provedli více než jednou. Po provedení dat lze provádět analýza ABC.

Jak vidíme, výsledky získané pomocí volby pomocí komplexního vzorce se neliší od těch výsledků, které jsme provedli třídění. Všechny zboží je přiřazeno stejné kategorie, pouze zároveň řádky nezměnily svou počáteční polohu.

Lekce: Mistr funkcí v Excelu

Program Excel je schopen významně zmírnit ABC analýzu pro uživatele. Toho je dosaženo použitím takového nástroje jako třídění. Poté se vypočítá individuální specifická gravitace, akumulovaný podíl a vlastně rozdělení do skupin. V případech, kdy změna počáteční polohy řádků v tabulce není povolena, můžete použít metodu pomocí komplexního vzorce.