- Descargar libro de trabajo
- Suscribirse al canal de YouTube de ExcelTotal
- Artículo: Calcular el valor mínimo con condiciones
Transcripción del video
Las funciones MAX y MIN no nos permiten aplicar criterios para obtener el valor máximo y mínimo de un rango de celdas.
Por esa razón, en el pasado era necesario usar otras funciones como BDMAX, BDMIN, o crear una fórmula matricial que nos permitiera aplicar una condición.
Sin embargo, a partir de Excel 2019 y Excel 365 tenemos un par de nuevas funciones que nos ayudarán con esta tarea y que son las funciones MAX.SI.CONJUNTO y MIN.SI.CONJUNTO.
Si no tienes ninguna de estas versiones recientes de Excel, dejaré un link en la parte inferior de este video hacia un artículo donde encontrarás los métodos que hemos usado en el pasado para realizar este cálculo.
Por el contrario, si tienes Excel 2019 o Excel 365 podrás seguir los siguientes ejemplos.
En esta hoja tenemos información sobre las ventas de una empresa y vamos a obtener la información relevante de las ventas máximas y mínimas.
En la columna I he colocado la lista de productos únicos y vamos a obtener el precio máximo y mínimo de cada uno de ellos.
En la celda J4 ingresaré el símbolo igual y la función MAX.SI.CONJUNTO y pulsaré el botón Insertar función para explicar de una mejor manera los argumentos de la función.
Como primer argumento tenemos un rango y que son las celdas en las que se va a determinar el valor máximo que en nuestro ejemplo será la columna Total así que haré clic en la celda F2 y en seguida pulsaré el atajo Ctrl + Shift + Flecha abajo para seleccionar todas las celdas de la columna y además pulsaré la tecla F4 para convertir la referencia en absoluta.
Con este argumento, la función ya sabe la columna que contiene los datos de los cuales obtendrá el valor máximo.
El siguiente paso es indicar los criterios que deben cumplir las filas que vamos a incluir en la evaluación.
Estos criterios siempre vendrán en pares: el primero de ellos será el rango que contiene los valores que serán evaluados y el segundo es el criterio que deberán cumplir esos valores para ser incluidos en el cálculo.
En nuestra fórmula queremos incluir las filas del producto Agenda así que vamos a evaluar la columna Producto.
Haré clic en la celda E2 y pulsaré el atajo Ctrl + Shift + Flecha abajo y en seguida la tecla F4 para fijar el rango.
Con esto le decimos a la fórmula que vamos a evaluar los valores de la columna Producto y en el tercer argumento le vamos a decir que incluya solo aquellos productos que tengan el valor Agenda.
Podría ingresar la cadena de texto Agenda en este argumento, pero haré clic en la celda I4 que ya tiene dicha cadena de texto.
En esta fórmula necesitamos un solo criterio, así que terminaré pulsando el botón Aceptar y tendremos el valor máximo para el producto Agenda.
La función MIN.SI.CONJUNTO tiene los mismos argumentos que la fórmula que acabamos de crear, así que voy a copiar el texto de la fórmula directamente de la barra de fórmulas.
Selecciono el texto y pulso Ctrl + C para copiarlo. Pulso la tecla de Escape para salir del modo de edición y en seguida activaré la celda K4 y pegaré el texto en la barra de fórmulas, pero en lugar de dejar la función MAX.SI.CONJUNTO removeré la palabra MAX y la reemplazaré por la palabra MIN.
Aceptaré los cambios y tendremos el valor mínimo para el producto Agenda.
Ya que hemos creado correctamente las referencias de nuestras fórmulas, puedo copiarlas hacia abajo haciendo doble clic en el controlador de relleno de cada una de las celdas y de esta manera obtenemos el valor máximo y mínimo de cada uno de los productos.
En este ejemplo hemos usado solo una columna de criterios, pero ambas funciones nos permiten ingresar hasta 126 criterios.
Hagamos un ejemplo donde agregaremos un segundo criterio a nuestra fórmula.
En la celda J2 voy a ingresar el nombre Norte, porque voy a obtener el valor máximo y mínimo de cada producto, pero de la zona Norte.
En este momento, las fórmulas consideran todas las zonas, y para lograr nuestro objetivo tendremos que agregar un segundo criterio.
Editaré la fórmula de la celda K4, la cual calcula el valor mínimo del producto Agenda y pulsaré el botón Insertar función para ver claramente cada uno de los argumentos.
Ya tenemos un criterio que nos ayuda a obtener solo los valores que tienen el producto de la celda I4 y ahora agregaré un segundo criterio para incluir solo aquellas filas que tengan la zona Norte.
Haré clic en el rango de criterios 2 y en seguida en la celda C2 que es la columna que tiene los nombres de las zonas.
Pulsaré el atajo Ctrl + Shift + Flecha abajo para incluir todas las celdas de la columna y en seguida la tecla F4 para convertir la referencia en absoluta.
Con esto hemos indicado a la función que vamos a evaluar la columna Zona y solo incluiremos en el cálculo aquellas que sean iguales a la zona Norte.
Ese valor ya lo tenemos en la celda J2, así que haré clic en esa celda para incluir la referencia y en seguida pulsaré la tecla F4 para convertirla en absoluta ya que quiero que permanezca fija al momento de copiar la fórmula hacia abajo.
Como puedes observar, nuestra fórmula ya tiene 2 pares de criterios: la columna de Producto, para obtener aquellas filas que tengan el producto Agenda y la columna Zona, para obtener las filas de la zona Norte.
Esto quiere decir que en el cálculo solo serán incluidas las filas que cumplen con ambos criterios.
Pulsaré el botón Aceptar y en seguida haré doble clic en el controlador de relleno para copiar la fórmula hacia abajo y tendremos los valores mínimos de cada producto para la zona Norte.
Como lo mencioné anteriormente, la función MIN.SI.CONJUNTO tiene los mismos argumentos de la función MAX.SI.CONJUNTO así que copiaré el texto de la fórmula de la celda K4 y reemplazaré el texto de la fórmula de la celda J4.
Solo debo cambiar el nombre de la función y reemplazar la palabra MIN por MAX y tendremos el valor máximo para el producto Agenda en la zona Norte.
Haré doble clic en el controlador de relleno de la celda J4 y tendremos el valor máximo para todos los productos.
Para mejorar nuestro reporte, voy a insertar una lista desplegable en la celda J2 de manera que podamos cambiar la zona para la cual se realiza el cálculo.
Activaré la celda J2 e iré a la pestaña Datos, y en el grupo Herramientas de datos, haré clic en el botón Validación de datos y elegiré la opción Lista.
Haré clic en el cuadro de texto Origen e ingresaré los valores: Norte, Sur, Este y Oeste que son los valores que tenemos disponibles en la columna Zona.
Pulsaré el botón Aceptar y tendremos nuestra lista desplegable en la celda J2.
Puedo elegir la zona Sur y las fórmulas obtendrán el valor máximo y mínimo de cada producto para dicha zona.
El último ejemplo que te mostraré será útil para esas ocasiones en las que nuestro criterio es un rango de fechas.
Supongamos que quiero obtener el valor máximo para el mes de agosto del 2019.
Una opción es crear columnas auxiliares donde debemos extraer el mes y año de cada fecha y las usamos como los criterios de las funciones, pero existe otra alternativa y es usar un rango de fechas.
Para esta técnica, debemos establecer la fecha inicial y la fecha final del rango que queremos considerar y para nuestro ejemplo, la fecha inicial será el 01/08/2019 que ingresaré en la celda Q3 y la fecha final que será el 31/08/2019 que colocaré en la celda Q4.
En la celda Q5 ingresaré la función MAX.SI.CONJUNTO y el primer argumento será la columna Total así que haré clic en la celda F2 y en seguida pulsaré el atajo Ctrl + Shift + Flecha abajo y pulsaré la tecla F4 para convertir la referencia en absoluta.
El primer rango de criterios será la columna de fechas y para ingresarla haré clic en la celda A2 y en seguida pulsaré Ctrl + Shift + Flecha abajo y terminaré con la tecla F4.
Ahora observa la construcción del criterio. Voy a ingresar comillas dobles y el operador mayor o igual que y cierro las comillas dobles.
A esta cadena de texto, le vamos a concatenar la fecha inicial del rango a evaluar por lo que ingresaré el ampersand y haré clic en la celda Q3 para insertar la referencia de celda y pulsaré F4 para convertirla en absoluta.
Con este criterio le estamos pidiendo a la función que considere las fechas que son mayores o iguales que el 01/08/2019, pero debemos agregar una segunda condición para que no incluya las fechas posteriores al mes de agosto.
Para el segundo rango de criterio voy a ingresar de nuevo la columna de fechas y por lo tanto puedo copiar la misma referencia del segundo argumento y pegarla en el cuarto argumento de la función.
Ahora crearemos el criterio para que solo se consideren las fechas menores o iguales a la fecha final.
Ingresaré unas comillas dobles y el operador menor o igual que, cierro las comillas dobles e ingreso un ampersand para concatenar el valor de la celda Q4 y pulso la tecla F4 para convertirla en absoluta.
Con esto hemos terminado el segundo criterio de nuestra función y por lo tanto las fechas deberán cumplir con ambas condiciones: por un lado, ser mayores que el 01/8/2019, y por el otro, ser menores que el 31/08/2019.
Cerraré el paréntesis de la función y pulsaré Enter y hemos obtenido el valor máximo de las filas que tienen una fecha del mes de agosto del 2019.
Para obtener el valor mínimo, copiaré la fórmula hacia abajo y cambiaré el nombre de la función MAX por la función MIN y con eso obtendremos el valor mínimo.
Ya que hemos colocado las fechas en celdas diferentes a la fórmula, podemos cambiar sus valores para obtener el resultado de un rango de fechas diferente, por ejemplo, para obtener el valor máximo y mínimo del mes de enero del 2020, cambiaré la fecha inicial a 01/01/2020 y la fecha final al 31/01/2020 y las fórmulas obtendrán los nuevos valores automáticamente.
Las funciones que hemos revisado en esta ocasión serán de mucha utilidad para obtener rápidamente el valor máximo o el valor mínimo de un rango de celdas aplicando múltiples criterios lo cual nos ayudará a realizar un mejor análisis de nuestros datos.