Es probable que se encuentre con la necesidad de totales corridos si está tratando con cualquier tipo de datos diarios.
Imagine que hace un seguimiento de las ventas cada día. Sus datos contienen una fila para cada fecha con un importe total de ventas, pero tal vez quiera saber el total de ventas del mes en cada día. Se trata de un total acumulado, es la suma de todas las ventas hasta e incluyendo las ventas del día actual.
En este post cubriremos múltiples formas de calcular un total acumulado para tus datos diarios. Exploraremos cómo utilizar 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 cuando se insertan o eliminan filas de datos y cómo actualizar los resultados.
Obtenga el archivo con todos los ejemplos.
Tabla de contenidos
Totales corridos con una fórmula simple
Es posible crear una fórmula básica de totales corridos utilizando el operador +.
Sin embargo, necesitaremos utilizar dos fórmulas diferentes para realizar el trabajo.
- =C3 será la primera fórmula y sólo estará en la primera fila del total acumulado.
- =C4+D3 estará en la segunda fila y se puede copiar hacia abajo las filas restantes para el total en ejecución.
La fórmula en nuestra primera fila no puede agregar la celda por encima de ella al total, ya que contiene un valor de texto para un encabezado de columna. Esto provocaría un error de #Valor! en el total en ejecución 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á lugar a un hueco en el total acumulado. Para solucionar esto, tendremos que copiar la fórmula hacia abajo desde la primera celda por encima de las filas recién insertadas hasta la última fila.
Eliminar cualquier fila dará lugar a errores #REF! ya que eliminar una fila significa eliminar una celda referenciada por la fórmula que hay debajo. Para solucionar esto, tendremos que copiar la fórmula desde la última celda libre de errores hasta la última fila.
Ejecutar totales con una fórmula SUM
Podemos evitar la incomodidad de utilizar dos fórmulas diferentes en nuestra columna de totales en ejecución utilizando la función SUM en lugar del operador +. Cuando la función SUM encuentra una celda de texto la tratará igual que si contuviera un 0.
De esta manera podemos utilizar la siguiente fórmula de manera uniforme para cada fila incluyendo la primera.
=SUMA(C3,D2)
Esta fórmula hará referencia a la cabecera de la columna que contenga texto para la primera fila, pero esto está bien ya que se trata como un 0.
Al insertar o eliminar filas, nos seguiremos encontrando con los mismos problemas de celdas en blanco y errores. Podemos solucionarlos de la misma manera que con los totales en ejecución en el método de fórmula simple.
Ejecución de totales con un rango parcialmente fijo
Otra opción con la función SUMA es referenciar sólo la columna Ventas y utilizar una referencia de rango parcialmente fijo.
Si utilizamos la siguiente fórmula =SUMA($C$3:C3), podemos copiar y pegar esto en el rango. No hará referencia a ningún encabezado de columna y el rango referenciado crecerá hasta cada fila.
Desgraciadamente, esto también tendrá los mismos problemas (y soluciones) con la inserción o eliminación de filas.
Ejecutar totales con un rango con nombre relativo
Podemos evitar los problemas con la inserción y eliminación de filas de nuestros datos si utilizamos un rango con nombre relativo. Éste hará referencia a la celda directamente superior sin importar cuántas filas insertemos o eliminemos.
Es un truco que consiste en cambiar temporalmente el estilo de referencia de Excel de A1 a R1C1. Luego definir un rango con nombre usando la notación R1C1. A continuación, volver a cambiar el estilo de referencia a A1.
En el estilo de referencia R1C1, las celdas se refieren a lo lejos que están de la celda que utiliza la referencia. Por ejemplo, =RC se refiere a la celda 2 arriba y 3 a la derecha de la celda que utiliza esta fórmula.
Podemos utilizar esta referencia relativa para crear un rango con nombre que esté siempre una celda por encima de la celda de referencia con la fórmula =RC.
Para cambiar el estilo de referencia, vaya a la pestaña Archivo y luego elija Opciones. Vaya a la sección Fórmula del menú Opciones de Excel y marque la casilla Estilo de referencia R1C1 y luego pulse el botón Aceptar.
Ahora podemos añadir nuestro rango con nombre. Ve a la pestaña Fórmula de la cinta de Excel y elige el comando Definir nombre.
Inserta un nombre como «Arriba» como nombre del rango. Añade la fórmula =RC en la entrada Refiere a y pulsa el botón Aceptar.
Ahora podemos volver a cambiar Excel al estilo de referencia por defecto. Ve a la pestaña Archivo > Opciones la sección Fórmula > desmarca la casilla Estilo de referencia R1C1 > y luego pulsa el botón Aceptar.
Ahora podemos utilizar la fórmula =SUMA(,Arriba) en nuestra columna de total en ejecución.
El rango con nombre Arriba siempre se referirá a la celda directamente superior. 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 rellenará automáticamente para cualquier nueva fila, ya que la fórmula es uniforme para toda la columna. No se necesita ninguna acción para copiar ninguna fórmula.
Ejecutar totales con una tabla dinámica
Las tablas dinámicas son súper útiles para resumir cualquier tipo de datos. Hay algo más que sumar, contar y encontrar promedios. Hay muchos otros tipos de cálculos incorporados y, de hecho, ¡hay un cálculo de total corrido!
Primero, necesitamos insertar una tabla dinámica basada en los datos. Selecciona una celda dentro de los datos y ve a la pestaña Insertar y elige el comando TablaPivotante. A continuación, pase por la ventana Crear tabla pivotante para elegir dónde quiere la tabla pivotante, ya sea en una nueva hoja de cálculo o en algún lugar de una existente.
Añada el campo Fecha en el área Filas de la tabla pivotante, y luego añada el campo Ventas en el área Valores de la tabla pivotante. Ahora añada otra instancia del campo Ventas en el área Filas.
Ahora deberíamos tener dos campos 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 algo como Total de ejecución.
Haga clic con el botón derecho del ratón en cualquiera de los valores del campo Suma de ventas2 y seleccione Mostrar valor como y, a continuación, elija Total de ejecución en.
Queremos mostrar el total de ejecución por fecha, por lo que en la siguiente ventana debemos seleccionar Fecha como Campo base.
Ya está, ahora tenemos un nuevo cálculo que muestra el total acumulado de nuestras ventas dentro de la tabla pivotante.
¿Qué ocurre si añadimos o eliminamos una fila en nuestros datos de origen, cómo afecta esto al total acumulado? Los cálculos de la tabla pivotante son dinámicos y tendrán en cuenta cualquier dato nuevo en su cálculo del total acumulado, sólo tendremos que actualizar la tabla pivotante.
Haga clic con el botón derecho en cualquier lugar dentro de la tabla pivotante y elija Actualizar en el menú.
Ejecutar totales con Power Pivot y medidas DAX
Los primeros pasos para esto son exactamente los mismos utilizando una tabla pivotante normal.
Seleccione una celda dentro de los datos y vaya a la pestaña Insertar y elija el comando PivotTable.
Cuando llegue al menú Create PivotTable, marque la casilla Add this data to the Data Model para añadir los datos al modelo de datos y habilitarlos para su uso con power pivot.
Coloque el campo Fecha en el área Filas y el campo Ventas en el área Valores de la tabla pivotante.
Con power pivot, tendremos que crear los cálculos extra que queramos utilizando el lenguaje DAX. Haga clic con el botón derecho del ratón en el nombre de la tabla en la ventana Campos de la tabla dinámica y, a continuación, seleccione Añadir medida para crear un nuevo cálculo. Tenga en cuenta que esto sólo está disponible con el modelo de datos.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Ahora podemos crear nuestra nueva medida de total en ejecución.
- En la ventana de Medida, tenemos que añadir un Nombre de medida. En este caso, podemos nombrar la nueva medida como Running Total.
- También tenemos que añadir la fórmula anterior en el cuadro Fórmula.
- 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 utilicemos esta medida en una tabla pivotante, el formato se aplicará automáticamente.
Pulse el botón Aceptar y se creará la nueva medida.
Habrá un nuevo campo listado en la ventana Campos de la tabla pivotante. Tiene un pequeño icono fx a la izquierda para denotar que es una medida y no un campo normal en los datos.
Podemos utilizar este nuevo campo como cualquier otro campo y arrastrarlo al área de Valores para añadir nuestro cálculo del total corrido en la tabla dinámica.
¿Qué ocurre con el total acumulado cuando añadimos o eliminamos datos de la tabla de origen? Al igual que una tabla dinámica normal, simplemente tenemos que hacer clic con el botón derecho del ratón en la tabla dinámica y seleccionar Actualizar para actualizar el cálculo.
Totales corridos con un Power Query
También podemos añadir totales corridos a nuestros datos utilizando power query.
Primero tenemos que importar la tabla a power query. Seleccionamos la tabla de datos y vamos a la pestaña Datos y elegimos la opción Desde tabla/rango. Esto abrirá el editor de power query.
Luego podemos ordenar nuestros datos por fecha. Este es un paso opcional que podemos añadir para que, si cambiamos el orden de nuestros datos de origen, el total acumulado siga apareciendo por fecha.
Haga clic en la palanca de filtro en el encabezado de la columna de fecha y elija Ordenar de forma ascendente entre las opciones.
Necesitamos añadir una columna de índice. Esta se utilizará en el cálculo del total acumulado más adelante. Vaya a la pestaña Añadir columna y haga clic en la flecha pequeña junto a la columna de índice para insertar un índice que comience en el 1 de la primera fila.
Necesitamos añadir una nueva columna a nuestra consulta para calcular el total acumulado. Vaya a la pestaña Añadir columna y elija el comando Columna personalizada.
Podemos nombrar la columna como Total corrido y añadir la siguiente fórmula.
La función List.Range crea una lista de valores de la columna Ventas comenzando en la primera fila (elemento 0) que abarca un número de filas basado en el valor de la columna índice.
La función List.Sum suma entonces esta lista de valores que es nuestro total actual.
Ya no necesitamos la columna índice, ha cumplido su propósito y podemos eliminarla. Hacemos clic con el botón derecho del ratón en la cabecera de la columna y seleccionamos Eliminar en las opciones.
Ya tenemos nuestro total en funcionamiento y hemos terminado con el editor de consultas. Podemos cerrar la consulta y cargar los resultados en una nueva hoja de trabajo. Vaya a la pestaña Inicio del editor de consultas y pulse el botón Cerrar & Cargar.
¿Qué ocurre con el total en ejecución cuando añadimos o eliminamos filas de nuestros datos de origen? Tendremos que refrescar la tabla de salida de power query para actualizar el total en ejecución con los cambios. Haga clic con el botón derecho en cualquier lugar de la tabla y elija Actualizar para actualizar la tabla.
Con el paso de ordenación opcional anterior, si añadimos fechas fuera de orden a los datos de origen, power query ordenará por fecha y devolverá el orden correcto por fecha para el total acumulado.
Conclusiones
Hay muchas opciones diferentes para calcular totales acumulados en Excel.
Hemos explorado opciones que incluyen fórmulas en la hoja de trabajo, tablas pivotantes, fórmulas DAX de power pivot y power query. Algunos ofrecen una solución más robusta cuando se añaden o eliminan 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 fácilmente. Otras soluciones como las tablas dinámicas, DAX y power query son más robustas y manejan la inserción o eliminación de filas de datos con facilidad, pero son más difíciles de configurar.
Es bueno ser consciente de 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 cálculo podrían ser el camino a seguir.