total acumulado (también conocido comosuma acumulada) es de uso muy común en muchos casos.Esta es una métrica que te dice cuál es la suma de los valores hasta el momento.
Por ejemplo, si tiene datos de ventas mensuales, un total acumulado le dirá cuántas ventas se realizaron desde el primer día del mes hasta una fecha específica.
Hay otras situaciones en las que a menudo se utilizan los totales, como el cálculo de saldos de efectivo en extractos bancarios/libros mayores, el cálculo de calorías en planes de comidas, etc.
En Microsoft Excel, hay varias formas diferentes de calcular los totales acumulados.
El método que elija también depende de la estructura de los datos.
Por ejemplo, si tiene datos tabulares simples, puede usar una fórmula SUMA 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, los cubriré todos.Calcular un total acumulado en Exceldiferentes métodos.
¡Entonces empecemos!
Contenido
Calcular totales acumulados usando datos tabulares
Si tiene datos tabulares (es decir, tablas en Excel que no se convierten en tablas de Excel), puede usar algunas fórmulas simples para calcular los totales acumulados.
Usar el operador de suma
Suponga que tiene datos de ventas por fecha y desea在 en la columna CCálculoTotal acumulado.
Estos son los pasos para hacer esto.
Paso 1 – En la celda C2, la primera celda para la que desea ejecutar el total, ingrese
+/- 2 KHz
Esto solo obtiene el mismo valor de ventas 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 (esto ajustará automáticamente la referencia y dará el resultado correcto).
Esto le dará el resultado que se muestra a continuación.
Este es un método muy simple que funciona bien en la mayoría de los casos.
La lógica es simple: cada celda toma el valor (que es la suma acumulada hasta el día anterior) y agrega el valor en la celda adyacente (que es el valor de ventas del día actual).
Solo hay un inconveniente: si elimina cualquier fila existente en este conjunto de datos, todas las celdas a continuación devolverán un error de referencia (¡#REF!)
Si su conjunto de datos tiene esta posibilidad, use el siguiente método usando la fórmula SUM
Use SUM con referencias de celda parcialmente bloqueadas
Suponga que tiene datos de ventas por fecha y desea calcular un total acumulado en la columna C.
A continuación se muestra la fórmula SUMA que le dará un total acumulado.
= SUMA ($ B $ 2: B2)
Déjame explicarte cómo funciona esta fórmula.
En la fórmula SUM anterior, utilicé la referencia para agregar como $B$2:B2
- $ B $ 2: esta es una referencia absoluta, lo que significa que esta referencia no cambia cuando copio la misma fórmula en la celda a continuación.Entonces, cuando copie la fórmula en la celda a continuación, la fórmula cambiará a SUMA ($ B $ 2: B3)
- B2: esta es la segunda parte de la referencia, es una referencia relativa, lo que significa que cuando copio la fórmula hacia abajo o hacia la derecha, esto se ajusta.Entonces, al copiar la fórmula en la celda a continuación, el valor se convierte en B3
Lo bueno de este enfoque es que si elimina alguna fila en el conjunto de datos, esta fórmula se ajusta y todavía le da el total acumulado correcto.
Calcular el total acumulado en la hoja de Excel
Cuando se trabaja con datos tabulares en Excel, es mejor convertirlos a una tabla de Excel.Facilita la administración de datos y también facilita el uso de herramientas como Power Query y Power Pivot.
El uso de una tabla de Excel brinda varios beneficios, como referencias estructuradas (que facilitan la referencia de datos en la tabla y su uso en fórmulas) y el ajuste automático de referencias a medida que agrega o elimina datos de la tabla.
Si bien aún puede usar la fórmula anterior que le mostré en una hoja de Excel, permítame mostrarle algunas formas mejores de hacerlo.
Suponga que tiene una tabla de Excel como la siguiente y desea calcular un total acumulado en la columna C.
Aquí está la fórmula para hacer esto:
=SUMA(DatosVentas[[#Encabezados],[Venta]]:[@Venta])
La fórmula anterior puede parecer un poco larga, pero no es necesario que la escriba usted mismo.Lo que ve en una fórmula de suma se denomina referencia estructurada y es una forma eficiente para que Excel se refiera a puntos de datos específicos en una tabla de Excel.
Por ejemplo, SalesData[[#Headers],[Sale]] hace referencia al encabezado Sales en la tabla SalesData (SalesData es el nombre de la tabla de Excel que le di cuando creé la tabla)
Mientras que [@Sale] se refiere al valor en una celda en la misma fila de la columna Venta.
Solo explico esto aquí para que lo entiendas, pero incluso si no sabes nada sobre referencias estructuradas, puedes crear fácilmente esta fórmula.
Estos son los pasos para hacer esto:
- En la celda C2, ingresa =SUMA(
- Seleccione la celda B1, que es el encabezado de la columna con valor de venta.Puede usar el mouse o usar las teclas de flecha.Notará que Excel ingresa automáticamente una referencia estructurada a la celda
- Añadir: (dos puntos)
- Seleccione la celda B2.Excel insertará automáticamente referencias estructuradas a las celdas nuevamente
- Cierra el corchete y presiona Enter
También notará que no tiene que copiar la fórmula en toda la columna, la hoja de Excel lo hace automáticamente.
Otro beneficio de este enfoque es que si agrega un nuevo registro a este conjunto de datos, la hoja de Excel calcula automáticamente un total acumulado para todos los registros nuevos.
Aunque incluimos los encabezados de las columnas en la fórmula, tenga en cuenta que la fórmula ignora el texto del encabezado y solo considera los datos de la columna.
Calcular totales acumulados con Power Query
Power Query es una herramienta increíble cuando se trata de conectarse a bases de datos, extraer datos de múltiples fuentes y transformarlos antes de ponerlos en Excel.
Si ya usa Power Query, es más eficiente agregar totales acumulados al transformar los datos en el propio Power Query Editor (en lugar de obtener primero los datos en Excel y luego agregar los totales acumulados usando cualquiera de los métodos anteriores).
Si bien no hay una funcionalidad integrada en Power Query para agregar totales acumulados (me gustaría que la hubiera), aún puede hacerlo con una fórmula simple.
Suponga que tiene una tabla de Excel que se ve así y desea agregar totales acumulados a estos datos:
Estos son los pasos para hacer esto:
- Seleccione cualquier celda en una 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 Power Query Editor.
- [Opcional] Si su columna de fecha aún no está ordenada, haga clic en el ícono de filtro en la columna de fecha, luego haga clic en Ordenar de forma 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 Columna de índice, sino en la pequeña flecha negra inclinada junto a él 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á en 1 e ingresará números incrementados en 1 a lo largo de la columna.
- Haga clic en el icono Columnas personalizadas (también en la pestaña Agregar columnas)
- En el cuadro de diálogo Columnas personalizadas 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 detectaron errores de sintaxis".
- Haga clic en Aceptar.Esto agregará una nueva columna de total acumulado
- soltar columna de índice
- Haga clic en la pestaña Archivo, luego haga clic en Cerrar y cargar
Los pasos anteriores insertarán una nueva hoja de trabajo en su libro de trabajo con una tabla con totales acumulados.
Ahora, si cree que estos son demasiados pasos en comparación con el enfoque anterior con 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 en situaciones en las que tiene que extraer datos de una base de datos o combinar datos de varios libros de trabajo diferentes y también agregarles totales acumulados en el proceso.
Además, una vez que automatice esto con Power Query, la próxima vez que cambie su conjunto de datos, no tendrá que volver a hacerlo, simplemente puede actualizar la consulta y obtendrá resultados basados en el nuevo conjunto de datos.
¿Como funciona esto?
Ahora déjame explicarte rápidamente lo que sucede con este enfoque.
Lo primero que hacemos en el Editor de Power Query es insertar una columna de índice que comienza en 1 y se incrementa a medida que avanza por la celda.
Hacemos esto porque necesitamos usar esta columna cuando calculamos el total acumulado en otra columna que se inserta 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á la suma del rango especificado en ella.
El rango se especifica mediante la función List.Range.
La función List.Range toma como salida el rango especificado en la columna de venta, y el rango varía según el valor del Índice.Por ejemplo, para el primer registro, el rango es el valor de la primera venta.Este rango se expande a medida que avanza por la celda.
Entonces, para la primera celda.List.Sum solo le dará la suma del primer valor de venta, para la segunda celda le dará la suma de los dos primeros valores de venta, y así sucesivamente.
Si bien este enfoque funciona bien, puede volverse muy lento para grandes conjuntos de datos (miles de filas).
Calcule los totales acumulados en función de los criterios
Hasta ahora, hemos visto ejemplos de cómo calcular un total acumulado de todos los valores en una columna.
Pero en algunos casos, es posible que desee calcular un total acumulado para un registro específico.
Por ejemplo, a continuación tengo un conjunto de datos donde quiero contar el total acumulado de impresoras y escáneres en dos columnas diferentes.
Esto se puede hacer usando la fórmula SUMAR.SI, que calcula un total acumulado al tiempo que garantiza que se cumplan las condiciones especificadas.
Aquí está la fórmula para hacer esto para la columna de la impresora:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Asimismo, para calcular el total acumulado del escáner, utilice la siguiente fórmula:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
En la fórmula anterior, he usado SUMAR.SI, que me dará una suma en un rango cuando se cumplan las condiciones especificadas.
La fórmula toma tres parámetros:
- distancia: Este es el rango de condiciones que se compararán con la condición especificada
- criterios: este es el criterio que solo se verificará si se cumple este criterio, luego se agregará el valor en el tercer parámetro, el rango de suma
- [rango suma]: este es el rango de la suma de los valores a sumar cuando se cumple la condición
Además, endistancia和rango sumaparámetro, he bloqueado la segunda parte de la referencia para que a medida que nos movemos hacia abajo en la celda, el rango se siga expandiendo.Esto nos permite solo considerar y agregar valores hasta ese rango (por lo tanto, un total acumulado).
En esta fórmula, utilizo las columnas de encabezado (Impresoras y Escáneres) como criterio.También puede codificar el estándar si los encabezados de sus columnas no son exactamente iguales al texto estándar.
Si necesita verificar varias condiciones, puede usar la fórmula SUMAR.SI.CONJUNTO.
Totales acumulados en una tabla dinámica
Si desea agregar un total acumulado a los resultados de su tabla dinámica, puede hacerlo fácilmente usando la funcionalidad integrada en las tablas dinámicas.
Suponga que tiene una tabla dinámica como la siguiente con fechas en una columna y valores de ventas en otra.
Estos son los pasos para agregar una columna adicional que mostrará las ventas acumuladas por fecha:
- Arrastre el campo Venta y suéltelo en el área Valor.
- Esto agregará otra columna con el valor de las ventas.
- Haga clic en la opción Ventas totales 2 en el área Valor
- Haga clic en la opción Configuración de campo de valor
- En el cuadro de diálogo Configuración de campo de valor, cambie Nombre personalizado a Total acumulado
- 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 de Campos básicos, asegúrese de que Fecha esté seleccionada
- Haga clic en Aceptar
Los pasos anteriores cambiarán la segunda columna de ventas a una columna de "total acumulado".
Entonces, estos son algunos de los métodos que puede usar para calcular los totales acumulados en Excel.Si tiene datos en formato tabular, puede usar fórmulas simples y si tiene tablas de Excel, puede usar fórmulas que utilicen referencias estructuradas.
También cubrí cómo calcular totales acumulados usando Power Query y tablas dinámicas.
Espero que hayas encontrado útil este tutorial.
