Du bist hier::--Excel Formeln – Anzahl an Zeilen mit einem bestimmten String ausgeben

Excel Formeln – Anzahl an Zeilen mit einem bestimmten String ausgeben

In diesem Artikel werde ich ihnen zeigen, wie Sie in einer Excel Tabelle ganz einfach mit einer Formel die Anzahl an Zeilen ausgeben können, in denen ein bestimmter Suchbegriff auftritt.

Mit Sicherheit kennen Sie die Funktion ZÄHLENWENN. Damit kann man angeben, wie viele Zellen in einem bestimmten Datenbereich mit einem Suchbegriff übereinstimmen. Jedoch handelt es sich in unserem Beispiel ja nicht um einzelne Zellen, sondern um Zeilen. Im Zusammenhang mit dem Zählen von Zeilen, habe ich schon diesen Artikel geschrieben, jedoch deckt auch dieser die hier gewünschte Funktion nicht ab, da er eben nur die Anzahl der ausgefüllten Zeilen ausgibt und dabei nicht auf einen bestimmten Suchbegriff achtet. Wie man aber auch dieses „Problem“ lösen kann, sehen Sie, wenn Sie weiterlesen.

Beginn:

Bevor Sie damit beginnen die Anzahl an Zeilen auszulesen, müssen Sie zuerst noch die Strings in die Zeilen eintragen. Am besten ist es, wenn Sie dafür den Zellbereich A1-D6 verwenden, da die unten aufgeführten Formeln, dann nicht angepasst sondern einfach so übernommen werden könnten. Desweiteren ist es angebracht auch mal in einer Zelle das gesuchte Wort nicht vorkommen zu lassen, damit man sieht, was die Funktion genau bewirkt. Haben Sie die Zeilen beschriftet, geht es zum nächsten Schritt.

Anzahl an Zeilen mit dem gewünschten Suchbegriff ausgeben:

Nachdem die Werte in den Zeilen eingetragen worden sind, können Sie nun damit beginnen die Anzahl an Zellen auszugeben, in denen der Suchbegriff auftritt. Dabei haben Sie grundsätzlich zwei verschiedene Möglichkeiten. Beide sehen sich sehr ähnlich und bestehen aus einer Matrixformel, jedoch möchte ich Sie beide aufführen. Jetzt werde ich mich um die erste Möglichkeit kümmern. Haben Sie die gleichen Zellbezüge wie ich gewählt, so ist die Zelle A8 frei. Schreiben Sie hier nun folgende Formel hinein:

=SUMME(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D6;ZEILE(1:6)-1;);"Hallo");1))

Zu dieser Formel gibt es mehrere Dinge anzumerken. Zum einen sein gesagt, dass die Formel eine Matrixformel ist. Das bedeutet, dass sie nicht einfach ausgeführt wird, wenn Sie die Formel in die Zelle eingeben. Möchten Sie den richtigen Wert bekommen, so müssen Sie die Zelle A8 erneut anklicken. Haben Sie das getan, so klicken Sie nun oben in die Bearbeitungszeile der Formel. Abschließend drücken Sie auf ihrer Tastatur noch gleichzeitig die Tasten STRG+SHIFT+Enter, wodurch die Formel jetzt korrekt funktioniert. Die neue Formel sieht jetzt so aus:

{=SUMME(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D6;ZEILE(1:6)-1;);"Hallo");1))}

Die anderen Anmerkungen beziehen sich darauf, wie man die Formel verändert. Haben Sie andere Zellebezüge als ich gewählt, so verändern Sie bitte folgendes: gehen ihre Zellbezüge über die Spalte D und/oder die Zeile 6 hinaus, so ändern Sie einfach „D6“ in ihre letzte noch relevante Formel ab. Desweiteren müssen Sie die Zahl „6“ in der Formel ändern, wenn Sie mehr als sechs Zeilen benutzt haben. Wenn Sie nicht nach dem Wort „Hallo“ suchen, dann müssen Sie dieses Wort in der Formel natürlich auch entsprechend abändern.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-zeilen-nach-einem-string-durchsuchen-470.png?nocache=1308692093849

Anzahl an Zeilen mit dem gewünschten Suchbegriff ausgeben – anderer Weg:

Wie ich vorhin schon angesprochen habe, gibt es eine zweite Möglichkeit die Anzahl der Zeilen auszugeben, in denen der gesuchte Begriff mindestens einmal vorkommt. Auch diese basiert auf einer Matrixformel. Insgesamt sehen sich beide Formeln relativ ähnlich, jedoch wird in dieser Formel statt WENN einmal die Funktion N verwendet, welche in manchen Fällen – so wie in diesem – fast gleichwertig ersetzt werden können. Die neue Funktion sieht dafür so aus:

=SUMME(N(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D6;ZEILE(1:6)-1;);"Hallo")>0))

Auch hier bleibt wieder zu sagen, dass die Formel eine Matrixformel ist. Deshalb klicken Sie die Zelle und danach die Bearbeitungszeile der Formel wieder an. Danach müssen Sie wieder mit STRG+SHIFT+Enter bestätigen. Haben Sie das gemacht, so sieht die neue Formel im Matrixstil so aus:

{=SUMME(N(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D6;ZEILE(1:6)-1;);"Hallo")>0))}

Die Veränderung der Formel sieht der aus dem anderen Beispiel sehr ähnlich. Die Zelle „D6“ in der Formel müssen Sie dann verändern, wenn ihr Zellbereich über diese Zelle hinausgeht. Außerdem müssen Sie die „6“ umändern, wenn Sie mehr als sechs Zeilen benutzt haben. Das Wort „Hallo“ steht in der Formel für den Suchbegriff, auch diesen müssen Sie logischerweise anpassen.

02-excel-formeln-zeilen-nach-einem-string-durchsuchen-anders-470.png?nocache=1308692110857

Nun wissen Sie auch, wie man es schafft nur die Zeilen zu zählen in denen ein bestimmter Suchbegriff vorkommt. Wichtig zu sagen ist, dass nichts nach Zellen gesucht wird und die Formel dadurch etwas komplizierter wird. Mit der Kombination einer bekannten Funktion und einer aus einem anderen Artikel kann man das Problem aber lösen.

Von |2018-07-25T14:23:01+00:00Juni 22nd, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar