Ronald importe un certain nombre de mesures de niveau de signal sous forme de série de valeurs dans Excel. Il doit compter combien de groupes consécutifs de valeurs existent dans cette série qui tombent en dessous d’un certain seuil. Par exemple, il peut avoir les mesures suivantes:

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

S’il veut connaître le nombre de groupements où les membres de ces groupements étaient inférieurs à 26, la réponse serait 4. Notez qu’il s’agit des groupements de valeurs consécutives inférieures à 26, et non du nombre de valeurs individuelles inférieures à 26. Ainsi, dans ce cas, les quatre groupements seraient indiqués par les crochets dans le texte suivant:

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

Ronald se demande quel genre de formule il peut utiliser pour déterminer le nombre de groupements qui tombent en dessous d’un certain seuil arbitraire qu’il pourrait spécifier.

Il existe en fait plusieurs façons d’aborder cela. La première consiste à utiliser une «colonne de résultats» qui note essentiellement les changements de seuil et de regroupement de séquences. Par exemple, si vous aviez les valeurs ci-dessus dans la colonne A d’une feuille de calcul (à partir de la cellule A2) et la valeur de seuil dans la cellule E1, vous pouvez utiliser la formule suivante dans chaque cellule à droite d’une valeur de la colonne A:

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

La formule maintient une somme des groupes en dessous du seuil. La valeur max (ou dernière valeur) de la colonne B fournit le nombre total de groupes en dessous du seuil. La formule vérifie si la valeur immédiatement à gauche, dans la colonne A, est supérieure ou inférieure au seuil.

Si c’est au-dessus, ou sinon et que la valeur précédente dans la colonne A était également en dessous, cela n’incrémente pas la somme en cours. Sinon, il s’incrémente car un nouveau regroupement démarre.

Une façon connexe de faire le comptage consiste à utiliser cette formule dans la colonne B, à la place:

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

Il en résulte que la colonne B contient une série de valeurs 0 ou 1. Le seul moment où une valeur 1 se produit est au début d’une série qui est en dessous du seuil. Cela facilite la somme de toutes les valeurs de la colonne B, qui fournit le nombre de regroupements.

Si vous ne souhaitez pas utiliser la colonne de résultats, vous pouvez utiliser une formule matricielle pour déterminer le nombre. La formule suivante suppose, à nouveau, que les valeurs à analyser sont dans la colonne A, commençant à A2, et que la valeur de seuil est dans la cellule E1. Rappelez-vous également que les formules matricielles sont entrées en appuyant sur Ctrl + Maj + Entrée.

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

La formule fait essentiellement ce que la formule précédente de la colonne de résultats a fait (détermine un 0 ou 1 en fonction du début ou non d’un regroupement inférieur au seuil), puis additionne ces valeurs.

Bien sûr, si vous effectuez souvent ces types de comparaisons, vous souhaiterez peut-être développer votre propre fonction définie par l’utilisateur (une macro) pour calculer le nombre de regroupements à votre place. Voici un exemple d’une telle fonction.

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

La fonction examine chaque cellule d’une plage et calcule s’il s’agit du début d’un nouveau groupe en dessous du seuil ou non. Vous utilisez la fonction en utilisant une formule telle que la suivante dans votre feuille de calcul:

=CountGroups(A2:A16,E1)

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (8888) s’applique à Microsoft Excel 2007, 2010 et 2013. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Counting_Groupings_Below_a_Threshold [Compter les groupements sous un seuil].