Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1732to1736
1732to1736
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Filter Formel Teilstring

Forumthread: Filter Formel Teilstring

Filter Formel Teilstring
20.01.2020 10:22:02
GüntherW
Hallo zusammen,
nachdem ich einige Stunden im WEB recherchiert habe und keine Lösung gefunden habe nun dieser Versuch. Ich hoffe es gibt Spezialisten, die mir weiterhelfen können.
Ich möchte eine Spalte mit Artikeln über eine Formel filtern. Allerdings habe ich als Filterkriterien 1(TR3), 2 (PQ012) oder auch 3, Teilstrings. Auf der Seite "Excelformeln" habe ich zwar einige Sachen gefunden, doch bringen sie mich wegen zu wenig Kenntnis nicht weiter.
Artikel Gefiltert Suchstring 1 Suchstring 2 Suchstring 3
10001006 TR3 PQ012
10101000
10102000
10103000
214327
215091
BCM5862
Tr-PSOZ-TOC3
Tr-PSOZ-TOC4a
Tr-PSOZ-Module
Tr-PSOZ-MOC
Tr-TOC2
Tr-TOC2DC
Tr-TOC3
Tr-TOC3DC
Tr-TOC4a
Tr-Module
Tr-MOC
Tr30-PSOZ-TOC3
Tr30-PSOZ-TOC4a
Tr30-PSOZ-MOC
Tr30-TOC2
Tr30-TOC2-PQ012
Tr30-TOC2DC
Es wäre toll, wenn mir jemand weiterhelfen könnte.
Vielen Dank und beste Grüße
Günther
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Filter Formel Teilstring
20.01.2020 10:40:27
Daniel
Hi
da würde ich erstmal in einer Hilfspalte neben der zu filternden Tabelle die anzuzeigenden Zeilen kennzeichnen.
beispielsweise mit der Formel (für Zeile2)
die zu suchenden Teilstrings stehen in D1-F1
beachte, dass ein Leerstring immer gefunden wird.
=Wenn(IstZahl(Aggregat(15;6;Finden($D$1:$F$1;A2);1));Zeile();"")
damit bekommst du die Zeilennummer einer Zeile, die in A1 einen der gesuchten Teilstrings enthält.
die gefilterte Liste erstellst du dann mit dieser Formel:
=Index(A:A;KKleinste(Hilfsspalte mit den Zeilennummern;Zeile(A1)))
Gruß Daniel
Anzeige
AW: Filter Formel Teilstring
21.01.2020 07:18:27
GüntherW
Hallo Daniel,
leider funktioniert es nicht. Was mache ich für einen Fehler?
Hier meine Datei: https://www.herber.de/bbs/user/134566.xlsx
Viele Grüße
Günther
AW: (m)eine Beispiellösung unter Annahmen ...
21.01.2020 09:27:07
neopa
Hallo Günther,
... für Deine Beispieldatei nehme ich mal an, dass die Groß-/Kleinschreibung Suchstrings egal ist und nur einer von diesen im Artikelnamen vorhanden sein muss sowie z.B. max ca 150 Daten auszuwerten sind und das Ergebnislisting keine Dopplungen enthalten soll.
Dies wäre mit einer Formel realisierbar (mit Hilfsspalte geht die Auswertung zwar schneller, aber ist bei Deiner Datenmenge auch so noch kein Problem.
In C2:

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$151)/SUCHEN(E$2:G$2;A$2:A$151)^0/(E$2:G$2"") /(ZÄHLENWENN(C$1:C1;A$2:A$151)=0);ZEILE(A1)));"")
Und Formel ziehend weit genug nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: Korrektur ...
21.01.2020 09:38:15
neopa
Hallo,
... anstelle ZEILE(A1) muss es natürlich nur 1 lauten.
Also:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$151)/SUCHEN(E$2:G$2;A$2:A$151)^0/(E$2:G$2"") /(ZÄHLENWENN(C$1:C1;A$2:A$151)=0);1));"")
Gruß Werner
.. , - ...
AW: Korrektur ...
21.01.2020 13:04:42
GüntherW
Hallo Daniel,
sorry das hatte ich nicht bedacht. In der Datei findest Du ein Tabellenblatt "Daniel". Ich habe als Teisstrings 3 Begriffe eingegeben. Es sollten alle angezeigt werden die diese 3 Teilstrings enthalten, also UND nicht ODER.
Hallo Werner,
Deine Formel zeigt leider auch nicht das gewünschte Ergebnis an, Siehe Tabellenblatt "Werner". Auch hier sollten die Teilstrings als UND und nicht als ODER suchen.
https://www.herber.de/bbs/user/134584.xlsx
Ich danke Euch schon mal und seid etwas nachsichtig mit mir, es ist das erste mal daß ich ein Forum nutze.
Viele Grüße
Günther
Anzeige
AW: Korrektur ...
21.01.2020 13:19:30
Daniel
Hi
dann im Prinzip so, Achtung Matrixformel, dh Eingabe immer mit STRG+SHIFT+ENTER abschließen
=WENN(UND(ISTZAHL(SUCHEN($D$1:$F$1;A2)));ZEILE();"")
(FINDEN: Groß/Kleinschreibung wird berücksichtigt, SUCHEN: Groß/Kleinschreibung ist egal)
habe ich getestet mit deiner Beispieldatei, es funktionniert.
allerdings kommen die angegebenen Begriffe in den vorhandenen Werten nicht vor.
Gruß Daniel
Anzeige
AW: deswegen hatte ich ja nachgefragt ...
21.01.2020 13:34:18
neopa
Hallo Günther
... bei einer UND-Beziehung mit mit einer Matrixfunktion(alität)sformel in C2 dann z.B. so:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$151)/SUCHEN(E$2;A$2:A$151)^0/SUCHEN(F$2;A$2:A$151)^0/SUCHEN(G$2;A$2:A$151)^0;ZEILE(A1)));"")
und Formel nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: deswegen hatte ich ja nachgefragt ...
21.01.2020 14:29:13
GüntherW
Hallo Werner,hallo Daniel,
es funktioniert! Das mit der definierten Tabelle werde ich noch einbauen. Daniels Lösung ist für meine anderen großen Tabellen besser.
Werner, Deine Lösung ist für meine Tabelle mit ca 800 Zeilen noch machbar.
Ich danke Euch recht herzlich für Eure Hilfe, Ihr habt mir sehr geholfen!!
Viele Grüße
Günther
Anzeige
AW: bitteschön owT
21.01.2020 14:51:28
neopa
Gruß Werner
.. , - ...
AW: Filter Formel Teilstring
21.01.2020 09:28:48
Daniel
HI
da du in der Beispieldatei noch nichts gemacht hast, kann ich dir auch nicht sagen was du falsch gemacht hast.
ansonsten ist die Fehlerbeschreibung "funktioniert nicht" nicht besonders hilfreich dabei, die Fehlerursache zu finden. Sie ist ungefähr so die die Antwort, "dann machs doch einfach richtig, dann funktionierts auch".
du solltest das "nicht funktionieren" schon etwas genauer beschreiben, zb. was passiert und was sollte eigentlich passieren.
Gruß Daniel
Anzeige
AW: hierzu sind noch einige Fragen zu klären ...
21.01.2020 09:17:45
neopa
Hallo Günther,
... welches Ergebnis strebst Du denn an? Dies ist nämlich von einigen momentan Deinerseits noch nicht eindeutigen Angaben abhängig.
- Sollen die vorgegebenen Suchstrings alle im Artikelnamen gefunden werden werden (UND-Beziehung)
- oder soll mindestens eines von diesen im Artikelnamen gefunden werden (ODER-Beziehung).
- Und sollen die Suchstrings identisch vorliegen oder ist Groß-/Kleinschreibweise egal?
- soll das Auflisten von evtl. doppelt/mehrfach vorkommenden Artikelenamen verhindert werden?
- Ist eine Massendatenauswertung geplant (viele hunderte Artikelnamen) oder nicht?
Es empfiehlt sich auch, die Artikelnamen in einer sogenannten "intelligenten" Tabelle (zu erzielen durch die Ausführung der Funktion "Als Tabelle formatieren") zu führen. Dies hätte für eine Formelauswertung den Vorteil, dass die dafür definierte Formel nachträglich nicht geändert werden muss, wenn sich die auszuwertende Datenmenge vergrößert.
Gruß Werner
.. , - ...
Anzeige
;
Anzeige

Infobox / Tutorial

Filterformel für Teilstrings in Excel

Schritt-für-Schritt-Anleitung

  1. Vorbereitung der Daten: Stelle sicher, dass Deine Daten in einer Excel-Tabelle organisiert sind. Beispielsweise kannst Du eine Spalte mit Artikeln und mehrere Spalten für die Suchstrings anlegen.

  2. Hilfspalte erstellen: Füge eine Hilfspalte neben deiner Artikelliste hinzu. In dieser Hilfspalte kannst Du die Zeilen kennzeichnen, die einen der gesuchten Teilstrings enthalten. Verwende dazu folgende Formel (angenommen, Deine Artikel sind in Spalte A und die Suchstrings in D1 bis F1):

    =WENN(ISTZAHL(AGGREGAT(15;6;FINDEN($D$1:$F$1;A2);1));ZEILE();"")

  3. Gefilterte Liste erstellen: Nutze die INDEX- und KKLEINSTE-Funktion, um die gefilterte Liste zu erstellen. Setze diese Formel in eine neue Zelle (z.B. in C2):

    =INDEX(A:A;KKLEINSTE(<Hilfsspalte>;ZEILE(A1)))

  4. Matrixformel verwenden: Wenn Du mehrere Suchstrings in einer UND-Beziehung auswerten möchtest, kannst Du die folgende Matrixformel verwenden. Denke daran, die Eingabe mit STRG + SHIFT + ENTER abzuschließen:

    =WENN(UND(ISTZAHL(SUCHEN($D$1:$F$1;A2)));ZEILE();"")

  5. Formel nach unten ziehen: Kopiere die Formeln in den entsprechenden Zellen nach unten, um alle möglichen Treffer zu erfassen.

Häufige Fehler und Lösungen

  • Fehler: Formel funktioniert nicht: Überprüfe, ob die Zellreferenzen korrekt sind. Achte darauf, dass keine Leerzeichen in den Suchstrings vorhanden sind und die Formeln richtig eingegeben wurden.

  • Fehler: Ergebnisse werden nicht angezeigt: Stelle sicher, dass Du die Formel als Matrixformel eingegeben hast, falls Du eine UND-Bedingung verwendest.

  • Fehler: Falsche Ergebnisse: Wenn die Ergebnisse nicht stimmen, überprüfe die Groß-/Kleinschreibung Deiner Suchstrings. Nutze die Funktion SUCHEN, um dies zu umgehen.

Alternative Methoden

  • Verwendung von Power Query: Du kannst Power Query zur Datenfilterung nutzen, was besonders hilfreich ist, wenn Du mit großen Datenmengen arbeitest.

  • Filteroptionen im Excel: Nutze die eingebauten Filterfunktionen von Excel, um Daten manuell zu filtern, ohne Formeln verwenden zu müssen.

Praktische Beispiele

Angenommen, Du hast folgende Artikel:

Artikel Suchstring 1 Suchstring 2 Suchstring 3
TR3-001 TR3 PQ012
PQ012-002 PQ012 TR3
ABC-003

Mit der oben beschriebenen Methode kannst Du alle Artikel filtern, die den Suchstring "TR3" oder "PQ012" enthalten, indem Du die entsprechenden Formeln anwendest.

Tipps für Profis

  • Definierte Tabellen verwenden: Mache Deine Daten zu einer Tabelle (über Als Tabelle formatieren), um die Handhabung der Formeln zu erleichtern und um sicherzustellen, dass sie sich automatisch an neue Daten anpassen.

  • Dynamische Arrays: In neueren Excel-Versionen kannst Du dynamische Arrays verwenden, um die gefilterten Ergebnisse einfacher und übersichtlicher darzustellen.

  • Verwendung von Namen: Definiere Namen für Deine Suchstrings, um die Formelformeln leserlicher zu machen.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Suchstrings in einer Formel kombinieren? Du kannst die Funktion WENN und die Suchfunktionen kombinieren, um mehrere Suchstrings in einer Formel zu verarbeiten.

2. Funktioniert das auch in älteren Excel-Versionen? Ja, die meisten der beschriebenen Methoden funktionieren auch in älteren Excel-Versionen, solange sie die grundlegenden Funktionen unterstützen.

3. Was ist der Unterschied zwischen FINDEN und SUCHEN? FINDEN ist groß-/kleinschreibungsabhängig, während SUCHEN dies nicht ist. Wähle je nach Bedarf die geeignete Funktion.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige