Ar „Excel“ gali atspausdinti kiekvieno puslapio poraštėje rodomą bendrą sumą? Tai nėra įmontuota, tačiau problemą išspręs trumpas makrokomandas.
Žiūrėti video
- Tikslas: Spausdinti kategoriją iš viso ir kategorijos procentą kiekvieno spausdinto puslapio apačioje
- Problema: „Excel“ vartotojo sąsajoje niekas negali pranešti formulei, kad esate atspausdinto puslapio apačioje
- Taip, galite „pamatyti“ puslapio pertraukas, bet formulės jų nemato
- Galimas sprendimas: naudokite makrokomandą
- Strategija: pridėkite kiekvienos eilutės bendrą sumą ir kategorijos procentą. Slėpti visose eilutėse.
- Veikia iš viso kategorijos formulėje:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % kategorijos formulės:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Jei jūsų darbaknygė išsaugota kaip XLSX, atlikite „Išsaugoti kaip“, kad išsaugotumėte kaip XLSM
- Jei niekada nenaudojote makrokomandų, pakeiskite makrokomandų saugumą
- Jei niekada nenaudojote makrokomandų, rodykite skirtuką Kūrėjas
- Perjunkite į VBA
- Įdėkite modulį
- Įveskite kodą
- Priskirkite tą makrokomandą formai
- Kai keičiasi puslapio dydis, vykdykite makrokomandą iš naujo
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2058 serija: Viso rodymas kiekvieno puslapio pabaigoje
Ei, sveiki sugrįžę į internetinę transliaciją, aš Billas Jelenas. Šiandienos Wiley atsiųstas klausimas: Wiley nori parodyti einamąsias bendras pajamas ir kategorijos procentus paskutinėje kiekvieno atspausdinto puslapio eilutėje. Taigi, Wiley čia atspausdino ataskaitas su tonomis ir tonomis įrašų, po kelis puslapius kiekvienai kategorijai, esančiai A stulpelyje. Ir kai mes pateksime į spausdinimo puslapio pabaigą, Wiley čia ieškos bendros sumos, rodančios visas pajamas, šioje kategorijoje, o tada kategorijos procentais. Taigi, matote, kad ten yra 9,7%, kai einu į 2 - 21.1, 3 - 33.3 ir pan. Puslapius. Puslapio pertraukoje, kur mes darome su A kategorija, bendras šios kategorijos ir 100% bendras rezultatas. Gerai, ir kai Wiley manęs apie tai paklausė, aš buvau tokia: „O ne, mes ne - ten“jokiu būdu negalima poraštėje sudaryti bėgimo sumos “. Gerai, taigi tai, be abejo, siaubingas pigus apgaulė, ir aš raginu visus, kurie tai žiūri „YouTube“, jei turite geresnį būdą, prašau visais būdais, paminėkite tai komentaruose, gerai? Taigi mano idėja yra tiesiog G ir H stulpeliuose, kad kiekvienoje eilutėje būtų paslėpta bendra suma ir kategorijos procentas. Gerai, tada mes naudojame makrokomandą, kad nustatytume, ar esame puslapio pabaigoje.vėl puslapio pabaigoje.vėl puslapio pabaigoje.
Gerai, todėl dvi norimos formulės sako: ei, jei ši kategorija yra lygi ankstesnei kategorijai. Taigi, jei A6 = A5, paimkite šių pajamų sumą, taigi tai yra F6, o ankstesnė suma ten buvo G5. Kadangi aš čia naudoju SUM funkciją, tai nepadaro klaidos, jei mes kada nors bandytume pridėti bendrą rodiklį. Priešingu atveju mes tiesiog būsime visiškai naujoje kategorijoje, taigi, kai pereisime iš A į B, mes tiesiog paimsime SUM vertę kairėje iš mūsų, kurią aš galėjau tiesiog įdėti F6. Bet štai mes, žinot, per vėlai. Tada kategorijos procentas bus siaubingai neefektyvus. Šios eilutės pajamas padalijame iš visų pajamų SUM, jei kategorija lygi A6. Taigi tai visos kategorijos,tai yra šios eilutės kategorija, tada sudėkite atitinkamą langelį iš visų eilučių. Žinoma, $ ženklai - 1, 2, 3, 4 $ ženklai ten. Nr A6 ženklų A6 ir 4 $ ženklų ten. Gerai, ir mes parodysime šį skaičių kaip skaičių, o gal ir 1000 skiriklį, spustelėkite Gerai, tada čia procentais su viena dešimtųjų tikslumu. Gerai, ir mes nukopijuosime šią formulę žemyn į visus langelius. BAM, gerai, gerai. Bet dabar tikslas yra įsitikinti, kad tas sumas matome tik patekę į puslapio pertrauką. Gerai, čia pat. Tai yra automatinis puslapio pertraukimas, o vėliau, kai pereisime nuo A galo į B, rankinis puslapio pertraukimas. Taigi, ši rankinė puslapio pertrauka skiriasi nuo automatinio puslapio pertraukimo.ir mes parodysime šį skaičių kaip skaičių, galbūt 1000 skiriklį, spustelėkite Gerai, tada čia procentais su viena dešimtųjų tikslumu. Gerai, ir mes nukopijuosime šią formulę žemyn į visus langelius. BAM, gerai, gerai. Bet dabar tikslas yra įsitikinti, kad tas sumas matome tik patekę į puslapio pertrauką. Gerai, jis čia pat. Tai yra automatinė puslapio pertrauka, o vėliau, kai pereisime nuo A galo į B, rankinis puslapio pertraukimas. Taigi, ši rankinė puslapio pertrauka skiriasi nuo automatinio puslapio pertraukimo.ir mes parodysime šį skaičių kaip skaičių, galbūt 1000 skiriklį, spustelėkite Gerai, tada čia procentais su viena dešimtųjų tikslumu. Gerai, ir mes nukopijuosime šią formulę žemyn į visus langelius. BAM, gerai, gerai. Bet dabar tikslas yra įsitikinti, kad tas sumas matome tik patekę į puslapio pertrauką. Gerai, čia pat. Tai yra automatinė puslapio pertrauka, o vėliau, kai pereisime nuo A galo į B, rankinis puslapio pertraukimas. Taigi, ši rankinė puslapio pertrauka skiriasi nuo automatinio puslapio pertraukimo.Bet dabar tikslas yra įsitikinti, kad tas sumas matome tik patekę į puslapio pertrauką. Gerai, čia pat. Tai yra automatinė puslapio pertrauka, o vėliau, kai pereisime nuo A galo į B, rankinis puslapio pertraukimas. Taigi, ši rankinė puslapio pertrauka skiriasi nuo automatinio puslapio pertraukimo.Bet dabar tikslas yra įsitikinti, kad tas sumas matome tik patekę į puslapio pertrauką. Gerai, čia pat. Tai yra automatinis puslapio pertraukimas, o vėliau, kai pereisime nuo A galo į B, rankinis puslapio pertraukimas. Taigi, ši rankinė puslapio pertrauka skiriasi nuo automatinio puslapio pertraukimo.
Gerai, dabar čia pastebėsite, kad šis failas yra išsaugotas kaip XLSX failas, nes taip „Excel“ nori išsaugoti failus. XLSX yra sugedęs failo tipas, kuris neleidžia makrokomandų, tiesa? Blogiausias failo tipas pasaulyje. Taigi, nepraleiskite šio ar šio žingsnio. Visi jūsų darbai iš čia ir atgal bus prarasti. Išsaugoti kaip, mes išsaugosime ne kaip „Excel“, o kaip „Macro“ įgalintą, arba kaip dvejetainę, arba kaip XLS darbaknygę. Aš eisiu su makrokomandomis įgalinta darbaknygė. Jei to nepadarysite, prarasite likusį atliktą darbą. Gerai, tada, jei dar niekada neveikėte makrokomandų, dešiniuoju pelės mygtuku spustelėkite ir pasakysime Tinkinti juostelę. Dešinėje pusėje pasirinkite „Developer“ langelį, kad gautumėte „Developer“ skirtuką. Kai turėsite skirtuką Kūrėjas, galime pereiti į „Macro Security“,pagal numatytuosius nustatymus tai bus čia. Išjunkite visas makrokomandas ir nesakykite, kad išjungėte visas makrokomandas. Norite pereiti prie antrojo, tokiu būdu atidarę failą pasakysime: „Ei, čia yra makrokomandų. Ar jūs juos sukūrėte? Ar tau viskas gerai? “ Ir jūs galite pasakyti: Įjunkite makrokomandas. Gerai, spustelėkite Gerai.
Dabar pereisime prie „Visual Basic“ redaktoriaus. Jei dar niekada nenaudojote „Visual Basic“, pradėsite nuo šio visiškai pilko ekrano, eikite į „View“ ir „Project Explorer“. Čia pateikiamas visų atidarytų darbaknygių sąrašas. Taigi turiu „Solver“ priedą, savo asmeninę makrokomandų knygelę, o štai - darbaknygę, su kuria dirbu. Įsitikinkite, kad pasirinkta ši darbaknygė, atlikite „Insert“, „Module“. Įterpti, modulis gaus gražų didelį tuščią, baltą drobę. Gerai, tada jūs ketinate įvesti šį kodą. Gerai, dabar mes naudojame objektą, vadinamą „HPageBreak“, horizontaliu puslapio pertraukimu. Kadangi aš to daug nenaudoju, turėjau tai deklaruoti čia kaip kintamąjį, kaip objekto HPB, kad galėčiau kiekviename pamatyti pasirinkimus, kurie man prieinami. Gerai,išsiaiškink, kur šiandien yra paskutinė eilutė su duomenimis, todėl naudoju A stulpelį, einu į A stulpelio pabaigą - A1048576. Čia yra L, o ne 1, tai L. Visi tai užsuka. L kaip „Excel“. Tai skamba kaip „Excel“. Gauti? „Excel“. Taigi eikite į A1048576, paspauskite klavišą Pabaiga ir rodyklę aukštyn, kad patektumėte į paskutinę eilutę. Išsiaiškinkite, kokia tai eilutė. Tada G ir H stulpeliuose, o jei tai žiūrite, turite pažvelgti į savo „Excel“ duomenis ir išsiaiškinti, kur yra jūsų nauji stulpeliai. Nežinau, kiek turite stulpelių. Galbūt jūsų nauji stulpeliai pasibaigė aš ir J, o gal jie yra C ir D. Aš nežinau, supraskite, kur jie yra, ir mes gerai paslėpsime visas tas eilutes. Taigi, mano atveju, tai prasidėjo nuo G6, tai yra pirmoji vieta, kur turime numerį:H ir tada aš sujungiu paskutinę eilutę, kurią turime šiandien, naudodamas trijų kabliataškių skaičių formatą, kuris paslėps duomenis.
Gerai, tada šis kitas, aš sužinojau šį kitą iš pranešimų lentos. Jei prieš paleidžiant šį kodą nepadėsite aktyvaus lango į puslapio pertraukos peržiūros režimą, šis kodas neveiks. Tai veikia kai kuriose puslapių pertraukose, bet ne visose puslapių pertraukose, todėl turite laikinai rodyti puslapių pertraukas. Tada kilpa čia: Kiekvienam tai yra mano objekto kintamasis - HPB „ActiveSheet.HPageBreaks“. Išsiaiškink paskutinę eilutę, gerai? Taigi šiam objektui, puslapio pertraukai, išsiaiškinkite vietą, išsiaiškinkite eilutę. Tai iš tikrųjų yra pirmoji kito puslapio eilutė, todėl turiu iš jų atimti 1, gerai. Ir tada, aš pripažįstu, kad tai nepaprastai pigu, eikite į 7 stulpelį, kuris yra G stulpelis, pakeiskite „NumberFormat“ į valiutą, tik tos eilutės. Tada eikite į 8 stulpelį, kuris yra H, pakeiskite jį į procentą ir eikite toliau.Galiausiai išeikite iš horizontalios arba puslapio pertraukos peržiūros ir grįžkite į įprastą rodinį.
Gerai, taigi toks yra mūsų kodas. Aš padarysiu failą, uždarysiu ir grįšiu į „Microsoft Excel“. Noriu paprasto būdo tai paleisti, todėl eisiu įterpti, pasirinkti čia gražią formą. Pasirinksiu suapvalintą stačiakampį, piešsiu dešinįjį kampą aplink stačiakampį, „Puslapio išdėstymas“, eikite į „Efektai“, pasirinkite „Office 2007“ efektus. Tada čia, skirtuke Formatas, turime gerą būdą pridėti tam tikrą švytėjimą .
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.
Ei, noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2058.xlsm
