Calcular el mínimo con criterios excluyendo ceros

sábado, abril 16, 2011

Un lector me consultaba cómo calcular el precio mínimo de los tornillos en esta lista



Para hacerlo tenemos que crear una matriz (array) de valores de la lista que correspondan a la categoría tornillos. Esta lista la usamos como argumento en la función MIN para obtener el valor buscado. La fórmula matricial sería

=MIN((A2:A13="Tornillos")*B2:B13)

Esta es una fórmula matricial que introducimos apretando simultáneamente Ctrl+Mayúscula+Enter.

El problema con esta fórmula es que cuando un valor en la lista no corresponde a la categoría buscada ("tornillos" en nuestro caso), el valor en la matriz es 0 (cero) y éste pasa a ser el valor mínimo (a excepción de que la lista contenga valores negativos).

La solución es usar la función SI para crear una condición que excluya los ceros. La fórmula matricial que excluye los ceros del cálculo del mínimo es

=MIN(SI((A2:A13="Tornillos"),B2:B13))

Nótese que excluimos el tercer argumento de la función SI, es decir, ponemos solamente el resultado a calcular cuando la condición se cumple.

Los precios máximos se calculan sin mucho trámite con esta función matricial

=MAX((A2:A13="Tornillos")*(B2:B13))

Ahora vamos a darle un toque un poco más profesional a la solución agregándole la posibilidad de elegir el tipo de artículo en forma dinámica. Para esto creamos una lista desplegable con validación de datos en la celda E3



Nuestro modelo se ve ahora así



donde hemos modificado las fórmulas de esta manera

=MAX((A2:A13=E3)*(B2:B13))

=MIN(SI((A2:A13=E3),B2:B13))

Ahora, si queremos realmente impresionar al jefe (o mejor aún, a la secretaria) hacemos que también el tipo de valor buscado se establezca dinámicamente.

Agregamos dos columnas a la izquierda de la columna A, donde haremos cálculos auxiliares



En la celda A7 ponemos esta fórmula

=COINCIDIR(G2,A3:A4,0)

En las celdas A9 y A10 ponemos las fórmulas de máximo y mínimo respectivamente.

En G2 ponemos una lista desplegable con validación de datos con dos posibilidades: Máximo y Mínimo.

Finalmente, en la celda G4 ponemos la fórmula

=INDICE(A9:A10,A7)

Ahora, cuando el usuario elige el máximo, la celda A7 recibe el valor 1; si elige el mínimo la celda muestra el 2. Este valor es usado como argumento en la función INDICE de la celda G4, que extrae el valor de la celda A9 o A10, según el caso.

El último toque es ocultar la columna A.

El archivo con el ejemplo se puede descargar aquí.

30 comments:

Juan Eduardo Gonzales Sandoval 16 abril, 2011 18:02  

Muy bueno, pero tengo una duda Jorge si tengo una tercera columna que indique el proveedor. y deseo que adicionalmente del precio mínimo/máximo identifique el proveedor como hacer la fórmula.

Jorge L. Dunkelman 16 abril, 2011 18:48  

En tu caso sería mejor usar una tabla dinámica, usando la función MIN en el campo de valores.
Para ser sincero, no se me ocurrió usar tablas dinámicas para el modelo.

Flaco 17 abril, 2011 01:21  

Jorge, muchas gracias por tu aporte a los usuarios de Excel. Esto realmente me sirvió.

José de Diego 17 abril, 2011 15:10  

Excelente Jorge, gracias por ilustrarnos el uso de estas funciones. No siempre las tablas dinámicas son la respuesta a nuestros problemas.

Saludos.

Marco Antonio 19 abril, 2011 14:51  

Hola Jorge
Muy bueno el post.
Me puedes explicar de nuevo, por qué usar "Coincidir" e "Indice" antes que "Buscarv" o "Buscarh"
(creo que lo leí en otro post tuyo sobre funciones dinamicas y estáticas)
saludos cordiales

Jorge L. Dunkelman 19 abril, 2011 18:52  

En este caso no podemos usar BUSCARV. De hecho INDICE da como resultado la referencia a una de las celdas A9 o A10, no a un valor en una lista.

Marco Antonio 21 abril, 2011 14:12  

Pero si ordenamos en columna A los valores a buscar "Máximo" y "Mínimo" y en la columna B los valores con las formulas aquí expuestas y en la celda "Precio" ponemos
=Buscarv(G2;A1:B2;2;0) nos ahorramos la vuelta por el índice (celda A7 en el ejemplo).
mi duda era que, si a pesar de que es una referencia y una fórmula menos, ¿es realmente más eficiente?, considerando claro, un cantidad enorme de datos.

Jorge L. Dunkelman 21 abril, 2011 18:09  

¿Qué contiene el rango A1:B2? Suponiendo que A1:A2 contienen las palabras "Máximo" y Mínimo", B1 y B2 deben contener las fórmulas que los calculen. De esta manera el modelo funcionaría tal como el que propongo, sin necesidad de tener que ordenar la lista. No crea que haya diferencias notables en cuanto a eficiencia. Por lo general INDICE con COINCIDIR es más eficiente que BUSCARV.

Amadeo 26 abril, 2011 01:29  

Amadeo Govoni

Creo que no hace falta ordenar nada, ya que, en el BUSCARV en el último parámetro poniendo la palabra FALSO te buscar el valor exacto.

Saludos

Marco Antonio 27 abril, 2011 16:54  

Como la idea es aprender nuevas formas de agrupar y resumir datos, propongo una solución para Juan Eduardo, que espero Jorge pueda calificar. Se puede agregar dos columnas; una con los proveedores por cada articulo y su respectivo precio (columna E), y otra auxiliar en que agrupe en forma de código el articulo con el precio (columna B). En este ejemplo se puede agregar en columna B la siguiente formula =CONCATENAR(C2;"-";D2)y luego agregar en otra celda =BUSCARV(CONCATENAR(H3;"-";H4);B2:E13;4;0) donde H3 y H4 son "articulo" y "precio" buscados segun el criterio de Máximo y Mínimo.

Jorge L. Dunkelman 27 abril, 2011 20:55  

Amadeo,

estamos buscando mínimos y máximos sin ceros de acuerdo a dos condiciones. BUSCARV con FALSO no no soluciona el problema (no busca de acuerdo a criterios y además no abliga a ordenar de mayor a menor o de menor a mayor según lo que estamos buscando).

Patricia 23 abril, 2012 05:39  

Hola Jorge
Tengo una base de datos que se va alimentando en diferentes momentos. Quiero obtener el valor máximo de un rango de columnas (BE:BL), pero excluyendo alguas celdas (BE3, BF3, BG3, BE4, BF4, BE5, BF5). El resto de valores de las columnas especificadas si quiero que entren en el cálcul, sólo excluir ese 7 celdas ¿se puede?. Gracias y saludos!

Jorge L. Dunkelman 24 abril, 2012 06:56  

En principio se puede, como casi todo en Excel. Para dar con la mejor solución tendría que ver como funciona el modelo (por ejemplo: ¿son siempre las mismas celdas que tienen que quedar fuera del cálculo?)

Anónimo,  26 septiembre, 2012 19:55  

Hola, Jorge, tengo una tabla de Excel que tiene 2 columnas, la primera con montos crecientes negativos y positivos, la segunda con valores 0,1,2,3,4 y 5

Necesito asignar al primer valor de la columna 1 mayor que cero el correspondiente valor de la columna 2. ¿Cómo se puede hacer?
Muchas gracias
Saludos,
Matías

Jorge L. Dunkelman 05 octubre, 2012 11:39  

Creo que no termino de entender la consulta (es el tema que trato en la nota). Puedes ponerte en contacto conmigo en forma privada siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).

Danieñ Nuñez 12 julio, 2013 19:08  

RESUELTO, en vez de cero pon la rayita "-", y listo ahora sacaras solo el valor superior a cero

Jorge Dunkelman 12 julio, 2013 19:39  

Daniel, si te refieres a la consulta de Anónimo del 26/9/2012, el guión (rayita) es texto, no cero (número). Si te refieres al formato numérico personalizado que ehibe el cero como guión, sigue siendo cero.

Javier Forero Duque 28 enero, 2014 22:28  

El ejemplo sigue vigente para Excel 2010 y me fue de mucha utilidad.

Muchas Gracias.

Anónimo,  11 febrero, 2015 23:21  

Excelente. El vinculo es viejo, pero efectivo.

THEMRDJPHILIP 27 agosto, 2015 18:40  

si pones "0" a un precio no va funcionar tus formulas ya que esta arrojando el "0" como minimo y se supone que debe de excluirlo

Jorge Dunkelman 27 agosto, 2015 19:49  

Estimado, su uno de los precios de los tornillos es 0, ese es el mínimo (tornillos gratis).
Si quieres excluir todos los ceros, también del artículo buscado, tienes que modificar la fórmula. Fijate en este post donde toco el tema.

Unknown 26 marzo, 2016 01:28  

Buenas tardes Jorge Dunkelman, como comento THEMRDJPHILIP, tambien tengo esa duda, como podría excluir los precios que esten en blanco, o que esten en 0, usando las formulas MIN(SI((A2:A13="Tornillos"),B2:B13)) y MAX((A2:A13="Tornillos")*(B2:B13)) con matrices?, en tu otro post pones ese tema pero no logro adaptarlo cuando se trata de usar matrices, gracias por tu respuesta

Jorge Dunkelman 26 marzo, 2016 10:12  

No me queda claro a que te refeires con matrices (datos en tablas o fórmulas matriciales).
De todas maneras, si estás usando Excel 2010 o posterior, te sugiero ver este post sobre el uso de la función AGREGAR.

Gustavo GM 05 julio, 2016 09:45  

Hola. Trato de hacer lo mismo pero no me sale, según yo el problema es el mimo, te cuento:
Tengo una serie de 10950 datos, que son los días de 30 años, repitiéndose el día 1 del año 30 veces, el día 2 del año 30 veces así para cada día del año (pero ordenado por año). En la columna de en seguida tengo datos de temperatura. Quiero que agarre todos los días 1 y me de el valor mínimo, pongo este código (como el tuyo)
=MIN((A2:A10951=1)*(B2:B10951)) (lo tengo del 1 al 365).
Lo que pasa es que me da el primero valor que encuentra y no compara con los demas datos.

¿Esta bien usar ese código o es necesario agregarle algo más?

De ante mano agradezco tu respuesta

Saludos

Jorge Dunkelman 08 julio, 2016 11:47  

Si quieres ver los mínimos de todos los dias 1 (30 valores) tendrías que usar tablas dinámicas, resumiendo los valores por MIN.

Gustavo GM 08 julio, 2016 22:38  

Muchas gracias, Jorge, me sirvió muy bien hacer la tabla dinámica.

ce.alva 01 octubre, 2016 20:34  

HOLA QUISIERA SABER UNA FORMULA DE UN PRECIO MINIMO CON 2 CRITERIOS DE BUSQUEDA, GRACIAS

Gabriel Garza 18 agosto, 2017 18:00  

Buen dia, busco el valor minio de un rango de columnas para encontrar el puto de equilibrio en un balance general (A1:A60) entonces necesito resaltar el valor minimo, para lo cual establezco una funcion de MIN(A1:A60), luego doy formato condicional a las casillas para que resalte la celda de (A1:A60) que coincida con el valor minimo, PERO en el rango original (A1:A60) se encuentran valores NEGATIVOS y yo solo quiero el valor minimo de los porsitivos, existe una funcion MIN.SI.CONJUNTo en excel 2016, pero el mio no es esa version, alguien tiene una idea de como hacerlo ?

Jorge Dunkelman 18 agosto, 2017 18:17  

Suponiendo que trabajás con Excel 2010 te sugiero ver este post.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP