Excel: Nach definierten Wörtern suchen und einen definierten Text schreiben?
Hallo Excel Nutzer,
Ich würde gerne folgendes realisieren:
Suche in einer Zelle nach "Apfel", "Orangen", "Trauben" und wenn du einer von diesen Wörtern gefunden hast, schreiben in eine andere Zelle "Obst" als Ergebnis.
Ich habe mit folgender Formel probiert, aber es funktioniert leider nicht.
=IF(OR((SEARCH("Apfel";A1))>0;(SEARCH("Orangen";A1))>0);"Obst";"Nothing")
Ich wäre dankbar, wenn jemand mir schreiben könnte, was ich falsch mache.
Danke
6 Antworten
War nicht ganz einfach, aber ich denke, ich habs:
- Mach Dir in Tabelle2 eine Liste der möglichen Sorten in A (hab da jetzt nur 5 Demo-Zeilen genommen.
- beginne sie in Zeile 1 mit dem Eintrag AAA und schreib daneben in B1 Nicht gefunden
- in A2:A4 schreibst Du die möglichen Obstsorten (Apfel, Orange, Traube, übrigens immer Singular, denn Äpfel<>Apfel uvam. Willst Du auch Äpfel, Orangen erfassen, trage sie extra ein, aber Vorsicht: Traube ist in Trauben enthalten, Apfel in Äpfel nicht)
- In A5 noch was ganz anderes, zB Zucchini
- In B1:B5 die Kategorie: nicht gefunden, 3x Obst, in B5 Gemüse
ich habe in Tabelle1!A1 den Eintrag gesetzt: Erdapfelschalen (nicht eben erdÄpfelschalen), also apfel klein und mitten im Text, in A2 leer, in A3 hellorange Kaki
In B1 diese Matrix-{Formel}:
{=INDEX(Tabelle2!$B$1:$B$5;SUMMENPRODUKT(WENNFEHLER(1*(SUCHEN(Tabelle2!$A$1:$A$5;A1;1)>0);0)*ZEILE(Tabelle2!$A$1:$A$5)))}
Bemerkung zu Matrixformeln:
diese Formel aber ohne diese {Klammern} dann anstatt mit einfachem Enter mit Strg+Umsch+Enter eingeben (auch bei Korrekturen wieder). Die {Klammern} entstehen dann automatisch. (mach das mit der rechten Shift- und Ctrl-Taste, dann gehts mit einer Hand und ist leichter zu merken, du brauchst es nach jeder Korrektur!)
Die Formel liefert bei mir Obst, auch wenn ich in zB A3 die o.g. hellorange Kaki eingebe oder in A4 Konserven mit Zucchini-Stücken. bei leeren Zellen und nicht gefundenen Begriffen liefert die Formel Nicht gefunden.
Bei langen Listen könnte es sein, dass die Performance leidet, habs jetzt um halb zwei nachts nicht mehr ausprobiert.
Viel Erfolg, aber frag zurück, wenn was nicht klappt (bitte mit Positionen im Blatt und Fehlerbeschreibung!, nicht nur "im Blatt" einen "Fehler gemeldet")
Ach so, in Englisch folgt sie gleich noch in einem Kommentar
Hallo nochmals.
es hat super funktioniert und ich bin sehr glücklich.
Ich hatte über fünf Stunden gekämpft um meinen Denkfehler zu finden, bevor ich hier die Frage gestellt habe. Ohne deine Hinweise hätte ich sicher noch mehrere Stunden forschen müssen. Daher herzlichen Dank!
Prima, und danke für den Stern! (brauche als Experte regelmäßig eine Mindestzahl...)
Ich selber kämpfe gerade noch darum, wie die Formel funktioniert, wenn man oberhalb einige Zeilen einfügt, aber das ist ein abgeleitetes Problemchen, das Dich nicht betreffen muss!
ja das habe ich auch gemerkt. Ich habe Matrixgröße so ausgewählt, dass es für mich ausreichen sollte. 150 Zeilen...
ich habe aber andere Herausforderungen gefunden...
Kann man in Excel mit variablen Tabellennamen arbeiten? :)
Also wenn ich den gesamten Inhalt in eine neue Tabelle kopiere, muss ich alle Formeln anfassen, die einen Bezug auf andere Tabellen haben...
Ich kämüfe noch mit anderen Kleinigkeiten dazu.
- Gehe ohne künstliche Änderung des Tabellennamens so vor:
- Schneide den gesamten Tabellenbereich (Obstsorte|Kategorie) aus (nicht nur kopieren!) und
- füge ihn bei offener Quelldatei in die Zieldatei ein (vllt etwas größer, als die Formeln angeben).
- Mach das gleiche mit der/den Formel/n
- Kontrolliere, ob sich die Bezüge exakt angepasst haben
- schließe die Quelldatei OHNE SPEICHERN: sie ist unverändert verfügbar.
- speichere die Zieldatei und arbeite dort weiter.
Zwar ließen sich auch Datei-&Tabellenname mit einem Mords-Formelklapparatismus aus Zelle("Dateiname") extrahieren (wobei ich hörte, dass xl14 oder 16 endlich auch die Formel =Blattname() haben, weiß nicht sicher) , aber es heißt so schön: warum einfach, wenn's umständlich auch geht?
Wenn Du den (jetzt nur exakt) ausgeschnittennen Bereich vorher einfärbst oder umrahmst, kannst Du ihn gezielt auch mit neuen Werten Narren-sicher füllen, überflüssige Zeilen/Zellenpaare löschen.
Bist Du zurechtgekommen?
Es gäbe auch eine weitere ähnliche und möglicherweise einfachere Methode des Transfers in eine andere Datei, führe ich aber nur auf Nachfrage aus.
Übrigens: Die Formel funktioniert nicht, wenn mehrere Begriffe aus der Liste zuträfen:
Habs zwar nicht ausprobiert, aber wenn Orange zur Kategorie Ciotrusfrüchte gehörte und Kaki zur Kategorie exotische Frucht oder sonstwas, dann ist die "hellorange Kakifrucht" in zwei Kategorien zu finden bzw wegen der Summenbildung eben nicht zu finden. (FINDEN anstatt SUCHEN könnte das in genau diesem Fall schon beheben, aber es gibt möglicherweise ähnliches, wo auch Groß/kleinscheibung übereinstimmt: wenn zB alles großgeschrieben ist etc.). Ggf trotzdem lösbar.
Bin leider nicht zurecht gekommen. Ich arbeite nicht mit zwei Excel Dateien sondern mit nur einer. In der gleichen Excel Datei gibt es vier Tabelle (Tabelle1, Tabelle2, Tabelle3, Tabelle4). In Tabelle 1 habe ich sehr viele Zellen wo ich Link auf Tabelle2 habe. Jetzt kopiere ich den gesamten Inhalt von Tabelle1 auf Tabelle3 und möchte aber, dass ich an einer Stelle ändern kann auf welche Tabelle jetzt die Links gehören sollen, indem Fall auf Tabelle4. Also jetzt sollen die Variablen in Tabelle3 auf Tabelle4 schauen. Ob das überhaupt so einfach geht, würde mich wundern, aber wenn ja, wäre geil.
Außerdem, ich habe heute wieder ein paar Stunden investiert, um einige Kleinigkeiten zu erledigen, wobei ich auch von der deiner Formel oben (mit Summenprodukt usw.) einiges abgucken und anpassen wollte. Aber stellte ich fest, dass ich Hintergründe von einiges in deiner Formal nicht verstehe. Somit kann ich irgendwo anders nicht nutzen, denn es funktioniert net mehr was ich machen will, wenn ich etwas anpasse. Daher wäre es möglich wenn mir deine formel oben einbisschen einläuterst?
z.B. dieser Ausdruck:
IFERROR(1*(SEARCH($A$1:$A$148;E1;1)>0);0)
Warum IFERROR? Warum 0, wenn Fehler?
Wenn ich nur die obige Formel so auf die gleiche A Spalte anwender, kommt als Ergebnis "0" raus. Aber das kann doch net sein. Denn mit der gesamten Formal (mit Index) kommt das richtige Ergebnis raus. Also wie du siehst, ich verstehe nicht. :)
Ich habe noch zwei andere Fragen, die eher für dich einfach zu antworten sind, gehe ich davon aus. Soll ich diese Frage als getrennter Fall stellen, so dass du mehr Sterne bekommen kannst, oder soll ich hier einfach weiterschreiben?
Danke im Voraus.
erstmal: stelle die neuen Fragen nochmal, nicht nur wg. evtl Sterne (die gerne auch), sondern weil andere auch oft gute Lösungsansätze haben und ich nicht weiß, wann ich zum Bearbeiten komme. Solche Antworten verschlingen doch viel Zeit und ich bin ja Amateur mit noch anderen Interessen/Tätigkeiten!
Nun zur Erklärung obiger Formel:
IFERROR(1*(SEARCH($A$1:$A$148;E1;1)>0);0)
erstens: Du verwendest darin einen Zellenbereich (A1:A148), in dem jede Zelle ein anderes Ergebnis liefern kann. Also brauchst Du auch schon für diesen Formelteil eine Matrixformel: {Ctrl+shift+Enter!} !!
xl erstellt dann im Hintergrund eine Ergebnis"spalte" (-Variable)
Search liefert aber ERROR, wenn der gesuchte String nicht zu finden ist. Ein einziger Fehler in der Ergebnisvariablen reicht, um alles zu Fall zu bringen. Wird aber durch IFERROR bei Fehler 0 gesetzt, liefern alle Fehler Nullen, die nicht stören.
Nur die Zelle, in der der String vorkommt, liefert eine Zahl (Position, >0). Um das zu 1 zu machen, egal, wo das losgeht, die Wahrheitsabfrage >0? Und da TRUE ein Text ist, der aber mit +0 oder *1 zu einer 1 gemacht werden kann, ergibt sich dieser Formelteil.
Diese einzige 1 in dem Zellbereich wird nun mit der Zeile() ihres Vorkommens in der Liste multipliziert (Summenprodukt zählt die vielen Ergebnis-Nullen ohne Beeinflussung dazu) und die sich ergebende ZeilenNummer wird letztlich durch INDEX(Spaltenbereich;Ergebnis-Zeile) ausgelesen.
Eine gute Einführung in Matrixformeln gibts bei P.Haserodt: www.online-excel.de/excel/singsel.php?f=26 ff Dauert aber schon ein paar Stunden, bis man durch die 7 "Stationen" durch ist, und dann fehlt noch die Übung. Aber versuchs mal.
Es gibt übrigens einige Formeln, die analog Matrixformeln arbeiten aber keine solche Eingabe brauchen: Summenprodukt() ist das bekannteste Beispiel (und kann auch nicht wirklich alles, was Matrixformeln können). Im vorliegenden Fall brauchts die Matrixeingabe zusätzlich.
Hoffe, das reicht zum Weiterkommen.
Zum Anwenden auf andere Blätter: morgren, ist mir jetzt zu spät geworden!(halb vier nachts!)
nur noch schnell, bevor ich aus dem Haus muss, um Missverstzändnis zu vermeiden: Die letztgenannte Teilformel
IFERROR(.....
liefert ja so viele Ergebnisse, wie in der Liste stehen und abgefragt werden. Das Ergebnis ist also eine Liste von lauter 0 und einmal >0.
So eine Liste kann man auch bei Eingabe als Matrixformel nir darstellen, wenn man DER LISTE ENTSPRECHEN VIELE Zellen markiert und in eine dann (bei fortbestehender Markierung) die {Matrix}formel eingibt.
Vllt hilft das auch der Vorstellung bzgl des Arbeitens von Matrixformeln. So eine Liste kann man aber anschließend nur als Ganzes ändern, einzelne Zell(Grupp)en können nicht veröndert werden, kriegst du aber sofort so ähnlich gemeldet.
Ging schneller, als ich dachte:Hab jetzt Summenprodukt ersetzt durch KGrösste, damit auch die "hellorangene Zucchini" oder das Erdbeer-Kirschen-Gelee ...
(Beim Yoghurt ist das fast automatisch das linke, es sei denn, Du hast einen Zuckersüssen oder einen Zimt-Yoghurt, aber meist schreibt man heute ohnehin Joghurt, da kann das dann sehr akut werden)
...erfasst werden kann (solche Dopplungen sind gar nicht so selten, wie Du siehst.
Den Namen des zu durchsuchende Blatts schreibst du in eine Zelle und zwar gleich mit dem ! hintendran, also zB (bei mir jetzt) Tabe02! (oder wie immer das Blatt heißt). Die Zelle benennst Du als Bereich, ich hab BlaNa gewählt.
Die Auswertung muss allerdings mit Verstand erfolgen: Steht Dein zu identifizierendes Item in A2, dann
in B2: =INDEX(INDIRECT(BlaNa&"$B$1:$B$10"),LARGE(IFERROR(1*(SEARCH(INDIRECT(BlaNa&"$A$1:$A$10"),A2,1)>0),0)*ROW(INDIRECT(BlaNa&"$A$1:$A$10")),1),1)
in C2: =INDEX(INDIRECT(BlaNa&"$B$1:$B$10"),LARGE(IFERROR(1*(SEARCH(INDIRECT(BlaNa&"$A$1:$A$10"),A2,1)>0),0)*ROW(INDIRECT(BlaNa&"$A$1:$A$10")),2),1)
in D2: =INDEX(INDIRECT(BlaNa&"$B$1:$B$10"),LARGE(IFERROR(1*(SEARCH(INDIRECT(BlaNa&"$A$1:$A$10"),A2,1)>0),0)*ROW(INDIRECT(BlaNa&"$A$1:$A$10")),3),1)
Alle als {Matrix}formeln !!!
Könntest auch noch KGrösste(...;4) bzw LARGE(...,4) in E2 schreiben, falls erforderlich
Das allerletzte ,1)-Argument kann man sich sparen, bei Spalten ist das in INDEX(...) entbehrlich, da automatisch 1 gesetzt, wenn nicht genannt. [Bei Zeilen muss es rein, da die Syntax so heißt:=Index(Bereich;Zeile;Spalte)]
Ich hoffe, Du kommst jetzt damit zurecht, Rückmeldung wäre schön, aber mir reicht das jetzt erst mal (immerhin hab ich selber auch wieder was dazugelernt ;)
PS: hab meine kleine Makroformel zum Formel-übersetzen noch nie so gut gebrauchen können wie diesmal!
Sorry für späte Antwort. Ich muss alle diese Information zuerst abarbeiten, und testen :). Ich denke das wird für einen Excel Anfänger bisschen Zeit in Anspruch nehmen. Aber sicher melde ich mich nach meinem Test hier wieder!
Ich schreibe dann noch die anderen eher einfacheren Fragen separat.
Ich bedanke mich bei dir rechtherzlich und bis bald.
Danke für die vorläufige Rückmeldung!
Natürlich ist es immer problematisch, wenn man seinen Führerschein auf einem Goggomobil gemacht hat und dann sofort einen Maserati steuern soll. (ich habe lange aufs Auto verzichtet und komme mit den modernen Armaturen auch nicht so ohne weiteres klar...)
Aber Deine Rückfragen und Kommentare machen mir durchaus den Eindruck, als seist Du begabt!
Die von mir angeführten Komplikationen (Stachelbeer-Kiwi-Yoghurt zB) kann xl natürlich nicht auflösen, aber vllt durchsichtiger machen. Xl kann nicht wissen, dass Apfelringe heutzutage Spüßwaren sind und vom Apfel nur Form und Farbe haben. Oder dass Zitronenlimonade ein Getränk ist: aber es würde die Alternativen Obst und Limo als Getränk anzeigen.
Zur Toleranz gegen Rechtschreib-Fehler/Alternativen: Nimm zB nur oghurt in die Liste auf, dann ist es egal, ob mit Y oder mit J geschrieben. itrone findet Citrone wie auch Zitrone, ulasch Gulasch wie Goulasch(? gibts das?) , ugat Nougat wie Nugat u.v.a.m. Geht natürlich nicht mit allem.
Ich wünsche dir jedenfalls viel Erfolg!
Wenn mir noch was einfällt, bewerte ich Deinen letzten Kommentar, damit Du nochmal reinschaust!
Servus. Ich habe leider bisher keine Zeit gehabt, mich mit Excel zu beschäftigen. Arbeit usw... Heute habe mich wieder hingesetzt, und habe mir deine letzte Hilfe angeguckt. Schwer ... :). Ich muss wieder reinkommen und genau zu verstehen was du da genau gemacht hast. Vielleicht ist es besser, wenn ich eine excel datei erstelle und dir schicke, damit man auf einem konkreten Beispiel arbeitet.
Ich versuch ein bisschen und melde mich wahrscheinlich wieder...
stell einen FA, damit wir nichtöffentlich mail-Adressen austauschen können!
Es tut mir leid, aber ich habe leider nicht verstanden was du meinst. Was meinst du mit FA?
Das Problem mit deiner Formel ist, dass bei nicht gefundenem ersten OR-Argument (auf gut deutsch, wenn "Apfel" nicht gefunden wird), ein Fehler (#VALUE) zurückgegeben wird. Um dies abzufangen musst du IFERROR oder alternativ IF(ISERROR(... verwenden.
Die Formel könnte dann etwa so lauten:
=IF(ISERROR(Search("Apfel";A1)>0);IF(ISERROR(Search("Orangen";A1)>0);"Nothing";"Obst");"Obst")
Diese Vorgehensweise mag für 2-3 Begriffe noch funktionieren, für mehr wird sie aber schnell unübersichtlich.
Besser ist es, sich eine Positiv-Liste aller Begriffe anzulegen und dann auf diese zu verweisen, mit SVERWEIS oder besser INDEX(VERGLEICH), englisch VLOOKUP und INDEX(MATCH)
Nachtrag: Man kann statt der Verschachtelung natürlich auch die Bedingungen mit OR verknüpfen, wie in deiner Formel.
ISERROR liefert ein TRUE, wenn ein Fehler auftritt; in diesem Fall also wenn etwas nicht gefunden wurde. Um dies umzudrehen, verwendet man NOT(ISERROR(...).
Vielen Dank für deine Antwort. Es hat wie folgt funktioniert:
=WENNFEHLER(WENN(ODER(WENNFEHLER(WENN(SUCHEN("Apfel";C5);1)=1;0);WENNFEHLER(WENN(SUCHEN("orangen";C5);1)=1;0));"Obst";"nix");"nix")
Aber so wird es ziemlich unübersichtlich, da ich ca. 20 unterschiedlichen Wörtern suchen muss und jenach gefundenem Wort ein bestimmtes Ergebnis in eine Zelle schreiben muss (MATRIX). Daher mir fehlt hier eine Funktion, die folgendes tut:
Suche in einer nach definierten Wörtern, und gibt True raus, wenn gefunden, wenn nicht dann False.
Ich hätte mir so vorgestellt, aber es geht leider nicht:
=SUCHEN(ODER("Apfel";"Orangen";"Trauben"))
Weder "Suchen" noch "Finden" funktioniert für die Logik. Gibt es in Excel eine Funktion, die mir diese Logik einfacher ermöglicht?
Gar nicht so einfach, da Du ja schreibst, dass diese Wörter in Text eingebettet sind!
jetzt aber... (ist auch getestet)
sieht noch komisch aus, aber funktioniert - zumindest um nach "Orangen" und "Apfel" zu suchen...
=WENNFEHLER(WENN(ODER(WENNFEHLER(WENN(SUCHEN("Apfel";A1);1;"")=1;0);WENNFEHLER(WENN(SUCHEN("orangen";A1);1;"")=1;0));"Obst";"nix");"nix")
Vielen Dank für deine Antwort. Es hat wie folgt funktioniert:
=WENNFEHLER(WENN(ODER(WENNFEHLER(WENN(SUCHEN("Apfel";C5);1)=1;0);WENNFEHLER(WENN(SUCHEN("orangen";C5);1)=1;0));"Obst";"nix");"nix")
Aber so wird es ziemlich unübersichtlich, da ich ca. 20 unterschiedlichen Wörtern suchen muss und jenach gefundenem Wort ein bestimmtes Ergebnis in eine Zelle schreiben muss (MATRIX). Daher mir fehlt hier eine Funktion, die folgendes tut:
Suche in einer nach definierten Wörtern, und gibt True raus, wenn gefunden, wenn nicht dann False.
Ich hätte mir so vorgestellt, aber es geht leider nicht:
=SUCHEN(ODER("Apfel";"Orangen";"Trauben"))
Weder "Suchen" noch "Finden" funktioniert für die Logik. Gibt es in Excel eine Funktion, die mir diese Logik einfacher ermöglicht?
Sollte so klappen:
=WENN(WENNFEHLER(SUCHEN("Apfel";A1);0)+WENNFEHLER(SUCHEN("Orangen";A1);0)+WENNFEHLER(SUCHEN("Trauben";A1);0)>0;"Obst";"Nothing")
Das ins Englische umzusetzen müsstest Du selbst hinkriegen, oder?
Vielen Dank für deine Antwort. Es hat wie folgt funktioniert:
=WENNFEHLER(WENN(ODER(WENNFEHLER(WENN(SUCHEN("Apfel";C5);1)=1;0);WENNFEHLER(WENN(SUCHEN("orangen";C5);1)=1;0));"Obst";"nix");"nix")
Aber so wird es ziemlich unübersichtlich, da ich ca. 20 unterschiedlichen Wörtern suchen muss und jenach gefundenem Wort ein bestimmtes Ergebnis in eine Zelle schreiben muss (MATRIX). Daher mir fehlt hier eine Funktion, die folgendes tut:
Suche in einer nach definierten Wörtern, und gibt True raus, wenn gefunden, wenn nicht dann False.
Ich hätte mir so vorgestellt, aber es geht leider nicht:
=SUCHEN(ODER("Apfel";"Orangen";"Trauben"))
Weder "Suchen" noch "Finden" funktioniert für die Logik. Gibt es in Excel eine Funktion, die mir diese Logik einfacher ermöglicht?
unübersichtlich: ja, ohne Liste wird es das, mit Liste aber komplizierter. Du kannst in xl übrigens nach einzelnen Formelteilen mit Alt+Enter Umbrüche erzwingen, die das ganze etwas übersichtlicher machen! (zB immer vor dem ;Wennfehler(...
(nochmal: vor dem Semicolon! probierst du am besten aus)
Spontan getippt... Die klammern sind falsch gesetz...
Die einzelnen Oder(Or) Abfragen müssen nochmal in Klammern gesetzt werden also =if(or(search("apfel";a1)>0);(search("Orangen";a1)>0);"Obst";"nix")
notfalls mal in die formelauswertung schauen und guggen, wo der Fehler produziert wird...
nebenbei schaut die formel auch nur, was in a1 steht...
Es hat leider nicht funktioniert, bringt mir Fehlermeldung...
search verlangt noch als drittes Argument ab wo im Text zu suchen ist (warum MS da nicht längst als Standard-Argument 1 gesetzt hat, ist mir schleierhaft. Wäre sooo einfach!)
ausserdem liefert es bei Nichtfinden Fehler, den man auch erst umschiffen muss!
=INDEX([Mappe3]Tabelle2!$B$1:$B$5,SUMPRODUCT(IFERROR(1*(SEARCH([Mappe3]Tabelle2!$A$1:$A$5,A1,1)>0),0)*ROW([Mappe3]Tabelle2!$A$1:$A$5)))
Das [Mappe3] musst Du noch entfernen oder Deinen Dateinamen einsetzen. A1 ist der einzige Bezug im Ergebnisblatt, kannst Du runterkopieren.
Zum Erweitern der Liste beliebig viele neue Zeilen einfügen oberhalb Zucchini und unterhalb AAA, egal, ob alphabetisch oder nicht. Die Bezüge passen sich an. Machst Du A1 zu $A1, kannst Du die Formel auch nach rechts kopieren, sie bezieht sich dann immer auf den Eintrag in Spalte A (oft aber gar nicht erwünscht)
Und natürlich Tabelle durch wahrscheinlich Sheet etc. ersetzen.