ABC-Analyse mit Power Query

  Wis­sens­stand: Lev­el 2 ⇒ Solides Basiswis­sen in PQ, etwas Erfahrung   

Die Aufgabe

Was das Ergeb­nis ein­er ABC-Analyse aus­sagt wer­den Sie wis­sen, wenn Sie eine solche erstellen wollen (oder sollen). Diese Auswer­tung ist eines der Lieblings-„Spielzeuge” der Con­troller. 😎 Wie eine ABC-Analyse mit Excel-Mit­teln zu erstellen ist, kön­nen Sie zu genüge im Inter­net nach­le­sen. Den­noch biete ich Ihnen hier als Ver­gle­ich eine nicht kom­men­tierte Lösung auf der Basis Plain Excel an. Diese Vorge­hensweise, die Tech­nik ist recht ein­fach und für viele Anwender/innen auch trans­par­ent. – Ein gewiss­es Maß an Nachteil kann in man­gel­nder Flex­i­bil­ität in Sachen Darstel­lung gese­hen wer­den. So wird die Anord­nung, di Sortierung grund­sät­zlich so sein, wie in dem Arbeits­blatt dargestellt. Den­noch ste­he ich zum Leit­satz: „Form fol­lows func­tion” …

Wenn Sie ‑aus welchen Grün­den auch immer- solch eine Grup­pen­bil­dung in Pow­er Query vornehmen wollen, ist das ganze schon etwas kom­plex­er. Ich habe die Auf­gabe noch etwas verkom­pliziert, indem ich die (optionale) Forderun­gen aufgestellt habe, dass die ursprüngliche Rei­hen­folge der Artikel bzw. Artikel­grup­pen erhal­ten bleibt oder die Liste auch nach Umsätzen von hoch nach niedrig bzw. nicht vorhan­den (also 0 €) sortiert wird.

Begin­nen Sie damit, diese Datei mit den Roh-Dat­en in Excel zu laden. Es sind nicht allzu viele Posi­tio­nen aber für das Prinzip reicht es vol­lkom­men aus und so ist es auch deut­lich über­sichtlich­er. Das Ergeb­nis dieser Analyse beruht auf der 80–20-Regel, die immer wieder für eine Bew­er­tung der Pri­or­ität herange­zo­gen wird. Ganz grob aus­ge­drückt machen (beispiel­sweise) die Top 20% der Pro­duk­te 80% des Umsatzes aus. Die Pro­duk­te mit den höch­sten Umsätzen gehören zur Klasse oder Gruppe A, ein weit­er­er Teil zur Gruppe B und die restlichen wer­den C zuge­ord­net. Wegen der gerin­gen Anzahl der Posi­tio­nen sind diese Prozen­twerte nicht wörtlich zu nehmen aber eine Richtlin­ie.

Nach dem Import der Quell­dat­en in den Pow­er Query-Edi­tor stellt sich das so dar:

Die Roh-Dat­en direkt nach dem Import

Die Rei­hen­folge der Pro­duk­t­grup­pen ist schein­bar willkür­lich, die Umsätze sind keineswegs geord­net. Bere­its an dieser Stelle wer­den Sie sich entschei­den, ob nach Abschluss der Auswer­tung die bish­erige Rei­hen­folge erhal­ten bleiben soll oder ob wahlweise auch nach A‑B-C sortiert wer­den soll. Ich entschei­de mich, dass bei­de Möglichkeit­en genutzt wer­den kön­nen. Darum wer­den Sie im ersten Schritt eine Index-Spalte ein­fü­gen:

• Menü/Register Spalte zufü­gen
• Im Menüband Indexs­palte anklick­en.

Dadurch wird eine neue Spalte mit einem 0‑basierten Index erzeugt. Diese kann später zur Sortierung auf die ursprüngliche (also jet­zige) Rei­hen­folge genutzt wer­den. Da (später) vor dem Schließen & laden alle nicht rel­e­van­ten Spal­ten ent­fer­nt wer­den, kann diese Indexs­palte auch nicht schaden. 😉 

Im näch­sten Schritt ist es wichtig, alle leeren Felder der Spalte Umsatz mit einem numerischen Wert zu füllen. Bei diesen weni­gen Zeilen wird Ihnen sofort auf­fall­en, dass bei Serv­er und Leih-Ser­vice der Wert null einge­tra­gen ist, was ja für eine (wirk­lich) leere Zelle ste­ht. Das darf nicht so bleiben. Den fol­gen­den Schritt kön­nen Sie auch bei großen Daten­men­gen anwen­den, selb­st wenn nicht ersichtlich ist, ob irgend­wo ein leeres Feld in der Umsatzs­palte enthal­ten ist:

• Recht­sklick in die Über­schrift der Spalte Umsatz.
• Im Kon­textmenü wählen Sie Werte erset­zen…
• Geben Sie im Dia­log bei Zu suchen­der Wert den Text null (in Klein­schrei­bung) ein.
• Bei Erset­zen durch schreiben Sie die Zahl 0.

Leere Zellen der Umsatz-Spalte durch die Zahl 0 erset­zen

• Bestäti­gen Sie mit OK.
• Sortieren Sie die Spalte Umsatz nun noch auf­steigend.

 Der fol­gende Schritt dient dazu, einen Hin­ter­grund-Prozess zu ini­ti­ieren. Sie erzeu­gen wiederum eine Benutzerdefinierte Spalte und geben dieser beispiel­sweise die Über­schrift Liste. Als Formel tra­gen Sie in exakt dieser Schreib­weise ein:
List.Buffer(#"Sortierte Zeilen"[Umsatz])

In jed­er Zelle dieser Hil­f­ss­palte ste­ht das Wort List. Im Gegen­satz zu vie­len anderen Gele­gen­heit­en wer­den Sie es dabei belassen und den Dop­pelpfeil  in der Über­schrift nicht für die Erweiterung der Werte nutzen. Die Spalte bleibt genau so, wie sie ist.

Bei der Gele­gen­heit eine Anmerkung: Ich zeige Ihnen die Formeln hier auf, erk­läre sie aber nicht weit­er. Dafür hat Microsoft eine pri­ma Web­site mit guten und aus­führlichen Erk­lärun­gen zu den Funk­tio­nen der Sprache M. In diesem Beitrag geht es um die Lösung, weniger um die Hin­ter­gründe und es sollen keine tief­er­en Ken­nt­nisse in Sachen Pro­gram­mierung mit der Abfrage­sprache ver­mit­telt wer­den.

Falls Sie die A‑B-C Analyse aus Excel ken­nen wer­den sie gewiss wis­sen, dass eine auf­steigende Sortierung der Umsätze die Basis dafür ist, dass in jed­er Zeile die kumulierte Summe der Umsätze gebildet wer­den muss. Und das ist auch der näch­ste Schritt hier im Edi­tor. Wiederum eine neue Benutzerdefinierte Spalte, die Über­schrift kann zum Beispiel lfd. Summe (kumuliert) sein; sie kön­nen aber auch gerne einen anderen sprechen­den Namen wählen. Als Benutzerdefinierte Spal­tenformel geben Sie in exakt dieser Schreib­weise ein:
List.Sum(List.Select([Liste], (x)=>x>=[Umsatz]))
wobei sie die Spal­tenna­men [Liste] und [Umsatz] vorzugsweise durch einen Dop­pelk­lick auf den entsprechen­den Spal­tenna­men in der recht­en Textbox übernehmen.

Nach einem OK wer­den Sie erken­nen, dass die berech­neten Werte zwar stim­men aber in umgekehrter Rei­hen­folge in den Zeilen ste­hen. Der höch­ste kumulierte Umsatz ste­ht in Zeile 1, der niedrig­ste in der let­zten Zeile. Und das ist kor­rekt so.

Aus diesen berech­neten Werten muss nun je Pro­duk­t­gruppe der prozen­tuale Anteil in Bezug zur Summe aller Umsätze berech­net wer­den. Dazu erstellen Sie (natür­lich) eine weit­ere Benutzerdefinierte Spalte. Als Über­schrift ver­wende ich % (kumuliert), die Formel sieht so aus:
[#"lfd. Summe (kumuliert)"]/List.Sum([Liste])
und es wird eine Dez­i­malzahl aus­gegeben, die den prozen­tualen Anteil wider­spiegelt. Die 1 entspricht dabei 100%, kleinere Werte entsprechend weniger. Sie dür­fen aber die Werte hier im Edi­tor nicht als Prozent-Darstel­lung umfor­matieren, das würde später unweiger­lich zu einem Fehler führen.

Fast fer­tig. Jet­zt bedarf es nur noch eines einzi­gen Schritts, näm­lich die Zuweisung der Typ­isierung A, B oder C. Dazu wer­den sie wiederum eine neue Spalte gener­ieren, jet­zt aber ein­mal im Dia­log. Ein Klick auf Bed­ingte Spalte und tra­gen Sie bei Neuer Spal­tenname den Text A‑B-C ein. In der Zeile Wenn wählen Sie bei Spal­tenname im Drop­down das Feld % (kumuliert), bei Oper­a­tor die Zeile ist klein­er als, bei Wert tra­gen Sie 0,8 ein (hier wirk­lich mit dem Kom­ma) und bei Aus­gabe tra­gen Sie A ein. Weit­ere Werte erken­nen sie in der fol­gen­den Abbil­dung:

Kom­plett aus­ge­füll­ter Dia­log „Bed­ingte Spalte”

Falls Sie den Dia­log mei­den möcht­en, kön­nen Sie das ganze aber auch von vorn­here­in als Benutzerdefinierte Spalte per Formel eingeben; ich wäh­le aus prinzip­iellen Erwä­gun­gen stets diesen Weg, er scheint mir flex­i­bler zu sein (das mag aber auch daran liegen, dass ich gerne pro­gram­miere):
if [#"% (kumuliert)"] <0.8 then "A" else if [#"% (kumuliert)"] <0.95 then "B" else "C"

Acht­en Sie darauf, dass hier die Dez­i­malzahlen den Punkt als Tren­ner zwis­chen Ganz­zahl und Nachkom­mas­tellen ver­wen­den. – Welche Vorge­hensweise Sie auch immer wählen: In jedem Falle wer­den Sie entsprechend dem Umsatz der Gruppe oder des Pro­duk­ts die Zuord­nung zur entsprechen­den Typ­isierung bekom­men, was ja das Ziel der Auf­gaben­stel­lung ist.

Eventuell wer­den sie jet­zt nach Ihren Wün­schen eine Sortierung vornehmen. Soll die ursprüngliche Rei­hen­folge ver­wen­det wer­den, dann sortieren sie die Spalte Index auf­steigend. Soll die Rei­hen­folge nach Umsatz ange­ord­net sein, wer­den sie die Spalte Umsatz ver­mut­lich absteigend sortieren und last but not least bietet es sich natür­lich auch noch an, nach der Typisierung/Gruppierung zu sortieren, obwohl das oft keinen Unter­schied zur Sortierung nach Umsatz macht.

Bleibt noch das Große Aufräu­men. Markieren Sie nacheinan­der die Spal­ten Pro­duk­t­gruppe, Umsatz und A‑B-C, dann ein Recht­sklick in eine der markierten Über­schriften und Andere Spal­ten ent­fer­nen. Nun noch Schließen & laden oder Schließen & laden in… und die Auswer­tung „ste­ht” in einem Excel Arbeits­blatt.

So weit der Kern der Auf­gabe, die Basis. Wenn Sie ver­gle­ichen wollen, dann laden Sie diese Ver­sion von meinem Serv­er herunter. Ich habe die Sortierung nach der ursprünglichen Rei­hen­folge vorgenom­men. – Und selb­stre­dend ste­ht es Ihnen frei, diese Abfrage zu duplizieren und dort eine andere Sortierung vorzunehmen, welche eine dif­feren­zierte Sichtweise ermöglicht.

▲ nach oben …

Pivot-Auswertung

Ein wirk­lich hohes Maß an Flex­i­bil­ität erre­ichen Sie, wenn Sie die bzw. eine von der Abfrage gener­ierte Tabelle als Basis für eine Piv­ot­Table nutzen. Der fol­gende kleine Teil soll Sie etwas inspiri­eren, keine detail­lierte Anleitung sein. Darum ist hier vieles nur stich­wor­tar­tig angeris­sen … Und aus­nahm­sweise ein­mal gle­ich zu Beginn mein Ergeb­nis. 😀 

Um eine „saubere” Daten­ba­sis zu schaf­fen, öff­nen Sie erforder­lichen­falls die zuerst erstellte (und vielle­icht auch einzige) Abfrage, also die nach A‑B-C sortierte.

• Erstellen Sie hier­von ein Dup­likat, damit der erste Teil der Arbeit unverän­dert bleibt.
• Löschen Sie den Schritt der let­zten Sortierung (Sortierte Zeilen1)
• Geben Sie dieser Abfrage einen „sprechen­den” Namen, beispiel­sweise Basis für Piv­ot.
• Jet­zt  Schließen & laden.

Sie haben nun in einem neuen Arbeits­blatt die Roh-Form der A‑B-C Auswer­tung, die aber inhaltlich vol­lkom­men OK ist:

Als näch­stes nehme ich einige grundle­gende Ein­stel­lun­gen an der Piv­ot Tabelle vor. Über das Reg­is­ter Entwurf gelange ich in das Bericht­slay­out und dort wäh­le ich Im Tabel­len­for­mat anzeigen. Bei Gesamtergeb­nisse klicke ich auf die 1. Zeile Für Zeilen und Spal­ten deak­tiviert, bei Tei­l­ergeb­nisse klicke ich auch auf die ober­ste Menü-Auswahl Tei­l­ergeb­nisse nicht anzeigen. Das sieht ja schon ein­mal recht ordentlich aus:

Die grob for­matierte Piv­ot­Table

Wenige Kleinigkeit­en stören mich noch. Das sind zum Beispiel die Schalt­flächen mit dem Minusze­ichen vor der Pro­duk­t­gruppe, die A‑B-C Grup­pierung sollte mit­tig sein und der Umsatz macht sich naturgemäß viel bess­er im Währungs­for­mat. Zur Ent­fer­nung der Schalt­flächen wählen Sie in den Piv­ot­Table-Tools den Punkt Analysieren und rechts in der Gruppe Anzeigen ein Klick auf das Sym­bol Schalt­flächen +/-.

Die Grup­pierung for­matieren sie wie gewohnt über das Reg­is­ter Start, markieren die Dat­en und dann ein Klick auf das zen­tri­eren-Sym­bol. Um die Umsätze im Währungs­for­mat darzustellen, Recht­sklick in die Über­schrift Summe von Umsatz, Wert­felde­in­stel­lun­gen und im Dia­log wählen Sie unten links die Schalt­fläche Zahlen­for­mat. Im fol­gen­den Dia­log wählen Sie das Währungs­for­mat und bestäti­gen alle Ein­stel­lun­gen mit OK. Weit­ere For­matierun­gen kön­nen Sie natür­lich noch vornehmen.

Last but not least lässt sich natür­lich aus den Piv­ot­dat­en ein Piv­otChart erstellen. Fil­ter, die sie in den Daten­schnit­ten geset­zt haben, wer­den hier selb­stre­dend und auch automa­tisch berück­sichtigt.

Beispiel ein­er Piv­ot-Auswer­tung mit Piv­ot Chart

In der realen Geschäftswelt wären das gewiss erhe­blich mehr Artikel und auch eine zusät­zliche Spalte, wo eine Ober-Grup­pierung angegeben ist. Da wären dann beispiel­sweise die Serv­er, PCs und Lap­tops in ein­er Ober­gruppe, LAN, WLAN, DSL, Inter­net, … in ein­er anderen, etc. Das würde dann in ein­er Piv­ot­Table mehr Möglichkeit­en bieten. Aber für heute soll es das erst ein­mal gewe­sen sein. 😆 

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)