Může Excel vytisknout průběžný součet v zápatí pro každou stránku? Není vestavěný, ale problém vyřeší krátké makro.
Sledovat video
- Cíl: Celková kategorie tisku a% kategorie v dolní části každé vytištěné stránky
- Problém: Nic v uživatelském rozhraní aplikace Excel nemůže dát vzorec vědět, že jste ve spodní části vytištěné stránky
- Ano, konce stránek můžete „vidět“, ale vzorce je nevidí
- Možné řešení: Použijte makro
- Strategie: Přidejte průběžný součet a% kategorie pro každý řádek. Skrýt na všech řádcích.
- Průběžný součet pro vzorec kategorie:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % vzorce kategorie:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Pokud je váš sešit uložen jako XLSX, uložte jej jako XLSM
- Pokud jste makra nikdy nepoužili, změňte zabezpečení maker
- Pokud jste nikdy nepoužívali makra, zobrazte kartu Vývojář
- Přepnout na VBA
- Vložte modul
- Zadejte kód
- Přiřaďte toto makro k tvaru
- Při změně velikosti stránky spusťte resetovací makro
Přepis videa
Naučte se Excel z Podcastu, epizoda 2058: Celkový běh na konci každé stránky
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka zaslaná Wileyem: Wiley chce v posledním řádku každé vytištěné stránky ukázat průběžný součet příjmů a procento kategorie. Takže Wiley zde vytiskla sestavy se spoustou a spoustou záznamů, s více stránkami pro každou kategorii ve sloupci A. A když se dostaneme dolů na konec stránky pro tisk, Wiley zde hledá součet, který ukazuje celkové výnosy, průběžný součet v této kategorii a poté procento kategorie. Takže můžete vidět, že jsme na 9,7%, když přejdu na stránku 2 - 21,1, stránku 3 - 33,3 a tak dále. A na konci stránky, kde skončíme s kategorií A, celkový součet za kategorii a celkový součet 100%. Dobře, a když se mě na to Wiley zeptala, říkala jsem si: „Ach ne, my ne - tam“v zápatí není možné určit průběžný součet. “ Dobře, tak to je nepochybně hrozný levný cheat a doporučuji každému, kdo to sleduje na YouTube, pokud máte lepší způsob, prosím, v každém případě uveďte, že v komentářích, dobře? Takže můj nápad je právě tam ve sloupcích G a H, skrýt průběžný součet a procento kategorie v každém jednotlivém řádku. Dobře, a pak pomocí makra zjistíme, zda jsme na konci stránky.na konci stránky.na konci stránky.
Dobře, takže dva vzorce, které zde chceme, říkají, hej, pokud se tato kategorie rovná předchozí kategorii. Pokud tedy A6 = A5, vezměte SUM z těchto příjmů, takže je to ve F6 a předchozí průběžný součet tam nahoře v G5. Nyní, protože zde používám funkci SUMA, nedochází k chybě, pokud bychom se někdy pokusili přidat běh celkem. Jinak budeme v úplně nové kategorii, takže když přepneme z A do B, vezmeme SUM hodnoty nalevo od nás, kterou jsem tam mohl dát F6. Ale tady jsme, víte, příliš pozdě. A pak procento z kategorie, tohle bude strašně neefektivní. Bereme tržby na tomto řádku dělené SUMEM všech výnosů, kde se kategorie rovná A6. To jsou všechny kategorie,toto je kategorie v tomto řádku a poté sečtěte odpovídající buňku ze všech řádků. Samozřejmě, $ znaky - 1, 2, 3, 4 $ značky tam. Žádné znaménka $ na A6 a 4 znaménka $ tam. Dobře, a toto číslo zobrazíme jako oddělovač čísla, možná 1 000, klikneme na OK a pak sem jako procento s jedním takovým desetinným místem. Dobře, a zkopírujeme tento vzorec do všech buněk. BAM, takhle, dobře. Nyní je ale cílem zajistit, abychom tyto součty viděli, až se dostaneme na konec stránky. Dobře, je to tam. To je automatické zalomení stránky a později, když přepneme z konce A na B, ruční zalomení stránky. Toto ruční zalomení stránky se tedy liší od automatického zalomení stránky.a toto číslo zobrazíme jako číslo, možná 1 000 oddělovačů, klikneme na OK a pak zde jako procento s jedním desetinným místem. Dobře, a zkopírujeme tento vzorec do všech buněk. BAM, takhle, dobře. Nyní je ale cílem zajistit, abychom tyto součty viděli, až se dostaneme na konec stránky. Dobře, je to tam. To je automatické zalomení stránky a později, když přepneme z konce A na B, ruční zalomení stránky. Toto ruční zalomení stránky se tedy liší od automatického zalomení stránky.a toto číslo zobrazíme jako číslo, možná 1 000 oddělovačů, klikneme na OK a pak zde jako procento s jedním desetinným místem. Dobře, a zkopírujeme tento vzorec do všech buněk. BAM, takhle, dobře. Nyní je ale cílem zajistit, abychom tyto součty viděli, až se dostaneme na konec stránky. Dobře, je to tam. To je automatické zalomení stránky a později, když přepneme z konce A na B, ruční zalomení stránky. Toto ruční zalomení stránky se tedy liší od automatického zalomení stránky.Nyní je ale cílem zajistit, abychom tyto součty viděli, až se dostaneme na konec stránky. Dobře, je to tam. To je automatické zalomení stránky a později, když přepneme z konce A na B, ruční zalomení stránky. Toto ruční zalomení stránky se tedy liší od automatického zalomení stránky.Nyní je ale cílem zajistit, abychom tyto součty viděli, až se dostaneme na konec stránky. Dobře, je to tam. To je automatické zalomení stránky a později, když přepneme z konce A na B, ruční zalomení stránky. Toto ruční zalomení stránky se tedy liší od automatického zalomení stránky.
Dobře, nyní si tady nahoře všimnete, že tento soubor je uložen jako soubor XLSX, protože tak chce Excel ukládat soubory. XLSX je poškozený typ souboru, který neumožňuje makra, že? Nejhorší typ souboru na světě. Takže nevynechávejte tento nebo tento krok. Veškerá vaše práce odtud i ven bude ztracena. Uložit jako a nebudeme ukládat jako sešit aplikace Excel, ale jako sešit s podporou maker nebo jako binární sešit nebo jako XLS. Půjdu s Macro-Enabled Workbook. Pokud tento krok neuděláte, přijdete o zbytek práce, kterou děláte. Dobře, a pak, pokud jste ještě nikdy nespouštěli makra, klikneme pravým tlačítkem a řekneme Přizpůsobit pás karet. Tady na pravé straně vyberte pole pro vývojáře, které vám poskytne kartu Vývojář. Jakmile budete mít kartu Vývojář, můžeme přejít na Zabezpečení maker,ve výchozím nastavení to bude nahoře Zakažte všechna makra a neříkejte mi, že jste deaktivovali celá makra. Chcete přejít na druhou, tak při otevření souboru řekneme: „Hej, tady jsou makra. Vytvořili jste je? Jsi v pořádku? “ A můžete říci: Povolte makra. Dobře, klikněte na OK.
Nyní přejdeme k editoru jazyka Visual Basic. Pokud jste nikdy předtím nepoužívali Visual Basic, začnete s touto zcela šedou obrazovkou, přejděte na Zobrazit a Průzkumník projektu. Zde je seznam všech otevřených sešitů. Takže mám doplněk Řešitel, můj osobní sešit maker a tady je sešit, na kterém pracuji. Ujistěte se, že je tento sešit vybrán, proveďte Vložit, Modul. Vložte, modul zde získá pěkné velké prázdné bílé plátno. Dobře, a potom zadáte tento kód. Dobře, teď používáme objekt s názvem HPageBreak, vodorovný konec stránky. A protože to moc nepoužívám, musel jsem to zde deklarovat jako proměnnou, jako objekt HPB, takže bych mohl vidět možnosti, které mám v každém z nich k dispozici. V pořádku,zjistit, kde je dnes poslední řádek s daty, takže používám sloupec A, jdu na konec sloupce A - A1048576. Toto je L zde a ne 1, toto je L. Každý to posere. L jako v Excelu. Zní to jako Excel. Pochopit to? Excel nahoru. Přejděte tedy na A1048576, stiskněte klávesu Konec a klávesu Šipka nahoru a přejděte na poslední řádek. Zjistěte, o jaký řádek se jedná. A pak ve sloupcích G a H, a pokud to sledujete, musíte se podívat na svá data aplikace Excel a zjistit, kde jsou vaše dva nové sloupce, dobře. Nevím, kolik sloupců máte. Možná, že vaše nové sloupce skončily v I a J, nebo možná jsou v C a D. Nevím, zjistěte, kde jsou, a my všechny tyto řádky skryjeme, dobře. Takže v mém případě to začalo od G6, to je první místo, kde máme číslo,:H a pak zřetězím poslední řádek, který dnes máme, pomocí číselného formátu tří středníků, který skryje data.
Dobře, pak ten další, ten další jsem se dozvěděl z vývěsky. Pokud před spuštěním tohoto kódu nevložíte aktivní okno do režimu Náhled konce stránky, tento kód nebude fungovat. Funguje pro některé konce stránek, ale ne pro všechny konce stránek, takže je nutné dočasně zobrazit konce stránek. A pak smyčka zde: Pro každého je to moje objektová proměnná - HPB In ActiveSheet.HPageBreaks. Zjistit poslední řádek, dobře? Takže pro tento objekt, pro konec stránky, zjistěte umístění, zjistěte řádek. A toto je vlastně první řádek na další stránce, takže od toho musím odečíst 1, dobře. A pak tady, připouštím, že je to neuvěřitelně levné, jděte do sloupce 7, což je sloupec G, změňte NumberFormat na měnu, jen z toho řádku. A pak jděte do sloupce 8, což je H, změňte to na procento a pokračujte dále.Nakonec ukončete vodorovný náhled nebo náhled konce stránky a vraťte se do normálního zobrazení.
Dobře, tak to je náš kód. Budu soubor, zavřít a vrátit se do aplikace Microsoft Excel. Chci snadný způsob, jak to spustit, takže jdu do Vložit, vyberte zde pěkný tvar. Vyberu si zaoblený obdélník, nakreslím své pravé oko kolem obdélníku, Rozvržení stránky, přejděte na Efekty, vyberte efekty pro Office 2007. A pak tady na kartě Formát máme pěkný způsob, jak k tomu přidat trochu záře, dobře .
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.
Ach, chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2058.xlsm
