Die neue FILTER-Funktion mit Jokersuche 11

Trio mit vier Fäusten: Die FILTER-Funktion wird verstärkt durch zwei alte Bekannte
 

Excel aus Microsoft 365 (ehemals Office 365) bietet mit der FILTER-Funktion ein ungemein praktisches und einfaches Werkzeug, um aus einem Datenbestand einen bestimmten Extrakt herauszufiltern – und das Ganze auch noch komplett dynamisch.

So genial und einfach sie auch ist, die FILTER-Funktion hat dennoch eine kleine Schwäche: Sie kann nur exakte Treffer liefern. Wenn man nur nach einem Teil eines Textes sucht, scheitert sie leider.

Wie man die Funktion trotzdem dazu bringen kann, auch Textfragmente zu akzeptieren, zeigt der heutige Artikel.

Falls du FILTER und die anderen neuen Array-Funktionen noch nicht kennen solltest (und Microsoft 365 im Einsatz hast), solltest du dir unbedingt diesen Artikel ansehen.

In diesem Zusammenhang kommt auch gleich die schlechte Nachricht: Wenn du kein Microsoft 365/Office 365 nutzt, dann kommst du leider nicht in den Genuss der hier beschriebenen FILTER-Funktion 🙁

Die FILTER-Funktion und ihre Einschränkung

Zu diesem Artikel habe ich mich durch einen Leserkommentar inspirieren lassen (danke an Arno!). Ich hatte im letzten April ein Anwendungsbeispiel für die damals in Microsoft 365 neu eingeführte FILTER-Funktion geschrieben (wer den Artikel nicht gelesen hat, kann dies hier nachholen).

Die Beispieldatei zum heutigen Artikel kannst du dir bei Interesse hier herunterladen.

Nach Eingabe eines Ländernamens in einer Zelle hat diese Funktion alle in einer Liste aufgeführten Städte zu diesem Land ausgegeben. Diese FILTER-Funktion erwartet allerdings einen vollständigen Suchbegriff. Das heißt, es werden nur Zellen gefunden, deren Inhalt exakt mit diesem Begriff übereinstimmt.

Nach Eingabe von „Deutschland“ in der Suchzelle F1 spuckt die Formel in meinem Beispiel also alle betreffenden Städte aus, die in der Liste in den Zellen A2:C309 enthalten sind:

Die FILTER-Funktion im Einsatz

Die FILTER-Funktion im Einsatz

Wenn ich jedoch auf die Idee käme, mir alle Städte ausgeben zu wollen, deren Land ein „reich“ enthält, liefert die Formel einen #KALK!-Fehler:

FILTER sucht nach exakter Übereinstimmung

FILTER sucht nach exakter Übereinstimmung


Mit anderen Worten, sie findet nichts, denn sie braucht eine exakte Übereinstimmung.

Zwei alte Bekannte kommen zu Hilfe

Wenn man ein wenig im Excel-Funktionskatalog stöbert, dann stößt man irgendwann auf die SUCHEN-Funktion. Die wiederum durchsucht einen Text nach einem Textschnippel und gibt bei einem Treffer die Anfangsposition innerhalb des Textes an.
=SUCHEN(Suchtext; Text; [Erstes_Zeichen])
So liefert beispielsweise die Formel
=SUCHEN(„reich“;“Frankreich“)
als Ergebnis „6“, da „reich“ ab der 6. Position im Wort Frankreich beginnt. Der zu durchsuchende Text kann dabei auch ein Verweis auf eine Zelle sein:

Einen Textausschnitt mit SUCHEN finden

Einen Textausschnitt mit SUCHEN finden

Wird hingegen der Suchtext nicht gefunden, dann liefert die Funktion einen #WERT!-Fehler. Normalerweise kann die SUCHEN-Funktion nur auf einen einzelnen Text oder eine einzelne Zelle angewendet werden. Die neue Behandlung von Arrays in Microsoft 365/Office 365 ermöglicht es jedoch, die Funktion auch auf einen ganzen Zellenbereich anzuwenden. Für unser Beispiel sieht das dann so aus:

SUCHEN liefert die Position

SUCHEN liefert die Position


Für die ersten 15 Zeilen wird ein #WERT!-Fehler ausgegeben, da in den ersten 15 Ländern kein „reich“ enthalten ist. Dann kommt jedoch „Vereinigtes Königreich“ und unsere Formel gibt den Wert 18 aus, nämlich die Position von „reich“ innerhalb von „Vereinigtes Königreich“.

Uns ist die Position aber völlig egal, es ist nur wichtig zu wissen, ob überhaupt ein Treffer vorliegt. Und jetzt kommt der zweite alte Bekannte dazu, die ISTZAHL-Funktion. Sie gibt ein logisches WAHR aus, wenn der angegebene Wert eine Zahl enthält und ein logisches FALSCH, falls es sich um keine Zahl handelt. Also auch bei einem Fehlerwert. Kombiniert man die beiden Funktionen ISTZAHL und SUCHEN, sieht unsere Liste so aus:

ISTZAHL und SUCHEN in Kombination

ISTZAHL und SUCHEN in Kombination

Nur noch ein kleiner Schritt…

Du merkst schon, in welche Richtung es geht, oder? Wir müssen jetzt nur noch unsere ursprüngliche FILTER-Formel mit den beiden Funktionen ISTZAHL und SUCHEN kombinieren…
=FILTER(A2:C309;ISTZAHL(SUCHEN(F1;A2:A309)))
…und erhalten damit das gewünschte Ergebnis:

Ein starkes Trio: FILTER, ISTZAHL und SUCHEN

Ein starkes Trio: FILTER, ISTZAHL und SUCHEN

Hinweis
Auch die bekannten Jokerzeichen * und ? können bei dieser Lösung verwendet werden, um bei Bedarf noch spezifischer suchen zu können:

Und Jokerzeichen funktionieren auch

Und Jokerzeichen funktionieren auch

Wie man sieht, sind die neuen Excel-Funktionen wie FILTER sehr mächtig und extrem praktisch. Richtig gut werden sie jedoch manchmal erst, wenn man sie mit altbekannten Funktionen zu kombinieren weiß!

 

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

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

11 Gedanken zu “Die neue FILTER-Funktion mit Jokersuche

  • M.T-S.

    Moin Martin,

    schöne und nützliche Formel. 🙂

    Ich wollte bei mir noch zusätzlich die Zahl der Treffer angezeigt bekommen. Ich dachte erst an ZÄHLENWENN, was allerdings immer 0 geliefert hat.

    ANZAHL war dann die Lösung.

    =ANZAHL(SUCHEN(F1;A2:A309))

    Gruß Marcel

    • Martin Weiß Autor des Beitrags

      Hi Marcel,

      ja, ohne die SUCHEN-Funktion tritt man hier in die gleiche Falle. Vielen Dank für die Ergänzung!

      Schöne Grüße,
      Martin

  • Sandra

    Lieber Martin,

    ich möchte deinen ersten Blog-Beitrag im neuen Jahr nutzen, um mich einmal für deine Beiträge zu bedanken!
    Du hast in deinem Beitrag „Die Fragen nach dem Warum“ geschrieben, dass es dir ein Anliegen ist, Freude an Excel zu vermitteln.
    Dies ist dir in meinem Fall, mit deinen liebevoll aufbereiteten Beiträgen, gelungen!

    Ich wünsche dir ein tolles Jahr 2021! Vor allem Gesundheit!

    Viele Grüße, Sandra

    • Martin Weiß Autor des Beitrags

      Hallo Sandra,

      vielen lieben Dank für das tolle Feedback, das freut mich wirklich.
      Auch dir wünsche ich ein gesundes und tolles Jahr 2021!

      Liebe Grüße,
      Martin

  • Johannes Sandkamp

    Hallo Martin,

    ich nochmal. Ich hätte da noch eine Frage die vielleicht zum Thema passt. Hast du schon mal versucht so eine Matrix in einen Bereichsnamen zu packen? Der Bereichsname an sich funktioniert auf dem Tabellenblatt, aber er lässt sich nicht als Liste in eine Datenüberprüfung packen.
    Kennst du das Problem bzw. wenn ja hättest du vielleicht einen Lösungsansatz für mich?

    Gruß
    Hannes

    • Martin Weiß Autor des Beitrags

      Hallo Johannes,

      ja, das Problem kenne ich und ich hatte schon ein paar Anfragen dazu. Daher werde ich das bei Gelegenheit in einen eigenen Artikel packen 😉

      Schöne Grüße,
      Martin

    • Carola

      Die Datenüberprüfung kann nur EINE Zeile oder Spalte. Darüber bin ich auch schon gestolpert.
      Meine Lösung: Die Inhalte einfach zusammenkleben per Formel und diese Spalte für die Liste nehmen:
      =Inhalt1 & Inhalt2 & ….
      Verketten-Funktion geht natürlich auch.
      Für den SVERWEIS wird das ja genauso gebraucht.
      Gruß Carola

  • Carola

    Vielen Dank!
    Die Filter-Funktion kannte ich noch nicht, aber es kommen ja immer neue Dinge hinzu.
    Leider kann man immer nur das anwenden, was der ältesten Version in der Firma entspricht.
    Bei meinen Schulungen auf dem Land ist mir da noch Excel 2000 über den Weg gelaufen – keine Seltenheit!

    Aber zur Funktion: Auf die Variante ISTZAHL wäre ich jetzt nicht gekommen, ich hätte ISTFEHLER verwendet, funktioniert genauso, nur dass Wahr und Falsch vertauscht sind, ok, dann muss ich noch ein NICHT drumrum setzen, damit die WAHR-Werte verwendet werden.

    …Nicht(ISTFEHLER(SUCHEN(F1;A2:A309)))

    • Martin Weiß Autor des Beitrags

      Hallo Carola,

      uiuiui, Excel 2000, das hört sich ja richtig gruselig an 😉
      Und ja, mit ISTFEHLER geht es natürlich auch.

      Schöne Grüße,
      Martin