Wert innerhalb einer Matrix suchen und Wert in Spalte ausgeben

[maxderkurze]

Hallo,

ich habe ein Problem mit Excel!

Ich habe in mehrern Spalte untereinander einen Text bzw. einen Satz zu stehen, dabei kommt in den Sätzen das gleiche Wort vor (an unterschiedlichen Stellen). Nun möchte ich aus den Sätzen ein bestimmtes Wort manuell durch Eingabe heraussuchen und er soll mir dann diesen Satz mit den gesuchten Wort separat in eine Zeile wiedergeben. Mein Problem bisher ist, dass ich eine Funktion habe die mir nur den ersten Wert wieder gibt, sobald ich die Formeln aber erweitere bekomme ich wieder nur den ersten gefundenen Wert.

Als „Zwischenlösung“ habe ich eine Funktion in „Gebrauch“ die mir anzeigt wie oft das Wort in der Matrix vorkommt aber das hilft mir nur bedingt weiter, da ich den Text in eine separate Zeile benötige. Zum besseren Verständnis meines Problems, habe ich ein Bild eingefügt mit den genuzten Funktionen.

Habt ihr vielleicht eine Lösung?

[Niclaus_Wurthberg]

Hallo Max

Ich hänge Dir ein jpg mit einem Lösungsvorschlag an.

Ich arbeite gern mit Hilfsspalten, die man ausblenden kann. Und mit Bedingter Formatierung (Menü Start – Office 2013). Man könnte alles in eine einzige Formel reinbringen, aber das wird für mich selber zu unübersichtlich und unverständlich.

Ich habe die Daten auf die Zeilen 1 – 10 beschränkt. Auf 1 – 100 kannst Du sie sicher ohne Probleme erweitern. – Weiter habe ich Gross- und Kleinschreibung von „wächst“ berücksichtigt.

Vielleicht hilft Dir das. – Grüsse Niclaus

[Tobias]

Hallo.

@Niclaus_Wurthberg

Ich arbeite gern mit Hilfsspalten, die man ausblenden kann.

Das ist immer ein sehr guter Rat. Die Formeln werden übersichtlicher und manchmal löst sich dadurch ein Problem quasi von selbst.

@Niclaus_Wurthberg

Weiter habe ich Gross- und Kleinschreibung von „wächst“ berücksichtigt

Da hat sich irgendein Denkfehler eingeschlichen. Die Finden-Funktion beachtet die Klein-/Großschreibung, mit GROSS() wird aber alles in Großbuchstaben umgewandelt, wodurch im Endeffekt dann doch die Groß-/Kleinschreibung ignoriert wird, was man auch an den Suchergebnissen sieht. Für die Suche nach korrekter Schreibweise müsste B2 einfach nur =FINDEN($D$2;A2) lauten. Wenn die Schreibweise ignoriert werden soll geht auch =SUCHEN($D$2;A2)

---

Die Indirekt-Funktion ist extrem praktisch, sollte aber immer sparsam verwendet werden da es sonst, besonders bei schwächeren Rechnern, zu langen Ladezeiten kommen kann. Bei 10 Stück sollte es noch lange keine Probleme geben, bei 100 aber möglicherweise schon.
Ich würde stattdessen einfach die Verweis-Funktion verwenden. Für E2 wäre das: =VERWEIS(KKLEINSTE($C$2:$C$10;ZEILE()-1);$C$2:$C$10;$A$2:$A$10)

Gruß
Tobias

[Niclaus_Wurthberg]

Hallo Tobias
Vielen Dank für Deine Hinweise. - Den Unterschied zwischen FINDEN() und SUCHEN() lernt man eigentlich im Kindergarten. An dem Tag war ich wohl krank ;-))
Ein Frage habe ich an Dich zu meiner Formel in D4: Wie oft „wächst“ vorkommt in A1:A10:

=ZÄHLENWENN(A1:A10;"*"&D2&"*")

Diese Formel unterscheidet nicht zwischen Gross- und Kleinschreibung.
Ich möchte nur das Vorkommen von klein geschriebenen „wächst“ zählen. Ich habe versucht, IDENTISCH() in die Formel ZÄHLENWENN() einzufügen. Aber ich kam damit zu keinem Ergebnis.
Hast Du eine Idee, wie man hier ZÄHLENWENN() [oder eine andere Formel] und IDENTISCH() kombinieren könnte?

Viele Grüsse Niclaus

[Tobias]

@Niclaus_Wurthberg

Hast Du eine Idee, wie man hier ZÄHLENWENN() [oder eine andere Formel] und IDENTISCH() kombinieren könnte?

Du denkst nur zu kompliziert. Meine Lösung wäre FINDEN() in einer Hilfsspalte. Also genau das was Spalte B bereits macht. Mit =ZÄHLENWENN(C2:C10;">0") oder =ANZAHL(C2:C10) hast du bereits die Anzahl. Ich habe Spalte C verwendet weil dort die Ergebnisse von der Fehlermeldung bereinigt sind, es sollte aber auch in Spalte B keine Probleme geben.

@Niclaus_Wurthberg

Ich habe versucht, IDENTISCH() in die Formel ZÄHLENWENN() einzufügen.

IDENTISCH() vergleicht ob zwei Werte exakt gleich sind und benutzt keine Platzhalter, deshalb trägt man auch nicht Text und Suchtext ein, sondern Text1 und Text2. Mit FINDEN() könnte man ja nur prüfen ob der Suchtext irgendwo innerhalb der anderen Zelle vorkommt bzw. an welcher Stelle er ist, könnte aber nur über Umwege prüfen ob der komplette Inhalt identisch ist. Beispiel:

Ein Vergleich wie =A1=B1 verhält sich übrigens genau wie IDENTISCH() , missachtet aber die Groß-/Kleinschreibung.

[maxderkurze]

Hallo Niclaus und Tobias,

Entschuldige das ich mch erst jetzt zu Wort melde aber vielen, vielen Dank für diese Super Ideen. Wird gleich alles mal ausprobiert

@Niclaus_Wurthberg

Ich arbeite gern mit Hilfsspalten, die man ausblenden kann. Und mit Bedingter Formatierung (Menü Start – Office 2013). Man könnte alles in eine einzige Formel reinbringen, aber das wird für mich selber zu unübersichtlich und unverständlich.

So sehe ich das auch und ich bin Inzwischen auch auf eine Lösung mit Hilfe von Hilfspalten gekommen:

Ich habe mir auch einfach Zählen lassen in welchen Satz das Wort wächst vorkommt. Ergebnis ist dann bei gefunden eine 1 oder bei nicht gefunden eine 0 . Die 1 habe ich mir von einer Matrixformel suchen und somit den Satz ausgeben lassen.

„Problem“ ist, das dabei der Rechner bzw. Excel ordentlich arbeiten muss und es zu längeren Ladezeiten kommen kann

Ich habe die Lösung nochmal als Bild angehängt

[Tobias]

@maxderkurze

Ich habe mir auch einfach Zählen lassen in welchen Satz das Wort wächst vorkommt.

Falls die Groß-/Kleinschreibung nicht beachtet werden soll, kannst du das so machen.

@maxderkurze

„Problem“ ist, das dabei der Rechner bzw. Excel ordentlich arbeiten muss und es zu längeren Ladezeiten kommen kann

Das ist genau das selbe was ich in meinem Hinweis zu INDIREKT() gemeint hatte, wobei Matrix-Formeln um einiges schlimmer sind. Matrix-Formeln lassen sich aber üblicherweise vermeiden indem man das ganze in Einzelschritte zerlegt und Hilfsspalten verwendet.
So lange man nicht mit irgendwelchen versteckten Funktionen anfängt, sollten das aber die einzigen beiden Fälle sein bei denen man in Hinblick auf Performance aufpassen muss.

---

Ich gehe davon aus, dass die Überschriften in Zeile 4 sind. Ansonsten müssen die Bezüge und -4 angepasst werden.

Du könntest in B5 einfach =WENN(ZÄHLENWENN(C5;"*"&$D$5&"*");ZEILE();"") verwenden oder alternativ in A5 =WENN(B5;ZEILE();"") (die jeweilige Formel nach unten ausfüllen)
Dadurch hast du dann eine Spalte mit einem eindeutigen Indikator den du dann z.B. für SVERWEIS() oder VERWEIS() nutzen kannst. Wenn du die Formel für B5 benutzt, wäre das

=SVERWEIS(KKLEINSTE(B$5:B$20;ZEILE()-4);B$5:C$20;2;0)

bzw.

=VERWEIS(KKLEINSTE(B$5:B$20;ZEILE()-4);B$5:B$20;C$5:C$20)

Falls du die Formel für A5 benutzt, einfach die Bezüge und, im Fall von SVERWEIS(), den Spaltenindex anpassen.

Der Unterschied der beiden Funktionen ist, dass bei SVERWEIS() nur ein einziger Bereich ausgewählt wird in dem in der ersten Spalte die zu durchsuchenden Werte stehen müssen. Bei VERWEIS() kann man den Such- und Ergebnisbereich unabhängig voneinander wählen, die zu durchsuchenden Werte müssen allerdings immer aufsteigend nach Größe sortiert sein.