Poate Excel să imprime un total de rulare în subsol pentru fiecare pagină? Nu este încorporat, dar o macro scurtă va rezolva problema.
Urmăriți videoclipul
- Obiectiv: Categorie tipărită care rulează total și% din categorie în partea de jos a fiecărei pagini tipărite
- Problemă: nimic din interfața cu utilizatorul Excel nu poate informa o formulă că sunteți în partea de jos a unei pagini tipărite
- Da, puteți „vedea” pauzele de pagină, dar formulele nu le pot vedea
- Soluție posibilă: utilizați o macro
- Strategie: Adăugați totalul de rulare și% din categorie pentru fiecare rând. Ascundeți pe toate rândurile.
- Total alergător pentru Formula categoriei:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % din formula categoriei:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Dacă registrul dvs. de lucru este salvat ca XLSX, efectuați o Salvare ca pentru a salva ca XLSM
- Dacă nu ați folosit niciodată macrocomenzi, modificați securitatea macrocomenzilor
- Dacă nu ați folosit niciodată macrocomenzi, afișați fila Dezvoltator
- Treceți la VBA
- Introduceți un modul
- Tastați codul
- Atribuiți acea macro unei forme
- Pe măsură ce dimensiunea paginii se modifică, rulați macrocomanda de resetare
Transcriere video
Aflați Excel din Podcast, Episodul 2058: Rularea totală la sfârșitul fiecărei pagini
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi trimisă de Wiley: Wiley vrea să afișeze un total al veniturilor și un procent din categorie în ultimul rând al fiecărei pagini tipărite. Deci, Wiley a tipărit aici rapoarte cu tone și tone de înregistrări, mai multe pagini pentru fiecare categorie de acolo, în coloana A. Și când ajungem la sfârșitul paginii tipărite, Wiley caută aici un total care să arate venituri totale, rulează total în această categorie și apoi procent din categorie. Și așa, puteți vedea că suntem la 9,7% acolo, când trec la pagina 2 - 21.1, pagina 3 - 33.3 și așa mai departe. Și la pauza de pagină unde am terminat cu categoria A, total total pentru categorie și total total 100%. Bine, și când Wiley m-a întrebat despre asta, am spus: „Oh, nu, nu …Nu există nicio modalitate în subsol de a pune un total de rulare. " Bine, deci este o trăsătură oribilă ieftină și încurajez pe oricine urmărește acest lucru pe YouTube, dacă aveți o modalitate mai bună, vă rog să menționați în comentarii, bine? Și, așadar, ideea mea este doar în coloanele G și H, pentru a ascunde totalul de rulare și procentul de categorie în fiecare rând. Bine, apoi folosim o macro pentru a detecta dacă suntem la sfârșitul paginii.re la sfârșitul paginii.re la sfârșitul paginii.
Bine, deci cele două formule pe care le dorim aici spun, hei, dacă această categorie este egală cu categoria anterioară. Deci, dacă A6 = A5, atunci luați SUMA acestui venit, deci este în F6 și numărul total de rulare anterior în G5. Acum, pentru că folosesc funcția SUM aici, acest lucru nu este eronat dacă am încerca vreodată să adăugăm totalul de rulare. În caz contrar, vom fi doar la o categorie nouă, așa că, atunci când vom trece de la A la B, vom lua doar SUMA valorii din stânga noastră, pe care aș fi putut să o pun doar pe F6 acolo. Dar iată-ne, știi, prea târziu. Și apoi procentul din categorie, acesta va fi oribil de ineficient. Venim pe acest rând împărțit la SUMA tuturor veniturilor în care categoria este egală cu A6. Deci acestea sunt toate categoriile,aceasta este categoria din acest rând și apoi adăugați celula corespunzătoare din toate rândurile. Desigur, semnele $ - semnele 1, 2, 3, 4 $ acolo. Nu există semne $ în A6 și 4 $ semne acolo. Bine, și vom afișa acest număr ca un număr, poate 1000 separator, faceți clic pe OK, apoi aici ca procent cu o zecimală de genul. Bine, și vom copia această formulă în toate celulele. BAM, așa, bine. Dar acum scopul aici este să ne asigurăm că vom vedea acele totaluri doar când ajungem la pauza de pagină. Bine, este chiar acolo. Aceasta este o pauză automată de pagină și apoi mai târziu, când trecem de la sfârșitul lui A la B, o pauză manuală de pagină. Deci, această întrerupere manuală de pagină aici este diferită de o întrerupere automată de pagină.și vom afișa acest număr ca un număr, poate 1000 separator, faceți clic pe OK, apoi aici ca procent cu o zecimală de genul. Bine, și vom copia această formulă în toate celulele. BAM, așa, bine. Dar acum scopul aici este să ne asigurăm că vom vedea acele totaluri doar când ajungem la pauza de pagină. Bine, este chiar acolo. Aceasta este o pauză automată de pagină și apoi mai târziu, când trecem de la sfârșitul lui A la B, o pauză manuală de pagină. Deci, această întrerupere manuală de pagină aici este diferită de o întrerupere automată de pagină.și vom afișa acest număr ca un număr, poate 1000 separator, faceți clic pe OK, apoi aici ca procent cu o zecimală de genul. Bine, și vom copia această formulă în toate celulele. BAM, așa, bine. Dar acum scopul aici este să ne asigurăm că vom vedea acele totaluri doar când ajungem la pauza de pagină. Bine, este chiar acolo. Aceasta este o pauză automată de pagină și apoi mai târziu, când trecem de la sfârșitul lui A la B, o pauză manuală de pagină. Deci, această întrerupere manuală de pagină aici este diferită de o întrerupere automată de pagină.Dar acum scopul aici este să ne asigurăm că vom vedea aceste totaluri doar atunci când ajungem la pauza de pagină. Bine, este chiar acolo. Aceasta este o pauză automată de pagină și apoi mai târziu, când trecem de la sfârșitul lui A la B, o pauză manuală de pagină. Deci, această întrerupere manuală de pagină aici este diferită de o întrerupere automată de pagină.Dar acum scopul aici este să ne asigurăm că vom vedea acele totaluri doar când ajungem la pauza de pagină. Bine, este chiar acolo. Aceasta este o pauză automată de pagină și apoi mai târziu, când trecem de la sfârșitul lui A la B, o pauză manuală de pagină. Deci, această întrerupere manuală de pagină aici este diferită de o întrerupere automată de pagină.
Bine, acum veți observa aici că acest fișier este salvat ca fișier XLSX, deoarece așa vrea Excel să salveze fișierele. XLSX este tipul de fișier defect care nu permite macrocomenzile, nu? Cel mai prost tip de fișier din lume. Deci, nu omiteți acest pas sau acesta. Toată munca ta de aici și de afară se va pierde. Salvați ca și vom salva nu ca un registru de lucru Excel, ci ca un registru de lucru activat macro sau ca un registru de lucru binar sau ca un XLS. Voi merge cu Cartea de lucru Macro-Enabled. Dacă nu faceți acest pas, sunteți pe punctul de a pierde restul muncii pe care o faceți. Bine, apoi, dacă nu ați mai rulat niciodată macrocomenzi, vom face clic dreapta și vom spune Personalizați panglica. Aici, în partea dreaptă, alegeți caseta pentru Dezvoltator, care vă va oferi o filă Dezvoltator. Odată ce aveți fila Dezvoltator, putem merge la Macro Security,implicit, va fi aici sus Dezactivați toate macrocomenzile și nu-mi spuneți că ați dezactivat toate macrocomenzile. Doriți să treceți la cel de-al doilea, în acest fel, atunci când deschidem fișierul, vom spune: „Hei, există macro-uri aici. Le-ai creat? Ești bine cu asta? ” Și puteți spune: Activați macrocomenzile. Bine, faceți clic pe OK.
Now, we're going to switch over to the visual basic editor. If you've never used a visual basic before, you’re going to start with this completely gray screen, go to View and the Project Explorer. Here's a list of all the open workbooks. So I have the Solver Add-in, my personal macro workbook and here's the workbook that I'm working on. Make sure that this workbook is selected, do Insert, Module. Insert, Module will get a nice big blank, white canvas here. Alright, and then you're going to type in this code. Alright now, we're using an object here called HPageBreak, a horizontal page break. And because I don't use this a lot, I had to declare it up here as a variable, as an object HPB, that way I would be able to see the choices that are available to me in each one. Alright, figure out where the last row with data is today so I'm using Column A, I'm going to the end of Column A - A1048576. This is an L here and not a 1, this is an L. Everyone screws that up. L as in Excel. It sounds like Excel. Get it? Excel up. So, go to A1048576, press the End key, and the Up Arrow key to get to the last row. Figure out what row that is. And then in Columns G and H, and if you're watching this, you need to take a look at your Excel data and figure out where your two new columns are, alright. I don't know how many columns you have. Maybe your new columns are over in I and J, or maybe they're in C and D. I don't know, figure out where those are and we're going to hide all of those rows, alright. So in my case, it was starting from G6, that's the first place where we have a number, :H and then I'm concatenating the last row that we have today using a number format of three semicolons that will hide the data.
Alright, then this next one, I learned this next one from the message board. If you don't put the active window in Page Break Preview mode before you run this code, this code will not work. It works for some of the page breaks but not all of the page breaks, so you have to temporarily display the page breaks. And then a loop here: For Each, this is my object variable- HPB In ActiveSheet.HPageBreaks. Figure out the last row, alright? So for this object, for the page break, figure out the location, figure out the row. And this is actually the first row of the next page so I have to subtract 1 from that, alright. And then here, I admit this is incredibly cheap, go out to Column 7 which is Column G, change the NumberFormat to be currency, just of that row. And then go out to Column 8 which is H and change it to a percentage and go next. Finally, exit horizontal or a page break preview and go back into normal view.
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.
Oh, hei, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2058.xlsm
