Alle Treffer ausgeben: Mit Power Query 5

Und noch eine Variante, um eine Tabelle mit einem dynamischen Filter an anderer Stelle auszugeben
 

Heute gibt es den vorerst letzten Teil einer kleinen Artikelserie, die sich um ein gängiges Problem dreht: Wie kann ich aus einer vorhandenen Tabelle einen gefilterten Auszug an einer anderen Stelle ausgeben.

Den Abschluss bildet in diesem Artikel eine Lösungsvariante, in der ich Power Query eingesetzt habe. Denn dieses extrem vielseitige Tool ist geradezu prädestiniert für solche Aufgabenstellungen.

Und so geht’s:

Falls du direkt auf dieser Seite eingestiegen bist, gibt es der Vollständigkeit halber noch einen kurzen Überblick über die anderen Artikel dieser kleinen Serie:

Power Query? Noch nie gehört!

Sollte das tatsächlich bei dir der Fall sein, empfehle ich dir unbedingt, dich mit Power Query zu beschäftigen. Wenn es nur eine einzige Sache gäbe, die ich einem gestandenen Excel-Anwender ans Herz legen müsste, dann wäre das Power Query! Vielleicht wäre ja mein Online-Einsteiger-Kurs Daten importieren und aufbereiten mit Power Query etwas für dich.

Kleine Vorarbeiten

Um dir die Tipparbeit zu ersparen, kannst du dir hier die Beispieldatei herunterladen (die fertige Lösung mit den Abfragen findest du in dieser Datei).
Damit wir unsere Länderliste mit Power Query verarbeiten können, wandeln wir sie zunächst in eine formatierte Tabelle um. Stelle dazu die aktive Zelle irgendwo in die Länderlist und rufe dann das Menü „Start | Als Tabelle formatieren“ auf:

Die Liste als Tabelle formatieren

Die Liste als Tabelle formatieren


Wähle ein beliebiges Tabellendesign aus und kontrolliere danach noch kurz das folgende Fenster:
Der Datenbereich wird automatisch erkannt

Der Datenbereich wird automatisch erkannt


Da es sich um eine zusammenhängende Liste handelt, hat Excel gleich den kompletten Datenbereich erkannt und auch das Häkchen „Tabelle hat Überschriften“ gesetzt. Wenn das auch bei dir der Fall ist, kannst du das Fenster mit Klick auf „OK“ schließen.

Tipp:
Tastaturfreunde können – anstatt den Umweg mit der Maus über die Menüleiste zu nehmen – einfach mit Strg+T die Liste in eine formatierte Tabelle umwandeln. Dann wird auch gleich ein Standardlayout verwendet.

Außerdem empfehle ich dir, den standardmäßigen Tabellennamen „Tabelle1“ durch einen aussagekräftigen Namen zu ersetzen, wie zum Beispiel „Länderliste“:

Einen aussagefähigen Namen vergeben

Einen aussagefähigen Namen vergeben

Das ist natürlich nicht zwingend notwendig. Wenn du aber später einmal mehrere solcher Tabellen einsetzt, erleichtert das die Orientierung ungemein.
Als nächstes brauchen wir noch einen Eingabebereich, in den wir das gewünschte Land eintragen können. Gib dazu in einer freien Zelle neben der Länderliste „Land:“ ein (wichtig: Lass dazu mindestens eine Spalte neben der Länderliste frei, ansonsten wird deine Zelle gleich in die formatierte Tabelle integriert).
Dann färbe ich zur besseren Übersicht noch die eigentliche Eingabezelle rechts daneben ein und vergebe für diese Zelle den Namen „Eingabe“. Klicke dazu einfach in das Namensfeld links oberhalb der Tabelle und tippe dort direkt den Namen ein:

Das Eingabefeld benennen

Das Eingabefeld benennen


Das war’s dann auch schon mit den Vorarbeiten.

Jetzt kommt Power Query!

Stelle jetzt die aktive Zelle wieder irgendwo in die Länderliste und klicke im Menü „Daten“ auf die Schaltfläche „Aus Tabelle/Bereich“:

Daten mit Power Query abrufen

Daten mit Power Query abrufen


Nach wenigen Augenblicken wird sich ein neues Fenster mit dem Power Query-Editor öffnen, in dem die Länderliste angezeigt wird:
Die Tabelle im PQ-Editor

Die Tabelle im PQ-Editor


Um jetzt nach einem bestimmten Land zu filtern, musst du einfach nur das Auswahlfeld neben in der Länderspalte öffnen (1), das Häkchen vor „(Alles auswählen“) entfernen (2) und dann das gewünschte Land anklicken (3). In meinem Beispiel habe ich Brasilien gewählt:
Einen manuellen Filter setzen

Einen manuellen Filter setzen


Danach kannst du das Filterfenster mit OK schließen und es wird die nun gefilterte Länderliste angezeigt:
Die gefilterte Länderliste

Die gefilterte Länderliste

Soweit so gut. Diese gefilterte Liste wollen wir jetzt nach Excel zurückliefern. Klicke dazu im Start-Menü auf den unteren Teil der Schaltfläche „Schließen & laden“ und wählen dann den Punkt „Schließen & laden in…“

Abfrage schließen und laden in...

Abfrage schließen und laden in…


Ändere in dem neuen Fenster die Einstellungen so, dass die Daten in das bestehende Arbeitsblatt neben die vorhandene Tabelle geladen werden:
Abfrage in bestehendes Arbeitsblatt laden

Abfrage in bestehendes Arbeitsblatt laden


Nach einem Klick auf OK wird jetzt die auf Brasilien gefilterte Länderliste angezeigt:
Die manuell gefilterte Länderliste

Die manuell gefilterte Länderliste

Da wir aber für eine flexible Lösung das Eingabefeld in Zelle F1 nutzen wollen, sind wir noch nicht ganz fertig.

Tippe testweise ein anderes Land in F1 (oder welche Zelle du als Eingabezelle festgelegt hast), zum Beispiel China. Stelle danach die aktive Zelle wieder in das Feld F1 und rufe nun wieder das Menü „Daten | Aus Tabelle/Bereich“ auf:

Das Eingabefeld mit Power Query abrufen

Das Eingabefeld mit Power Query abrufen


Wichtig ist nur, dass du dabei auf der Eingabezelle stehst, bevor du das Menü aufrufst. Jetzt wird wieder der Power Query-Editor geöffnet, diesmal natürlich erwartungsgemäß deutlich übersichtlicher.
Automatisch angewendete Schritte entfernen

Automatisch angewendete Schritte entfernen

Schauen wir uns nun im rechten Teil des Fensters die Liste der angewendeten Schritte an. Power Query hat automatisch zwei zusätzliche Schritte „Geänderter Typ“ und „Höher gestufter Header“ eingefügt. Diese beiden Schritte brauchen wir aber nicht und löschen sie mit jeweils einem Klick auf das rote Kreuz vor dem jeweiligen Schritt, so dass am Ende nur noch der Eintrag „Quelle“ vorhanden ist:

Übrig bleibt nur die Quelle

Übrig bleibt nur die Quelle

Nun führst du einen Rechtsklick auf die Zelle mit dem Eintrag „China“ aus und wählst im Kontextmenü „Drilldown ausführen“:

Einen Drilldown ausführen

Einen Drilldown ausführen


Übriggeblieben ist nun das etwas verloren wirkende Wort „China“:
Das aktuell gewählte Land

Das aktuell gewählte Land


Wenn du jetzt am linken Fensterrand den Navigatorbereich mit den Abfragen einblendest, siehst du zwei Einträge: Die Länderliste und die gerade erstellte Abfrage namens „Eingabe“:
Der Navigationsbereich zeigt zwei Abfragen

Der Navigationsbereich zeigt zwei Abfragen

Jetzt kommt die Flexibilität ins Spiel!

Markiere nun die Abfrage „Länderliste“ und werfe dann einen Blick in die Bearbeitungsleiste oberhalb des Tabellenbereichs. Sollte diese Zeile bei dir nicht angezeigt werden, kannst du sie im Menü „Ansicht | Bearbeitungszeile“ einblenden:

Die generierte M-Funktion in der Bearbeitungsleiste

Die generierte M-Funktion in der Bearbeitungsleiste


Was man hier sieht, erinnert vom Aufbau her ein wenig an eine Excel-Formel. Es handelt sich dabei im die interne Abfragesprache „M“. Hinter jedem der angewendeten Schritte in Power Query verbirgt sich eine mehr oder weniger komplexe M-Funktion. Keine Angst, du musst jetzt keine neue Formelsprache lernen und die Details interessieren uns hier überhaupt nicht.

Was jedoch leicht zu erkennen ist, ist in doppelte Anführungszeichen gesetzte Land „Brasilien“. Das ist nämlich der Filter, den wir ganz zu Beginn manuell gesetzt hatten. Und diesen statischen Eintrag tauschen wir jetzt aus und setzten stattdessen den Namen unserer zweiten Abfrage rein, nämlich „Eingabe“.

Der statische Ländername wird ersetzt durch die Abfrage

Der statische Ländername wird ersetzt durch die Abfrage


Klicke einfach in die Bearbeitungszeile und lösche das Wort Brasilien und die beiden Anführungszeichen. Danach tippe das Wort „Eingabe“ (ohne Anführungszeichen!) ein. Achte nur darauf, dass die beiden schließenden Klammern erhalten bleiben.

Wenn du dich nicht vertippt hast, sollte das Ergebnis so aussehen:

Gleiches Ergebnis, aber dynamisch

Gleiches Ergebnis, aber dynamisch


Glückwunsch: Du hast damit gerade eine Variable in eine M-Funktion eingebaut!

Rufe jetzt im Start-Menü wieder die Funktion „Schließen & Laden in…“ auf:

Die Abfrage wird geschlossen...

Die Abfrage wird geschlossen…


…und wähle diesmal die Option „Nur Verbindung erstellen“:
...und nur als Verbindung eingerichtet

…und nur als Verbindung eingerichtet


Damit erreichen wir, dass für die Mini-Abfrage „Eingabe“ nicht ein neues Arbeitsblatt angelegt, sondern sie nur im Hintergrund gehalten wird – eben als Verbindung:
Die Ergebnistabelle und die beiden Abfragen

Die Ergebnistabelle und die beiden Abfragen

Die Probe auf’s Exempel

Nun wollen wir natürlich testen, ob unsere flexible Abfrage wirklich funktioniert. Gib dazu ein anderes Land ins Eingabefeld ein, z.B. Deutschland. Danach führst Du einen Rechtsklick irgendwo in der Ausgabeliste aus und wählst den Eintrag „Aktualisieren“:

Nach einer Neueingabe wird die Liste aktualisiert

Nach einer Neueingabe wird die Liste aktualisiert

Und nach einem Wimpernschlag erscheint wie von Zauberhand die gewünschte Liste:

Die Abfrage funktioniert!

Die Abfrage funktioniert!

Wie du siehst, muss man sich mit Power Query nur ein einziges Mal die Arbeit machen und kann sich danach mit einem Mausklick über die neuen Ergebnisse freuen.
 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Jörg Strobel Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

5 Gedanken zu “Alle Treffer ausgeben: Mit Power Query

  • Avatar-Foto
    Ralf

    Hallo Martin,
    vielen Dank, das ist eine tolle Lösung, genau das was ich gerade brauche.
    Wie kann ich jetzt die Auswahlkriterien erweitern, also suchen von 2 Ländern?
    Gruß Ralf

    • Avatar-Foto
      Martin Weiß

      Hallo Ralf,

      die Herangehensweise ist in diesem Fall etwas anders. Kurz gesagt würde man statt eines einzelnen Feldes eine eigene formatierte Tabelle anlegen, in die alle gesuchten Länder eingetragen werden. Diese Tabelle wird dann in Power Query eingelesen und über einen Join mit der Ländertabelle verknüpft werden (Stichwort „Anfragen zusammenführen“). Wie so etwas im Detail gemacht wird, kannst Du in den am Anfang des Artikels verlinkten Einführungen nachlesen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Gordon

    Da in meiner Arbeits-Excel-Version die Funktion „FILTER“ noch nicht existiert, habe ich meine Herausforderung mit der hier beschriebenen Methode mit Power Query gelöst und bin ansich ganz zufrieden. Allerdings hab ich mit Power Query vorher noch garnicht gearbeitet und müsste mir das wohl mal näher ansehen.
    Meine Fragen zum Artikel sind jetzt folgende:
    1. Gibt es eine Möglichkeit, dass die Aktualisierung irgendwie automatisch mit Änderung des Eingabefeldes durchgeführt wird?
    2. Wenn ich in der Quelltabelle eine Spalte hinzufüge, wie füge ich diese Spalte in der Power Query-Tabelle hinzu und damit auch in der Abfragetabelle?
    Beste Grüße

    • Avatar-Foto
      Martin Weiß

      Hallo Gordon,

      ja, ich kann dich nur dazu ermuntern, dich mit Power Query näher zu beschäftigen. Es lohnt sich auf jeden Fall. Zu deinen Fragen:
      Eine automatische Aktualisierung bei Änderung des Eingabefeldes ist leider nicht möglich (zumindest nicht ohne VBA).
      Wenn in der Quelltabelle eine neue Spalte dazukommt, dann wird sie erst einmal automatisch auch in Power Query eingelesen und auch automatisch in der Ausgabetabelle angezeigt. Das ist nicht immer der Fall, es hängt davon ab, welche Transformationen in Power Query vorgenommen wurden. Aber im Beispiel zu diesem Artikel sollte es funktionieren.

      Schöne Grüße,
      Martin