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

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.



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

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

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

  • Avatar-Foto
    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.

    • Avatar-Foto
      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.

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sebastian

    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

    • Avatar-Foto
      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 🙂

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    Achim Hoppius

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Achim Hoppius

        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

  • Avatar-Foto
    Fabian

    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

    • Avatar-Foto
      Martin Weiß

      Hallo Fabian,

      bei Google Sheets kann ich leider nicht weiterhelfen, meine „Baustelle“ ist einzig und allein Excel.

      Schöne Grüße,
      Martin