Transcripción del video
Cuando queremos contar o sumar con una o varias condiciones, podemos usar la función CONTAR.SI.CONJUNTO o la función SUMAR.SI.CONJUNTO.
Estas funciones nos proveen de un argumento a través del cual podemos indicar esas condiciones que deberán cumplir los valores para ser considerados dentro del cálculo.
Es importante conocer todas las opciones que tenemos disponibles para armar estos criterios de manera que podamos aprovechar al máximo estas funciones.
Las técnicas que aprenderemos en esta clase también las podrás utilizar en las funciones PROMEDIO.SI.CONJUNTO, SUMAR.SI, CONTAR.SI o PROMEDIO.SI ya que todas ellas tienen argumentos que nos permitirán indicar los criterios de la misma manera.
En esta hoja tengo una lista de las ventas realizadas por una empresa, con su fecha, nombre de vendedor, cliente, zona y monto de la venta.
A continuación, vamos a utilizar las funciones antes mencionadas para descubrir las diferentes maneras en que podemos crear los criterios.
Como primer ejemplo, en la celda H1 vamos a obtener el monto de todas las ventas realizadas en la zona Norte.
Ingresaré el símbolo igual y la función SUMAR.SI.CONJUNTO y el primer argumento es el rango de celdas que será sumado, así que voy a seleccionar las celdas de la columna Monto que tienen las cantidades que me interesa sumar.
El segundo argumento es el rango de celdas que debe cumplir con una condición y ya que estamos interesados en obtener la suma de la zona norte, vamos a ingresar el rango de la columna Zona.
El tercer argumento será el criterio y es aquí donde ingresaré la cadena de texto “Norte” para pedirle a la función que solo incluya las filas cuyo valor de la columna D sea igual a Norte.
Cerraré el paréntesis de la función y pulsaré la tecla Entrar y tendremos la suma de las ventas realizadas en la zona Norte.
En este primer ejemplo, puedes ver que es posible indicar el criterio como una cadena de texto directamente en el argumento de la función.
Sin embargo, si quisiéramos conocer el total de las ventas de la zona Sur, tendríamos que modificar la fórmula para que su tercer argumento fuera la cadena de texto Sur, pero en lugar de hacer eso, podemos colocar la referencia a una celda donde tengamos el valor del criterio y eso es precisamente lo que haremos en el siguiente ejemplo.
En la celda G5 ingresaré la cadena de texto Norte y en seguida copiaré el texto de la fórmula que acabamos de crear en la celda H1 y activaré la celda H5 para pegar la fórmula, pero antes de terminar, eliminaré la cadena de texto que colocamos como criterio y en su lugar ingresaré una referencia a la celda G5.
Al pulsar la tecla Entrar, obtendremos el mismo resultado de la fórmula de arriba, pero en esta ocasión nuestro Criterio ya se encuentra en una celda y podremos cambiarlo por el valor Sur y la fórmula reflejará automáticamente el nuevo cálculo.
Esto quiere decir que, podemos colocar nuestros criterios en alguna celda y posteriormente ingresar esa referencia como el argumento de la función.
Una de las características más útiles de los criterios en las fórmulas, es que podemos usar operadores de comparación, como mayor que, menor que, así como el símbolo igual.
Por ejemplo, en la celda G9 ingresaré la cadena de texto “Este” y en la celda H9 ingresaré el símbolo mayor que y el número 500.
En la celda I9 ingresaré la función CONTAR.SI.CONJUNTO para conocer cuantas ventas cumplen con estas dos condiciones.
Esta función requiere de un número par de argumentos.
El primero de ellos es el rango de celdas que será evaluado y el segundo es el criterio, así que en el primer argumento de nuestra función ingresaré el rango de la columna Zona y el segundo argumento será el criterio que deben cumplir estas celdas por lo que ingresaré la referencia a la celda G9 que tiene la cadena de texto Este.
En seguida, ingresaré la referencia a la columna Monto, y finalmente, el cuarto argumento será la referencia a la celda H9 que le indicará a la función que queremos contar solo los montos mayores que 500.
Cerraré el paréntesis de la función y pulsaré la tecla Entrar y obtendremos el número 137 que es la cantidad de ventas que cumplen con las dos condiciones que indicamos en las celdas de la izquierda.
Puedo cambiar el criterio de la zona para que sea Sur y obtendremos el total de 158.
También puedo cambiar el monto para pedirle a la función que cuente las ventas que son menores a 250 y obtendremos un total de 41 ventas.
Antes de continuar, quiero mostrarte que todos los resultados que estamos obtenido con nuestras fórmulas los puedes comprobar fácilmente a través de los filtros.
Por ejemplo, en el filtro de Zona removeré todas las marcas de selección y elegiré solo la opción Sur y pulsaré el botón Aceptar.
Enseguida en el filtro de la columna Monto, iré a la opción “Filtros de número” y elegiré la opción Menor que y en el cuadro de texto ingresaré el número 250 y pulsaré el botón Aceptar.
Finalmente seleccionaré las celdas de la columna Monto y la barra de estado mostrará un recuento de 41, es decir, tenemos 41 registros que son de la zona Sur y que además tienen un monto menor que 250.
Iré al menú del botón Ordenar y filtrar y elegiré la opción Borrar para eliminar los filtros y puedes ver que la fórmula que creamos previamente ya nos devolvía ese número 41 que son las ventas que cumplen con las dos condiciones.
Para nuestro siguiente ejemplo, quiero que observes los valores de la columna Cliente donde cada uno de ellos está formato por la palabra Cliente, un guion medio y finalmente un número.
Supongamos que me han pedido obtener el monto de las ventas para un cliente, pero nos han dado el número del cliente.
En la celda G13 ingresaré el número 47 porque ese es el cliente del cual queremos conocer la información.
En la celda H13 ingresaré el símbolo igual y la función SUMAR.SI.CONJUNTO y para el primer argumento ingresaré la referencia al rango de celdas de la columna Monto que son los valores que vamos a sumar y en seguida la referencia al rango de celdas de la columna Cliente que son las celdas que vamos a evaluar con nuestro criterio.
Las filas que queremos sumar tienen la cadena de texto Cliente guion medio 47, pero nosotros solo tenemos el número 47 así que tendremos que armar nosotros mismos la cadena de nuestro criterio.
En el tercer argumento de la función, ingresaré la cadena de texto “Cliente” seguida del guion medio y cerramos las comillas dobles y en seguida el operador de concatenación seguido de la referencia a la celda G3 que tiene el número del cliente.
Cerraré el paréntesis de la función y al pulsar la tecla Entrar obtendremos el valor 3975 que es el monto de todas las ventas realizadas al cliente 47.
En este ejemplo, el criterio lo hemos formado concatenando una cadena de texto y el valor de una celda, y de esa manera obtenemos un valor con el cual se comparan las celdas de la columna Cliente para devolvernos el resultado correcto.
La posibilidad de concatenar los valores de una celda para formar nuestros criterios nos dará una gran flexibilidad para crear fórmulas que nos ayudarán a encontrar precisamente los datos que estamos buscando.
Hagamos otro ejemplo.
Supongamos que nos han pedido encontrar el monto total de las ventas del año 2018 así que vamos a usar el primero y el último día del año como nuestros criterios para buscar las ventas dentro de ese rango de fechas.
En la celda G17 ingresaré el 1 de enero del 2018 y en la celda H17 ingresaré la fecha 31 de diciembre del 2018.
En la celda I17 ingresaré el símbolo igual y la función SUMAR.SI.CONJUNTO y el primer argumento será el rango de celdas que vamos a sumar por lo que ingresaré la referencia a las celdas de la columna Monto.
En seguida, vamos a indicar los criterios para que solo se sumen las celdas del año 2018.
Ingresaré el rango de la columna Fecha y vamos a pedirle a la función que considere solamente las fechas que son mayores o iguales al primero de enero del 2018 que es la fecha que se encuentra en la celda G17.
Si solo dejáramos la referencia a la celda G17 la función incluiría en el cálculo solo las fechas iguales al 1 de enero del 2018 así que para este criterio voy a ingresar unas comillas dobles seguidas del operador mayor o igual que y cerraré las comillas y finalmente el operador de concatenación.
De esta manera, sumaremos los montos cuya fecha sea mayor o igual que el 1 de enero del 2018.
Sin embargo, nuestros datos tienen información del año 2019 por lo que tenemos que incluir una segunda condición.
Voy a copiar el segundo y tercer argumento e insertaré una coma y voy a pegarlos dentro de la fórmula, pero vamos a modificar el criterio con el operador menor o igual que y cambiaré la referencia a la celda H17 que tiene la fecha del 31 de diciembre del 2018 y cerraré el paréntesis de la función.
Lo que quiero destacar de la construcción de esta fórmula es que hemos formado los criterios concatenando el operador con el valor de una celda, que en este caso tiene una fecha, y eso nos dará una gran flexibilidad en nuestra fórmula y eso lo comprobaremos en seguida.
Pulsaré la tecla Entrar y obtendremos el monto de ventas del año 2018.
Si ahora queremos conocer las ventas del mes de enero, puedo modificar la fecha final para que sea el 31 de enero del 2018 y la fórmula nos devolverá el resultado esperado.
Con estos ejemplos ya puedes ver el potencial que tenemos al crear nuestros criterios usando operadores de comparación y concatenando valores de celdas.
Pero esto no termina aquí ya que también podemos usar funciones de Excel que nos ayudarán a formar nuestros criterios.
Eso lo veremos en la próxima clase en donde pondremos a prueba este tipo de funciones y la creación de criterios para crear un reporte altamente dinámico.