Ali lahko Excel v nogi za vsako stran natisne tekoči seštevek? Ni vgrajen, vendar bo kratek makro rešil težavo.
Oglejte si video
- Cilj: Natisnite skupno kategorijo in% kategorije na dnu vsake natisnjene strani
- Težava: nič v Excelovem uporabniškem vmesniku ne more dati formuli vedeti, da ste na dnu natisnjene strani
- Da, prelome strani lahko "vidite", formule pa jih ne
- Možna rešitev: Uporabite makro
- Strategija: dodajte tekoči seštevek in% kategorije za vsako vrstico. Skrij v vseh vrsticah.
- Tekoči seštevek za Formulo kategorije:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % formule kategorije:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Če je vaš delovni zvezek shranjen kot XLSX, naredite Shrani kot, da shranite kot XLSM
- Če še nikoli niste uporabljali makrov, spremenite varnost makrov
- Če še nikoli niste uporabljali makrov, pokažite zavihek Razvijalec
- Preklopite na VBA
- Vstavite modul
- Vnesite kodo
- Dodelite ta makro obliki
- Ko se velikost strani spremeni, zaženite ponastavitveni makro
Video zapis
Naučite se Excela iz podcasta, epizoda 2058: Skupaj na koncu vsake strani
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje, ki ga je poslal Wiley: Wiley želi v zadnji vrstici vsake natisnjene strani prikazati tekoči skupni prihodek in odstotek kategorije. Torej, Wiley je tukaj natisnil poročila s tonami in tonami zapisov, več strani za vsako kategorijo tam v stolpcu A. In ko pridemo do konca strani za tiskanje, Wiley tukaj išče skupni znesek, ki prikazuje skupni prihodek, skupno število znotraj te kategorije in nato odstotek kategorije. In tako lahko vidite, da smo tam 9,7%, ko grem na stran 2 - 21.1, stran 3 - 33.3 itd. In na prelomu strani, kjer končamo s kategorijo A, skupni seštevek za kategorijo in skupni seštevek 100%. V redu, in ko me je Wiley vprašal o tem, sem rekel: "Oh ne, mi ne … tam"v nogo ni mogoče postaviti tekočega seštevka. " V redu, to je res grozno poceni goljufija in spodbujam vse, ki to gledajo v YouTubu, če imate boljši način, vsekakor to navedite v komentarjih, v redu? Torej, moja ideja je ravno v stolpcih G in H, da v vsaki vrstici skrijem tekoči seštevek in odstotek kategorije. V redu, nato pa z makrom zaznamo, ali smo na koncu strani.na koncu strani.na koncu strani.
V redu, torej dve formuli, ki ju želimo tukaj, pravita, hej, če je ta kategorija enaka prejšnji kategoriji. Torej, če je A6 = A5, potem vzemite SUM tega prihodka, torej je to v F6 in prejšnji skupni znesek tam zgoraj v G5. Ker zdaj uporabljam funkcijo SUM, to ne povzroči napake, če bi kdaj poskusili dodati tekoče skupno. V nasprotnem primeru bomo le v povsem novi kategoriji, zato bomo, ko preklopimo z A na B, le vzeli SUM vrednosti levo od nas, kar bi lahko pravkar postavil tja F6. Ampak tu smo, veste, prepozni. In potem odstotek kategorije, ta bo strašno neučinkovit. Prihodek v tej vrstici jemljemo z vsoto vseh prihodkov, kadar je kategorija enaka A6. To so vse kategorije,to je kategorija v tej vrstici, nato pa dodajte ustrezno celico iz vseh vrstic. Seveda znaki $ - tam so znaki 1, 2, 3, 4 $. V A6 ni nobenih znakov $ in noter 4 $. V redu in to številko bomo prikazali kot ločilo Število, morda 1000, kliknemo V redu in nato tukaj v odstotkih z enim takim decimalnim mestom. V redu in kopirali bomo to formulo v vse celice. BAM, takole, v redu. Zdaj pa je cilj tukaj zagotoviti, da te vsote vidimo šele, ko pridemo do preloma strani. Prav, tam je. To je samodejni prelom strani in nato kasneje, ko preklopimo s konca A na B, ročni prelom strani. Ta ročni prelom strani se torej razlikuje od samodejnega preloma strani.in to številko bomo prikazali kot ločilo Število, morda 1000, kliknemo V redu in nato tukaj v odstotkih z enim takim decimalnim mestom. V redu in kopirali bomo to formulo v vse celice. BAM, takole, v redu. Zdaj pa je cilj tukaj zagotoviti, da te vsote vidimo šele, ko pridemo do preloma strani. Prav, tam je. To je samodejni prelom strani in nato kasneje, ko preklopimo s konca A na B, ročni prelom strani. Ta ročni prelom strani se torej razlikuje od samodejnega preloma strani.in to številko bomo prikazali kot ločilo Število, morda 1000, kliknite V redu in nato tukaj v odstotkih z enim takim decimalnim mestom. V redu in kopirali bomo to formulo v vse celice. BAM, takole, v redu. Zdaj pa je cilj tukaj zagotoviti, da te vsote vidimo šele, ko pridemo do preloma strani. Prav, tam je. To je samodejni prelom strani in nato kasneje, ko preklopimo s konca A na B, ročni prelom strani. Ta ročni prelom strani se torej razlikuje od samodejnega preloma strani.Zdaj pa je cilj tukaj zagotoviti, da te vsote vidimo šele, ko pridemo do preloma strani. Prav, tam je. To je samodejni prelom strani in nato kasneje, ko preklopimo s konca A na B, ročni prelom strani. Ta ročni prelom strani se torej razlikuje od samodejnega preloma strani.Zdaj pa je cilj tukaj zagotoviti, da te vsote vidimo šele, ko pridemo do preloma strani. Prav, tam je. To je samodejni prelom strani in nato kasneje, ko preklopimo s konca A na B, ročni prelom strani. Ta ročni prelom strani se torej razlikuje od samodejnega preloma strani.
V redu, zdaj boste tukaj opazili, da je ta datoteka shranjena kot datoteka XLSX, ker tako Excel želi shraniti datoteke. XLSX je pokvarjena vrsta datoteke, ki ne dovoljuje makrov, kajne? Najslabša vrsta datoteke na svetu. Torej, ne preskočite tega ali tega koraka. Vse vaše delo od tu in zunaj bo izgubljeno. Shrani kot in ne bomo shranili kot Excelov delovni zvezek, temveč kot Macro-omogočeni delovni zvezek ali kot binarni delovni zvezek ali kot XLS. Šel bom z delovnim zvezkom z omogočeno makro. Če tega ne storite, boste kmalu izgubili preostalo delo, ki ste ga opravili. V redu in potem, če še nikoli niste zagnali makrov, bomo z desno miškino tipko kliknili in prilagodili trak. Tukaj na desni strani izberite polje za razvijalce, ki bo odprlo zavihek za razvijalce. Ko imate zavihek Developer, lahko odpremo Macro Security,privzeto bo tukaj zgoraj Onemogoči vse makre in ne govori mi, da si onemogočil celotne makre. Če želite preklopiti na drugega, bomo, ko bomo odprli datoteko, rekli: »Hej, tukaj so makri. Ste jih ustvarili? Si v redu s tem? " In lahko rečete: Omogoči makre. V redu, kliknite V redu.
Zdaj bomo prešli na urejevalnik visual basic. Če še nikoli niste uporabljali Visual Basic, boste začeli s tem popolnoma sivim zaslonom, pojdite na Pogled in Raziskovalec projektov. Tu je seznam vseh odprtih delovnih zvezkov. Tako imam dodatek Solver, svoj osebni delovni zvezek za makro in tukaj je delovni zvezek, na katerem delam. Prepričajte se, da je ta delovni zvezek izbran, naredite Vstavi, modul. Vstavi, Modul bo tu dobil lepo veliko prazno, belo platno. V redu, potem boste vtipkali to kodo. Zdaj uporabljamo objekt, imenovan HPageBreak, vodoravni prelom strani. In ker tega ne uporabljam veliko, sem ga moral tu zgoraj prijaviti kot spremenljivko, kot objektni HPB, tako da bom lahko videl izbire, ki so mi na voljo v vsaki. Vredu,ugotovite, kje je danes zadnja vrstica s podatki, zato uporabljam stolpec A, grem na konec stolpca A - A1048576. Tu je L in ne 1, to je L. Vsi to zajebajo. L kot v Excelu. Sliši se kot Excel. Razumeš? Excel gor. Torej, pojdite na A1048576, pritisnite tipko Konec in puščico gor, da pridete do zadnje vrstice. Ugotovite, katera vrstica je to. In nato v stolpcih G in H, in če to gledate, si morate ogledati podatke v Excelu in ugotoviti, kje sta nova dva stolpca. Ne vem, koliko stolpcev imate. Mogoče so vaši novi stolpci končani v I in J, ali pa v C in D. Ne vem, ugotovi, kje so, in vse te vrstice bomo skrili, prav. Torej, v mojem primeru se je začelo od G6, to je prvo mesto, kjer imamo številko:H in nato združujem zadnjo vrstico, ki jo imamo danes, z uporabo številčne oblike treh podpičkov, ki bodo skrivali podatke.
V redu, potem naslednji, tega sem se naučil na oglasni deski. Če aktivnega okna ne postavite v način predogleda Page Break, preden zaženete to kodo, ta koda ne bo delovala. Deluje pri nekaterih prelomih strani, vendar ne pri vseh prelomih strani, zato morate prelome strani začasno prikazati. In potem zanka tukaj: Za vsakega je to moja objektna spremenljivka - HPB v ActiveSheet.HPageBreaks. Ugotovite zadnjo vrstico, v redu? Torej, za ta predmet, za prelom strani, ugotovite lokacijo in vrstico. In to je pravzaprav prva vrstica naslednje strani, zato moram od tega odšteti 1, v redu. In potem tukaj, priznam, da je to neverjetno poceni, pojdite v stolpec 7, ki je stolpec G, spremenite NumberFormat v valuto, samo iz te vrstice. Nato pojdite na stolpec 8, ki je H, in ga spremenite v odstotek in pojdite na naslednjo.Končno zapustite vodoravni predogled ali predogled preloma strani in se vrnite v običajni pogled.
Torej, to je naša koda. Datoteko, zapiranje in vrnitev v Microsoft Excel. Želim preprost način za zagon tega, zato grem v Vstavi, tukaj izberite lepo obliko. Izbral bom zaobljeni pravokotnik, narisal svojo desno - okrog pravokotnika v, Postavitev strani, pojdite na Učinki, izberite učinke za Office 2007. In potem tukaj na zavihku Oblika imamo lep način, da temu dodamo nekaj sijaja, v redu .
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.
O, hej, rad bi se vam zahvalil, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2058.xlsm
