Kan Excel voor elke pagina een lopend totaal in de voettekst afdrukken? Het is niet ingebouwd, maar een korte macro lost het probleem op.
Bekijk video
- Doel: lopende categorie afdrukken en% van categorie onderaan elke afgedrukte pagina
- Probleem: niets in de gebruikersinterface van Excel kan een formule laten weten dat u onderaan een afgedrukte pagina staat
- Ja, u kunt de pagina-einden "zien", maar formules kunnen ze niet zien
- Mogelijke oplossing: gebruik een macro
- Strategie: voeg het lopende totaal en% van de categorie toe voor elke rij. Verberg op alle rijen.
- Lopend totaal voor categorieformule:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % van categorieformule:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Als uw werkmap is opgeslagen als XLSX, voer dan een Opslaan als uit om op te slaan als XLSM
- Als u nog nooit macro's hebt gebruikt, wijzigt u de macrobeveiliging
- Als je nog nooit macro's hebt gebruikt, laat je het tabblad Ontwikkelaar zien
- Schakel over naar VBA
- Plaats een module
- Typ de code
- Wijs die macro toe aan een vorm
- Als het paginaformaat verandert, voert u de reset-macro uit
Videotranscriptie
Leer Excel van Podcast, aflevering 2058: totaal aan het einde van elke pagina
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag ingezonden door Wiley: Wiley wil een lopend totaal van de omzet en het percentage van de categorie weergeven op de laatste rij van elke afgedrukte pagina. Dus Wiley heeft hier rapporten afgedrukt met tonnen en tonnen records, meerdere pagina's voor elke categorie daar in kolom A. En als we aan het einde van de gedrukte pagina komen, zoekt Wiley hier naar een totaal dat de totale inkomsten laat zien, lopend totaal binnen deze categorie en vervolgens percentage van de categorie. En dus kun je zien dat we daar 9,7% zitten, als ik naar pagina 2 - 21.1 ga, pagina 3 - 33.3 enzovoort. En bij het pagina-einde waar we klaar zijn met categorie A, eindtotaal voor de categorie en het eindtotaal 100%. Oké, en toen Wiley me hierover vroeg, dacht ik: "Oh nee, dat doen we niet"Het is niet mogelijk in de voettekst een lopend totaal te plaatsen. " Oké, dus dit is weliswaar een vreselijke goedkope cheat en ik moedig iedereen die dit op YouTube bekijkt aan, als je een betere manier hebt, vermeld dat dan in de comments, oké? En dus is mijn idee gewoon daarbuiten in de kolommen G en H, om het lopende totaal en het percentage van de categorie in elke rij te verbergen. Oké, en dan gebruiken we een macro om te detecteren of we aan het einde van de pagina staan.opnieuw aan het einde van de pagina.opnieuw aan het einde van de pagina.
Oké, dus de twee formules die we hier willen, zeggen, hé, als deze categorie gelijk is aan de vorige categorie. Dus als A6 = A5, neem dan de SOM van deze inkomsten, dus dat is in F6 en het vorige lopende totaal daarboven in G5. Nu, omdat ik de SOM-functie hier gebruik, geeft dit geen foutmelding als we ooit zouden proberen een lopend totaal toe te voegen. Anders komen we gewoon in een geheel nieuwe categorie, dus als we van A naar B overschakelen, nemen we gewoon de SOM van de waarde links van ons, die ik daar gewoon F6 had kunnen plaatsen. Maar hier zijn we, weet je, te laat. En dan het percentage van de categorie, deze wordt vreselijk inefficiënt. We nemen de inkomsten op deze rij gedeeld door de SOM van alle inkomsten waarbij de categorie gelijk is aan A6. Dit zijn dus alle categorieën,dit is de categorie in deze rij en tel vervolgens de corresponderende cel uit alle rijen op. Natuurlijk, $ tekens - 1, 2, 3, 4 $ tekens daar. Geen $ -borden in A6 en 4 $ -borden daar. Oké, en we laten dit getal zien als een getal, misschien als een 1000-scheidingsteken, klik op OK en dan hier als een percentage met een dergelijke decimaal. Oké, en we zullen deze formule naar alle cellen kopiëren. BAM, zo, oké. Maar nu is het doel hier om ervoor te zorgen dat we die totalen alleen zien als we bij het pagina-einde komen. Oké, het is daar. Dat is een automatisch pagina-einde en als we later van het einde van A naar B overschakelen, een handmatig pagina-einde. Dit handmatige pagina-einde is hier dus anders dan een automatisch pagina-einde.en we laten dit getal zien als een getal, misschien als een scheidingsteken als 1000, klik op OK en dan hier als een percentage met één cijfer achter de komma. Oké, en we kopiëren deze formule naar alle cellen. BAM, zo, oké. Maar nu is het doel hier om ervoor te zorgen dat we die totalen alleen zien als we bij het pagina-einde komen. Oké, het is daar. Dat is een automatisch pagina-einde en later als we van het einde van A naar B overschakelen, een handmatig pagina-einde. Dit handmatige pagina-einde is hier dus anders dan een automatisch pagina-einde.en we laten dit getal zien als een getal, misschien als een 1000-scheidingsteken, klik op OK en dan hier als een percentage met één cijfer achter de komma. Oké, en we zullen deze formule naar alle cellen kopiëren. BAM, zo, oké. Maar nu is het doel hier om ervoor te zorgen dat we die totalen alleen zien als we bij het pagina-einde komen. Oké, het is daar. Dat is een automatisch pagina-einde en als we later van het einde van A naar B overschakelen, een handmatig pagina-einde. Dit handmatige pagina-einde is hier dus anders dan een automatisch pagina-einde.Maar nu is het doel hier om ervoor te zorgen dat we die totalen alleen zien als we bij het pagina-einde komen. Oké, het is daar. Dat is een automatisch pagina-einde en als we later van het einde van A naar B overschakelen, een handmatig pagina-einde. Dit handmatige pagina-einde is hier dus anders dan een automatisch pagina-einde.Maar nu is het doel hier om ervoor te zorgen dat we die totalen alleen zien als we bij het pagina-einde komen. Oké, het is daar. Dat is een automatisch pagina-einde en als we later van het einde van A naar B overschakelen, een handmatig pagina-einde. Dit handmatige pagina-einde is hier dus anders dan een automatisch pagina-einde.
Oké, nu zul je hier zien dat dit bestand wordt opgeslagen als een XLSX-bestand, want dat is hoe Excel bestanden wil opslaan. XLSX is het kapotte bestandstype dat geen macro's toestaat, toch? Slechtste bestandstype ter wereld. Sla deze stap of deze dus niet over. Al uw werk van hier en van buiten gaat verloren. Opslaan als, en we gaan niet opslaan als een Excel-werkmap, maar als een werkmap met macro's of als een binaire werkmap of als een XLS. Ik ga met Macro-Enabled Workbook. Als u die stap niet doet, staat u op het punt de rest van het werk dat u doet, kwijt te raken. Oké, en als je nog nooit macro's hebt uitgevoerd, klikken we met de rechtermuisknop en zeggen we Lint aanpassen. Kies hier aan de rechterkant het vakje voor Ontwikkelaar, waarmee u een Ontwikkelaarstabblad krijgt. Zodra u het tabblad Ontwikkelaar heeft, kunnen we naar Macrobeveiliging gaan,standaard staat het hier. Schakel alle macro's uit, en vertel me niet dat je de hele macro's hebt uitgeschakeld. Je wilt terugschakelen naar de tweede, op die manier zullen we, wanneer we het bestand openen, zeggen: “Hé, er zijn hier macro's. Heeft u deze gemaakt? Vind je dit goed? " En je kunt zeggen: schakel de macro's in. Oké, klik op OK.
Nu gaan we overschakelen naar de visuele basiseditor. Als je nog nooit eerder een visuele basis hebt gebruikt, begin je met dit volledig grijze scherm, ga naar Beeld en de Projectverkenner. Hier is een lijst met alle geopende werkmappen. Dus ik heb de Oplosser-invoegtoepassing, mijn persoonlijke macrowerkmap en hier is de werkmap waar ik aan werk. Zorg ervoor dat deze werkmap is geselecteerd, doe Invoegen, Module. Invoegen, de module krijgt hier een mooi groot leeg, wit canvas. Oké, en dan ga je deze code typen. Oké, we gebruiken hier een object genaamd HPageBreak, een horizontaal pagina-einde. En omdat ik dit niet vaak gebruik, moest ik het hier als een variabele declareren, als een object HPB, op die manier zou ik de keuzes kunnen zien die voor mij beschikbaar zijn in elk ervan. Oké,zoek uit waar de laatste rij met gegevens zich vandaag bevindt, dus ik gebruik kolom A, ik ga naar het einde van kolom A - A1048576. Dit is hier een L en niet een 1, dit is een L. Dat verpest iedereen. L zoals in Excel. Het klinkt als Excel. Snap je? Excel omhoog. Ga dus naar A1048576, druk op de End-toets en de Pijl-omhoog-toets om naar de laatste rij te gaan. Zoek uit welke rij dat is. En dan in de kolommen G en H, en als je dit bekijkt, moet je je Excel-gegevens bekijken en uitzoeken waar je twee nieuwe kolommen zijn, oké. Ik weet niet hoeveel kolommen je hebt. Misschien zijn je nieuwe kolommen voorbij in I en J, of misschien staan ze in C en D. Ik weet het niet, zoek uit waar die zijn en we gaan al die rijen verbergen, oké. Dus in mijn geval begon het vanaf G6, dat is de eerste plaats waar we een nummer hebben:H en dan voeg ik de laatste rij die we vandaag hebben samen met een getalnotatie van drie puntkomma's die de gegevens verbergen.
Oké, dan deze volgende, ik heb deze volgende van het mededelingenbord geleerd. Als u het actieve venster niet in de modus Pagina-einde-voorbeeld zet voordat u deze code uitvoert, zal deze code niet werken. Het werkt voor sommige pagina-einden, maar niet voor alle pagina-einden, dus u moet de pagina-einden tijdelijk weergeven. En dan een lus hier: voor elk is dit mijn objectvariabele - HPB In ActiveSheet.HPageBreaks. Zoek de laatste rij uit, oké? Dus voor dit object, voor het pagina-einde, zoek de locatie uit, zoek de rij uit. En dit is eigenlijk de eerste rij van de volgende pagina, dus daar moet ik 1 van aftrekken, oké. En dan hier, ik geef toe dat dit ongelooflijk goedkoop is, ga naar kolom 7, dat is kolom G, verander het getalformaat in valuta, alleen van die rij. En ga dan naar kolom 8, dat is H, verander het in een percentage en ga verder.Sluit ten slotte het horizontale voorbeeld of een pagina-einde-voorbeeld af en ga terug naar de normale weergave.
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 hey, ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2058.xlsm
