Može li Excel ispisati ukupni iznos u podnožju za svaku stranicu? Nije ugrađen, ali kratka makronaredba će riješiti problem.
Gledaj video
- Cilj: Ispis ukupne kategorije i% kategorije na dnu svake ispisane stranice
- Problem: ništa u Excelovom korisničkom sučelju ne može formuli znati da ste na dnu ispisane stranice
- Da, možete "vidjeti" prijelome stranice, ali formule ih ne mogu vidjeti
- Moguće rješenje: Upotrijebite makronaredbu
- Strategija: dodajte ukupni iznos i% kategorije za svaki redak. Sakrij na svim redovima.
- Ukupno trčanje za formulu kategorije:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % formule kategorije:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Ako je vaša radna knjiga spremljena kao XLSX, napravite Spremi kao da biste spremili kao XLSM
- Ako nikada niste koristili makronaredbe, promijenite sigurnost makronaredbi
- Ako nikada niste koristili makronaredbe, otvorite karticu Developer
- Prebacite se na VBA
- Umetnite modul
- Upišite kod
- Dodijelite tu makronaredbu obliku
- Kako se veličina stranice mijenja, pokrenite makronaredbu za poništavanje
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2058: Ukupno prikazivanje na kraju svake stranice
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje koje je poslao Wiley: Wiley želi prikazati ukupni prihod i postotak kategorije u zadnjem redu svake ispisane stranice. Dakle, Wiley je ovdje tiskao izvješća s tone i tone zapisa, više stranica za svaku kategoriju tamo u stupcu A. A kad se spustimo na kraj stranice za ispis, Wiley ovdje traži ukupan iznos koji pokazuje ukupan prihod, ukupno prikazivanje unutar ove kategorije, a zatim postotak kategorije. I tako, možete vidjeti da smo tamo 9,7%, kad odem na stranicu 2 - 21.1, stranicu 3 - 33.3 i tako dalje. I na prijelomu stranice gdje ćemo završiti s kategorijom A, ukupni zbroj za kategoriju i ukupni zbroj 100%. U redu, i kad me Wiley pitao o ovome, rekao sam, "Ma ne, mi ne … tamo"u podnožje nema načina da se stavi tekući zbroj. " Dobro, ovo je doduše užasna jeftina prevara i potičem svakoga tko ovo gleda na YouTubeu, ako imate bolji način, molim vas, svakako to spomenite u komentarima, u redu? Dakle, moja ideja je upravo tamo u stupcima G i H, da sakrijem ukupni broj i postotak kategorije u svakom pojedinom retku. U redu, a zatim koristimo makronaredbu kako bismo otkrili jesmo li na kraju stranice.na kraju stranice.na kraju stranice.
Dobro, tako dvije formule koje ovdje želimo kažu, hej, ako je ova kategorija jednaka prethodnoj kategoriji. Dakle, ako je A6 = A5, uzmite zbroj ovog prihoda, tako da je to u F6, a prethodni ukupni ukupan rezultat tamo u G5. Budući da ovdje koristim funkciju SUM, ovo ne izbacuje iz pogreške ako bismo ikad pokušali dodati zbrojni zbroj. U suprotnom, jednostavno ćemo biti u potpuno novoj kategoriji, pa kad pređemo s A na B, uzet ćemo samo ZBROJ vrijednosti lijevo od nas, a mogao sam tamo staviti F6. Ali evo nas, znate, prekasno. A onda postotak kategorije, ovaj će biti užasno neučinkovit. U ovom retku uzimamo prihod podijeljen sa SUM-om svih prihoda gdje je kategorija jednaka A6. To su sve kategorije,ovo je kategorija u ovom retku, a zatim zbrojite odgovarajuću ćeliju iz svih redaka. Naravno, znakovi $ - tamo su znakovi 1, 2, 3, 4 $. Nema znakova $ u A6 i 4 $ tamo. U redu, i taj ćemo broj prikazati kao separator Broj, možda 1000, kliknite U redu, a zatim ovdje kao postotak s jednom takvom decimalom. Dobro, kopirat ćemo ovu formulu u sve stanice. BAM, onako, u redu. Ali sada je cilj ovdje osigurati da te ukupne rezultate vidimo tek kad dođemo do prijeloma stranice. Dobro, tamo je. To je automatsko prijelom stranice, a kasnije kada prijeđemo s kraja A na B, ručni prijelaz stranice. Dakle, ovaj ručni prijelaz stranice ovdje se razlikuje od automatskog prijeloma stranice.i taj ćemo broj prikazati kao separator Broj, možda 1000, kliknite U redu, a zatim ovdje kao postotak s jednom takvom decimalom. Dobro, kopirat ćemo ovu formulu u sve stanice. BAM, onako, u redu. Ali sada je cilj ovdje osigurati da te ukupne rezultate vidimo tek kad dođemo do prijeloma stranice. Dobro, tamo je. To je automatsko prijelom stranice, a kasnije kada prijeđemo s kraja A na B, ručni prijelaz stranice. Dakle, ovaj ručni prijelaz stranice ovdje se razlikuje od automatskog prijeloma stranice.i taj ćemo broj prikazati kao separator Broj, možda 1000, kliknite U redu, a zatim ovdje kao postotak s jednom takvom decimalom. Dobro, kopirat ćemo ovu formulu u sve stanice. BAM, onako, u redu. Ali sada je cilj ovdje osigurati da te ukupe vidimo tek kad dođemo do prijeloma stranice. Dobro, tamo je. To je automatsko prijelom stranice, a kasnije kada prijeđemo s kraja A na B, ručni prijelaz stranice. Dakle, ovaj ručni prijelaz stranice ovdje se razlikuje od automatskog prijeloma stranice.Ali sada je cilj ovdje osigurati da te ukupne rezultate vidimo tek kad dođemo do prijeloma stranice. Dobro, tamo je. To je automatsko prijelom stranice, a kasnije kada prijeđemo s kraja A na B, ručni prijelaz stranice. Dakle, ovaj ručni prijelaz stranice ovdje se razlikuje od automatskog prijeloma stranice.Ali sada je cilj ovdje osigurati da te ukupe vidimo tek kad dođemo do prijeloma stranice. Dobro, tamo je. To je automatsko prijelom stranice, a kasnije kada prijeđemo s kraja A na B, ručni prijelaz stranice. Dakle, ovaj ručni prijelaz stranice ovdje se razlikuje od automatskog prijeloma stranice.
Dobro, sad ćete ovdje primijetiti da je ova datoteka spremljena kao XLSX datoteka jer Excel tako želi spremiti datoteke. XLSX je slomljena vrsta datoteke koja ne dopušta makronaredbe, zar ne? Najgora vrsta datoteke na svijetu. Dakle, nemojte preskočiti ovaj ili ovaj korak. Sav vaš rad odavde i vani bit će izgubljen. Spremi kao i spremit ćemo ne kao Excel radnu knjigu već kao radnu knjigu s omogućenim makronaredbama ili kao binarnu radnu knjigu ili kao XLS. Idem s makronaredenom radnom knjigom. Ako ne napravite taj korak, izgubit ćete ostatak posla koji radite. U redu, a onda ćemo, ako nikada prije niste pokrenuli makronaredbe, kliknuti desnom tipkom miša i reći Prilagodi vrpcu. Ovdje s desne strane odaberite okvir za Developer koji će vam otvoriti karticu Developer. Nakon što imate karticu Developer, možemo prijeći na Macro Security,po defaultu će biti ovdje gore Onemogućite sve makronaredbe i nemojte mi reći da ste onemogućili cijele makronaredbe. Želite se prebaciti na drugu, na taj način, kad otvorimo datoteku, reći ćemo: „Hej, ovdje su makronaredbe. Jeste li ih stvorili? Jesi li dobro s ovim? " Možete reći: Omogućite makronaredbe. U redu, kliknite U redu.
Sada ćemo prijeći na uređivač visual basic. Ako nikada prije niste koristili Visual Basic, počet ćete s ovim potpuno sivim zaslonom, idite na View i Project Explorer. Evo popisa svih otvorenih radnih knjiga. Dakle, imam dodatak Solver, svoju osobnu makro radnu knjigu i evo radne knjige na kojoj radim. Obavezno odaberite ovu radnu knjigu, napravite Umetanje, modul. Umetnite, Modul će ovdje dobiti lijepo veliko prazno, bijelo platno. U redu, a onda ćete upisati ovaj kod. U redu, ovdje koristimo objekt koji se naziva HPageBreak, vodoravni prijelom stranice. A budući da ovo ne koristim puno, morao sam ga ovdje prijaviti kao varijablu, kao objektni HPB, na taj način mogao bih vidjeti izbore koji su mi dostupni u svakom. U redu,shvatite gdje je danas zadnji redak s podacima, pa koristim stupac A, idem do kraja stupca A - A1048576. Ovo je ovdje L, a ne 1, ovo je L. Svi to zeznu. L kao u Excelu. Zvuči kao Excel. Shvatili ste? Excel gore. Dakle, idite na A1048576, pritisnite tipku Kraj i tipku Strelica gore da biste došli do posljednjeg reda. Otkrijte koji je to red. A onda u stupcima G i H i ako ovo gledate, morate pogledati svoje Excel podatke i shvatiti gdje su vaša dva nova stupca, u redu. Ne znam koliko kolona imate. Možda su vaši novi stupci završeni u I i J, ili su možda u C i D. Ne znam, shvatite gdje su to i sakrit ćemo sve te retke, u redu. Dakle, u mom slučaju je počelo od G6, to je prvo mjesto na kojem imamo broj:H, a zatim spajam posljednji redak koji imamo danas koristeći format broja od tri zarez koji će sakriti podatke.
Dobro, onda ovaj sljedeći, sljedeći sam naučio na oglasnoj ploči. Ako aktivni prozor ne stavite u način pregleda prijeloma stranice prije pokretanja ovog koda, ovaj kôd neće raditi. Radi za neke prijelome stranice, ali ne i za sve prijelome, tako da morate privremeno prikazati prijelome stranice. I onda petlja ovdje: Za svaku je ovo moja varijabla objekta - HPB u ActiveSheet.HPageBreaks. Shvati zadnji red, u redu? Dakle, za ovaj objekt, za prijelom stranice, shvatite mjesto i red. A ovo je zapravo prvi red sljedeće stranice pa moram oduzeti 1 od toga, u redu. A onda ovdje, priznajem da je to nevjerojatno jeftino, izađite na stupac 7 koji je stupac G, promijenite format broja u valutu, samo iz tog reda. A zatim izađite na stupac 8 koji je H i promijenite ga u postotak i idite dalje.Napokon, izađite iz vodoravnog prikaza ili pregleda prijeloma stranice i vratite se u uobičajeni prikaz.
U redu, to je naš kod. Datoteku ću zatvoriti i vratiti u Microsoft Excel. Želim jednostavan način da ovo pokrenem, pa idem na Insert, ovdje odaberite lijep oblik. Odabrat ću zaobljeni pravokutnik, nacrtati svoj desni - oko pravokutnika u, Izgled stranice, otići na Učinci, odabrati efekte za Office 2007. A onda ovdje na kartici Oblik imamo lijep način da tome dodamo malo sjaja, u 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.
Oh, hej, želim ti zahvaliti što si svratio. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2058.xlsm
