Power Query, laufende Summe, ABC Analyse

Im Folgenden wird eine Methode beschrieben, wie man mit Excel Power Query eine
laufende Summe absolut, prozentual zum Gesamtergebnis, ABC Klassifizierung und somit
eine ABC Analyse durchführen kann.

Ausgangsbasis ist eine Tabelle mit 2 Spalten (Produkt, Wert)

Exkurs:

Excel Tabelle als Abfrage zur weiteren Bearbeitung in Power Query übernehmen



Schritt 1
Im Power Query Editor absteigend nach Spalte [Wert] sortieren

Schritt 2
benutzerdefinierte Spalte hinzufügen, [Custom]

= List.Buffer(Sortierung_Wert[Wert])

Jeder einzelne Wert der Spalte [Wert] wird in einem List Objekt gespeichert


Schritt 3
benutzerdefinierte Spalte [kumuliert] (laufende Summe) hinzufügen

=List.Sum(List.Select([Custom], (x)=>x>=[Wert]))

im jeweiligen Zeilenkontext wird dadurch die laufende Summe aller vorherigen Werte ermittelt

Schritt 4
benutzerdefinierte Spalte [Prozent_kumuliert] hinzufügen

= [kumuliert]/List.Sum([Custom])

Schritt 5
benutzerdefinierte Spalte [Klasse] hinzufügen,

=if [Prozent_kumuliert] < 0.8 then "A" else
    if [Prozent_kumuliert] < 0.95 then "B" else
"C"



siehe auch Anteil an Gesamtwert berechnen

Hinweis:
eine laufende Summe kann auch mit der Kombination aus
List.Sum(List.Range()) ermittelt werden, siehe Funktionen Language M

1    Index-Spalte beginnend mit 1 hinzufügen
2    benutzerdefinierte Spalte hinzufügen := List.Sum(List.Range(#"Geänderter Typ"[Wert],0,[Index]))

Kommentare

  1. Unknown9. März 2018 um 12:29

    Hallo Svens,

    sehr interessanter Artikel, der mir auch schon sehr weitergeholfen hat. Aber aktuell stehen ich vor dem Problem, dass ich mehrere Kriterien benötige. Das könnte zB. so sein Betrieb/Warengruppe/Artikel.
    Hast du eine Idee, wie ich das am besten umsetze. Bin echt ratlos

    Danke!!!!
    Sebastian

    AntwortenLöschen
  2. Böns9. März 2018 um 20:39

    Deine Anforderung ist etwas Komplexer, kann aber mit Power Pivot / DAX gelöst werden, siehe folgenden Artikel http://blog.gbrueckl.at/2013/01/dynamic-abc-analysis-in-powerpivot-using-dax/

    AntwortenLöschen
    Antworten
    1. Böns9. März 2018 um 21:52

      Ich habe mal eine quick & dirty eine Beispiel Arbeitsmappe erstellt, welche Deine Anforderungen abdecken könnte. Diese findest Du in folgendem Blog Beitrag als Download weiter unten http://svens-excel-welt.blogspot.de/2015/04/dynamische-abc-analyse-dax-allexcept.html

      Löschen
  3. Unknown10. März 2018 um 02:47

    Wow, vielen dank für die schnelle Hilfe. Ich hab leider große Datenmengen > 200T Zeilen, dabei bricht Powerpivot nach ca. 1h Berechnung mit hinweis auf den Speicher ab. Dabei sind 16GB Ram und 64 bit installiert. Daher wollte ich die Berechnung in die Powerquery verlegen.

    Vielen Dank für deine schnelle Hilfe!!!

    Grüße
    Sebastian

    AntwortenLöschen
  4. Böns11. März 2018 um 00:27

    verstehe, ein Ansatz könnte sein, die Daten komplett in eine Abfrage mit Power Query zu laden, dann die Struktur um die erforderlichen Berechnungen anzureichern. Anschließend kann man neue Abfragen erstellen, welche auf die erste verweisen (rechte Maustaste auf Abfrage -> Verweis. In diesen (auf erste Abfrage verweisende Abfragten) kann dann über Filterung der jeweiligen Spalten / Merkmale eine gewünschte Analyse-Sicht manifestiert werden. Jede Abfrage repräsentiert dann quasi eine konkrete Anforderung an die ABC Analyse

    AntwortenLöschen
  5. Unknown11. März 2018 um 05:04

    ... ich hab die Lösung, manchmal ist es echt verrückt an welchen Kleinigkeiten etwas scheitern kann. Ich hab einfach von Dezimal auf Ganzzahl umgestellt und schon ist der Speicherhunger deutlich kleiner. Wahnsinn das waren mindestens 5 Nächte und dann so eine Kleinigkeit.

    Interessanter Ansatz, das teste ich trotzdem aus.

    Danke dir für die schnelle und umfangreiche Hilfe!

    Sebastian

    AntwortenLöschen

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Dropdown Liste mit Mehrfachauswahl

Bild
Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden (Daten -> Datentools -> Datenüberprüfung). Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden weiterführende Informationen siehe hier Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen). --- SCHNIPP -- Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String '** Errorhandling On Error GoTo Errorhandling '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&
Mehr anzeigen

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()
Mehr anzeigen

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{"
Mehr anzeigen