ABC-Analyse in Excel

Eine der wichtigsten Methoden des Managements und der Logistik ist die ABC-Analyse. Damit können Sie die Ressourcen des Unternehmens, der Waren, Kunden usw. klassifizieren. Entsprechend dem Wichtigkeitsgrad. Gleichzeitig wird nach der wichtigsten Ebene jedes der oben genannten Einheit eine von drei Kategorien zugewiesen: A, B oder C. Excel-Programm hat in seinen Gepäckwerkzeugen, die es erleichtern, diese Art der Analyse zu leichtern. Lassen Sie uns herausfinden, wie Sie sie verwenden, und wie ist die ABC-Analyse.

Mit ABC-Analyse verwenden

Die ABC-Analyse ist eine Art verbesserter und an moderne Bedingungen für das Pareto-Prinzip angepasst. Nach dem Verfahren des Verhaltens werden alle Elemente der Analyse in drei Kategorien nach dem Wichtigkeitsgrad unterteilt:

• Kategorie A - Elemente mit einer Kombination von mehr als 80% der spezifischen Schwerkraft;
• Kategorie B - Elemente, deren Kombination von 5% bis 15% der spezifischen Schwerkraft liegt;
• Kategorie C - Die verbleibenden Elemente, deren Gesamtaggregat 5% und weniger spezifisch ist.

Separate Unternehmen nutzen fortgeschrittenere Techniken und Teilen von Gegenständen nicht auf 3, aber um 4 oder 5 Gruppen, sondern auch auf das klassische ABC-Analyseschema.

Methode 1: Analyse mit Sortierung

Excel ABC-Analyse wird mit Sortierungen durchgeführt. Alle Artikel werden von fettig bis weniger sortiert. Dann wird der akkumulative Anteil jedes Elements berechnet, auf deren Grundlage er eine bestimmte Kategorie zugewiesen wird. Finden Sie heraus, wie die angegebene Methodik in der Praxis verwendet wird.

Wir haben einen Tisch mit einer Liste von Produkten, die das Unternehmen für einen bestimmten Zeitraum verkauft, und die entsprechende Anzahl von Umsatzerlöse aus ihrem Verkauf. Am Ende der Tabelle, das Ergebnis der Umsatzerlöse im Allgemeinen auf allen Namen der Ware. Mit der ABC-Analyse besteht eine Aufgabe, die diese Waren in Gruppen durch ihre Bedeutung für das Unternehmen brechen.

1. Wir markieren einen Tisch mit dem Cursor, indem wir die linke Maustaste schließen, ohne die Kappe und die letzte Zeichenfolge. Gehen Sie zur Registerkarte "Daten". Wir klicken auf die Schaltfläche "Sortieren", die sich in der Symbolleiste "Sortieren und Filter" auf dem Band befinden.

   

   Sie können auch anders tun. Wir weisen den obigen Tabellenbereich zuordnen und wechseln dann auf die Registerkarte "Home" und klicken Sie auf die Schaltfläche "Sortieren und Filter", die sich in der Bearbeitungs-Toolbox auf dem Band befindet. Die Liste ist aktiviert, in der die Position "Anpassbare Sortierung" aktiviert ist.



2. Bei der Anwendung einer der obigen Aktionen startet das Fenster Sortiereinstellungen. Wir sehen so, dass die "Meine Daten enthalten Parameter" der Header "auf ein Häkchen eingestellt. Installieren Sie im Falle seiner Abwesenheit.

   Geben Sie im Feld "Spalten" den Namen der Spalte an, in der die Umsatzdaten enthalten sind.

   Im Feld "Sortieren" müssen Sie angeben, auf der spezifische Kriterien sortiert werden. Hinterlassen Sie die voreingestellten Einstellungen - "Werte".

   Auf dem Feld "Reihenfolge" zeigen Sie die Position "absteigend".

   Klicken Sie nach dem Produkt der angegebenen Einstellungen auf die Schaltfläche "OK" am unteren Rand des Fensters.



3. Nachdem die angegebene Aktion ausgeführt wurde, wurden alle Artikel von mehr als einem kleineren sortiert.



4. Jetzt sollten wir den Anteil jedes Elements für ein allgemeines Ergebnis berechnen. Erstellen Sie eine zusätzliche Spalte für diese Zwecke, die wir "Teilengewicht" nennen. In der ersten Zelle dieser Spalte setzen wir das Zeichen "=", nachdem Sie einen Link zur Zelle angeben, in dem der Umsatzbetrag der Umsetzung des relevanten Produkts ist. Als nächstes setzen Sie das Zeichen der Division ("/"). Danach geben wir die Koordinaten der Zelle an, die den Gesamtbetrag des Verkaufs der Waren im gesamten Unternehmen enthalten.

   Angesichts der Tatsache, dass die angegebene Formel mit dem Füllmarker in andere Zellen der Spalte "Freigabe" kopiert wird, müssen wir die Adresse des Links an das Element mit dem endgültigen Umsatzbetrag an das Enterprise festlegen, das wir beheben müssen. Um dies zu tun, machen Sie einen Link absolut. Wählen Sie die Koordinaten der angegebenen Zelle in der Formel aus und drücken Sie die Taste F4. Vor den Koordinaten erschien, wie wir sehen, ein Dollarzeichen, das anzeigt, dass der Link absolut geworden ist. Es sei darauf hingewiesen, dass der Verweis auf den Umsatzbetrag der ersten in der Warenliste (Produkt 3) relativ bleiben sollte.

   Um Berechnungen vorzunehmen, drücken Sie die ENTER-Taste.



5. Wie wir sehen, erschien der Anteil des Umsatzes aus dem in der Liste angegebenen ersten Produkt in der Zielzelle. Um die Formel in den untenstehenden Bereich zu kopieren, setzen wir den Cursor in die rechte untere Ecke der Zelle. Seine Transformation in der Füllmarkierung, mit einem Blick eines kleinen Kreuzes. Wir klicken auf die linke Maustaste und ziehen Sie den Füllmarker bis zum Ende der Säule.



6. Wie Sie sehen, ist die gesamte Spalte mit Daten gefüllt, die den Umsatzanteil aus der Implementierung jedes Produkts kennzeichnen. Der Wert des spezifischen Gewichts wird jedoch im numerischen Format angezeigt, und wir müssen ihn in den Prozentsatz umwandeln. Markieren Sie dazu den Inhalt der Spalte "Spezifisches Gewicht". Dann bewegen wir uns auf die Registerkarte "Home". Auf dem Band in der Gruppe Gruppeneinstellungen befindet sich ein Feld, das Datenformat anzeigt. Wenn Sie standardmäßig keine zusätzlichen Manipulationen erzielt haben, muss das Format "Allgemein" dort installiert sein. Klicken Sie auf das Symbol in Form eines Dreiecks rechts auf diesem Gebiet. Wählen Sie in der Liste der Formate die Position "Prozentsatz" aus.



7. Wie wir sehen, wurden alle Spaltenwerte in Prozentwerte umgewandelt. Wie es sein sollte, sind 100% in der Saite von "Total" angegeben. Der Warenanteil wird in der Spalte von einem größeren bis zu einem kleineren erwartet.



8. Jetzt sollten wir eine Säule erstellen, in der der angesammelte Anteil mit einem wachsenden Ergebnis angezeigt wird. Das heißt, in jeder Linie auf das individuelles spezifische Gewicht eines bestimmten Produkts wird der Anteil aller von allen oben genannten Produkten hinzugefügt, die sich in der obigen Liste befinden. Für die Erstwartungen in der Liste (Produkt 3) ist der individuelle Anteil und der angesammelte Aktie gleich, aber alle nach dem individuellen Indikator müssen den gesammelten Anteil des vorherigen Elements der Liste hinzufügen.

   In der ersten Zeile werden wir also in die Spalte "Akkumulierte Freigabe" des Indikators der Spalte "spezifisch" übertragen.



9. Stellen Sie dann den Cursor den Cursor in die zweite Zelle der Spalte "CHOARUMED Share" ein. Hier müssen wir die Formel anwenden. Wir setzen das Zeichen "gleich" und falten den Inhalt der Zelle "Der Anteil" derselben Zeile und den Inhalt der Zelle "Akkumulierter Freigabe" aus der oben genannten Zeichenfolge. Alle Referenzen sind relativ reserviert, dh wir produzieren keine Manipulationen mit ihnen. Führen Sie danach einen Klick auf die ENTER-Taste aus, um das Endergebnis anzuzeigen.



10. Jetzt müssen Sie diese Formel in den Zellen dieser Spalte kopieren, die unten aufgestellt sind. Dazu verwenden wir den Füllmarker, an den wir bereits beim Kopieren der Formel in der Teilenspalte zurückgegriffen haben. Gleichzeitig ist die Zeichenfolge "Total" nicht erforderlich, da das angesammelte Ergebnis 100% auf dem letzten Produkt aus der Liste angezeigt wird. Wie Sie sehen, waren alle Elemente unserer Säule danach gefüllt.



11. Erstellen Sie danach eine Spalte "Gruppe". Wir müssen Waren nach der angegebenen angesammelten Aktie nach Kategorie A, B und C gruppieren. Wie wir uns erinnern, werden alle Elemente von Gruppen gemäß dem folgenden System verteilt:
    • A - bis zu 80%;
    • B - die folgenden 15%;
    • C - die restlichen 5%.

    So weist alle Waren, die den Anteil des spezifischen Gewichts an der Grenze von bis zu 80% ansammeln, die Kategorie A zuweisen. Die Ware mit dem angesammelten spezifischen Gewicht von 80% bis 95% ist der Kategorie B zugewiesen. Die verbleibenden Warengruppe mit dem Wert von mehr als 95% des gesammelten spezifischen Gewichts, den wir eine Kategorie C vergeben.



12. Zur Klarheit können Sie die angegebenen Gruppen mit verschiedenen Farben füllen. Aber es ist in Wille.

So haben wir die Elemente auf der Ebene der Wichtigkeit unter Verwendung von ABC-Analysen gebrochen. Bei der Verwendung einiger anderer Techniken Wie oben erwähnt, wird es verwendet, um in eine größere Anzahl von Gruppen zu brechen, aber das Prinzip des Brechens in diesem Fall bleibt nahezu unverändert.

Lektion: Sortieren und Filtern in Excel

Methode 2: Verwenden einer komplexen Formel

Natürlich ist die Verwendung der Sortierung die häufigste Methode, um die ABC-Analyse in Excele durchzuführen. In einigen Fällen ist es jedoch notwendig, diese Analyse durchzuführen, ohne dass die Linien an Orten in der Quelltabelle neu angeordnet werden. In diesem Fall wird eine komplexe Formel zur Rettung kommen. Zum Beispiel verwenden wir dieselbe Quelltabelle wie im ersten Fall.

1. Wir fügen zur Quelltabelle hinzu, die den Namen der Ware und den Umsatz vom Verkauf von jedem von ihnen, der Spalte "Gruppe" enthält. Wie Sie sehen, können wir in diesem Fall keine Säulen mit der Berechnung einzelner und kumulierender Fraktionen hinzufügen.



2. Wir erstellen die erste Zellzuteilung in der Gruppe Gruppenspalte, nach der Sie einen Klick auf die Schaltfläche "Funktion" in der Nähe der Formelzeile ausführen.



3. Masters Aktivierung von Funktionen. Wir gehen in die Kategorie "Links und Arrays". Wählen Sie die Funktion "Wahl" aus. Wir klicken auf die Schaltfläche "OK".



4. Das Fenster des Spielarguments ist aktiviert. Die Syntax wird wie folgt dargestellt:

   = Auswahl (nummer_intex; value1; value2; ...)

   Die Aufgabe dieser Funktion ist der Rückzug eines der angegebenen Werte, abhängig von der Indexnummer. Die Anzahl der Werte kann 254 erreichen, aber wir benötigen nur drei Namen, die ABC-Analysekategorien entsprechen: A, B, C. Wir können das Symbol "A" sofort in das Feld "B" eingeben "Feld, Feld" Wert3 "-" c ".



5. Mit dem Argument "Indexnummer" muss jedoch durch den Bau mehrerer zusätzlicher Betreiber gründlich gerissen werden. Installieren Sie den Cursor im Feld "Indexnummer". Klicken Sie anschließend auf das Symbol mit einer Ansicht des Dreiecks links auf der linken Seite der Schaltfläche "Funktion". Eine Liste der neu verwendeten Bediener öffnet sich. Wir brauchen eine Funktion der Suche. Da es nicht in der Liste ist, klicken wir auf die Inschrift "Andere Funktionen ...".



6. Das Fenster des FUNKTIONS-Assistentenfensters wird erneut gestartet. Gehen Sie wieder in die Kategorie "Links und Arrays". Wir finden die Position der "Suchplatine", ordnen Sie es auf und klicken Sie auf die Schaltfläche "OK".



7. Die Argumente der Argumente des Suchbetreibers werden geöffnet. Die Syntax davon hat das folgende Formular:

   = Suchplatine (SEARCH_NAME; Ansicht __massive; type_station)

   Der Zweck dieser Funktion besteht darin, die Positionsnummer des angegebenen Elements zu definieren. Das heißt, genau das, was wir für das Feld "Indexnummer" benötigen, eine Auswahl.

   Im Feld "Listing Array" können Sie sofort den folgenden Ausdruck fragen:

   {0: 0,8: 0,95}

   Es sollte präzise in lockigen Klammern sein, als Array-Formel. Es ist nicht schwer zu erraten, dass diese Zahlen (0; 0,8; 0,95) die Grenzen der angesammelten Aktie zwischen Gruppen bezeichnen.

   Das Feld "Art des Vergleichs" ist nicht obligatorisch, und in diesem Fall werden wir es nicht füllen.

   Stellen Sie im Feld "Zweitwert" den Cursor ein. Als nächstes durch das oben beschriebene Symbol in Form eines Dreiecks bewegen wir uns zum Assistenten der Funktionen.



8. Diesmal im Function-Assistenten bewegen wir uns in die Kategorie "mathematisches". Wählen Sie den Namen "Silent" und drücken Sie die Taste "OK".



9. Das Fenster Funktionsargumente wird gestartet. Der angegebene Bediener fasst die Zellen zusammen, die den bestimmten Zustand erfüllen. Seine Syntax ist:

   = Silent (Reichweite; Kriterium; Reichweite_Suming)

   Geben Sie im Feld "Range" die Adresse der Spalte "Revenue" ein. Für diese Zwecke setzen wir den Cursor auf dem Feld, und wählen Sie dann durch den Clip der linken Maustaste alle Zellen der entsprechenden Spalte aus, ohne den Wert "Total" auszuschließen. Wie Sie sehen, erschien die Adresse sofort auf dem Feld. Darüber hinaus müssen wir diesen Link absolut machen. Um dies zu tun, erstellen Sie die Zuteilung und klicken Sie auf die Taste F4. Die Adresse wurde von Dollarzeichen freigegeben.

   Im Feld "Kriterium" müssen wir einen Zustand einstellen. Geben Sie den folgenden Ausdruck ein:

   ">"&

   Unmittelbar danach geben wir die Adresse der ersten Zelle der Säule "Revenue" ein. Wir machen koordiniert horizontal in dieser Adresse absolut und fügen das Dollarzeichen von der Tastatur vor dem Buchstaben hinzu. Vertikale Koordinaten verlassen relativ, das heißt, es sollte kein Anzeichen vor der Ziffer geben.

   Danach drücken wir nicht die "OK" -Taste, und klicken Sie in der Formelzeichenfolge auf den Namen der Suchfunktion.



10. Dann kehren wir in das Argument-Fenster der Suchplatine zurück. Wie wir sehen, war der vom Bediener des Bedieners eingestellte Daten in dem Feld "Nebelwert". Aber das ist nicht alles. Gehen Sie zu diesem Feld und fügt bereits den verfügbaren Daten hinzu, um ein "+" -Zeichen ohne Zitate hinzuzufügen. Dann stellen wir die Adresse der ersten Zelle der Spalte "Revenue" ein. Und wieder machen wir koordinale Koordinaten horizontal mit absolutem und vertikalem Urlaub relativ.

    Als nächstes nehmen wir den gesamten Inhalt des Feldes "den gewünschten Wert" in den Klammern, wonach wir das Zeichen der Division ("/") setzen. Gehen Sie danach wieder durch das Dreiecksymbol mit der Funktion der Auswahl von Funktionen.



11. Wie beim letzten Running-Funktionsassistenten suchen wir nach dem notwendigen Bediener in der Kategorie "mathematische". Diese Zeit wird die gewünschte Funktion als "Summen" bezeichnet. Wir markieren es und drücken die "OK" -Taste.



12. Das Fenster des Bedieners Arguments wird geöffnet. Sein Hauptzweck ist die Summation von Daten in den Zellen. Die Syntax dieses Bedieners ist ganz einfach:

    = Summen (Nummer1; Nummer2; ...)

    Für unsere Zwecke wird nur das Feld "number1" benötigt. Wir stellen die Koordinaten der Spalte "Revenue" ein, die die Zelle, die die Ergebnisse enthält, beseitigen. Wir haben bereits einen solchen Betrieb im Bereich "Range" der Funktion durchgeführt. In dieser Zeit machen die Bereichskoordinaten absolut und markieren sie und drücken die Taste F4.

    Klicken Sie danach auf die Taste "OK" am unteren Rand des Fensters.



13. Wie Sie sehen, machte der Komplex der eingegebenen Funktionen eine Berechnung und erteilte das Ergebnis in der ersten Zelle der Spalte "Gruppe". Das erste Produkt wurde eine Gruppe "A" zugewiesen. Die vollständige Formel, die von uns für diese Berechnung angewendet wird, ist wie folgt:

    = Wahl (SUCHE BOARD ((SILLT ($ B $ 2: $ 2 27; ">" & $ B2) + $ B2) / Beträge ($ B $ 2: $ B $ 27); {0: 0,8: 0 , 95}); "A"; "B"; "c")

    Natürlich sind jedoch in jedem Fall die Koordinaten in dieser Formel unterschiedlich sein. Daher kann es nicht als universell angesehen werden. Mit dem oben angegebenen Handbuch können Sie jedoch die Koordinaten jeder Tabelle einfügen und diese Methode in jeder Situation erfolgreich anwenden.



14. Dies ist jedoch nicht alles. Wir berechnen nur für die erste Reihe des Tisches. Um die Spalte "Gruppengruppen" vollständig auszufüllen, müssen Sie diese Formel in den folgenden Bereich kopieren (beseitigen Sie die Zelle der Zeichenfolge "Total") mit dem Füllmarker, wie wir bereits mehr als einmal durchgeführt haben. Nachdem die Daten getroffen wurden, kann ABC-Analyse als ausgeführt betrachtet werden.

Wie wir sehen, unterscheiden sich die Ergebnisse, die mit der Option mit einer komplexen Formel erhalten werden, nicht von den Ergebnissen, die wir durch Sortieren durchgeführt wurden. Alle Waren werden in derselben Kategorie zugewiesen, nur gleichzeitig die Zeilen ändern ihre Ausgangsposition nicht.

Lektion: Master der Funktionen in Excele

Das Excel-Programm kann die ABC-Analyse für den Benutzer erheblich lindern. Dies wird durch ein solches Werkzeug als Sortierung erreicht. Danach wird das individuelle spezifische Gewicht, der angesammelte Anteil und eigentlich in Gruppen in Gruppen berechnet. In Fällen, in denen die Änderung der Ausgangsposition der Zeilen in der Tabelle nicht zulässig ist, können Sie das Verfahren mit einer komplexen Formel anwenden.