Filter-Ausgabe für nicht zusammenhängende Spalten 6

Mit einem Trick können bei der FILTER-Funktion beliebige Spalten ausgegeben werden.
 

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 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

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

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

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

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

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

Noch mehr Dynamik in der SEQUENZ-Funktion

Zugegeben, keine unbedingt naheliegende und einfache Lösung. Aber auf jeden Fall eine hilfreiche!

 

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.



Schreibe einen Kommentar zu Chris Antworten abbrechen

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

6 Gedanken zu “Filter-Ausgabe für nicht zusammenhängende Spalten

  • Magdalena Teel

    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.

    • Mark

      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.

  • Oliver

    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.

    • Martin Weiß Autor des Beitrags

      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