Ronald importa una serie de medidas de nivel de señal como una serie de valores en Excel. Necesita contar cuántos grupos consecutivos de valores existen en esta serie que caen por debajo de cierto umbral. Por ejemplo, puede tener las siguientes medidas:

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

Si quiere saber el número de agrupaciones en las que los miembros de esas agrupaciones tenían menos de 26 años, la respuesta sería 4. Tenga en cuenta que estas son las agrupaciones de valores consecutivos por debajo de 26, no el número de valores individuales por debajo de 26. Por lo tanto, en este En este caso, las cuatro agrupaciones se mostrarían entre corchetes de la siguiente manera:

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

Ronald se pregunta qué tipo de fórmula puede usar para calcular el número de agrupaciones que caen por debajo de algún umbral arbitrario que podría especificar.

En realidad, hay varias formas diferentes de abordar esto. La primera es utilizar una «columna de resultados» que básicamente anota los cambios en el umbral y la agrupación de secuencias. Por ejemplo, si tuviera los valores anteriores en la columna A de una hoja de trabajo (comenzando en la celda A2) y el valor de umbral en la celda E1, entonces podría usar la siguiente fórmula en cada celda a la derecha de un valor en la columna A:

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

La fórmula mantiene una suma acumulada de los grupos por debajo del umbral. El máximo (o último valor) de la columna B proporciona el número total de grupos por debajo del umbral. La fórmula comprueba si el valor inmediatamente a la izquierda, en la columna A, está por encima o por debajo del umbral.

Si está por encima, o si no y el valor anterior en la columna A también estaba por debajo, entonces no incrementa la suma acumulada. De lo contrario, aumenta porque se está iniciando una nueva agrupación.

Una forma relacionada de hacer el recuento es usar esta fórmula en la columna B, en su lugar:

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

Esto da como resultado que la columna B contenga una serie de valores 0 o 1. La única vez que ocurre un valor de 1 es al comienzo de una serie que está por debajo del umbral. Esto facilita la suma de todos los valores en la columna B, que proporciona el recuento de agrupaciones.

Si no desea utilizar la columna de resultados, puede utilizar una fórmula de matriz para calcular el recuento. La siguiente fórmula asume, nuevamente, que los valores a analizar están en la columna A, comenzando en A2, y que el valor de umbral está en la celda E1. Recuerde, también, que las fórmulas de matriz se ingresan presionando Ctrl + Shift + Enter.

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

La fórmula básicamente hace lo que hizo la fórmula de la columna de resultados anterior (determina un 0 o 1 en función de si se está iniciando una agrupación por debajo del umbral) y luego suma esos valores.

Por supuesto, si hace mucho este tipo de comparaciones, es posible que desee desarrollar su propia función definida por el usuario (una macro) para calcular el recuento de agrupaciones por usted. El siguiente es un ejemplo de tal función.

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 función examina cada celda de un rango y calcula si es el inicio de un nuevo grupo por debajo del umbral o no. Usas la función usando una fórmula como la siguiente en tu hoja de trabajo:

=CountGroups(A2:A16,E1)

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (3171) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posteriores) aquí:

link: / excelribbon-Counting_Groupings_Below_a_Threshold [Contando agrupaciones por debajo de un umbral].