El artículo muestra algunas formas diferentes de obtener el valor máximo en Excel en función de una o varias condiciones que especifique.
En nuestro tutorial anterior, analizamos los usos comunes de la función MAX, que está diseñada para devolver el número más grande en un conjunto de datos. Sin embargo, en algunas situaciones, es posible que deba profundizar más en sus datos para encontrar el valor máximo según ciertos criterios. Esto se puede hacer usando algunas fórmulas diferentes, y este artículo explica todas las formas posibles.
Fórmula Excel MAX IF
Hasta hace poco, Microsoft Excel no tenía una función MAX IF incorporada para obtener el valor máximo según las condiciones. Hace un tiempo, introdujeron MAXIFS, y ahora los usuarios de Excel 2019 y Excel 2016 incluidos con las suscripciones de Office 365 pueden hacer el máximo condicional de una manera fácil.
En Excel 2013 y versiones anteriores, aún debe crear su propio fórmula matricial combinando la función MAX con una instrucción IF:
{=MAX(SI(rango_de_criterios=rango_de_criterios))}
Para ver cómo funciona esta fórmula genérica MAX IF en datos reales, considere el siguiente ejemplo. Supongamos que tienes una tabla con los resultados de salto de longitud de varios alumnos. La tabla incluye los datos de tres rondas y está buscando el mejor resultado de un atleta en particular, digamos Jacob. Con los nombres de los estudiantes en A2:A10 y las distancias en C2:C10, la fórmula toma esta forma:
=MAX(IF(A2:A10="Jacob", C2:C10))
Tenga en cuenta que siempre se debe ingresar una fórmula de matriz presionando las teclas Ctrl + Shift + Enter simultáneamente. Como resultado, se rodea automáticamente con corchetes como se muestra en la captura de pantalla a continuación (¡escribir las llaves manualmente no funcionará!).
En las hojas de trabajo de la vida real, es más conveniente ingresar el criterio en alguna celda, para que pueda cambiar fácilmente la condición sin cambiar la fórmula. Entonces, escribimos el nombre deseado en F1 y obtenemos el siguiente resultado:
=MAX(IF(A2:A10=F1, C2:C10))
Cómo funciona esta fórmula
En la prueba lógica de la función IF, comparamos la lista de nombres (A2:A10) con el nombre de destino (F1). El resultado de esta operación es una matriz de VERDADERO y FALSO, donde los valores VERDADEROS representan nombres que coinciden con el nombre de destino (Jacob):
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}
Para el argumento value_if_true, proporcionamos los resultados de salto largo (C2:C10), por lo que si la prueba lógica se evalúa como VERDADERO, se devuelve el número correspondiente de la columna C. Se omite el argumento value_if_false, lo que significa que solo tendrá un valor FALSO donde no se cumpla la condición:
{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}
Esta matriz se alimenta a la función MAX, que devuelve el número máximo ignorando los valores FALSO.
Consejo. Para ver las matrices internas discutidas anteriormente, seleccione la parte correspondiente de la fórmula en su hoja de trabajo y presione la tecla F9. Para salir del modo de evaluación de fórmulas, presione la tecla Esc.
Fórmula MAX IF con múltiples criterios
En una situación en la que necesite encontrar el valor máximo basado en más de una condición, puede:
Use sentencias IF anidadas para incluir criterios adicionales:
{=MAX(SI(criterios_rango1=criterios1, IF(criterios_rango2=criterios2, max_rango)))}
O maneje múltiples criterios usando la operación de multiplicación:
{=MAX(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), max_range))}
Digamos que tiene los resultados de niños y niñas en una sola tabla y desea encontrar el salto más largo entre las niñas en la ronda 3. Para hacerlo, ingresamos el primer criterio (femenino) en G1, el segundo criterio (3) en G2, y use las siguientes fórmulas para calcular el valor máximo:
=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16)))
=MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))
Dado que ambas son fórmulas de matriz, recuerde presionar Ctrl + Shift + Enter para completarlas correctamente.
Como se muestra en la captura de pantalla a continuación, las fórmulas producen el mismo resultado, por lo que cuál usar es una cuestión de preferencia personal. Para mí, la fórmula con la lógica booleana es más fácil de leer y construir: permite agregar tantas condiciones como desee sin anidar funciones IF adicionales.
Cómo funcionan estas fórmulas
La primera fórmula utiliza dos funciones SI anidadas para evaluar dos criterios. En la prueba lógica de la primera instrucción IF, comparamos los valores en la columna Sexo (B2:B16) con el criterio en G1 («Mujer»). El resultado es una matriz de valores VERDADERO y FALSO donde VERDADERO representa datos que coinciden con el criterio:
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE}
De manera similar, la segunda función IF verifica los valores en la columna Round (C2:C16) contra el criterio en G2.
Para el argumento valor_si_verdadero en la segunda declaración SI, proporcionamos los resultados de salto largo (D2:D16), y de esta manera obtenemos los elementos que tienen VERDADERO en las dos primeras matrices en las posiciones correspondientes (es decir, los elementos donde el género es «femenino»). » y la ronda es 3):
{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}
Esta matriz final va a la función MAX y devuelve el número más grande.
La segunda fórmula evalúa las mismas condiciones dentro de una sola prueba lógica y la operación de multiplicación funciona como el operador AND:
Cuando los valores VERDADERO y FALSO se utilizan en cualquier operación aritmética, se convierten en 1 y 0, respectivamente. Y debido a que multiplicar por 0 siempre da cero, la matriz resultante tiene 1 solo cuando todas las condiciones son VERDADERAS. Esta matriz se evalúa en la prueba lógica de la función SI, que devuelve las distancias correspondientes a los elementos 1 (VERDADERO).
MAX IF sin matriz
Muchos usuarios de Excel, incluyéndome a mí, tienen prejuicios contra las fórmulas de matriz y tratan de deshacerse de ellas siempre que sea posible. Afortunadamente, Microsoft Excel tiene algunas funciones que manejan la matriz de forma nativa, y podemos usar una de esas funciones, a saber, SUMPRODUCT, como una especie de «envoltura» alrededor de MAX.
La fórmula genérica MAX IF sin matriz es la siguiente:
=SUMAPRODUCTO(MAX((criterios_rango1=criterios1) * (criterios_rango2=criterios2) * max_rango))
Naturalmente, puede agregar más pares de rango/criterio si es necesario.
Para ver la fórmula en acción, usaremos los datos del ejemplo anterior. El objetivo es conseguir el salto máximo de una atleta femenina en la ronda 3:
=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))
Esta fórmula se compite con una pulsación de tecla Intro normal y devuelve el mismo resultado que la fórmula de matriz MAX IF:
Echando un vistazo más de cerca a la captura de pantalla anterior, puede notar que los saltos no válidos marcados con «x» en los ejemplos anteriores ahora tienen valores 0 en las filas 3, 11 y 15, y la siguiente sección explica por qué.
Cómo funciona esta fórmula
Al igual que con la fórmula MAX IF, evaluamos dos criterios comparando cada valor en las columnas Género (B2:B16) y Ronda (C2:C16) con los criterios en las celdas G1 y G2. El resultado son dos matrices de valores VERDADERO y FALSO. Multiplicar los elementos de las matrices en las mismas posiciones convierte VERDADERO y FALSO en 1 y 0, respectivamente, donde 1 representa los elementos que cumplen ambos criterios. La tercera matriz multiplicada contiene los resultados del salto de longitud (D2:D16). Y como multiplicar por 0 da cero, solo sobreviven los elementos que tienen 1 (VERDADERO) en las posiciones correspondientes:
{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}
En caso de que max_range contenga algún valor de texto, la operación de multiplicación devuelve el error #VALOR por lo que la fórmula completa no funcionará.
La función MAX lo toma desde aquí y devuelve el número más grande que cumple las condiciones especificadas. La matriz resultante que consta de un solo elemento {4.63} va a la función SUMPRODUCT y genera el número máximo en una celda.
Nota. Debido a su lógica específica, la fórmula funciona con las siguientes advertencias:
- El rango donde busca el valor más alto debe contener solo números. Si hay algún valor de texto, un #¡VALOR! se devuelve el error.
- La fórmula no puede evaluar la condición «no igual a cero» en un conjunto de datos negativos. Para encontrar el valor máximo ignorando los ceros, use una fórmula MAX IF o la función MAXIFS.
Fórmula Excel MAX IF con lógica OR
Para encontrar el valor máximo cuando ningún de las condiciones especificadas, use la ya familiar fórmula MAX IF de matriz con la lógica booleana, pero agregue las condiciones en lugar de multiplicarlas.
{=MAX(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), max_range))}
Alternativamente, puede usar la siguiente fórmula no matricial:
=SUMAPRODUCTO(MAX(((criterios_rango1=criterios1) + (criterios_rango2=criterios2)) * max_rango))
Como ejemplo, busquemos el mejor resultado en las rondas 2 y 3. Preste atención a que en el lenguaje de Excel, la tarea se formula de manera diferente: devuelva el valor máximo si la ronda es 2 o 3.
Con las rondas enumeradas en B2:B10, los resultados en C2:C10 y los criterios en F1 y H1, la fórmula es la siguiente:
=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))
Ingrese la fórmula presionando la combinación de teclas Ctrl + Shift + Enter y obtendrá este resultado:
El valor máximo con las mismas condiciones también se puede encontrar utilizando esta fórmula no matricial:
=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))
Sin embargo, debemos reemplazar todos los valores «x» en la columna C con ceros en este caso porque SUMPRODUCT MAX solo funciona con datos numéricos:
Cómo funcionan estas fórmulas
La fórmula matricial funciona exactamente de la misma manera que MAX IF con lógica AND excepto que usted une los criterios usando la operación de suma en lugar de la multiplicación. En las fórmulas de matriz, la suma funciona como el operador OR:
Sumar dos matrices de VERDADERO y FALSO (que resultan de verificar los valores en B2:B10 contra los criterios en F1 y H1) produce una matriz de 1 y 0 donde 1 representa los elementos para los cuales cualquiera de las condiciones es VERDADERA y 0 representa los elementos para lo cual ambas condiciones son FALSAS. Como resultado, la función SI «mantiene» todos los elementos en C2:C10 (valor_si_verdadero) para los cuales alguna condición es VERDADERA (1); los elementos restantes se reemplazan con FALSE porque no se especifica el argumento value_if_false.
La fórmula no matricial funciona de manera similar. La diferencia es que en lugar de la prueba lógica de IF, multiplica los elementos de la matriz de 1 y 0 por los elementos de la matriz de resultados de salto de longitud (C2: C10) en las posiciones correspondientes. Esto anula los elementos que no cumplen ninguna condición (tienen 0 en la primera matriz) y mantiene los elementos que cumplen una de las condiciones (tienen 1 en la primera matriz).
MAXIFS: forma fácil de encontrar el valor más alto con condiciones
Los usuarios de Excel 2019 y Excel para Office 365 están libres de la molestia de domesticar matrices para crear su propia fórmula MAX IF. Estas versiones de Excel proporcionan la tan esperada función MAXIFS que hace que encontrar el valor más grande con condiciones sea un juego de niños.
En el primer argumento de MAXIFS, ingresa el rango en el que se debe encontrar el valor máximo (D2: D16 en nuestro caso), y en los siguientes argumentos puede ingresar hasta 126 pares de rango/criterio. Por ejemplo:
=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)
Como se muestra en la captura de pantalla a continuación, esta fórmula simple no tiene problemas para procesar el rango que contiene valores numéricos y de texto:
Para obtener información detallada sobre esta función, consulte la función MAXIFS de Excel con ejemplos de fórmulas.
Así es como puede encontrar el valor máximo con condiciones en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)