Môže program Excel vytlačiť priebežný súčet v päte pre každú stránku? Nie je zabudovaný, problém však vyrieši krátke makro.
Pozeraj video
- Cieľ: Celková kategória tlače a% kategórie v spodnej časti každej vytlačenej stránky
- Problém: Nič v používateľskom rozhraní programu Excel nemôže dať vzorec vedieť, že ste v dolnej časti vytlačenej stránky
- Áno, konce stránky môžete „vidieť“, ale vzorce ich nevidieť
- Možné riešenie: Použite makro
- Stratégia: Pridajte priebežný súčet a% kategórie pre každý riadok. Skryť vo všetkých riadkoch.
- Priebežný súčet pre vzorec kategórie:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % vzorca kategórie:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Ak je váš zošit uložený ako XLSX, môžete ho uložiť ako XLSM
- Ak ste nikdy nepoužívali makra, zmeňte zabezpečenie makra
- Ak ste nikdy nepoužívali makra, zobrazte kartu Vývojár
- Prepnúť na VBA
- Vložte modul
- Zadajte kód
- Priraďte dané makro k tvaru
- Pri zmene veľkosti stránky spustite resetovacie makro
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2058: Celkový počet na konci každej stránky
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešná otázka, ktorú zaslal Wiley: Wiley chce v poslednom riadku každej vytlačenej stránky zobraziť priebežný súčet výnosov a percentuálneho podielu kategórie. Takže Wiley tu vytlačila správy s tonami a tonami záznamov, s viacerými stránkami pre každú kategóriu v stĺpci A. A keď sa dostaneme na koniec tlačenej stránky, Wiley tu hľadá celkovú sumu, ktorá ukazuje celkové príjmy, priebežný súčet v tejto kategórii a potom percento z tejto kategórie. Takže vidíte, že sme na 9,7%, keď idem na stranu 2 - 21,1, stranu 3 - 33,3 a tak ďalej. A na konci stránky, kde skončíme s kategóriou A, celkový súčet za kategóriu a celkový súčet 100%. Dobre, a keď sa ma na to Wiley opýtala, bol som rád: „Ach nie, my tam nie sme“v žiadnom prípade nemôžeme uviesť priebežný súčet. “ Dobre, takže toto je nepochybne strašný lacný cheat a odporúčam každému, kto to sleduje na YouTube, ak máte lepšiu cestu, prosím, vo všetkých ohľadoch, uveďte, že v komentároch, dobre? Takže môj nápad je len vonku v stĺpcoch G a H, skryť priebežný súčet a percento kategórie v každom jednom riadku. Dobre, a potom pomocou makra zistíme, či sme na konci stránky.na konci stránky.na konci stránky.
Dobre, takže dva vzorce, ktoré tu chceme povedať, hovoria, ak sa táto kategória rovná predchádzajúcej kategórii. Ak teda A6 = A5, vezmite SUMA týchto výnosov, takže je to vo F6 a predchádzajúci priebežný súčet tam hore v G5. Pretože tu používam funkciu SUMA, nedochádza k chybe, ak by sme sa niekedy pokúsili pridať celkový súčet. Inak sa budeme nachádzať v úplne novej kategórii, takže keď prepneme z A do B, vezmeme iba SUM hodnoty vľavo od nás, ktorú som tam mohol dať práve F6. Ale tu sme, viete, príliš neskoro. A potom percento z kategórie, toto bude strašne neefektívne. Berieme výnosy v tomto riadku vydelené SUMOM všetkých výnosov, kde sa kategória rovná A6. Toto sú všetky kategórie,toto je kategória v tomto riadku a potom pridajte zodpovedajúcu bunku zo všetkých riadkov. Samozrejme, $ znaky - 1, 2, 3, 4 $ znaky tam. Žiadne znaky $ v A6 a 4 doláre tam. Dobre, a toto číslo ukážeme ako oddeľovač čísel, asi 1 000, kliknite na tlačidlo OK a potom tu ako percento s takýmto desatinným miestom. Dobre, a tento vzorec skopírujeme do všetkých buniek. BAM, tak dobre. Teraz je tu však cieľom zabezpečiť, aby sme tieto súčty videli, až keď sa dostaneme na koniec stránky. Dobre, je to tam. Jedná sa o automatické zalomenie stránky a potom neskôr, keď prepneme z konca A na B, ručné zalamovanie stránky. Toto ručné zalomenie stránky sa teda líši od automatického zalomenia stránky.a toto číslo zobrazíme ako číslo, možno 1 000 oddeľovačov, kliknite na tlačidlo OK a potom tu ako percento s takýmto desatinným miestom. Dobre, a tento vzorec skopírujeme do všetkých buniek. BAM, tak dobre. Teraz je tu však cieľom zabezpečiť, aby sme tieto súčty videli, až keď sa dostaneme na koniec stránky. Dobre, je to tam. Jedná sa o automatické zalomenie stránky a potom neskôr, keď prepneme z konca A na B, ručné zalamovanie stránky. Toto ručné zalomenie stránky sa teda líši od automatického zalomenia stránky.a toto číslo zobrazíme ako číslo, možno 1 000 oddeľovačov, kliknite na tlačidlo OK a potom tu ako percento s takýmto desatinným miestom. Dobre, a tento vzorec skopírujeme do všetkých buniek. BAM, tak dobre. Teraz je tu však cieľom zabezpečiť, aby sme tieto súčty videli, až keď sa dostaneme na koniec stránky. Dobre, je to tam. Jedná sa o automatické zalomenie stránky a potom neskôr, keď prepneme z konca A na B, ručné zalamovanie stránky. Toto ručné zalomenie stránky sa teda líši od automatického zalomenia stránky.Teraz je tu však cieľom zabezpečiť, aby sme tieto súčty videli, až keď sa dostaneme na koniec stránky. Dobre, je to tam. Jedná sa o automatické zalomenie stránky a potom neskôr, keď prepneme z konca A na B, ručné zalamovanie stránky. Toto ručné zalomenie stránky sa teda líši od automatického zalomenia stránky.Teraz je tu však cieľom zabezpečiť, aby sme tieto súčty videli, až keď sa dostaneme na koniec stránky. Dobre, je to tam. Jedná sa o automatické zalomenie stránky a potom neskôr, keď prepneme z konca A na B, ručné zalamovanie stránky. Toto ručné zalomenie stránky sa teda líši od automatického zalomenia stránky.
Dobre, teraz si tu hore všimnete, že tento súbor je uložený ako súbor XLSX, pretože tak chce Excel ukladať súbory. XLSX je nefunkčný typ súboru, ktorý neumožňuje makrá, však? Najhorší typ súboru na svete. Takže nepreskočte tento alebo tento krok. Celá vaša práca odtiaľto a odtiaľ bude stratená. Uložiť ako a nebudeme ukladať ako zošit programu Excel, ale ako zošit podporujúci makra alebo ako binárny zošit alebo ako XLS. Pôjdem so zošitom podporujúcim makro. Ak tento krok neurobíte, stratíte zvyšnú časť svojej práce. Dobre, a potom, ak ste nikdy predtým nespúšťali makra, klikneme pravým tlačidlom myši a povieme Prispôsobiť pásku. Tu na pravej strane začiarknite políčko pre vývojára, ktoré vám poskytne kartu Vývojár. Keď získate kartu Vývojár, môžeme prejsť na Zabezpečenie makier,štandardne to bude tu hore Zakázať všetky makrá a nehovor mi, že si zakázal celé makrá. Chcete prepnúť na druhú, tak pri otvorení súboru povieme: „Hej, sú tu makrá. Tieto ste vytvorili? Si s tým v poriadku? “ A môžete povedať: Povoliť makrá. V poriadku, kliknite na OK.
Teraz prejdeme na editor jazyka Visual Basic. Ak ste nikdy predtým nepoužívali Visual Basic, začnete s touto úplne šedou obrazovkou, prejdite do časti Zobraziť a Prieskumník projektu. Tu je zoznam všetkých otvorených zošitov. Takže mám doplnok Riešiteľ, môj osobný zošit s makrami a tu je zošit, na ktorom pracujem. Uistite sa, že je tento zošit vybratý, urobte Vložiť, Modul. Vložiť, Modul, tu získa pekné veľké prázdne biele plátno. Dobre, a potom zadáš tento kód. V poriadku, teraz tu používame objekt s názvom HPageBreak, vodorovný koniec stránky. A pretože to príliš nepoužívam, musel som to tu deklarovať ako premennú, ako objekt HPB, aby som tak mohol vidieť možnosti, ktoré mám v každej z nich k dispozícii. Dobre,zistiť, kde je dnes posledný riadok s údajmi, takže používam stĺpec A, idem na koniec stĺpca A - A1048576. Toto je L tu a nie 1, toto je L. Každý to sráči. L ako v programe Excel. Znie to ako Excel. Získať? Excel hore. Prejdite teda na A1048576, stlačte kláves Koniec a šípku hore, aby ste sa dostali do posledného riadku. Zistite, o aký riadok ide. A potom v stĺpcoch G a H, a ak to sledujete, musíte sa pozrieť na svoje údaje v programe Excel a zistiť, kde sú vaše dva nové stĺpce. Neviem, koľko máte stĺpcov. Možno sú vaše nové stĺpce v I a J, alebo sú v C a D. Neviem, príďte na to, kde sú, a všetky tieto riadky skryjeme, dobre. Takže v mojom prípade to začínalo od G6, to je prvé miesto, kde máme číslo,:H a potom spojím posledný riadok, ktorý dnes máme, pomocou číselného formátu troch bodkočiarok, ktorý skryje údaje.
Dobre, potom tento ďalší, tento ďalší som sa dozvedel z vývesky. Ak pred spustením tohto kódu neprepnete aktívne okno do režimu Náhľad na koniec stránky, tento kód nebude fungovať. Funguje to na niektoré zalomenia stránky, ale nie všetky zalomenia stránky, takže musíte dočasne zobraziť ich zalomenia. A potom slučka tu: Pre každú z nich je to moja objektová premenná - HPB In ActiveSheet.HPageBreaks. Zistiť posledný riadok, dobre? Takže pre tento objekt, pre koniec stránky, zistite umiestnenie, vypočítajte riadok. A toto je vlastne prvý riadok nasledujúcej stránky, takže od toho musím odrátať 1, v poriadku. A potom tu pripúšťam, že je to neuveriteľne lacné, choďte do stĺpca 7, čo je stĺpec G, zmeňte NumberFormat na menu, iba z tohto riadku. A potom choďte do stĺpca 8, ktorý je H a zmeňte to na percento a pokračujte ďalej.Nakoniec ukončite vodorovný náhľad alebo ukážku prerušenia stránky a vráťte sa do normálneho zobrazenia.
Dobre, tak to je náš kód. Uložím, zatvorím a vrátim sa do programu Microsoft Excel. Chcem jednoduchý spôsob, ako to spustiť, takže sa chystám vložiť, tu zvoliť pekný tvar. Vyberiem zaoblený obdĺžnik, nakreslím svoje pravé koliesko okolo, v časti Rozloženie stránky, prejdem na položku Efekty, vyberiem efekty pre Office 2007. A potom tu na karte Formát máme pekný spôsob, ako k tomu pridať trochu žiary .
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.
Och, chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2058.xlsm
