Die ABC-Analyse mit Hilfe von Pivot Tabellen

Um eine ABC-Analyse mit Excel zu erstellen gibt es verschiedene Möglichkeiten. Eine einfache Variante ist die Verwendung von Pivot Tabellen. Aber bevor es mit der ABC-Analyse losgeht, benötigen wir zunächst eine konsistente Datengrundlage.

Daten für die ABC-Analyse vorbereiten

Im ersten Schritt benötigen wir also eine Tabelle mit mindestens zwei Spalten. In der ersten Spalte stehen die Artikel und in der zweiten Spalte der zugehörige Wert, nach dem klassifiziert werden soll. Das kann der Umsatz, der Absatz, der Deckungsbeitrag oder etwas anderes sein. Das hängt ganz davon ab, was analysiert werden soll. Idealerweise verwenden wir dafür eine intelligente Tabelle.

Grundlagentabelle für die ABC-Analyse

Daten mit einer Pivot Tabelle zusammenfassen

Liegt die Tabelle vor, werden die Daten mit einer Pivot Tabelle zusammengefasst. Wer im Umgang mit Pivot Tabellen noch etwas Hilfe benötigt, findet hier ausführliche Erklärungen und Anleitungen.

Bei der Erstellung der Pivot Tabelle wird das Feld mit den Artikeln im Zeilenbereich angeordnet. Die Werte (Umsatz, Absatz…) werden in den Wertebereich gezogen.

Daten mit Pivot-Tabelle zusammenfassen

Artikel absteigend sortieren

Im nächsten Schritt werden die Artikel über die weiteren Sortieroptionen, absteigend nach ihrem Wertfeld sortiert. Jetzt steht der Artikel mit dem größten Wert ganz oben und entsprechend der kleinste Wert ganz unten.

Artikel absteigend nach Wert sortieren

Wertanteil kumulieren

Als nächstes öffnen wir die Wertfeldeinstellungen des Wertfeldes, wechseln auf den zweiten Reiter „Werte anzeigen als“ und wählen dann aus dem Dropdown-Menü „% laufende Summe in“ aus. Durch diese Einstellung werden die Werte in Prozent, kumuliert dargestellt.

In den Wertfeldeinstellungen lassen sich die Werte kumulieren

Grenzfestlegung für die Klassifizierung

Was noch fehlt, ist die Festlegung der Grenzen für die ABC-Klassifizierung. Standardmäßig wird nach dem Raster 80-15-5 eingeteilt. Also bis 80% kumulierter Wertanteil = A, bis 95% = B und darüber hinaus = C.

Zuordnung der Artikelklassen

Wir springen in das Arbeitsblatt mit der Grundlagentabelle und erweitern diese um eine neue Spalte. Die Spalte erhält die Überschrift „kum. Wertanteil“. Jetzt bilden wir mit einem SVERWEIS einen Bezug zur zuvor erstellten Pivot Tabelle mit den kumulierten %-Werten.

=SVERWEIS(Artikel;Pivot Tabelle;Spaltenindex;FALSCH)

Wir erhalten dadurch zu jedem Artikel in unserer Grundlagentabelle den kumulierten %-Wert.

Kumulierten Wertanteil über den SVERWEIS holen

Jetzt erweitern wir die Tabelle um eine weitere Spalte, mit der Überschrift „ABC“. Mit einer WENN(UND)-Funktion ordnen wir jetzt die Artikelklassen nach unserer Eingrenzung zu.

=WENN(kum. %-Wert<0,8;"A";WENN(UND(kum. %-Wert>=0,8;kum. %-Wert<0,95);"B";"C")) [caption id="attachment_1243" align="alignnone" width="1030"] ABC-Klassifizierung zuordnen[/caption]

Ergebnis mit Pivot Tabelle zusammenfassen

Wenn wir alles richtig gemacht haben, sollte jetzt jedem Artikel das entsprechende Klassifizierungsmerkmal A, B oder C zugeordnet worden sein. Die Tabelle lässt sich jetzt mit einer neuen Pivot Tabelle zusammenfassen und nach den Artikelklassen auswerten sowie grafisch aufbereiten.

ABC-Analyse in Form eines Pivot-Tabellen-Berichts

ABC-Analyse mit Pivot-Charts visualisieren

Für mehr Details, genauere Erklärungen zur ABC-Analyse sowie die Erweiterung um die XYZ- und die ABC-XYZ-Analyse, haben wir einen Online-Kurs erstellt. In diesem Kurs wird jede Menge Hintergrundwissen vermittelt und der praktische Einsatz in Unternehmen erläutert.

Mehr Informationen findest du unter ABC-XYZ-Analyse – Artikelklassifizierung mit Excel.