PQ: Prozentuale Verteilung

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

In manchen Sit­u­a­tio­nen ist es erforder­lich, den prozen­tualen Anteil vom Total zu berech­nen. Und Pow­er Query kann die erste Wahl sein, wenn es sich um eine große Daten­menge han­delt. Als Denkmod­ell bietet sich eine Liste verkaufter Pro­duk­te an, wo unter­schiedliche Pro­duk­t­grup­pen entsprechend bew­ertet bzw. berech­net wer­den sollen. Oder die Umsäte mehrer Verkäufer, die Maschi­nen-Aus­las­tung in Zeit-Blöck­en eines Tages, oder, oder, oder… Zugegeben, in diesem hier vorgestell­ten Fall ist eine Vorge­hensweise in Plain Excel aus­nahm­sweise ein­mal deut­lich ein­fach­er, aber es geht in diesem Beitrag nun ein­mal um PQ. 😉

Für diese Übung beg­nüge ich mich mit etwas über 600 Daten­sätzen; laden Sie diese Datei mit den Abge­ord­neten des 18. Deutschen Bun­destages (Stand: 04.06.2015) herunter. Es han­delt sich um eine Text-Datei im csv-For­mat. Diese wer­den Sie über Dat­en | Aus Text/csv in Pow­er Query importieren und die erste Dat­en-Zeile als Über­schrift ein­richt­en. Und dass die Dat­en in kein­er Weise geord­net sind ist natür­lich gewollt. 😉 Die vorzunehmende Auswer­tung soll den prozentuellen Anteil der jew­eili­gen Frak­tio­nen enthal­ten, die „Ehe­ma­li­gen”, welche durch einen ange­hängten Aster­isk (*) gekennze­ich­net sind, fließen nicht in die Berech­nung ein.

Auch wenn es bei dieser rel­a­tiv gerin­gen Anzahl von Daten­sätzen zeitlich kaum merk­bar sein wird, soll­ten Sie (aus prinzip­iellen Erwä­gun­gen) die zu berech­nen­den Dat­en so „schlank“ wie möglich hal­ten. Und da Sie ja auss­chließlich die Frak­tion­szuge­hörigkeit auswerten wer­den, löschen Sie die bei­den ersten Spal­ten. Anschließend fil­tern Sie die Dat­en dergestalt, dass nur die aktiv­en Frak­tion­s­mit­glieder beibehal­ten wer­den; das geht sehr schön über den Textfil­ter  Endet nicht mit; natür­lich kön­nen Sie auch von Hand die Häkchen bei den entsprechen­den Ein­trä­gen in der Auflis­tung ent­fer­nen. Sie erin­nern sich: Das ange­hängte Sternchen ist das Kennze­ichen für nicht mehr aktive MdB (Mit­glied des Bun­destages).

Als näch­stes machen Sie sich bewusst, mit welch­er math­e­ma­tis­chen Funktion/Formel Sie zum gewün­scht­en Ergeb­nis kom­men. Der wohl kürzeste Weg wäre gewiss Anzahl Frak­tion­s­mit­glieder / Anzahl aller (gefilterten) Daten­sätze. Und da PQ beim Fil­tern die nicht zu ver­wen­den­den Zeilen real ent­fer­nt und nicht ver­steckt (wie Plain Excel), kön­nen Sie die Anzahl aller Zeilen erfassen oder berech­nen.

Aber … Was in Plain Excel prinzip­iell mit ein­er Hil­f­ss­palte und einem einzi­gen Mausklick auf das Sum­men-Sym­bol in der Menüleiste oder ein­er kurzen Formel erledigt wer­den kann ist in Pow­er Query aus­nahm­sweise einiges (oder je nach Vorge­hensweise auch deut­lich) mehr an Aufwand. Ich zeige Ihnen hier zwei dur­chaus unter­schiedliche Wege auf; bei­de führen zum kor­rek­ten Ergeb­nis, Sie kön­nen je nach Sym­pa­thie auswählen, wie Sie zum Ziel gelan­gen.

▲ nach oben …

Vorschlag 1

Zugegeben, nicht für fort­geschrit­tene PQ-Experten gedacht (die wür­den sich lang­weilen oder spon­tan einen anderen Weg wählen), aber ges­tandene Ein­steiger haben mit diesem Ablauf die Chance, PQ-Basics zu ver­ste­hen. Hier „Step by step” ein denkbar­er Ablauf:

• Gener­ieren Sie über Spalte hinzufü­gen | Benutzerdefinierte Spalte eine neue Spalte und tra­gen Sie in den Bere­ich für die Spal­tenformel die Zif­fer 1 ein.
• Die Über­schrift Benutzerdefiniert kann so bleiben.
• Nach einem Klick auf OK erstellen Sie von dieser Query ein Dup­likat, welch­es dann typ­is­cher­weise den Namen Bundestagsmitglieder_unsortiert (2) bekommt.
• Bleiben Sie im Dup­likat. Markieren Sie die Spalte Benutzerdefiniert, Grup­pieren nach… (auf beliebigem Wege).
• Belassen Sie es bei den Vor­gaben und bestäti­gen Sie sofort mit OK.
• Wech­seln Sie zur ursprünglichen Abfrage Bundestagsmitglieder_unsortiert.
• Über das Menü Start | Abfra­gen zusam­men­führen wählen sie im Dia­log beim einzeili­gen Textfeld in der Mitte die ger­ade zuvor erstellte Abfrage Bundestagsmitglieder_unsortiert (2).
• Markieren Sie nun bei Bundestagsmitglieder_unsortiert und bei Bundestagsmitglieder_unsortiert (2) die Spalte Benutzerdefiniert.
• Schließen Sie den Dia­log und erweit­ern dann die neu erstellte Spalte durch einen Klick auf den Dop­pelpfeil.
• Ent­fer­nen Sie in diesem Dia­log alle Häkchen außer bei Anzahl.
• Aus prinzip­iellen Erwä­gun­gen kön­nen Sie nun auch die mit­tlere Spalte Anzahl ent­fer­nen / löschen.

Nun ist zwar in jed­er einzel­nen Zeile auch die Gesamtzahl aller Abge­ord­neten ver­merkt, aber das ist ja nur ein Hil­f­skon­strukt; Sie wollen ja für jede einzelne Frak­tion den prozen­tualen Anteil berech­nen. Also …

• Markieren Sie dazu die Spalte Frak­tion und beispiel­sweise über einen Recht­sklick Grup­pieren nach…
• Die Vor­gabe ist ja bei Neuer Spal­tenname bere­its Anzahl und bei Vor­gang ist es Zeilen zählen. Das passt, darum gle­ich OK.
• Das Ergeb­nis stimmt zwar irgend­wie, ist aber gewiss keineswegs das, was Sie erwartet haben. Also öff­nen Sie noch ein­mal per Dop­pelk­lick im recht­en Seit­en­fen­ster den let­zten Schritt Grup­pierte Zeilen.
• Markieren Sie im oberen Bere­ich statt des Options­feldes Stan­dard die Auswahl Weit­ere.
• Ein Klick auf die untere Schalt­fläche Aggre­ga­tion hinzufü­gen sorgt dafür, dass in der Spalte Neuer Spal­tenname eine weit­ere, freie Zeile erscheint.
• Als Neuer Spal­tenname geben Sie beispiel­sweise Gesamt-Summe ein, bei Vor­gang Min oder Max und bei Spalte wählen Sie Anzahl.1 .
• Nun liegen bei­de erforder­lichen Werte für die Berech­nung der prozen­tualen Anteile vor. Über Spalte hinzufü­gen | Benutzerdefinierte Spalte geben Sie im Dia­log bei Neuer Spal­tenname beispiel­sweise Prozent-Anteil ein und als Benutzerdefinierte Spal­tenformel nach dem = im recht­en Seit­en­fen­ster bei Ver­füg­bar Spal­ten ein Dop­pelk­lick auf Anzahl, dann den Schrägstrich / und let­z­tendlich ein Dop­pelk­lick auf Gesamt-Summe.
• Die Spal­ten Anzahl und Gesamt-Summe kön­nen Sie nun löschen. Der Optik wegen wer­den Sie nun eventuell die Spalte Prozent-Anteil auch als Daten­typ:  Prozentsatz for­matieren.

Zugegeben, das ist ins­ge­samt ziem­lich umständlich. Auch für Pow­er Query. Der einzige Vorteil den ich in einem solchen Ablauf sehe ist: Die meis­ten Vorge­hensweisen wer­den sie als jemand, der/die eine gewisse Basis-Erfahrung mit PQ hat, bere­its ken­nen. Und dadurch wer­den Sie gewiss auch ver­ste­hen, was da wann passiert. Wenn Sie exper­i­men­tier­freudig sind oder ein­fach ein­mal einen „ele­gan­teren” Weg gehen wollen, dann wird Ihnen der näch­ste Vorschlag deut­lich bess­er gefall­en. 🙂

▲ nach oben …

Vorschlag 2

Die Aus­gangslage ist entwed­er direkt nach dem Import der Dat­en und der Anpas­sung der Über­schriften (also mit allen drei Spal­ten) oder aber nach dem Ent­fer­nen der bei­den ersten Spal­ten. Der Zeitun­ter­schied bei bei­den Vorge­hensweisen dürfte im End­ef­fekt nicht spür­bar, nur mess­bar sein. Wenn Sie wollen kön­nen Sie bere­its jet­zt die ehe­ma­li­gen Frak­tion­s­mit­glieder fil­tern, es geht aber auch sehr schön in einem der näch­sten Schritte.

Recht­sklick in die Über­schrift Frak­tion und Grup­pieren nach… und auch sofort mit OK bestäti­gen. An dieser Stelle müssen Sie sich entschei­den, ob sie die Ehe­ma­li­gen mit berück­sichti­gen wollen oder nicht. Bei „Nein” ein­fach per Fil­ter löschen, anson­sten müssten Sie die Abfrage duplizieren und dafür sor­gen, dass in der einen Query nur die aktuellen und in der zweit­en Abfrage nur die ehe­ma­li­gen MdB aufge­führt sind. Bedenken Sie aber, dass die Aus­sagekraft der Zahlen für nicht mehr dem Bun­destag zuge­hörende Per­so­n­en kaum rel­e­vant ist, denn oft sind Krankheit oder auch Tod der Grund dafür, dass ein Abge­ord­neter das Amt nicht mehr hat. – Die fol­gen­den Arbeitss­chritte set­zen voraus, dass nur die „aktiv­en” MdB in der Query enthal­ten sind.

Um die Gesamt­summe zu berech­nen ein Recht­sklick auf Anzahl und die unter­ste Auswahl im Kon­textmenü Als neue Abfrage hinzufü­gen nutzen. Pow­er Query erstellt automa­tisch eine neue Abfrage, wo unter der Über­schrift Liste auss­chließlich die jew­eils per Grup­pierung berech­nete Anzahl der Frak­tion­s­mit­glieder aufge­führt ist. Da es sich hier­bei (wie der Name der Über­schrift schon aufzeigt und auch am Sym­bol links des Abfrage-Namens im linken Seit­en­fen­ster erkennbar) um eine Liste han­delt haben Sie auch die Möglichkeit, die auf Spal­ten bezo­ge­nen sta­tis­tis­chen Funk­tio­nen zu nutzen. Ein Klick auf die Schalt­fläche Sta­tis­tiken und der erste Ein­trag Summe passt schon. Der Deut­lichkeit wegen ändere ich nun den Namen diese Abfrage auf MdB_Total. Und ich ver­wende den Unter­strich statt eines Leerze­ichens, weil ich mir so beim Bezug auf den Inhalt dieser Abfrage etwas Schreibar­beit ers­pare; so gebe ich nur den Namen ein, wenn ein Leerze­ichen enthal­ten wäre müsste ich #"MdB Total" eingeben. Jet­zt enthält diese Abfrage, die übri­gens links des Namens das Sym­bol 1²3 hat, in der einzi­gen Zeile den Wert 631.

Wech­sel zur ursprünglichen Abfrage. Spalte hinzufü­gen | Benutzerdefinierte Spalte und tra­gen Sie bei Neuer Spal­tenname beispiel­sweise Prozen­tualer Anteil ein und als Benutzerdefinierte Spal­tenformel entwed­er [Anzahl]/#"MdB Total" oder [Anzahl]/MdB_Total. Und ach ja, den Namen der Liste kön­nen Sie nicht per Klick übernehmen, den geben Sie in kor­rek­ter Groß- Klein­schrei­bung per Hand ein. Und das kor­rek­te Ergeb­nis ste­ht sofort in der neuen Spalte. 😎 Auch in diesem Fall kön­nen Sie natür­lich die Formatierung/den Daten­typ noch auf Prozentsatz anpassen.

Es wird sie nicht ver­wun­dern wenn sie erfahren, dass ich auss­chließlich diesen zweit­en Weg anwende. Weil ich ver­ste­he, warum ich den kleinen Umweg über das Erstellen der Liste gehen muss um die Funk­tion List.Sum anzuwen­den, ist mir diese Vorge­hensweise wesentlich sym­pa­this­ch­er. Und im Gegen­satz zum ersten Vorschlag ist das auch kein Umweg, eher eine deut­liche Verkürzung. – Wobei ich Sie darauf hin­weisen möchte dass ich es has­se, fer­tige Lösung per copy/paste zu übernehmen, wenn ich den Hin­ter­grund, den Ablauf nicht ver­ste­he; mit hoher Sicher­heit wird es irgend­wann ein­mal zu einem Fehler kom­men und ich weiß dann nicht, woran es liegt und wie ich den Miss­stand kor­rigieren kann.

▲ nach oben …

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

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