Een van de belangrijkste managementmethoden en logistiek is ABC-analyse. Hiermee kunt u de middelen van de onderneming, goederen, klanten, enz. Classificeren, classificeren Volgens de mate van belangrijkheid. Tegelijkertijd heeft elk van de bovenstaande eenheid elk van de bovenstaande eenheid een van de drie categorieën toegewezen: A, B of C. Excel-programma heeft in zijn bagagetools die het gemakkelijker maken om dit soort analyse uit te voeren. Laten we erachter komen hoe ze kunnen gebruiken, en wat is de ABC-analyse.
ABC-analyse gebruiken
ABC-analyse is een soort van verbeterd en aangepast aan moderne omstandigheden voor het Pareto-principe. Volgens de werkwijze van zijn gedrag zijn alle elementen van de analyse onderverdeeld in drie categorieën volgens de mate van belangrijkheid:- Categorie A-elementen met een combinatie van meer dan 80% van de specifieke zwaartekracht;
- Categorie B - elementen waarvan de combinatie varieert van 5% tot 15% van de specifieke zwaartekracht;
- Categorie C - de resterende elementen, waarvan het totale aggregaat 5% en minder specifieke zwaartekracht is.
Afzonderlijke bedrijven gebruiken meer geavanceerde technieken en verdelen items niet tot 3, maar met 4 of 5 groepen, maar we zullen vertrouwen op de Classic ABC-analyse-regeling.
Methode 1: Analyse met sorteren
Excel ABC-analyse wordt uitgevoerd met behulp van sorteren. Alle items zijn gesorteerd van vettig tot minder. Vervolgens wordt het accumulatieve aandeel van elk element berekend, op basis waarvan het een bepaalde categorie heeft toegewezen. Laten we ontdekken hoe de opgegeven methodologie in de praktijk wordt gebruikt.
We hebben een tafel met een lijst met producten die het bedrijf verkoopt, en het overeenkomstige aantal inkomsten uit hun verkoop voor een bepaalde periode. Aan de onderkant van de tabel, de uitkomst van de inkomsten in het algemeen over alle namen van de goederen. Er is een taak met ABC-analyse, breek deze goederen in groepen door hun belang voor de onderneming.
- We markeren een tabel met de cursor door de linkermuisknop te sluiten, met uitzondering van de dop en de laatste reeks. Ga naar het tabblad "Data". We klikken op de knop "Sorteren", in de werkbalk "Sorteren en filter" op de band.
Je kunt ook anders doen. We weken het bovenstaande tabelbereik toe en ga vervolgens naar het tabblad "Home" en klik op de knop "Sorteren en filteren" in de toolbox bewerken op de band. De lijst wordt geactiveerd waarin de positie "aanpasbare sortering" is geactiveerd.
- Bij het toepassen van een van de bovenstaande acties, lanceert het venster Sorteerinstellingen. We zien erop dat de parameter "Mijn gegevens de kopteksten" is ingesteld op een vinkje. In het geval van afwezigheid, installeren.
Geef in het veld "Column" de naam van de kolom op waarin de inkomstengegevens zijn ingesloten.
In het veld "Sorteren" moet u specificeren, waarop specifieke criteria worden gesorteerd. Laat de vooraf ingestelde instellingen achter - "Waarden".
In het veld "Order", vertent u de positie "aflopend".
Nadat het product van de opgegeven instellingen op de knop "OK" aan de onderkant van het venster klikt.
- Na het uitvoeren van de opgegeven actie werden alle items gesorteerd op inkomsten uit meer tot een kleiner.
- Nu moeten we het aandeel van elk van de elementen voor een algemeen resultaat berekenen. Maak een extra kolom voor deze doeleinden, die we "Gewicht" noemen. In de eerste cel van deze kolom plaatsen we het bord "=", waarna u een koppeling op de cel opgeeft waarin het bedrag aan inkomsten uit de uitvoering van het relevante product is. Stel vervolgens het teken van de divisie in ("/"). Daarna vermelden we de coördinaten van de cel, die het totale bedrag van de verkoop van goederen in de hele onderneming bevat.
Gezien het feit dat de opgegeven formule we zullen kopiëren naar andere cellen van de kolom "Deel" door de vulmarkering, het adres van de link naar het element dat het definitieve bedrag aan de inkomsten aan de onderneming bevat, moeten we het oplossen. Om dit te doen, maak een link absoluut. Selecteer de coördinaten van de opgegeven cel in de formule en druk op de F4-toets. Vóór de coördinaten, zoals we zien, verscheen een dollarteken, wat aangeeft dat de link absoluut is geworden. Opgemerkt moet worden dat de verwijzing naar het bedrag van de omzet van de eerste in de lijst met goederen (product 3) relatief moet blijven.
Om berekeningen te maken, drukt u op de knop ENTER.
- Zoals we zien, verscheen het aandeel van de inkomsten uit het eerste product in de lijst in de doelcel. Om de formule in het onderstaande bereik te kopiëren, plaatsen we de cursor in de rechterbenedenhoek van de cel. Zijn transformatie in de vulmarkering, met een blik van een klein kruis. We klikken op de linkermuisknop en sleep de vulmarkering naar beneden naar het einde van de kolom.
- Zoals u kunt zien, is de volledige kolom gevuld met gegevens die het aandeel van de inkomsten uit de implementatie van elk product kenmerken. Maar de waarde van de specifieke zwaartekracht wordt weergegeven in het numerieke formaat, en we moeten het omzetten in percentage. Om dit te doen, markeert u de inhoud van het kolom "Specifiek Gewicht". Dan gaan we naar het tabblad "Home". Op de tape in de groepsinstellingengroep is er een veld dat gegevensformaat weergeeft. Standaard, als u geen extra manipulaties hebt geproduceerd, moet het "algemene" formaat daar worden geïnstalleerd. Klik op het pictogram in de vorm van een driehoek aan de rechterkant van dit veld. Selecteer in de lijst met indelingen de positie "Percentage".
- Zoals we zien, werden alle kolomwaarden getransformeerd in percentagewaarden. Zoals het zou moeten zijn, vermeld 100% in de reeks "totaal". Het aandeel van de goederen wordt verwacht in de kolom van een groter tot een kleiner.
- Nu moeten we een kolom maken waarin het geaccumuleerde aandeel met een groeiende uitkomst zou worden weergegeven. Dat wil zeggen, in elke regel tot het individuele specifieke gewicht van een specifiek product zal het aandeel van al die producten worden toegevoegd die zich in de bovenstaande lijst bevinden. Voor de eerste goederen in de lijst (product 3), zullen individuele verhouding en geaccumuleerd aandeel gelijk zijn, maar alle volgende aan de individuele indicator moet het opgebouwde aandeel van het vorige element van de lijst toevoegen.
Dus in de eerste regel worden we overgebracht naar de kolom "Accumulated Share" de indicator van de kolom "Specifiek".
- Stel vervolgens de cursor in op de tweede cel van de kolom "Geaccumuleerde Share". Hier moeten we de formule toepassen. We plaatsen het teken "EQUAL" en vouwen de inhoud van de cel "het aandeel" van dezelfde lijn en de inhoud van de cel "geaccumuleerd aandeel" uit de bovenstaande tekenreeks. Alle referenties zijn gereserveerd met familielid, dat wil zeggen, we produceren geen manipulaties met hen. Voer daarna een klik op de knop ENTER om het eindresultaat weer te geven.
- Nu moet u deze formule in de cellen van deze kolom kopiëren, die hieronder worden geplaatst. Om dit te doen, gebruiken we de vulmarkering waaraan we al zijn omgekomen bij het kopiëren van de formule in de kolom Deel. Tegelijkertijd is het string "totaal" niet nodig, omdat het geaccumuleerde resultaat 100% is, wordt weergegeven op het laatste product uit de lijst. Zoals je kunt zien, waren alle elementen van onze kolom daarna gevuld.
- Maak daarna een kolom "groep". We zullen goederen moeten groeperen op categorie A, B en C volgens het opgegeven geaccumuleerde aandeel. Zoals we ons herinneren, worden alle elementen verdeeld door groepen volgens het volgende schema:
- A - tot 80%;
- B - de volgende 15%;
- C - de resterende 5%.
Aldus zijn alle goederen die het opeenvolgende aandeel van het specifieke gewicht in de grens van maximaal 80% vallen, we toewijzen Categorie A. De goederen met het geaccumuleerde specifieke gewicht van 80% tot 95% worden toegewezen aan de categorie B. De resterende Groep goederen met de waarde van meer dan 95% van de geaccumuleerde specifieke zwaartekracht wijzen we een categorie C toewijzen.
- Voor de duidelijkheid kunt u de opgegeven groepen met verschillende kleuren invullen. Maar het is in wil.
We braken dus de elementen op het niveau van belang, met behulp van ABC-analyse. Bij gebruik van een aantal andere technieken, zoals hierboven vermeld, wordt het gebruikt om in een groter aantal groepen te breken, maar het principe van het breken in deze zaak blijft bijna ongewijzigd.
Les: Sorteren en filteren in Excel
Methode 2: een complexe formule gebruiken
Natuurlijk is het gebruik van sortering de meest gebruikelijke manier om ABC-analyse in Excelse uit te voeren. Maar in sommige gevallen is het noodzakelijk om deze analyse uit te voeren zonder de lijnen op plaatsen in de bronstabel te herschikken. In dit geval zal een complexe formule bij de redding komen. We zullen bijvoorbeeld dezelfde brontabel gebruiken zoals in het eerste geval.
- We voegen toe aan de brontabel met de naam van de goederen en de inkomsten uit de verkoop van elk van hen, de kolom "Groep". Zoals u kunt zien, mogen we in dit geval geen kolommen toevoegen met de berekening van individuele en accumulatieve fracties.
- We produceren de eerste celtoewijzing in de kolom Groep, waarna u een klik uitvoert op de knop "Invoegenfunctie" in de buurt van de Formule Row.
- Masters Activering van functies. We gaan naar de categorie "Links and Arrays". Selecteer de functie "Choice". We maken een klik op de knop "OK".
- Het game-argumentvenster is geactiveerd. De syntaxis wordt als volgt gepresenteerd:
= Selectie (nummer_intex; waarde1; waarde2; ...)
De taak van deze functie is de intrekking van een van de opgegeven waarden, afhankelijk van het indexnummer. Het aantal waarden kan 254 bereiken, maar we hebben slechts drie namen nodig die overeenkomen met ABC-analysecategorieën: A, B, C. We kunnen onmiddellijk het "A" -symbool in het veld "B" in het veld "B". "Veld, veld" VALUE3 "-" C ".
- Maar met het argument "indexnummer" moet grondig worden ondernomen door verschillende extra operators te bouwen. Installeer de cursor in het veld "Indexnummer". Klik vervolgens op het pictogram met een weergave van de driehoek links van de knop "Invoegenfunctie". Een lijst met nieuw gebruikte operators wordt geopend. We hebben een functie van het zoeken nodig. Omdat het niet in de lijst staat, klikken we op de inscriptie "Andere functies ...".
- Het venster van het Wizard-venster Functies wordt opnieuw gestart. Nogmaals, ga naar de categorie "Links and Arrays". We vinden de positie van het "Search Board", toewijzen en een klik op de knop "OK".
- De argumenten van de argumenten van de zoekoperator worden geopend. De syntaxis van het heeft de volgende vorm:
= Zoekbord (search_name; weergave__massief; Type_station)
Het doel van deze functie is om het positienummer van het opgegeven item te definiëren. Dat wil zeggen, precies wat we nodig hebben voor het veld "Index Number" hebben een keuze.
In het veld "Listing Array" kunt u onmiddellijk de volgende uitdrukking vragen:
{0: 0.8: 0,95}
Het moet precies in krullende haakjes zijn, als een arrayformule. Het is niet moeilijk om te raden dat deze getallen (0; 0.8; 0.95) de grenzen van het geaccumuleerde aandeel tussen groepen aangeven.
Het veld "Type vergelijking" is niet verplicht en in dit geval zullen we het niet invullen.
Zet in het veld "Tweede waarde" de cursor in. Vervolgens weer door het hierboven beschreven pictogram in de vorm van een driehoek gaan we naar de tovenaar van de functies.
- Deze keer in de Functies Wizard maken we verhuizen naar de categorie "Mathematical". Kies de naam "Silent" en druk op de knop "OK".
- Het venster Functie-argumenten wordt gelanceerd. De opgegeven operator vat de cellen samen die aan de duidelijke toestand voldoen. De syntaxis is:
= Stil (bereik; criterium; range_suming)
Voer in het veld "Range" het adres in van de kolom "Revenue". Voor deze doeleinden plaatsen we de cursor in het veld en vervolgens door de klem van de linkermuisknop te maken, selecteert u alle cellen van de overeenkomstige kolom, exclusief de waarde "Totaal". Zoals u kunt zien, verscheen het adres onmiddellijk in het veld. Daarnaast moeten we deze link absoluut maken. Om dit te doen, produceert u de toewijzing en klikt u op de F4-toets. Het adres werd vrijgegeven door dollartekens.
In het veld "Criterium" moeten we een voorwaarde instellen. Voer de volgende uitdrukking in:
">"&
Dan onmiddellijk nadat we het adres van de eerste cel van de kolom "inkomsten" invoeren. We maken coördinaten horizontaal in dit adres absoluut, het toevoegen van het dollarteken van het toetsenbord voor de brief. Verticale coördinaten verlof relatief, dat wil zeggen, er zou geen teken voor het cijfer moeten zijn.
Daarna drukken we niet op de knop "OK" en klikt u op de naam van de zoekfunctie in de Formule-string.
- Dan keren we terug naar het argumentenvenster van het zoekbord. Zoals we zien, waren de door de operator ingestelde gegevens in het veld "Fogular-betekenis" stil. Maar dat is niet alles. Ga naar dit veld en voegt al bij aan de beschikbare gegevens om een "+" -teken zonder aanhalingstekens toe te voegen. Dan introduceren we het adres van de eerste cel van de kolom "Omzet". En opnieuw maken we horizontaal coördinaten met absoluut, en verticaal verlaten relatief.
Vervolgens nemen we alle inhoud van het veld "de gewenste waarde" in de haakjes, waarna we het teken van de divisie ("/") plaatsen. Hierna gaat u opnieuw via het driehoekspictogram naar de functie van het selecteren van functies.
- Net als de laatste keer in de Wizard Running Function, zijn we op zoek naar de nodige operator in de categorie "Mathematical". Deze keer wordt de gewenste functie "sommen" genoemd. We markeren het en druk op de knop "OK".
- Operator-argumentenvenster wordt geopend. Het belangrijkste doel is de sommatie van gegevens in de cellen. De syntaxis van deze operator is vrij eenvoudig:
= Sommen (nummer1; nummer2; ...)
Voor onze doeleinden is alleen het veld "Number1" nodig. We introduceren de coördinaten van de kolom "Revenue", die de cel die de resultaten bevat, elimineren. We hebben al een dergelijke bewerking uitgevoerd in het veld "Range" van de functie. Net als in die tijd doen de bereikcoördinaten absoluut, markeer ze en het drukken op de F4-toets.
Klik daarna op de toets "OK" onder aan het venster.
- Zoals u kunt zien, heeft het complex van de ingevoerde functies een berekening gedaan en het resultaat uitgegeven in de eerste cel van de kolom "Group". Het eerste product kreeg een groep "A" toegewezen. De volledige formule die door ons wordt toegepast voor deze berekening is als volgt:
= Keuze (zoekbord ((stil ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / bedragen ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")
Maar in elk geval zullen in elk geval de coördinaten in deze formule anders zijn. Daarom kan het niet als universeel worden beschouwd. Maar met behulp van de handleiding die hierboven is gegeven, kunt u de coördinaten van elke tabel invoegen en deze methode met succes toepassen in elke situatie.
- Dit is echter niet alles. We berekenden alleen voor de eerste rij van de tabel. Om de kolom van de groep "Group" volledig in te vullen, moet u deze formule kopiëren in het onderstaande bereik (het elimineren van de cel van het string "Totaal") met behulp van de vulmarkering, zoals we al meer dan eens hebben gedaan. Nadat de gegevens worden gedaan, kan ABC-analyse worden beschouwd als uitgevoerd.
Zoals we zien, zijn de resultaten verkregen met behulp van de optie met behulp van een complexe formule niet van die resultaten die we werden uitgevoerd door sorteren. Alle goederen krijgen dezelfde categorieën toegewezen, maar tegelijkertijd veranderden de rijen hun eerste positie niet.
Les: Master of Functions in Excele
Het Excel-programma kan de ABC-analyse voor de gebruiker aanzienlijk verlichten. Dit wordt bereikt door een dergelijk hulpmiddel te gebruiken als sorteren. Daarna wordt de individuele specifieke zwaartekracht, geaccumuleerd aandeel en, eigenlijk, partitionering in groepen berekend. In gevallen waarin de verandering in de initiële positie van de rijen in de tabel niet is toegestaan, kunt u de methode toepassen met behulp van een complexe formule.