ABC analyse i Excel

En av de viktigste metodene for ledelse og logistikk er ABC-analyse. Med det kan du klassifisere ressursene til bedriften, varene, kundene, etc. I henhold til graden av betydning. Samtidig, av det viktige viktigheten, er hver av de ovennevnte enheten tildelt en av tre kategorier: A, B eller C. Excel-programmet har i bagasjerommet som gjør det lettere å utføre denne typen analyse. La oss finne ut hvordan du bruker dem, og hva er ABC-analysen.

Bruker ABC-analyse

ABC-analyse er en slags forbedret og tilpasset moderne forhold for Pareto-prinsippet. I henhold til fremgangsmåten i sin oppførsel er alle elementene i analysen delt inn i tre kategorier i henhold til graden av betydning:

• Kategori A-elementer som har en kombinasjon av mer enn 80% av spesifikk tyngdekraften;
• Kategori B - Elementer hvis kombinasjon varierer fra 5% til 15% av konkret tyngdekraften;
• Kategori C - de resterende elementene, det totale aggregatet er 5% og mindre spesifikk tyngdekraften.

Separate selskaper bruker mer avanserte teknikker og deler elementer ikke til 3, men med 4 eller 5 grupper, men vi vil stole på den klassiske ABC-analysesystemet.

Metode 1: Analyse med sortering

Excel ABC-analyse utføres ved hjelp av sortering. Alle elementene er sortert fra fettete til mindre. Deretter beregnes akkumulativ andel av hvert element, på grunnlag av hvilken den tildeles en bestemt kategori. La oss finne ut hvordan den angitte metoden brukes i praksis.

Vi har et bord med en liste over produkter som selskapet selger, og tilsvarende antall inntekter fra salg i en viss tidsperiode. På bunnen av bordet, utfallet av inntektene generelt på alle varens navn. Det er en oppgave ved hjelp av ABC-analyse, bryter disse varene i grupper av deres betydning for bedriften.

1. Vi markerer et bord med markøren ved å lukke venstre museknapp, unntatt hetten og den endelige strengen. Gå til "Data" -fanen. Vi klikker på "Sorter" -knappen, som ligger i verktøylinjen "Sorter og filter" på båndet.

   

   Du kan også gjøre annerledes. Vi tildeler tabellområdet ovenfor, og flytt deretter til "Hjem" -fanen og klikk på "Sorter og filter" -knappen som ligger i redigeringsverktøyet på båndet. Listen er aktivert der posisjonen "tilpassbar sortering" er aktivert.



2. Når du bruker noen av de ovennevnte handlingene, starter vinduet Sorteringsinnstillinger. Vi ser slik at "Mine data inneholder overskriftene" -parameteren ble satt til et merke. I tilfelle av fraværet, installer.

   I feltet "kolonne" angir du navnet på kolonnen der inntektsdataene er inneholdt.

   I feltet "Sorter" må du spesifisere, hvor spesifikke kriterier vil bli sortert. La de forhåndsinnstilte innstillingene - "verdier".

   I "Order" -feltet, utviser posisjonen "nedadgående".

   Etter produktet av de angitte innstillingene, klikker du på "OK" -knappen nederst i vinduet.



3. Etter å ha utført den angitte handlingen, ble alle elementer sortert etter inntekter fra mer til en mindre.



4. Nå skal vi beregne andelen av hver av elementene for et generelt resultat. Lag en ekstra kolonne for disse formålene, som vi kaller "dele vekt". I den første cellen i denne kolonnen legger vi skiltet "=", hvorpå du angir en lenke til cellen der mengden av inntekter fra implementeringen av det aktuelle produktet er. Deretter setter du tegnet på divisjonen ("/"). Deretter angir vi koordinatene til cellen, som inneholder den totale mengden av salg av varer gjennom hele bedriften.

   Gitt det faktum at den angitte formelen vi vil kopiere til andre celler i "Del" -kolonnen ved å fylle markøren, adressen til lenken til elementet som inneholder den endelige mengden av inntekter til bedriften, må vi fikse. For å gjøre dette, gjør en link absolut. Velg koordinatene til den angitte cellen i formelen, og trykk på F4-tasten. Før koordinatene, som vi ser, oppstod et dollarskilt, noe som indikerer at lenken er blitt absolutt. Det skal bemerkes at referansen til mengden av inntekter av den første i listen over varer (produkt 3) bør forbli relativ.

   Deretter, for å gjøre beregninger, trykk på Enter-knappen.



5. Som vi ser, oppsto andelen av inntekter fra det første produktet som er angitt i listen i målcellen. For å kopiere formelen i området nedenfor, legger vi markøren til nedre høyre hjørne av cellen. Dens transformasjon i fyllingsmarkøren, med en titt på et lite kryss. Vi klikker på venstre museknapp og dra fyllmarkøren ned til slutten av kolonnen.



6. Som du kan se, er hele kolonnen fylt med data som karakteriserer andelen av inntekter fra implementeringen av hvert produkt. Men verdien av den spesifikke tyngdekraften vises i det numeriske formatet, og vi må forvandle det til prosent. For å gjøre dette, marker innholdet i kolonnen "spesifikk vekt". Deretter flytter vi til "Hjem" -fanen. På båndet i gruppeinnstillingsgruppen er det et felt som viser dataformat. Som standard, hvis du ikke produserte flere manipulasjoner, må "General" -formatet installeres der. Klikk på ikonet i form av en trekant som ligger til høyre for dette feltet. I listen over formater velger du "prosent" -posisjonen.



7. Som vi ser, ble alle kolonnverdiene forvandlet til prosentvis verdier. Som det skal være 100% angitt i strengen av "Total". Andelen varer forventes i kolonnen fra en større til en mindre.



8. Nå skal vi lage en kolonne der den akkumulerte andelen med et voksende utfall vil bli vist. Det vil si, i hver linje til den enkelte spesifikke vekten av et bestemt produkt vil bli lagt til andelen av alle de produktene som er plassert i listen ovenfor. For de første varene i listen (produkt 3), vil individuell andel og akkumulert andel være lik, men alle påfølgende til den enkelte indikatoren må legge til den akkumulerte andelen av det forrige elementet i listen.

   Så, i den første linjen overføres vi til kolonnen "Akkumulert del" indikatoren for kolonnen "spesifikk".



9. Deretter setter du markøren til den andre cellen i kolonnen "Akkumulert del". Her må vi søke formelen. Vi legger skiltet "like" og bretter innholdet i cellen "Andelen" av samme linje og innholdet i cellen "Akkumulert del" fra strengen ovenfor. Alle referanser er reservert relativt, det vil si at vi ikke produserer noen manipulasjoner med dem. Etter det, utfør et klikk på Enter-knappen for å vise sluttresultatet.



10. Nå må du kopiere denne formelen i cellene i denne kolonnen, som er plassert under. For å gjøre dette, bruker vi påfyllingsmarkøren som vi allerede har tatt med på når vi kopierer formelen i aksjekolonnen. Samtidig er strengen "Total" ikke nødvendig, siden det akkumulerte resultatet er 100% vil bli vist på det siste produktet fra listen. Som du kan se, var alle elementene i vår kolonne etter det fylt.



11. Etter det, opprett en kolonne "gruppe". Vi må gruppere varer etter kategori A, B og C i henhold til den angitte akkumulerte andelen. Når vi husker, fordeles alle elementer av grupper i henhold til følgende ordning:
    • A - opptil 80%;
    • B - følgende 15%;
    • C - de resterende 5%.

    Således, alle varer som akkumulerte andel av den spesifikke vekten, som er i grensen på opptil 80%, tilordner vi kategori A. Varene med akkumulert spesifikk vekt fra 80% til 95% er tildelt kategorien B. de resterende Gruppe av varer med verdien av mer enn 95% av den akkumulerte spesifikke tyngdekraften vi tilordner en kategori C.



12. For klarhet kan du fylle de angitte gruppene med forskjellige farger. Men det er på vilje.

Dermed brøt vi elementene på nivået av betydning, ved hjelp av ABC-analyse. Ved bruk av andre teknikker, som nevnt ovenfor, brukes det til å bryte inn i et større antall grupper, men prinsippet om å bryte i dette tilfellet forblir nesten uendret.

Lekse: Sortering og filtrering i Excel

Metode 2: Bruke en kompleks formel

Selvfølgelig er bruken av sortering den vanligste måten å utføre ABC-analyse i Excel. Men i noen tilfeller er det nødvendig å utføre denne analysen uten omarrangeringslinjer på steder i kildetabellen. I dette tilfellet vil en kompleks formel komme til redning. For eksempel vil vi bruke samme kildebord som i det første tilfellet.

1. Vi legger til kildetabellen som inneholder navnet på varene og inntektene fra salget av hver av dem, kolonnen "Gruppe". Som du kan se, kan vi i dette tilfellet ikke legge til kolonner med beregning av individuelle og akkumulative fraksjoner.



2. Vi produserer den første cellemeldingen i kolonnen Konsern, hvorpå du utfører et klikk på "Sett inn funksjon" -knappen i nærheten av Formula-raden.



3. Masters aktivering av funksjoner. Vi flytter til kategorien "linker og arrays". Velg funksjonen "Choice". Vi klikker på "OK" -knappen.



4. Spillargumentvinduet er aktivert. Syntaxen presenteres som følger:

   = Valg (nummer_intex; verdi1; verdi2; ...)

   Oppgaven med denne funksjonen er tilbaketrekking av en av de angitte verdiene, avhengig av indeksnummeret. Antall verdier kan nå 254, men vi trenger bare tre navn som samsvarer med ABC-analysekategorier: A, B, C. Vi kan umiddelbart gå inn i "A" -symbolet i "B" -feltet i "B "Felt, felt" verdi3 "-" C ".



5. Men med argumentet "indeksnummer" må bli grundig tinket ved å bygge flere ekstra operatører. Installer markøren i feltet "Indeksnummer". Deretter klikker du på ikonet med en visning av trekanten, til venstre for "Sett inn funksjon" -knappen. En liste over nyanserte operatører åpnes. Vi trenger en funksjon av søket. Siden det ikke er i listen, klikker vi på påskriften "Andre funksjoner ...".



6. Vinduet på vinduet Funksjoner Wizard er startet igjen. Igjen, gå til kategorien "linker og arrays". Vi finner posisjonen til "Search Board", allokere det og ta et klikk på "OK" -knappen.



7. Argumentene i søkeroperatørens argumenter åpnes. Syntaxen til den har følgende skjema:

   = Søkstavle (search_name; visning__Massive; Type_station)

   Formålet med denne funksjonen er å definere posisjonsnummeret til det angitte elementet. Det er bare det vi trenger for feltet "Index Number" har et valg.

   I feltet "Listing Array" kan du umiddelbart spørre følgende uttrykk:

   {0: 0.8: 0,95}

   Det skal være nettopp i krøllete parenteser, som en arrayformel. Det er ikke vanskelig å gjette at disse tallene (0; 0,8; 0,95) betegner grensene til den akkumulerte andelen mellom grupper.

   "Type sammenligning" -feltet er ikke obligatorisk, og i dette tilfellet vil vi ikke fylle den.

   I feltet "Andre verdi", sett markøren. Neste igjen gjennom ikonet beskrevet ovenfor i form av en trekant, flytter vi til veiviseren av funksjonene.



8. Denne gangen i funksjonsveiviseren gjør vi flytting til kategorien "Matematisk". Velg navnet "Silent" og trykk på "OK" -knappen.



9. Vinduet Funksjonsargumenter vil bli lansert. Den angitte operatøren oppsummerer cellene som møter den bestemte tilstanden. Syntaxen er:

   = Silent (rekkevidde; kriterium; Range_suming)

   I feltet "Range", skriv inn adressen til kolonnen "Revenue". For disse formålene stiller vi markøren i feltet, og deretter ved å lage klippet til venstre museknapp, velg alle cellene i den tilsvarende kolonnen, unntatt verdien "Total". Som du kan se, vises adressen umiddelbart i feltet. I tillegg må vi gjøre denne linken absolutt. For å gjøre dette, produserer du tildelingen og klikk på F4-tasten. Adressen ble utgitt av dollarskilt.

   I "kriteriet" feltet må vi sette en tilstand. Skriv inn følgende uttrykk:

   ">"&

   Så umiddelbart etter det går vi inn i adressen til den første cellen i kolonnen "Revenue". Vi lager koordinater horisontalt i denne adressen Absolutt, og legger til dollarskiltet fra tastaturet foran brevet. Vertikale koordinater gir relativt, det vil si det ikke skal være tegn foran sifferet.

   Etter det trykker vi ikke på "OK" -knappen, og klikker på navnet på søkefunksjonen i formelstrengen.



10. Deretter går vi tilbake til Arguments-vinduet i søket. Som vi ser, i "foggulære betydningen" -feltet, var datasettet av operatøren stille. Men det er ikke alt. Gå til dette feltet og legger allerede til tilgjengelige data for å legge til et "+" tegn uten anførselstegn. Deretter presenterer vi adressen til den første cellen i kolonnen "Revenue". Og igjen gjør vi koordinater horisontalt med absolutt, og vertikalt forlater relativt.

    Deretter tar vi alt innholdet i feltet "ønsket verdi" i parentesene, hvoretter vi legger tegn på divisjonen ("/"). Etter det, igjen gjennom Triangle-ikonet, gå til funksjonen for å velge funksjoner.



11. Som forrige gang i Running Funksjonsveiviseren, leter vi etter den nødvendige operatøren i kategorien "Matematisk". Denne gangen kalles den ønskede funksjonen "SUMS". Vi fremhever det og trykker på "OK" -knappen.



12. Operatørargument-vinduet åpnes. Hovedformålet er summasjonen av data i cellene. Syntaxen til denne operatøren er ganske enkel:

    = Sums (nummer1; nummer2; ...)

    For våre formål vil bare "nummer1" -feltet være nødvendig. Vi introduserer i det koordinatene til kolonnen "Inntekt", og eliminerer cellen som inneholder resultatene. Vi har allerede utført en slik operasjon i feltet "Range" -feltet. Som på den tiden gjør området Koordinatene absolutt, og fremhever dem og trykker på F4-tasten.

    Deretter klikker du på "OK" -tasten nederst i vinduet.



13. Som du kan se, gjorde komplekset av de angitte funksjonene en beregning og utstedt resultatet i den første cellen i kolonnen "-gruppen". Det første produktet ble tildelt en gruppe "A". Den fulle formelen som brukes av oss for denne beregningen er som følger:

    = Valg (søk bord ((stille ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / Beløp ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "A", "B"; "C")

    Men selvfølgelig, i hvert tilfelle, vil koordinatene i denne formelen være annerledes. Derfor kan det ikke betraktes som universelt. Men ved hjelp av den håndboken som ble gitt ovenfor, kan du sette inn koordinatene til et hvilket som helst tabell og vellykket bruke denne metoden i enhver situasjon.



14. Dette er imidlertid ikke alt. Vi beregnet bare for den første raden i tabellen. For å fylle ut kolonnen Gruppe "Gruppe", må du kopiere denne formelen i området nedenfor (eliminere cellen i strengen "Total") ved hjelp av fyllingsmarkøren, som vi allerede har gjort mer enn en gang. Etter at dataene er gjort, kan ABC-analyse betraktes som utføres.

Som vi ser, er resultatene som er oppnådd ved hjelp av alternativet ved hjelp av en kompleks formel, ikke annerledes enn de resultatene vi ble utført ved å sortere. Alle varene er tildelt samme kategorier, bare samtidig raderne endret ikke sin opprinnelige posisjon.

Lekse: Master of Funksjoner i Excele

Excel-programmet kan betydelig lindre ABC-analyse betydelig for brukeren. Dette oppnås ved å bruke et slikt verktøy som sortering. Etter det beregnes den individuelle spesifikke tyngdekraften, akkumulert andel, og faktisk er partisjonering i grupper beregnet. I tilfeller der endringen i den opprinnelige posisjonen til radene i tabellen ikke er tillatt, kan du bruke metoden ved hjelp av en kompleks formel.