Kan Excel skrive ut en løpende sum i bunnteksten for hver side? Det er ikke innebygd, men en kort makro vil løse problemet.
Se på video
- Mål: Skriv ut kategorien som kjører totalt og% av kategorien nederst på hver utskrevne side
- Problem: ingenting i Excel-brukergrensesnittet kan fortelle en formel at du er nederst på en utskrevet side
- Ja, du kan "se" sideskiftene, men formler kan ikke se dem
- Mulig løsning: Bruk en makro
- Strategi: Legg til løpende total og% av kategori for hver rad. Skjul på alle radene.
- Kjører totalt for kategoriformel:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % av kategoriformel:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Hvis arbeidsboken din er lagret som XLSX, gjør du en Lagre som for å lagre som XLSM
- Hvis du aldri har brukt makroer, kan du endre makrosikkerhet
- Hvis du aldri har brukt makroer, kan du vise fanen Utvikler
- Bytt til VBA
- Sett inn en modul
- Skriv inn koden
- Tilordne den makroen til en figur
- Når sidestørrelsen endres, kjører du tilbakestillingsmakroen
Videoutskrift
Lær Excel fra Podcast, Episode 2058: Running Total på slutten av hver side
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål sendt av Wiley: Wiley ønsker å vise en løpende total inntekt og prosentandel av kategori i den siste raden på hver utskrevne side. Så, Wiley har skrevet ut rapporter her med tonnevis av poster, flere sider for hver kategori der i kolonne A. Og når vi kommer til slutten av utskriftssiden, ser Wiley etter en total her som viser total inntekt, kjører totalt innenfor denne kategorien og deretter prosentandelen av kategorien. Og så kan du se at vi er på 9,7% der, når jeg går til side 2 - 21.1, side 3 - 33.3 og så videre. Og på sideskiftet der vi blir ferdige med kategori A, totalsum for kategorien og totalsummen 100%. Ok, og da Wiley spurte meg om dette, var jeg som: "Å nei, vi gjør ikke - der 'Det er ingen måte i bunnteksten å sette en løpende sum. ” Ok, så dette er riktignok et fryktelig billig juks, og jeg oppfordrer alle som ser på dette på YouTube, hvis du har en bedre måte, vær så snill å nevne det i kommentarene, ok? Og så, ideen min er bare der ute i kolonnene G og H, for å skjule den totale summen og prosentandelen av kategorien i hver eneste rad. Greit, og så bruker vi en makro for å oppdage om vi er på slutten av siden.re på slutten av siden.re på slutten av siden.
Ok, så de to formlene vi vil ha her sier, hei, hvis denne kategorien er lik den forrige kategorien. Så hvis A6 = A5, så ta SUMMET av denne inntekten, så det er i F6 og forrige løpende total der oppe i G5. Nå, fordi jeg bruker SUM-funksjonen her, feiler ikke dette hvis vi noen gang vil prøve å legge til løpende total. Ellers kommer vi bare til å være i en helt ny kategori, så når vi bytter fra A til B, tar vi bare SUM av verdien til venstre for oss, som jeg bare kunne satt F6 der. Men her er vi, vet du, for sent. Og så prosentandel av kategori, denne kommer til å være fryktelig ineffektiv. Vi tar inntektene på denne raden delt på SUM av alle inntektene der kategorien er lik A6. Så dette er alle kategoriene,dette er kategorien i denne raden, og legg deretter til den tilsvarende cellen fra alle rader. Selvfølgelig, $ skilt - 1, 2, 3, 4 $ tegn der. Ingen $ tegn i A6 og 4 $ tegn der. OK, så viser vi dette tallet som et tall, kanskje 1000 skilletegn, klikker OK, og så her som en prosentandel med en slik desimalplass. Ok, så kopierer vi denne formelen til alle celler. BAM, sånn, greit. Men nå er målet her å sørge for at vi bare ser totalene når vi kommer til sideskiftet. Greit, det er der. Det er en automatisk sideskift og senere når vi bytter fra slutten av A til B, en manuell sideskift. Så dette manuelle sideskiftet her er annerledes enn et automatisk sideskift.og vi viser dette tallet som et tall, kanskje 1000 skilletegn, klikker OK, og så her som en prosentandel med en slik desimalplass. Ok, så kopierer vi denne formelen til alle celler. BAM, sånn, greit. Men nå er målet her å sørge for at vi bare ser disse totalene når vi kommer til sideskiftet. Greit, det er der. Det er en automatisk sideskift og senere når vi bytter fra slutten av A til B, en manuell sideskift. Så dette manuelle sideskiftet her er annerledes enn et automatisk sideskift.og vi viser dette tallet som et tall, kanskje 1000 skilletegn, klikker OK, og så her som en prosentandel med en slik desimalplass. Ok, så kopierer vi denne formelen til alle celler. BAM, sånn, greit. Men nå er målet her å sørge for at vi bare ser totalene når vi kommer til sideskiftet. Greit, det er der. Det er en automatisk sideskift og senere når vi bytter fra slutten av A til B, en manuell sideskift. Så dette manuelle sideskiftet her er annerledes enn et automatisk sideskift.Men nå er målet her å sørge for at vi bare ser totalene når vi kommer til sideskiftet. Greit, det er der. Det er en automatisk sideskift og senere når vi bytter fra slutten av A til B, en manuell sideskift. Så dette manuelle sideskiftet her er annerledes enn et automatisk sideskift.Men nå er målet her å sørge for at vi bare ser totalene når vi kommer til sideskiftet. Greit, det er der. Det er en automatisk sideskift og senere når vi bytter fra slutten av A til B, en manuell sideskift. Så dette manuelle sideskiftet her er annerledes enn et automatisk sideskift.
OK, nå vil du legge merke til her oppe at denne filen er lagret som en XLSX-fil fordi det er slik Excel vil lagre filer. XLSX er den ødelagte filtypen som ikke tillater makroer, ikke sant? Verste filtype i verden. Så ikke hopp over dette trinnet eller dette. Alt arbeidet ditt herfra og utover vil gå tapt. Lagre som, og vi skal ikke lagre som en Excel-arbeidsbok, men som en makroaktivert arbeidsbok eller som en binær arbeidsbok eller som en XLS. Jeg skal gå med Macro-Enabled Workbook. Hvis du ikke gjør det trinnet, er du i ferd med å miste resten av jobben du gjør. Greit, og hvis du aldri har kjørt makroer før, skal vi høyreklikke og si Tilpass båndet. Her på høyre side velger du ruten for Utvikler, som gir deg en Utvikler-fane. Når du har utviklerfanen, kan vi gå til Makrosikkerhet,som standard kommer det til å være oppe her. Deaktiver alle makroer, og ikke fortell meg at du har deaktivert hele makroene. Du vil bytte ned til den andre, på den måten når vi åpner filen, vil vi si, “Hei, det er makroer her. Skapte du disse? Har du det bra med dette? ” Og du kan si: Aktiver makroene. OK, klikk OK.
Nå skal vi bytte til Visual Basic Editor. Hvis du aldri har brukt en visuell grunnleggende før, kommer du til å starte med denne helt grå skjermen, gå til Vis og Prosjektutforskeren. Her er en liste over alle de åpne arbeidsbøkene. Så jeg har Solver-tillegget, min personlige makroarbeidsbok og her er arbeidsboken jeg jobber med. Forsikre deg om at denne arbeidsboken er valgt, gjør Sett inn modul. Sett inn, modul vil få et fint, stort, hvitt lerret her. Greit, og så skal du skrive inn denne koden. Greit nå, vi bruker et objekt her som heter HPageBreak, et horisontalt sideskift. Og fordi jeg ikke bruker dette mye, måtte jeg erklære det her oppe som en variabel, som et objekt HPB, slik at jeg kunne se hvilke valg som er tilgjengelige for meg i hver enkelt. Ok,finne ut hvor den siste raden med data er i dag, så jeg bruker kolonne A, jeg skal til slutten av kolonne A - A1048576. Dette er en L her og ikke en 1, dette er en L. Alle skrur det opp. L som i Excel. Det høres ut som Excel. Skjønner? Excel opp. Så gå til A1048576, trykk på End-tasten og Pil opp-tasten for å komme til den siste raden. Finn ut hvilken rad det er. Og så i kolonne G og H, og hvis du ser på dette, må du ta en titt på Excel-dataene dine og finne ut hvor de to nye kolonnene dine er, ok. Jeg vet ikke hvor mange kolonner du har. Kanskje de nye kolonnene dine er over i I og J, eller kanskje de er i C og D. Jeg vet ikke, finn ut hvor de er, og vi skal skjule alle disse radene, ok. Så i mitt tilfelle startet det fra G6, det er det første stedet vi har et nummer:H og så sammenkobler jeg den siste raden vi har i dag ved hjelp av et tallformat på tre semikoloner som vil skjule dataene.
OK, så denne neste, jeg lærte denne neste fra oppslagstavlen. Hvis du ikke setter det aktive vinduet i Page Break Preview-modus før du kjører denne koden, vil ikke denne koden fungere. Det fungerer for noen av sideskiftene, men ikke alle sideskiftene, så du må midlertidig vise sideskiftene. Og så en løkke her: For hver er dette objektvariabelen min - HPB i ActiveSheet.HPageBreaks. Finn ut den siste raden, ok? Så for dette objektet, for sideskiftet, finn ut plasseringen, finn ut raden. Og dette er faktisk den første raden på neste side, så jeg må trekke 1 fra det, ok. Og her innrømmer jeg at dette er utrolig billig, gå ut til Kolonne 7 som er Kolonne G, endre NumberFormat til å være valuta, bare av den raden. Og så gå ut til Kolonne 8 som er H og endre den til en prosentandel og gå videre.Avslutt til slutt vannrett eller forhåndsvisning av sideskift og gå tilbake til normal visning.
OK, så det er koden vår. Jeg vil arkivere, lukke og gå tilbake til Microsoft Excel. Jeg vil ha en enkel måte å kjøre dette på, så jeg skal sette inn, velge en fin form her. Jeg vil velge et avrundet rektangel, tegne mitt høyre rundt et rektangel i, Sideoppsett, gå til Effekter, velge effektene for Office 2007. Og så her i kategorien Format har vi en fin måte å legge litt glød til det, ok .
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.
Å hei, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2058.xlsm
