Función MAXIFS en Excel: encuentre el valor máximo con múltiples criterios

Encuentre el valor máximo basado en varios criterios

En la primera parte de este tutorial, creamos una fórmula MAXIFS en su forma más simple para obtener el valor máximo basado en una sola condición. Ahora, continuaremos con este ejemplo y evaluaremos dos criterios diferentes.

Digamos que quieres encontrar al jugador de baloncesto más alto de tu escuela. Para hacer esto, defina los siguientes argumentos:

• max_interval – un rango de celdas que contienen alturas – D2:D11.
• criterio_intervalo1 – un rango de celdas que contienen deportes – B2:B11.
• Criterios1 – «baloncesto», que se inserta en la celda G1.
• criterio_intervalo2 – un rango de celdas que define el tipo de escuela – C2:C11.
• Criterio 2 – «junior», que se ingresa en la celda G2.

Poniendo los argumentos juntos, obtenemos las siguientes fórmulas:

Con criterios «codificados»:

=MAXIFS(D2:D11, B2:B11, "basketball", C2:C11, "junior")

Con criterios en celdas predefinidas:

=MAXIFS(D2:D11, B2:B11, G1, C2:C11, G2)

Tenga en cuenta que la función MAXIFS en Excel es mayúsculas y minúsculaspara que no tenga que preocuparse por las mayúsculas en sus criterios.

Si planea usar la fórmula en varias celdas, asegúrese de bloquear todos los rangos con referencias de celda absolutas, como esta:

=MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2)

Esto garantizará que la fórmula se copie en otras celdas correctamente: las referencias de criterios cambian según la posición relativa de la celda en la que se copia la fórmula, mientras que los rangos permanecen sin cambios:

Como beneficio adicional, le mostraré una forma rápida de extraer un valor de otra celda que esté asociada con el valor máximo. En nuestro caso, este será el nombre del hombre más alto. Para ello, utilizaremos la fórmula clásica INDEX MATCH y colocaremos MAXIFS en el primer argumento de MATCH como valor de búsqueda:

=INDEX($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D$11, 0))

La fórmula nos dice que el nombre del jugador de baloncesto más alto de la escuela es Liam:

Excel MAXIFS con operadores lógicos

Cuando necesite evaluar criterios numéricos, utilice operadores lógicos como:

• mayor que (>)
• menos que (<)
• mayor o igual que (>=)
• menor o igual que (<=)
• no es igual a (<>)

El operador «igual a» (=) se puede omitir en la mayoría de los casos.

Por lo general, elegir un operador no es un problema, la parte más difícil es construir criterios con la sintaxis correcta. Así es cómo:

• Un operador lógico seguido de un número o texto debe estar entre comillas dobles, como «>=14» o «<>running».
• En el caso de una referencia de celda u otra función, use comillas para comenzar una cadena y un ampersand para concatenar la referencia y terminar la cadena, por ejemplo «>»&B1 o «<"&HOY().

Para ver cómo funciona esto en la práctica, agreguemos la columna Edad (columna C) a nuestra tabla de muestra y encontremos la altura máxima entre los niños entre las edades de 13 y 14 años. Esto se puede hacer con los siguientes criterios:

Criterio 1: «>=13»

Criterio 2: «<=14"

Dado que estamos comparando números en la misma columna, el rango_de_criterios en ambos casos es el mismo (C2:C11):

=MAXIFS(D2:D11, C2:C11, ">=13", C2:C11, "<=14")

Si no desea codificar los criterios en la fórmula, ingréselos en celdas separadas (por ejemplo, G1 y H1) y use la siguiente sintaxis:

=MAXIFS(D2:D11, C2:C11, ">="&G1, C2:C11, "<="&H1)

La siguiente captura de pantalla muestra el resultado:

Además de números, los operadores lógicos también pueden trabajar con criterios de texto. En particular, el operador «no es igual a» es útil cuando desea excluir algo de sus cálculos. Por ejemplo, para encontrar al estudiante más alto en todos los deportes excepto voleibol, use la siguiente fórmula:

=MAXIFS(D2:D11, B2:B11, "<>volleyball")

O este, donde G1 es el deporte excluido:

=MAXIFS(D2:D11, B2:B11, "<>"&G1)

Fórmulas MAXIFS con caracteres comodín (coincidencia parcial)

Para evaluar una condición que contiene un texto o carácter específico, incluya uno de los siguientes caracteres comodín en sus criterios:

• Signo de interrogación (?) para que coincida con cualquier carácter.
• Asterisco

para que coincida con cualquier cadena de caracteres.

=MAXIFS(D2:D11, B2:B11, "*ball")

Para este ejemplo, averigüemos quién es el tipo más alto en los deportes de juego. Dado que los nombres de todos los deportes en nuestro conjunto de datos terminan con la palabra «pelota», incluimos esta palabra en los criterios y usamos un asterisco para que coincida con los caracteres anteriores:

=MAXIFS(D2:D11, B2:B11, "*"&G1)

También puede escribir «pelota» en una celda, por ejemplo G1, y concatenar el carácter comodín con la referencia de celda:

Una fórmula MAXIFS con caracteres comodín

Obtener el valor máximo en un rango de datos

Debido a que los datos se almacenan como números de serie en el sistema interno de Excel, trabaja con criterios de datos de la misma manera que trabaja con números. Para ilustrar esto, reemplazaremos Edad columna con Fecha de nacimiento

e intente determinar la altura máxima de los niños nacidos en un año determinado, digamos 2004. Para realizar esta tarea, necesitamos «filtrar» las fechas de nacimiento que sean mayores o iguales al 1 de enero de 2004 y menores o iguales a 31 -diciembre-2004.

=MAXIFS(D2:D11, C2:C11, ">=1-Jan-2004", C2:C11, "<=31-Dec-2004")

Al crear los criterios, es importante proporcionar los datos en un formato que Excel pueda entender:

=MAXIFS(D2:D11, C2:C11, ">=1/1/2004", C2:C11, "<=12/31/2004")

O

=MAXIFS(D2:D11, C2:C11, ">="&DATE(2004,1,1), C2:C11, "<="&DATE(2004,12,31))

Para evitar malas interpretaciones, tiene sentido usar la función DATA:

=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "<="&DATE(G1,12,31))

A diferencia de los números, las fechas deben estar entre comillas cuando se usan en criterios por sí solos. P.ej:

Encuentre el valor máximo basado en múltiples criterios con lógica OR La función MAXIFS de Excel está diseñada para probar condiciones con lógica AND, es decir, procesa solo los números de rango máximo

para los cuales todos los criterios son VERDADEROS. En algunas situaciones, sin embargo, es posible que deba evaluar las condiciones con la lógica OR, es decir, procesar todos los números para los que cualquiera de los criterios especificados sea VERDADERO.

Para hacer las cosas más fáciles de entender, considere el siguiente ejemplo. Suponga que desea encontrar la altura máxima de los niños que juegan baloncesto o fútbol. ¿Cómo lo harías tú? El uso de «baloncesto» como criterio 1 y «fútbol» como criterio 2 no funcionará porque Excel asumiría que ambos criterios deberían evaluarse como VERDADERO.

=MAX(MAXIFS(C2:C11, B2:B11, "basketball"), MAXIFS(C2:C11, B2:B11, "football"))

La solución es hacer 2 fórmulas MAXIFS separadas, una para cada deporte, y luego usar la antigua función MAX para obtener un número mayor:

Encuentre el valor máximo basado en múltiples criterios con lógica OR