ANBC ANALÝZA V EXCEL

Jedným z kľúčových metód riadenia a logistiky je ABC ANALÝZA. S tým môžete klasifikovať zdroje podniku, tovaru, zákazníkov atď. Podľa stupňa významu. Zároveň, podľa úrovne dôležitosti, každá z uvedená jednotka je pridelená jednou z troch kategórií: A, B alebo C. Excel program má vo svojich batožinových nástrojoch, ktoré uľahčujú vykonanie tohto druhu analýzy. Poďme zistiť, ako ich používať, a čo je ABS ABC.

Použitie ABS ANBC

ANBC analýza je druh posilnenej a prispôsobenej moderným podmienkam pre princíp parento. Podľa spôsobu svojho konania sú všetky prvky analýzy rozdelené do troch kategórií podľa stupňa dôležitosti:

• Kategória A - Prvky, ktoré majú kombináciu viac ako 80% špecifickej hmotnosti;
• Kategória B - prvky, ktorých kombinácia sa pohybuje od 5% do 15% špecifickej hmotnosti;
• Kategória C - Zostávajúce prvky, ktorého celkový agregát je 5% a menej špecifická hmotnosť.

Samostatné spoločnosti používajú pokročilejšie techniky a rozdeľujú položky, ktoré nie sú 3, ale 4 alebo 5 skupín, ale my sa spoliehame na klasickú analýzu ABC analýzy.

Metóda 1: Analýza s triedením

Analýza Excel ABC sa vykonáva pomocou triedenia. Všetky položky sú zoradené od mastného na menej. Potom sa vypočíta akumulatívny podiel každého prvku, na základe ktorých je priradená určitá kategória. Pozrime sa, ako sa v praxi používa zadaná metodika.

Máme tabuľku so zoznamom výrobkov, ktoré spoločnosť predáva, a zodpovedajúci počet výnosov z ich predaja na určité časové obdobie. V spodnej časti tabuľky, výsledok príjmov vo všeobecnosti na všetkých menách tovaru. Tam je úloha pomocou ABC ANALÝZA, BRUKT Tento tovar do skupín podľa ich dôležitosti pre podnik.

1. Zvýrazňujeme tabuľku s kurzorom zatvorením ľavého tlačidla myši, s výnimkou čiapky a konečného reťazca. Prejdite na kartu "Údaje". Kliknutím na tlačidlo "Triediť", ktorý sa nachádza v paneli nástrojov "Zoradiť a filter" na páske.

   

   Môžete tiež robiť inak. Prideľujeme vyššie uvedený rozsah tabuľky, potom sa presuniete na kartu "HOME" a kliknite na tlačidlo "Triediť a filter" umiestnené v nástroji na úpravu nástrojov na pásku. Zoznam je aktivovaný, v ktorom je aktivovaná poloha "prispôsobiteľné triedenie".



2. Pri použití niektorého z vyššie uvedených akcií spúšťa okno nastavení triedenia. Vyzeráme tak, že "Moje údaje obsahuje hlavičky" parameter bol nastavený na začiarknutie. V prípade jeho neprítomnosti inštaláciu.

   V poli "Stĺpec" zadajte názov stĺpca, v ktorom sú údaje o príjmoch obsiahnuté.

   V poli "Zoradiť", musíte zadať, na ktoré špecifické kritériá budú zoradené. Nechajte nastavenia predvoľby - "Hodnoty".

   V poli "Objednávka" vykazujú polohu "Zostupu".

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



3. Po vykonaní zadanej akcie boli všetky položky zoradené podľa príjmov z viac na menšie.



4. Teraz by sme mali vypočítať časť každého prvku na všeobecný výsledok. Vytvorte ďalší stĺpec na tieto účely, ktoré nazývame "Hmotnosť akcií". V prvej bunke tohto stĺpca sme vložili znak "=", po ktorom zadáte odkaz na bunku, v ktorej je výška príjmov z implementácie príslušného výrobku. Ďalej nastaviť znamenie rozdelenia ("/"). Potom uvádzame súradnice bunky, ktorá obsahuje celkové množstvo predaja tovaru v celom podniku.

   Vzhľadom na skutočnosť, že špecifikovaná vzorca budeme kopírovať do iných buniek stĺpca "Share" pomocou výplňového markera, adresa prepojenia s prvkom obsahujúcim konečnú sumu príjmov do podniku, musíme opraviť. Urobte to, urobte odkaz absolútny. Vyberte súradnice špecifikovanej bunky vo vzorci a stlačte kláves F4. Pred súradnicou, ako vidíme, objavil sa znak dolára, čo naznačuje, že odkaz sa stal absolútnym. Treba poznamenať, že odkaz na výšku príjmov z prvého v zozname tovaru (výrobok 3) by mal zostať relatívny.

   Ak chcete vykonať výpočty, stlačte tlačidlo ENTER.



5. Ako vidíme, podiel príjmov z prvého produktu uvedeného v zozname sa objavil v cieľovej bunke. Ak chcete kopírovať vzorca v rozsahu nižšie, vložíme kurzor do pravého dolného rohu bunky. Jeho transformácia v náplňovom marker, ktorá má vzhľad malého kríža. Kliknite na ľavé tlačidlo myši a presuňte značku výplne nadol na koniec stĺpca.



6. Ako vidíte, celý stĺpec je naplnený údajmi charakterizujúcimi podiel príjmov z realizácie každého výrobku. Hodnota konkrétnej gravitácie sa však zobrazuje v numerickom formáte a musíme ju premeniť na percento. Ak chcete urobiť, zvýraznite obsah stĺpca "Špecifická hmotnosť". Potom sa presunieme na kartu "HOME". Na páske v skupine Skupiny sa nachádza pole zobrazujúci formát údajov. V predvolenom nastavení, ak ste nevytvorili dodatočné manipulácie, musí byť tam nainštalovaný formát "všeobecného". Kliknite na ikonu vo forme trojuholníka umiestneného na pravej strane tohto poľa. V zozname formátov vyberte polohu "percento".



7. Ako vidíme, všetky hodnoty stĺpcov boli transformované na percentuálne hodnoty. Ako by to malo byť 100% uvedené v reťazci "Celkom". Podiel tovaru sa očakáva v stĺpci z väčšieho na menšie.



8. Teraz by sme mali vytvoriť stĺpec, v ktorom sa zobrazí akumulovaný podiel s rastúcim výsledkom. To znamená, že v každom riadku k individuálnej špecifickej hmotnosti konkrétneho produktu bude pridaný podiel všetkých týchto produktov, ktoré sa nachádzajú v zozname vyššie. Pre prvý tovar v zozname (produkt 3) bude individuálny podiel a nahromadený podiel rovnaký, ale všetky nasledujúce po individuálnom indikátore budú musieť pridať kumulovaný podiel predchádzajúceho prvku zoznamu.

   Takže v prvom riadku sa prenesieme do stĺpca "Akumulovaný zdieľanie" indikátor stĺpca "špecifický".



9. Potom nastavte kurzor na druhú bunku stĺpca "Akumulované zdieľanie". Tu musíme aplikovať vzorec. Značkujeme "rovnaké" a zložte obsah bunky "podiel" tej istej čiary a obsah bunky "akumulovaný podiel" z vyššie uvedeného reťazca. Všetky odkazy sú vyhradené relatívne, to znamená, že s nimi nevytvárame žiadne manipulácie. Potom vykonajte kliknutím na tlačidlo ENTER, aby ste zobrazili konečný výsledok.



10. Teraz musíte skopírovať tento vzorec v bunkách tohto kolóny, ktoré sú umiestnené nižšie. Aby sme to urobili, používame náplňový marker, ku ktorému sme sa už uchýlili k kopírovaniu vzorec v stĺpci akcií. Súčasne nie je reťazec "celkom" potrebný, pretože akumulovaný výsledok je 100% sa zobrazí na poslednom produkte zo zoznamu. Ako môžete vidieť, všetky prvky nášho stĺpca potom, čo boli vyplnené.



11. Potom vytvorte stĺpec "skupina". Budeme musieť zoskupovať tovar podľa kategórie A, B a C podľa zadaného kumulovaného podielu. Ako si pamätáme, všetky prvky sú distribuované skupinami podľa nasledujúcej schémy:
    • A - až 80%;
    • B - Nasledujúce 15%;
    • C - Zvyšných 5%.

    Všetok tovar, ktorý sa nahromadil podiel konkrétnej hmotnosti, ktorej je v hraniciach až do výšky 80%, prideľujeme kategóriu A. Tovar s kumulovanou osobitnou hmotnosťou z 80% na 95% sú priradené k kategórii B. Skupina tovaru s hodnotou viac ako 95% kumulovanej špecifickej hmotnosti prideľujeme kategóriu C.



12. Pre jasnosť môžete vyplniť špecifikované skupiny rôznymi farbami. Ale je to na vôli.

Preto sme rozbili prvky na úrovni dôležitosti, pomocou ABC analýzy. Pri použití niektorých iných techník, ako je uvedené vyššie, sa používa na preniknutie do väčšieho počtu skupín, ale zásada lámania v tomto prípade zostáva takmer nezmenený.

Lekcia: Triedenie a filtrovanie v Excel

Metóda 2: Použitie komplexného vzorca

Samozrejme, používanie triedenia je najbežnejším spôsobom, ako vykonávať ABC analýzu v exteriéri. Ale v niektorých prípadoch je potrebné vykonať túto analýzu bez preskupenia čiar v miestach v zdrojovej tabuľke. V tomto prípade príde komplexný vzorec na záchranu. Napríklad, použijeme rovnakú zdrojovú tabuľku ako v prvom prípade.

1. Pridávame do zdrojovej tabuľky obsahujúcej názov tovaru a príjmy z predaja každého z nich, stĺpec "skupina". Ako vidíte, v tomto prípade nemusíme pridať stĺpce s výpočtom individuálnych a akumulačných frakcií.



2. Vyrábame prvé alokácie buniek v stĺpci skupiny, po ktorom vykonáte kliknutím na tlačidlo "Vložiť funkciu", ktorá sa nachádza v blízkosti riadku vzorca.



3. Masters aktivácia funkcií. Prestupujeme do kategórie "Odkazy a polia". Vyberte funkciu "Choice". Kliknutím na tlačidlo "OK".



4. Okno Argument hra je aktivované. Syntax je prezentovaná nasledovne:

   = Výber (číslo_intex; hodnota1; hodnota2; ...)

   Úlohou tejto funkcie je výber jednej zo zadaných hodnôt, v závislosti od čísla indexu. Počet hodnôt môže dosiahnuť 254, ale budeme potrebovať len tri mená, ktoré zodpovedajú kategóriám ABC-analýzy: A, B, C. Môžeme okamžite zadať symbol "B" v poli "B" v "B" "pole, pole" hodnota3 "-" C ".



5. Ale s argumentom "indexové číslo" bude musieť byť dôkladne prepojené budovaním niekoľkých ďalších operátorov. Nainštalujte kurzor do poľa "Indexové číslo". Ďalej kliknite na ikonu s výhľadom na trojuholník, vľavo od tlačidla "INSERT FUNCTION". Otvorí sa zoznam novo používaných operátorov. Potrebujeme funkciu vyhľadávania. Keďže to nie je v zozname, potom klikneme na nápisy "Iné funkcie ...".



6. Opäť sa spustí okno Windows Wizard Wizard. Opäť prejdite na kategóriu "Odkazy a polia". Nájdeme pozíciu "vyhľadávacej rady", prideľujeme ho a vytvorte kliknutím na tlačidlo "OK".



7. Otvorí sa argumenty argumentov vyhľadávacieho operátora. Syntax má nasledujúci formulár:

   = Hľadajúca doska (search_name; prezeranie__massive; type_station)

   Účelom tejto funkcie je definovať číslo polohy zadanej položky. To znamená, čo potrebujeme pre pole "Indexové číslo" funkcia.

   V poli "Listing Array" môžete okamžite požiadať nasledujúci výraz:

   {0: 0,8: 0,95}

   Mal by byť presne v kučeravých zátvorkách, ako vzorec poľa. Nie je ťažké hádať, že tieto čísla (0; 0,8; 0,95) označujú hranice nahromadeného podielu medzi skupinami.

   Pole "Typ porovnania" nie je povinný av tomto prípade ho nevyplníme.

   V poli "Druhá hodnota" nastavte kurzor. Ďalej opäť cez ikonu opísanú vyššie vo forme trojuholníka, prejdeme sa k sprievodcovi funkcií.



8. Týmto časom v Sprievodcovi funkcií, robíme sa presunúť do kategórie "matematické". Vyberte názov "Tichý" a stlačte tlačidlo "OK".



9. Spustí sa okno argumentov funkcie. Zadaný operátor sumarizuje bunky, ktoré spĺňajú určitú podmienku. Jeho syntax je:

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

   V poli "Range" zadajte adresu stĺpca "Príjmy". Na tieto účely sme nastavili kurzor v poli a potom vykonaním klipu ľavého tlačidla myši vyberte všetky bunky zodpovedajúceho stĺpca, s výnimkou hodnoty "Celkom". Ako vidíte, adresa sa okamžite objavila v poli. Okrem toho musíme tento odkaz absolútne. Ak to chcete urobiť, produkujte jeho alokáciu a kliknite na tlačidlo F4. Adresa bola vydaná známkami dolára.

   V oblasti "Kritérium" musíme nastaviť podmienku. Zadajte nasledujúci výraz:

   ">"&

   Potom ihneď po tom zadáme adresu prvej bunky stĺpca "príjmu". Robíme súradnice horizontálne v tejto adrese absolútne, pridávanie znak dolára z klávesnice pred listom. Vertikálne súradnice opúšťajú príbuzné, to znamená, že by nemalo byť žiadne znamenie pred číslicou.

   Potom nestlačíme tlačidlo "OK" a kliknite na názov funkcie vyhľadávania vo formuláre reťazca.



10. Potom sa vrátime do okno argumentov výhonky. Ako vidíme, v poli "Hrozný význam", údaje nastavené prevádzkovateľom mlčali. Ale to nie je všetko. Prejdite na toto pole a už pridá k dostupným údajom na pridanie znaku "+" bez úvodzoviek. Potom predstavíme adresu prvej bunky stĺpca "príjmu". A opäť robíme súradnice horizontálne s absolútnymi a vertikálne opustil príbuzných.

    Ďalej berieme všetky obsah poľa "požadovaná hodnota" v zátvorkách, po ktorej sme dali znamenie rozdelenia ("/"). Potom, opäť cez ikonu trojuholníka, prejdite na funkciu voľby funkcií.



11. Rovnako ako naposledy v Sprievodcovom spustenej funkcii, hľadáme potrebný prevádzkovateľ v kategórii "matematická". Tentokrát sa požadovaná funkcia nazýva "sumy". Zvýrazňujeme ho a stlačte tlačidlo "OK".



12. Otvorí sa okno Argumenty operátora. Jeho hlavným cieľom je súčet údajov v bunkách. Syntax tohto operátora je celkom jednoduchá:

    = Sumy (číslo1; číslo2; ...)

    Na naše účely bude potrebná iba pole "Number1". Predstavujeme do neho súradnice stĺpca "príjmu", eliminuje bunku, ktorá obsahuje výsledky. Túto operáciu sme už vykonali v poli "Range" funkcie. Rovnako ako v tom čase, súradnice rozsahu absolútne, zvýrazňujú ich a stlačením klávesu F4.

    Po tom, kliknite na tlačidlo "OK" v dolnej časti okna.



13. Ako vidíte, komplex zadaných funkcií urobil výpočet a vydal výsledok v prvej bunke stĺpca "skupiny". Prvý produkt bol pridelený skupinu "A". Úplný formurát aplikovaný nami pre tento výpočet je nasledovný:

    = Voľba (vyhľadávacia doska ((Silent ($ B $ 2: $ B $ 27; ">"> "& $ B2) + $ B2) / Sumy ($ B $ 2: $ B $ 27); , 95}); "A"; "B"; "C")

    Samozrejme, v každom prípade budú súradnice v tomto vzorci odlišné. Preto nemožno považovať za univerzálne. Ale pomocou príručky, ktorý bol uvedený vyššie, môžete vložiť súradnice akejkoľvek tabuľky a úspešne aplikovať túto metódu v akejkoľvek situácii.



14. Toto však nie je všetko. Vypočítali sme len pre prvý riadok stola. Aby ste úplne vyplnili stĺpec skupiny "Group", musíte skopírovať tento vzorec v rozsahu nižšie (eliminácia bunky reťazca "celkom") pomocou náplne marker, ako sme už urobili viac ako raz. Po vykonaní údajov je možné zvážiť analýzu ABC.

Ako vidíme, výsledky získané pomocou možnosti použitím komplexného vzorca sa nelíšia od výsledkov, ktoré sme vykonali triedením. Všetok tovar sú priradené tie isté kategórie, len zároveň riadky nezmenili svoju počiatočnú pozíciu.

Lekcia: Majster funkcií v exteriéri

Program Excel je schopný výrazne zmierniť ABC analýzu pre používateľa. To sa dosahuje pomocou takéhoto nástroja ako triedenia. Potom sa vypočíta jednotlivec špecifická hmotnosť, kumulovaný podiel a vlastne, rozdelenie do skupín. V prípadoch, keď zmena v počiatočnej polohe riadkov v tabuľke nie je povolená, môžete použiť metódu pomocou komplexného vzorca.