Wenn du Excel aus Microsoft 365/Office 365 im Einsatz hast, dann kennst du vielleicht die dort verfügbare FILTER-Funktion. Falls nicht, empfehle ich dir, mal einen Blick in diesen Artikel zu werfen: https://www.tabellenexperte.de/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 ist, wenn ich vielleicht nur ganz bestimmte Spalten in meiner Ausgabeliste benötige, die eben keinem zusammenhängenden Bereich entstammen?
Wie das geht, zeigt der heutige Artikel:
Die „normale“ FILTER-Funktion
Gleich zu Beginn noch ein Wort der Warnung: Wenn du eine andere Excel-Version als Microsoft 365/Office 365 einsetzt, steht dir die FILTER-Funktion leider nicht zur Verfügung.
Um das Problem zu verdeutlichen, habe ich eine kleine Adressenliste erstellt (die Datei kannst du dir hier herunterladen). Aus dieser Liste möchte ich nun alle Datensätze ausgeben, die dem Namen in Zelle G4 entsprechen:
Die Ausgangslage
Die Filterfunktion kennt drei Parameter:
=FILTER(Matrix;Einschließen;Wenn_leer)=FILTER(A4:E33;B4:B33=G4;"")
FILTER gibt sämtliche Spalten zurück
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:
Alternativ: zusammenhängender Spaltenbereich
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…
Nicht zusammenhängender Filterbereich
…allerdings nur in Kombination mit ein paar weiteren Funktionen. Gesehen habe ich diesen coolen Trick bei Leila Gharani, Ruhm und Ehre gebühren also ihr ganz alleine 🙂
Dieser Trick greift auf die INDEX-Funktion zurück, die wir anstelle des ersten Parameters für die Matrix verwenden. Der Rest der Formel bleibt unverändert.
=FILTER(INDEX(A4:E33;SEQUENZ(30);{1.3.5});B4:B33=G4;"")
FILTER kombiniert mit INDEX
Was genau passiert hier?
Als Matrix wird wie gehabt der komplette Tabellenbereich der Datentabelle angegeben. Für den Zeilenparameter hingegen kommt die SEQUENZ-Funktion zum Einsatz. Diese liefert in der einfachsten Form eine Liste mit fortlaufenden Zahlen. In meinem Beispiel habe ich an die SEQUENZ den Wert 30 übergeben, daher liefert die Funktion ein Array mit den Zahlen von 1 bis 30 zurück. Gib einfach mal nur die SEQUENZ-Funktion in eine separate Zelle ein:
SEQUENZ liefert fortlaufende Zahlen
Und warum 30? Weil mein Datenbereich insgesamt 30 Zeilen umfasst.
Ok, soweit so gut. Aber was hat es mit dem merkwürdigen Spaltenparameter {1.3.5} auf sich?
Exkurs:
Dabei handelt es sich um eine sogenannte Matrixkonstante. Üblicherweise verwendet man in Formeln Bezüge auf Zellen, welche dann einen Wert enthalten, also zum Beispiel =SUMME(A1:A10). Man kann aber auch konstante Werte in Form einer Matrix übergeben. Dazu schreibt man die Werte in geschweifte Klammern. Werte innerhalb einer Zeile werden mit einem Punkt getrennt, Werte innerhalb einer Spalte mit einem Semikolon.
So ergibt die Eingabe von…
={1.2.3.4;5.6.7.8;9.10.11.12}
…eine Matrix mit 3 Zeilen und 4 Spalten.
Matrixkonstante mit 3 Zeilen und 4 Spalten
In unserem einfachen Beispiel verwende ich ein einzeiliges Array mit drei Elementen, welches die Spalten 1, 3 und 5 anspricht.
Also Vorname, Straße und Ort. Und genau das ist es, was wir in der Ausgabetabelle wollen. Wenn du stattdessen nur gerne Vorname und PLZ hättest, dann gibst du einfach {1.4} als Spaltenparameter an.
Ziemlich verblüffend, oder?
Anstelle des fixen Wertes 30 könntest du die Zeilenanzahl auch dynamisch berechnen, zum Beispiel mit ANZAHL2
=FILTER(INDEX(A4:E33;SEQUENZ(ANZAHL2(A4:A33));{1.3.5});B4:B33=G4;"")
oder mit der ZEILEN-Funktion
=FILTER(INDEX(A4:E33;SEQUENZ(ZEILEN(A4:A33));{1.3.5});B4:B33=G4;"")
Noch mehr Dynamik in der SEQUENZ-Funktion
Zugegeben, keine unbedingt naheliegende und einfache Lösung. Aber auf jeden Fall eine hilfreiche!
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,
ich finde deinen Newsletter echt super.
Ich bin leider nicht der Profi, aber deine Beiträge, nicht alle, sind sehr interessant für mich.
Ich hab da noch eine Frage, die mir mtl. das Leben viel leichter machen würde.
Ist es möglich vorhandenen Teilergenissen, zusätzliche in leere Zellen, innerhalb der Zeile eine fortlaufende Nummer zu geben, ohne vorhandenen Inhalt zu verändern?
Ich habe da eine Tabelle mit z. Bsp. über 1000 Zeilen und ca. 300 Teilergebnissen. Diese Teilergebnisse möchte ich, für die Vorbereitung der EDV Eingabe mit lfd. Nummern ergänzen, damit jeder Beleg eindeutig zugeordnet werden kann. Bisher muss ich diese Nummern immer manuell dazufügen, da ich es nicht besser kann.
Wäre prima, wenn du mir hierfür ein Lösung hättest.
Moin.
Folgende Idee, sofern eine einfache, fortlaufende Nummerierung ausreicht:
– Zeile 1 stehen die Überschriften
– Spalte A wird hochgezählt
– Spalte B steht das Kiterium, nach dem die Teilergebnisse gruppiert sind
Wenn die Teilergebnisse durch Excel gebildet wurden, folgende Formel in Zelle A2 einfügen und nach unten kopieren: =WENN(RECHTS(B2;8)=“Ergebnis“;ZÄHLENWENN($B$1:B2;“*“&“Ergebnis“);““)
Sobald in Spalte B der Text „Ergebnis“ steht, zählt die Formel hoch, das wie vielte „Ergebnis“ in der jeweiligen Zeile steht. So hat jedes Teilergebnis, aber auch am Ende das Gesamtergebnis, eine eindeutige, fortlaufende Nummer.
geht das nicht mit Pivot viel einfacher?
und b.t.w.: Ich liebe deine Seite und feiere dein Wissen! Danke für die permanent gute Arbeit.
Hallo Oliver,
danke für dein tolle Feedback. Ja, das geht natürlich auch mit Pivot, die gezeigte Formellösung ist nur eine mögliche Variante. Wie so oft führen in Excel viele Wege ans Ziel 🙂
Schöne Grüße,
Martin
Danke für die Hinweise. Nicht zusammenhängende Spalten können auch durch doppelte Anwendung der Filterfunktion ausgegeben werden. Auch dazu hat Leila ein sehenswertes Video veröffentlicht:
https://youtu.be/Onudkw9DMlU
Grüße
Chris
Hallo Chris,
danke für die Ergänzung.
Schöne Grüße,
Martin
Danke für die tolle Lösung! Bei kniffligen Fragestellungen lande ich am Ende meist bei Dir!
Hallo Dirk,
das hört man gerne!
Schöne Grüße,
Martin
Hallo.
Ich nutzte inzwischen meisten auch die doppelte Filter-Funktion, damit ich die die Ermittlung der Zeilen sparen kann.
Nun stoße ich auch eine neue Herausforderung…
Ist es eigentlich möglich den Bereich der auszugebenden Spalten dynamisch zu ermitteln?
Bezogen die Lösung in diesem Artikel lautet meine Formel:
=FILTER(INDEX(MA;SEQUENZ(ANZAHL2(MA[ID]));{3.4.5.6.7.8.9.10.11.15.16});MA[Status]=“aktiv“)
oder, da ich immer den doppelten Filter nehme:
=FILTER(FILTER(MA;MA[Status]=“aktiv“);{0.0.1.1.1.1.1.1.1.1.1.0.0.0.1.1.0.0})
Die Kollegen fügen jedoch gerne mal neue Spalten in der Ausgangstabelle hinzu, so dass in meiner Formel ebenfalls ein Spalte hinzugefügt, geändert oder entfernt werden muss. Kann ich also statt „{3.4.5.6.7.8.9.10.11.15.16}“ zu tippen, mich irgendwie auf etwas beziehen, was ich dann dynamisch gestalten kann?
Ich hatte schon mit der Funktion Indirekt versucht in die Zelle „{…}“ reingeschrieben. Da es aber dann nur als Text erkannt wird, bin ich kläglich gescheitert.
Geht das überhaupt, was ich da versuche?
Danke+Gruß
Sebastian
Ich nochmal…
Habe es doch noch selbst geschafft.
In einem Tabellenblatt für alle möglichen Verweise habe ich einen Array der Kopfzeilen der Tabelle MA eingefügt, also eine Zeile.
In Zeile 2 habe ich einen Array mit meiner Angabe geschrieben: ={0.0.1.1.1.1.1.1.1.1.1.0.0.0.1.1.0.0}
Den Array in Zeile 2 (z.B. $A$2#) habe ich noch mit dem Namen „Spalten“ definiert.
Jetzt sieht meine vollständige Formel wie folgt aus: =FILTER(FILTER(MA;MA[Status]=“aktiv“);Spalten)
Ist zunächst einmal deutlich kürzer und ich muss bei neuen Spalten oder sonstigen Änderungen nicht mehr 3^4x die Spalten neu angeben, sondern nur noch einmal.
Manchmal muss ein Problem nur nervig genug sein, um die Motivation zu haben die Lösung zu finden 🙂
Hallo Sebastian,
freut mich zu hören, dass du selbst eine Lösung gefunden hast. Die Variante mit dem doppelten Filter und einem separat gepflegten Array gefällt mir gut!
Schöne Grüße,
Martin
Hallo, Martin!
Ich bin Office 365-Nutzer und sehr dankbar, auf diesen Deinen Artikel gestoßen zu sein.
Hoffnungsvoll habe ich Deine Formel nutzen wollen und akribisch auf alle von Dir gesetzten Zeichen und Klammern geachtet. Trotz aller Genauigkeit ergab die Formel bei mir immer einen #WERT-Fehler.
Ich habe mir dann die Mühe gemacht und das Video der von Dir zitierten Leila Gharani angesehen. Ich war überrascht, dass die statt Deiner Formel eine doppelte FILTER-FUNKTION nutzt und nicht gewünschte Spalten mit der Zahl 0 ausschließt. Die dort vorgestellte Formel mit der doppelten Filterfunktion lautet bei mir nun so:
=FILTER(FILTER(C17:Q66;Q17:Q66>1);{1.0.0.0.0.0.0.0.0.0.0.0.0.0.1})
Und die funktioniert anstandslos, selbst wenn ich die ZS-Notation durch die entsprechenden Bezeichnungen der von mir genutzten intelligenten Tabelle ersetze.
Ich habe keine Ahnung, wieso Deine Formel bei mir nicht funktioniert hat.
Trotzdem vielen Dank für diesen Artikel und die vielen anderen, die mir schon oft geholfen haben.
Beste Grüße
Achim
Hallo Achim,
danke für deinen Kommentar. Ich wollte nicht einfach die Lösung von Leila 1:1 abschreiben, daher meine leicht abgewandelte Version. Warum diese bei Dir nicht funktioniert, kann ich aus der Ferne leider nicht beurteilen. Hast Du Dir die Beispieldatei heruntergeladen, die im Artikel verlinkt ist? Vielleicht findest Du ja da noch einen Hinweis.
Schöne Grüße,
Martin
Hallo, Martin!
Ich habe nun die von Dir oben angebotene Datei heruntergeladen:
1. Diese Datei „arbeitet“ in meiner Umgebung fehlerfrei!
2. Ich habe per Copy & Paste Deine Formel in meine Datei eingefügt und die erforderlichen Änderungen durch Überschreiben vorgenommen – und siehe da: Alles funktioniert wie es soll. Das kann doch nur bedeuten, dass ich trotz allen Kontrolllesens und Überprüfens irgendeinen Tippfehler gemacht und übersehen habe. Sorry, dass ich Dir diesen Umstand bereitet habe.
Achim
Vielen Dank erstmal dafür, die Funktion ist grundsätzlich genial. In Excel funktioniert die Ausgabe auch wie gewünscht – ich nutze teilweise aber Google Sheets. Die „normale“ Filterfunktion klappt dort auch, aber die Ausgabe über nicht zusammenhängende Spalten leider nicht. Hat da jemand eine Idee?
Danke
Hallo Fabian,
bei Google Sheets kann ich leider nicht weiterhelfen, meine „Baustelle“ ist einzig und allein Excel.
Schöne Grüße,
Martin