Excel - Suchfunktion: mehrere Wörter
Hallo liebe Excelprofis,
folgendes Problem: ich habe eine lange Excel mit Produkten. Diese Produkte haben Musternamen z.B.
- Sterne
- Herzen
- Kreise
und Produkttypen wie z.B.
- Hose
- Haarband
- Rock
Jedes Muster gibt es in den verschiedenen Produkttypen.
ich möchte nun in der Tabelle ganz gezielt nach Muster+Typ suchen also z.B.
- Sterne Haarband
- Herzen Rock
Leider kann ich aber mit der Suchfunktion nur EINEN Begriff (also nur Muster oder nur Typ) angeben und nicht beide zusammen. Welchen Trick müsste ich dafür anwenden?
Vielen lieben Dank für eure Hilfe. Malena
2 Antworten
Wenn 1. Wort in Spalte A steht und zweites Wort in Spalte B, dann liefert Dir folgende Formel die Zeilennummer (hier für max Zeile999):
=Summenprodukt((A1:A999="Sterne")*(B1:B999="Haarband")*Zeile(A1:A999))
technischer Hinweis: fixiere den Titel, indem Du zB auf D4 klickst und mittels Menü-Ansicht-Fenster-:Fenster einfrieren und nochmals Fenster einfrieren:
Die Spalten A:C (=links von markierter Zelle) und die Zeilen 1:3 (=oberhalb der markierten Zelle) bleiben jetzt beim Scrollen im Bild.
Setze die Formel zB in C2, dann bleibt ihr Ergebnis immer sichtbar, Du brauchst dir die Zahl also nicht zu merken und kannst zwischendurch auch mal eine Frage beantworten oder einem anderen Kunden was sagen (da vergisst man ja solche Zahlen leicht) und kannst hinterher weiter zur Zielzeile scrollen.
Erklärung der Funktion: Summenprodukt((A1:A999="Sterne" erstellt eine unsichtbatre Hilfsspalte mit den Werten WAHR (=zutreffend≙1) oder FALSCH (nicht zutreffend≙0) und multipliziert das mit den logischen Ergebnissen von (B1:B999="Haarband"). Nur wenn beides zutrifft, kommt 1 heraus und die Multiplikation mit der jeweiligen Zeilennummer ergibt eine Zahl <> 0 Da Deine Kombination nur 1x auftreten sollte, ist die Ergebniszahl die Zeilennummer.
Tritt sie mehrmals auf, wirds komplizierter, dann brauchst du eine Matrixformel **):*
{=KGrösste((A1:A999="Sterne")*(B1:B999="Haarband")*Zeile(A1:A999);1)}
liefert die Zeile des untersten solchen Eintrags, ...;2) die des zweituntersten
(mit Min würde es nochmals komplizierter).
Die Anzahl gleicher Einträge erhältst Du mit
=Summenprodukt((A1:A999="Sterne")*(B1:B999="Haarband")
würde ich zur Kontrolle in C3 einfügen, damit Du ein <>1 sofort erkennst.
Du Kannst anstatt "Sterne" auch einen Bezug auf eine Zelle setzen, in der Sterne steht, gleiches für Haarband, musst aber wissen, dass -wenn Du das in A2 bzw B2 schreibst, das mitgezählt wird und somit die oberste Formel nicht mehr geht und dass die Anzahl um eins zunimmt.
Ausweg: setz Sterne und Haarband in zwei Zellen untereinander, dann treten sie nicht schon hier in der gleichen Zeile auf. (Deshalb auch drei fixierte Zeilen, zwei für die Einträge, eine für die Spaltenüberschrift
und ausserdem drei fixierte Spalten, damit Formeln in C2/C3 kommen können und es keinen Zirkelbezug gibt)
): Achtung:Matrixformeln!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
*Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!
Liest sich alles furchtbar kompliziert, aber wenn Du das Schritt für Schritt durchführst, wirst Du sehen, dass es ganz einfach ist!
Lieber lamian,
ich danke dir vielmals für deine sehr gute und ausführliche Anleitung, die sich so liest, als könnte ich als Nicht-Profi sie tatsächlich Schritt für Schritt umsetzen! (danke, dass du dir so viel Zeit genommen hast!)
Was dieser Lösung leider entgegen stehen könnte, ist, dass ich diese Produkttabelle gar nicht selbst erstellt habe, sondern vom Produzenten bekomme. Er hat aber schon alle Muster und Typen (plus übrigens noch Größen) in der Tabelle in EINE Spalte gesetzt (also Sterne, Rock, Größe x). Und mir fällt momentan kein Zaubertrick ein, wie ich das wieder auseinanderklamüsert bekomme :(
Verflixt... Also falls du noch ne Alternativlösung kennst...
Lieben Gruß Malena
Kannst du die Begriff nicht in einer Hilfsspalte verketten?
Hallo, bin leider absolut kein Excel-Profi. Kannst du das bitte näher erläutern (an meinem Beispiel)?
Danke. LG