Clases Excel

Blog


Domingo, 05 Julio 2015 23:28

Cómo hallar en Excel el valor máximo o mínimo con condiciones

Valora este artículo
(14 votos)

¿Qué es esto de máximos y mínimos con condiciones? Imagina que tienes una lista de valores positivos y negativos y necesitas hallar el menor número positivo. O supón que algunos números de la lista se repiten y quieres encontrar el mayor no repetido. ¿Cómo lo resolverías? Siendo más amplios aún: ¿cómo puedes obtener números máximos y mínimos en Excel con cualquier tipo de condicionamiento?

Cómo hallar máximos y mínimos con condiciones 1

 

Las funciones MAX y MIN nos devuelven el valor máximo y mínimo, respectivamente, dentro de un rango de números.

 

Si sintaxis básica es:

=MAX(números)

=MIN(números)

 

Como argumento debemos indicar un rango de celdas desde el que Excel pueda tomar los números a evaluar.

 

Para hallar el mayor número del rango A1:A10, por ejemplo, basta con escribir:

=MAX(A1:A10)

Cómo hallar máximos y mínimos con condiciones 2

 

 

=MIN(A1:A10)

Cómo hallar máximos y mínimos con condiciones 3

 

 

Hasta aquí todo bien. Es muy probable que ya conocieras estas funciones. Pero el problema surge cuando queremos aplicar condiciones a MAX y MIN. Estas funciones no nos permiten establecer directamente ningún tipo de condicionamiento. Por ejemplo, a la función MIN no podemos especificarle que queremos obtener el menor número de un rango sin tomar en cuenta al cero, ni podemos a la función MAX pedirle el mayor número par de un rango.

 

Y si bien existen las funciones SUMAR.SI, CONTAR.SI y PROMEDIO.SI que nos permiten aplicar condiciones a la suma, el recuento y el promedio, Excel carece de las funciones MAX.SI y MIN.SI. De modo que nos toca a nosotros mismos armar una fórmula que las emule. Lo más lógico, parecería ser combinar las funciones MAX y MIN con la función SI, para imponerles una condición. Veamos cómo hacerlo con varios ejemplos prácticos.

 

 

Estructura básica de la función SI

Antes de seguir adelante creo que es conveniente hacer un breve repaso de cómo funciona la función SI, para que nadie se pierda por el camino.

 

En su forma más básica, la función SI avalúa si una condición se cumple y, en base a esa evaluación, devolverá uno de dos resultados previstos; el primer resultado será devuelto si la condición se cumple y, el segundo, si la condición no se cumple. Por ejemplo: podemos establecer que, si en una celda hay un determinado valor, la función SI devuelva un resultado y que, de lo contrario, devuelva otro. De este modo, podemos "enseñar" a Excel a obrar de una manera u otra en respuesta a una situación concreta.

=SI(Condición; Resultado si la condición se cumple; Resultado si no se cumple)

 

Otra forma aún más coloquial de explicar su sintaxis es:

=SI(esta condición se cumple; hacer esto; sino hacer esto otro)

 

En la siguiente fórmula se evalúa si en A1 hay un valor menor que cero (A1<0); si esa premisa es verdadera, la función devolverá como resultado el segundo argumento (“Nro. Negativo”); en caso de que sea falsa, devolverá el tercer argumento (“Nro. Positivo“):

=SI(A1<0;"Nro. Negativo";"Nro. Positivo")

Cómo hallar máximos y mínimos con condiciones 4

 

 

Hallar el menor número superior a cero

Volvamos a las funciones MAX y MIN con condiciones y comencemos por el caso más sencillo. Supongamos, por ejemplo, que en el rango A1:A10 tenemos una lista de números, algunos positivos y otros negativos. Podemos hallar el valor positivo más bajo con la siguiente expresión:

=MIN(SI(A1:A10>0;A1:A10))

Cómo hallar máximos y mínimos con condiciones 5

 

 

La forma más habitual de usar la función SI es para evaluar el contenido de una sola celda, pero como habrás notado, en esta fórmula estamos evaluando un rango (A1:A10). Excel da a este tipo de fórmulas la denominación de matriciales.

 

En este caso, la función SI evaluará cada celda del rango A1:A10, para ver si contiene un número mayor que cero y solo los valores de celda que cumplan con esa condición serán tomados en cuenta por la función MIN.

 

Pero falta un detalle muy importante: debemos indicarle a Excel que estamos ingresando una fórmula matricial, para que la considere como tal; de lo contrario nos devolverá un mensaje de error. La forma de hacerlo es presionar la combinación de teclas Ctrl+Mayús+Enter, en lugar de presionar Enter, una vez que terminemos de escribir la fórmula. Al hacerlo así, veremos que Excel encerrará la fórmula matricial que hemos creado entre dos llaves, del siguiente modo:

{=MIN(SI(A1:A10>0;A1:A10))}

 

No tendrá el mismo efecto si ingresamos las llaves con el teclado. Las llaves las debe colocar Excel como consecuencia de la acción antes indicada y no nosotros manualmente.

 

El proceso puede parecerte un poco complicado si es la primera vez que entras en contacto con las fórmulas matriciales. Pero, para el caso que nos ocupa, los pasos se resumen a dos:

1) Ingresar la fórmula: =MIN(SI(rango>0;rango))
2) Presionar las teclas Ctrl+Mayús+Enter (Los signos de + significan que debes mantener presionada cada tecla de la serie, hasta haberlas presionado todas. La tecla Mayús en algunos teclados se llama Shift.)

 

 

Hallar el mayor (o menor) número estableciendo límites

Es el caso que ilustra la imagen principal de este artículo. Imaginemos que deseamos hallar la edad de la persona más joven con mayoría de edad. No es muy diferente del caso analizado antes. Suponiendo que la mayoría de edad fueran los 18 años y que el rango de datos fuera A1:A10, bastaría con escribir:

=MIN(SI(A1:A10>17;A1:A10))

Cómo hallar máximos y mínimos con condiciones 6

 

 

Si por el contrario, deseáramos saber la mayor edad dentro del grupo de los menores de 18 años, la fórmula sería:

=MAX(SI(A1:A10<18;A1:A10))

 

 

Hallar el mayor (o menor) número par (o impar)

Estos casos se resuelven también de forma muy similar a los anteriores, pero es necesario agregar un ingrediente más: la función RESIDUO (llamada RESTO en Excel 2010).

 

La función RESIDUO retorna el resto de una división. Un número par dividido entre dos tiene resto cero. Por lo tanto, si el resto de una división entre dos es cero, significa que el número que estamos dividiendo (el dividendo) es un número par; de lo contrario es impar.

 

Hay una descripción detallada de la función RESIDUO en el artículo Cómo saber si un número es múltiplo de otro en Excel.

 

Veamos cómo aplicar todo esto para hallar el menor número par del rango A1:A10, que venimos usando como ejemplo:

=MIN(SI(RESIDUO(A1:A10;2)=0;A1:A10))

Cómo hallar máximos y mínimos con condiciones 7

 

 

Para obtener el menor número impar, bastará con cambiar el signo de igual (=) por el de diferente (<>) en la condición del SI:

=MIN(SI(RESIDUO(A1:A10;2)<>0;A1:A10))

 

Para encontrar el máximo número par y el máximo número impar, solo debemos cambiar MIN por MAX en las fórmulas anteriores:

=MAX(SI(RESIDUO(A1:A10;2)=0;A1:A10))

 

=MAX(SI(RESIDUO(A1:A10;2)<>0;A1:A10))

 

 

Hallar el mayor (o menor) número no repetido

Supongamos que en una lista tenemos los números 4, 7, 4, 8 y 9. El menor no repetido es el 7 porque el 4, aunque es menor, está repetido. ¿Cómo lograr una fórmula mediante la cual podamos obtener el mayor (o menor) número no repetido de cualquier rango de datos?

 

Las fórmulas hechas anteriormente nos sirven de base. Solo tenemos que encontrar la forma de expresar la nueva condición.

 

En este caso nos puede auxiliar la función CONTAR.SI, que nos dice cuántas veces se encuentra un valor dentro de un rango. Por ejemplo, la siguiente función devuelve la cantidad de celdas que muestran como resultado un 5, dentro del rango A1:A10:

=CONTAR.SI(A1:A10;5)

 

Y la fórmula que retorna el mayor número no repetido de un rango es:

=MAX(SI(CONTAR.SI(A1:A10;A1:A10)=1;A1:A10))

Cómo hallar máximos y mínimos con condiciones 8

 

 

Esta fórmula localiza el mayor número dentro de A1:A10, tomando en cuenta solo aquellos para los cuáles la función CONTAR.SI da un resultado de 1. Es decir, solo se consideran los números que se encuentran una sola vez dentro del rango de datos.

 

Para hallar el menor número no repetido de un rango, solo basta cambiar MAX por MIN:

=MIN(SI(CONTAR.SI(A1:A10;A1:A10)=1;A1:A10))

 

 

Conclusiones

Resumiendo el artículo en una frase, podemos decir que la forma de aplicar condiciones a las funciones MAX y MIN es por medio de interponer, entre ellas y los datos a evaluar, una función SI, que siempre será matricial. Cambiando apropiadamente la condición que evalúa la función SI podrás establecer cualquier tipo de condicionamiento a las búsquedas de máximos y mínimos.

 

Es posible que no comprendas totalmente la lógica detrás de estas fórmulas, en particular si es la primera vez que te enfrentas al concepto de fórmula matricial. Iremos brindando más información sobre este tema. Sin embargo, lo bueno es que no necesitas entender todo detalle de las fórmulas aquí proporcionadas para sacarles provecho. Funcionarán con solo cambiar el rango de los ejemplos por el que necesites evaluar.

 

También recuerda que para ingresar una fórmula matricial debes presionar Ctrl+Mayús+Enter y no solo Enter.

 

Hay que reconocer que este tema es bastante profundo y requiere para su total comprensión algunos conocimientos previos sobre las funciones básicas de Excel. Pero también creo que es poco probable que a una persona que no conoce nada de Excel comience siquiera a leerlo. Es necesario al menos un conocimiento mínimo de Excel para entender ya el problema que plantea.

 

Un solo artículo no alcanza para entrar en un análisis detallado de todas las funciones utilizadas. Pero confío en que te haya aportado una guía lo suficiente clara como para que puedas sacarle provecho tanto a nivel teórico como práctico.

 

Visto 64105 veces Modificado por última vez en Domingo, 06 Marzo 2016 00:57

Deja un comentario

Los campos imprescindibles están marcados con asterisco (*). Tu dirección de correo nunca será publicada.

57 comentarios

  • Enlace al Comentario Clases Excel Sábado, 18 Marzo 2017 21:46 publicado por Clases Excel

    Hola, Esther.

    Probé ambas fórmulas en mi versión de Excel (2016) y no me devolvió un mensaje de error ni con el punto y coma ni sin él, con ninguna de las funciones.

    Como el tercer argumento de la función SI es el último y es opcional, es admisible tanto que coloquemos el punto y coma como si no.

    Pero puede ser que te dé diferentes resultados porque la sintaxis:
    =SI(prueba lógica; respuesta si es VERDADERO;)

    es diferente de:
    =SI(prueba lógica; respuesta si es VERDADERO)

    En el primer caso, si la prueba lógica da FALSO, el resultado de la función será FALSO.
    En el segundo caso, si la prueba lógica da FALSO, el resultado de la función será 0.

    Eso puede marcar una diferencia en el resultado obtenido. Aunque no debería devolverte un mensaje de error, porque sintácticamente es lícito tanto colocar el punto y coma al final como no ponerlo.

    Saludos

  • Enlace al Comentario Esther Viernes, 17 Marzo 2017 18:59 publicado por Esther

    Hola!
    A ver si pudieras resolverme una duda, dada la matricial
    {=MAX(SI('Ej 14 Empresa A'!E2:E59="M";SI('Ej 14 Empresa A'!H2:H59="Valencia";'Ej 14 Empresa A'!P2:P59;)))}, que funciona, porqué para convertirla a MIN tengo que quitar el último ;
    {=MIN(SI('Ej 14 Empresa A'!E2:E59="M";SI('Ej 14 Empresa A'!H2:H59="Valencia";'Ej 14 Empresa A'!P2:P59)))}
    si quiero que funcione? (sin el ; la MAX también funciona)
    Esta matricial la encontré hace tiempo en MAX y al convertirla a MIN ha sido cuando he dado con tu página, pero no entiendo el propósito de ese ; de más o de menos :'(
    Graacias!

  • Enlace al Comentario Clases Excel Domingo, 12 Marzo 2017 03:59 publicado por Clases Excel

    Hola, Tomas.
    Hay dos problemas que creo detectar de entrada:

    Uno es que el primer argumento del IF debe ser un valor lógico, por lo que me parece que debes haber querido poner:
    IF($W12:$W2999="OPEN",$Y12:$Y2999)
    en lugar de
    IF($W12:$W2999,"OPEN",$Y12:$Y2999)

    En segundo lugar, si lo que deseas buscar con VLOOKUP es un texto, la función MAX no te lo está devolviendo, porque solo devuelve números.

    Algo que me ayuda a ver los errores más rápido en el caso de las funciones anidadas es probarlas todas por separado (en este caso las funciones MAX e IF). De esta manera puedes identificar claramente cuál de las dos funciones está dando problemas (o si ambas tienen errores).

    Saludos

  • Enlace al Comentario Tomas Brunet Viernes, 10 Marzo 2017 19:46 publicado por Tomas Brunet

    Que tal. Oye, tengo un problema que incluye la funcion VLOOKUP; el criterio del valor es un texto (no número) y
    utilizo la siguiente sintaxis:
    =VLOOKUP(MAX(IF($W12:$W2999,"OPEN",$Y12:$Y2999)),$A12:$Y2999,11,FALSE)
    pero me arroja el error #VALUE!
    Espero puedas ayudarme.

  • Enlace al Comentario Clases Excel Miércoles, 01 Marzo 2017 17:03 publicado por Clases Excel

    Hola, Carlos.
    Necesitaría tener más detalles para comprender mejor tu consulta, pero, por lo visto, no sería muy fácil de resolver. Aunque, usando un atajo, para obtener el nombre del día de la semana a partir de una fecha podrías usar la función TEXTO:
    =TEXTO(fecha;"dddd")
    Saludos

  • Enlace al Comentario carlosballin Martes, 28 Febrero 2017 22:29 publicado por carlosballin

    ¿Y si tambien quisiera que me apareciera el nombre de la ciudad sin tener que escribirlo? Es decir, tengo dos columnas A1:A7, días de la semana, y B1:B7, números del 1 al 7. Quiero encontrar el mayor =MAX(B1:B7) pero quiero que también me muestre el dÍa al cual pertenece el numero mayor. No he podido hacer eso.

  • Enlace al Comentario Clases Excel Martes, 07 Febrero 2017 18:07 publicado por Clases Excel

    Hola, Darío.

    Es cierto, con la función Y no se resuelve correctamente el problema. Creo recordar que el día que propuse una solución que implicaba la función Y estaba muy corto de tiempo y el par de pruebas que hice dieron un aparente resultado correcto por pura coincidencia.

    Pensándolo con más detenimiento, me parece que una solución más correcta sería esta:
    =MIN(SI(rango1>0;rango1);SI(rango2>0;rango2))

    Suponiendo que los rangos fueran A1:A10 y B1:B10, quedaría así:
    =MIN(SI(A1:A10>0;A1:A10);SI(B1:B10>0;B1:B10))

    Por supuesto, debe presionarse CTRL+MAYÚS+ENTER para ingresarla, porque es una fórmula matricial.

    Sobre tu solución no puedo opinar porque no sé reproducir el escenario real en el que la probaste.

    Igualmente, muchas gracias por tu aporte, ya que permitió alcarar un poco mejor las cosas.
    Saludos

  • Enlace al Comentario Dario Martes, 07 Febrero 2017 16:37 publicado por Dario

    Hola, ya lo resolví, lo que hice fue en vez de usar la función Y, multipliqué las condiciones en la prueba lógica de la función Si e inesperadamente funcionó. Quedó así:

    {=MIN(SI(('Listado ventas'!$I:$I=ListadoInformeFormulas!E$1)*('Listado ventas'!$A:$A=ListadoInformeFormulas!$A6);'Listado ventas'!$C:$C))}

    (Aclaro que las funciones del comentario anterior tenían las llaves mal puestas, pero eso fue después de copiarlo acá, así que ese no fue el problema)

  • Enlace al Comentario Dario Martes, 07 Febrero 2017 16:25 publicado por Dario

    Hola, necesito tu ayuda por favor.
    Necesito saber el mínimo de un rango con 2 criterios.
    Utilizando la fórmula matricial.

    Tengo esta fórmula:

    ={MIN(SI(Y('Listado ventas'!$I:$I=ListadoInformeFormulas!E$1;'Listado ventas'!$A:$A=ListadoInformeFormulas!A2);'Listado ventas'!$C:$C))}

    pero no me funciona

    El tema es que si uso la fórmula matricial para cada una de las condiciones por separado sí me funciona.

    ={MIN(SI('Listado ventas'!$A:$A=ListadoInformeFormulas!$A$2;'Listado ventas'!C:C))}

    ={MIN(SI('Listado ventas'!$I:$I=ListadoInformeFormulas!E$1;'Listado ventas'!$C:$C))}

    Pareciera como si la formula matricial no permitiera el uso de la función Y

    Vi que diste un ejemplo utilizando la función Y. Estás seguro de que funciona? Lo probaste en la práctica, porque si no, no me explico cuál es el problema.

    Muchas gracias!!!

  • Enlace al Comentario Clases Excel Martes, 20 Diciembre 2016 20:34 publicado por Clases Excel

    Hola, LDR.

    Una solución podría ser la siguiente (suponiendo, por ejemplo, que las secciones están en A1:A10 y los precios en B1:B10):

    =MAX(SI(A1:A10="tienda";DESREF(A1:A10;0;1)))

    Aquí Excel buscará el mayor valor de la Sección "tienda". Cambiando el nombre entre comillas puedes obtener el máximo de las diferentes secciones. (En lugar de escribir directamente el nombre, podrías poner una referencia a una celda donde vayas cambiando las secciones, para no tener que modificar continuamente la fórmula.)

    Por último, recuerda que por ser una fórmula matricial no se ingresa con Enter, sino con la secuencia Ctrl+Mayús+Enter (algunas personas lo conocen como Ctrl+Shift+Enter).

    Saludos

  • Enlace al Comentario Clases Excel Martes, 20 Diciembre 2016 19:39 publicado por Clases Excel

    Hola, Tori. Me alegro realmente de que haya servido la información. Gracias por comentarlo.

  • Enlace al Comentario LDR Sábado, 10 Diciembre 2016 16:37 publicado por LDR

    Como sacar el máximo valor a partir DE UNA SECCIÓN de una serie de producto.
    ejm:
    SECCIÓN PRECIO
    electrónica 85.50
    electrónica 45.30
    electrónica 35.50
    tienda 45.58
    tienda 87.45
    cuarto 25.87
    cuarto 69.30
    gastos 96.85
    gastos 75.85

  • Enlace al Comentario Tori Murasakiro Lunes, 28 Noviembre 2016 06:20 publicado por Tori Murasakiro

    Muchas gracias :) Excelente y me ayudo muchísimo!

  • Enlace al Comentario Clases Excel Domingo, 23 Octubre 2016 02:26 publicado por Clases Excel

    Hola, Dayamer.
    Tu consulta no se relaciona directamente con el tema del artículo.
    De todos modos te digo que no hay una solución sencilla para lo que planteas. Es posible que solo pueda resolverse con macros o puede haber una solución con fórmulas, pero para darte una respuesta más concreta debería ver cómo está dispuesta la información en el libro.
    Saludos

  • Enlace al Comentario Dayamer Mendoza Jueves, 20 Octubre 2016 17:12 publicado por Dayamer Mendoza

    Buenas tardes,

    tengo un documento de excel en el cual incorpore varias hojas con precios de distintas empresas y quiero que al final haya un resumen y la misma me de un rango por producto de precio bajo e indique a que hoja pertenece ese rango , es posible?

  • Enlace al Comentario Clases Excel Martes, 18 Octubre 2016 00:36 publicado por Clases Excel

    Hola, Mary.
    Lamentablemente no entendí tu consulta. Te agradezco si puedes dar más detalles.
    Saludos

  • Enlace al Comentario Mary Lunes, 17 Octubre 2016 21:01 publicado por Mary

    Hola, Necesito obtener el numero maximo dependiendo del tipo documento.
    Ejem:
    B 1
    F 1
    B 2
    B 3

  • Enlace al Comentario Clases Excel Sábado, 24 Septiembre 2016 19:39 publicado por Clases Excel

    Hola, Don Miguelo.
    Es posible pero la solución es demasiado compleja como para explicarla por este medio. Tendrías que usar una combinación de las funciones MIN, INDICE, COINCIDIR e INDIRECTO.
    Saludos

  • Enlace al Comentario DON MIGUELO Viernes, 23 Septiembre 2016 03:24 publicado por DON MIGUELO

    Hola que tal, el caso es el siguiente:
    En la celda de la fila 2 de la columna A, tengo "Producto", en la misma columna A de la fila 3 a la 15 tengo "El nombre del producto", en la fila 2 de las columnas B,C y D, tengo "Proveedor 1", "Proveedor 2", "Proveedor 3", y en estas mismas columnas pero de la fila 3 a la 15, tengo los precios que me ofrece cada proveedor al producto que corresponde, es un comparativo de precios, y lo que quiero, es una formula, que al teclear "El nombre del producto" , me arroje en celdas distintas el precio mas "BAJO" o "MÍNIMO" y también a que proveedor le pertenece ese precio "Proveedor 1" por ejemplo... espero no generarle confusión y me pueda ayudar.. Saludos

  • Enlace al Comentario Clases Excel Martes, 13 Septiembre 2016 16:43 publicado por Clases Excel

    Hola, Agustín.

    La función genérica podría ser:
    =MIN(SI(rango1>0;rango1);SI(rango2>0;rango2))

    Suponiendo que los rangos fueran A1:A10 y B1:B10, quedaría así:
    =MIN(SI(A1:A10>0;A1:A10);SI(B1:B10>0;B1:B10))

    Para ingresarla no olvides presionar CTRL+MAYÚS+ENTER.
    Saludos

Suscríbete a nuestro Boletín gratuito

Recibe periódicamente en tu email los últimos artículos, trucos, tutoriales y otras novedades de Excel.


Tus datos son confidenciales, nunca serán compartidos con nadie.

Blog: Excel básico

Blog: Excel avanzado

Blog: Trucos y productividad

Blog: Funciones

Blog: Macros y VBA

Blog: Artículos generales

Inicio    Contacto    Términos y condiciones    Mapa del sitio

Ir arriba

Desarrollado por www.espectradesign.com