Fórmula genérica
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Resumen
Para contar pedidos mensuales consecutivos, puede utilizar una fórmula de matriz basada en la función FRECUENCIA, con la ayuda de COLUMN y MAX.
En el ejemplo que se muestra, la fórmula en I5 es:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Explicación
Esta es una fórmula difícil de entender, ¡así que abróchese el cinturón!
La clave de la fórmula es saber que FREQUENCY reúne números en "contenedores" de una manera específica. Cada contenedor representa un límite superior y genera un recuento de todos los números en el conjunto de datos que son menores o iguales que el límite superior y mayor que el número de contenedor anterior. El truco entonces es crear el data_array usando la condición que desea probar (recuento de pedidos mayor que cero en este caso), y el bins_array usando la condición opuesta.
Para crear el bin data_array usamos lo siguiente:
IF(C5:H5>0,COLUMN(C5:H5))
Probamos el recuento de pedidos en cada mes y, si es positivo, devolvemos el número de columna donde el recuento de pedidos> 0. La matriz resultante se ve así:
(3, FALSO, FALSO, 6,7,8)
Observe que solo las columnas en las que el recuento de pedidos es> 0 entran en esta matriz.
La matriz de bins se genera con esto:
IF(C5:H5=0,COLUMN(C5:H5))
Esto coloca los números de columna para los recuentos de pedidos = 0 en una matriz que termina así:
(FALSO, 4,5, FALSO, FALSO, FALSO)
Solo las columnas donde recuento de pedidos = 0 entran en esta matriz donde, según el comportamiento de FRECUENCIA estándar, se convierten en contenedores funcionales que cuentan los pedidos distintos de cero. Las ganancias se traducen a FALSE y no recopilan ningún número de la matriz de datos, ya que los valores FALSE se ignoran.
Con la matriz de datos y las matrices de contenedores anteriores, la frecuencia devuelve una matriz de recuentos por contenedor en una matriz como esta:
(1; 0; 3)
Ahora simplemente envolvemos la función MAX alrededor de la matriz devuelta por FREQUENCY. MAX luego devuelve el número más alto de la matriz como resultado final.
Otros valores consecutivos
Para contar apariciones consecutivas de otros valores, simplemente ajuste la lógica según sea necesario siguiendo el mismo patrón: la primera condición prueba lo que desea contar, la segunda condición prueba lo contrario.
