Ronald importiert eine Reihe von Signalpegelmessungen als eine Reihe von Werten in Excel. Er muss zählen, wie viele aufeinanderfolgende Wertegruppen in dieser Reihe existieren, die unter einen bestimmten Schwellenwert fallen. Zum Beispiel kann er die folgenden Maße haben:

27, 22, 22, 30, 32, 18, 22, 23, 28, 39, 24, 27, 35, 25, 21

Wenn er die Anzahl der Gruppierungen wissen möchte, bei denen die Mitglieder dieser Gruppierungen unter 26 waren, wäre die Antwort 4. Beachten Sie, dass dies die Gruppierungen aufeinanderfolgender Werte unter 26 sind, nicht die Anzahl der Einzelwerte unter 26. Daher in diesem Fall In diesem Fall werden die vier Gruppierungen in den Klammern im Folgenden angezeigt:

27, [22, 22], 30, 32, [18, 22, 23], 28, 39, [24], 27, 35, [25, 21]

Ronald fragt sich, mit welcher Formel er die Anzahl der Gruppierungen ermitteln kann, die unter einen von ihm festgelegten willkürlichen Schwellenwert fallen.

Es gibt tatsächlich verschiedene Möglichkeiten, wie Sie dies angehen können. Die erste besteht darin, eine „Ergebnisspalte“ zu verwenden, in der Änderungen der Schwellenwert- und Sequenzgruppierung im Wesentlichen notiert werden. Wenn Sie beispielsweise die obigen Werte in Spalte A eines Arbeitsblatts (beginnend bei Zelle A2) und den Schwellenwert in Zelle E1 hatten, können Sie in jeder Zelle rechts von einem Wert in Spalte A die folgende Formel verwenden:

=IF(A2>=$E$1,B1,IF(A1<$E$1,B1,B1+1))

Die Formel hält eine laufende Summe der Gruppen unter dem Schwellenwert. Das Maximum (oder der letzte Wert) in Spalte B gibt die Gesamtzahl der Gruppen unterhalb des Schwellenwerts an. Die Formel prüft, ob der Wert unmittelbar links in Spalte A über oder unter dem Schwellenwert liegt.

Wenn es über oder nicht liegt und der vorherige Wert in Spalte A ebenfalls unter dem Wert liegt, wird die laufende Summe nicht erhöht. Andernfalls wird es erhöht, da eine neue Gruppierung gestartet wird.

Eine verwandte Methode zum Zählen ist die Verwendung dieser Formel in Spalte B, stattdessen:

=IF(A2>=$E$1,0,IF(A1<$E$1,0,1))

Dies führt zu Spalte B, die eine Reihe von 0 oder 1 Werten enthält. Das einzige Mal, dass ein 1-Wert auftritt, ist der Beginn einer Reihe, die unter dem Schwellenwert liegt. Dies macht es einfach, alle Werte in Spalte B zu summieren, die die Anzahl der Gruppierungen angibt.

Wenn Sie die Ergebnisspalte nicht verwenden möchten, können Sie die Anzahl mithilfe einer Arrayformel ermitteln. Die folgende Formel geht wiederum davon aus, dass sich die zu analysierenden Werte in Spalte A befinden, beginnend bei A2, und dass sich der Schwellenwert in Zelle E1 befindet. Denken Sie auch daran, dass Array-Formeln durch Drücken von Strg + Umschalt + Eingabetaste eingegeben werden.

=SUM(IF((A2:A16<$E$1)((A2:A16((A1:A15<$E$1)ISNUMBER(A1:A15))),1))

Die Formel macht im Wesentlichen das, was die vorherige Ergebnisspaltenformel getan hat (bestimmt eine 0 oder 1 basierend darauf, ob eine Gruppierung unter dem Schwellenwert beginnt) und summiert dann diese Werte.

Wenn Sie diese Art von Vergleichen häufig durchführen, möchten Sie möglicherweise Ihre eigene benutzerdefinierte Funktion (ein Makro) entwickeln, um die Anzahl der Gruppierungen für Sie zu ermitteln. Das Folgende ist ein Beispiel für eine solche Funktion.

Function CountGroups(ByVal MyRange As Range, Threshold As Single)

Dim Cell As Range     Dim bInGroup As Boolean     Dim iCount As Integer

Application.Volatile     iCount = 0     bInGroup = False     For Each Cell In MyRange         If Application.IsNumber(Cell) Then             If Cell < Threshold Then 'Less than the threshold?

If Not bInGroup Then  'Only count if starting new group                     iCount = iCount + 1                     bInGroup = True     'Mark as being in group                 End If             Else                 bInGroup = False        'No longer in a group             End If         End If     Next     CountGroups = iCount End Function

Die Funktion durchsucht jede Zelle in einem Bereich und berechnet, ob es sich um den Beginn einer neuen Gruppe unterhalb des Schwellenwerts handelt oder nicht. Sie verwenden die Funktion, indem Sie eine Formel wie die folgende in Ihrem Arbeitsblatt verwenden:

=CountGroups(A2:A16,E1)

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

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

Dieser Tipp (8888) gilt für Microsoft Excel 2007, 2010 und 2013. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Gruppierungen unterhalb eines Schwellenwerts zählen.