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í.
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.
ResponderBorrarEn tu caso sería mejor usar una tabla dinámica, usando la función MIN en el campo de valores.
ResponderBorrarPara ser sincero, no se me ocurrió usar tablas dinámicas para el modelo.
Jorge, muchas gracias por tu aporte a los usuarios de Excel. Esto realmente me sirvió.
ResponderBorrarExcelente Jorge, gracias por ilustrarnos el uso de estas funciones. No siempre las tablas dinámicas son la respuesta a nuestros problemas.
ResponderBorrarSaludos.
Hola Jorge
ResponderBorrarMuy 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
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.
ResponderBorrarPero 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
ResponderBorrar=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.
¿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.
ResponderBorrarAmadeo Govoni
ResponderBorrarCreo 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
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.
ResponderBorrarAmadeo,
ResponderBorrarestamos 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).
Hola Jorge
ResponderBorrarTengo 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!
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?)
ResponderBorrarHola, 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
ResponderBorrarNecesito 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
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).
ResponderBorrarRESUELTO, en vez de cero pon la rayita "-", y listo ahora sacaras solo el valor superior a cero
ResponderBorrarDaniel, 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.
ResponderBorrarEl ejemplo sigue vigente para Excel 2010 y me fue de mucha utilidad.
ResponderBorrarMuchas Gracias.
Excelente. El vinculo es viejo, pero efectivo.
ResponderBorrarsi 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
ResponderBorrarEstimado, su uno de los precios de los tornillos es 0, ese es el mínimo (tornillos gratis).
ResponderBorrarSi 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.
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
ResponderBorrarNo me queda claro a que te refeires con matrices (datos en tablas o fórmulas matriciales).
ResponderBorrarDe todas maneras, si estás usando Excel 2010 o posterior, te sugiero ver este post sobre el uso de la función AGREGAR.
Hola. Trato de hacer lo mismo pero no me sale, según yo el problema es el mimo, te cuento:
ResponderBorrarTengo 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
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.
ResponderBorrarMuchas gracias, Jorge, me sirvió muy bien hacer la tabla dinámica.
ResponderBorrarHOLA QUISIERA SABER UNA FORMULA DE UN PRECIO MINIMO CON 2 CRITERIOS DE BUSQUEDA, GRACIAS
ResponderBorrarEsta esplicado en este post
ResponderBorrarBuen 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 ?
ResponderBorrarSuponiendo que trabajás con Excel 2010 te sugiero ver este post.
ResponderBorrar