Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo calcular subtotales por grupo usando la función SUMIFS en Excel y Google Sheets.
Tabla de subtotales por categoría o grupo
Primero, demostraremos cómo crear una tabla de resumen de subtotales dinámica a partir de un rango de datos en Excel 365 en adelante o en Hojas de cálculo de Google.
Usamos la función ÚNICA y la función SUMIFS para subtotalizar automáticamente el Número de productos por Grupo de productos:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Para crear esta tabla de subtotales, usamos la aplicación estándar de la función SUMIFS para sumar el Número de productos que coinciden con cada uno Grupo de productos. Sin embargo, antes de que esto sea posible, necesitamos crear una lista de Grupos de productos. Los usuarios de Microsoft Excel 365 y Google Sheets tienen acceso a la función ÚNICA para crear una lista dinámica de valores únicos a partir de un rango de celdas. En este ejemplo, agregamos la siguiente fórmula a la celda E3:
| 1 | = ÚNICO (B3: B11) |
Cuando se ingresa esta fórmula, se crea automáticamente una lista debajo de la celda para mostrar todos los valores únicos que se encuentran dentro de la Grupo de productos rango de datos. En este ejemplo, la lista se amplió para cubrir E3: E5 para mostrar los 3 únicos Grupo de productos valores.
Esta es una función de matriz dinámica en la que no es necesario definir el tamaño de la lista de resultados, y se reducirá y crecerá automáticamente a medida que cambien los valores de los datos de entrada.
Tenga en cuenta que en Excel 365, la función ÚNICA no distingue entre mayúsculas y minúsculas, pero en Google Sheets sí. Considere la lista {"A"; "a"; "B"; "C"}. La salida de la función ÚNICA depende del programa:
- {"A"; "B"; "C"} en Excel 365
- {"A"; "a"; "B"; "C"} en Hojas de cálculo de Google
Si está utilizando una versión de Excel anterior a Excel 365, deberá adoptar un enfoque diferente. Esto se analiza en la siguiente sección.
Tabla de subtotales por categoría o grupo - Pre Excel 365
Si está utilizando una versión de Excel anterior a Excel 365, la función ÚNICA no está disponible para su uso. Para replicar el mismo comportamiento, puede combinar la función INDICE y la función COINCIDIR con una función CONTAR.SI para crear una fórmula de matriz para producir una lista de valores únicos a partir de un rango de celdas:
| 1 | {= ÍNDICE ($ B $ 3: $ B $ 11, COINCIDIR (0, CONTAR.SI ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Para que esta fórmula funcione, las referencias de celda fija deben escribirse con cuidado, con la función COUNTIF haciendo referencia al rango $ E $ 2: E2, que es el rango que comienza desde E2 hasta la celda sobre la celda que contiene la fórmula.
La fórmula también debe ingresarse como una fórmula de matriz presionando CTRL + MAYÚS + ENTRAR después de que se haya escrito. Esta fórmula es una Fórmula de matriz de 1 celda, que luego se puede copiar y pegar en las celdas E4, E5, etc. No ingrese esto como una fórmula de matriz para todo el rango E3: E5 en una sola acción.
De la misma manera que en el ejemplo anterior, se usa una función SUMIFS para subtotalizar Número de productos por Grupo de productos:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Suma por categoría o grupo: subtotales en tablas de datos
Como alternativa al método de tabla de resumen que se muestra arriba, podemos agregar subtotales directamente en una tabla de datos. Demostraremos esto usando las funciones IF junto con la función SUMIFS para agregar un Subtotal por grupo a la tabla de datos original.
| 1 | = SI (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Este ejemplo usa una función SUMIFS anidada dentro de una función IF. Analicemos el ejemplo en pasos:
Para agregar estadísticas de resumen directamente en una tabla de datos, podemos usar la función SUMIFS. Empezamos por sumar el Número de productos que coinciden con lo relevante Grupo de productos:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Esta fórmula produce un valor subtotal para cada fila de datos. Para mostrar subtotales solo en la primera fila de datos de cada Grupo de productos, usamos la función SI. Tenga en cuenta que los datos ya deben estar ordenados por Grupo de productos para asegurarse de que los subtotales se muestren correctamente.
| 1 | = SI (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
La función SI compara cada fila de datos Grupo de productos valor con la fila de datos encima, y si tienen el mismo valor, genera una celda en blanco (“”).
Si el Grupo de productos los valores son diferentes, se muestra la suma. De esta manera, cada Grupo de productos La suma se muestra solo una vez (en la fila de su primera instancia).
Ordenar conjuntos de datos por grupo
Si los datos aún no están ordenados, aún podemos usar la misma fórmula para el subtotal.
El conjunto de datos anterior no está ordenado por Grupo de productos, entonces el Subtotal por grupo La columna muestra cada subtotal más de una vez. Para obtener los datos en el formato que queremos, podemos seleccionar la tabla de datos y hacer clic en "Ordenar de la A a la Z".
Referencias de celda de bloqueo
Para que nuestras fórmulas sean más fáciles de leer, mostramos algunas de las fórmulas sin referencias de celda bloqueadas:
| 1 | = SI (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Pero estas fórmulas no funcionarán correctamente cuando se copien y peguen en otro lugar de su archivo. En su lugar, debe usar referencias de celda bloqueadas como esta:
| 1 | = SI (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Lea nuestro artículo sobre Referencias de celdas bloqueadas para obtener más información.
Uso de tablas dinámicas para mostrar subtotales
Para eliminar el requisito de clasificar previamente los datos por Grupo de productos, podemos utilizar el poder de las tablas dinámicas para resumir los datos. Las tablas dinámicas calculan subtotales automáticamente y muestran totales y subtotales en varios formatos diferentes.
Suma por categoría o grupo en Hojas de cálculo de Google
Estas fórmulas funcionan igual en Google Sheets que en Excel. Sin embargo, la función ÚNICA distingue entre mayúsculas y minúsculas en Google Sheets.
