Vai Excel katras lapas kājenē var izdrukāt kopējo summu? Tas nav iebūvēts, bet īss makro atrisinās problēmu.
Skatīties video
- Mērķis: katras izdrukātās lapas apakšdaļā drukāt kategoriju kopā un% no kategorijas
- Problēma: nekas Excel lietotāja interfeisā nevar pateikt formulai, ka atrodaties drukātas lapas apakšdaļā
- Jā, jūs varat "redzēt" lapu pārtraukumus, bet formulas tos nevar redzēt
- Iespējamais risinājums: izmantojiet makro
- Stratēģija: pievienojiet katras rindas kopējo rādītāju un kategorijas%. Slēpt visās rindās.
- Darbojas kopā kategorijai Formula:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % no kategorijas formulas:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Ja jūsu darbgrāmata ir saglabāta kā XLSX, veiciet Saglabāt kā, lai saglabātu kā XLSM
- Ja jūs nekad neesat lietojis makro, mainiet makro drošību
- Ja nekad neesat lietojis makro, parādiet cilni Izstrādātājs
- Pārejiet uz VBA
- Ievietojiet moduli
- Ierakstiet kodu
- Piešķiriet šo makro formai
- Mainoties lapas izmēram, palaidiet atiestatīšanas makro
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2058. sērija: Kopējais rādītājs katras lapas beigās
Sveiki, sveicināti atkal netcast ēterā, es esmu Bils Jelens. Šodienas jautājums, ko iesūtīja Vilija: Vilija vēlas, lai katras izdrukātās lapas pēdējā rindā būtu redzams kopējais ieņēmumu kopums un kategorijas procentuālais daudzums. Tātad Wiley šeit ir izdrukājis pārskatus ar tonnām un tonnām ierakstu, vairākas lapas katrai kategorijai, tur A slejā. Un, kad mēs esam nonākuši līdz drukas lapas beigām, Wiley šeit meklē kopsummu, kas parāda kopējos ieņēmumus kopējais rādītājs šajā kategorijā un pēc tam kategorijas procentuālais daudzums. Tātad, jūs varat redzēt, ka mēs tur esam 9,7%, kad es eju uz 2. - 21.1., 3. - 33.3. Lapu un tā tālāk. Lappuses pārtraukumā, kur mēs paveicam ar A kategoriju, kategorijas kopējais kopsumma un 100% kopsumma. Labi, un, kad Vilija man jautāja par šo jautājumu, es biju kā: "Ak, nē, mēs to nedarām - tur"kājenē nekādā gadījumā nevar ievietot kopējo summu. ” Labi, tāpēc tas, protams, ir šausmīgs lēts krāpnieks, un es aicinu ikvienu, kurš to skatās vietnē YouTube, ja jums ir labāks veids, lūdzu, ar visiem līdzekļiem, pieminiet to komentāros, labi? Un tāpēc mana ideja ir tikai tur G un H kolonnās, lai katrā rindā paslēptu kopējo rādītāju un kategoriju procentuālo daudzumu. Labi, un pēc tam mēs izmantojam makro, lai noteiktu, vai esam lapas beigās.re lapas beigās.re lapas beigās.
Labi, tāpēc divas formulas, kuras mēs šeit vēlamies, saka: hei, ja šī kategorija ir vienāda ar iepriekšējo kategoriju. Tātad, ja A6 = A5, ņem šo ieņēmumu summu, tātad F6 un iepriekšējo kopējo summu G5. Tā kā es šeit izmantoju funkciju SUM, tas nekļūdās, ja mēs kādreiz mēģinātu pievienot kopējo rādītāju. Pretējā gadījumā mēs vienkārši nonāksim pie pavisam jaunas kategorijas, tāpēc, pārejot no A uz B, mēs vienkārši paņemsim vērtības SUM pa kreisi no mums, kuru es varētu tikko ievietot F6. Bet šeit mēs esam, jūs zināt, par vēlu. Un pēc tam kategorijas procentuālā daļa būs šausmīgi neefektīva. Šīs rindas ieņēmumus dalām ar visu ieņēmumu SUM, kur kategorija ir vienāda ar A6. Tātad šīs ir visas kategorijas,šī ir kategorija šajā rindā un pēc tam summējiet atbilstošo šūnu no visām rindām. Protams, $ zīmes - tur ir 1, 2, 3, 4 $ zīmes. A6 zīmē nav $ zīmju un tur nav 4 $ zīmju. Labi, un mēs parādīsim šo skaitli kā skaitli, varbūt 1000 atdalītāju, noklikšķiniet uz Labi un pēc tam šeit procentos ar vienu līdzīgu zīmi aiz komata. Labi, un mēs nokopēsim šo formulu visās šūnās. BAM, tāpat kā, labi. Bet tagad mērķis ir pārliecināties, ka šīs kopsummas mēs redzam tikai tad, kad nonākam līdz lapu pārtraukumam. Labi, tas ir tieši tur. Tas ir automātisks lapu pārtraukums un pēc tam vēlāk, kad mēs pārslēdzamies no A gala uz B, manuāla lapu pārtraukšana. Tātad, šis manuālais lapu pārtraukums šeit atšķiras no automātiskā lapu pārtraukuma.un mēs parādīsim šo skaitli kā skaitli, varbūt 1000 atdalītāju, noklikšķiniet uz Labi un pēc tam šeit procentos ar vienu līdzīgu zīmi aiz komata. Labi, un mēs nokopēsim šo formulu visās šūnās. BAM, tāpat kā, labi. Bet tagad mērķis ir pārliecināties, ka šīs kopsummas mēs redzam tikai tad, kad nonākam līdz lapu pārtraukumam. Labi, tas ir tieši tur. Tas ir automātisks lapu pārtraukums un pēc tam vēlāk, kad mēs pārslēdzamies no A gala uz B, manuāla lapu pārtraukšana. Tātad, šis manuālais lapu pārtraukums šeit atšķiras no automātiskā lapu pārtraukuma.un mēs parādīsim šo skaitli kā skaitli, varbūt 1000 atdalītāju, noklikšķiniet uz Labi un pēc tam šeit procentos ar vienu līdzīgu zīmi aiz komata. Labi, un mēs nokopēsim šo formulu visās šūnās. BAM, tāpat kā, labi. Bet tagad mērķis ir pārliecināties, ka šīs kopsummas mēs redzam tikai tad, kad nonākam līdz lapu pārtraukumam. Labi, tas ir tieši tur. Tas ir automātisks lapu pārtraukums un pēc tam vēlāk, kad mēs pārslēdzamies no A gala uz B, manuāla lapu pārtraukšana. Tātad, šis manuālais lapu pārtraukums šeit atšķiras no automātiskā lapu pārtraukuma.Bet tagad mērķis ir pārliecināties, ka šīs kopsummas mēs redzam tikai tad, kad nonākam līdz lapu pārtraukumam. Labi, tas ir tieši tur. Tas ir automātisks lapu pārtraukums un pēc tam vēlāk, kad mēs pārslēdzamies no A gala uz B, manuāla lapu pārtraukšana. Tātad, šis manuālais lapu pārtraukums šeit atšķiras no automātiskā lapu pārtraukuma.Bet tagad mērķis ir pārliecināties, ka šīs kopsummas mēs redzam tikai tad, kad nonākam līdz lapu pārtraukumam. Labi, tas ir tieši tur. Tas ir automātisks lapu pārtraukums un pēc tam vēlāk, kad mēs pārslēdzamies no A gala uz B, manuāla lapu pārtraukšana. Tātad, šis manuālais lapu pārtraukums šeit atšķiras no automātiskā lapu pārtraukuma.
Labi, tagad jūs pamanīsit šeit augšā, ka šis fails tiek saglabāts kā XLSX fails, jo šādi Excel vēlas saglabāt failus. XLSX ir bojāts faila tips, kas nepieļauj makro, vai ne? Sliktākais faila tips pasaulē. Tātad, neizlaidiet šo vai šo soli. Visi jūsu darbi no šejienes un ārpuses tiks zaudēti. Saglabāt kā, un mēs to saglabāsim nevis kā Excel darbgrāmatu, bet kā makro iespējotu darbgrāmatu vai kā bināru darbgrāmatu vai kā XLS. Es eju ar Macro-Enable Workbook. Ja jūs neveicat šo soli, jūs drīz zaudēsiet atlikušo darbu. Labi, un tad, ja jūs nekad iepriekš neesat palaidis makro, mēs ar peles labo pogu noklikšķiniet un teiksim Pielāgot lenti. Šeit labajā pusē atlasiet izvēles rūtiņu Izstrādātājam, lai iegūtu cilni Izstrādātājs. Kad būsit cilni Izstrādātājs, mēs varēsim pāriet uz Macro Security,pēc noklusējuma tas būs šeit. Atspējojiet visus makro un nesakiet man, ka esat atspējojis visus makro. Jūs vēlaties pāriet uz otro, tādā veidā, atverot failu, mēs teiksim: “Hei, šeit ir makro. Vai jūs tos izveidojāt? Vai jums viss ir kārtībā? ” Un jūs varat teikt: Iespējot makro. Labi, noklikšķiniet uz Labi.
Tagad mēs pāriet uz vizuālo pamata redaktoru. Ja nekad iepriekš neesat izmantojis vizuālo pamatprogrammu, sāksit ar šo pilnīgi pelēko ekrānu, dodieties uz skatu un Project Explorer. Šeit ir visu atvērto darbgrāmatu saraksts. Tāpēc man ir pievienojumprogramma Solver, mana personīgā makro darbgrāmata, un šeit ir darbgrāmata, pie kuras es strādāju. Pārliecinieties, vai ir atlasīta šī darbgrāmata, veiciet Insert, Module. Ievietojiet, modulis šeit iegūs jauku lielu tukšu, baltu audeklu. Labi, un tad jūs ierakstīsit šo kodu. Labi, mēs šeit izmantojam objektu ar nosaukumu HPageBreak - horizontālu lapu pārtraukumu. Tā kā es to daudz nelietoju, man tas bija jādeklarē šeit kā mainīgais, kā objekta HPB, tādā veidā es katrā no tiem varētu redzēt man pieejamās izvēles. Labi,noskaidrojiet, kur šodien ir pēdējā rinda ar datiem, tāpēc es izmantoju A kolonnu, dodos uz A slejas beigām - A1048576. Šeit ir L, nevis 1, tas ir L. Visi to ieskrūvē. L kā programmā Excel. Tas izklausās kā Excel. Dabūt to? Excel uz augšu. Tātad, dodieties uz A1048576, nospiediet taustiņu Beigt un augšupvērsto bultiņu, lai nokļūtu pēdējā rindā. Izdomājiet, kas tā ir par rindu. Pēc tam G un H kolonnās, un, ja jūs to skatāties, jums ir jāaplūko jūsu Excel dati un jānoskaidro, kur atrodas jūsu abas jaunās kolonnas. Es nezinu, cik jums ir sleju. Varbūt jūsu jaunās kolonnas ir beigušās I un J, vai varbūt tās ir C un D. Es nezinu, izdomājiet, kur tās atrodas, un mēs labi slēpsim visas šīs rindas. Tāpēc manā gadījumā tas sākās no G6, tā ir pirmā vieta, kur mums ir numurs:H, un pēc tam es savienoju pēdējo mūsu pašreizējo rindu, izmantojot trīs semikolu skaitļu formātu, kas paslēps datus.
Labi, tad šis nākamais, es uzzināju šo nākamo no ziņojumu dēļa. Ja pirms šī koda palaišanas neesat ievietojis aktīvo logu lapas pārtraukuma priekšskatījuma režīmā, šis kods nedarbosies. Tas darbojas dažos lappušu pārtraukumos, bet ne visos lappušu pārtraukumos, tāpēc jums ir īslaicīgi jāparāda lappušu pārtraukumi. Un pēc tam cilpa šeit: Katram tas ir mans objekta mainīgais - HPB ActiveSheet.HPageBreaks. Izdomā pēdējo rindu, labi? Tātad šim objektam, lapas pārtraukumam, izdomājiet vietu, izdomājiet rindu. Un šī faktiski ir nākamās lapas pirmā rinda, tāpēc man no tā ir jāatņem 1, labi. Un tad šeit, es atzīstu, ka tas ir neticami lēti, dodieties uz 7. sleju, kas ir G sleja, nomainiet NumberFormat kā valūtu, tikai šajā rindā. Pēc tam dodieties uz 8. kolonnu, kas ir H, nomainiet to procentos un dodieties tālāk.Visbeidzot, izejiet no horizontālā vai lapas pārtraukuma priekšskatījuma un atgriezieties normālā skatā.
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.
Ak, hei, es gribu pateikties, ka apstājies. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2058.xlsm
