O Excel pode imprimir um total parcial no rodapé de cada página? Não é embutido, mas uma macro curta resolverá o problema.
Assistir vídeo
- Objetivo: imprimir o total da categoria em execução e% da categoria na parte inferior de cada página impressa
- Problema: nada na interface de usuário do Excel pode permitir que uma fórmula saiba que você está no final de uma página impressa
- Sim, você pode "ver" as quebras de página, mas as fórmulas não podem vê-las
- Solução possível: use uma macro
- Estratégia: Adicione o total de execução e% da categoria para cada linha. Ocultar em todas as linhas.
- Total em execução para fórmula de categoria:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % da fórmula da categoria:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Se sua pasta de trabalho for salva como XLSX, faça um Salvar como para salvar como XLSM
- Se você nunca usou macros, altere a segurança de macro
- Se você nunca usou macros, mostre a guia Desenvolvedor
- Mudar para VBA
- Insira um módulo
- Digite o código
- Atribuir essa macro a uma forma
- Conforme o tamanho da página muda, execute a macro de redefinição
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2058: Total Correndo no Final de Cada Página
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Pergunta de hoje enviada por Wiley: Wiley deseja mostrar um total de receita corrente e a porcentagem da categoria na última linha de cada página impressa. Então, Wiley imprimiu relatórios aqui com toneladas e toneladas de registros, várias páginas para cada categoria ali na Coluna A. E quando chegamos ao final da página impressa, Wiley está procurando um total aqui que mostra a receita total, total corrente dentro desta categoria e, em seguida, porcentagem da categoria. E então, você pode ver que estamos em 9,7% lá, quando vou para a página 2 - 21,1, página 3 - 33,3 e assim por diante. E na quebra de página, concluímos a categoria A, total geral para a categoria e o total geral 100%. Tudo bem, e quando Wiley me perguntou sobre isso, eu disse, "Oh não, nós não- lá"Não há nenhuma maneira no rodapé de colocar um total corrido. ” Tudo bem, então esta é reconhecidamente uma trapaça horrível e barata e encorajo qualquer um que esteja assistindo no YouTube, se você tiver uma maneira melhor, por favor, mencione isso nos comentários, certo? E assim, minha ideia está lá nas colunas G e H, para ocultar o total corrente e a porcentagem da categoria em cada linha. Tudo bem, e então usamos uma macro para detectar se estamos no final da página.re no final da página.re no final da página.
Tudo bem, então as duas fórmulas que queremos aqui dizem, ei, se esta categoria é igual à categoria anterior. Então, se A6 = A5, então pegue a SOMA dessa receita, que está em F6 e o total anterior em execução lá em G5. Agora, como estou usando a função SUM aqui, não haverá erro se tentarmos somar o total em execução. Caso contrário, estaremos em uma categoria totalmente nova, então quando mudarmos de A para B, vamos apenas pegar a SOMA do valor à nossa esquerda, que eu poderia simplesmente colocar F6 lá. Mas aqui estamos, você sabe, tarde demais. E então a porcentagem da categoria, esta vai ser terrivelmente ineficiente. Tomamos a receita nesta linha dividida pela soma de todas as receitas em que a categoria é igual a A6. Então, essas são todas as categorias,esta é a categoria nesta linha e, em seguida, some a célula correspondente de todas as linhas. Claro, sinais $ - 1, 2, 3, 4 sinais $ aqui. Nenhum sinal $ em A6 e 4 sinais $ ali. Tudo bem, e mostraremos esse número como um número, talvez um separador de 1000, clique em OK e aqui como uma porcentagem com uma casa decimal como essa. Tudo bem, e vamos copiar esta fórmula para todas as células. BAM, assim, certo. Mas agora o objetivo aqui é garantir que só vejamos esses totais quando chegarmos à quebra de página. Tudo bem, está bem aí. Essa é uma quebra de página automática e, mais tarde, quando mudarmos do final de A para B, uma quebra de página manual. Portanto, esta quebra de página manual aqui é diferente de uma quebra de página automática.e mostraremos esse número como um número, talvez um separador de 1000, clique em OK e aqui como uma porcentagem com uma casa decimal como essa. Tudo bem, e vamos copiar esta fórmula para todas as células. BAM, assim, certo. Mas agora o objetivo aqui é garantir que só vejamos esses totais quando chegarmos à quebra de página. Tudo bem, está bem aí. Essa é uma quebra de página automática e, mais tarde, quando mudamos do final de A para B, uma quebra de página manual. Portanto, esta quebra de página manual aqui é diferente de uma quebra de página automática.e mostraremos esse número como um número, talvez um separador de 1000, clique em OK e aqui como uma porcentagem com uma casa decimal como essa. Tudo bem, e vamos copiar esta fórmula para todas as células. BAM, assim, certo. Mas agora o objetivo aqui é garantir que só vejamos esses totais quando chegarmos à quebra de página. Tudo bem, está bem aí. Essa é uma quebra de página automática e, mais tarde, quando mudarmos do final de A para B, uma quebra de página manual. Portanto, esta quebra de página manual aqui é diferente de uma quebra de página automática.Mas agora o objetivo aqui é garantir que só vejamos esses totais quando chegarmos à quebra de página. Tudo bem, está bem aí. Essa é uma quebra de página automática e, mais tarde, quando mudamos do final de A para B, uma quebra de página manual. Portanto, esta quebra de página manual aqui é diferente de uma quebra de página automática.Mas agora o objetivo aqui é garantir que só vejamos esses totais quando chegarmos à quebra de página. Tudo bem, está bem aí. Essa é uma quebra de página automática e, mais tarde, quando mudamos do final de A para B, uma quebra de página manual. Portanto, essa quebra de página manual aqui é diferente de uma quebra de página automática.
Certo, agora você notará aqui que este arquivo é salvo como um arquivo XLSX porque é assim que o Excel deseja salvar os arquivos. XLSX é o tipo de arquivo corrompido que não permite macros, certo? O pior tipo de arquivo do mundo. Portanto, não pule esta ou esta etapa. Todo o seu trabalho daqui e de fora será perdido. Salvar como, e vamos salvar não como uma pasta de trabalho do Excel, mas como uma pasta de trabalho habilitada para macro ou como uma pasta de trabalho binária ou como um XLS. Vou usar a pasta de trabalho habilitada para macro. Se você não fizer essa etapa, estará prestes a perder o resto do trabalho que faz. Tudo bem, e então, se você nunca executou macros antes, vamos clicar com o botão direito e dizer Personalizar a Faixa de Opções. Aqui no lado direito, escolha a caixa para Desenvolvedor, que lhe dará uma guia Desenvolvedor. Depois de ter a guia Desenvolvedor, podemos ir para Segurança de macro,por padrão, estará aqui Desative todas as macros e não me diga que desativou todas as macros. Você deseja mudar para o segundo, dessa forma, quando abrirmos o arquivo, diremos: “Ei, há macros aqui. Você os criou? Você está bem com isso? " E você pode dizer: Habilite as macros. Tudo bem, clique em OK.
Agora, vamos mudar para o editor visual básico. Se você nunca usou um visual básico antes, você vai começar com esta tela completamente cinza, vá para Exibir e Explorador de Projetos. Aqui está uma lista de todas as pastas de trabalho abertas. Portanto, tenho o suplemento Solver, minha pasta de trabalho de macro pessoal e aqui está a pasta de trabalho em que estou trabalhando. Certifique-se de que esta pasta de trabalho esteja selecionada, insira, Módulo. Inserir, o Módulo obterá uma grande tela em branco aqui. Tudo bem, e então você vai digitar este código. Tudo bem agora, estamos usando um objeto aqui chamado HPageBreak, uma quebra de página horizontal. E como não uso muito isso, tive que declarar aqui em cima como uma variável, como um objeto HPB, assim eu poderia ver as escolhas que tenho em cada um. Tudo bem,descobrir onde está a última linha com dados hoje, então estou usando a coluna A, vou para o final da coluna A - A1048576. Este é um L aqui e não um 1, este é um L. Todo mundo estraga tudo. L como no Excel. Parece Excel. Pegue? Excel para cima. Então, vá para A1048576, pressione a tecla End e a tecla de seta para cima para chegar à última linha. Descubra que linha é essa. E então nas Colunas G e H, e se você estiver observando isso, você precisa dar uma olhada em seus dados do Excel e descobrir onde suas duas novas colunas estão, certo. Não sei quantas colunas você tem. Talvez suas novas colunas tenham terminado em I e J, ou talvez estejam em C e D. Não sei, descubra onde estão e vamos ocultar todas essas linhas, certo. Então, no meu caso, foi começando com G6, que é o primeiro lugar onde temos um número,:H e então estou concatenando a última linha que temos hoje usando um formato de número de três pontos-e-vírgulas que ocultará os dados.
Tudo bem, então este próximo, eu aprendi este próximo no quadro de mensagens. Se você não colocar a janela ativa no modo Visualização de quebra de página antes de executar este código, este código não funcionará. Funciona para algumas das quebras de página, mas não para todas as quebras de página, portanto, você deve exibi-las temporariamente. E então um loop aqui: For Each, esta é minha variável de objeto - HPB In ActiveSheet.HPageBreaks. Descubra a última linha, certo? Portanto, para este objeto, para a quebra de página, descubra a localização, descubra a linha. E esta é na verdade a primeira linha da próxima página, então eu tenho que subtrair 1 disso, certo. E então aqui, eu admito que isso é incrivelmente barato, vá para a coluna 7 que é a coluna G, mude o NumberFormat para ser a moeda, apenas dessa linha. E então vá para a coluna 8 que é H e mude para uma porcentagem e vá em seguida.Finalmente, saia da visualização horizontal ou de quebra de página e volte para a visualização 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, ei, quero agradecer por você passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2058.xlsm
