Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Zelle mit Text nach Wörterliste durchsuchen
808 views
Skip to first unread message
beckma...@googlemail.com
May 7, 2014, 5:52:59 AM5/7/14
to
Hallo,
ich arbeite gerade an einer Tabelle in der es notwendig ist, eine Textzeile nach verschiedenen Wörtern aus einer anderen Spalte zu durchsuchen.
Ich nutze dabei diesen Befehl.
{=WENN(SPALTE(E2)>SUMME(ZÄHLENWENN($E2;WENN(DB!$D$2:$D$500="";"ÿ";"*"&DB!$D$2:$D$500&"*"))); "";INDEX(DB!$D:$D;KKLEINSTE(WENN(WECHSELN($E2;DB!$D$2:$D$500;)<>$E2;ZEILE($2:$500); 9^9); SPALTE(E2))))}
{} nicht mit kopieren sondern Matrixbefehl mit STRG+Shift+Enter auslösen
E2 ist hierbei die zu durchsuchende Zelle
DB!D2:D500 ist die Spalte nach deren Worten im Text gesucht werden soll
Ich habe mir die Formel nicht selbst ausgedacht, sondern von einer anderen Website kopiert.
Im Prinzip funktioniert das ganze schon ganz gut, nur das nicht alle Wörter gefunden werden, obwohl sie in der Liste enthalten sind. Wenn ich nachträglich Wörter zu der Liste hinzufüge wird diese auch nicht aktualisiert.
Ich habe auch das Gefühl, dass bei längeren Texten die Wörter weniger gefunden werden.
Die automatisch Aktualisierung der Formeln ist aktiviert, wenn ich Sachen hinzufüge ist er auch einen Moment damit beschäftigt, alles noch einmal "durchzurechnen" nur das neue Wörter dann trotzdem nicht gefunden werden.
Hat jemand evtl. eine Idee woran das liegen kann, das ich keine weiteren Wörter hinzufügen kann und er bei längeren Texten es nicht hinbekommt die Wörter zu finden, obwohl sie von vornerein in der Spalte vorhanden waren?
Wenn jemand eine schönere(kürzere) Formel hat, nehme ich diese natürlich auch gerne. :-)
Grüße
P.S.
Ich habe da teilweise Wörter mit französischen Accents drin, kann man Excel sagen, das er auf Accents nicht achten soll?
ich arbeite gerade an einer Tabelle in der es notwendig ist, eine Textzeile nach verschiedenen Wörtern aus einer anderen Spalte zu durchsuchen.
Ich nutze dabei diesen Befehl.
{=WENN(SPALTE(E2)>SUMME(ZÄHLENWENN($E2;WENN(DB!$D$2:$D$500="";"ÿ";"*"&DB!$D$2:$D$500&"*"))); "";INDEX(DB!$D:$D;KKLEINSTE(WENN(WECHSELN($E2;DB!$D$2:$D$500;)<>$E2;ZEILE($2:$500); 9^9); SPALTE(E2))))}
{} nicht mit kopieren sondern Matrixbefehl mit STRG+Shift+Enter auslösen
E2 ist hierbei die zu durchsuchende Zelle
DB!D2:D500 ist die Spalte nach deren Worten im Text gesucht werden soll
Ich habe mir die Formel nicht selbst ausgedacht, sondern von einer anderen Website kopiert.
Im Prinzip funktioniert das ganze schon ganz gut, nur das nicht alle Wörter gefunden werden, obwohl sie in der Liste enthalten sind. Wenn ich nachträglich Wörter zu der Liste hinzufüge wird diese auch nicht aktualisiert.
Ich habe auch das Gefühl, dass bei längeren Texten die Wörter weniger gefunden werden.
Die automatisch Aktualisierung der Formeln ist aktiviert, wenn ich Sachen hinzufüge ist er auch einen Moment damit beschäftigt, alles noch einmal "durchzurechnen" nur das neue Wörter dann trotzdem nicht gefunden werden.
Hat jemand evtl. eine Idee woran das liegen kann, das ich keine weiteren Wörter hinzufügen kann und er bei längeren Texten es nicht hinbekommt die Wörter zu finden, obwohl sie von vornerein in der Spalte vorhanden waren?
Wenn jemand eine schönere(kürzere) Formel hat, nehme ich diese natürlich auch gerne. :-)
Grüße
P.S.
Ich habe da teilweise Wörter mit französischen Accents drin, kann man Excel sagen, das er auf Accents nicht achten soll?
Claus Busch
May 7, 2014, 7:49:53 AM5/7/14
to
Hallo Max,
Am Wed, 7 May 2014 02:52:59 -0700 (PDT) schrieb
beckma...@googlemail.com:
> ich arbeite gerade an einer Tabelle in der es notwendig ist, eine Textzeile nach verschiedenen W�rtern aus einer anderen Spalte zu durchsuchen.
mache es doch mit VBA. Folgender Code in einem Standardmodul z�hlt dir
ab F2 die �bereinstimmenden Treffer auf:
Sub WoerterAufzaehlen()
Dim arrCheck As Variant, arrConst As Variant
Dim arrOut() As Variant
Dim i As Long, j As Long, n As Long
arrCheck = Sheets("DB").Range("D2:D500")
arrConst = Split(Range("E2"), " ")
For i = LBound(arrConst) To UBound(arrConst)
For j = LBound(arrCheck) To UBound(arrCheck)
If arrConst(i) = arrCheck(j, 1) Then
ReDim Preserve arrOut(n)
arrOut(n) = arrCheck(j, 1)
n = n + 1
Exit For
End If
Next
Next
Range("F2").Resize(rowsize:=UBound(arrOut) + 1) = _
Application.Transpose(arrOut)
End Sub
Mit freundlichen Gr��en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Am Wed, 7 May 2014 02:52:59 -0700 (PDT) schrieb
beckma...@googlemail.com:
> ich arbeite gerade an einer Tabelle in der es notwendig ist, eine Textzeile nach verschiedenen W�rtern aus einer anderen Spalte zu durchsuchen.
mache es doch mit VBA. Folgender Code in einem Standardmodul z�hlt dir
ab F2 die �bereinstimmenden Treffer auf:
Sub WoerterAufzaehlen()
Dim arrCheck As Variant, arrConst As Variant
Dim arrOut() As Variant
Dim i As Long, j As Long, n As Long
arrCheck = Sheets("DB").Range("D2:D500")
arrConst = Split(Range("E2"), " ")
For i = LBound(arrConst) To UBound(arrConst)
For j = LBound(arrCheck) To UBound(arrCheck)
If arrConst(i) = arrCheck(j, 1) Then
ReDim Preserve arrOut(n)
arrOut(n) = arrCheck(j, 1)
n = n + 1
Exit For
End If
Next
Next
Range("F2").Resize(rowsize:=UBound(arrOut) + 1) = _
Application.Transpose(arrOut)
End Sub
Mit freundlichen Gr��en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Max
May 7, 2014, 7:59:47 AM5/7/14
to
Hallo Claus,
auf VBA wollte ich nach Möglichkeit verzichten, da diese Tabelle für andere Nutzer sich selbst aktualisieren soll und diese nur die neuen Informationen einfügen und die Formeln sich dann automatisch anpassen.
Eine Formel die schonmal besser funktioniert ist diese:
{=WENN(MAX(WENN(ISTFEHLER(FINDEN(WENN(DB!$D$2:$D$500<>"";DB!$D$2:$D$500;"@");D4;1));0;1))=1;"Ja";"Nein")}
Nur das sie leider nur Ja oder Nein ausgibt. Wie könnte ich diese noch anpassen damit der gefundene Suchbegriff ausgegeben wird?
Grüße
auf VBA wollte ich nach Möglichkeit verzichten, da diese Tabelle für andere Nutzer sich selbst aktualisieren soll und diese nur die neuen Informationen einfügen und die Formeln sich dann automatisch anpassen.
Eine Formel die schonmal besser funktioniert ist diese:
{=WENN(MAX(WENN(ISTFEHLER(FINDEN(WENN(DB!$D$2:$D$500<>"";DB!$D$2:$D$500;"@");D4;1));0;1))=1;"Ja";"Nein")}
Nur das sie leider nur Ja oder Nein ausgibt. Wie könnte ich diese noch anpassen damit der gefundene Suchbegriff ausgegeben wird?
Grüße
Claus Busch
May 7, 2014, 8:49:00 AM5/7/14
to
> Ich nutze dabei diesen Befehl.
> {=WENN(SPALTE(E2)>SUMME(Z�HLENWENN($E2;WENN(DB!$D$2:$D$500="";"�";"*"&DB!$D$2:$D$500&"*"))); "";INDEX(DB!$D:$D;KKLEINSTE(WENN(WECHSELN($E2;DB!$D$2:$D$500;)<>$E2;ZEILE($2:$500); 9^9); SPALTE(E2))))}
da du SPALTE verwendest, ziehst du also nach rechts und nicht nach
unten. Es ist klar, dass du nicht alle Worte bekommst, denn du hast als
Z�hler SPALTE(E2) eingebaut. Du f�ngst also erst beim 5. Treffer an.
�ndere die Formel mal so um:
=WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2));ZEILE($1:$499));SPALTE(A1))))
und beende die Eingabe mit STRG+Shift+Enter
Max
May 7, 2014, 9:38:14 AM5/7/14
to
Am Mittwoch, 7. Mai 2014 14:49:00 UTC+2 schrieb Claus Busch:
> Hallo Max,
>
>
>
> Am Wed, 7 May 2014 02:52:59 -0700 (PDT) schrieb
>
>
> Hallo Max,
>
>
>
> Am Wed, 7 May 2014 02:52:59 -0700 (PDT) schrieb
>
>
> > Ich nutze dabei diesen Befehl.
>
> > {=WENN(SPALTE(E2)>SUMME(Z�HLENWENN($E2;WENN(DB!$D$2:$D$500="";"�";"*"&DB!$D$2:$D$500&"*"))); "";INDEX(DB!$D:$D;KKLEINSTE(WENN(WECHSELN($E2;DB!$D$2:$D$500;)<>$E2;ZEILE($2:$500); 9^9); SPALTE(E2))))}
>
>
>
>
> da du SPALTE verwendest, ziehst du also nach rechts und nicht nach
>
> unten. Es ist klar, dass du nicht alle Worte bekommst, denn du hast als
>
> Z�hler SPALTE(E2) eingebaut. Du f�ngst also erst beim 5. Treffer an.
>
>
> da du SPALTE verwendest, ziehst du also nach rechts und nicht nach
>
> unten. Es ist klar, dass du nicht alle Worte bekommst, denn du hast als
>
>
> �ndere die Formel mal so um:
>
> =WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2));ZEILE($1:$499));SPALTE(A1))))
>
> und beende die Eingabe mit STRG+Shift+Enter
>
>
>
>
>
> Mit freundlichen Gr��en
> =WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E$2));ZEILE($1:$499));SPALTE(A1))))
>
> und beende die Eingabe mit STRG+Shift+Enter
>
>
>
>
>
>
> Claus
>
> --
>
> Vista Ultimate / Windows7
>
> Office 2007 Ultimate / 2010 Professional
Hallo,
> Claus
>
> --
>
> Vista Ultimate / Windows7
>
> Office 2007 Ultimate / 2010 Professional
klappt schonmal deutlich besser :) - vielen Dank.
Ein Problem hat er jedoch immernoch, das er denselben Suchbegriff nicht immer findet. Ich habe fünf Zellen mit Text untereinander die alle die Buchstabenkombi ABC enthalten, beim zweiten Eintrag findet er es, bei den Zellen 1,3,4 und 5 jedoch nicht.
Und wenn ich händisch an die Liste der Wörter nach denen er suchen soll etwas dranhänge, wird das gefundene Wort in der Haupttabelle nur teilweise aktualisiert.
Ich habe aktuell einen Datensatz den er durchsuchen soll von 710 Einträgen und eine Wörterliste(die sich jedoch wiederholt, geht leider nicht anders) von 220 Wörtern.
Max
May 7, 2014, 9:40:32 AM5/7/14
to
Man kann ja gar nicht editieren. Ich muss mich hier gerade erst noch an die Funktionen gewöhnen, auch das Eintragen meiner Emailadresse, war gelinde gesagt suboptimal.
Welchen Zweck hat die Spalte(A1) am Ende des Terms?
Welchen Zweck hat die Spalte(A1) am Ende des Terms?
Claus Busch
May 7, 2014, 10:02:39 AM5/7/14
to
Hallo Max,
Am Wed, 7 May 2014 06:40:32 -0700 (PDT) schrieb Max:
> Welchen Zweck hat die Spalte(A1) am Ende des Terms?
du verwendest doch die Funktion KKLEINSTE in der Formel und die ben�tigt
ein zweites Argument. Das Argument das festlegt das wievielt Kleinste.
Spalte(A1) ergibt 1, gibt dir also den ersten Treffer. Beim Ziehen nach
Rechts erh�ht sich das auf Spalte(B1) . Das ergibt 2 und zeigt dir den
zweiten Treffer usw.
Mit freundlichen Gr��en
Am Wed, 7 May 2014 06:40:32 -0700 (PDT) schrieb Max:
> Welchen Zweck hat die Spalte(A1) am Ende des Terms?
ein zweites Argument. Das Argument das festlegt das wievielt Kleinste.
Spalte(A1) ergibt 1, gibt dir also den ersten Treffer. Beim Ziehen nach
Rechts erh�ht sich das auf Spalte(B1) . Das ergibt 2 und zeigt dir den
zweiten Treffer usw.
Mit freundlichen Gr��en
Claus Busch
May 7, 2014, 10:15:51 AM5/7/14
to
Hallo Max,
Am Wed, 7 May 2014 06:38:14 -0700 (PDT) schrieb Max:
> Ein Problem hat er jedoch immernoch, das er denselben Suchbegriff nicht immer findet. Ich habe f�nf Zellen mit Text untereinander die alle die Buchstabenkombi ABC enthalten, beim zweiten Eintrag findet er es, bei den Zellen 1,3,4 und 5 jedoch nicht.
>
> Und wenn ich h�ndisch an die Liste der W�rter nach denen er suchen soll etwas dranh�nge, wird das gefundene Wort in der Haupttabelle nur teilweise aktualisiert.
=WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2));ZEILE($1:$499));SPALTE(A1))))
wenn du die Formel auch nach unten ziehst, darf E2 nicht absolut sein.
�ndere $E$2 auf $E2 um. Wenn dein "W�rterbuch" in D2:D500 steht, muss
trotzdem der Z�hler in der Formel bei 1 anfangen und da dieser Z�hler
die gleich Anzahl an Zeilen haben muss, darf er dann nur bis 499 gehen.
Wenn du diese Suchw�rter erweiterst, musst du auch in der Formel die
Bez�ge erweitern. Du k�nntest f�r deinen Bereich einen dynamischen
Bereichsnamen erstellen und diesen in der Formel verwenden. Wenn du dann
noch die Anzahl der W�rter �ber eine Formel in das Blatt schreibst,
k�nntest du diese Zelle �ber INDIREKT in deine Formel aufnehmen. Dann
w�re die Formel immer korrekt, wenn du in DB W�rter entfernst oder
hinzuf�gst.
Mit freundlichen Gr��en
Am Wed, 7 May 2014 06:38:14 -0700 (PDT) schrieb Max:
> Ein Problem hat er jedoch immernoch, das er denselben Suchbegriff nicht immer findet. Ich habe f�nf Zellen mit Text untereinander die alle die Buchstabenkombi ABC enthalten, beim zweiten Eintrag findet er es, bei den Zellen 1,3,4 und 5 jedoch nicht.
>
> Und wenn ich h�ndisch an die Liste der W�rter nach denen er suchen soll etwas dranh�nge, wird das gefundene Wort in der Haupttabelle nur teilweise aktualisiert.
=WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2));ZEILE($1:$499));SPALTE(A1))))
wenn du die Formel auch nach unten ziehst, darf E2 nicht absolut sein.
�ndere $E$2 auf $E2 um. Wenn dein "W�rterbuch" in D2:D500 steht, muss
trotzdem der Z�hler in der Formel bei 1 anfangen und da dieser Z�hler
die gleich Anzahl an Zeilen haben muss, darf er dann nur bis 499 gehen.
Wenn du diese Suchw�rter erweiterst, musst du auch in der Formel die
Bez�ge erweitern. Du k�nntest f�r deinen Bereich einen dynamischen
Bereichsnamen erstellen und diesen in der Formel verwenden. Wenn du dann
noch die Anzahl der W�rter �ber eine Formel in das Blatt schreibst,
k�nntest du diese Zelle �ber INDIREKT in deine Formel aufnehmen. Dann
w�re die Formel immer korrekt, wenn du in DB W�rter entfernst oder
hinzuf�gst.
Mit freundlichen Gr��en
Claus Busch
May 7, 2014, 10:43:16 AM5/7/14
to
Hallo Max,
Am Wed, 7 May 2014 16:15:51 +0200 schrieb Claus Busch:
> =WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2));ZEILE($1:$499));SPALTE(A1))))
in dieser Formel ist immer noch ein Fehler drin. Bei der Abfrage am
Anfang muss man auch von 1, also von Spalte(A1) ausgehen.
Schau mal hier:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3%21326#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
nach der Mappe "W�rterZ�hlen".
Die ist mit dynamischen Bereichsnamen und die Formel passt sich dadurch
dem Datenbestand in DB Spalte D an.
Am Wed, 7 May 2014 16:15:51 +0200 schrieb Claus Busch:
> =WENN(SPALTE()>SUMME(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2))*1);"";INDEX(DB!$D$2:$D$500;KKLEINSTE(WENN(ISTZAHL(SUCHEN(DB!$D$2:$D$500;$E2));ZEILE($1:$499));SPALTE(A1))))
Anfang muss man auch von 1, also von Spalte(A1) ausgehen.
Schau mal hier:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3%21326#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
nach der Mappe "W�rterZ�hlen".
Die ist mit dynamischen Bereichsnamen und die Formel passt sich dadurch
dem Datenbestand in DB Spalte D an.
0 new messages