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
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
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
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
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
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
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
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ß!
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.
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
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
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
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
Hallo Martin,
genau solche Hinweise braucht man – super!
Auch von mir alles Gute für 2021 und ein herzliches Dankeschön für deine wirklich nützlichen Beiträge!
Gruß
Hannes
Hallo Hannes,
dankeschön, auch dir alles Gute für das neue Jahr!
Schöne Grüße,
Martin
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
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
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
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)))
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
Hallo Martin.
Wie immer ist es eine Freude, mit deinem Blog auf die richtigen Lösungen zu kommen und neue Funktionen kennenzulernen.
Die Jokersuche ist in der Tat eine tolle Sache.
Die Trefferliste umzukehren, sprich alles anzeigen zu lassen, was NICHT gefunden wird, ist bei einem einzigen Ausschlusskriterium kein Problem: Statt ISTZAHL nehme ich ISTFEHLER.
Aber wie geht man am besten vor, wenn es zwei oder mehrere sind?
Bisher habe ich eine Kaskaden-Formel genommen, die an die alten verschachtelten „WENN(wahr, dann dieses, sonst WENN(wahr, dann dieses, sonst WENN….)):
Ich setze FILTER mit Such- bzw. Ausschlusskriterium Nr. 3 über eine Liste, die ich mit FILTER zu Ausschlusskriterium 2 aus einer Liste mit FILTER zu Ausschlusskriterium 1 laufen lasse. Wenn dann noch ein Sortieren vorne dran ist, wird die Formel drei bis 4 Zeilen lang, weil die FILTER1,2,3-Listen ja dann auch bei SUCHEN auftauchen.
Die Lesbarkeit ist – gelinde gesagt – schon nach der ersten Erfassung praktisch nicht mehr gegeben, auch wenn das Ergebnis richtig schein.
Hast du da einen einfacheren Tipp?
Besten Dank und Gruß
Gerd
Hallo Gerd,
ich glaube, da kann dir geholfen werden. Die FILTER-Funktion kann auch mehrere Kriterien verwenden. Je nachdem, ob eine logische ODER-Verknüpfung oder eine UND-Verknüpfung notwendig ist, werden die Kriterien mit einem + oder einem * kombiniert.
Nehmen wir mein Beispiel aus dem Artikel mit den Ländern und nehmen wir an, in den drei Zellen F1, F2 und F3 stehen die Länder, die ausgeschlossen werden sollen. Dann lautet die Formel:
=FILTER(A2:C309;ISTFEHLER(SUCHEN(F1;A2:A309))+ISTFEHLER(SUCHEN(F2;A2:A309))+ISTFEHLER(SUCHEN(F3;A2:A309)))
Ich hoffe, das hilft dir weiter.
Schöne Grüße,
Martin
Hallo Martin, ich wollte deine Funktion für eine Tabelle nutzen in der Personen aufgelistet sind, zum Beispiel „Max“, aber auch mehrere Personen zusammen, zum Beispiel „Max/Ben“, und es sollte mir alles ausgeben wo „Max“ enthalten ist also sowohl nur „Max“ als auch „Max/Ben“ wenn ich nur Max eingebe… Allerdings gibt mir die Formel nur den #NAME? Fehler aus. Kannst du mir vielleicht schreiben was der Fehler ist??
Bilder:
Hallo Fabian,
deinen Screenshots nach zu urteilen, nutzt du nicht Microsoft 365. Die FILTER-Funktion ist erst in Excel für M365 (oder Excel 2021) verfügbar.
Schöne Grüße,
Martin
Hi Martin,
tolle Funktion, kann ich richtig gut gebrauchen, aber leider will sie nicht funktionieren und finde auch nicht die Ursache dafür.
Habe die Formel genau nach Vorgabe für meine Anwendung angepasst.
=FILTER(A4:B10;ISTZAHL(SUCHEN(F1;A4:B10)))
Es kommt immer wieder nur #WERT.
Hast DU noch einen Tipp für mich? Wir verwenden EXCEL 365.. Freue mich auf Deine Rückmeldung.
Gruß
Uwe
Hi Uwe,
der Fehler liegt darin, dass du in der SUCHEN-Funktion über 2 Spalten suchst (A4:B10). Damit werden nicht 7 Ergebnisse zurück geliefert, sondern 14 (7x WAHR bzw. FALSCH für Spalte A und 7x WAHR bzw. FALSCH für Spalte B). Der ganze zu filternde Bereich umfasst aber nur 7 Zeilen, daher kann die Filterfunktion nichts mit den Suchergebnissen anfangen.
Korrekt wäre also
=FILTER(A4:B10;ISTZAHL(SUCHEN(F1;A4:A10)))
Schöne Grüße,
Martin