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)

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.

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.

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.

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))}

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.

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.