Een van die sleutelmetodes van bestuur en logistiek is ABC-analise. Hiermee kan jy die bronne van die onderneming, produkte, kliënte, ens. Klassifiseer. in volgorde van belangrikheid. Terselfdertyd, volgens die vlak van belang, word elk van die bogenoemde eenhede toegeken aan een van drie kategorieë: A, B, of C. Excel het gereedskap in sy bagasie wat dit makliker maak om hierdie soort analise uit te voer. Kom ons kyk hoe om dit te gebruik, en wat is die ABC-analise.
Gebruik ABC analise
ABC-analise is 'n soort verbeterde en aangepas by die moderne toestande variant van die Pareto-beginsel. Volgens die metodologie van sy optrede word alle elemente van die analise in drie kategorieë in volgorde van belang verdeel:
- kategorie A - elemente wat meer gemeen het 80% spesifieke gewig;
- kategorie B - elemente waarvan die totale bestaan 5% tot en met 15% spesifieke gewig;
- kategorie C - die oorblywende elemente waarvan die totale hoeveelheid is 5% en minder spesifieke gewig.
Sommige maatskappye gebruik meer gevorderde tegnieke en verdeel die elemente nie in 3 nie, maar in 4 of 5 groepe, maar ons sal staatmaak op die klassieke skema van ABC-analise.
Metode 1: analise deur sortering
In Excel word ABC-analise uitgevoer met behulp van sorteer. Alle items word gesorteer van die grootste na die kleinste. Dan word die kumulatiewe spesifieke gewig van elke element bereken, op grond waarvan 'n bepaalde kategorie daaraan toegeken word. Kom ons gebruik 'n spesifieke voorbeeld om uit te vind hoe hierdie tegniek in die praktyk toegepas word.
Ons het 'n tafel met 'n lys van goedere wat die maatskappy verkoop, en die ooreenstemmende hoeveelheid inkomste uit hul verkoop vir 'n sekere tydperk. Aan die onderkant van die tabel word die totale inkomste opgesom vir alle goedere. Die taak is om ABC-analise te gebruik om hierdie produkte in groepe te verdeel volgens hul belang vir die maatskappy.
- Kies die tabel met die datapaneel, hou die linker muisknop, behalwe die kop en die laaste ry. Gaan na die oortjie "Data". Klik op die knoppie. "Sort"geleë in 'n blok gereedskap "Sorteer en filter" op die band.
Jy kan ook anders doen. Kies die bogenoemde omvang van die tabel en beweeg dan na die oortjie "Home" en klik op die knoppie "Sorteer en filter"geleë in 'n blok gereedskap "Editing" op die band. 'N Lys word geaktiveer waarin ons 'n posisie daarin kies. "Aangepaste Sorteer".
- By die toepassing van enige van die bogenoemde aksies word die venster vir sorteerinstellings van stapel gestuur. Ons kyk na die parameter "My data bevat opskrifte" merkmerk is ingestel. In geval van afwesigheid, installeer.
In die veld "Kolom" spesifiseer die naam van die kolom waarin die data op inkomste.
In die veld "Sort" jy moet spesifiseer deur watter spesifieke kriteria gesorteer sal word. Ons verlaat die vooraf ingestelde instellings - "Waardes".
In die veld "Order" stel die posisie "Dalende".
Nadat u hierdie instellings gemaak het, klik op die knoppie "OK" aan die onderkant van die venster.
- Nadat hierdie aksie uitgevoer is, is alle items gesorteer volgens inkomste van die hoogste na die laagste.
- Nou moet ons die verhouding van elk van die elemente vir die totaal bereken. Vir hierdie doeleindes skep ons 'n addisionele kolom wat ons sal bel "Spesifieke gewig". Plaas in die eerste sel van hierdie kolom 'n teken "="waarna ons die verwysing na die sel aandui waarin die som van die opbrengs uit die verkoop van die betrokke produk geleë is. Vervolgens stel die verdelingsteken ("/"). Daarna dui ons die koördinate van die sel aan, wat die totale hoeveelheid verkope van goedere in die onderneming bevat.
Oorweging van die feit dat ons die aangeduide formule na ander selle van die kolom sal kopieer "Spesifieke gewig" Met behulp van 'n vuller, die adres van die skakel na die element wat die totale hoeveelheid inkomste vir die onderneming bevat, moet ons regmaak. Om dit te doen, maak die skakel absoluut. Kies die koördinate van die gespesifiseerde sel in die formule en druk die sleutel F4. Soos ons kan sien, het 'n dollarteken voor die koördinate verskyn, wat aandui dat die skakel absolute geword het. Daar moet kennis geneem word dat die verwysing na die hoeveelheid inkomste van die eerste item in die lys (Item 3) moet relatief bly.
Dan, om berekeninge te maak, klik op die knoppie. Tik.
- Soos u kan sien, word die deel van die inkomste uit die eerste produk wat in die lys voorkom, in die teikensel vertoon. Om 'n afskrif van die formule in die reeks hieronder te maak, plaas die wyser in die onderste regterkantste hoek van die sel. Dit word omskep in 'n vul merker wat lyk soos 'n klein kruis. Klik op die linker muisknop en sleep die vulhandvatsel tot aan die einde van die kolom.
- Soos u kan sien, is die hele kolom gevul met data wat die deel van die inkomste uit die verkoop van elke produk beskryf. Maar die waarde van die spesifieke gewig word in 'n numeriese formaat vertoon, en ons moet dit omskep in 'n persentasie. Om dit te doen, kies die inhoud van die kolom "Spesifieke gewig". Skuif dan na die oortjie "Home". Op die lint in die instellingsgroep "Nommer" Daar is 'n veld waarin die dataformaat vertoon word. As u geen bykomende manipulasies gedoen het nie, moet die formaat daar ingestel word. "Generaal". Ons kliek op die ikoon in die vorm van 'n driehoek regs van hierdie veld. Kies die posisie in die lys met formate wat oopmaak "Rente".
- Soos u kan sien, is al die kolomwaardes omgeskakel na persentasies. Soos dit moet wees, in die lyn "Total" onbekend 100%. Die hoeveelheid goedere wat na verwagting in die kolom geleë sal wees, van groter na kleiner.
- Nou moet ons 'n kolom skep waarin die kumulatiewe deel met 'n kumulatiewe totaal vertoon sal word. Dit is in elke ry die spesifieke gewig van al die goedere wat in die lys hierbo voorkom, sal bygevoeg word aan die individuele spesifieke gewig van 'n spesifieke produk. Vir die eerste item in die lys (Item 3) Die individuele spesifieke gewig en die opgehoopte deel sal gelyk wees, maar vir al die daaropvolgende kinders moet die opgehoopte deel van die vorige item in die lys by die individuele aanwyser gevoeg word.
So, in die eerste ry, oordra ons na die kolom "Opgelope Aandele" kolom koers "Spesifieke gewig".
- Stel dan die wyser in die tweede kolom sel. "Opgelope Aandele". Hier moet ons die formule toepas. Ons sit 'n teken "Gelykes" en vou die inhoud van die sel "Spesifieke gewig" dieselfde ry en selinhoud "Opgelope Aandele" van die lyn hierbo. Alle skakels is relatief, dit wil sê, ons doen geen manipulasies met hulle nie. Klik daarna op die knoppie. Tik om die finale uitslag te vertoon.
- Nou moet jy hierdie formule na die selle van hierdie kolom, wat hieronder geleë is, kopieer. Om dit te doen, gebruik die vul merker, waaraan ons alreeds gewend het om die formule in die kolom te kopieer "Spesifieke gewig". Op dieselfde tyd, die tou "Total" vang is nie nodig nie omdat die opgehoopte gevolg in 100% sal op die laaste item uit die lys vertoon word. Soos u kan sien, is al die elemente van ons kolom daarna gevul.
- Daarna skep ons 'n kolom "Groep". Ons sal produkte in kategorieë moet groepeer A, B en C volgens die kumulatiewe aandeel wat aangedui word. Soos ons onthou, word alle elemente in groepe verdeel volgens die volgende skema:
- A - tot 80%;
- B - die volgende 15%;
- C - oorblywend 5%.
Dus, alle goedere, die kumulatiewe deel van die spesifieke gewig van wat die grens na 80%ken 'n kategorie toe A. Goedere met 'n opgehoopte spesifieke gewig van 80% tot en met 95% ken 'n kategorie toe B. Die oorblywende produkgroep met 'n waarde van meer 95% toegevalde spesifieke gewig toeken 'n kategorie C.
- Vir die helderheid kan jy hierdie groepe in verskillende kleure vul. Maar dit is opsioneel.
Dus, ons het die elemente in groepe afgebreek volgens die vlak van belang, met behulp van die ABC-analise. As u ander metodes gebruik, soos hierbo genoem, pas 'n partisie in meer groepe toe, maar die beginsel van partisie bly byna onveranderd.
les: Sorteer en filter in Excel
Metode 2: Gebruik 'n komplekse formule
Natuurlik is die gebruik van sorteer die mees algemene manier om ABC-analise in Excel te doen. In sommige gevalle is dit egter nodig om hierdie analise te doen sonder om die rye in die bronnetabel te herrangskik. In hierdie geval sal 'n komplekse formule tot die redding kom. Byvoorbeeld, ons sal dieselfde bronstabel gebruik as in die eerste geval.
- Voeg by die oorspronklike tabel met die naam van die goedere en die opbrengs uit die verkoop van elk van hulle, die kolom "Groep". Soos u kan sien, kan ons in hierdie geval nie kolomme byvoeg met die berekening van individuele en kumulatiewe aandele nie.
- Kies die eerste sel in die kolom. "Groep"Klik dan op die knoppie. "Voeg funksie in"naby die formulebalk geleë.
- Aktivering word uitgevoer Funksie Meesters. Skuif na kategorie "Skakels en skikkings". Kies 'n funksie "Keuse". Klik op die knoppie. "OK".
- Die funksie argument venster is geaktiveer. KEURING. Die sintaksis is soos volg:
= SELECT (Index_number; Value1; Value2; ...)Die doel van hierdie funksie is om een van die gespesifiseerde waardes uit te voer, afhangende van die indeksnommer. Die aantal waardes kan 254 bereik, maar ons benodig net drie name wat ooreenstem met die kategorieë van ABC-analise: A, B, C. Ons kan dadelik in die veld ingaan "VALUE1" die simbool "A"in die veld "VALUE2" - "B"in die veld "Value3" - "C".
- Maar met 'n argument "Indeks nommer" Dit sal nodig wees om deeglik te tink, met die bou van 'n paar addisionele operateurs. Stel die wyser in die veld "Indeks nommer". Klik dan op die ikoon met die vorm van 'n driehoek aan die linkerkant van die knoppie "Voeg funksie in". 'N Lys van onlangs gebruikte operateurs word geopen. Ons benodig 'n funksie MATCH. Aangesien dit nie op die lys is nie, klik op die onderskrif "Ander kenmerke ...".
- Loop weer die venster. Funksie Meesters. Weer gaan na die kategorie "Skakels en skikkings". Ons vind daar 'n posisie "MATCH"kies dit en klik op die knoppie "OK".
- Operator argument venster oopmaak MATCH. Die sintaksis is soos volg:
= MATCH (Gevraagde waarde; gekyk skikking; match_type)Die doel van hierdie funksie is om die posnommer van die gespesifiseerde element te bepaal. Dit is net wat ons nodig het vir die veld "Indeks nommer" funksies KEURING.
In die veld "Gewysde skikking" U kan die volgende uitdrukking onmiddellik stel:
{0:0,8:0,95}Dit moet presies in krulhakies wees, soos 'n skikkingformule. Dit is nie moeilik om te raai dat hierdie getalle (0; 0,8; 0,95) dui die grense van die opgehoopte aandeel tussen groepe aan.
veld "Mapping Type" Nie verpligtend nie en in hierdie geval sal ons dit nie vul nie.
In die veld "Gewaardeerde waarde" stel die wyser in. Dan beweeg ons weer deur die bogenoemde ikoon in die vorm van 'n driehoek Funksie Wizard.
- Hierdie keer in Funksiewizard skuif na kategorie "Wiskundige". Kies 'n naam "SUMIF" en klik op die knoppie "OK".
- Die funksie argument venster begin. SUMIF. Die gespesifiseerde operateur sommeer die selle wat aan die gespesifiseerde toestand voldoen. Die sintaksis is:
= SOMMES (reeks; kriterium; reeks_summing)In die veld "Range" Tik die adres van die kolom in "Inkomste". Vir hierdie doeleindes stel ons die wyser in die veld in, en dan, nadat u die linker muisknop geklem het, kies al die selle van die ooreenstemmende kolom, uitgesluit die waarde "Total". Soos u kan sien, word die adres onmiddellik in die veld vertoon. Daarbenewens moet ons hierdie skakel absoluut maak. Om dit te doen, maak die keuse en druk die sleutel F4. Die adres is gemerk met dollarstekens.
In die veld "Criterion" ons moet 'n voorwaarde stel. Tik die volgende uitdrukking in:
">"&Dan onmiddellik daarna gaan ons die adres van die eerste sel van die kolom in. "Inkomste". Ons maak die horisontale koördinate in hierdie adres absoluut, en voeg 'n dollar teken van die sleutelbord voor die brief by. Die vertikale koördinate is relatief, dit wil sê, daar moet geen teken voor die nommer wees nie.
Druk dan nie op die knoppie nie "OK", en klik op die funksie naam MATCH in die formulebalk.
- Dan gaan ons terug na die funksie argument venster. MATCH. Soos u kan sien, in die veld "Gewaardeerde waarde" data verskyn deur die operateur gegee SUMIF. Maar dis nie alles nie. Gaan na hierdie veld en voeg die teken by die bestaande data. "+" sonder aanhalingstekens. Dan betree ons die adres van die eerste sel van die kolom. "Inkomste". En weer maak ons die horisontale koördinate van hierdie skakel absoluut, en vertikaal vertrek ons relatief.
Volg dan die hele inhoud van die veld "Gewaardeerde waarde" tussen hakies, plaas dan die verdelingsteken ("/"). Daarna gaan jy weer deur die driehoekikoon na die funksie keuringsvenster.
- Soos verlede keer in hardloop Funksiewizard op soek na die gewenste operateur in die kategorie "Wiskundige". Hierdie keer word die gewenste funksie genoem "Som". Kies dit en klik op die knoppie. "OK".
- Operator argument venster oopmaak som. Die hoofdoel is die opsomming van data in selle. Die sintaksis van hierdie stelling is redelik eenvoudig:
= SUM (Nommer1; Nommer2; ...)Vir ons doeleindes benodig ons net 'n veld. "Nommer 1". Voer die koördinate van die kolomreeks in "Inkomste", uitgesluit die sel wat die totale bevat. Ons het reeds 'n soortgelyke operasie in die veld uitgevoer. "Range" funksies SUMIF. Soos in daardie tyd maak ons absolute koördinate van die omvang deur hulle te kies en die sleutel te druk F4.
Klik daarna op die sleutel "OK" aan die onderkant van die venster.
- Soos u kan sien, het die kompleks van die ingevoerde funksies 'n berekening gemaak en die resultaat in die eerste sel van die kolom gegee "Groep". Die eerste item is 'n groep toegeken. "A". Die volledige formule wat ons vir hierdie berekening gebruik het, is soos volg:
= SELECT (SAT ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C");Maar, natuurlik, in elk geval sal die koördinate in hierdie formule anders wees. Daarom kan dit nie as universeel beskou word nie. Maar met die handleiding wat hierbo gegee is, kan u die koördinate van enige tabel invoeg en hierdie metode suksesvol toepas in enige situasie.
- Dit is egter nie alles nie. Ons het slegs bereken vir die eerste ry van die tafel. Om die data kolom volledig te vul "Groep", moet u hierdie formule na die onderstaande reeks kopieer (met uitsondering van die ry sel "Total") met behulp van die vul merker, soos ons meer as een keer gedoen het. Nadat die data ingevoer is, kan die ABC-analise as volledig beskou word.
Soos u kan sien, verskil die resultate wat gebruik word deur die gebruik van 'n komplekse formule met die variant, glad nie te verskil van die resultate wat ons deur sortering uitgevoer het nie. Alle produkte word aan dieselfde kategorieë toegewys, maar die lyne het nie hul aanvanklike posisie verander nie.
les: Excel funksie wizard
Excel kan ABC analise vir 'n gebruiker baie fasiliteer. Dit word behaal met behulp van 'n instrument soos sorteer. Daarna word die individuele spesifieke gewig, die opgehoopte deel en, in werklikheid, die verdeling in groepe bereken. In gevalle waar die verandering in die aanvanklike posisie van die rye in die tabel nie toegelaat word nie, kan u die metode gebruik deur 'n komplekse formule te gebruik.
