ABC-Analyse mit Power Query

Hinweis: Diese Aufgabe setzt voraus, dass Sie bereits etwas Erfahrung im Umgang mit Power Query haben. Tiefgreifende Kenntnisse sind nicht erforderlich.

Die Aufgabe

Was das Ergebnis einer ABC-Analyse aussagt werden Sie wissen, wenn Sie eine solche erstellen wollen (oder sollen). Diese Auswertung ist eines der Lieblings-“Spielzeuge“ der Controller. 😎 Wie eine ABC-Analyse mit Excel-Mitteln zu erstellen ist, können Sie zu genüge im Internet nachlesen. Dennoch biete ich Ihnen hier als Vergleich eine nicht kommentierte Lösung auf der Basis plain Excel an. Diese Vorgehensweise, die Technik ist recht einfach und für viele Anwender/innen auch transparent. – Ein gewisses Maß an Nachteil kann in mangelnder Flexibilität in Sachen Darstellung gesehen werden. So wird die Anordnung, di Sortierung grundsätzlich so sein, wie in dem Arbeitsblatt dargestellt. Dennoch stehe ich zum Leitsatz: „Form follows function“ …

Wenn Sie -aus welchen Gründen auch immer- solch eine Gruppenbildung in Power Query vornehmen wollen, ist das ganze schon etwas komplexer. Ich habe die Aufgabe noch etwas verkompliziert, indem ich die (optionale) Forderungen aufgestellt habe, dass die ursprüngliche Reihenfolge der Artikel bzw. Artikelgruppen erhalten bleibt oder die Liste auch nach Umsätzen von hoch nach niedrig bzw. nicht vorhanden (also 0 €) sortiert wird.

Beginnen Sie damit, diese Datei mit den Roh-Daten in Excel zu laden. Es sind nicht allzu viele Positionen aber für das Prinzip reicht es vollkommen aus und so ist es auch deutlich übersichtlicher. Das Ergebnis dieser Analyse beruht auf der 80-20-Regel, die immer wieder für eine Bewertung der Priorität herangezogen wird. Ganz grob ausgedrückt machen (beispielsweise) die Top 20% der Produkte 80% des Umsatzes aus. Die Produkte mit den höchsten Umsätzen gehören zur Klasse oder Gruppe A, ein weiterer Teil zur Gruppe B und die restlichen werden C zugeordnet. Wegen der geringen Anzahl der Positionen sind diese Prozentwerte nicht wörtlich zu nehmen aber eine Richtlinie.

Nach dem Import der Quelldaten in den Power Query-Editor stellt sich das so dar:

Die Roh-Daten direkt nach dem Import

Die Roh-Daten direkt nach dem Import

Die Reihenfolge der Produktgruppen ist scheinbar willkürlich, die Umsätze sind keineswegs geordnet. Bereits an dieser Stelle werden Sie sich entscheiden, ob nach Abschluss der Auswertung die bisherige Reihenfolge erhalten bleiben soll oder ob wahlweise auch nach A-B-C sortiert werden soll. Ich entscheide mich, dass beide Möglichkeiten genutzt werden können. Darum werden Sie im ersten Schritt eine Index-Spalte einfügen:

  • Menü/Register Spalte zufügen
  • Im Menüband Indexspalte anklicken.

Dadurch wird eine neue Spalte mit einem 0-basierten Index erzeugt. Diese kann später zur Sortierung auf die ursprüngliche (also jetzige) Reihenfolge genutzt werden. Da (später) vor dem Schließen & laden alle nicht relevanten Spalten entfernt werden, kann diese Indexspalte auch nicht schaden. 😉 

Im nächsten Schritt ist es wichtig, alle leeren Felder der Spalte Umsatz mit einem numerischen Wert zu füllen. Bei diesen wenigen Zeilen wird Ihnen sofort auffallen, dass bei Server und Leih-Service der Wert null eingetragen ist, was ja für eine (wirklich) leere Zelle steht. Das darf nicht so bleiben. Den folgenden Schritt können Sie auch bei großen Datenmengen anwenden, selbst wenn nicht ersichtlich ist, ob irgendwo ein leeres Feld in der Umsatzspalte enthalten ist:

  • Rechtsklick in die Überschrift der Spalte Umsatz.
  • Im Kontextmenü wählen Sie Werte ersetzen…
  • Geben Sie im Dialog bei Zu suchender Wert den Text null (in Kleinschreibung) ein.
  • Bei Ersetzen durch schreiben Sie die Zahl 0.

Leere Zellen der Umsatz-Spalte durch die Zahl 0 ersetzen

Leere Zellen der Umsatz-Spalte durch die Zahl 0 ersetzen

  • Bestätigen Sie mit OK.
  • Sortieren Sie die Spalte Umsatz nun noch aufsteigend.

 Der folgende Schritt dient dazu, einen Hintergrund-Prozess zu initiieren. Sie erzeugen wiederum eine Benutzerdefinierte Spalte und geben dieser beispielsweise die Überschrift Liste. Als Formel tragen Sie in exakt dieser Schreibweise ein:
List.Buffer(#"Sortierte Zeilen"[Umsatz])

In jeder Zelle dieser Hilfsspalte steht das Wort List. Im Gegensatz zu vielen anderen Gelegenheiten werden Sie es dabei belassen und den Doppelpfeil Doppelpfeil in der Überschrift nicht für die Erweiterung der Werte nutzen. Die Spalte bleibt genau so, wie sie ist.

Bei der Gelegenheit eine Anmerkung: Ich zeige Ihnen die Formeln hier auf, erkläre sie aber nicht weiter. Dafür hat Microsoft eine prima Website mit guten und ausführlichen Erklärungen zu den Funktionen der Sprache M. In diesem Beitrag geht es um die Lösung, weniger um die Hintergründe und es sollen keine tieferen Kenntnisse in Sachen Programmierung mit der Abfragesprache vermittelt werden.

Falls Sie die A-B-C Analyse aus Excel kennen werden sie gewiss wissen, dass eine aufsteigende Sortierung der Umsätze die Basis dafür ist, dass in jeder Zeile die kumulierte Summe der Umsätze gebildet werden muss. Und das ist auch der nächste Schritt hier im Editor. Wiederum eine neue Benutzerdefinierte Spalte, die Überschrift kann zum Beispiel lfd. Summe (kumuliert) sein; sie können aber auch gerne einen anderen sprechenden Namen wählen. Als Benutzerdefinierte Spaltenformel geben Sie in exakt dieser Schreibweise ein:
List.Sum(List.Select([Liste], (x)=>x>=[Umsatz]))
wobei sie die Spaltennamen [Liste] und [Umsatz] vorzugsweise durch einen Doppelklick auf den entsprechenden Spaltennamen in der rechten Textbox übernehmen.

Nach einem OK werden Sie erkennen, dass die berechneten Werte zwar stimmen aber in umgekehrter Reihenfolge in den Zeilen stehen. Der höchste kumulierte Umsatz steht in Zeile 1, der niedrigste in der letzten Zeile. Und das ist korrekt so.

Aus diesen berechneten Werten muss nun je Produktgruppe der prozentuale Anteil in Bezug zur Summe aller Umsätze berechnet werden. Dazu erstellen Sie (natürlich) eine weitere Benutzerdefinierte Spalte. Als Überschrift verwende ich % (kumuliert), die Formel sieht so aus:
[#"lfd. Summe (kumuliert)"]/List.Sum([Liste])
und es wird eine Dezimalzahl ausgegeben, die den prozentualen Anteil widerspiegelt. Die 1 entspricht dabei 100%, kleinere Werte entsprechend weniger. Sie dürfen aber die Werte hier im Editor nicht als Prozent-Darstellung umformatieren, das würde später unweigerlich zu einem Fehler führen.

Fast fertig. Jetzt bedarf es nur noch eines einzigen Schritts, nämlich die Zuweisung der Typisierung A, B oder C. Dazu werden sie wiederum eine neue Spalte generieren, jetzt aber einmal im Dialog. Ein Klick auf Bedingte Spalte und tragen Sie bei Neuer Spaltenname den Text A-B-C ein. In der Zeile Wenn wählen Sie bei Spaltenname im Dropdown das Feld % (kumuliert), bei Operator die Zeile ist kleiner als, bei Wert tragen Sie 0,8 ein (hier wirklich mit dem Komma) und bei Ausgabe tragen Sie A ein. Weitere Werte erkennen sie in der folgenden Abbildung:

Komplett ausgefüllter Dialog "Bedingte Spalte"

Komplett ausgefüllter Dialog „Bedingte Spalte“

Falls Sie den Dialog meiden möchten, können Sie das ganze aber auch von vornherein als Benutzerdefinierte Spalte per Formel eingeben; ich wähle aus prinzipiellen Erwägungen stets diesen Weg, er scheint mir flexibler zu sein (das mag aber auch daran liegen, dass ich gerne programmiere):
if [#"% (kumuliert)"] <0.8 then "A" else if [#"% (kumuliert)"] <0.95 then "B" else "C"

Achten Sie darauf, dass hier die Dezimalzahlen den Punkt als Trenner zwischen Ganzzahl und Nachkommastellen verwenden. – Welche Vorgehensweise Sie auch immer wählen: In jedem Falle werden Sie entsprechend dem Umsatz der Gruppe oder des Produkts die Zuordnung zur entsprechenden Typisierung bekommen, was ja das Ziel der Aufgabenstellung ist.

Eventuell werden sie jetzt nach Ihren Wünschen eine Sortierung vornehmen. Soll die ursprüngliche Reihenfolge verwendet werden, dann sortieren sie die Spalte Index aufsteigend. Soll die Reihenfolge nach Umsatz angeordnet sein, werden sie die Spalte Umsatz vermutlich absteigend sortieren und last but not least bietet es sich natürlich auch noch an, nach der Typisierung/Gruppierung zu sortieren, obwohl das oft keinen Unterschied zur Sortierung nach Umsatz macht.

Bleibt noch das Große Aufräumen. Markieren Sie nacheinander die Spalten Produktgruppe, Umsatz und A-B-C, dann ein Rechtsklick in eine der markierten Überschriften und Andere Spalten entfernen. Nun noch Schließen & laden oder Schließen & laden in… und die Auswertung „steht“ in einem Excel Arbeitsblatt.

So weit der Kern der Aufgabe, die Basis. Wenn Sie vergleichen wollen, dann laden Sie diese Version von meinem Server herunter. Ich habe die Sortierung nach der ursprünglichen Reihenfolge vorgenommen. – Und selbstredend steht es Ihnen frei, diese Abfrage zu duplizieren und dort eine andere Sortierung vorzunehmen, welche eine differenzierte Sichtweise ermöglicht.

▲ nach oben …

Pivot-Auswertung

Ein wirklich hohes Maß an Flexibilität erreichen Sie, wenn Sie die bzw. eine von der Abfrage generierte Tabelle als Basis für eine PivotTable nutzen. Der folgende kleine Teil soll Sie etwas inspirieren, keine detaillierte Anleitung sein. Darum ist hier vieles nur stichwortartig angerissen … Und ausnahmsweise einmal gleich zu Beginn mein Ergebnis. 😀 

Um eine „saubere“ Datenbasis zu schaffen, öffnen Sie erforderlichenfalls die zuerst erstellte (und vielleicht auch einzige) Abfrage, also die nach A-B-C sortierte.

  • Erstellen Sie hiervon ein Duplikat, damit der erste Teil der Arbeit unverändert bleibt.
  • Löschen Sie den Schritt der letzten Sortierung (Sortierte Zeilen1)
  • Geben Sie dieser Abfrage einen „sprechenden“ Namen, beispielsweise Basis für Pivot.
  • Jetzt  Schließen & laden.

Sie haben nun in einem neuen Arbeitsblatt die Roh-Form der A-B-C Auswertung, die aber inhaltlich vollkommen OK ist:

Excel-Tabelle als Basis für eine Pivot Tabelle

Excel-Tabelle als Basis für eine Pivot Tabelle

Erforderlichenfalls klicken Sie im Menü-Register unterhalb Tabellentools auf den Eintrag Entwurf. Im Menüband erkennen sie links die Auswahl Mit PivotTable zusammenfassen; ein Klick darauf, und der Dialog PivotTable erstellen wird geöffnet. Da die Anzahl der Daten ja noch recht übersichtlich ist, wählen Sie als Ziel Vorhandenes Arbeitsblatt und beispielsweise die Zelle E10. Falls Sie Platz brauchen, können Sie nun das Seitenfenster mit den beiden (oder weiteren) Abfragen problemlos schließen; Sie werden sie für diese Aufgabe nicht mehr brauchen. Und falls Sie doch noch einmal zur Abfrage zurück wechseln wollen, Menü-Register Daten | Abrufen und transformieren | Abfragen anzeigen.

Ziehen Sie nun das Feld Produktgruppe in den Bereich Zeilen. Der Umsatz gehört in den Bereich Werte und A-B-C sollte wiederum in den Bereich Zeilen gezogen werden. Um ein höheres Maß an Komfort zu erreichen, können Sie nun noch einen Datenschnitt für die A-B-C – Gruppierung und einen weiteren für die Produktgruppe einrichten.

Als nächstes nehme ich einige grundlegende Einstellungen an der Pivot Tabelle vor. Über das Register Entwurf gelange ich in das Berichtslayout und dort wähle ich Im Tabellenformat anzeigen. Bei Gesamtergebnisse klicke ich auf die 1. Zeile Für Zeilen und Spalten deaktiviert, bei Teilergebnisse klicke ich auch auf die oberste Menü-Auswahl Teilergebnisse nicht anzeigen. Das sieht ja schon einmal recht ordentlich aus:

Die grob formatierte PivotTable

Die grob formatierte PivotTable

Wenige Kleinigkeiten stören mich noch. Das sind zum Beispiel die Schaltflächen mit dem Minuszeichen vor der Produktgruppe, die A-B-C Gruppierung sollte mittig sein und der Umsatz macht sich naturgemäß viel besser im Währungsformat. Zur Entfernung der Schaltflächen wählen Sie in den PivotTable-Tools den Punkt Analysieren und rechts in der Gruppe Anzeigen ein Klick auf das Symbol Schaltflächen +/-.

Die Gruppierung formatieren sie wie gewohnt über das Register Start, markieren die Daten und dann ein Klick auf das zentrieren-Symbol. Um die Umsätze im Währungsformat darzustellen, Rechtsklick in die Überschrift Summe von Umsatz, Wertfeldeinstellungen und im Dialog wählen Sie unten links die Schaltfläche Zahlenformat. Im folgenden Dialog wählen Sie das Währungsformat und bestätigen alle Einstellungen mit OK. Weitere Formatierungen können Sie natürlich noch vornehmen.

Last but not least lässt sich natürlich aus den Pivotdaten ein PivotChart erstellen. Filter, die sie in den Datenschnitten gesetzt haben, werden hier selbstredend und auch automatisch berücksichtigt.

Beispiel einer Pivot-Auswertung mit Pivot Chart

Beispiel einer Pivot-Auswertung mit Pivot Chart

In der realen Geschäftswelt wären das gewiss erheblich mehr Artikel und auch eine zusätzliche Spalte, wo eine Ober-Gruppierung angegeben ist. Da wären dann beispielsweise die Server, PCs und Laptops in einer Obergruppe, LAN, WLAN, DSL, Internet, … in einer anderen, etc. Das würde dann in einer PivotTable mehr Möglichkeiten bieten. Aber für heute soll es das erst einmal gewesen sein. 😆 

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Filtern & Sortieren, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.