El tutorial muestra cómo usar la función MAXIFS en Excel para obtener el valor máximo con condiciones.
Tradicionalmente, cuando necesitaba encontrar el valor más alto con condiciones en Excel, tenía que crear su propia fórmula MAX IF. Si bien no es un gran problema para los usuarios experimentados, puede presentar ciertas dificultades para los novatos porque, en primer lugar, debe recordar la sintaxis de la fórmula y, en segundo lugar, debe saber cómo trabajar con fórmulas de matriz. Afortunadamente, Microsoft ha introducido recientemente una nueva función que nos permite hacer el máximo condicional de una manera fácil.
función MAXIFS de Excel
La función MAXIFS devuelve el valor numérico más grande en el rango especificado según uno o más criterios.
La sintaxis de la función MAXIFS es la siguiente:
MAXIFS(rango_máximo, rango_de_criterios1, criterio1, [criteria_range2, criteria2]…)
Dónde:
- Rango máximo (requerido) – el rango de celdas donde desea encontrar el valor máximo.
- Criterios_rango1 (requerido) – el primer rango para evaluar con criterios1.
- Criterios1 – la condición de uso en la primera gama. Puede ser representado por un número, texto o expresión.
- Criterios_rango2 / criterio2, …(opcional) – rangos adicionales y sus criterios relacionados. Se admiten hasta 126 pares de rango/criterio.
Esta función MAXIFS está disponible en Excel 2019, Excel 2021 y Excel para Microsoft 365 en Windows y Mac.
Como ejemplo, busquemos al jugador de fútbol más alto de nuestra escuela local. Suponiendo que las alturas de los estudiantes están en las celdas D2:D11 (rango_máximo) y los deportes están en B2:B11 (rango_criterios1), use la palabra «fútbol» como criterio1 y obtendrá esta fórmula:
=MAXIFS(D2:D11, B2:B11, "football")
Para que la fórmula sea más versátil, puede ingresar el deporte objetivo en alguna celda (por ejemplo, G1) e incluir la referencia de la celda en el argumento criterio1:
=MAXIFS(D2:D11, B2:B11, G1)
Nota. Los argumentos rango_máximo y rango_de_criterios deben tener el mismo tamaño y forma, es decir, contener el mismo número de filas y columnas, de lo contrario, el valor #¡VALOR! se devuelve el error.
Cómo usar la función MAXIFS en Excel – ejemplos de fórmulas
Como acaba de ver, Excel MAXIFS es bastante sencillo y fácil de usar. Sin embargo, tiene algunos pequeños matices que marcan una gran diferencia. En los siguientes ejemplos, intentaremos aprovechar al máximo el máximo condicional en Excel.
Encuentre el valor máximo basado en múltiples 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 condición. Ahora, vamos a tomar ese ejemplo más allá y evaluar dos criterios diferentes.
Supongamos que quieres encontrar al jugador de baloncesto más alto de la escuela primaria. Para hacerlo, defina los siguientes argumentos:
- Max_range: un rango de celdas que contienen alturas: D2: D11.
- Criteria_range1 – un rango de celdas que contienen deportes – B2:B11.
- Criterio1 – «baloncesto», que se ingresa en la celda G1.
- Criteria_range2 – un rango de celdas que define el tipo de escuela – C2:C11.
- Criterio2 – «junior», que se ingresa en la celda G2.
Poniendo los argumentos juntos, obtenemos estas 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 no distingue entre mayúsculas y minúsculaspor lo que no necesita preocuparse por las mayúsculas y minúsculas en sus criterios.
En caso de que planee usar su 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 asegurará que la fórmula se copie en otras celdas correctamente: las referencias de criterios cambian según la posición relativa de la celda donde se copia la fórmula mientras los rangos permanecen sin cambios:
Como bonificación 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, ese será el nombre de la persona más alta. Para esto, usaremos la fórmula clásica INDEX MATCH y anidaremos MAXIFS en el primer argumento de MATCH como el 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 secundaria es Liam:
Excel MAXIFS con operadores lógicos
En una situación en la que 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 complicada 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, utilice las comillas para comenzar una cadena y un ampersand para concatenar la referencia y terminar la cadena, por ejemplo, «>»&B1 o «<"&TODAY().
Para ver cómo funciona 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 de entre 13 y 14 años. Esto se puede hacer con los siguientes criterios:
Criterio1: «>=13»
Criterio 2: «<=14"
Debido a que comparamos los números en la misma columna, criterio_rango 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:
Aparte de los números, los operadores lógicos también pueden trabajar con criterios de texto. En particular, el operador «distinto de» 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 único.
- Asterisco
para que coincida con cualquier secuencia de caracteres.
=MAXIFS(D2:D11, B2:B11, "*ball")
Para este ejemplo, busquemos al tipo más alto en los deportes de juego. Debido a que los nombres de todos los deportes de juego en nuestro conjunto de datos terminan con la palabra «pelota», incluimos esta palabra en los criterios y usamos un asterisco para que coincida con cualquier carácter anterior:
=MAXIFS(D2:D11, B2:B11, "*"&G1)
También puede escribir «bola» en alguna celda, por ejemplo, G1, y concatenar el carácter comodín con la referencia de celda:
Una fórmula MAXIFS con carácter comodín
Obtenga el valor máximo dentro de un rango de fechas
Debido a que las fechas se almacenan como números de serie en el sistema interno de Excel, trabaja con los criterios de fechas de la misma manera que trabaja con números.
Para ilustrar esto, reemplazaremos la columna Edad con Fecha de nacimiento e intentaremos calcular la altura máxima entre los niños nacidos en un año en particular, digamos en 2004. Para lograr esta tarea, necesitamos «filtrar» las fechas de nacimiento que sean mayores o iguales al 1-ene-2004 y menores o iguales al 31-dic-2004.
=MAXIFS(D2:D11, C2:C11, ">=1-Jan-2004", C2:C11, "<=31-Dec-2004")
Al crear sus criterios, es importante que proporcione las fechas 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 utilizar la función FECHA:
=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "<="&DATE(G1,12,31))
Máximo condicional para fechas Nota.
=MAXIFS(D2:D11, C2:C11, "10/5/2005")
A diferencia de los números, las fechas deben estar entre comillas cuando se usan en los criterios por sí solas. Por ejemplo:
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 las condiciones con la lógica AND, es decir, procesa solo aquellos números en max_range 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 es VERDADERO.
Para hacer las cosas más fáciles de entender, considere el siguiente ejemplo. Supongamos que desea encontrar la altura máxima de los muchachos que juegan baloncesto o fútbol. ¿Cómo lo harías tú? El uso de «baloncesto» como criterio1 y como criterio de «fútbol»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 buena función MAX para obtener un número más alto:
Encuentre el valor máximo basado en múltiples criterios con lógica OR
Otra forma es usar una fórmula MAX IF con lógica OR.
7 cosas para recordar sobre Excel MAXIFS
- A continuación encontrará algunos comentarios que le ayudarán a mejorar sus fórmulas y evitar errores comunes. Algunas de estas observaciones ya se han discutido como consejos y notas en nuestros ejemplos, pero puede ser útil obtener un breve resumen de lo que ya ha aprendido: La función MAXIFS en Excel puede obtener el valor más alto basado en una ovarios criterios
- . De forma predeterminada, Excel MAXIFS funciona con elY lógica
- es decir, devuelve el número máximo que cumple todas las condiciones especificadas. Para que la función funcione, el rango máximo y los rangos de criterios deben tener la mismo tamaño yforma
- . La función SUMAR.SI esno distingue entre mayúsculas y minúsculas
- es decir, no reconoce mayúsculas y minúsculas en los criterios de texto. Al escribir una fórmula MAXIFS para múltiples celdas, recuerde bloquear los rangos
- con referencias de celdas absolutas para que la fórmula se copie correctamente. Cuidado consintaxis de sus criterios
- ! Aquí están las reglas principales:
- Cuando se usan solos, el texto y las fechas deben estar entre comillas, los números y las referencias de celda no.
- Cuando se utiliza un número, fecha o texto con un operador lógico, la expresión completa debe ir entre comillas dobles como «>=10»; las referencias de celda y otras funciones se deben concatenar mediante el uso de un ampersand como «>»&G1.
MAXIFS solo está disponible en Excel 2019 y Excel para Office 365. En versiones anteriores, esta función no está disponible.
Así es como puedes encontrar el valor máximo en Excel con condiciones. ¡Gracias por leer y espero verte pronto en nuestro blog!
Descargar libro de práctica:
(archivo .xlsx)