Xtract: Auf der Basis der Anzahl von Datensätzen soll die prozentuale Verteilung unterschiedlicher Werte einer Spalte (hier die Fraktionendes Bundestages) berechnet werden.
Prozentuale Verteilung nach Zeilen-Anzahl oder Werten
In meinen Schulungen habe ich (gefühlt viel zu oft) erleben „dürfen”, dass auch für „gestandene” Excel-User die Berechnung von prozentualen Anteilen eine immer wieder auftretende Hürde darstellt. Das gilt für Plain Excel und natürlich auch für Power Query und ebenfalls PivotTable. Diese Problematik wird in einer kleinen Serie abgehandelt. Laden Sie idealerweise die jeweils genannten Dateien von unserem Server herunter, sie dienen als Vorlage für die hier diskutierten Aufgaben.
Basis: Zeilen (Excel)
Für diese Übung verwenden Sie die Datei Bundestagsmitglieder_unsortiert.xlsx. Das Ziel ist, die prozentuale Verteilung der Fraktionen, also der Sitze im (damaligen) Bundestag zu berechnen. Um die korrekten Werte zu erhalten müssen allerdings zu Beginn jene Datensätze gelöscht werden, wo der Name der Fraktion mit einem * endet; die entsprechenden Personen sind zu dem Zeitpunkt bereits aus dem Bundestag ausgeschieden und sollen/dürfen naturgemäß nicht in die Berechnung einfließen.
Löschen der ausgeschiedenen Bundestags-Mitglieder
Wie schon beschrieben, sollen alle Zeilen (Datensätze) gelöscht werden, wo in der Spalte Fraktion als letztes Zeichen ein * steht. Idealerweise werden Sie die Daten erst einmal als Tabelle formatieren, beispielsweise per StrgT oder StrgL. Da es ja nur 8 verschiedene Fraktion-Bezeichnungen gibt könnten Sie die Spalte Fraktion durch einen Klick auf den Dropdown-Pfeil erweitern und dann das Häkchen bei all jenen Einträgen ohne den * am Ende entfernen. Nach einem OK werden dann nur noch die ausgeschiedenen Mitglieder angezeigt. Alternativ bietet sich aber auch bei deutlich mehr unterschiedlichen auszuwertenden Begriffen der entsprechenden Spalte die Möglichkeit an, statt die einzelnen Häkchen zu setzen oder zu entfernen im Kontextmenü die Auswahl Textfilter | Endet mit zu treffen. Es zeigt sich dieser Dialog:
Hier erfolgt die Eingabe des Filter-Kriteriums
Lesen Sie sich den Text unterhalb der beiden Eingabe-Zeilen durch und sie werden erkennen, dass es nicht zielführend sein wird, wenn Sie in den (hier gelb markierten) Eingabe-Bereich den Stern schreiben. Es würden „natürlich” alle Zeilen ausgewählt werden. Um das zu umgehen, muss der * „maskiert” werden. Schreiben Sie in das Textfeld als erstes die Tilde ~ (AltGr und die Taste + rechts vom Ü) und direkt danach den *. Das Filtrat zeigt dann ebenfalls alle ausgeschiedenen Mitglieder an. Die restlichen Einträge der zu dem Zeitpunkt vertetenen Mitglieder sind natürlich nur ausgeblendet und nicht gelöscht.
Markieren Sie nun die soeben gefilterten Datensätze (beispielsweise durch einen Klick in die Daten und dann StrgA), Rechtsklick und im Kontextmenü Zeile/Spalte löschen | Gesamte Blattzeile. Nach einem Klick auf das Filter-Symbol in der Überschrift markieren Sie Alles auswählen und sie haben die bereinigte Liste der Bundestagsmitglieder zu dem Zeitpunkt auf dem Bildschirm.
Berechnung der prozentualen Anteile
Von diesen Daten soll nun berechnet werden, wie der prozentuale Anteil der einzelnen Fraktionen im Verhältnis aller Mitglieder des Bundestages ist. Dazu brauchen Sie erst einmal die Gesamt-Zahl der Mitglieder. Schreiben Sie also beispielsweise in E1 Gesamt-Zahl und berechnen in F1, wie viele Bundestagsmitglieder in der Liste enthalten sind:
=ANZAHL2(Tabelle1[Fraktion])
Wenn bei Ihnen der Name der Tabelle mit den Daten ein anderer ist, werden Sie die Formel entsprechend anpassen. In F1 steht nun 631, was der Anzahl von Zeilen der Tabelle (ohne die Überschrift) entspricht, also die Zahl der Datensätze.
Schreiben Sie nun in E2:E5 die Namen der Fraktionen in beliebiger Reihenfolge. Ich wähle hier vollkommen unideologisch die alphabetische Reihenfolge: Bündnis 90/Die Grünen, CDU/CSU, Die Linke, SPD. In die Zelle F2 fügen Sie nun diese Formel ein:
=ZÄHLENWENN(Tabelle1[Fraktion];E2)
und ziehen diese bis zur Zelle F5 nach unten. Beispielsweise in G1 schreiben Sie die Überschrift Sitzverteilung in Prozent und in G2 die Berechnung des prozentualen Anteils:
=F2/F$1%
und ziehen Sie auch diese Formel bis G5 nach unten. Idealerweise werden Sie das Zahlenformat noch auf ein oder zwei Nachkommastellen begrenzen.
Das Ergebnis stimmt selbstverständlich und natürlich können Sie diese kleine Tabelle auch noch nach Sitzen oder Prozentanteilen sortieren. Ein kleiner Hinweis noch: Probieren Sie gerne auch die Verwendung von Bereichsnamen für beispielsweise die einzelnen Fraktionen aus, dass kann die Arbeit und die Übersichtlichkeit enorm erleichtern.
Den Folge-Beitrag können Sie hier (1 Woche nach der Veröffentlichung) aufrufen.
Rückmeldungen / Feedback 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 Ihrerseits z.B. 1,50 € freuen … (← Klick mich!)