Du bist hier::--Excel Formeln – Anzahl bestimmter Wörter in einem Satz zählen

Excel Formeln – Anzahl bestimmter Wörter in einem Satz zählen

[imgr=logo-470.png]logo-80.png?nocache=1309248436679[/imgr]In diesem Excel Artikel werde ich ihnen heute zeigen, wie Sie es schaffen, dass die Anzahl der gesuchten Wörter in einem Satz angezeigt wird. Die Wörter, nach denen gesucht werden soll, stehen dabei in einer eigenen Spalte.

Die Funktion, die ich ihnen jetzt zeigen möchte, ist eigentlich ziemlich ähnlich zu einer anderen, die ich schon einmal vorgestellt habe. Diesen Artikel finden Sie übrigens hier. Hier habe ich ihnen gezeigt, wie Sie ausgeben können, wie oft ein bestimmtes Wort in einem Bereich aufgetreten ist. Dabei hat es sich wie gesagt um einen Bereich gehandelt, in diesem Fall geht es jetzt aber explizit nur um eine Zelle. Der andere Unterschied ist der, dass ihnen in diesem Artikel erklärt wird, wie man auch nach mehreren Wörtern suchen kann. Dies war im anderen Beispiel nicht gegeben. Wie im anderen Artikel aber auch, werden die zu suchenden Wörter in eine extra Spalte eingetragen. Wie das nun alles funktioniert, sehen Sie jetzt.

Beginn:

Bevor Sie damit anfangen, die Anzahl an Wörtern auszugeben, die im Text vorkommen, müssen Sie erst einmal eine Tabelle erstellen. Diese Tabelle ist zweigeteilt. In die Spalte A dieser Tabelle schreiben Sie die Wörter, die im Text gesucht werden sollen. Dabei können Sie so viele Wörter schreiben, wie Sie möchten, diese müssen aber immer wieder in einer neuen Zelle stehen.  Eigentlich ist auch die Anzahl an den verschiedenen Wörtern unbegrenzt, sollten Sie jedoch die gleiche Formel wie ich verwenden möchten, so schreiben Sie ihre Einträge bis Zeile 6. Bis dahin müssen dann aber auch alle Zellen wirklich ausgefüllt sein, damit die Formeln funktionieren. Schaffen Sie es nicht, diese Angaben genau einzuhalten, dann ändern Sie die Formeln bitte entsprechend um. Lassen Sie die Spalte C am besten frei, da hier später der Text und die Formel eingegeben werden. Haben sie ihre Einträge nun gemacht, dann schauen Sie, ob ihre Tabelle nun ungefähr so aussieht, wie die im Screenshot und gehen Sie dann zum nächsten Schritt weiter.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-beginn-200.png?nocache=1309017314965

Mit leeren Zellen:

Grundsätzlich haben Sie für die Berechnung zwei Formeln zur Verfügung. Die eine reagiert richtig auf leere Zellen im Zellbereich, die andere jedoch nicht. Zuerst werde ich ihnen die Formel zeigen, bei der es nichts ausmacht, ob im Zellbereich leere Zellen auftreten oder nicht. Aber auch bei dieser Formel gibt es wieder zwei Unterscheidungen. Die eine ist für den Fall, dass im zu untersuchenden Satz die Wörter nur einmal auftreten dürfen, die andere hingegen hat auch damit kein Problem. Von diesen beiden Formeln werde ich nun wieder die erste behandeln, also die, dass keines der gesuchten Wörter doppelt oder noch häufiger auftreten dürfen.

Wörter werden nur einmal gezählt:

Wie schon gesagt, behandle ich zuerst die Formel, dass die auftretenden Wörter nur einmal gezählt werden. Bevor mit der Ausgabe begonnen werden kann, müssen Sie aber erst einmal einen Satz eingeben, in dem die anderen Wörter überprüft werden sollen. Diesen Satz schreiben Sie in die Zelle C1. In meinem Beispiel lautet er „Ich esse einen Apfel und eine Erdbeere“. Dieses Beispiel habe ich bewusst so gewählt, da ich ihnen zeigen möchte, was passiert, wenn die gesuchten Wörter aus Spalte A einzeln im Text auftreten. Um nun die Anzahl an den Wörtern aus Spalte A zu bekommen, die sich auch im Text wiederfinden, benötigen Sie selbstverständlich eine Formel. Tragen Sie diese am besten in der Zelle C2 ein. Sie lautet:

=ANZAHL(FINDEN(WENN(A1:A9="";"µ";A1:A9);C1))

Diese Formel liefert aber noch kein richtiges Ergebnis, wenn Sie sie eingeben. Das kommt daher, dass die Formel eine Matrixformel ist. Wenn Sie sich häufiger mit Excel beschäftigen, wissen Sie, was nun zu tun ist. Ich möchte es aber nochmals erklären. Nachdem Sie die Formel eingegeben haben, klicken Sie erneut auf die Zelle mit der Formel, in meinem Beispiel also C2. Anschließend klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie abschließend auf ihrer Tastatur gleichzeitig die Tasten STRG+SHIFT+Enter. Nun erscheint in der Zelle der richtige Ausgabewert. In meinem Fall war das die Zahl „2“, da mit den Wörtern „Apfel“ und „Erdbeere“ im Satz zwei Wörter aufgetreten sind, die man auch in der Spalte A findet. Neben der korrekten Ausgabe in der Zelle hat sich aber auch die Formel verändert. Sie sieht nun so aus:

{=ANZAHL(FINDEN(WENN(A1:A9="";"µ";A1:A9);C1))}

Haben Sie ihren Satz nicht in die Zelle C1 geschrieben, so müssen Sie diesen Teil der Formel entsprechend umändern. Wie weit Sie den Zellbereich der zu suchenden Wörter machen, ist eigentlich egal. So könnte hier in der Formel statt „A1:A9“ auch „A1:A100“ stehen, und die Formel würde kein Problem damit haben. Jedoch muss der Zellbereich natürlich alle zu suchenden Wörter miteinschließen.

02-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-einzeln-470.png?nocache=1309017334781

So funktioniert die Formel aber nur, wenn eines der gesuchten Wörter nicht mehrmals im Satz auftritt. Ist das der Fall, so würde das Wort nur einmal gezählt werden. Um ihnen das zu demonstrieren, habe ich den Satz in der Zelle C1 verändert. Als neuen Satz habe ich hier „Willst du Orange und Paprika? Nur Orange“ eingetragen. Bei diesem Beispielsatz tritt das Wort „Orange“ nämlich zwei Mal auf. Nachdem, was ich ihnen über die Ausgabe der Formel erzählt habe, wenn manche Wörter aus der Liste häufiger auftreten, wissen Sie wahrscheinlich schon, welcher Wert ausgegeben wird. In meinem Fall war es nun logischerweise die „2“ anstelle einer „3“, die man normalerweise erwarten würde. Möchten Sie, dass die Formel genau so funktionieren soll, dann benötigen Sie den nächsten Teil nicht, denn in diesem werde ich erklären, wie Sie es schaffen, dass dieser „Fehler“ abgestellt wird.

03-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-fehler-470.png?nocache=1309017349477

Wörter werden immer gezählt:

Wie schon gesagt, werde ich ihnen nun zeigen, wie Sie die Formel verändern müssen, damit die Anzahl aller Wörter, die in der Zelle C1 vorkommen und auch in Spalte A eingetragen sind angezeigt wird. Den Satz von gerade eben können Sie eigentlich so unverändert lassen, da unsere neue Formel ja genau bei so einer Art von Sätzen anschlagen soll. Das einzige, was Sie verändern müssen ist die Formel. Löschen Sie dazu zuerst den aktuellen Inhalt der Zelle C2. Anschließend geben Sie folgendes ein:

=SUMME((LÄNGE(C1)-LÄNGE(WECHSELN(C1;A1:A9;)))/WENN(A1:A9="";1;LÄNGE(A1:A9)))

Wie die andere Formel auch, ist auch diese Formel eine Matrixformel. Das bedeutet, dass Sie nachdem Sie die Formel eingegeben haben nun wieder in die Zelle klicken müssen. Haben Sie das gemacht, so klicken Sie nun oben in die Bearbeitungszeile der Formel. Abschließend drücken Sie auf ihrer Tastatur wieder gleichzeitig die Tasten STRG+SHIFT+Enter. Nun ist in der Zelle der richtige Ausgabewert erschienen. In meinem Beispiel war das nun die Zahl „3“ für die Wörter „Paprika“ und zwei Mal „Orange“. Außerdem hat sich aber auch die Formel verändert. Sie sieht nun, wie für eine Matrixformel üblich, so aus:

{=SUMME((LÄNGE(C1)-LÄNGE(WECHSELN(C1;A1:A9;)))/WENN(A1:A9="";1;LÄNGE(A1:A9)))}

Steht ihr Text nicht in der Zelle C1, so ändern Sie diesen Zellbezug in der Formel wieder entsprechend ab. Bei „A1:A9“ ist so wie vorhin auch, dass hier auch noch mehrere Zellen mit einbezogen werden könnten, egal ob ihre Zellinhalte ausgefüllt sind. Jedoch muss die Angabe „A1:A9“ alle Wörter einschließend, auf die der Satz untersucht werden soll.

04-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-fehler-richitg-470.png?nocache=1309017366205

Ohne leere Zellen:

Bisher hat sich die Anleitung auf den Fall bezogen, dass in der Spalte A, in der die Wörter aufgelistet sind, auf die der Text untersucht werden soll, nicht alle Zellen ausgefüllt sein müssen, und somit auch Zellen auftreten können, die keinen Inhalt besitzen. Wissen Sie aber, dass in der Spalte A keine leere Spalte auftritt, so haben Sie die Möglichkeit zwei andere Formeln als oben zu verwenden, die außerdem auch noch kürzer sind. Aber auch bei ihnen wird wie oben wieder unterschieden, ob alle Wörter gezählt werden sollen, oder nur das erste Wort.

Wörter werden nur einmal gezählt:

Wie vorhin auch, werde ich mich zunächst wieder um den Fall kümmern, dass die Wörter nur einmal gezählt werden. Dazu habe ich meinen Satz wieder in den Beispielsatz vom Anfang umgewandelt, in meiner Zelle C1 steht nun also wieder „Ich esse einen Apfel und eine Erdbeere“. Da ich zu Beginn meine Tabelle schon so gewählt habe, dass in der Spalte A keine freie Zelle auftritt, muss ich hier in der Spalte A auch keine Änderungen vollziehen. Möchten Sie die Wirkung der Formel nur testen und Sie hatten in der Spalte A leere Zellen, so löschen Sie diese zuerst. Anschließend ändern Sie die Formel ab. Geben Sie nun, nachdem Sie die Zelle C2 geleert haben, folgendes ein:

=ANZAHL(FINDEN(A1:A6;C1))

Auch bei dieser Formel werden Sie zu Beginn noch kein richtiges Ergebnis geliefert bekommen, da die Formel wieder eine Matrixformel ist. Das heißt für Sie, dass Sie erneut die Zelle anklicken müssen und danach noch in die Bearbeitungszeile der Formel klicken. Abschließend drücken Sie wieder STRG+SHIFT+Enter. Nun ist in der Zelle wahrscheinlich der richtige Ausgabewert erschienen. In meinem Fall war das natürlich wieder die „2“ aufgrund der Wörter „Apfel“ und „Erdbeere“. Ist bei ihnen aber immer noch nicht das gewünschte Ergebnis in der Zelle zu sehen, liegt das mit ziemlich großer Wahrscheinlichkeit daran, dass Sie den Zellbezug in der Formel noch verändern müssen. Da diesmal ja keine leeren Zellen auftreten müssen, muss auch der Zellbezug so angepasst werden, dass keine freien Zellen zu sehen sind. Da bei mir alles von A1 bis A6 ausgefüllt wurde, habe ich als Zellbezug „A1:A6“ angegeben. Sie müssen ihren natürlich dann entsprechend abändern. Die Formel im Matrixstil sieht natürlich wieder so aus:

{=ANZAHL(FINDEN(A1:A6;C1))}

05-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-ohne-leer-470.png?nocache=1309017382845

Wörter werden immer gezählt:

Wie vorhin gibt es auch jetzt wieder eine Formel mit der die Anzahl aller gesuchten Wörter angezeigt wird, die sich auch im Text in der Zelle C1 befinden. Um das zu testen, geben Sie nun wenn möglich in die Zelle C1 wieder einen Text ein, in dem ein Wort aus der Spalte A mehrmals auftritt. In meinem Beispiel bietet sich „Willst du Orange und Paprika? Nur Orange“ also wieder perfekt an. Das einzige, was Sie nun verändern müssen ist wieder die Formel in der Zelle C2. Löschen Sie dazu erst einmal ihren aktuellen Inhalt und geben Sie dann folgendes ein:

=SUMME((LÄNGE(C1)-LÄNGE(WECHSELN(C1;A1:A6;)))/LÄNGE(A1:A6))

Auch diese Formel ist wieder eine Matrixformel. Das bedeutet, wenn Sie sie korrekt ausführen müssen, dass Sie wieder die drei Schritte befolgen müssen, die bei einer Matrixformel von Nöten sind. Klicken Sie dazu zuerst wieder in die Zelle mit der Formel also C2. Danach klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie nun abschließend STRG+SHIFT+Enter. Nun erscheint in der Zelle der richtige Ausgabewert. Bei mir war das aufgrund der Wörter „Paprika“, „Orange“ und nochmals „Orange“ wieder die „3“. Aber auch die Formel hat sich wieder verändert, sie sieht nun so aus:

{=SUMME((LÄNGE(C1)-LÄNGE(WECHSELN(C1;A1:A6;)))/LÄNGE(A1:A6))}

Ist bei ihnen nicht der richtige Ausgabewert, sondern ein Fehler in der Zelle erschienen, so liegt das mit ziemlich hoher Sicherheit daran, dass die Zellbezüge falsch sind. Denken Sie daran, dass bei dieser Formel, wie auch bei der aus dem Beispiel zuvor nur die Zellen eingeschlossen sein dürfen, in denen auch Werte stehen. Ist in diesem Zellbereich eine einzige leere Zelle mit eingetragen, so gibt die Formel einen Fehler aus.

06-excel-formeln-anzahl-bestimmter-woerter-in-einem-satz-zaehlen-ohne-leer-2-470.png?nocache=1309017405221

Nun wissen Sie, wie man in Excel die Anzahl an bestimmten Wörtern ausgeben kann, die in einer Zelle auftreten. Dabei können Sie unterscheiden, ob alle gesuchten Wörter gezählt werden, oder bei mehrmaligem Auftreten nur die ersten. Wissen Sie, dass Sie in der Spalte A alle Zelle ausfüllen, so können Sie auch zwei Formeln benutzen, die deutlich kürzer sind.

Von |2018-07-25T14:21:23+00:00Juli 1st, 2011|Kategorien: Tabellenkalkulation|3 Kommentare

Über den Autor:

3 Comments

  1. Samudra 5. Juli 2011 um 21:53 Uhr

    Nach diesen Formeln habe ich für meine Magisterarbeit lange
    gesucht, sie sind wirklich super!! Vielen Dank für das Tutorial.

    Nur ein Problem ergibt sich bei mir. Ich suche nämlich nach sog.
    Funktionswörtern wie "und", "oder" usw. Wenn ich jetzt einen Satz
    habe wie

    "Ich habe einen Hund und eine Katze"

    und ich suche nach dem (mehrmaligen) Vorkommen von "und",
    dann ergibt das mit den vorliegenden Formeln den Wert 2 (sofern
    Wörter immer gezählt werden und nicht nur einmal), da das "und" in
    "Hund" ebenfalls mitgezählt wird.

    Gibt es einen Trick, um dieses Problem zu lösen?
    (Ich habe keine leere Zellen, somit könnte man die einfachere der
    beiden Formeln verwenden.)

  2. yannick_b 5. Juli 2011 um 23:47 Uhr

    Freut mich, dass die Formeln helfen.

    Wenn Sie nur nach einem Wort suchen sollten, wie zum Beispiel jetzt "und", dann können Sie das mit der Formel in diesem Artikel erledigen.
    http://www.supportnet.de/faqsthread/2375383
    ("Genaue Schreibweise des Wortes in B1:" -> "Vorkommen in anderen Wörtern beheben:")
    Dabei müssen Sie dann in die Zelle A1 den Text eingeben, also zum Beispiel "Ich habe einen Hund und eine Katze" und in die Zelle B1 folgt dann das Wort, nach welchem gesucht werden soll, also "und". In die Zelle C1 schreiben Sie dann die aufgezeigte Formel, wobei Sie den Zellbezug "A1:A10" einfach in "A1:A1" abändern müssen.
    Sollen dann noch nach weiteren Wörtern wie "oder" gesucht werden, müssen Sie den Vorgang einfach wiederholen.
    Das ist zwar nicht die eleganteste Methode, aber sollte – hoffe ich zumindestens – ihren Zweck erfüllen.

    Ich hoffe, ich habe mich verständlich ausgedrückt und konnte helfen.

  3. Samudra 11. Juli 2011 um 18:04 Uhr

    Besten Dank, das war sehr verständlich.

    Das Problem bei mir ist, dass ich eine Liste mit ca. 300 so
    genannten Funktionswörtern habe, nach denen ich suchen möchte,
    somit ist diese Methode zeitaufwändig und wirklich nicht sehr
    elegant.

    Ich habe allerdings nach langem Probieren eine gute Lösung
    gefunden. Die Liste mit den Wörtern, nach denen man sucht, wird
    in einem separaten Tabellenblatt abgespeichert (bei einer kleinen
    Datenmenge geht das natürlich auch im gleichen Blatt). Dabei
    erstellt man am besten drei Spalten, wobei in der ersten Spalte das
    Wort an sich, in der zweiten das Wort mit einem Punkt und in der
    dritten das Wort mit einem Komma steht (mit der "Wechseln"-
    Funktion geht das ja ziemlich schnell). Dann markiert man den
    gesamten Zellbereich, in dem sich die Wörter befinden (Achtung:
    leere Zellen vermeiden) und gibt dem Bereich unter Formeln >
    Namens-Manager einen Namen (in meinem Beispiel
    "Funktionswörter").

    Dann kommt Ihre Formel zum Zug:

    {=SUMME((LÄNGE(" "&A1:A10&" ")-LÄNGE(WECHSELN("
    "&KLEIN(A1:A10)&" ";" "&KLEIN(B1)&" ";"")))/(LÄNGE(B1)+2))}

    "A1:A10" wird in meinem Beispiel einfach durch die jeweilige Zelle
    ersetzt, in der ein Satz steht (also angefangen mit A1), und "B1"
    wird durch den Begriff "Funktionswörter" ersetzt. Sofern sich der
    Bereich mit den gesuchten Wörtern in einem anderen Tabellenblatt
    befindet, muss im Namens-Manager unbedingt darauf geachtet
    werden, dass unter "Bereich" die "Arbeitsmappe" angegeben ist,
    sonst klappt es mit dem Verweis in der Formel nicht.

    Die neue Formel sieht dann so aus:

    {=SUMME((LÄNGE(" "&A1&" ")-LÄNGE(WECHSELN("
    "&KLEIN(A1)&" ";" "&KLEIN(Funktionswörter)&"
    ";"")))/(LÄNGE(Funktionswörter)+2))}

    Ich hoffe, das war auch für andere Nutzer verständlich. Sonst
    können Sie dies ja in einem anderen Blog einmal noch
    verdeutlichen. 🙂

Hinterlassen Sie einen Kommentar