Prozentuale Verteilung nach Zeilen-Anzahl oder Werten

In meinen Schu­lun­gen habe ich (gefühlt viel zu oft) erleben dür­fen, dass auch für „ges­tandene” Excel-User die Berech­nung von prozen­tualen Anteilen eine immer wieder auftre­tende Hürde darstellt. Das gilt für Plain Excel und natür­lich auch für Pow­er Query und eben­falls Piv­ot­Table. Laden Sie ide­al­er­weise die jew­eils genan­nten Dateien von unserem Serv­er herunter, sie dienen als Vor­lage für die hier disku­tierten Auf­gaben.

Basis: Zeilen (Power Query)

Wie schon im plain-Excel-Abschnitt wer­den Sie die Datei mit den Bun­destag-Mit­gliedern ver­wen­den, allerd­ings (natür­lich) in unverän­dert­er Form, also „jungfräulich” 😉. Dass Sie die Werte über den Weg Dat­en | Aus Tabelle/Bereich in den Pow­er Query-Edi­tor importieren, sollte Ihnen geläu­fig sein. Natür­lich acht­en Sie dabei darauf, dass bei der Fes­tle­gung des Tabellen-Bere­ichs fest­gelegt wird, dass die Dat­en eine Über­schrift haben. – Nach dem Import benenne ich die Query gle­ich zu Raw­Da­ta um, damit die Zuord­nung der Dat­en auch durch die Namensge­bung deut­lich wird.

▲ nach oben …

Löschen der ausgeschiedenen Bundestags-Mitglieder

In der PQ-Umge­bung ist das Ent­fer­nen der aus­geschiede­nen Mit­glieder aus der Tabelle/Abfrage deut­lich kom­fort­abler als in Plain Excel (sofern Sie etwas Erfahrung mit PQ haben). Erweit­ern Sie die Spalte Frak­tion  und wählen Sie im Kon­textmenü Textfil­ter | Endet nicht mit… Fügen Sie in das erste Eingabefeld auss­chließlich den * ein. Nach einem OK ist das Wun­schergeb­nis gegeben. Die Dat­en der nicht mehr im Bun­destag vertrete­nen Per­so­n­en sind tat­säch­lich gelöscht und nicht nur „unsicht­bar” gemacht/gefiltert.

▲ nach oben …

Berechnung der prozentualen Anteile

Wie in der Excel-Umge­bung prak­tisch Stan­dard gibt es mehrere unter­schiedliche Wege zum Ziel. Ich zeige Ihnen hier eine Möglichkeit auf, die auch für Nicht-Profis gut nachvol­lziehbar sein sollte. Markieren Sie die Spalte Frak­tion. Recht­sklick in die Über­schrift und dann Als neue Abfrage hinzufü­gen. Automa­tisch wird eine Liste mit dem Namen Frak­tion erstellt. Ein Klick auf das Sym­bol Sta­tis­tiken und­danach  Werte zählen. – Für mich ist Trans­parenz wichtiger als etwas mehr Tipp-Arbeit, darum benenne ich jet­zt die eben erstellte Liste zu Anzahl um.

Wech­seln Sie nun zur Abfrage Raw­Da­ta, indem Sie im linken Seit­en­fen­ster auf diesen Ein­trag Klick­en. Recht­sklick in Frak­tion und Grup­pieren nach… Den fol­gen­den Dia­log kön­nen Sie so belassen, denn es sollen ja aus der Spalte Frak­tion die Anzahl des jew­eili­gen Frak­tion­sna­mens berech­net wer­den:

Die Default-Ein­stel­lun­gen sind in diesem Fall passend

Die Query „schrumpft” auf 4 Zeilen, je eine mit dem Namen der Frak­tion (in der Rei­hen­folge des erst­ma­li­gen Auftretens) in der ersten Spalte; und der zweit­en Spalte sind die berech­nete Anzahl der Ein­träge aufge­führt, also die der jew­eili­gen Abge­ord­neten der Frak­tion. Damit haben Sie alle erforder­lichen Werte für die Berech­nung der prozen­tualen Anteile. Markieren Sie die Spalte Anzahl. Wech­seln Sie zum Menü Spalte hinzufü­gen | Stan­dard | Divi­sion und tra­gen Sie als Divi­sor erst ein­mal eine beliebige Zahl ein; ich ver­wende 999, die kann ich leicht iden­ti­fizieren 😉.

Pow­er Query berech­net nun den prozen­tualen Anteil für jede Frak­tion unter der Annahme, dass die Gesamtzahl der Abge­ord­neten 999 sei; so haben Sie es ja auch im Dia­log eingegeben. Natür­lich ist die Berech­nung aus math­e­ma­tis­ch­er Sicht richtig, das gewün­schte Ergeb­nis aber nicht, denn es sind ja 631 Abge­ord­nete. Hin­weis: PQ hat ja schon genau diese Zahl in der Liste Anzahl berech­net und abgelegt. Um den Wert (dynamisch) zu ver­wen­den, markieren Sie in der Edi­ti­er-Eingabezeile den Wert 999 und über­schreiben diesen mit dem Namen der (Berech­nungs-) Abfrage: Anzahl (Achtung, exak­te Groß- Klein­schrei­bung!). Nach einem Klick in den Bere­ich unter­halb der Edi­ti­er-Zeile oder einem ein­fachen Return wer­den die berech­neten Werte umge­hend auf die kor­rek­ten Werte angepasst. Ändern Sie nun die Über­schrift der Spalte Divi­sion zu prozen­tualer Anteil und passen den Daten­typ dieser Spalte auf Prozen­twert an.

Das war’s. Nun noch Datei | Schließen & laden und bei­de Queries (Raw­Da­ta und Anzahl) wer­den in ein neues Tabel­len­blatt geschrieben. Es wäre gewiss eine gute Idee, die Abfrage Raw­Da­ta im Vor­wege in beispiel­sweise prozen­tuale Anteile umzube­nen­nen, da beim Spe­ich­ern automa­tisch der Name der Abfrage als Beze­ich­nung für das Reg­is­terblatt über­nom­men wird. Über den Weg Schließen & laden in… hät­ten sie bequem erre­ichen kön­nen, dass beispiel­sweise die Liste Anzahl als Nur Verbindung gesichert wird und somit kein sep­a­rates Tabel­len­blatt erstellt und genutzt wird. Nachträglich kön­nen Sie das Spe­ich­ern-Ver­hal­ten wie hier beschrieben ändern.

Obwohl in Pow­er Query das Zahlen­for­mat der Berech­nung die Prozent-Schreib­weise ist, wird im Tabel­len­blatt die nor­male Fließkom­ma-Schreib­weise ver­wen­det. For­matieren Sie hier die entsprechende Spalte im Zahlen­for­mat „Prozent For­mat” und passen Sie eventuell die Nachkom­mas­tellen auf eine oder zwei Stellen an. – Damit ist die Auf­gabe auch in Pow­er Query gelöst.

▲ nach oben …

Teil 1 dieser Serie kön­nen Sie hier anse­hen.

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. 1,50 € freuen … (← Klick mich!)