Total acumulado (también llamado suma acumulativa) se utiliza con bastante frecuencia en muchas situaciones. Es una métrica que le indica cuál es la suma de los valores hasta el momento.
Por ejemplo, si tiene los datos de ventas mensuales, un total acumulado le indicará cuántas ventas se han realizado hasta un día específico desde el primer día del mes.
También hay algunas otras situaciones en las que se usa con frecuencia el total acumulado, como calcular el saldo de efectivo en sus extractos bancarios / libro mayor, contar calorías en su plan de comidas, etc.
En Microsoft Excel, hay varias formas diferentes de calcular los totales acumulados.
El método que elija también dependerá de cómo estén estructurados sus datos.
Por ejemplo, si tiene datos tabulares simples, puede usar una fórmula SUM simple, pero si tiene una tabla de Excel, es mejor usar referencias estructuradas. También puede usar Power Query para hacer esto.
En este tutorial, cubriré todos estos métodos diferentes para calcular totales acumulados en Excel.
¡Entonces empecemos!
Calcular el total acumulado con datos tabulares
Si tiene datos tabulares (es decir, una tabla en Excel que no se convierte en una tabla de Excel), puede usar algunas fórmulas simples para calcular los totales acumulados.
Uso del operador de suma
Suponga que tiene datos de ventas por fecha y desea calcular el total acumulado en la columna C.
A continuación se muestran los pasos para hacer esto.
Paso 1 - En la celda C2, que es la primera celda donde desea el total acumulado, ingrese
= B2
Esto simplemente obtendrá los mismos valores de venta en la celda B2.
Paso 2 - En la celda C3, ingrese la siguiente fórmula:
= C2 + B3
Paso 3 - Aplicar la fórmula a toda la columna. Puede usar el controlador de relleno para seleccionarlo y arrastrarlo, o simplemente copiar y pegar la celda C3 en todas las celdas restantes (lo que ajustaría automáticamente la referencia y daría el resultado correcto).
Esto le dará el resultado que se muestra a continuación.
Es un método realmente simple y funciona bien en la mayoría de los casos.
La lógica es simple: cada celda toma el valor por encima de ella (que es la suma acumulada hasta la fecha anterior) y agrega el valor en la celda adyacente (que es el valor de venta para ese día).
Solo hay un inconveniente: en caso de que elimine cualquiera de las filas existentes en este conjunto de datos, todas las celdas siguientes devolverían un error de referencia (#REF!)
Si esa es una posibilidad con su conjunto de datos, use el siguiente método que usa la fórmula SUM
Uso de SUM con referencia de celda parcialmente bloqueada
Suponga que tiene datos de ventas por fecha y desea calcular el total acumulado en la columna C.
A continuación se muestra la fórmula SUM que le dará el total acumulado.
= SUMA ($ B $ 2: B2)
Déjame explicarte cómo funciona esta fórmula.
En la fórmula SUM anterior, he usado la referencia para agregar como $ B $ 2: B2
- $ B $ 2: esta es una referencia absoluta, lo que significa que cuando copio la misma fórmula en las celdas a continuación, esta referencia no va a cambiar. Entonces, al copiar la fórmula en la celda a continuación, la fórmula cambiaría a SUM ($ B $ 2: B3)
- B2: esta es la segunda parte de la referencia, que es una referencia relativa, lo que significa que esto se ajustará a medida que copio la fórmula hacia abajo o hacia la derecha. Entonces, al copiar la fórmula en la celda a continuación, este valor se convertirá en B3
Lea también: Referencias de celda absoluta, relativa y mixta en Excel
Lo mejor de este método es que, en caso de que elimine cualquiera de las filas del conjunto de datos, esta fórmula se ajustará y aún le dará los totales acumulados correctos.
Calcular el total acumulado en una tabla de Excel
Cuando se trabaja con datos tabulares en Excel, es una buena idea convertirlos en una tabla de Excel. Facilita mucho la administración de los datos y también facilita el uso de herramientas como Power Query y Power Pivot.
Trabajar las tablas de Excel viene con beneficios como referencias estructuradas (lo que hace que sea realmente fácil hacer referencia a los datos en la tabla y usarlos en fórmulas) y ajuste automático de referencias en caso de que agregue o elimine datos de la tabla.
Si bien aún puede usar la fórmula anterior que le he mostrado en una tabla de Excel, permítame mostrarle algunos métodos mejores para hacerlo.
Suponga que tiene una tabla de Excel como se muestra a continuación y desea calcular el total acumulado en la columna C.
A continuación se muestra la fórmula que hará esto:
= SUM (SalesData [[# Headers], [Sale]]: [@ Sale])
La fórmula anterior puede parecer un poco larga, pero no es necesario que la escriba usted mismo. lo que ve dentro de la fórmula de suma se llama referencias estructuradas, que es la forma eficiente de Excel para referirse a puntos de datos específicos en una tabla de Excel.
Por ejemplo, SalesData [[# Headers], [Sale]] se refiere al encabezado Sale en la tabla SalesData (SalesData es el nombre de la tabla de Excel que di cuando creé la tabla)
Y [@Sale] se refiere al valor en la celda de la misma fila en la columna Venta.
Acabo de explicar esto aquí para su comprensión, pero incluso si no sabe nada sobre referencias estructuradas, aún puede crear fácilmente esta fórmula.
A continuación se muestran los pasos para hacer esto:
- En la celda C2, ingrese = SUMA (
- Seleccione la celda B1, que es el encabezado de la columna que tiene el valor de venta. Puede usar el mouse o usar las teclas de flecha. Notará que Excel ingresa automáticamente la referencia estructurada para esa celda
- Agregar un: (símbolo de dos puntos)
- Seleccione la celda B2. Excel volvería a insertar automáticamente la referencia estructurada para la celda
- Cierra el corchete y presiona enter
También notará que no tiene que copiar la fórmula en toda la columna, una tabla de Excel lo hace automáticamente por usted.
Otra gran ventaja de este método es que, en caso de que agregue un nuevo registro en este conjunto de datos, la tabla de Excel calcularía automáticamente el total acumulado de todos los registros nuevos.
Si bien hemos incluido el encabezado de la columna en nuestra fórmula, recuerde que la fórmula ignoraría el texto del encabezado y solo consideraría los datos de la columna.
Calcular el total acumulado con Power Query
Power Query es una herramienta increíble cuando se trata de conectarse con bases de datos, extraer los datos de múltiples fuentes y transformarlos antes de ponerlos en Excel.
Si ya está trabajando con Power Query, sería más eficiente agregar totales acumulados mientras transforma los datos dentro del propio editor de Power Query (en lugar de obtener primero los datos en Excel y luego agregar los totales acumulados usando cualquiera de los anteriores métodos cubiertos anteriormente).
Si bien no hay una función incorporada en Power Query para agregar totales acumulados (que desearía que hubiera), aún puede hacerlo usando una fórmula simple.
Suponga que tiene una tabla de Excel como se muestra a continuación y desea agregar los totales acumulados a estos datos:
A continuación se muestran los pasos para hacer esto:
- Seleccione cualquier celda de la tabla de Excel
- Haga clic en Datos
- En la pestaña Obtener y transformar, haga clic en el icono de Tabla / Rango. Esto abrirá la tabla en el editor de Power Query.
- [Opcional] En caso de que su columna Fecha aún no esté ordenada, haga clic en el ícono de filtro en la columna Fecha y luego haga clic en Ordenar ascendente
- Haga clic en la pestaña Agregar columna en el editor de Power Query
- En el grupo General, haga clic en el menú desplegable Columna de índice (no haga clic en el ícono de la Columna de índice, sino en la pequeña flecha negra inclinada que está justo al lado para mostrar más opciones)
- Haga clic en la opción "Desde 1". Al hacer esto, se agregará una nueva columna de índice que comenzaría desde uno e ingresaría números que se incrementarán en 1 en toda la columna
- Haga clic en el ícono "Columna personalizada" (que también se encuentra en la pestaña Agregar columna).
- En el cuadro de diálogo de la columna personalizada que se abre, ingrese un nombre para la nueva columna. en este ejemplo, usaré el nombre "Total acumulado"
- En el campo Fórmula de columna personalizada, ingrese la siguiente fórmula: List.Sum (List.Range (# ”Índice agregado” [Venta], 0, [Índice]))
- Asegúrese de que haya una casilla de verificación en la parte inferior del cuadro de diálogo que diga: "No se han detectado errores de sintaxis".
- Haga clic en Aceptar. Esto agregaría una nueva columna de total acumulado
- Eliminar la columna de índice
- Haga clic en la pestaña Archivo y luego haga clic en "Cerrar y cargar"
Los pasos anteriores insertarían una nueva hoja en su libro de trabajo con una tabla que tiene los totales acumulados.
Ahora, si está pensando que estos son simplemente demasiados pasos en comparación con los métodos anteriores de usar fórmulas simples, tiene razón.
Si ya tiene un conjunto de datos y todo lo que necesita hacer es agregar totales acumulados, es mejor no usar Power Query.
El uso de Power Query tiene sentido cuando tiene que extraer datos de una base de datos o combinar datos de varios libros de trabajo diferentes y, en el proceso, también agregarle totales acumulados.
Además, una vez que realice esta automatización utilizando Power Query, la próxima vez que cambie su conjunto de datos, no tendrá que volver a hacerlo, simplemente puede actualizar la consulta y le dará el resultado basado en el nuevo conjunto de datos.
¿Como funciona esto?
Ahora permítame explicarle rápidamente lo que sucede con este método.
Lo primero que hacemos en el editor de Power Query es insertar una columna de índice comenzando desde uno e incrementándose en uno a medida que avanza por las celdas.
Hacemos esto porque necesitamos usar esta columna mientras calculamos el total acumulado en otra columna que insertamos en el siguiente paso.
Luego insertamos una columna personalizada y usamos la siguiente fórmula
List.Sum (List.Range (# "Índice agregado" [Venta], 0, [Índice]))
Esta es una fórmula List.Sum que le daría la suma del rango que se especifica dentro de ella.
Y ese rango se especifica usando la función List.Range.
La función List.Range da el rango especificado en la columna de venta como salida y este rango cambia según el valor del índice. Por ejemplo, para el primer registro, el rango sería simplemente el primer valor de venta. Y a medida que avanza por las celdas, este rango se expandirá.
Entonces, para la primera celda. List.Sum solo le daría la suma del primer valor de venta, y para la segunda celda, le daría la suma de los dos primeros valores de venta y así sucesivamente.
Si bien este método funciona bien, se vuelve realmente lento con grandes conjuntos de datos: miles de filas. Si está tratando con un conjunto de datos grande y desea agregarle totales acumulados, eche un vistazo a este tutorial que muestra otros métodos que son más rápidos.Cálculo del total acumulado según criterios
Hasta ahora, hemos visto ejemplos en los que hemos calculado el total acumulado para todos los valores en una columna.
Pero puede haber casos en los que desee calcular el total acumulado para registros específicos.
Por ejemplo, a continuación tengo un conjunto de datos y quiero calcular el total acumulado de Impresoras y Escáneres por separado en dos columnas diferentes.
Esto se puede hacer usando una fórmula SUMIF que calcula el total acumulado mientras se asegura de que se cumpla la condición especificada.
A continuación se muestra la fórmula que hará esto para las columnas de la impresora:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Del mismo modo, para calcular el total acumulado de los escáneres, utilice la siguiente fórmula:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
En las fórmulas anteriores, he usado SUMIF, que me daría la suma en un rango cuando se cumplan los criterios especificados.
La fórmula toma tres argumentos:
- abarcar: este es el rango de criterios que se compararía con los criterios especificados
- Criterios: este es el criterio que se verificaría solo si se cumple este criterio, entonces se agregarían los valores en el tercer argumento, que es el rango de la suma
- [rango suma]: este es el rango de suma desde el cual se agregarían los valores si se cumplen los criterios
Además, en el abarcar y rango suma argumento, he bloqueado la segunda parte de la referencia, de modo que a medida que bajamos por las celdas, el rango seguiría expandiéndose. Esto nos permite solo considerar y agregar valores hasta ese rango (por lo tanto, los totales acumulados).
En esta fórmula, he utilizado la columna de encabezado (Impresora y Escáner) como criterio. también puede codificar los criterios si los encabezados de sus columnas no son exactamente los mismos que el texto de los criterios.
En caso de que tenga varias condiciones que deba verificar, puede usar la fórmula SUMIFS.
Total acumulado en tablas dinámicas
Si desea agregar totales acumulados en el resultado de una tabla dinámica, puede hacerlo fácilmente utilizando una funcionalidad incorporada en las tablas dinámicas.
Suponga que tiene una tabla dinámica como se muestra a continuación, donde tengo la fecha en una columna y el valor de venta en la otra columna.
A continuación se muestran los pasos para agregar una columna adicional que mostrará el total acumulado de las ventas por fecha:
- Arrastre el campo Venta y colóquelo en el área Valor.
- Esto agregará otra columna con los valores de Ventas
- Haga clic en la opción Suma de venta2 en el área Valor
- Haga clic en la opción "Configuración del campo de valor".
- En el cuadro de diálogo Configuración del campo de valor, cambie el nombre personalizado a "Totales acumulados".
- Haga clic en la pestaña "Mostrar valor como".
- En el menú desplegable Mostrar valor como, seleccione la opción "Total acumulado en"
- En las opciones del campo Base, asegúrese de que la Fecha esté seleccionada
- Haga clic en Aceptar
Los pasos anteriores cambiarían la segunda columna de venta a la columna Total acumulado.
Estas son algunas de las formas que puede utilizar para calcular el total acumulado en Excel. Si tiene datos en formato tabular, puede usar fórmulas simples, y si tiene una tabla de Excel, puede usar fórmulas que hagan uso de referencias estructuradas.
También he cubierto cómo calcular el total acumulado con Power Query y en tablas dinámicas.
Espero que este tutorial te haya resultado útil.
