PQ-Basics (3): Filtern und teilen

Aktive und ehemalige Mitglieder des Deutschen Bundestages ausgeben

Vorbereitung

Laden bzw. öffnen Sie zu Beginn diese Datei. Dort sind (per Stand Mitte 2015) die aktiven und ehemaligen Mitglieder des Deutschen Bundestages in einer tabellarischen Aufstellung erfasst. Es ist übrigens keine Tabelle oder Liste im eigentlichen Sinn, denn ein wichtiges Element fehlt: Die Überschriften. Die recht einfache Aufgabe ist es nun, je eine Liste der aktiven und ehemaligen Mitglieder zu erstellen. Diese Listen sollen nach Fraktionen und innerhalb derer nach Namen geordnet sein.

Analyse

Sie haben die Daten auf dem Bildschirm. Zu Beginn geht es darum, alle Zeilen in die Abfrage zu holen. Anschließend müssen Sie erkennen, wie die Ehemaligen gekennzeichnet sind. Obwohl es nur etwas über 600 Zeilen sind ist es mühselig, alle Zeilen auf Inkonsistenzen (Leerzeilen) zu prüfen. Recht komfortabel geht das so:

  • Erst einmal StrgEnde, um die letzte Zeile des genutzten Bereichs zu aktivieren.
  • Nun StrgA, damit der gesamte zusammenhängende Bereich markiert wird.

Wenn jetzt nicht alles bis A1 markiert ist, dann ist die Zeile oberhalb der Markierung eine Leerzeile. Und die hat in einer Liste nichts zu suchen. Der einzig sinnvolle Weg: Löschen. Und anschließend noch einmal bzw. so lange prüfen, bis wirklich alles bis zur Zeile 1 markiert ist. Damit sind die Voraussetzungen für die Erstellung einer Liste/Tabelle aus den Daten erfüllt. – Ach ja, falls Sie keinen Fehler in den vom Blog herunter geladenen gefunden haben, ist das OK. Die Daten sind konsistent.

Bleibt noch festzustellen oder zu erkunden, welches die „Ehemaligen“ sind. Daraus folgert dann natürlich, dass die anderen Namen (zu dem Zeitpunkt) aktive Mitglieder des Deutschen Bundestages waren. Ich habe bei der Erstellung natürlich genau hingesehen und kann Ihnen sagen, dass alle Namen, welche in der Fraktion mit einem Stern als letztes Zeichen gekennzeichnet sind, nicht mehr Mitglieder der Fraktion sind.

▲ nach oben …

Einlesen in die Abfrage

Egal ob die Abfrage später in ein bestehendes oder ein neues Blatt eingefügt werden soll, die Daten müssen in den Abfrage-Editor eingelesen werden. Dazu werden Sie eine beliebige Zelle innerhalb der Daten markieren, den Menüpunkt Power Query anklicken und dann im Menüband in der Gruppe Excel-Dateien auf das Symbol Von Tabelle klicken:

Hier klicken, um die Daten einzulesen

Hier klicken, um die Daten einzulesen

Automatisch wird der gesamte, zusammenhängende Datenbereich markiert und es öffnet sich ein Dialog, wo genau dieser Bereich vorgeschlagen wird:

Bereich festlegen und Überschrift-Status korrigieren

Bereich festlegen und Überschrift-Status korrigieren

Der vorgeschlagene Bereich stimmt. Aber es ist klar ersichtlich, dass  die Tabelle keine Überschriften hat. Und nicht nur PQ (Power Query) ist der Meinung, dass eine Tabelle Überschriften haben muss. Also werden Sie das Häkchen in dem Optionskästchen entfernen. Die Quelldaten werden nun automatisch in eine Liste mit Überschriften umgewandelt und optisch auch in eine Tabelle mit blau/weißer Tabellierung dargestellt. Gleichzeitig öffnet sich in einem neuen Fenster der Abfrage-Editor mit zumindest einem Teil der Daten:

Der soeben geöffnete Abfrage-Editor

Der soeben geöffnete Abfrage-Editor

An dieser Stelle einige fundamentale Anmerkungen, die für diesen Editor gelten:

  • Wie gewohnt sind ja nach Menü-Auswahl unterschiedliche Menü-Bänder (Symbol-Leisten, Ribbons) sichtbar.
  • Die Editierzeile sollte Tabu bleiben, solange Sie nicht sehr fit in Sachen Power Query sind.
  • Da es keine Zeilenköpfe gibt, wird ein Klick in die Überschrift die gesamte Spalte markieren.
  • Bei großen Datenmengen werden nicht alle Datensätze angezeigt, die initiierten Änderungen wirken sich aber auf jeden Fall auch auf die nicht sichtbaren Zeilen aus.
  • Einzelne Zellen können nicht geändert, editiert werden.

In Excel 2016 gibt es kleine Unterschiede, insbesondere bei den ersten Schritten. Darum hier „im Telegrammstil“ die wichtigsten geänderten Vorgehensweisen:

  • Es ist eine beliebige Zelle in den Daten markiert.
  • Menü Daten, dort in der Gruppe Abrufen und transformieren das Symbol  Aus Tabelle anklicken.
  • Im Dialog-Fenster den vorgeschlagenen Bereich prüfen. Hier ist normalerweise kein Häkchen bei Tabelle hat Überschriften.

Das Dialogfenster in 2016

Das Dialogfenster in 2016

Das weitere Vorgehen ist dem zu der 2010/2013er Version praktisch identisch.

▲ nach oben …

Sortieren

Im nächsten Schritt sollen die Einträge so sortiert werden, dass alle Fraktionen nach dem Alphabet aufsteigend sortiert sind und innerhalb der Fraktion die Namen in aufsteigender Reihenfolge. Um den Effekt und die Vorgehensweise etwas deutlicher zu machen, werden Sie erst bewusst eine andere Ordnung in die Daten bringen. Sortieren Sie nach dem Vornamen, am besten Z→A. Dazu klicken Sie auf den DropDown-Pfeil DropDownPfeil rechts neben der Überschrift Spalte2 und markieren dann absteigend sortieren durch einen Klick:

Des guten Zwecks wegen: Nach Vornamen absteigend sortieren

Des guten Zwecks wegen: Nach Vornamen absteigend sortieren

Bei der Gelegenheit werden Sie vielleicht auch erkennen, dass nach amerikanischer Logik sortiert wird: Das Ö steht noch vor dem Y … Aber jetzt haben Sie das erreicht, was ich wollte: Ein herrliches Durcheinander bei den Namen und Fraktionen. Sie fangen also bei Null an.

Wenn Sie nach mehreren Spalten sortieren wollen, dann müssen Sie sich in Excel immer von hinten nach vorne durcharbeiten (wenn es mehr als 3 Kriterien sind). Hier ist es so, wie es prinzipiell logisch ist: Erst das wichtigste Kriterium sortieren, dann den zweitwichtigsten Wert, und so weiter. Dass immer die komplette Zeile sortiert wird, versteht sich von alleine. 

Wenn Sie nun Spalte3 und dann Spalte1 sortieren, werden Sie vielleicht sehr kleine Änderungen sehen, aber den gewünschten Effekt nicht. Das liegt an dem eben schon erwähnten Umstand, dass die Spalte2 (Vornamen) ja als erstrangiges Sortierkriterium festgelegt wurde. Also: Klick in den (nun etwas anders aussehenden) DropDown-Pfeil der Spalte2 und Sortierung löschen anklicken. Jetzt ist alles so, wie gewünscht. – Übrigens: Rechts in der Überschrift wird die Rangfolge der Sortierung in kleinen, grauen Zahlen angezeigt, wenn mehrere Felder (Spalten) sortiert sind.  Und bei der Gelegenheit: Links im Überschrift-Feld wird symbolisch der Typ des Feldes dargestellt. Diese Neuerungen gibt es erst seit dem Update Mitte Juli 2016. Die erste Aufgabe ist erledigt und es kann nun die folgende Aufgabe begonnen werden.

▲ nach oben …

Schönheitskorrektur

Was ein wenig stört, das sind die Inhalte, die Texte der Überschriften. „Sprechende“ Bezeichnungen machen sich einfach besser. Aber der Demonstration wegen werden Sie dennoch diese Abfrage erst einmal speichern. Die Bezeichnung in dem entsprechenden Symbol ist nicht so ganz eingängig, aber wenn Sie auf Schließen & laden klicken, erreichen Sie Ihr Ziel. Sie schließen das Abfrage-Fenster und laden das Ergebnis in ein Tabellenblatt. Sie werden erkennen, dass der Arbeitsbereich nun geteilt ist. Links ist der herkömmliche Tabellen-Bereich, ein kleinerer Bereich rechts wird von einem gesonderten Fenster eingenommen:

Der Steuer-Bereich für die Abfragen

Der Steuer-Bereich für die Abfragen

Hier werden alle Abfragen untereinander aufgeführt, welche in der aktuellen Arbeitsmappe vorhanden sind. Der grüne Bereich ist eine sensitive Schaltfläche, welche auf Zeigen und auf auf einen Doppelklick reagiert; darin ist rechts oben eine weitere Schaltfläche (das Blatt mit dem Symbol), welche bei einem einfachen Klick diese Abfrage aktualisiert. Wenn Sie einen Moment auf die Fläche zeigen, wird eine kleine Vorschau der Daten einblendet. 

Natürlich könnten Sie nun in dem durch die Abfrage erstellten Tabellenblatt, der Liste die Sortierung nach Ihren Wünschen bzw. meiner Vorgabe ändern. Aber das ist nicht wirklich hilfreich, denn wenn Sie in der eigentlichen Abfrage, im Editor-Fenster anschließend beliebige Änderungen vornehmen, dann wird hier in der Liste wieder genau das erscheinen, was in der Abfrage sichtbar ist, also auch die wenig aussagekräftigen Überschriften.

Bekanntlich führen ja viele Wege nach Rom. Und um die Abfrage wieder zu öffnen, können Sie beispielsweise auf die grüne Fläche im Steuer-Bereich einen Doppelklick durchführen. Umgehend wird wieder das Abfrage-Fenster geöffnet und steht für weitere Aktionen bereit. Hier erst einmal für die sinnvolle Anpassung der Überschriften …

Klicken Sie ein Mal in die Überschrift Spalte1. Es wird die ganze Spalte markiert. Jetzt ein Doppelklick in die Überschrift, damit dieser Text markiert wird. Dann den Text Name eingeben und Return.

Nun ein Mal in die Überschrift Spalte2 klicken. F2 und Sie können sofort losschreiben; der Text sollte natürlich Vorname sein.

Alle guten Dinge sind ja drei, darum bitte auf die Überschrift Spalte3 klicken. Und zwar mit einem Rechtsklick. Im Kontextmenü finden Sie im unteren Viertel die Auswahlmöglichkeit Umbenennen… Jetzt werden Sie auch diese Überschrift zu Fraktion ändern.

Schließen Sie nun wieder das Editor-Fenster und damit auch die Abfrage selbst. Das geht beispielsweise auch, indem Sie in den grün bzw. dunkel hinterlegten, ersten Menüpunkt  Datei  klicken und dort die oberste Auswahl treffen.

▲ nach oben …

Aktive und Ehemalige trennen

Vorarbeiten

Bei jeder Aufgabe ist es gut, ein wenig in die Zukunft zu denken, zu planen. Und hier ist das besonders sinnvoll. – Unter dem Aspekt, dass Sie aus einer Abfrage nur ein Ergebnis, eine Auswertung erzielen können stellt sich die Frage, ob Sie für 2 Ergebnisse auch ein zweites Mal die gerade durchgeführten Vorgänge wiederholen müssen. Sie ahnen es: Nein, das muss nicht sein …

Sie können eine Abfrage, an der in unterschiedlichen Versionen nur wenige Änderungen durchgeführt werden sollen, duplizieren. Und da eine Abfrage an sich kaum Speicherplatz braucht, friere ich den derzeitigen Stand erst einmal ein, man weiß ja nie, wozu der derzeitige Stand noch gut sein kann. Dann erstelle ich zwei Kopien; eine für die „Aktiven“, eine für die „Ehemaligen“.

Der einfachste Weg dorthin: Rechtsklick auf die grüne Schaltfläche im Regie-Zentrum der Abfragen, dann in Kontextmenü etwa in der Mitte den Punkt Duplizieren anklicken. Sofort wird eine Kopie der ursprünglichen Abfrage erstellt und das Editor-Fenster wird geöffnet. Im rechten Bereich, bei den Abfrageeinstellungen erkennen Sie im Bereich Eigenschaften, dass der ursprüngliche Name der Abfrage (eigentlich der erzeugten Tabelle/Liste) übernommen und durch einen numerischen Zusatz in runden Klammern ergänzt worden ist:

Der automatisch erstellte Name der Abfrage-Kopie

Der automatisch erstellte Name der Abfrage-Kopie

Sie könnten diese Abfrage nun schließen und auf gleichem Wege eine weitere Kopie des Originals erstellen. Oder Sie bleiben im Abfrage-Fenster und wählen in der Gruppe Abfrage den Punkt Verwalten und hier wiederum Duplizieren. Dann wird auf der Basis der aktuellen Abfrage ein Duplikat erstellt.

Wiederum wird ohne Ihr weiteres Zutun die aktuelle Abfrage geschlossen und eine neue Abfrage mit der entsprechenden Nummerierung erstellt. In einem Fenster links des eigentlichen Editors erkennen Sie, dass es 3 Abfragen in dieser Arbeitsmappe gibt und die dritte die aktive ist:

Übersicht der Abfragen

Übersicht der Abfragen

Ähnlich wie bei den Überschriften ist die Namensgebung der Tabellen nicht wirklich aussagekräftig. Diese Bezeichnungen sollen nun angepasst werden. Im rechten Seitenfenster (Abfrageeinstellungen) finden Sie bei den Eigenschaften das Feld Name. Hier ändern Sie den Namen auf lst_Ausgeschieden. „lst“ steht übrigens als Abkürzung für eine Liste, denn es sind im Endeffekt die erzeugten Listen, welche sich dahinter verbergen.

Im linken Seitenfenster einen Klick auf Tabelle1 (2) und sofort wechselt auch die Abfrage. Hier ändern Sie bitte den Namen auf lst_Aktive Mitglieder und der Tabelle1 geben Sie bitte den Namen lst_Alle Mitglieder. Bei der Gelegenheit: Wenn es Ihnen sympatischer ist, können Sie auch statt „lst_„ das Präfix „tbl_“ (Kurzform für Tabelle) verwenden, was vielfach auch so gemacht wird.

▲ nach oben …

Filtern

Erst einmal werden Sie die aktiven Mitglieder des Bundestages in der entsprechenden Liste filtern. Dazu muss natürlich genau diese Abfrage auch geöffnet sein, klar. Beginnen Sie mit den aktiven Mitgliedern des Hohen Hauses.

Sie brauchen alle Zeilen, wo in der Spalte Fraktion kein * als letztes Zeichen existiert. Einfach ein Klick auf den DropDown-Pfeil in der Spalte Fraktion und als Textfilter wählen Sie Endet nicht mit… Im Dialogfenster geben Sie das gesuchte Zeichen * ein und dann OK. – Die Änderung ist nicht sofort, nicht auf den ersten Blick erkennbar, aber vertrauen Sie PQ, es hat geklappt (wenn Sie nach meiner Vorgabe gearbeitet haben). Dennoch: Sie könnten nun alle Abfragen schließen und dann erkennen, dass in dieser Liste tatsächlich 11 Zeilen weniger enthalten sind. Aber das geht auch noch später  😉 . Die Stichprobe wäre: Suchen Sie einmal in der Abfrage mit allen Abgeordneten die erste Position mit einer Sternchen-Markierung. Es ist Zeile 64. Wechsel zurück zu tbl_Aktive Mitglieder und die Zeile 64 suchen. Stichprobe erfolgreich bestanden. Diese Abfrage schließen Sie nun.

Prinzipiell war der hierüber aufgezeigte Weg ideal bei dieser Konstellation. Darum werden Sie den gleichen Weg auch für die ausgeschiedenen Mitglieder der Fraktionen beschreiten. Nur dass Sie hier naturgemäß die Bedingung Endet mit… markieren und dann im Dialog den Stern eingeben. Und hier ist natürlich sofort der Effekt deutlich sichtbar, es verbleiben gerade einmal 11 Namen. Auch diese Abfrage können Sie nun schließen.

Ihnen ist nun gewiss klar, dass die erzeugten Listen wirklich nur die Extrakte enthalten. Sie können innerhalb der Liste nicht wieder Namen einblenden, sie sind wirklich so klein. Das ist ein großer Vorteil, wenn die Datenmengen erheblich größer sind und die Rechenzeit dadurch bedingt zu lang ist. Wenn Sie in einer Liste mit 100.000 Zeilen nur jeden hundertsten Datensatz anzeigen lassen, dann sehen Sie zwar nur 1.000 Zeilen, aber Excel muss immer mit 100.000 Zeilen umgehen und diese berechnen. Per PQ gefilterte Listen sind so klein, wie sie aussehen. – Und daraus ergibt sich auch der nicht zu unterschätzende Vorteil, dass in solchen Arbeitsblättern keine Daten enthalten sind, die den Leser nichts angehen… Und das ist auch einer der wenigen Gründe, wo auch ich akzeptiere, dass eigentlich zusammen gehörende Daten in unterschiedliche Arbeitsblätter gesplittet werden. Wichtig ist und bleibt nämlich, dass der eigentliche Datenstamm, die Quelldaten nach wie vor in einer einzigen Auflistung bzw. innerhalb Power Query in einer echten Liste vorliegen und auf der Basis immer wieder neu ausgewertet werden können.

Ein kleines „Schmankerl“ zum Schluss: Natürlich gibt es noch die Liste mit den Abgeordneten im Internet. Die Namen finden Sie hier (auch wenn der Link durchgestrichen ist, einfach einmal versuchen). Diese Seite lässt sich mit relativ wenig Aufwand mit Bordmitteln des Excel (einschließlich Power Query) zu der Liste umwandeln, welche Sie zu Beginn geladen haben. Die Daten sind nun allerdings aktueller.

▲ nach oben …

Übersicht der Beiträge Power Query Einstieg
Lerneinheit 1 (1) Web-Abfragen mit Power Query – Teil 1
Lerneinheit 1 (2) Web-Abfragen mit Power Query – Teil 2
Lerneinheit 2 Grundlegende Menü-Elemente Kurzreferenz
Lerneinheit 3 Filtern und teilen
Lerneinheit 4 Text-basierte Files importieren


Bundesweite  ✉ Schulungen ✉  durch unseren Sponsor GMG Computer-Consulting

 
Dieser Beitrag wurde unter Filtern & Sortieren, Power Query, PQ-Basics, Spalten bearbeiten, Verschiedenes abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.