¿Puede Excel imprimir un total acumulado en el pie de página de cada página? No está integrado, pero una pequeña macro resolverá el problema.
Ver video
- Objetivo: Imprimir el total acumulado de la categoría y el% de la categoría en la parte inferior de cada página impresa
- Problema: nada en la interfaz de usuario de Excel puede permitir que una fórmula sepa que está al final de una página impresa
- Sí, puede "ver" los saltos de página, pero las fórmulas no pueden verlos
- Posible solución: use una macro
- Estrategia: Sume el total acumulado y el% de categoría para cada fila. Ocultar en todas las filas.
- Total acumulado para la fórmula de categoría:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % de fórmula de categoría:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Si su libro de trabajo está guardado como XLSX, haga un Guardar como para guardarlo como XLSM
- Si nunca ha utilizado macros, cambie la seguridad de las macros
- Si nunca ha usado macros, muestre la pestaña Desarrollador
- Cambiar a VBA
- Insertar un módulo
- Escribe el código
- Asignar esa macro a una forma
- A medida que cambia el tamaño de la página, ejecute la macro de restablecimiento
Transcripción del video
Aprenda Excel del Podcast, Episodio 2058: Total acumulado al final de cada página
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Pregunta de hoy enviada por Wiley: Wiley quiere mostrar un total acumulado de ingresos y un porcentaje de categoría en la última fila de cada página impresa. Entonces, Wiley ha impreso informes aquí con toneladas y toneladas de registros, varias páginas para cada categoría allá en la Columna A. Y cuando llegamos al final de la página de impresión, Wiley está buscando un total aquí que muestre los ingresos totales, total acumulado dentro de esta categoría y luego porcentaje de la categoría. Y así, pueden ver que estamos al 9,7% allí, cuando voy a la página 2 - 21,1, página 3 - 33,3 y así sucesivamente. Y en el salto de página donde terminamos con la categoría A, el gran total de la categoría y el gran total 100%. Muy bien, y cuando Wiley me preguntó sobre esto, yo dije: "Oh, no, nosotros no …No hay forma en el pie de página para poner un total acumulado ". Muy bien, este es ciertamente un truco barato horrible y animo a cualquiera que esté viendo esto en YouTube, si tiene una mejor manera, por favor, mencione eso en los comentarios, ¿de acuerdo? Entonces, mi idea está ahí en las Columnas G y H, para ocultar el total acumulado y el porcentaje de categoría en cada fila. Muy bien, y luego usamos una macro para detectar si estamos al final de la página.re al final de la página.re al final de la página.
Muy bien, entonces las dos fórmulas que queremos aquí dicen, oye, si esta categoría es igual a la categoría anterior. Entonces, si A6 = A5, tome la SUMA de estos ingresos, por lo que está en F6 y el total acumulado anterior allí en G5. Ahora, debido a que estoy usando la función SUM aquí, esto no es un error si alguna vez intentamos agregar el total acumulado. De lo contrario, estaremos en una categoría completamente nueva, así que cuando cambiemos de A a B, tomaremos la SUMA del valor a la izquierda de nosotros, que podría haber puesto F6 allí. Pero aquí estamos, ya sabes, demasiado tarde. Y luego, porcentaje de categoría, este será horriblemente ineficiente. Tomamos los ingresos en esta fila divididos por la SUMA de todos los ingresos donde la categoría es igual a A6. Estas son todas las categoríasesta es la categoría en esta fila y luego sume la celda correspondiente de todas las filas. Por supuesto, los signos $ - 1, 2, 3, 4 $ allí. No hay señales de $ en A6 y señales de 4 $ allí. Muy bien, y mostraremos este número como un Número, quizás un separador de 1000, haga clic en Aceptar, y luego aquí como un porcentaje con un decimal como ese. Muy bien, copiaremos esta fórmula en todas las celdas. BAM, así, está bien. Pero ahora el objetivo aquí es asegurarnos de que solo veamos esos totales cuando lleguemos al salto de página. Muy bien, está ahí. Eso es un salto de página automático y luego, cuando cambiamos del final de A al B, un salto de página manual. Por lo tanto, este salto de página manual aquí es diferente a un salto de página automático.y mostraremos este número como un Número, quizás un separador de 1000, haga clic en Aceptar, y luego aquí como un porcentaje con un decimal como ese. Muy bien, copiaremos esta fórmula en todas las celdas. BAM, así, está bien. Pero ahora el objetivo aquí es asegurarnos de que solo veamos esos totales cuando lleguemos al salto de página. Muy bien, está ahí. Eso es un salto de página automático y luego, cuando cambiamos del final de A al B, un salto de página manual. Por lo tanto, este salto de página manual aquí es diferente a un salto de página automático.y mostraremos este número como un Número, quizás un separador de 1000, haga clic en Aceptar, y luego aquí como un porcentaje con un decimal como ese. Muy bien, copiaremos esta fórmula en todas las celdas. BAM, así, está bien. Pero ahora el objetivo aquí es asegurarnos de que solo veamos esos totales cuando lleguemos al salto de página. Muy bien, está ahí. Eso es un salto de página automático y luego, más adelante, cuando cambiamos del final de A a B, un salto de página manual. Por lo tanto, este salto de página manual aquí es diferente a un salto de página automático.Pero ahora el objetivo aquí es asegurarnos de que solo veamos esos totales cuando lleguemos al salto de página. Muy bien, está ahí. Eso es un salto de página automático y luego, cuando cambiamos del final de A al B, un salto de página manual. Por lo tanto, este salto de página manual aquí es diferente a un salto de página automático.Pero ahora el objetivo aquí es asegurarnos de que solo veamos esos totales cuando lleguemos al salto de página. Muy bien, está ahí. Eso es un salto de página automático y luego, cuando cambiamos del final de A al B, un salto de página manual. Por lo tanto, este salto de página manual aquí es diferente a un salto de página automático.
Muy bien, ahora notará aquí arriba que este archivo se guarda como un archivo XLSX porque así es como Excel quiere guardar los archivos. XLSX es el tipo de archivo roto que no permite macros, ¿verdad? El peor tipo de archivo del mundo. Por lo tanto, no omita este paso ni este. Todo su trabajo de aquí y de afuera se perderá. Guardar como, y no lo guardaremos como un libro de Excel, sino como un libro de trabajo habilitado para macros o como un libro de trabajo binario o como un XLS. Voy a usar el libro de trabajo habilitado para macros. Si no realiza ese paso, está a punto de perder el resto del trabajo que realiza. Muy bien, y luego, si nunca antes ha ejecutado macros, haremos clic derecho y diremos Personalizar la cinta. Aquí en el lado derecho, elija el cuadro para Desarrollador, que le dará una pestaña Desarrollador. Una vez que tenga la pestaña Desarrollador, podemos ir a Seguridad de macros,de forma predeterminada, estará aquí. Desactive todas las macros, y no me diga que ha desactivado todas las macros. Si desea cambiar al segundo, de esa manera cuando abramos el archivo, diremos: “Oye, aquí hay macros. ¿Creaste estos? ¿Estas de acuerdo con esto?" Y puede decir: Habilite las macros. Muy bien, haz clic en Aceptar.
Ahora, vamos a cambiar al editor visual básico. Si nunca antes ha usado un visual básico, comenzará con esta pantalla completamente gris, vaya a Ver y al Explorador de proyectos. Aquí hay una lista de todos los libros abiertos. Así que tengo el complemento Solver, mi libro de trabajo de macros personal y aquí está el libro de trabajo en el que estoy trabajando. Asegúrese de que este libro de trabajo esté seleccionado, inserte, Módulo. Insertar, el módulo obtendrá un lienzo en blanco grande y agradable aquí. Muy bien, y luego escribirás este código. Muy bien, estamos usando un objeto aquí llamado HPageBreak, un salto de página horizontal. Y como no lo uso mucho, tuve que declararlo aquí arriba como una variable, como un objeto HPB, de esa manera podría ver las opciones que están disponibles para mí en cada uno. Bien,averigüe dónde está hoy la última fila con datos, así que estoy usando la Columna A, voy al final de la Columna A - A1048576. Esto es una L aquí y no un 1, esto es una L. Todo el mundo lo arruina. L como en Excel. Suena a Excel. ¿Consíguelo? Excel para arriba. Entonces, vaya a A1048576, presione la tecla Finalizar y la tecla Flecha hacia arriba para llegar a la última fila. Averigua qué fila es esa. Y luego en las Columnas G y H, y si está viendo esto, debe echar un vistazo a sus datos de Excel y averiguar dónde están sus dos nuevas columnas, de acuerdo. No sé cuántas columnas tienes. Tal vez sus nuevas columnas hayan terminado en I y J, o tal vez estén en C y D. No sé, averigüe dónde están y vamos a ocultar todas esas filas, está bien. Entonces, en mi caso, estaba comenzando desde G6, ese es el primer lugar donde tenemos un número:H y luego estoy concatenando la última fila que tenemos hoy usando un formato numérico de tres puntos y comas que ocultará los datos.
Muy bien, entonces este próximo, aprendí este próximo del tablero de mensajes. Si no coloca la ventana activa en el modo Vista previa de salto de página antes de ejecutar este código, este código no funcionará. Funciona para algunos de los saltos de página, pero no para todos los saltos de página, por lo que debe mostrar temporalmente los saltos de página. Y luego un bucle aquí: Para cada, esta es mi variable de objeto: HPB en ActiveSheet.HPageBreaks. Averigua la última fila, ¿de acuerdo? Entonces, para este objeto, para el salto de página, calcule la ubicación, calcule la fila. Y esta es en realidad la primera fila de la página siguiente, así que tengo que restar 1 de eso, está bien. Y luego aquí, admito que esto es increíblemente barato, vaya a la Columna 7 que es la Columna G, cambie el Formato de Número para que sea la moneda, solo de esa fila. Y luego vaya a la Columna 8 que es H y cámbiela a un porcentaje y vaya a continuación.Finalmente, salga de la vista previa horizontal o de un salto de página y vuelva a la vista normal.
Alright, so that's our code. I will File, Close and Return to Microsoft Excel. I want an easy way to run this, so I'm going to Insert, choose a nice shape here. I will choose a rounded rectangle, draw my right- around a rectangle in, Page layout, go to Effects, choose the effects for Office 2007. And then here on the Format tab we have a nice way to add some glow to that, alright.
So we've created a button, I'm just going to say Reset Page Breaks Totals, we’re going to center that, so on the Home tab, Center Vertically, Center Horizontally, increase the height and then right-click, Assign Macro and say that we're adding it to FindAllPageBreaksTwo, click OK. Alright, and then you see we have all of our totals here and I reset page break totals, and now it's - The totals are still there, the formula is still there but it's hidden them except for on the rows that is the last page break.
Alright now, I just want you to notice here that we're in A46 and A93. Page breaks are funny things. If you change the margins around a little bit, if you change the header and footer, then the page break is going to move to a new spot. So, we'll go to Wide margins, and the page break moved in those numbers in the wrong spot. Also print titles on what- rows 124 to appear at the top of each page which means that we're going to have less rows, and so then again, now my totals are in completely the wrong place that's why I need that button back here to reset page break totals and you will see that now, this cell in Row 45 and this cell in Row 86, that's in a new place.
Alright, so today's question from Wiley. We want to print category running total and % of Category at the bottom of each printed page. There's nothing in the Excel user interface, they can let a formula know that you're at the bottom of the printed page. Yeah, you can see the page breaks but the formulas can’t see them. So, one possible solution, and I'm welcoming others in the YouTube comments, use a macro. So add the running total and % of category for each row, hide all those rows. Here's the two formulas that we used: Saved As to save the workbook as XLSM or your macros will not be allowed to run next time. They'll actually- You'll lose your macros. If you've never used macros: change the macro security, show the Developer tab, switch to VBA, insert a module, type the code and then assign that macro to a shape. As the page size changes, reset the macro. And you will have a cheap solution to what Wiley is trying to do.
Oh, oye, quiero darte las gracias por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2058.xlsm
