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

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)

In meinem konkreten Beispiel sieht das also so aus:
=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 zu Martin Weiß Antworten abbrechen

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

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

        Hallo, ich bin auch ganz dankbar für diese Lösung, suche aber noch eine Lösung für folgendes: Über eine Formel, ermittele ich die Spalten, die angezeigt werden und welche nicht. Wenn ich dann auf die Zelle mit dem Ergebnis verweise, übernimmt Excel statt
        {1.1.1.0.1} -> „{1.1.1.0.1}“
        was dann nicht mehr in der Filterformel funktioniert. Hat jemand dafür eine Lösung (wenn ich mich überhaupt verständlich ausgedrückt habe).

  • 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

  • Avatar-Foto
    Claudia Slabke

    Hallo Martin,
    ich habe eben erst die Filter-Funktion für mich entdeckt (besser spät als nie). Aber wie kann ich sie in einem Dropdown nutzen? Das wäre die Lösung meines Problems.
    Viele Grüße
    Claudia

    P.S.: Ein großes Lob für Deinen Blog.

    • Avatar-Foto
      Martin Weiß

      Hallo Claudia,

      erst einmal vielen Dank für das Lob!
      Kannst du vielleicht noch etwas näher beschreiben, was du genau bezwecken möchtest? Soll ein Dropdown-Feld mit Werten erstellt werden, die auf einer gefilterten Liste basieren?
      Falls ja, dann erstelle in einem separaten Tabellenbereich deine gefilterte Liste. Dann legst du über den Namensmmanager einen Namen fest, z.B. ddFilterliste und gibst in dem Feld „Bezieht sich auf“ die erste Zelle der gefilterten Liste an, gefolgt von dem #-Zeichen. Also beispielsweise =$F$1#
      Und diesen Namen verwendest du dann in der Datenprüfung. Achtung: Das funktioniert nur vernünftig, wenn die gefilterte Liste einspaltig ist.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Claudia

        Hallo Martin,

        danke für Deine Antwort.
        Ich erstelle gerade eine Datei, in der von Mitarbeitern in einer Tabelle Verkaufsdaten für Tickets eingegeben werden sollen. Und zwar nur hier. in einer anderen Tabelle „Veranstaltungen“ werden die einzelnen Veranstaltungen mit unterschiedlicher Anzahl von Tickets jeweils mit Block/Reihe/Platz angelegt. In der Eingabetabelle habe ich nun Dropdowns für Block/Reihe/Platz, die auf die Tabelle Veranstaltungen zugreift. Nun möchte ich nur die zu genau dieser Veranstaltung zugehörigen Block/Reihe/Platz-Daten im Dropdown anzeigen.
        Die Mitarbeiter sollen/können keinen separaten Tabellenbereich oder Namen anlegen. Das ginge schiief.

        Viele Grüße
        Claudia

        • Avatar-Foto
          Martin Weiß

          Hallo Claudia,

          was du beschreibst, sind mehrstufige abhängige Dropdown-Felder. Das ist kein ganz triviales Thema, das ich hier schnell mit ein paar Sätzen beschreiben könnte. Ich versuche es trotzdem:
          Du brauchst eine Liste mit allen Veranstaltungen also Hauptdropdown. Für jede dieser Veranstaltungen legst du eine separate Liste der verfügbaren Blöcke an und vergibst für diese Liste den Namen der Veranstaltung. Im Dropdownfeld für die Blöcke arbeitest du dann mit der INDIREKT-Funktion.
          Angenommen, in A5 wird die Veranstaltung ausgewählt, dann lautet die Formel für die Blöcke in der Datenprüfung =INDIREKT(A5).
          Das Ganze geht dann weiter für die Reihen und Plätze. Wieder brauchst du für jeden Block eine Liste der verfügbaren Reihen und vergibst dafür den Namen des Blocks usw usf.

          Wie gesagt, das ist kein triviales Thema und das Verfahren ist auch nicht für jedes Senario geeignet. Aber vielleicht hilft dir das ja ein wenig weiter.

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Claudia

            Hallo Martin,

            vielen Dank!
            Ja, das ist definitiv nicht trivial. Da sich die Liste mit den Veranstaltungen (die habe ich) permanent erweitert und die Mitarbeiter keine Listen anlegen, geschweige denn Namen vergeben können, wird sich das so wahrscheinlich nicht lösen lassen.
            Trotzdem vielen Dank!

            Viele Grüße
            Claudia

  • Avatar-Foto
    Annika

    Hallo Martin,
    vielen Dank für den nützlichen Artikel. Ein Danke geht auch an Leila für den Tipp zu nicht zusammenhängenden Spalten (meine Tabelle hat nämlich sehr viele Spalten, welche nicht bei jedem Filter von Interesse sind).
    Meine Frage: Besteht die Möglichkeit, dass das „Suchfeld“ nicht 1:1 dem Eintrag in der Suchspalte entsprechen muss, sondern nur nach“enthält “ sucht?
    Beispiel: In einem Fall stehen in der Tabelle Namen in der Form . Teilweise sind die Namen sehr lang (Doppelnamen, Bindestriche, ungewöhnliche Schreibweisen etc.). Es wäre also hilfreich, wenn ich bereits Ergebnisse bekomme wenn ich nur einen Teil eingebe, z.B den Nachnamen.

    • Avatar-Foto
      Martin Weiß

      Hallo Annika,

      ja, das geht. Dazu muss man allerdings die Formel noch etwas aufbohren. Bezogen auf mein Beispiel oben aus dem Artikel:
      =FILTER(INDEX(A4:E33;SEQUENZ(ZEILEN(A4:A33));{1.3.5});ISTZAHL(SUCHEN(G4;B4:B33));““)

      Das heißt, für das Kriterieren-Argument wird eine Kombination aus ISTZAHL und SUCHEN verwendet. Wenn du genauer wissen möchtest, was dahinter steckt, kannst du das in diesem Artikel nachlesen:
      Die neue FILTER-Funktion mit Jokersuche

      Schöne Grüße,
      Martin