Sumar una columna según los valores de otra
En esta publicación, aprenderemos cómo sumar una columna de números según los valores de otra columna. Por ejemplo, estamos intentando analizar las ventas de productos en función de la calificación promedio de los clientes. Es decir, los clientes califican nuestros productos en una escala del 1 al 10, por lo que cada producto tiene una calificación promedio como 9,8, 7,2, 6,1, etc. Nos gustaría calcular los totales de ventas de productos clasificados del 9 al 10, del 7 al 8, etc.
Al igual que con cualquier cosa en Excel, existen varias formas de lograrlo. Pero quería tener la oportunidad de hablar sobre cómo la función SUMIFS admite el uso de operadores de comparación, y este es un gran ejemplo. Y, dado que CONTAR.SI también admite operadores de comparación, también podemos calcular la cantidad de productos en cada grupo.
La idea de esta publicación surgió de un intercambio de correo electrónico con Eileen, quien hizo una pregunta sobre cómo agregar una columna de tarifas basada en los resultados en la columna de puntuación. Gracias Eileen!
Objetivo
Antes de pasar directamente a las fórmulas, echemos un vistazo rápido a nuestros datos para que podamos tener claro nuestro objetivo. Nuestros datos incluyen la identificación del producto, la calificación promedio y las ventas totales del período. Los datos se almacenan en una tabla llamada Tabla1 … así:
Estamos intentando calcular las ventas de cada grupo de productos. Los productos “excelentes” tienen una calificación de 9 a 10, los productos “excelentes” tienen una calificación de 7 a 8, los productos “promedio” tienen una calificación de 5 a 6, los productos “malos” tienen una calificación de 3 a 4 y los productos “malos” tienen una calificación de 1 a 2. Nuestro resumen debe básicamente se ve así:
Ahora que podemos ver nuestro final, conectemos los puntos y usemos SUMIFS y CONTAR.SI. He preparado un vídeo y una narrativa escrita que proporciona los detalles.
Video
Narrativo
Usaremos SUMIFS y COUNTIFS para calcular nuestros valores de resumen. Veámoslos uno a la vez.
SUMIFS
Básicamente, la función SUMIFS está diseñada para sumar una columna de números, pero incluye solo aquellas filas que cumplen una o más condiciones. Cada condición está definida por un par de argumentos. Aquí está la sintaxis básica:
=SUMIFS(rango_suma, rango_criterio1, criterio1, ...)
Dónde:
- sum_range es la columna de números a sumar
- criterios_range1 es la primera columna de criterios
- criterio1 es el primer valor de criterio
- … pares de argumentos adicionales
Se me ocurrió una pequeña narrativa que me ayuda a mantener los argumentos de la función en orden y, con suerte, también puede ayudarte a ti. Básicamente, cada “esto” en la siguiente narrativa representa un argumento: “Suma esta columna de números, pero incluye solo aquellas filas donde esta columna es igual a este valor”.
Veamos si podemos aplicar esta función a nuestro informe resumido. Aquí está la estructura del informe resumido:
Vayamos paso a paso y comencemos escribiendo una fórmula en C7 que sume la columna de ventas, pero solo incluya productos con una calificación igual a 9. Esto no es exactamente lo que queremos (realmente queremos más de 9) . , no igual a 9), pero nos ayudará a introducir los argumentos básicos en la función.
La tabla de datos se denomina Tabla1 . Entonces, usar nuestra narrativa suena así: “suma la columna Ventas, pero solo incluye aquellas filas donde la columna Calificación es igual a 9”. Por lo tanto, nuestra primera fórmula se ve así:
=SUMIFS(Tabla1[Ventas], Tabla1[Calificación],9)
Pulsamos Enter y obtenemos 0. ¿Por qué? Porque no hay productos con una calificación igual a 9. Entonces, ¿cómo actualizamos la fórmula para incluir todos los productos con una calificación de 9 o más? Aquí está la respuesta y el objetivo completo de esta publicación:
- La función SUMIFS admite operadores de comparación en argumentos de valor de criterio
Dicho de manera más simple: podemos usar lógica mayor que y menor que. Mayor que está representado por el operador de comparación y menor que utiliza el operador . Mayor o igual es así =, y menor o igual es así =.
Entonces, podemos hacer un pequeño ajuste a nuestra fórmula al incluir el operador mayor o igual que = en el argumento de criterios. Ahora, todo esto debe estar entre comillas, por lo que la versión actualizada de la fórmula está aquí:
=SUMIFS(Tabla1[Ventas], Tabla1[Calificación], "=9")
Le dimos Enter y esta vez… ¡sí, funcionó!
Hasta el momento, Excelente suma todos los productos con una calificación mayor o igual a 9.
Nota: en la práctica, probablemente ingresaría el valor 9 en una celda en lugar de en la fórmula, y luego actualizaría la fórmula para hacer referencia a la celda. Por ejemplo, si se ingresó 9 en la celda A1, la fórmula actualizada sería: =SUMIFS(Tabla1[Ventas], Tabla1[Calificación], “=”A1)
Ahora, pensemos en la siguiente categoría: Bueno. Esto debe incluir productos con calificaciones de 7 y 8. Pero seamos más precisos. Debe incluir productos con una calificación mayor o igual a 7 y menor a 9. ¿Tiene sentido? Recuerde, nuestras calificaciones incluyen decimales, por lo que debemos manejar calificaciones de 7,1 y 8,5, etc. Entonces, la fórmula para C8 es:
=SUMIFS(Tabla1[Ventas],Tabla1[Calificación],"=7",Tabla1[Clasificación],"9")
Nota : hicimos referencia a la columna Calificación dos veces. ¿Podemos hacer eso? ¡Sí! Y la función SUMIFS utiliza la lógica AND, por lo que todas las condiciones deben ser verdaderas para ser incluidas en la suma. Si necesita utilizar la lógica OR, consulte esta publicación .
Y seguimos así para completar el resumen. Por ejemplo, la fórmula para productos promedio es:
=SUMIFS(Tabla1[Ventas],Tabla1[Calificación],"=5",Tabla1[Clasificación],"7")
Y así sucesivamente hasta completar nuestro resumen:
Ahora sólo queda calcular la columna Cantidad:
Para eso, usaremos la función CONTAR.SI.
GRATIS: Desafío de velocidad de Excel
Si te ha gustado esta publicación, consulta nuestro desafío gratuito de velocidad de Excel.
Mire un vídeo corto de Excel al día durante 5 días. El tiempo total del video es de solo 45 minutos. Aprenda las habilidades de Excel que pueden ayudarle a ahorrar una hora a la semana.
Información del desafío
CONTAR.SI
COUNTIFS básicamente funciona igual que SUMIFS, pero devuelve el recuento del número de filas que satisfacen las condiciones. Como no hay una columna de números para sumar, simplemente usamos pares de argumentos para definir las condiciones (no hay ningún argumento sum_range).
Entonces, para calcular la cantidad de productos Excelentes, usaríamos esto en D7 :
=CONTAR.SI(Tabla1[Calificación],"=9")
Entonces, buenos productos en D8 :
=CONTAR.SI(Tabla1[Clasificación],"=7",Tabla1[Clasificación],"9")
Y así sucesivamente a lo largo del resumen. El resumen terminado está aquí:
Sí… ¡lo logramos! Y así es como sumar (y contar) una columna de números basándose en los valores de otra columna.
Las fórmulas se proporcionan en este archivo de muestra: ComparisonOperators.xlsx
Deja una respuesta