Clases Excel
Domingo, 05 Julio 2015 23:28

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

Valora este artículo
(13 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 48859 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.

45 comentarios

  • 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(Y(rango1>0;rango2>0);rango1;rango2))

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

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

  • Enlace al Comentario Agustín Lunes, 12 Septiembre 2016 14:22 publicado por Agustín

    El problema que tengo es que el rango que quiero buscar el mínimo distinto de 0 no son celdas contiguas.
    No sé como ponerlo, probé de mil maneras....

  • Enlace al Comentario francisco garcia Lunes, 05 Septiembre 2016 19:25 publicado por francisco garcia

    Muchas Gracias !!!!!!!!!!

  • Enlace al Comentario Clases Excel Lunes, 05 Septiembre 2016 10:16 publicado por Clases Excel

    Hola, Francisco.
    Tendrías que usar la función SI. En el ejemplo las palabras"existencia", "mínimo" y "máximo" representan celdas; de modo que tienes que sustituir esas expresiones por las celdas correspondientes:

    =SI(mínimo>existencia;máximo-existencia;0)

    Lo que hace esta fórmula es verificar si la existencia es menor al mínimo y, si es así, devuelve la cantidad necesaria de reposición para llegar al máximo. De lo contrario devuelve cero.
    Espero te sirva.
    Saludos

  • Enlace al Comentario francisco garcia Domingo, 04 Septiembre 2016 23:00 publicado por francisco garcia

    formula para sacar la cantidad de artículos para compra.

    tengo el minimo y máximo de artículos , tengo la existencia , como saco el numero de artículos para comprar cuando este en el minimo y no comprar mas del máximo
    minimo 5
    máximo 15
    existencia 4
    FORMULA para que me diga que hay que pedir 11 pzas

  • Enlace al Comentario Clases Excel Sábado, 06 Agosto 2016 22:08 publicado por Clases Excel

    Hola, Jessica.
    De los datos que das me surgen varias interrogantes que creo que solo se podrían contestar si pudiera ver la planilla. Hay demasiadas variables en juego y no sé cómo se distribuyen en la hoja. Lo que sí puedo decirte es que la fórmula requerida no es demasiado sencilla. Lamentablemente, no puedo darte una respuesta concreta.
    Saludos

  • Enlace al Comentario Jessica Ponce Sábado, 06 Agosto 2016 18:33 publicado por Jessica Ponce

    Me faltó indicarles que requiero el Kilometraje Inicial y Final mensual, por cada Automóvil. Gracias!!

  • Enlace al Comentario Jessica Ponce Sábado, 06 Agosto 2016 18:30 publicado por Jessica Ponce

    Hola, solicitó su ayuda..... tengo una sábana de datos del kilometraje Inicial y Final por día de una flotilla de 75 automóviles, pero requiero que me dé por mes el kilometraje Inicial y Final, como puedo aplicar la fórmula???

  • Enlace al Comentario Clases Excel Martes, 02 Agosto 2016 11:41 publicado por Clases Excel

    Hola, marylucy84.
    Las celdas en blanco son ignoradas por las funciones MAX, MIN y PROMEDIO. De modo que, si quieres que no sean tomadas en cuenta, no debes hacer nada. Ese es el comportamiento normal de esas funciones.
    Espero haber entendido bien la consulta.
    Saludos

  • Enlace al Comentario marylucy84 Sábado, 30 Julio 2016 17:10 publicado por marylucy84

    buenas tardes. intento hacer estas funciones (min, max y promedio) respecto de una base de datos de valores que me envían con la medida de lunes a viernes una muestra de agua y no resulta un valor, porque el fin de semana no se mide y esa celda esta en blanco. como hago para poder obtener resultado?

  • Enlace al Comentario Clases Excel Sábado, 30 Julio 2016 05:56 publicado por Clases Excel

    Hola, DiegoV.
    Exactamente, tendrías que seguir anidando los SI de la misma manera.
    ¡Saludos!

  • Enlace al Comentario DiegoV Miércoles, 27 Julio 2016 20:09 publicado por DiegoV

    Profe la verdad que un genio! Si necesito un tercer campo lo vuelvo a anidar al "si" ?

  • Enlace al Comentario Clases Excel Miércoles, 27 Julio 2016 08:02 publicado por Clases Excel

    Con el Y parece no funcionar, tendrías que usar un SI anidado:

    En el comentario no se puede usar el signo "menor que" debido a que es parte de la sintaxis del lenguaje HTML.

    Así que lo sustituyo por la expresión "menor que"

    =MAX(SI(VENTAS!D2:D154968>=FECHAINI;SI(VENTAS!D2:D154968"menor que"=FECHAFIN;VENTAS!F2:F154968)))

    El rango del último argumento es posible que tengas que ajustarlo, claro.

    Saludos

Suscríbete a nuestro Boletín gratuito

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


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

Inicio    Contacto    Términos y condiciones    Mapa del sitio

Ir arriba

Desarrollado por www.espectradesign.com