In Excel ist es ja bekan­ntlich eine „Fin­gerübung”, die Summe aller Werte ein­er Spalte oder eines definierten Bere­ichs ein­er Spalte zu berech­nen. Pow­er Query hinge­gen ist darauf aus­gelegt, dass Formeln nor­maler­weise nur auf die Gesamtheit aller Zeilen, nicht jedoch auf Spal­ten wirken. Aus diesem Grunde ist es auch nicht direkt möglich, die Summe in der Spalte direkt in der dargestell­ten Auflis­tung zu berech­nen und diese dann auch für weit­ere Berech­nun­gen zu ver­wen­den. Für die Aus­rech­nung, wie viel Prozent beispiel­sweise jed­er einzel­nen Monat eines Jahres vom Gesamt-Jahre­sum­satz aus­macht ist aber diese Spal­tensumme erforder­lich, wenn die Monate beispiel­sweise in Spalte A und die Umsätze unter­schiedlich­er Pro­duk­te in den Folges­pal­ten ste­hen.

Den­noch ist es in bzw. mit Pow­er Query möglich, zum Ziel zu gelan­gen. Es gibt Wege die auch für Anwen­der, die nur gele­gentlich Pow­er Query ein­set­zen, gang­bar sind. Das ist gewiss ab und zu der eine oder andere Schritt mehr, aber die einzel­nen Schritte sind dur­chaus nachvol­lziehbar und auch die Geschwindigkeit ist zumut­bar. Solch eine Lösung in diesem Beitrag vorgestellt.

Es gibt aber auch einen deut­lich ele­gan­teren Weg, der „kurz und knack­ig” das Ergeb­nis liefert. Sie kön­nen ihn durch Recherche im Inter­net, ins­beson­dere auf englis­chsprachi­gen Seit­en find­en. Prinzip­iell ist die Suche und das damit ver­bun­dene herumpro­bieren nach der „besten Lösung” nicht so ganz triv­ial und kostet einiges an Zeit, ver­bun­den mit einiger Pro­bier­erei. Wenn Sie das ver­mei­den wollen, zeige ich Ihnen anhand eines Beispiels die Lösung dieses Prob­lems. Schreiben Sie mir eine Mail mit dem Betr­e­ff „PQ-Prozen­tuale Aufteilung von Spal­tenwerten” und wenn Sie mir beispiel­sweise über den Donate-But­ton (oben rechts auf jed­er Seite) den Betrag von 5,00 € mit dem Ver­wen­dungszweck „PQ-Spalte-%-Werte” über­weisen erhal­ten Sie per Mail den Link und auch das erforder­liche Pass­wort, um die Seite mit Lösung zu öff­nen. Kon­tak­tieren Sie mich aber auch gerne per Mail oder auch tele­fonisch, falls Sie Fra­gen dazu haben. In dem Rah­men ein wichtiger Hin­weis: Die vorgestelle Lösung bezieht sich auss­chließlich auf die Muster-Datei. Bei eige­nen Dat­en wer­den sie unter Umstän­den kleinere Anpas­sun­gen in der Namensge­bung vornehmen müssen. Hier ist etwas Erfahrung mit Pow­er Query von Vorteil, aber auch mit etwas Exper­i­men­tier­freude kom­men Sie gewiss zum Ziel. 😉 

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äck­erei Klein­brot herunter. Markieren Sie den Bere­ich A4:G16, also die reinen Umsatz­dat­en mit den Über­schriften und den Monat­sna­men. StrgL oder StrgT um aus den markierten Dat­en eine Intel­li­gente Tabelle / Liste zu gener­ieren. Dieser Bere­ich stellt sich nun so dar:

Der als Tabelle for­matierte Bere­ich wird in Pow­er Query importiert

Sie erken­nen, dass in A4 automa­tisch eine Über­schrift einge­set­zt wor­den ist: Spalte1. Ändern Sie hier schon diese Zelle auf die Über­schrift Monat, dann ers­paren Sie sich das im Pow­er Query-Edi­tor. – Falls Sie gerne durch Excel oder andere Pro­gramme berech­nete Ergeb­nisse noch ein­mal kon­trol­lieren, dann druck­en Sie bitte den Bere­ich A4:H17 aus, wobei ins­beson­dere die Spalte Gesamt wichtig sein wird. Dieser Gesamt-Bere­ich ist auch in der obi­gen Abbil­dung dargestellt.

Acht­en Sie darauf, dass der Cur­sor inner­halb der Dat­en der Liste ste­ht und importieren Sie nun die Umsätze über den Weg Dat­en | Aus Tabelle (Ver­sio­nen ²⁰¹⁶⁄₃₆₅) bzw. Von Tabelle in Excel ²⁰¹⁰⁄₁₃. Nach dem Import in den Abfrage-Edi­tor sieht das ganze so aus:

Die im Abfrage-Edi­tor importierten, noch unverän­derten Dat­en

▲ nach oben …

Zeilen-Summe berechnen

Im näch­sten Schritt wer­den Sie eine neue Spalte gener­ieren, wo jew­eils die Summe aller Pro­duk­te des Monats berech­net wird, also die ursprünglichen Werte der Spalte H. Dazu aktivieren Sie erst ein­mal das Menü Spalte hinzufü­gen. Ich zeige Ihnen jet­zt die klas­sis­che Möglichkeite auf, die dur­chaus zielführend ist: Klick auf Benutzerdefinierte Spalte und dann fügen Sie beispiel­sweise durch Dop­pelk­lick jedes Feld (aus dem kleinen, recht­en Fen­ster) oder durch einen Klick auf die Schalt­fläche « Ein­fü­gen in den Bere­ich Benutzerdefinierte Spal­tenformel ein. Zwis­chen den einzel­nen Feldern schreiben Sie jew­eils ein Plus-Sym­bol +, da Sie ja eine Addi­tion vornehmen wollen. Als Über­schrift ver­wen­den Sie beispiel­sweise Gesamt-Umsatz.

▲ nach oben …

Monate prozentual bewerten

Im End­ef­fekt soll ja nur für den jew­eili­gen Monat in der getätigte Umsatz aller Waren prozen­tu­al bew­ertet wer­den. Darum kön­nen auch die Spal­ten der einzel­nen Pro­duk­te bzw. Pro­duk­t­grup­pen gelöscht wer­den. Markieren Sie dazu in die Über­schrift der Spalte Monat, Strg und dann ein Klick in Gesamt-Umsatz. Recht­sklick in eine der bei­den markierten Über­schriften und Andere Spal­ten ent­fer­nen.

So weit, so gut. Und es gibt dur­chaus die Möglichkeit, mit Bor­d­mit­teln des Pow­er Query an die Summe ein­er Spalte zu berech­nen und als Einzel­w­ert in eine neue Tabelle zu spe­ich­ern. Gehen Sie dazu fol­gen­den Weg:

• Erstellen Sie zu Beginn ein Dup­likat dieser Tabelle. Das geht über das Menü Start | Ver­wal­ten und dann entwed­er Duplizieren oder Ver­weis; welche der bei­den Optio­nen sie hier ver­wen­den, obliegt Ihrer Entschei­dung.
• Benen­nen Sie sin­nvoller­weise dieser neue Tabelle um in Umsätze.
• Markieren Sie einen Wert oder die ganze Spalte Gesamt-Umsatz.
• Wählen Sie das Menü Trans­formieren, Gruppe Zahlenspalte | Sta­tis­tiken | Summe.
• Stören Sie sich nicht und dem durch die 4 Nachkom­mas­tellen Bed­ingten Run­dungs­fehler son­dern Klick­en Sie auf das Sym­bol Zu Tabelle.
• Ver­gle­ichen Sie gerne noch ein­mal das Ergeb­nis mit de.m in Pläne Excel berech­neten Wert.
• Ändern Sie die Über­schrift eventuell in Gesamt-Umsatz um.
• Spalte hinzufü­gen < Benutzerdefinierte Spalte und geben Sie als Spal­tenformel ein­fach nur eine 1 ein. Den Spal­tenna­men kön­nen Sie so belassen.
• Menü Datei | Schließen & laden in… | Nur Verbindung erstellen und Laden.

Vielle­icht ist Ihnen ja die 1 in der eben erstell­ten Benutzerdefinierte Spalte ein Rät­sel. Zugegeben, Sie hät­ten auch jede beliebige andere Zahl oder auch Zeichen ver­wen­den kön­nen. Diese Spalte gilt prak­tisch als Index, als Link zum verknüpfen mit der Tabelle, wo die Monate und die Gesam­tum­sätze der Pro­duk­te drin­ste­hen.

Und da in dieser Tabelle eine ver­gle­ich­bare Spalte fehlt, muss diese auch hier erstellt wer­den. Wech­seln 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 gle­ichen Wert wie ger­ade eben an; wenn Sie also mein Vorschlag gefol­gt sind, wer­den sie auch hier die 1 ein­tra­gen. Damit sind die Vor­bere­itun­gen abgeschlossen. – Ach ja, wenn Sie schon beim Umbe­nen­nen sind: Geben Sie dieser Abfrage den Namen Monate. Denn dann kön­nen Sie die bei­den Abfra­gen bess­er auseinan­der­hal­ten. Auch hier kön­nen Sie es bei der Über­schrift Benutzerdefiniert belassen oder einen beliebi­gen Titel ver­wen­den. Weit­er geht’s so:

• Menü Start | Kom­binieren | Abfra­gen zusam­men­führen und sie erken­nen, dass die Abfrage Monate im oberen Bere­ich bere­its einge­fügt ist. Ein Klick auf das Drop­down darunter und wählen Sie die Abfrage mit dem Namen Umsätze.
• Markieren Sie im oberen als auch im unteren Fen­ster einen Wert, eine einzelne Zelle in der Spalte mit der 1.
• Belassen Sie es bei Join-Art bei der vorgegebe­nen Auswahl Link­er äußer­er Join.
• Schließen Sie das Fen­ster mit OK.
• Klick­en Sie in der neu geschaf­fe­nen Spalte Umsätze auf den Dop­pelpfeil  in der Über­schrift.
• Acht­en Sie darauf, dass das einzige Häkchen bei Umsatz ste­ht und schließen dann das Fen­ster.
• Ent­fer­nen Sie die Hil­f­ss­palte mit dem Indexw­ert (1).
• Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Vergeben sie als neuen Spal­tenna­men beispiel­sweise Anteil in Prozent.
• Als Spal­tenformel geben Sie in die Berech­nung = [#"Gesamt-Umsatz"]/[Umsatz] ein und Schließen danach dieses Fen­ster.
• For­matieren Sie die Spalte Anteil in Prozent, indem Sie den Daten­typ der eben gener­ierten Spalte auf Als Prozent ändern.
• Löschen Sie die Spalte Umsatz, wo ja auss­chließlich der Jahres-Umsatz enthal­ten ist.
• Schließen & laden.
• In der neu erstell­ten Tabelle (in Excel) ändern Sie bitte die noch in das Zahlen­for­mat auf Prozent und damit ist das Ziel erre­icht.