ABC-Analyse in Excel: Rechenweg, Formeln und laufende Pflege

Der Export aus dem ERP liegt auf dem Schirm, gefragt sind die A-Artikel, und zwar heute. Die ABC-Analyse in Excel ist dafür das naheliegende Werkzeug. Ravinder und Misra halten in ihrem Überblick zu den Rechenverfahren fest, dass sich das Gewichten und Bewerten „easily performed on a spreadsheet“ lasse; sie plädieren dabei für mehrkriterielle Verfahren und halten die Einteilung nach dem Wert allein für nicht mehr ausreichend. Interessanter für die Praxis ist, was dieselben Autoren 2014 über die Lehrbücher schrieben: Sie führten die Klassifikation „rarely“ mit einer brauchbaren Diskussion fort, wie A-, B- und C-Artikel danach eigentlich zu steuern seien. Genau diese Lücke macht aus einer fertigen Tabelle noch keine Entscheidung.

Wie rechnet man die ABC-Analyse in Excel?

Fünf Schritte, drei Formeln. Vorher stehen allerdings drei Entscheidungen an, die später nicht mehr ohne Neurechnung zu ändern sind.

Welche Spalten die Tabelle braucht, und was vorher zu entscheiden ist

Drei Spalten reichen als Eingang: Artikelnummer, Menge und Preis je Einheit, alles für einen festen Zeitraum. Den Wert je Artikel rechnet die Tabelle daraus selbst. Liefert Ihr Export den Wert bereits fertig, entfällt Schritt 2 weiter unten; wer ihn trotzdem rechnet, multipliziert die Menge ein zweites Mal hinein.

Vor der ersten Formel sind drei Dinge zu klären. Erstens die Wertgröße: Umsatz, Einstandswert oder Deckungsbeitrag führen zu verschiedenen Klassenbildern, weil Einkaufswert und Spanne auseinanderfallen. Das Beispiel hier rechnet mit dem Einstandswert, also Menge mal Einstandspreis. Welche Größe für welchen Zweck passt, behandeln die Artikel zur ABC-XYZ-Analyse und zur ABC-Analyse in der Beschaffung.

Zweitens der Zeitraum. Ein volles Geschäftsjahr glättet Saison, ein Quartal ist aktueller. Was Sie wählen, bestimmt mit, wie das Ergebnis aussieht: Artikel, die erst seit dem Frühjahr gelistet sind, haben keinen vollen Jahreswert und rutschen deshalb nach unten, ohne dass ihr Grundabsatz klein wäre.

Drittens, was aus dem Export herausgehört. Auslaufartikel, Musterpositionen, interne Verrechnungen und Streckengeschäfte verzerren das Bild, wenn sie mitlaufen. Ein Artikel, der zweimal in der Aktion war, verschiebt seinen Jahreswert nach oben, obwohl sich sein Grundabsatz nicht geändert hat; ob Sie Aktionsmengen herausrechnen, ist eine Entscheidung, die dokumentiert gehört.

Der Rechenweg in fünf Schritten

Die Formeln stehen in deutscher Schreibweise mit Semikolon als Trennzeichen.

Schritt 1: Die Klassengrenzen in eigene Zellen. Tragen Sie in H1 den Wert 80 % und in H2 den Wert 95 % ein, und zwar als Prozentwert, nicht als blanke Zahl 80 beziehungsweise 95. Steht dort die Zahl, vergleicht die Klassenformel später 0,46 mit 80, und alle Artikel landen in der A-Klasse, ohne dass Excel etwas meldet. Das klingt nach einer Kleinigkeit und ist der wichtigste Schritt der ganzen Anleitung. Powell, Baker und Lawson haben die Fehlerarten in betrieblich genutzten Tabellen systematisch ausgewertet; die fest in eine Formel geschriebene Zahl ist dort der häufigste Typ. Eine Grenze, die in der Formel steht, findet niemand wieder, wenn sie geändert werden soll.

Schritt 2: Den Wert je Zeile rechnen. In Spalte D: =B2*C2, also Menge mal Einstandspreis, nach unten ziehen.

Schritt 3: Absteigend nach Wert sortieren. Markieren Sie den gesamten Datenbereich und sortieren Sie nach Spalte D, absteigend. Ohne diesen Schritt ergibt die kumulierte Spalte keinen Sinn, weil sie dann irgendeine Reihenfolge aufaddiert.

Schritt 4: Den kumulierten Anteil rechnen. In Spalte E: =SUMME($D$2:D2)/SUMME($D$2:$D$7). Der erste Bezug hat zwei Dollarzeichen und bleibt beim Kopieren auf D2 stehen, das Ende wandert mit der Zeile mit. So summiert jede Zeile alles von oben bis zu sich selbst. Der Nenner $D$7 ist die letzte Zeile dieses Beispiels mit sechs Artikeln; bei 400 Artikeln steht dort entsprechend $D$401.

Schritt 5: Die Klasse zuordnen. In Spalte F: =WENN(E2<=$H$1;"A";WENN(E2<=$H$2;"B";"C")). Die Formel liest die Grenzen aus den Zellen von Schritt 1, statt 0,8 und 0,95 zu enthalten.

Rechenbeispiel, eigene Darstellung. Die Klassengrenzen 80 und 95 Prozent sind belegt, die Artikelwerte sind gewählt.

Die Variante für den Dauerbetrieb. Wenn Sie die Tabelle wiederverwenden, formatieren Sie den Bereich als Excel-Tabelle. Strukturierte Verweise passen sich laut Microsoft „beim Hinzufügen oder Entfernen von Daten in einer Tabelle“ automatisch an, der feste Endbezug entfällt damit. Der kumulierte Anteil lautet dann =SUMME(INDEX([Wert];1):[@Wert])/SUMME([Wert]). Und der Import aus dem ERP lässt sich als Abfrage speichern und laut Microsoft beim Öffnen der Datei oder in festen Zeitabständen aktualisieren, sodass der manuelle Export entfällt. Beide Varianten setzen voraus, dass die Formel innerhalb derselben Tabelle steht und die Wertspalte auch „Wert“ heißt; benennen Sie sie um, ist der Spaltenname in der Formel mitzuziehen.

Klassengrenzen setzen und das Ergebnis lesen

Die Grenzen sind eine Entscheidung, kein Rechenergebnis. Juhala und Shamsuzzoha beschreiben die verbreitete Aufteilung mit 80 und 95 Prozent des kumulierten Werts. Lolli und Kollegen dokumentieren eine Fallstudie, in der das Management 75 und 95 Prozent festgelegt hat. Zwei seriöse Arbeiten, zwei verschiedene Schnitte: Es gibt keine richtige Grenze, es gibt eine dokumentierte.

Auch die Zuordnung an der Grenze ist eine Konvention, und hier unterscheiden sich die Quellen ebenfalls. Für dieses Beispiel gilt die Konvention: Ein Artikel, dessen kumulierter Anteil die Grenze überschreitet, gehört bereits zur nächsten Klasse; wer sie exakt trifft, bleibt in der oberen. Die WENN-Formel aus Schritt 5 setzt mit <= genau das um.

Der Fall ist nicht theoretisch: Im Beispiel unten liegt Art. 1407 bei exakt 95,0 Prozent und ist nur deshalb B. Lolli und Kollegen ordnen umgekehrt zu, dort endet die A-Klasse bei 75,208 Prozent. Welche Variante Sie wählen, ist gleichgültig; dass sie dokumentiert ist, nicht.

So sieht das Ergebnis für sechs Artikel aus:

Rechenbeispiel mit gewählten Werten, Gesamtwert 100.000 Euro. Der Aufbau ist zum Nachbauen gedacht.

Zwei Artikel tragen hier 70 Prozent des Einkaufswerts, der letzte trägt 5 Prozent.

Eine Eigenheit des Beispiels zeigt zugleich eine Falle: Die letzten beiden Artikel haben denselben Wert von 5.000 Euro und landen trotzdem in verschiedenen Klassen. Welcher von beiden B wird, entscheidet allein ihre Reihenfolge vor dem Sortieren, denn Excel lässt gleiche Werte in ihrer ursprünglichen Ordnung stehen. Wo Werte gleich sind, braucht die Sortierung deshalb ein zweites Kriterium, etwa die Artikelnummer. Für die Auswertung über ein echtes Sortiment lohnt eine Pivot-Tabelle mit zwei Zahlen je Klasse: wie viele Artikel darin liegen und welchen Wertanteil sie tragen. Erst dieser Vergleich macht sichtbar, ob die Einteilung Ihr Sortiment überhaupt trennt. Schnelldreher und Langsamdreher stehen im selben Export, und die Menge allein ordnet sie falsch ein.

Was folgt aus den Klassen, und wer hält die Excel-Tabelle verlässlich?

Die ABC-Analyse in Excel ist damit gerechnet, die Klassen stehen. Ab hier endet, was Excel von allein leisten kann, und es beginnt die Frage, was mit dem Ergebnis geschieht.

Von der Klasse zur Bestandsentscheidung

Üblich ist, an die Klassen Servicegrade und Prüfintervalle zu hängen. Lolli und Kollegen beschreiben genau das: Verschiedene Servicegrade und Prüfrhythmen werden mit den Klassen gekoppelt, „in order to simplify the inventory management per class rather than per item“. Bei Juhala und Shamsuzzoha bekommt jede Klasse ein Zeitfenster für Bestellungen, im dortigen Fallunternehmen A wochenweise bis D mit drei Monatsverbräuchen. Das ist die Praxis eines einzelnen Elektronikherstellers und kein Richtwert, zeigt aber das Muster.

Diese Kopplung hat eine dokumentierte Grenze. Lolli und Kollegen halten fest, dass Klassifikation und die Suche nach passenden Strategien je Klasse in der Praxis „generally kept separate“ bleiben, sodass das ursprüngliche Ziel der Verbesserung „often being forgotten“ wird. Ravinder und Misra finden dieselbe Lücke in den Lehrbüchern. Und Teunter, Babai und Syntetos zeigen an drei großen realen Datensätzen, dass klassenweise fest vorgegebene Servicegrade zu Lösungen führen, die weit vom Kostenoptimum entfernt sind; ihre Aussage betrifft ausdrücklich den Servicegrad.

Die Klasse strukturiert also die Aufmerksamkeit, sie ersetzt die Einzelfallentscheidung nicht. Wie viel bestellt wird, entscheidet daneben die Gebindegröße des Lieferanten, und die interessiert sich nicht dafür, ob ein Artikel A oder C ist.

Vier Fehler, die eine Tabelle nicht von selbst anzeigt

Nur ein Teil wurde sortiert. Thorne und Collins beschreiben einen Fall aus Virginia, in dem eine Behörde 2023 bei über 40.000 Einträgen eine Sortierung nur auf einen Teil der Daten anwandte. In der ABC-Tabelle trifft dieser Fehler Schritt 3, und er ist von außen unsichtbar: Die kumulierte Spalte rechnet weiter und liefert Zahlen, die plausibel aussehen.

Der Bereich ist nicht mitgewachsen. Mit $D$7 als festem Ende landen Zeilen, die Sie unterhalb anhängen, nicht im Nenner, und die kumulierten Anteile steigen über 100 Prozent. Zeilen, die Sie innerhalb des Bereichs einfügen, nimmt Excel von selbst mit, der Bezug wächst dann auf $D$8. In einer als Excel-Tabelle formatierten Liste tritt beides nicht auf, weil die Bezüge mitwachsen; mit festen Bezügen ist es der Fehler, mit dem jede wiederverwendete Tabelle rechnen muss.

Die Zahl steht in der Formel. Wer 0,8 und 0,95 direkt in die WENN-Formel schreibt, hat heute ein richtiges Ergebnis und beim nächsten Ändern ein Problem, weil die Grenze an mehreren Stellen steht und nicht alle gefunden werden. Deshalb Schritt 1 vor allen anderen.

Die Datenbasis war schon verzerrt, und das nur bei Absatz- oder Verbrauchswerten. Eine ungebuchte Fehlmenge senkt den gemessenen Absatz; der Artikel rutscht Richtung C, obwohl er häufiger gefehlt hat als andere. Zenkova und Kollegen zeigen diese Richtung für die ABC-XYZ-Klassifikation. Bei der Einkaufswertbasis dieses Beispiels tritt der Effekt nicht ein, weil die bestellte Menge eine Entscheidung ist und kein Messwert. Wie sich solche Perioden erkennen lassen, behandelt der Artikel zur Stockout-Verzerrung.

Was diese vier Fälle verbindet, ist nicht ihre Häufigkeit, sondern dass man sie nicht sieht. Panko fasst neun Experimente mit über 1.000 Teilnehmern zusammen: Die durchschnittliche Entdeckungsrate bei der Tabellenprüfung liegt bei 60 Prozent, bei den schwierigsten Fehlern unter 10 Prozent, und Erfahrung mit Tabellenkalkulation ändert daran nichts. Daraus folgt kein Misstrauen gegen Excel. Es folgt, dass die Kontrolle nicht vom Augenschein abhängen darf: eine Summenprobe über die Wertspalte, ein Blick auf den letzten kumulierten Wert, der exakt 100 Prozent sein muss, und eine Stichprobe an der Klassengrenze. Die 100-Prozent-Probe trägt allerdings nur bei durchgehend positiven Werten; stehen Retouren oder Gutschriften mit negativem Vorzeichen in der Spalte, zeigt die letzte Zeile trotzdem 100 Prozent, während einzelne Zwischenzeilen über 100 Prozent liegen.

Die Klasse ist ein Stichtagswert

Die Einteilung beschreibt den Zeitraum, aus dem sie gerechnet wurde, und sonst nichts. Eine im Januar gerechnete Klasseneinteilung beschreibt im Juli ein anderes Sortiment, wenn Saison und Aktionen dazwischenliegen.

Dass Artikel bei erneuter Rechnung die Klasse wechseln, ist dokumentiert: Dhoka und Choudary halten in einer Fallstudie an 330 Artikeln eines Automobilzulieferers fest, dass Artikel in den Folgemonaten von einer Klasse in die andere springen können und manche inaktiv werden. Sie misst die Veränderung zwar über eine eigene Kennziffer, eine interpretierbare Wanderungsquote ergibt sich daraus aber nicht, und eine belastbare Zahl existiert auch sonst nicht. Dieselben Autoren halten fest, es gebe keine Branchenstandards für die Länge des Auswertungszeitraums, er werde „randomly decided based on convenience“; das ist ihre Einschätzung der Praxis, kein Befund ihrer Fallstudie.

Für den Rhythmus folgt daraus eine Überlegung, keine Regel: Rechnen Sie das ganze Sortiment in einem einheitlichen Takt neu, nicht klassenweise unterschiedlich. Wer C-Artikel seltener prüft als A-Artikel, bemerkt einen aufsteigenden C-Artikel nie, weil er genau in dem Takt geprüft wird, der seiner alten Klasse entspricht.

Damit steht die Frage, auf die dieser Artikel hinausläuft. Excel wiederholt die Rechnung zuverlässig, mit gespeicherter Abfrage auch den Import. Offen bleibt, wer die Datenbasis verantwortet, wer die Grenzen festlegt und wer dafür sorgt, dass aus der neuen Klasse auch eine andere Entscheidung folgt.

Was ein Planungssystem anders macht als die Excel-Tabelle

Der Unterschied liegt nicht im Rechnen, sondern darin, wo das Ergebnis steht und woher die Daten kommen.

In Circly Bestellvorschlägen zeigt der Vorschlag je Artikel die ABC-XYZ-Klassifizierung, und zwar neben Bestand, Reichweite, Lieferzeit, Prognose und Lieferantenrestriktionen. Die Klasse liegt damit dort, wo disponiert wird, und nicht in einer Datei daneben. Die Datenbasis wird per Schnittstelle aus dem bestehenden ERP gezogen, bei täglicher Datenlieferung also ohne manuellen Export dazwischen. Ob die Klassifizierung selbst in diesem Takt neu gerechnet wird, ist eine andere Frage; der Vorschlag zeigt sie an.

Zwei Punkte betreffen die Qualität der Grundlage. Fehlbestandsperioden in der Historie werden gesondert behandelt und nicht als Nullnachfrage gewertet, sofern das ERP Bestandsverlauf oder Verkaufsstatus liefert. Und jeder Vorschlag ist auf Artikelebene nachvollziehbar begründet: keine Blackbox, jede Empfehlung einsehbar. Eine Tabelle zeigt über die Spur zum Vorgänger zwar, aus welchen Zellen ein Wert entstanden ist, nicht aber, aus welchem Vorgang im Quellsystem er stammt.

Fazit

Legen Sie zuerst fest, womit Sie rechnen: Wertgröße, Zeitraum, Klassengrenzen und die Konvention an der Grenze. Schreiben Sie diese vier Festlegungen auf, am besten in dasselbe Tabellenblatt. Sie kosten zehn Minuten und entscheiden darüber, ob das Ergebnis ein halbes Jahr später noch nachvollziehbar ist.

Dann rechnen Sie, und zwar mit den Grenzen in eigenen Zellen und als Excel-Tabelle, wenn die Auswertung wiederkehren soll.

Erst danach kommt der Teil, den die Tabelle nicht abnimmt: festlegen, wer in welchem Rhythmus neu rechnet und wer die Folgeentscheidungen pflegt. Wer diesen Schritt auslässt, hat eine Momentaufnahme im Ordner, keine Klassifikation.

Weiterführende Quellen

1. Ravinder, H. & Misra, R.B. (2014): ABC Analysis For Inventory Management: Bridging The Gap Between Research And Classroom, American Journal of Business Education 7(3)

2. Lolli, F., Ishizaka, A., Gamberini, R. & Rimini, B. (2017): A multicriteria framework for inventory classification and control with application to intermittent demand

3. Juhala, J. & Shamsuzzoha, A. (2024): Automation in purchasing order through items classifications: a case study in Finland

4. Powell, S.G., Baker, K.R. & Lawson, B. (2009): Errors in Operational Spreadsheets, Journal of Organizational and End User Computing 21(3)

5. Panko, R. (2015): What We Don’t Know About Spreadsheet Errors Today, EuSpRIG 2015

6. Thorne, S. & Collins, J. (2026): Anatomy of a Spreadsheet Failure: Analysing the EuSpRIG Horror Story Corpus, EuSpRIG 2026

7. Teunter, R.H., Babai, M.Z. & Syntetos, A.A. (2010): ABC Classification: Service Levels and Inventory Costs, Production and Operations Management 19(3)

8. Zenkova, Z., Musoni, W. & Tarima, S. (2020): Accounting for deficit in ABC-XYZ analysis

9. Dhoka, D.K. & Choudary, Y.L. (2013): ABC Classification for Inventory Optimization, IOSR Journal of Business and Management 15(1)

10. Microsoft Support: Verwenden von strukturierten Verweisen in Excel-Tabellen

11. Microsoft Support: Aktualisieren einer externen Datenverbindung in Excel

FAQ