7 formas de sumar totales acumulados en Excel

Es probable que se encuentre con la necesidad de acumular totales si está tratando con cualquier tipo de datos diarios.

Imagina que haces un seguimiento de las ventas todos los días. Sus datos contienen una fila para cada fecha con un monto total de ventas, pero tal vez desee conocer las ventas totales del mes en cada día. Este es un total acumulado, es la suma de todas las ventas hasta e incluyendo las ventas del día actual.

En esta publicación, cubriremos varias formas de calcular un total acumulado para sus datos diarios. Exploraremos cómo usar fórmulas de hojas de trabajo, tablas dinámicas, power pivot con DAX y power query.

También exploraremos qué sucede con el cálculo del total acumulado al insertar o eliminar filas de datos y cómo actualizar los resultados.

Obtenga el archivo con todos los ejemplos.

Descargar archivo de ejemplo

Contenidos
  1. Totales acumulados con una fórmula simple
  2. Totales acumulados con una fórmula SUM
  3. Totales acumulados con un rango parcialmente fijo
  4. Totales acumulados con un rango con nombre relativo
  5. Totales acumulados con una tabla dinámica
  6. Totales acumulados con medidas de Power Pivot y DAX
  7. Ejecución de totales con una Power Query
  8. Conclusiones

Totales acumulados con una fórmula simple

Es posible crear una fórmula de total acumulado básica usando el operador + .

Sin embargo, necesitaremos usar dos fórmulas diferentes para hacer el trabajo.

  1. =C3 será la primera fórmula y solo estará en la primera fila del total acumulado.
  2. =C4+D3 estará en la segunda fila y se puede copiar en las filas restantes para el total acumulado.

La fórmula en nuestra primera fila no puede agregar la celda de arriba al total ya que contiene un valor de texto para un encabezado de columna. ¡ Esto causaría un #VALOR! error para aparecer en el total acumulado ya que el + no puede manejar valores de texto. Evitamos esto con una fórmula diferente en la primera fila que no hace referencia a la celda de arriba.

¿Qué sucede con el total acumulado cuando insertamos o eliminamos filas en nuestros datos?

Insertar una nueva fila dará como resultado una brecha en el total acumulado. Para solucionar esto, necesitaremos copiar la fórmula desde la primera celda arriba de las filas recién insertadas hasta la última fila.

¡ Eliminar cualquier fila resultará en #REF! errores ya que eliminar una fila significa eliminar una celda a la que hace referencia la fórmula debajo de ella. Para solucionar esto, necesitaremos copiar la fórmula desde la última celda sin errores hasta la última fila.

Totales acumulados con una fórmula SUM

Podemos evitar la incomodidad de usar dos fórmulas diferentes en nuestra columna de total acumulado utilizando la función SUMA en lugar del operador + . Cuando la función SUMA encuentra una celda de texto, la tratará igual que si tuviera un 0.

De esta manera, podemos usar la siguiente fórmula de manera uniforme para cada fila, incluida la primera fila.

=SUMA(C3,D2)

Esta fórmula hará referencia al encabezado de la columna que contiene el texto de la primera fila, pero está bien, ya que se trata como un 0.

Al insertar o eliminar filas, seguiremos encontrando los mismos problemas con celdas en blanco y errores. Podemos corregirlos de la misma manera que con los totales acumulados en el método de fórmula simple.

Totales acumulados con un rango parcialmente fijo

Otra opción con la función SUMA es solo hacer referencia a la columna Ventas y usar una referencia de rango parcialmente fijo.

Si usamos la siguiente fórmula =SUM($C$3:C3) , podemos copiar y pegar esto en el rango. No hará referencia a ningún encabezado de columna y el rango al que se hace referencia crecerá en cada fila.

Desafortunadamente, esto también tendrá los mismos problemas (y soluciones) al insertar o eliminar filas.

Totales acumulados con un rango con nombre relativo

Podemos evitar los problemas de insertar y eliminar filas de nuestros datos si usamos un rango con nombre relativo. Esto se referirá a la celda directamente arriba sin importar cuántas filas insertemos o eliminemos.

Este es un truco que consiste en cambiar temporalmente el estilo de referencia de Excel de A1 a R1C1 . Luego definiendo un rango con nombre usando la notación R1C1. Luego, cambie el estilo de referencia de nuevo a A1.

En el estilo de referencia R1C1, se hace referencia a las celdas según la distancia a la que se encuentran de la celda que usa la referencia. Por ejemplo, =R[-2]C[3] se refiere a la celda 2 arriba y 3 a la derecha de la celda usando esta fórmula.

Podemos usar esta referencia relativa para crear un rango con nombre que siempre está una celda arriba de la celda de referencia con la fórmula =R[-1]C .

Para cambiar el estilo de referencia, vaya a la pestaña Archivo y luego elija Opciones . Vaya a la sección Fórmula en el menú Opciones de Excel y marque la casilla de estilo de referencia F1C1 y luego presione el botón Aceptar .

Ahora podemos agregar nuestro rango con nombre. Vaya a la pestaña Fórmula de la cinta de opciones de Excel y elija el comando Definir nombre .

Inserte un nombre como " Arriba " como el nombre del rango. Agregue la fórmula =R[-1]C en la entrada Se refiere a y presione el botón Aceptar .

Ahora podemos volver a cambiar Excel al estilo de referencia predeterminado. Vaya a la pestaña Archivo Opciones de la sección Fórmula desmarque la casilla de estilo de referencia F1C1 luego presione el botón Aceptar .

Ahora podemos usar la fórmula =SUMA([@Ventas],Arriba) en nuestra columna de total acumulado.

El rango con nombre Arriba siempre se referirá a la celda directamente arriba. Cuando insertamos o eliminamos filas, el rango con nombre relativo se ajustará en consecuencia y no se necesita ninguna acción.

De hecho, si colocamos nuestros datos en una tabla de Excel , la fórmula se completará automáticamente para cualquier fila nueva, ya que la fórmula es uniforme para toda la columna. No se necesita ninguna acción para copiar ninguna fórmula.

Totales acumulados con una tabla dinámica

Las tablas dinámicas son muy útiles para resumir cualquier tipo de datos. Hay más en ellos que solo sumar, contar y encontrar promedios. Hay muchos otros tipos de cálculos incorporados, ¡y en realidad hay un cálculo total acumulado!

Primero, necesitamos insertar una tabla dinámica basada en los datos. Seleccione una celda dentro de los datos y vaya a la pestaña Insertar y elija el comando Tabla dinámica . Luego vaya a la ventana Crear tabla dinámica para elegir dónde desea la tabla dinámica, ya sea en una nueva hoja de trabajo o en algún lugar de una existente.

Agregue el campo Fecha en el área Filas de la tabla dinámica, luego agregue el campo Ventas en el área Valores de la tabla dinámica. Ahora agregue otra instancia del campo Ventas en el área Filas .

Ahora deberíamos tener dos campos de Ventas idénticos con uno de ellos etiquetado como Suma de Ventas2 . Podemos cambiar el nombre de esta etiqueta en cualquier momento simplemente escribiendo sobre ella con algo como Total acumulado .

Haga clic con el botón derecho en cualquiera de los valores en el campo Suma de ventas2 y seleccione Mostrar valor como y luego elija Total acumulado en .

Queremos mostrar el total acumulado por fecha, por lo que en la siguiente ventana debemos seleccionar Fecha como campo base .

Eso es todo, ahora tenemos un nuevo cálculo que muestra el total acumulado de nuestras ventas dentro de la tabla dinámica.

¿Qué sucede si agregamos o eliminamos una fila en nuestros datos de origen? ¿Cómo afecta esto al total acumulado? Los cálculos de la tabla dinámica son dinámicos y tendrán en cuenta cualquier dato nuevo en su cálculo total acumulado, solo necesitaremos actualizar la tabla dinámica.

Haga clic con el botón derecho en cualquier lugar dentro de la tabla dinámica y elija Actualizar en el menú.

Totales acumulados con medidas de Power Pivot y DAX

Los primeros dos pasos para esto son exactamente los mismos usando una tabla dinámica normal.

Seleccione una celda dentro de los datos y vaya a la pestaña Insertar y elija el comando Tabla dinámica .

Cuando llegue al menú Crear tabla dinámica , marque la casilla Agregar estos datos al modelo de datos para agregar los datos al modelo de datos y permitir su uso con Power Pivot.

Coloque el campo Fecha en el área Filas y el campo Ventas en el área Valores de la tabla dinámica.

Con Power Pivot, necesitaremos crear cualquier cálculo adicional que queramos usando el lenguaje DAX. Haga clic con el botón derecho en el nombre de la tabla en la ventana Campos de tabla dinámica , luego seleccione Agregar medida para crear un nuevo cálculo. Tenga en cuenta que esto solo está disponible con el modelo de datos.

=CALCULATE (    SUM ( Sales[Sales] ),    FILTER (        ALL (Sales[Date] ),        Sales[Date] = MAX (Sales[Date])    ))

Ahora podemos crear nuestra nueva medida de total acumulado.

  1. En la ventana Medida , necesitamos agregar un Nombre de medida . En este caso, podemos nombrar la nueva medida como Total acumulado .
  2. También necesitamos agregar la fórmula anterior en el cuadro Fórmula .
  3. Lo bueno de Power Pivot es la capacidad de asignar un formato de número a una medida. Podemos elegir el formato de Moneda para nuestra medida. Siempre que usemos esta medida en una tabla dinámica, el formato se aplicará automáticamente.

Pulse el botón OK y se creará la nueva medida.

Aparecerá un nuevo campo en la ventana Campos de la tabla dinámica . Tiene un pequeño ícono fx a la izquierda para indicar que es una medida y no un campo regular en los datos.

Podemos usar este nuevo campo como cualquier otro campo y arrastrarlo al área de Valores para agregar nuestro cálculo total acumulado en la tabla dinámica.

¿Qué sucede con el total acumulado cuando agregamos o eliminamos datos de la tabla de origen? Al igual que una tabla dinámica normal, simplemente debemos hacer clic con el botón derecho en la tabla dinámica y seleccionar Actualizar para actualizar el cálculo.

Ejecución de totales con una Power Query

También podemos agregar totales acumulados a nuestros datos usando power query.

Primero necesitamos importar la tabla en power query. Seleccione la tabla de datos y vaya a la pestaña Datos y elija la opción Desde tabla/rango . Esto abrirá el editor de consultas de energía.

A continuación, podemos ordenar nuestros datos por fecha. Este es un paso opcional que podemos agregar para que, si cambiamos el orden de nuestros datos de origen, el total acumulado siga apareciendo por fecha.

Haga clic en el interruptor de filtro en el encabezado de la columna de fecha y elija Ordenar de forma ascendente de las opciones.

Necesitamos agregar una columna de índice. Esto se usará en el cálculo del total acumulado más adelante. Vaya a la pestaña Agregar columna y haga clic en la flecha pequeña junto a la columna de índice para insertar un índice que comience en 1 en la primera fila.

Necesitamos agregar una nueva columna a nuestra consulta para calcular el total acumulado. Vaya a la pestaña Agregar columna y elija el comando Columna personalizada .

Podemos nombrar la columna como Total acumulado y agregar la siguiente fórmula.

List.Sum(List.Range(#"Added Index"[Sales],0,[Index]))

La función List.Range crea una lista de valores de la columna Ventas a partir de la primera fila (elemento 0) que abarca una cantidad de filas según el valor de la columna de índice.

La función List.Sum luego suma esta lista de valores, que es nuestro total acumulado.

Ya no necesitamos la columna de índice, ha cumplido su propósito y podemos eliminarla. Haga clic derecho en el encabezado de la columna y seleccione Eliminar de las opciones.

Tenemos nuestro total acumulado y hemos terminado con el editor de consultas. Podemos cerrar la consulta y cargar los resultados en una nueva hoja de cálculo. Vaya a la pestaña Inicio del editor de consultas y presione el botón Cerrar y cargar .

¿Qué sucede con el total acumulado cuando agregamos o eliminamos filas de nuestros datos de origen? Tendremos que actualizar la tabla de salida de consulta de energía para actualizar el total acumulado con los cambios. Haga clic derecho en cualquier parte de la tabla y elija Actualizar para actualizar la tabla.

Con el paso de ordenación opcional anterior, si agregamos fechas fuera de orden a los datos de origen, Power Query ordenará por fecha y devolverá el orden por fecha correcto para el total acumulado.

Conclusiones

Hay muchas opciones diferentes para calcular los totales acumulados en Excel.

Hemos explorado opciones que incluyen fórmulas en la hoja de trabajo, tablas dinámicas, fórmulas DAX dinámicas y consulta avanzada. Algunos ofrecen una solución más robusta al agregar o eliminar filas de los datos, otros métodos ofrecen una implementación más sencilla.

Las fórmulas simples en la hoja de trabajo son fáciles de configurar, pero no manejarán la inserción o eliminación de nuevas filas de datos con facilidad. Otras soluciones, como las tablas dinámicas, DAX y Power Query, son más sólidas y manejan la inserción o eliminación de filas de datos fácilmente, pero son más difíciles de configurar.

Es bueno conocer los pros y los contras de cada método y elegir el más adecuado. Si no va a insertar o eliminar nuevos datos, entonces las fórmulas de la hoja de trabajo pueden ser el camino a seguir.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.