Transcripción del video
La función que nos ayudará a contar aquellas celdas que cumplan con más de una condición es la función CONTAR.SI.CONJUNTO, pero antes de hacer algunos ejemplos, te mostraré la manera en que se hacía este tipo de cálculo antes de que existiera esta función.
En esta hoja tenemos una lista de alumnos y sus calificaciones y vamos a contar la cantidad de alumnos que han obtenido una calificación entre 85 y 90.
Ya hemos visto en una clase previa, cómo saber si un número está dentro de un rango, y aprendimos que es necesario que dicho número cumpla con dos condiciones.
Antes de que existiera la función CONTAR.SI.CONJUNTO era necesario utilizar la función Y para asegurarnos que se cumplieran todas las condiciones requeridas.
En la celda C2 ingresaré el símbolo igual y la función Y.
El primer argumento será la referencia a la celda B2 que contiene la calificación y en seguida el operador mayor o igual que seguido del número 85.
El segundo argumento de la función Y, será de nuevo la referencia a la celda B2 y el operador menor o igual que y el número 90 que es el límite superior del rango.
De esta manera la función Y nos ayuda a saber si la calificación de cada alumno está dentro del rango 85 y 90.
Pulsaré la tecla Entrar y copiaré la fórmula hacia abajo para obtener el resultado de todas las calificaciones.
Para saber la cantidad de calificaciones que están dentro del rango, en la celda F2 ingresaré el símbolo igual y la función CONTAR.SI y como primer argumento ingresaré el rango de celdas que tiene los resultados de la función Y que es el rango C2:C21.
El segundo argumento de la función será el valor verdadero porque quiero contar aquellas calificaciones que cumplen con las dos condiciones indicadas en la función Y.
Al pulsar Entrar obtenemos el número 9 que es la cantidad de calificaciones en el rango 85-90.
La función Y es la que nos permite evaluar todas las condiciones que necesitamos y posteriormente utilizamos la función CONTAR.SI para contar los valores verdaderos.
En el pasado, esta era la manera en que podíamos contar con múltiples condiciones hasta que en Excel 2007 se introdujo la función CONTAR.SI.CONJUNTO.
Esta función tiene dos argumentos obligatorios: El primer argumento es el rango de celdas que vamos a evaluar y el segundo argumento es el criterio que deben cumplir.
El criterio se forma de la misma manera que en la función CONTAR.SI, es decir, podemos utilizar los operadores de comparación de Excel para crear dicho criterio.
La diferencia, es que esta función nos permitirá indicar rangos de celdas adicionales que vamos a evaluar junto con el criterio que deben cumplir.
La cantidad de argumentos de la función CONTAR.SI.CONJUNTO siempre será un número par ya que un rango de celdas siempre debe ir acompañado del criterio de debe cumplir para ser considerado en la cuenta.
Hagamos el mismo ejemplo anterior, pero utilizando esta nueva función.
En la celda F3 ingresaré el símbolo igual y la función CONTAR.SI.CONJUNTO y el primer argumento será el rango B2:B21 que contiene las calificaciones que vamos a evaluar.
Para el segundo argumento ingresaré unas comillas dobles para indicar el criterio que deben cumplir las celdas para ser incluidas en la cuenta.
Ingresaré el operador mayor o igual que, seguido del número 85 y cierro las comillas dobles.
Con este argumento contaremos las calificaciones mayores o iguales a 85 pero además necesitamos ingresar el límite superior del rango, así que vamos a ingresar un nuevo par de argumentos en la función.
El tercer argumento será de nuevo el rango B2:B21 porque vamos a evaluar los mismos números y para el cuarto argumento ingresaré el operador menor o igual que seguido del número 90.
De esta manera, la función CONTAR.SI.CONJUNTO aplicará dos criterios sobre el mismo rango.
El primero de ellos será para asegurarnos que contaremos las calificaciones mayores o iguales a 85 y el segundo criterio para garantizar que dichas calificaciones serán menores o iguales a 90.
Al pulsar Entrar obtenemos el mismo resultado de la fórmula anterior, comprobando que la función CONTAR.SI.CONJUNTO ha hecho la cuenta de las calificaciones que cumplen con ambas condiciones.
La ventaja de la segunda fórmula es que no necesitamos de columnas auxiliares, ni de utilizar la función Y como un paso intermedio.
Lo importante a recordar de la función CONTAR.SI.CONJUNTO es que sus argumentos siempre deben ir en números pares, donde el primer argumento será siempre el rango de celdas a evaluar, y el segundo argumento será el criterio que determinará si las celdas serán incluidas en la cuenta.
Hagamos un segundo ejemplo con esta función.
Activaré la Hoja2 y aquí tenemos algunos datos diferentes donde se muestra una columna que indica el nivel del curso al cual pertenece la calificación y nos han pedido contar las calificaciones del nivel Intermedio que se encuentran en el rango 85 y 90.
Esto quiere decir que las calificaciones deberán cumplir con tres criterios: Deben ser mayores o iguales a 85, menores o iguales a 90 y además pertenecer al nivel intermedio.
Comencemos con la construcción de esta fórmula.
En la celda F2 ingresaré el símbolo igual y la función CONTAR.SI.CONJUNTO y el primer argumento será el rango C2:C16 que tiene las calificaciones a evaluar y el primer criterio será el operador mayor o igual que seguido del número 85.
Continuamos con el siguiente par de argumentos.
El tercer argumento será de nuevo el rango C2:C16 y el criterio será el operador menor o igual que seguido del número 90.
Con esto hemos asegurado que las calificaciones que serán contadas estarán en el rango 85-90.
Para pedirle a la función que incluya aquellas categorías del nivel intermedio solamente, el quinto argumento de nuestra función será el rango B2:B16 que tiene los niveles a los cuales pertenece cada una de las calificaciones y el criterio que deberán cumplir esos valores será el de ser igual a la cadena de texto Intermedio.
Al pulsar Entrar obtendremos el número 3 que se refiere a las calificaciones de Angélica, de Daniel y de Enrique que pertenecen al nivel intermedio y que además están en el rango 85-90.
De esta manera hemos contado los valores que cumplen con tres condiciones y además has aprendido que la función CONTAR.SI.CONJUNTO nos permite evaluar varias columnas de los datos para aplicar algún criterio diferente a cada una de ellas si así lo necesitamos.
Como lo dije anteriormente, los argumentos de la función CONTAR.SI.CONJUNTO siempre irán en números pares, indicando en primer lugar el rango que será evaluado y en seguida el criterio que deberán cumplir esos valores.
De esta manera, la función nos permite ingresar hasta un máximo de 127 pares de argumentos.
Para nuestro último ejemplo activaré la Hoja3 y aquí tenemos una lista de facturas y sus fechas, así como la región en donde se hizo la venta ya sea Norte, Sur, Este u Oeste.
Y finalmente tenemos el monto de cada factura.
Nos han pedido contar las facturas para las zonas Norte y Sur en los años 2016, 2017 y 2018, así que en la columna Facturas haremos la cuenta con la función CONTAR.SI.CONJUNTO.
En la celda H2 ingresaré el símbolo igual y la función CONTAR.SI.CONJUNTO y el primer argumento será el rango B2:B160 que es el rango de fechas.
Quiero contar aquellas fechas que pertenecen al año 2016, así que el primer criterio será pedir que las fechas sean mayores o iguales que el 01 de enero del 2016.
Ahora vamos a indicar el segundo par de argumentos y vuelvo a ingresar el rango B2:B160 porque vamos a comparar de nuevo las fechas para asegurarnos que sean menores o iguales al 31 de diciembre del 2016.
De esta manera podemos contar las fechas que pertenecen a dicho año y solo para probar que nuestros criterios funcionan correctamente, voy a cerrar el paréntesis de la función y pulsaré la tecla Entrar y obtendremos el número 49.
Sin embargo, tenemos una desventaja al haber colocado las fechas como una cadena de texto dentro de los criterios y es que no podremos copiar la fórmula hacia abajo, ya que estaríamos comparando siempre con las mismas fechas, y por lo tanto tendríamos que editar cada una de las fórmulas para ingresar un año diferente.
Podemos hacer más eficiente nuestra fórmula si tomamos el año de la columna F, y para eso haré el siguiente cambio.
Voy a remover la fecha que colocamos dentro del primer criterio y solo dejaré el operador mayor o igual que.
En seguida ingresaré el operador de concatenación y utilizaré la función FECHA que nos permite crear una fecha válida en Excel con solo ingresar el año, el mes y el día.
Para este criterio necesitamos la fecha del primero de enero del año indicado en la columna F, así que comenzaré con la referencia a la celda F2 que contiene el año y para el mes ingresaré el número 1 que equivale a enero y para su tercer argumento también ingresaré el número uno para tener la fecha primero de enero del 2016.
Para nuestro segundo criterio haremos un cambio similar.
Removeré la fecha que se encuentra dentro de la cadena de texto e ingresaré el operador de concatenación seguido de la función FECHA y de nueva cuenta ingresaré la referencia a la celda F2 que contiene el año.
Para el segundo argumento ingresaré el número 12 y en el tercer argumento el número 31 para obtener la fecha 31 de diciembre del 2016.
Como puedes observar, la función FECHA nos ayuda a crear la fecha basándonos en el año indicado en la columna F y por lo tanto podremos copiar fácilmente nuestra fórmula hacia abajo.
Antes de terminar, voy a seleccionar la referencia al rango B2:B160 y pulsaré la tecla F4 para convertirla en absoluta de manera que se mantenga fija cuando copiemos la fórmula hacia abajo.
Haré lo mismo para la segunda referencia al rango B2:B160 y finalmente pulsaré la tecla Entrar y obtendremos el número 49 que es la cantidad de facturas para el año 2016.
Recuerda que aún no hemos incluido el criterio para la región, así que, si copiamos la fórmula hacia abajo, tendremos el mismo resultado ya que ambas filas se refieren al año 2016 y lo mismo sucederá con el año 2017 y 2018 al copiar la fórmula hacia abajo.
Voy a editar de nuevo la fórmula de la celda H2 para pedirle a la función que considere la región.
Así que vamos a agregar otro par de argumentos, pero en este caso vamos a evaluar el rango C2:C160 y para el criterio voy a ingresar la referencia a la celda G2 que contiene la región que me interesa contar.
Nuestra fórmula va a contar las facturas del año 2016 que además pertenecen a la región Norte que se indica en la celda G2.
Al pulsar Entrar obtenemos el número 8 y finalmente, haré doble clic en el controlador de relleno de la celda H2 para copiar la fórmula hacia abajo y tendremos los resultados para cada uno de los años y regiones indicados en las columnas de la izquierda.
De esta manera podemos contar celdas que cumplan con varias condiciones, utilizando la función CONTAR.SI.CONJUNTO.
En la próxima clase te mostraré cómo sumar valores utilizando una función que es muy similar a la que hemos aprendido en esta clase.