Excel Formeln – Anzahl eines besitmmten Wortes in einem Bereich ausgeben

In diesem Artikel zu Microsoft Excel werde ich ihnen zeigen, wie man mit wenigen Formeln bestimmte Wörter auf verschiedene Weise zählen kann. Dabei müssen die Wörter nicht einmal in einer Zelle stehen, sondern Sie haben dafür einen beliebig großen Zellbereich.

In diesem Artikel hier habe ich ihnen schon erklärt, wie man es schafft, dass Excel alle Wörter in einem festgelegten Bereich zählt. Die Formel, die dafür benutzt wurde, hat aber alle Wörter gezählt. Möchten Sie nun, dass aber nur gezählt wird, wie oft ein bestimmtes Wort in einem Zellbereich vorkommt, so geht das auf diese Art und Weise nicht. Wenn Sie aber diesen Artikel lesen, werden Sie auch dieses „Problem“ lösen können.

Beginn:

Am Anfang müssen Sie natürlich in ihre Excel Tabelle erst einmal den Text oder die Wörter eingeben, in denen das gewählte Wort gezählt werden soll. Um die Wörter oder den Text einzutragen verwenden Sie am besten einfach die Spalte A. Möchten Sie die Anleitung nur dazu benutzen, die Funktion zu testen und keine eigenen Text zu überprüfen, so empfehle ich ihnen, nur die Zellen A1 bis A10 zu benutzen, da Sie dann die Formeln komplett übernehmen können und nicht noch auf ihre Zellbezüge anpassen müssen. In die Zelle B1 schreiben Sie jetzt das Wort, welches in der Spalte A gesucht und gezählt werden soll. Natürlich können Sie auch hier eine andere Zelle nehmen, doch dann hätten Sie das Problem mit den Bezügen wieder. Haben Sie alles eingetragen, so können Sie nun zum nächsten Schritt gehen und damit anfangen die Anzahl an den gesuchten Wörtern auszugeben.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Genaue Schreibweise des Wortes in B1:

Kein Vorkommen in anderen Wörtern:

Zunächst werde ich ihnen den Standardfall zeigen, dass das Wort in der Zelle einfach ganz normal auftritt ohne in anderen Wörtern enthalten zu sein. In meiner Beispieldatei wird das Wort gesucht, welches in der Zelle B1 steht. In meinem Fall also das Wort „der“. Jetzt möchte ich ihnen zeigen, wie man es schafft, dass genau nach diesem Wort gesucht wird. Neben der Bedingung, dass das Wort irgendwo in Spalte A enthalten sein muss, ist auch die genau Schreibweise des Wortes extrem wichtig. So springt die Formel auch wirklich nur auf das Wort an, wenn es exakt so wie in B1 geschrieben wird. Nun aber zu der Formel. Tragen Sie jetzt in die Zelle C1 folgendes ein:

=SUMME((LÄNGE(A1:A10)-LÄNGE(WECHSELN(A1:A10;B1;"")))/LÄNGE(B1))

Diese Formel ist aber eine Matrixformel. Das heißt für Sie, dass die Formel aktuell noch nicht korrekt ausgeführt wird. Klicken Sie also nun nochmals in die Zelle C1. Anschließend klicken Sie oben in die Bearbeitungszeile der Formel. Nun reicht es wenn Sie gleichzeitig die Tasten STRG+SHIFT+Enter drücken. In der Zelle selbst sehen Sie nun den richtigen Ausgabewert der Formel. In meinem Fall ist das die Zahl „2“. Das kommt daher, da die Formel nur auf das „der der“ in der Zelle A4 anspringt. Die anderen Schreibweisen des Wortes „der“ werden ignoriert. Neben der Tatsache, dass nun der korrekte Wert in der Zelle steht, hat sich auch die Formel verändert. Sie sieht nun so aus:

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

Haben Sie andere Zellbezüge als ich gewählt, so ändern Sie die Formel entsprechend ab, indem Sie „A10“ mit der letzten Zell ersetzen, die noch im Zellbereich sein soll. Die Zelle „B1“ steht hier für den Suchbegriff. Möchten Sie ohne diese „Hilfszelle“ auskommen, so schrieben Sie hier das Wort einfach in Anführungszeichen hinein.

Vorkommen in anderen Wörtern:

In meiner Beispielstabelle hatte ich bisher nur Fälle eingetragen, in denen das gesuchte Wort aus der Zelle B1 nur als eigenständiges Wort auftritt. Im Deutschen gibt es aber häufig auch Fälle, dass ein Wort in einem anderen steckt auch wenn die Bedeutung ganz verschieden ist. Beispiele dafür sind zum Beispiel das Wort „Hosenbund“, indem auch das Wörtchen „und“ steckt und das Wort „oder“, welches auch „der“ im Namen hat. Verwenden Sie die Formel von gerade eben, so wird die Formel auch auf solche Fälle anspringen. In meinem Beispiel ist es jetzt so, dass wenn ich das Wort „oder“ in den Zellbereich der Spalte A einfüge, in der Zelle C1 noch ein Eintrag mehr erscheint. Wie das genau aussieht, sehen Sie auf dem Screenshot. Wollen Sie wissen, wie man das Problem löst, dann lesen sich den nächsten Abschnitt durch.

Vorkommen in anderen Wörtern beheben:

Gerade eben habe ich ihnen ja erzählt, dass die Formel auch auf Wörter reagiert, die in anderen Wörtern stecken. Jetzt möchte ich ihnen aber zeigen, wie man genau diesen Fall ausnehmen kann. Dazu benötigen Sie selbstverständlich wieder eine neue Formel. Löschen Sie also zuerst wieder den Inhalt der Zelle C1. Haben Sie das getan, so geben Sie nun das folgende ein:

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

Wie schon die andere Formel, ist auch diese eine Matrixformel. Das bedeutet für Sie, dass Sie nachdem Sie die Formel eingegeben haben, nochmals auf die Zelle und anschließend in die Bearbeitungszeile der Formel klicken müssen. Haben Sie das gemacht, so drücken Sie nun noch STRG+SHIFT+Enter auf ihrer Tastatur. Nun erscheint in der Zelle der korrekte Ausgabewert. In meinem Beispiel war es wieder die Zahl „2“, da die Zelle A10 mit „oder“ nun korrigiert wurde und nur die beiden Wörter „und“ in Zelle A4 gewertet wurden. Aber nicht nur die Ausgabe hat sich verändert, sonder auch wie bei einer Matrixformel üblich ihre Form. Nun sieht sie so aus:

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

Haben Sie einen anderen Zellbezug als ich in meinem Beispiels gewählt, so ändern Sie die Formel entsprechend um. Steht das zu suchende Wort nicht in „B1“, so ändern Sie auch diesen Eintrag.

Schreibweise des Wortes egal:

Die Anleitung hat sich bis jetzt nur darauf bezogen, dass das Wort in der Spalte A genauso aussehen muss, wie das zu suchende Wort in der Zelle C1. Dabei war selbst die Groß- und Kleinschreibung relevant. Nun möchte ich ihnen aber zeigen, wie man es auch schafft, dass die Anzahl an Wörtern gezählt werden, jedoch stört es die Formel nicht ob ein Wort groß- oder kleingeschrieben ist und sie springt auf alles an.

Kein Vorkommen in anderen Wörtern:

Zuerst möchte ich ihnen wieder den Fall zeigen, dass das gesuchte Wort ein eigenständiges Wort ist und nicht an einem anderen Wort hängt. Da bei den vorherigen Formeln immer noch zwischen Groß- und Kleinschreibung unterschieden wurde und das nun nicht mehr der Fall ist, benötigen Sie natürlich eine neue Formel. Löschen Sie dazu erst einmal der Inhalt der Zelle C1. Hier schreiben Sie nun die folgende Formel hinein:

=SUMME((LÄNGE(A1:A100)-LÄNGE(WECHSELN(KLEIN(A1:A100);KLEIN(B1);"")))/LÄNGE(B1))

Auch diese Formel ist wieder eine Matrixformel. Das bedeutet, dass Sie, nachdem Sie die Formel eingegeben haben, die Zelle wieder anklicken müssen. Anschließend klicken Sie nun noch in die Bearbeitungszeile der Formel. Jetzt genügt noch ein gleichzeitiger Druck der Tasten STRG+SHIFT+Enter. Haben Sie das erledigt, so wird in der Zelle nun umgehend der korrekte Wert angezeigt, da die Formel nun ordnungsgemäß läuft. Neben der Tatsache, dass nun der Wert erschienen ist, hat sich auch die Formel verändert. Nach dem richtigen Ausführen sieht sie so aus:

{=SUMME((LÄNGE(A1:A100)-LÄNGE(WECHSELN(KLEIN(A1:A100);KLEIN(B1);"")))/LÄNGE(B1))}

In meinem Beispiel wird jetzt der Wert „4“ angezeigt. Das kommt daher, da die Formel nun wirklich auf alle vier „der“-s anspringt, egal ob sie „der“, „Der“ oder „DER“ geschrieben sind. Haben Sie andere Zellbezüge, dann denken Sie daran, diese wieder zu ändern.

Vorkommen in anderen Wörtern:

Natürlich haben Sie auch die Möglichkeit, dass das gesuchte Wort verschieden geschrieben ist, aber dennoch mit einem anderen Wort zusammenhängt. So zum Beispiel das Wort „Dermatologe“. Auch hier steckt wieder ein „der“ drin. Lassen Sie die Formel so wie sie aktuell ist, dann würde auch das Wort „Dermatologe“ oder „oder“ wieder mitgezählt werden. Wahrscheinlich würde das aber den Ausgabewert verfälschen, da es nicht das Ziel ist, auch Wörter zu zählen, die nur Bestandteil von anderen Wörtern sind. Möchten Sie, dass diese Wörter nicht auftreten, dann lesen Sie den nächsten Teil der Anleitung.

Vorkommen in anderen Wörtern beheben:

Wie schon gesagt werde ich ihnen jetzt zeigen, wie die Formel lauten muss, wenn wirklich nur Wörter gezählt werden, die auch alleine auftreten. Löschen Sie nun also wieder den Inhalt der Formelzelle C1. Anschließend geben Sie folgendes ein:

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

Wie alle Formeln bisher ist auch diese eine Matrixformel. Das bedeutet für Sie wieder, dass Sie nachdem Sie die Formel eingegeben haben erneut auf die Zelle klicken müssen. Haben Sie das gemacht, so klicken Sie nun auch oben nochmals in die Bearbeitungszeile der Formel. Abschließend drücken Sie noch die Tasten STRG+SHIFT+Enter auf ihrer Tastatur. Nun ist die Formel korrekt ausgeführt und der richtige Wert wird in der Zelle angezeigt. In meinem Fall ist das die Zahl „5“, da das „oder“ aus der Zelle A10 nicht mehr dazu gezählt wird. Neben dem korrekten Wert hat sich aber auch die Formel matrixtypisch verändert, sie sieht nun so aus:

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

Haben Sie einen anderen Zellbereich, denken Sie daran, die Formel entsprechend anzupassen.

Zellen zählen, in denen das Wort vorhanden ist:

Stehen in ihren Zellen jeweils nur ein Wort, so ist dieser Teil der Anleitung für Sie nicht mehr relevant. Haben Sie aber, wie ich in meinem Beispiel in manche Zellen mehrere Wörter geschrieben, so ist auch diese Funktion noch wichtig. Stehen in dieser Zelle auch noch mehrmals das gesuchte Wort der Zelle C1, so werden Sie am Ergebnis auch Änderungen sehen. Bei dieser Formel wird nun nämlich gezählt, wie viele Zellen das gesuchte Wort enthalten. Dabei werde ich ihnen zuerst eine Formel zeigen, die auch den Fall „oder“ mit berechnet und anschließend mit anzeigt. Löschen Sie wieder den Inhalt von C1. Danach schreiben Sie hinein:

=ANZAHL(SUCHEN(B1;A1:A100))

Auch das ist wieder eine Matrixformel. Also müssen Sie die Zelle erneut anklicken. Haben Sie das gemacht, so klicken Sie nun auch nochmals in die Bearbeitungszeile der Formel. Abschließend genügt es die Tasten STRG+SHIFT+Enter zu drücken. Die Zelle zeigt nun den richtigen Wert an. In meinem Fall war es die Zahl „4“, da die Zellen A4, A6, A9 und A10 gezählt wurden. Aber auch die Formel hat sich wieder verändert. Der Matrixtyp sieht so aus:

{=ANZAHL(SUCHEN(B1;A1:A100))}

Haben Sie andere Zellen gewählt, so ändern Sie auch hier wieder die Zellbezüge.

Zellen zählen, in denen das Wort vorhanden ist und alleine steht:

Gerade eben habe ich ihnen gezeigt, wie die Formel aussehen soll, wenn die Zellen gezählt werden, in denen der Suchbegriff enthalten ist und das Wort auch Teil eines anderen sein kann. Soll das aber nicht der Fall sein, so benötigen Sie dafür natürlich auch wieder eine neue Formel. Schreiben Sie nun in die Zelle C1 folgende Formel, nachdem Sie den alten Inhalt gelöscht haben:

=ANZAHL(SUCHEN(" "&B1&" ";" "&A1:A100&" "))

Wie Sie sich wahrscheinlich schon denken konnten, ist auch diese Formel eine Matrixformel. Also müssen Sie nachdem Sie die Formel eingegeben haben, wieder auf die Zelle klicken. Anschließend muss auch noch die Bearbeitungszeile der Formel angeklickt werden. Haben Sie das alles gemacht, so sehen Sie in der Zelle jetzt den korrekten Wert. In meinem Fall war das die Zahl „3“, da jetzt nur die Zellen A4, A6 und A9 aufgeführt wurden. Die neue Formel sieht jetzt so aus:

{=ANZAHL(SUCHEN(" "&B1&" ";" "&A1:A100&" "))}

Wie auch bei den anderen Formeln müssen Sie wieder auf die korrekten Zellbezüge achten.

Nun wissen Sie, wie man es schafft in Excel die Anzahl auszugeben, wie oft ein bestimmtes Wort in einem Zellbereich auftritt. Je nachdem, was für Ansprüche Sie haben, benötigen Sie dafür eine eigene Formel.