Du bist hier::--Excel Formeln – Zeichen über mehrere Zellen suchen

Excel Formeln – Zeichen über mehrere Zellen suchen

[imgr=logo-470.png]logo-80.png?nocache=1309248277943[/imgr]In diesem Artikel zu Excel werde ich ihnen heute zeigen, wie man Zeichen in einer Zeile über mehrere Spalten hinweg suchen kann und anschließend die Anzahl der Zeilen ausgeben kann, in denen die Zeichen vorkommen. All das kann man mit einer Formel lösen, wie Sie gleich sehen werden.

In diesem Artikel hier habe ich ihnen schon einmal gezeigt, wie Sie es schaffen, dass in Excel die Anzahl an Zeilen ausgegeben wird, die einen bestimmten String enthalten. Dabei war es aber wichtig, dass die Zeichen des Strings genau nacheinander folgen müssen. So wurde eine Spalte nur gezählt, wenn Sie in einer Zelle genau das zu suchende Wort enthalten hat, dabei durfte kein anderes Zeichen dazwischen folgen. In diesem Artikel werde ich ihnen nun zeigen, wie auch die Zeilen gezählt werden, in denen einfach nur die gewünschten Buchstaben auftreten.

Beginn:

Zuerst müssen Sie in ihrem Datenblatt ein paar Zeilen eintragen. Am besten machen Sie es so, dass ein Datensatz, also eine Zeile, aus drei Werten besteht. Diese Werte werden einzeln in Spalte A, Spalte B und Spalte C eingetragen. So haben Sie also einen Datensatz bestehend aus drei Werten. Später möchten Sie ja, dass die Anzahl an Zeilen ausgegeben wird, die gewisse Zeichen enthalten, dabei ist die Art des Auftretens völlig egal. Achten Sie jetzt also schon darauf, dass in manchen Zellen diese Werte auftreten. Machen Sie das natürlich nur, wenn Sie die Funktion testen möchten. Andernfalls nehmen Sie natürlich ihre richtigen Werte. In meinem Fall werden es die Buchstaben „W“ und „F“ sein. Haben Sie ihre Tabelle erstellt, dann schauen Sie, ob sie ungefähr so aussieht, wie meine und folgen Sie dann der weiteren Anleitung. Ich habe hier jetzt nur Buchstaben oder Zahlen gewählt, natürlich können in ihrer Tabelle aber auch Wörter oder ganze Sätze stehen.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-zeichen-ueber-mehrere-zellen-suchen-beginn-470.png?nocache=1308922794461

Anzahl an Zeilen, die bestimmte Zeichen enthalten anzeigen:

Um nun an die Anzahl an Zellen zu gelangen, die bestimmte Zeichen enthalten, benötigen Sie nun eine Formel. Nehmen Sie dafür irgendeine freie Zelle, aber am besten keine aus den Spalten A, B oder C, da Sie sich sonst die Möglichkeit nehmen noch weitere Datensätze hinzuzufügen. Deshalb bietet sich die Zelle E1 eigentlich ideal an. Tragen Sie hier nun folgende Formel ein:

=SUMMENPRODUKT((ZÄHLENWENN(INDIREKT("A"&ZEILE(1:10)&":C"&ZEILE(1:10));"*W*")>0)*(ZÄHLENWENN(INDIREKT("A"&ZEILE(1:10)&":C"&ZEILE(1:10));"*F*")>0))

Diese Formel ist keine Matrixformel, also wird ihnen das richtige Ergebnis umgehend angezeigt. In meinem Fall war es die Zahl „4“, da in vier Zeilen die Buchstaben „W“ und „F“ auftreten. Gezählt werden also die Zeilen 2, 5, 7 und 10. Wie Sie sehen, ist es egal, ob „W“ und „F“ in einer Zelle auftreten und dabei noch nicht einmal zusammenstehen. Deshalb wird auch „FCKW“ gewertet. Haben Sie andere Zellbezüge als ich gewählt, so denken Sie daran, dass die Formel entsprechend verändert werden muss. Auch wenn Sie nicht nach „W“ und „F“ suchen, können Sie das hier einstellen.

02-excel-formeln-zeichen-ueber-mehrere-zellen-suchen-formel-470.png?nocache=1308922817212

Anzahl an Zeilen, die bestimmte Zeichen enthalten anzeigen (Matrixformel):

Um die Anzahl an Zeilen auszugeben, die bestimmte Zeichen enthalten, haben Sie aber auch noch eine andere Möglichkeit. Dafür benötigen Sie selbstverständlich wieder eine neue Formel. Löschen Sie also nun den aktuellen Inhalt der Zelle E1 und geben Sie anschließend folgendes ein:

=ANZAHL((SUCHEN("W";A1:A10&B1:B10&C1:C10))*(SUCHEN("F";A1:A10&B1:B10&C1:C10)))

Geben Sie diese Formel ein, werden Sie noch keinen richtigen Wert geliefert bekommen. Das kommt daher, dass diese Formel nun eine Matrixformel ist. Haben Sie sich schon etwas mit Excel auseinandergesetzt, dann wissen Sie, was Sie nun machen müssen, um die Formel erfolgreich auszuführen. Ich erkläre es aber nochmal. Klicken Sie zuerst in die Zelle, in der die Formel steht, in meinem Beispiel also E1. Haben Sie das gemacht, so klicken Sie nun in die Bearbeitungszeile der Formel. Anschließend drücken Sie nun STRG+SHIFT+Enter auf ihrer Tastatur. Nun sehen Sie in der Zelle die korrekte Ausgabe. In meinem Fall also immer noch die Zahl „4“. Neben der korrekten Ausgabe hat sich aber noch etwas verändert. Die Formel sieht nun, wie für eine Matrixformel üblich, so aus:

{=ANZAHL((SUCHEN("W";A1:A10&B1:B10&C1:C10))*(SUCHEN("F";A1:A10&B1:B10&C1:C10)))}

Haben Sie zu Beginn andere Zellbereiche als ich gewählt, so müssen Sie die Formel wieder anpassen. Das geht aber ganz einfach. Verändern Sie einfach „A1:A10“, „B1:B10“ und „C1:C10“ so, dass ihre Spalten eingeschlossen sind. Möchten Sie, dass ein Datensatz aus vier Werten besteht, so fügen Sie einfach hinter „C1:C10“ ein kaufmännisches Und Zeichen ein und schreiben Sie dahinter den anderen Zellbezug. Möchten Sie, dass nach etwas anderen außer „W“ und „F gesucht wird, so fügen Sie in der Formel noch einen weiteren Faktor ein, der aufgebaut ist, wie die anderen oben. Möchten Sie nicht nach „W“ und „F“ suchen, so ändern Sie diese beiden Buchstaben in der Formel einfach ab.

03-excel-formeln-zeichen-ueber-mehrere-zellen-suchen-formel-matrix-470.png?nocache=1308922847156

Anzahl an Zeilen, die bestimmte Zeichen enthalten anzeigen:

Neben den oben vorgestellten zwei Formeln gibt es aber auch noch eine dritte Möglichkeit. Diese basiert nicht mehr auf einer Matrixformel, wie die aus dem Beispiel oben, sondern auf einer „normalen“ Formel. Um sie einzugeben, müssen Sie wieder den aktuellen Inhalt der Zelle E1 löschen. Haben Sie das gemacht, so tragen Sie hier nun die folgende Formel ein:

=SUMME(WENN(MMULT((ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:C1;ZEILE(1:10)-1;;);{"*W*"."*F*"})>0)*1;{1;1})=2;1))

Haben Sie die Formel eingegeben, wird ihnen umgehend das richtige Ergebnis präsentiert. Bei mir also immer noch die Zahl „4“. Diese Formel ist womöglich für Sie die einfachste Formel zum Verändern. Beim Zellbereich müssen Sie einfach „A1:C1“ in die Länge der ersten Zeile umändern. Also zum Beispiel in „A1:D1“. Möchten Sie einstellen, dass Sie mehr als 10 Datensätze haben, so müssen Sie „1:10“ in der Formel entsprechend verändern. Vor allem die Suche nach einem weiteren Buchstaben oder Zeichen ist aber deutlich einfacher. Geben Sie statt „{"*W*"."*F*"}“ nun „{"*W*"."*F*"."*neues Zeichen*"}“ ein. Anschließend müssen Sie auch „{1;1}“ in „{1;1;1}“ umändern. Geben Sie vier Suchkriterien ein, so müssen Sie natürlich vier Mal eine „1“ eintragen.

04-excel-formeln-zeichen-ueber-mehrere-zellen-suchen-formel-470.png?nocache=1308922865436

Nun wissen Sie, wie man mit einer Formel die Anzahl an Zeilen ausgeben kann, in denen bestimmte Zeichen auftreten. Welche der hier aufgeführten Formeln Sie verwenden, bleibt ihnen überlassen, wobei ich jedoch glaub, dass die letzte Formel wahrscheinlich die einfachste zu verändern ist.

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

Über den Autor:

Hinterlassen Sie einen Kommentar