PQ: Zeilenweiser prozentualer Anteil einer Spalte

In Excel ist es ja bekanntlich eine „Fingerübung“, die Summe aller Werte einer Spalte oder eines definierten Bereichs einer Spalte zu berechnen. Power Query hingegen ist darauf ausgelegt, dass Formeln normalerweise nur auf die Gesamtheit aller Zeilen, nicht jedoch auf Spalten wirken. Aus diesem Grunde ist es auch nicht direkt möglich, die Summe in der Spalte direkt in der dargestellten Auflistung zu berechnen und diese dann auch für weitere Berechnungen zu verwenden. Für die Ausrechnung, wie viel Prozent beispielsweise jeder einzelnen Monat eines Jahres vom Gesamt-Jahresumsatz ausmacht ist aber diese Spaltensumme erforderlich, wenn die Monate beispielsweise in Spalte A und die Umsätze unterschiedlicher Produkte in den Folgespalten stehen.

Dennoch ist es in bzw. mit Power Query möglich, zum Ziel zu gelangen. Es gibt Wege die auch für Anwender, die nur gelegentlich Power Query einsetzen, gangbar sind. Das ist gewiss ab und zu der eine oder andere Schritt mehr, aber die einzelnen Schritte sind durchaus nachvollziehbar und auch die Geschwindigkeit ist zumutbar. Solch eine Lösung in diesem Beitrag vorgestellt.

Es gibt aber auch einen deutlich eleganteren Weg, der „kurz und knackig“ das Ergebnis liefert. Sie können ihn durch Recherche im Internet, insbesondere auf englischsprachigen Seiten finden. Prinzipiell ist die Suche und das damit verbundene herumprobieren nach der „besten Lösung“ nicht so ganz trivial und kostet einiges an Zeit, verbunden mit einiger Probiererei. Wenn Sie das vermeiden wollen, zeige ich Ihnen anhand eines Beispiels die Lösung dieses Problems. Schreiben Sie mir eine Mail mit dem Betreff „PQ-Prozentuale Aufteilung von Spaltenwerten“ und wenn Sie mir beispielsweise über den Donate-Button (oben rechts auf jeder Seite) den Betrag von 5,00 € mit dem Verwendungszweck „PQ-Spalte-%-Werte“ überweisen erhalten Sie per Mail den Link und auch das erforderliche Passwort, um die Seite mit Lösung zu öffnen. Kontaktieren Sie mich aber auch gerne per Mail oder auch telefonisch, falls Sie Fragen dazu haben. In dem Rahmen ein wichtiger Hinweis: Die vorgestelle Lösung bezieht sich ausschließlich auf die Muster-Datei. Bei eigenen Daten werden sie unter Umständen kleinere Anpassungen in der Namensgebung vornehmen müssen. Hier ist etwas Erfahrung mit Power Query von Vorteil, aber auch mit etwas Experimentierfreude kommen Sie gewiss zum Ziel. 😉 

[Donate-Hinweis $Loesung=”{$Loesung}”]

Die (kostenlose) und gut funktionierende Basislösung hier im Blog

Prozentualen Anteil jeder Zeile einer Spalte mit Power Query berechnen

Laden Sie zu Beginn diese Datei mit den Jahres-Umsätzen der Bäckerei Kleinbrot herunter. Markieren Sie den Bereich A4:G16, also die reinen Umsatzdaten mit den Überschriften und den Monatsnamen. StrgL oder StrgT um aus den markierten Daten eine Intelligente Tabelle / Liste zu generieren. Dieser Bereich stellt sich nun so dar:

Der als Tabelle formatierte Bereich wird in Power Query importiert

Der als Tabelle formatierte Bereich wird in Power Query importiert

Sie erkennen, dass in A4 automatisch eine Überschrift eingesetzt worden ist: Spalte1. Ändern Sie hier schon diese Zelle auf die Überschrift Monat, dann ersparen Sie sich das im Power Query-Editor. – Falls Sie gerne durch Excel oder andere Programme berechnete Ergebnisse noch einmal kontrollieren, dann drucken Sie bitte den Bereich A4:H17 aus, wobei insbesondere die Spalte Gesamt wichtig sein wird. Dieser Gesamt-Bereich ist auch in der obigen Abbildung dargestellt.

Achten Sie darauf, dass der Cursor innerhalb der Daten der Liste steht und importieren Sie nun die Umsätze über den Weg Daten | Aus Tabelle (Versionen 2016/365) bzw. Von Tabelle in Excel 2010/13. Nach dem Import in den Abfrage-Editor sieht das ganze so aus:

Die im Abfrage-Editor importierten, noch unveränderten Daten

Die im Abfrage-Editor importierten, noch unveränderten Daten

▲ nach oben …

Zeilen-Summe berechnen

Im nächsten Schritt werden Sie eine neue Spalte generieren, wo jeweils die Summe aller Produkte des Monats berechnet wird, also die ursprünglichen Werte der Spalte H. Dazu aktivieren Sie erst einmal das Menü Spalte hinzufügen. Ich zeige Ihnen jetzt die klassische Möglichkeite auf, die durchaus zielführend ist: Klick auf Benutzerdefinierte Spalte und dann fügen Sie beispielsweise durch Doppelklick jedes Feld (aus dem kleinen, rechten Fenster) oder durch einen Klick auf die Schaltfläche << Einfügen in den Bereich Benutzerdefinierte Spaltenformel ein. Zwischen den einzelnen Feldern schreiben Sie jeweils ein Plus-Symbol +, da Sie ja eine Addition vornehmen wollen. Als Überschrift verwenden Sie beispielsweise Gesamt-Umsatz.

▲ nach oben …

Monate prozentual bewerten

Im Endeffekt soll ja nur für den jeweiligen Monat in der getätigte Umsatz aller Waren prozentual bewertet werden. Darum können auch die Spalten der einzelnen Produkte bzw. Produktgruppen gelöscht werden. Markieren Sie dazu in die Überschrift der Spalte Monat, Strg und dann ein Klick in Gesamt-Umsatz. Rechtsklick in eine der beiden markierten Überschriften und Andere Spalten entfernen.

So weit, so gut. Und es gibt durchaus die Möglichkeit, mit Bordmitteln des Power Query an die Summe einer Spalte zu berechnen und als Einzelwert in eine neue Tabelle zu speichern. Gehen Sie dazu folgenden Weg:

  • Erstellen Sie zu Beginn ein Duplikat dieser Tabelle. Das geht über das Menü Start | Verwalten und dann entweder Duplizieren oder Verweis; welche der beiden Optionen sie hier verwenden, obliegt Ihrer Entscheidung.
  • Benennen Sie sinnvollerweise dieser neue Tabelle um in Umsätze.
  • Markieren Sie einen Wert oder die ganze Spalte Gesamt-Umsatz.
  • Wählen Sie das Menü Transformieren, Gruppe Zahlenspalte | Statistiken | Summe.
  • Stören Sie sich nicht und dem durch die 4 Nachkommastellen Bedingten Rundungsfehler sondern klicken Sie auf das Symbol Zu Tabelle.
  • Vergleichen Sie gerne noch einmal das Ergebnis mit de.m in Pläne Excel berechneten Wert.
  • Ändern Sie die Überschrift eventuell in Gesamt-Umsatz um.
  • Spalte hinzufügen < Benutzerdefinierte Spalte und geben Sie als Spaltenformel einfach nur eine 1 ein. Den Spaltennamen können Sie so belassen.
  • Menü Datei | Schließen & laden in… | Nur Verbindung erstellen und Laden.

Vielleicht ist Ihnen ja die 1 in der eben erstellten Benutzerdefinierte Spalte ein Rätsel. Zugegeben, Sie hätten auch jede beliebige andere Zahl oder auch Zeichen verwenden können. Diese Spalte gilt praktisch als Index, als Link zum verknüpfen mit der Tabelle, wo die Monate und die Gesamtumsätze der Produkte drinstehen.

Und da in dieser Tabelle eine vergleichbare Spalte fehlt, muss diese auch hier erstellt werden. Wechseln Sie also zu dieser Tabelle mit den Monats-Umsätzen und fügen Sie auch dort eine Benutzerdefinierte Spalte ein. Als Formel geben Sie exakt den gleichen Wert wie gerade eben an; wenn Sie also mein Vorschlag gefolgt sind, werden sie auch hier die 1 eintragen. Damit sind die Vorbereitungen abgeschlossen. – Ach ja, wenn Sie schon beim Umbenennen sind: Geben Sie dieser Abfrage den Namen Monate. Denn dann können Sie die beiden Abfragen besser auseinanderhalten. Auch hier können Sie es bei der Überschrift Benutzerdefiniert belassen oder einen beliebigen Titel verwenden. Weiter geht’s so:

  • Menü Start | Kombinieren | Abfragen zusammenführen und sie erkennen, dass die Abfrage Monate im oberen Bereich bereits eingefügt ist. Ein Klick auf das Dropdown darunter und wählen Sie die Abfrage mit dem Namen Umsätze.
  • Markieren Sie im oberen als auch im unteren Fenster einen Wert, eine einzelne Zelle in der Spalte mit der 1.
  • Belassen Sie es bei Join-Art bei der vorgegebenen Auswahl Linker äußerer Join.
  • Schließen Sie das Fenster mit OK.
  • Klicken Sie in der neu geschaffenen Spalte Umsätze auf den Doppelpfeil Doppelpfeil in der Überschrift.
  • Achten Sie darauf, dass das einzige Häkchen bei Umsatz steht und schließen dann das Fenster.
  • Entfernen Sie die Hilfsspalte mit dem Indexwert (1).
  • Spalte hinzufügen | Benutzerdefinierte Spalte.
  • Vergeben sie als neuen Spaltennamen beispielsweise Anteil in Prozent.
  • Als Spaltenformel geben Sie in die Berechnung = [#"Gesamt-Umsatz"]/[Umsatz] ein und Schließen danach dieses Fenster.
  • Formatieren Sie die Spalte Anteil in Prozent, indem Sie den Datentyp der eben generierten Spalte auf Als Prozent ändern.
  • Löschen Sie die Spalte Umsatz, wo ja ausschließlich der Jahres-Umsatz enthalten ist.
  • Schließen & laden.
  • In der neu erstellten Tabelle (in Excel) ändern Sie bitte die noch in das Zahlenformat auf Prozent und damit ist das Ziel erreicht.
Dieser Beitrag wurde unter Allgemein, Verschiedenes, Join-Art, Power Query, PQ-Formeln (Sprache M), Spalten bearbeiten, {Liste} abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.