EXCEL: Valor máximo en un día laborable dado

Para encontrar el valor máximo en un día de la semana determinado (es decir, lunes, martes, miércoles, etc.) puede usar una fórmula de matriz simple basada en las funciones MAX, SI y TEXTO. En el ejemplo que se muestra, la fórmula en la celda F5 es:

=MAX(SI(TEXTO(dates,"ddd")=F4,values))

Donde las fechas (B5: B15) y los valores (C5: C15) se denominan rangos.

Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.

Cómo funciona esta fórmula

Trabajando de adentro hacia afuera, la función TEXTO se usa para extraer un valor de día de la semana para cada fecha:

=TEXTO(dates,"ddd")

Esto da como resultado una matriz como esta:

{"Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon"}

que luego se compara con el texto en F4, "Mon". El resultado es otra matriz, que contiene solo valores VERDADERO y FALSO:

{VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO}

Tenga en cuenta que cada VERDADERO corresponde a un lunes. Esta matriz se devuelve dentro de la función SI como prueba lógica. Actúa como un filtro para descartar valores en otros días de la semana. El resultado final de SI, que se devuelve directamente a la función MAX, tiene este aspecto:

=MAX({85;FALSO;FALSO;FALSO;FALSO;94;FALSO;FALSO;FALSO;FALSO;52})

MAX ignora automáticamente los valores FALSO y devuelve el valor restante más alto, 94.

Con AGREGAR

Para una fórmula un poco más geek que no requiere control + shift + enter, puede usar la función AGREGAR de esta manera:

=AGREGAR(14,6,values/(TEXTO(dates,"ddd")=F4),1)

Esta es la fórmula utilizada en la celda F6 en el ejemplo que se muestra. Aquí damos AGREGAR 14 para el argumento de la función (K.ESIMO.MAYOR) y 6 para el argumento de la opción (ignorar errores). Luego construimos una expresión lógica usando la función TEXTO para verificar todas las fechas de los lunes. El resultado de esta operación es una matriz de valores VERDADERO / FALSO, que se convierten en el denominador de los valores originales. Cuando se usa en una operación matemática, ¡FALSO evalúa como cero y arroja un # DIV / 0! error. VERDADERO se evalúa como 1 y devuelve el valor original. La matriz final de valores y errores actúa como un filtro. AGREGAR ignora todos los errores y devuelve el mayor (máximo) de los valores supervivientes.

MAXIFS

La función MAXIFS, disponible en Excel Office 365, puede devolver un valor máximo utilizando uno o más criterios sin la necesidad de una fórmula de matriz. Sin embargo, MAXIFS es una función basada en rangos y no permitirá que otras funciones como TEXTOO procesen valores en rangos de criterios. Sin embargo, puede agregar una columna auxiliar a los datos, generar valores de día de la semana con TEXTOO y luego usar MAXIFS con la columna auxiliar como rango de criterios.