Jeff hat ein Arbeitsblatt mit ziemlich viel Text in Spalte C. Er möchte das Auftreten einer bestimmten Zeichenfolge in Spalte C zählen, jedoch nur für ungerade Zeilen (1, 3, 5 usw.) in den Daten. Die Zeichenfolge, nach der er sucht, ist möglicherweise nicht der gesamte Zelleninhalt und kann innerhalb einer bestimmten Zelle mehrmals vorkommen. (Wenn es zwei- oder dreimal in einer Zelle vorkommt, sollte es als zwei- oder dreimal vorkommen.) Er fragt sich, ob es einen formelhaften Weg gibt, dies zu tun, ohne auf ein Makro zurückzugreifen.

Eine Möglichkeit, dies zu erreichen, ist die Verwendung einer Hilfssäule. Angenommen, Sie können Spalte D als Hilfsspalte verwenden, und die erste Zelle, die Daten enthält, ist Zelle C2. (Möglicherweise enthält Zelle C1 eine Spaltenüberschrift.) In Zelle D2 können Sie die folgende Formel verwenden:

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

Alles was Sie tun müssen, ist die Suchzeichenfolge („mein Text“) durch das zu ersetzen, wonach Sie suchen. Die LEN-Funktion wird zweimal verwendet, um zuerst die Länge von allem in Zelle C2 zu ermitteln und dann die Länge des Textes zu subtrahieren, wobei alle Instanzen von „Mein Text“ entfernt wurden. Dieser Wert wird dann durch die Länge Ihrer Suche geteilt, was dazu führt, wie viele Instanzen des Suchtextes sich in der Zelle befanden. Beachten Sie, dass die IF-Funktion sicherstellt, dass ein numerischer Wert nur zurückgegeben wird, wenn die Zeile eine ungeradzahlige Zeile ist.

Sie können diese Formel so viele Zeilen wie nötig nach unten kopieren und dann die Spalte summieren. Das Ergebnis ist die Häufigkeit, mit der die Zeichenfolge in ungeraden Zeilen angezeigt wird.

Wenn Sie im Layout Ihres Arbeitsblatts keine Hilfsspalte verwenden können, können Sie sich auf eine Formel verlassen, die für Datenfelder funktioniert.

Hier ist einer, der den Trick macht:

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

Diese Formel entspricht im Wesentlichen der vorherigen Formel, außer dass die Funktion SUMPRODUCT die Berechnung intern für jede Zelle in Spalte C durchführt. Sie sollten sich darüber im Klaren sein, dass die Formel die gesamte Spalte C untersucht, dh wenn Ihr Suchtext („mein Text“ „) tritt in allen Nicht-Datenzellen in der Spalte auf (z. B. in einem Spaltenkopf) und wird dann auch in die zurückgegebene Summe einbezogen.

Wenn Sie sich für die Verwendung eines Makros entscheiden, können Sie leicht eine benutzerdefinierte Funktion erstellen, die einen Zellbereich untersucht und die Anzahl bestimmt. Das Folgende ist ein Beispiel für den Makrotyp, den Sie verwenden können:

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

Um dies zu verwenden, müssen Sie lediglich einen Bereich angeben, wonach Sie suchen möchten und ob bei der Übereinstimmung die Groß- und Kleinschreibung nicht berücksichtigt werden soll oder nicht. Wenn Sie beispielsweise nach „Mein Text“ suchen möchten und der Fall keine Rolle spielt, verwenden Sie Folgendes:

=CountInst(C2:C99,"my text",TRUE)

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (1514) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.