Wenn du eine aktuelle Excel-Version im Einsatz hast (Excel 2021, Excel 2024 oder Microsoft 365), dann kennst du vielleicht die dort verfügbare FILTER-Funktion. Falls nicht, empfehle ich dir, mal einen Blick in diesen Artikel zu werfen: Eine Funktion liefert alle Treffer.
Denn damit kann man eine Tabelle sehr bequem nach einem beliebigen Kriterium filtern und die gefilterten Ergebnisse an anderer Stelle ausgeben lassen. Allerdings wirft die FILTER-Funktion immer sämtliche Spalten der Quelltabelle aus oder zumindest nur einen zusammenhängenden Spaltenbereich.
Was aber, wenn ich vielleicht nur ganz bestimmte Spalten in meiner Ausgabeliste benötige, die eben keinem zusammenhängenden Bereich entstammen?
Wie das geht, zeige ich in diesem Artikel.
Beispieldatei herunterladen
Die „normale“ FILTER-Funktion
Um das Problem zu verdeutlichen, habe ich eine kleine Adressenliste erstellt. Aus dieser Liste möchte ich nun alle Datensätze ausgeben, die dem Namen in Zelle G4 entsprechen:
Die Beispieltabelle
Die Filterfunktion kennt drei Parameter:
=FILTER(Matrix;Einschließen;Wenn_leer)
In meinem konkreten Beispiel sieht das also so aus:
=FILTER(A4:E33;B4:B33=G4;"")
Mit FILTER werden Daten aus der Quelltabelle gefiltert
Dabei kann man im ersten Parameter „Matrix“ den Bereich angeben, den man in der gefilterten Tabelle ausgeben möchte. Im Beispiel oben habe ich alle Spalten A:E angegeben, denkbar wäre aber auch eine einzelne Spalte oder ein kleinerer zusammenhängender Spaltenbereich:
Ein kleiner Spaltenbereich wird ausgegeben
Aber es kann leider kein nicht zusammenhängender Spaltenbereich angegeben werden, um zum Beispiel nur Vorname, Straße und Ort auszugeben.
Mit einem Trick geht es doch…
Die SPALTENWAHL macht’s möglich
Um dieses Kunststück zu bewerkstelligen, greife ich auf eine weitere, relativ unbekannte Funktion zurück: SPALTENWAHL.
Wie der Name schon vermuten lässt, gib sie eine bestimmte Anzahl an Spalten aus einem Bereich oder Array zurück:
=SPALTENWAHL(Array;Spalte1;[Spalte2];[Spalte3]...)
Das Array markiert den Quelldatenbereich, danach können die Nummern der gewünschten Ausgabespalten angegeben werden. Auch bei dieser Funktion handelt es sich um eine sogenannte dynamische Arrayfunktion. Das heißt, die Ergebnisse dehnen sich soweit aus, wie eben Platz benötigt wird.
Um mit dieser Funktion die Spalten für Vorname (= Spalte 1), Straße (= Spalte 3) und Ort (=Spalte 5) aus meiner Quelltabelle auszugeben, würde das so aussehen:
=SPALTENWAHL(A3:E33;1;3;5)
Die Funktion SPALTENWAHL
Ersetzt man jetzt den ersten Parameter für das Array durch die FILTER-Funktion, hat man praktisch schon die Lösung:
=SPALTENWAHL(FILTER(A4:E33;B4:B33=G4;"");1;3;5)
SPALTENWAHL und FILTER werden kombiniert
Um andere Spalten auszugeben, müssen nur die Spaltennummern in der Funktion ausgetauscht werden.
Flexible Auswahl der Spalten
Wer es etwas komfortabler möchte, kann sich die Spaltennummern auch dynamisch berechnen lassen. In diesem Szenario können in der grünen Kopfzeile im Bereich von I3:K3 einfach die gewünschten Spalten per Dropdown ausgewählt werden. Dazu habe ich folgende Datenprüfung eingerichtet:
Datenprüfung für die Kopfzeile einrichten
Somit kann ich für alle drei Ausgabespalten gezielt auswählen, welche Daten ich sehen möchte. In der Zeile darüber ermittle ich mit Hilfe der VERGLEICH-Funktion die passende Spaltennummer:
=VERGLEICH(I3;$A$3:$E$3;0)
VERGLEICH liefert die Spaltennummer zum ausgewählten Feld
Und zu guter Letzt verweise ich in meiner SPALTENWAHL-Formel auf diese Zeile mit den Spaltennummern:
=SPALTENWAHL(FILTER(A4:E33;B4:B33=G4;"");I2:K2)
Die angepasste Formel
Wird nun irgendwo in der Kopfzeile eine andere Spalte ausgewählt, passt sich die Ausgabe automatisch an:
Dynamische Auswahl der Spaltenköpfe
Ziemlich praktisch, oder? Aber es geht noch besser.
Die ultimative Flexibilität bei der Spaltenwahl
Schalten wir noch einen Gang höher. In diesem Szenario möchte ich nicht nur eine flexible Auswahl in diesen drei Spalten haben, sondern ich möchte auch noch wählen, wie viele Spalten überhaupt angezeigt werden sollen!
Da meine einfache Quelltabelle nur 5 Spalten enthält, beschränke ich mich bei der Ausgabetabelle auch auf maximal 5 Spalten und erweitere meine Datenprüfung in der Kopfzeile auf die Bereiche L3 und M3.
Wenn in diesen Feldern noch keine Spalte ausgewählt wurde, dann liefert die darüber liegende VERGLEICH-Funktion jedoch einen #NV-Fehler. Und in der Folge gibt auch die SPALTENWAHL/FILTER-Formel nichts zurück:
VERGLEICH liefert einen Fehler
Wir müssen also die Fehlerwerte loswerden. Die naheliegende WENNFEHLER-Funktion hilft hier leider nicht weiter. Damit lassen sich die Fehler zwar durch einen leeren Eintrag ersetzen, aber das bringt auch keine Verbesserung. Denn die SPALTENWAHL/FILTER-Formel greift ja trotzdem auf alle 5 Zellen in I2:M2 zu:
WENNFEHLER hilft nicht weiter
Die Lösung führt über eine weitere Funktion: ZUZEILE
Ja, vielleicht hast du auch von dieser Funktion bisher noch nie gehört. Sie wird normalerweise dazu verwendet, um Werte aus einer Spalte in einer Zeile auszugeben:
ZUZEILE dreht eine Spalte in eine Zeile
Handelt es sich beim angegebenen Bereich bereits um eine Zeile, wird diese unverändert ausgeben…
Ohne weitere Angaben werden auch Fehlerwerte ausgegeben
…sofern man nicht einen zweiten Parameter angibt:
Der zweite Parameter erlaubt Einschränkungen
Hier kann ich nämlich festlegen, dass Fehlerwerte ignoriert werden sollen. Und damit ist der zurückgelieferte Bereich auch entsprechend kürzer:
Option 2 unterdrückt Fehlerwerte
Nun muss ich nur noch in unserer Ausgangsformel diese ZUZEILE-Funktion einfügen und schon funktioniert’s:
Die voll dynamisierte Ausgabetabelle
Ach ja:
Wer sich die VERGLEICH-Funktion in Zeile 2 sparen möchte, kann alles zusammen in eine einzige Formel packen:
=SPALTENWAHL(FILTER(A4:E33;B4:B33=G4;"");ZUZEILE(VERGLEICH(I3:M3;A3:E3;0);2))
Fazit
An diesem Beispiel lassen sich vor allem zwei Dinge erkennen:
Die dynamischen Array-Funktionen an sich sind sehr leistungsfähig und ermöglichen Dinge, die in älteren Excel-Versionen entweder überhaupt nicht oder nur sehr umständlich zu bewerkstelligen waren.
Ihre volle Stärke spielen sie aber dann aus, wenn man sie geschickt miteinander kombiniert.
Ich hoffe, dass ich mit diesem Artikel ein wenig deine Experimentierfreude geweckt habe 🙂
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.
Super! Und mit bedingter Formatierung könnte auch noch die Zeile mit den Spaltenüberschriften dynamisch gemacht werden!
Hallo Andreas,
guter Hinweis, das macht das Ergebnis nochmal runder!
Schöne Grüße,
Martin
Hallo, die „Erweiterte Filterfunktion“ lässt doch auch zu Tabellen zu Filtern unter Verwendung eines Kriterienbereichs und eines Ausgabebereichs. Hier kann ich im Ausgabebereich direkt angeben welche Spalten ich in welcher Reihenfolge haben möchte, völlig Flexibel. Zusätzlich kann ich auch noch berechnete Spalten dazunehmen und anschließend die Ausgabe-Tabelle weiter bearbeiten. Allerdings ist das statisch und muss immer neu vorgenommen werden.
DATEN – „Sortieren und Filtern“ – Erweitert – Tabelle an einer anderen Stelle filtern.
Im Kriterienbereich kann mann UND und/oder ODER Kritereien angeben wenn man den Kriterienbereich mehrzeilig definiert. Das ganze geht auch mit deutlich älteren Excel-Versionen und hieß früher Spezialfilter.
Oder liege ich hier falsch.
Hallo Matthias,
danke für die Ergänzung, du liegst vollkommen richtig. Die beschriebene Funktion führt am Ende zum gleichen Ergebnis und ist auch in alten Excel-Versionen verfügbar. Nur die Dynamik gibt es halt damit nicht.
Schöne Grüße,
Martin