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:
- Der Original-Artikel: Besser als SVERWEIS: Alle Werte finden
- Alternative 1: FILTER-Funktion (nur Office 365)
- Alternative 2: Der Spezialfilter
- Alternative 3: Eine Pivot-Tabelle
- Alternative 4: Power Query – davon handelt der heutige Artikel
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
Wähle ein beliebiges Tabellendesign aus und kontrolliere danach noch kurz das folgende Fenster:
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
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 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
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
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
Danach kannst du das Filterfenster mit OK schließen und es wird die nun gefilterte Länderliste angezeigt:
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…
Ä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
Nach einem Klick auf OK wird jetzt die auf Brasilien gefilterte Länderliste angezeigt:
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
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
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
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
Übriggeblieben ist nun das etwas verloren wirkende Wort „China“:
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
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
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
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
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…
…und wähle diesmal die Option „Nur Verbindung erstellen“:
…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 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
Und nach einem Wimpernschlag erscheint wie von Zauberhand die gewünschte Liste:
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.
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.
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
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
Total interessant, aber auch nicht Ohne! Zum Glück, ist da meine Frau verhältnismäßig fit. Ich glaube alleine wäre ich bei dieser Thematik auf verlorenem Posten.
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
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