Este episodio muestra tres formas de hacer totales acumulados.
Un total acumulado es, para una lista de valores numéricos, una suma de los valores de la primera fila a la fila del total acumulado. Los usos comunes de un total acumulado están en un registro de chequera o una hoja de contabilidad. Hay muchas formas de crear un total acumulado, dos de las cuales se describen a continuación.
La técnica más simple es, en cada fila, sumar el total acumulado de la fila de arriba al valor de la fila. Entonces, la primera fórmula en la fila 2 es:
=SUM(D1,C2)
La razón por la que usamos la función SUM es porque, en la primera fila, estamos mirando el encabezado en la fila de arriba. Si usamos la fórmula más simple e intuitiva de =D1+C2, se generará un error porque el valor del encabezado es texto versus numérico. La magia es que la función SUM ignora los valores de texto, que se agregan como valores cero. Cuando la fórmula se copia en todas las filas en las que se desea un total acumulado, las referencias de celda se ajustan en consecuencia:
La otra técnica también usa la función SUMA, pero cada fórmula suma todos los valores de la primera fila a la fila que muestra el total acumulado. En este caso usamos un signo de dólar ($) para hacer que la primera celda de la referencia sea una referencia absoluta, lo que significa que no se ajusta cuando se copia:
Ambas técnicas no se ven afectadas por la ordenación y la eliminación de filas, pero, al insertar filas, la fórmula debe copiarse en las nuevas filas.
Excel 2007 introdujo la Tabla, que es una reimplementación de la Lista en Excel 2003. Las Tablas introdujeron una serie de características muy útiles para las tablas de datos, como formatear, ordenar y filtrar. Con la introducción de Tablas también se nos proporcionó una nueva forma de referenciar las partes de una Tabla. Este nuevo estilo de referencia se denomina referenciación estructurada.
Para convertir el ejemplo anterior en una tabla, seleccionamos los datos que queremos incluir en la tabla y presionamos Ctrl + T.Después de mostrar un mensaje que nos pide que confirmemos el rango de la tabla y si hay encabezados existentes o no, Excel convierte los datos en una tabla formateada:
Tenga en cuenta que las fórmulas que ingresamos anteriormente siguen siendo las mismas.
Una de las funciones útiles que ofrece Tablas es el formato automático y el mantenimiento de fórmulas a medida que se agregan, eliminan, clasifican y filtran filas. Es el mantenimiento de la fórmula en particular en lo que nos centraremos y que puede ser problemático. Para mantener las tablas en funcionamiento mientras se manipulan, Excel utiliza columnas calculadas que son columnas con fórmulas como la columna D en el ejemplo anterior. Cuando se agregan nuevas filas a la parte inferior, Excel automáticamente llena las nuevas filas con la fórmula “predeterminada” para esa columna. El problema con el ejemplo anterior es que Excel se confunde con las fórmulas estándar y no siempre las maneja correctamente. Esto se hace evidente cuando se agregan nuevas filas a la parte inferior de la Tabla (seleccionando la celda inferior derecha en la Tabla y presionando TAB):
Esta deficiencia se resuelve utilizando la referencia estructurada más nueva. La referencia estructurada elimina la necesidad de hacer referencia a celdas específicas utilizando el estilo de referencia A1 o R1C1 y, en su lugar, utiliza nombres de columna y otras palabras clave para identificar y hacer referencia a las partes de una tabla. Por ejemplo, para crear la misma fórmula de total acumulado que se usó anteriormente pero utilizando referencias estructuradas, tenemos:
=SUM(INDEX((Sales),1):(@Sales))
En este ejemplo, tenemos una referencia al nombre de la columna, "Ventas", junto con el signo arroba (@) para hacer referencia a la fila de la columna en la que se encuentra la fórmula, que también se conoce como la fila actual.
Para implementar el primer ejemplo anterior, donde agregamos el valor total acumulado en la fila anterior al monto de ventas en la fila actual, puede usar la función DESPLAZAMIENTO:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Si las cantidades utilizadas para calcular el total acumulado están en dos columnas, por ejemplo, una para "Débitos" y otra para "Créditos", entonces la fórmula es:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Aquí estamos usando la función ÍNDICE para ubicar las celdas de Crédito y Débito de la primera fila, y sumamos toda la columna hasta incluir los valores de la fila actual. El total acumulado es la suma de todos los créditos hasta la fila actual incluida, menos la suma de todos los débitos hasta la fila actual incluida.
Para obtener más información sobre referencias estructuradas en particular y tablas en general, recomendamos el libro Tablas de Excel: una guía completa para crear, usar y automatizar listas y tablas de Zack Barresse y Kevin Jones.
Cuando les pedí a los lectores que votaran por sus consejos favoritos, las mesas fueron populares. Gracias a Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel y Paul Peton por sugerir esta característica. Peter Albert escribió el consejo adicional sobre referencias legibles. Zack Barresse escribió la sugerencia de bonificación de totales acumulados. Cuatro lectores sugirieron usar OFFSET para crear rangos de expansión para gráficos dinámicos: Charley Baak, Don Knowles, Francis Logan y Cecelia Rieb. Las tablas ahora hacen lo mismo en la mayoría de los casos.
Ver video
- Este episodio muestra tres formas de hacer totales acumulados
- El primer método tiene una fórmula diferente en la Fila 2 que todas las demás filas
- El primer método es = Izquierda en la fila 2 y = Izquierda + Arriba en las filas 3 a N
- Si intenta utilizar la misma fórmula, obtiene un error de #Value con = Total + Número
- El método 2 utiliza
=SUM(Up,Left)o=SUM(Previous Total,This Row Amount) - SUM ignora el texto para que no obtenga un error de VALOR
- El método 3 usa un rango de expansión:
=SUM(B$2:B2) - Los rangos en expansión son geniales pero son lentos
- Lea el documento técnico de Charles Williams sobre Excel Formula Speed
- El tercer método es un problema cuando usa Ctrl + T y agrega nuevas filas
- Excel no puede averiguar cómo escribir la fórmula
- Las soluciones provisionales requieren cierto conocimiento de referencias estructuradas en tablas
- La solución 1 es la lenta
=SUM(INDEX((Qty),1):(@Qty)) - La solución alternativa 2 es la volátil
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) se refiere a Qty en esta fila
- (Cant.) Se refiere a todos los valores de Cant.
Transcripción del video
Aprenda Excel para podcast, episodio 2004: totales acumulados
Haré un podcast de este libro completo. Haga clic en la I en la esquina superior derecha para suscribirse.
Hola, bienvenido de nuevo al netcast de mystic cell. Soy Bill Jelen. Ahora, este tema en el libro, fue una contribución de mi amigo Zach Parise. Hablando de tablas de Excel, Zach es el experto mundial en tablas de Excel. Ha escrito un libro sobre tablas de Excel, pero primero hablemos de los totales acumulados, no en tablas.
Entonces, cuando pienso en los totales acumulados, hay tres formas diferentes de hacer los totales acumulados, y la forma en que siempre comencé es en la primera fila, simplemente dices, trae el valor. Así que igual a lo que esté a mi izquierda. Muy bien, este formato aquí es solo = B2. Todos estos son texto de fórmula aquí en la esquina derecha, por lo que está viendo lo que estamos usando, y luego, de ahí en adelante, es una pequeña fórmula simple de igual al valor anterior, más el valor actual a la derecha y cópielo , pero ya sabes, tenemos este problema de que se requieren dos fórmulas diferentes y sabes que en una situación perfecta tienes exactamente la misma fórmula hasta el final, y la razón por la que tenemos que tener una fórmula diferente en la primera fila es que cuando intentas sumar 7 más el total de palabras es un error de valor,pero el trabajador interesante aquí es no solo usar left plus up, sino usar = (SUM) del valor anterior más la cantidad en esta fila, y ver que algo es lo suficientemente lejos como para ignorar los textos. Así que permite la misma fórmula. toda la calle abajo.
De acuerdo, eso fue cuando comencé a usar Excel, lo estaba usando y luego descubrí el rango en expansión, el rango en expansión dice que vamos a hacer L $ 2: L2 y lo que sucede es que esto siempre comienza en la fila 2, pero luego baja a la fila actual. Entonces, cuando miras cómo funciona esto cuando se copia, siempre comenzamos la fila 2, pero bajamos a la fila actual y este se convirtió en mi método favorito. Pensé, oh, esto es mucho más sofisticado y cuando entramos en Opciones de Excel, vamos a la pestaña Fórmulas y elegimos R1C1 en Estilo de referencia. Muy bien, R1C1, todas estas fórmulas son exactamente iguales hasta el final. No sé si entiendes R1C1, es bueno saber que tenemos fórmulas R1C1 idénticas hasta el final.
Volvamos. Así que este método de aquí es el método que me gustó, hasta que Charles Williams, un MBP de Excel de Inglaterra, que tiene un artículo increíble sobre la velocidad de la fórmula, la velocidad de la fórmula de Excel, desacreditó por completo este método. Este método, digamos que tiene 10,000 filas, cada fórmula está mirando dos referencias. Así que estás viendo 20.000 referencias, pero esta, esta es dos, esta es tres, esta es cuatro, esta es cinco y la última es 10.000 referencias, y es horriblemente más lento. y entonces dejé de usar este método.
Luego sigo leyendo a Zack en el libro de Kevin Jones sobre tablas de Excel y descubro otro problema más con este método. Así que una de las características útiles que ofrecen las tablas es 'el formato automático y las filas de mantenimiento de fórmulas se agregan, eliminan, clasifican y filtran'. De acuerdo, esa es una cita de su libro. Y para agregar una fila a una tabla, simplemente vaya a la última celda de la tabla y presione el tabulador. Entonces todo está funcionando aquí. Bajamos a 70, eso es increíble y luego A104 y pondré un 100 aquí. Muy bien, entonces 70 debería cambiar a 170 y lo hace, pero este 70 no debería haber cambiado en absoluto. De acuerdo, 68 + 2 no es un 170. Lo haré de nuevo. Un 104 y poner otros cien en el último es correcto. Estos dos no están bien. Muy bien, tenemos una situación extraña que si tuSi está usando esta fórmula y convierte en tabla, comienza a agregar filas, el total acumulado no va a funcionar. ¿Qué tan malo es eso?
Muy bien, Zack ofrece dos soluciones y ambas requieren un poco de conocimiento sobre cómo funcionan las referencias de estructura. Vamos a tener una nueva columna aquí y si quisiera hacer cantidad, igual cantidad, correcto, entonces = (@ Qty) dice cantidad en esta fila. Oh genial, bueno, hay otro tipo de referencia donde usamos Qty sin @. Mira esto. Entonces = SUM (INDEX ((Qty), 1: (@ Qty)) significa todas las cantidades y vamos a decir que queremos SUMA de la primera cantidad, entonces (INDEX ((Qty), 1 dice el el primer valor aquí, hasta la cantidad de filas actual, y esto está usando una versión realmente especial del índice, cuando el índice va seguido de dos puntos, en realidad cambia a una referencia de celda. Muy bien, esta solución infringe la regla de Charles Williams. de nosotrosTendremos que mirar todas y cada una de las referencias, por lo que cuando obtenga 10,000 filas de esto, será muy, muy lento.
Zach tiene otra solución que no viola el problema de Charles Williams, pero está usando el temido OFFSET. OFFSET es una función volátil, por lo que cada vez que calcule algo, OFFSET se volverá a calcular y todo lo que hay en la línea descendente de OFFSET se volverá a calcular. Es simplemente una excelente manera de arruinar completamente sus fórmulas, y lo que está haciendo es decir, estamos tomando el total de esta fila, subiendo una fila, más de cero columnas y lo que está haciendo es decir: tome el total de la fila anterior y luego le agregamos la cantidad de esta fila. Muy bien, ahora todo está mirando dos referencias cada vez, pero desafortunadamente el OFFSET está introduciendo funciones volátiles.
Bueno, ahí lo tiene, más de lo que nunca quiso saber sobre Running Totals. Supongo que mi opinión final aquí es usar este método, porque solo parece dos. La misma fórmula hasta el final y sus referencias de tabla estructuradas funcionarán.
Para esta exploración y otros 39 consejos realmente buenos, consulte este libro XL, los 40 mejores consejos de Excel de todos los tiempos.
En resumen de este episodio, hablamos sobre tres formas de hacer totales acumulados. El primer método tiene una fórmula diferente, fila 2, que todas las demás filas. Es igual a la izquierda en la fila 2 y luego igual a la izquierda más arriba en las filas 3 a la N, pero si intenta usar la misma fórmula, igual a la izquierda más arriba, hasta abajo, ¿cómo va a obtener un error de #Valor? . Entonces = SUM (arriba, izquierda), que es el total anterior, más esta hoja de ruta, que funciona muy bien, sin errores de valor y luego el rango de expansión que solía amar. Son geniales, pero hasta que leí el libro blanco de Charles Williams sobre la forma de velocidad de Excel. Entonces comencé a odiar estas referencias en expansión. También tiene un problema cuando usa CTRL T y agrega nuevas filas. Excel no puede descubrir cómo expandir esa fórmula, cómo agregar nuevas filas. Me encanta este consejo, ve a la última celda de la tabla y presiona Tab,eso agregará una nueva fila y luego hablamos sobre algunas referencias estructuradas, donde estamos usando la cantidad en esta fila y luego todas las cantidades. = SUMA (COMPENSACIÓN ((@ Total), - 1,00, (@ Cant.)).
Bien, quiero agradecer a Zach por contribuir con ese consejo. Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2004.xlsx
