Total acumulado en una tabla de Excel
Las tablas son una de las mejores características de Excel. Si bien es posible utilizar la referencia de celda estándar con una tabla, tienen su propio estilo de referencia llamado referencias estructuradas. Tenemos que pensar un poco diferente para crear un total acumulado en una tabla de Excel utilizando referencias estructuradas.
Veremos todas las opciones en esta publicación.
Tabla de contenido
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0075 Total acumulado en Excel Tables.zip
Tener acceso
Todos los escenarios de esta publicación utilizan la misma tabla. La meta es:
- Cree un total acumulado en la columna Total acumulado
- Asegúrese de que los valores se calculen correctamente cuando se agreguen o eliminen filas
Referencias de celda normales
Podríamos usar referencias de celda normales en el estilo A1 o R1C1. Para esto, existen dos opciones comunes (1) Celda arriba + valor (2) Ampliar rango con referencias mixtas.
Método #1: Celda arriba + valor
El propósito de una Tabla es utilizar la misma fórmula en cada fila de la columna. Por lo tanto, no es posible utilizar simplemente la celda de arriba más el método del valor.
La fórmula en la celda D2 es:
=D1+C2
El resultado de la fórmula es #¡VALOR!; La celda D1 es un valor de texto que no se puede sumar a un número mediante un cálculo básico.
Sin embargo, la función SUMA ignora los valores de texto; por lo tanto, al utilizar SUM con una coma (conocido como operador de unión ), el valor del texto se calculará como cero. ¡Esto evita el #VALOR! error.
La fórmula en la celda D2 es:
=SUMA(D1,C2)
El resultado de esta fórmula es un total acumulado en cada fila de la columna Total acumulado.
En lugar de SUMA, también puedes usar la función N. Esto devuelve cero si la referencia de celda que contiene no es un número; en caso contrario, devuelve el número. Por ejemplo, si la celda D2 contuviera la siguiente fórmula, también crearía un total acumulado.
=N(D1)+C2
Sin embargo, ambos métodos fallan cuando se eliminan filas. La siguiente captura de pantalla muestra el resultado después de eliminar la fila 4.
¡Como podéis ver en el #REF! error, este método no funciona cuando se eliminan filas. Esto no cumple con los requisitos que queríamos.
° 2: ampliar el rango con referencias mixtas
Existe otro método que podemos adoptar utilizando rangos estándar que utiliza referencias mixtas.
La fórmula en la celda D2 es:
=SUM($C$2:C2))
Este método crea un rango en expansión para cada fila de la tabla.
Entonces, si eliminas una fila, todavía funciona… ¡perfecto! ¿O es eso? El problema surge cuando agregas una nueva fila de datos en la parte inferior:
Al principio no hay ningún problema evidente. Pero eche un vistazo a la celda D17; ¿Cuál es la fórmula que se ha copiado automáticamente? La fórmula hace referencia a la celda C18 (la última fila de la tabla), lo cual es incorrecto.
=SUM($C$2:C18)
Con este método, agregar filas hace que las fórmulas se expandan para incluir la última fila, lo que crea un problema de cálculo. Por tanto, éste tampoco es un método adecuado.
Referencias estructuradas
Realmente queremos utilizar referencias estructuradas. Después de todo, ese es el objetivo de utilizar una tabla.
Método #3: SUMA con fila de encabezado
En la sección anterior, identificamos que SUM ignora los valores de texto. Dado que la fila del encabezado en una tabla siempre es texto y una referencia de fila absoluta (es decir, no se mueve cuando se arrastra una celda hacia abajo), podemos crear un total acumulado solo con la función SUMA y referencias estructuradas.
La fórmula en la celda D2 es:
=SUMA(tblDatos[[#Encabezados],[Valor]]:[@Valor])
El resultado de esta fórmula es un total acumulado en cada fila.
- tblData[[#Headers],[Value]] es la referencia a la fila del encabezado
- [@Value] es la referencia a la celda dentro de la columna Valor contenida en la misma fila
- : (Dos puntos) entre dos referencias de celda crea un rango
Es más fácil ver este método en funcionamiento cuando seleccionamos una celda de la columna Total acumulado.
Aunque no se muestra el rango completo, se crea un único rango entre los cuadros azul y rojo.
Podemos eliminar o agregar a la Tabla y aún funciona. ¡ASOMBROSO!
Este parece ser el método perfecto. Sin embargo, creo que usar la fila del encabezado hace que esto sea más una solución visual que una solución de datos. Con lo que quiero decir, en otras partes de Excel, como Power Query y Power Pivot, no es posible hacer referencia al valor en una fila de encabezado. Por lo tanto, esto sólo funciona porque los datos están contenidos en una hoja de cálculo de Excel.
Sé que esto funciona, pero conceptualmente creo que deberíamos buscar otra solución.
Método #4: función ÍNDICE
No todo está perdido; hay otro método.
La función ÍNDICE permite hacer referencia a cualquier celda dentro de una columna.
=ÍNDICE([Valor],1)
Por lo tanto, en lugar de usar la celda del encabezado de la columna, podemos crear una referencia absoluta seleccionando la primera celda de la columna usando la función ÍNDICE.
Mire la solución a continuación:
La fórmula en la celda D2 es:
=SUMA(ÍNDICE([Valor],1):[@Valor])
- ÍNDICE([Valor],1) es siempre una referencia a la primera celda de la columna Valor
- [@Value] es la referencia a la celda dentro de la columna Valor contenida en la misma fila
- : (Dos puntos) entre las dos referencias de celda crea un rango
El resultado de esta fórmula es un total acumulado no volátil en cada fila de la columna Total acumulado. Calcula correctamente incluso si se agregan o eliminan filas.
¡Perfecto! Esta es mi opción preferida.
Otras opciones
Hay otras opciones que usan OFFSET y rangos con nombres relativos que podríamos usar. Sin embargo, los he descontado porque:
- OFFSET es una función volátil que se vuelve a calcular cada vez que cambia una celda en Excel, lo que puede provocar tiempos de cálculo lentos en las hojas de cálculo.
- Los rangos con nombres relativos son una técnica que se puede utilizar cuando estás atascado o arrinconado. Sin embargo, disponemos de técnicas adecuadas sin recurrir a esto.
Total acumulado con criterios
Sugerimos que no queremos un total acumulado único, sino el total acumulado para cada elemento individual en la columna Elemento. ¿Puede nuestro método INDEX manejar esa situación? Puedes apostar que sí.
Método #5: ÍNDICE con SUMIFS
Usaremos la misma técnica ÍNDICE dentro de la función SUMIFS.
La fórmula en la celda D2 es:
=SUMARSIFS(ÍNDICE([Valor],1):[@Valor],ÍNDICE([Artículo],1):[@Artículo],[@Artículo])
Notará que esto utiliza el mismo método INDEX que el anterior para crear un rango dinámico dentro de los argumentos sum_range y criterio_range1 de la función SUMIFS.
Comprobemos que esto funciona:
- Alfa existe en las celdas B2 (20) y B12 (80). Por lo tanto, el valor en la celda C12 debería ser 100, que es. ✅
- Alfa también existe en la celda B15 (60). El valor en la celda C15 debe ser 160, que es. ✅
Este método funciona cuando se añaden o eliminan filas, precisamente lo que necesitamos. Mira, el método INDEX es muy flexible.
Conclusión
Ahí lo tienes, muchas opciones para lograr un total acumulado en una tabla de Excel. Pero también hay muchos obstáculos.
Los métodos que utilizan referencias estándar no pueden garantizar los valores correctos al agregar o eliminar filas, por lo que deben descontarse.
Ambos métodos de referencia estructurada se pueden utilizar con COUNT, COUNTIFS, MIN, MINIFS, MAX, MAXIFS, AVERAGE y AVERAGEIFS.
El método SUM + fila de encabezado funciona, pero conceptualmente, tengo un problema al hacer referencia a la fila de encabezado de esta manera. No me malinterpretes; Si fuera la única opción, definitivamente la usaría.
El método SUMA + ÍNDICE es mi opción preferida. Es flexible y proporciona todo lo que necesitamos.
Deja una respuesta