Kör totalt i sidfot - Excel-tips

Kan Excel skriva ut en löpande summa i sidfoten för varje sida? Det är inte inbyggt, men ett kort makro kommer att lösa problemet.

Kolla på video

• Mål: Skriv ut kategori som kör totalt och% av kategori längst ner på varje utskriven sida
• Problem: ingenting i Excel-användargränssnittet kan meddela en formel att du befinner dig längst ner på en utskriven sida
• Ja, du kan "se" sidbrytningarna, men formlerna kan inte se dem
• Möjlig lösning: Använd ett makro
• Strategi: Lägg till den totala körningen och% av kategorin för varje rad. Dölj på alla rader.
• Löpande totalt för kategoriformel: =IF(A6=A5,SUM(F6,G5),SUM(F6))
• % av kategoriformel: =G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844)
• Om din arbetsbok sparas som XLSX, gör en Spara som för att spara som XLSM
• Om du aldrig har använt makron, ändra makrosäkerheten
• Om du aldrig har använt makron, visa fliken Utvecklare
• Byt till VBA
• Sätt i en modul
• Skriv koden
• Tilldela detta makro till en form
• När sidstorleken ändras kör du återställningsmakronet

Videoutskrift

Lär dig Excel från Podcast, avsnitt 2058: Total körning i slutet av varje sida

Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga skickad av Wiley: Wiley vill visa en löpande summa av intäkter och andel av kategori i den sista raden på varje tryckt sida. Så Wiley har skrivit ut rapporter här med massor av massor av poster, flera sidor för varje kategori där borta i kolumn A. Och när vi kommer till slutet av utskriftssidan letar Wiley efter en summa här som visar totala intäkter, kör totalt i denna kategori och sedan procent av kategorin. Och så kan du se att vi är på 9,7% där, när jag går till sidan 2 - 21.1, sida 3 - 33.3 och så vidare. Och vid sidbrytningen där vi blir klara med kategori A, totalsumma för kategorin och totalsumman 100%. Okej, och när Wiley frågade mig om det här var jag som, "Åh nej, vi gör inte där"Det finns inget sätt i sidfoten att sätta en löpande summa. ” Okej, så detta är visserligen ett hemskt billigt fusk och jag uppmuntrar alla som tittar på detta på YouTube, om du har ett bättre sätt, snälla, i alla fall, nämna det i kommentarerna, okej? Och så, min idé är bara där ute i kolumnerna G och H, för att dölja den totala körningen och andelen kategori i varje enskild rad. Okej, och sedan använder vi ett makro för att upptäcka om vi är i slutet av sidan.åter i slutet av sidan.åter i slutet av sidan.

Okej, så de två formlerna som vi vill ha här säger, hej, om denna kategori är lika med den föregående kategorin. Så om A6 = A5 tar du SUMMA av dessa intäkter, så det är i F6 och den tidigare löpande summan där uppe i G5. Eftersom jag använder SUM-funktionen här, felar det här inte om vi någonsin skulle försöka lägga till löpande total. Annars kommer vi bara att vara i en helt ny kategori, så när vi byter från A till B tar vi bara SUMMET av värdet till vänster om oss, vilket jag bara kunde ha satt F6 där. Men här är vi, du vet, för sent. Och sedan procentandel av kategori, den här kommer att vara fruktansvärt ineffektiv. Vi tar intäkterna på den här raden dividerat med SUMMA för alla intäkter där kategorin är lika med A6. Så det här är alla kategorier,detta är kategorin i den här raden och lägg sedan till motsvarande cell från alla rader. Naturligtvis $ tecken - 1, 2, 3, 4 $ tecken där. Inga $ tecken i A6 och 4 $ tecken där. Okej, så visar vi det här numret som ett nummer, kanske 1000 separator, klicka på OK och sedan här som en procentsats med en decimal så. Okej, så kopierar vi den här formeln till alla celler. BAM, som det, okej. Men nu är målet här att se till att vi bara ser dessa summor när vi kommer till sidbrytningen. Okej, det är precis där. Det är en automatisk sidbrytning och sedan senare när vi byter från slutet av A till B, en manuell sidbrytning. Så denna manuella sidbrytning här är annorlunda än en automatisk sidbrytning.och vi kommer att visa detta nummer som ett nummer, kanske 1000 separator, klicka på OK och sedan här som en procentsats med en sådan decimal. Okej, så kopierar vi den här formeln till alla celler. BAM, som det, okej. Men nu är målet här att se till att vi bara ser dessa summor när vi kommer till sidbrytningen. Okej, det är precis där. Det är en automatisk sidbrytning och sedan senare när vi byter från slutet av A till B, en manuell sidbrytning. Så denna manuella sidbrytning här är annorlunda än en automatisk sidbrytning.och vi kommer att visa detta nummer som ett nummer, kanske 1000 separator, klicka på OK och sedan här som en procentsats med en sådan decimal. Okej, så kopierar vi den här formeln till alla celler. BAM, som det, okej. Men nu är målet här att se till att vi bara ser dessa summor när vi kommer till sidbrytningen. Okej, det är precis där. Det är en automatisk sidbrytning och sedan senare när vi byter från slutet av A till B, en manuell sidbrytning. Så denna manuella sidbrytning här är annorlunda än en automatisk sidbrytning.Men nu är målet här att se till att vi bara ser dessa summor när vi kommer till sidbrytningen. Okej, det är precis där. Det är en automatisk sidbrytning och sedan senare när vi byter från slutet av A till B, en manuell sidbrytning. Så denna manuella sidbrytning här är annorlunda än en automatisk sidbrytning.Men nu är målet här att se till att vi bara ser dessa summor när vi kommer till sidbrytningen. Okej, det är precis där. Det är en automatisk sidbrytning och sedan senare när vi byter från slutet av A till B, en manuell sidbrytning. Så denna manuella sidbrytning här är annorlunda än en automatisk sidbrytning.

Okej, nu kommer du att märka här uppe att den här filen sparas som en XLSX-fil eftersom det är så här Excel vill spara filer. XLSX är den trasiga filtypen som inte tillåter makron, eller hur? Värsta filtypen i världen. Så hoppa inte över det här steget eller det här. Allt ditt arbete härifrån och ut kommer att gå vilse. Spara som, och vi kommer inte att spara som en Excel-arbetsbok utan som en makroaktiverad arbetsbok eller som en binär arbetsbok eller som en XLS. Jag ska gå med Macro-Enabled Workbook. Om du inte gör det steget är du på väg att förlora resten av det arbete du gör. Okej, och sedan, om du aldrig har kört makron förut, ska vi högerklicka och säga Anpassa menyfliksområdet. Här på höger sida väljer du rutan för Developer, som ger dig en Developer-flik. När du väl har fliken Utvecklare kan vi gå till Makrosäkerhet,som standard kommer det att vara här här Inaktivera alla makron och säg inte att du har inaktiverat hela makron. Du vill växla ner till den andra, på det sättet när vi öppnar filen, säger vi, ”Hej, det finns makron här. Skapade du dessa? Är du okej med det här? ” Och du kan säga, Aktivera makron. Okej, klicka på OK.

Nu ska vi byta till Visual Basic Editor. Om du aldrig har använt en visuell bas förut, kommer du att börja med denna helt grå skärm, gå till Visa och Project Explorer. Här är en lista över alla öppna arbetsböcker. Så jag har Solver-tillägget, min personliga makroarbetsbok och här är arbetsboken som jag arbetar med. Se till att den här arbetsboken är vald, gör Infoga, Modul. Insert, Module får en fin stor tom, vit duk här. Okej, och sedan skriver du in den här koden. Okej nu, vi använder ett objekt här som heter HPageBreak, en horisontell sidbrytning. Och eftersom jag inte använder det här mycket, var jag tvungen att förklara det här som en variabel, som ett objekt HPB, så skulle jag kunna se de val som finns tillgängliga för mig i var och en. OK,ta reda på var den sista raden med data är idag så jag använder kolumn A, jag ska till slutet av kolumn A - A1048576. Detta är en L här och inte en 1, det här är en L. Alla skruvar upp det. L som i Excel. Det låter som Excel. Förstår? Excel upp. Så gå till A1048576, tryck på Avsluta och Uppåtpilen för att komma till sista raden. Ta reda på vilken rad det är. Och sedan i kolumnerna G och H, och om du tittar på det här måste du titta på dina Excel-data och ta reda på var dina två nya kolumner är, okej. Jag vet inte hur många kolumner du har. Kanske är dina nya kolumner över i I och J, eller kanske är de i C och D. Jag vet inte, ta reda på var de är och vi kommer att dölja alla dessa rader, okej. Så i mitt fall började det från G6, det är det första stället där vi har ett nummer:H och sedan sammanfogar jag den sista raden som vi har idag med ett nummerformat på tre semikolon som döljer data.

Okej, sedan den här nästa, jag lärde mig den här nästa från anslagstavlan. Om du inte placerar det aktiva fönstret i förhandsgranskningsläget för sidbrytning innan du kör den här koden fungerar den här koden inte. Det fungerar för vissa sidbrytningar men inte alla sidbrytningar, så du måste tillfälligt visa sidbrytningarna. Och sedan en slinga här: För varje är detta min objektvariabel - HPB i ActiveSheet.HPageBreaks. Ta reda på den sista raden, okej? Så för detta objekt, för sidbrytning, räkna ut platsen, räkna ut raden. Och detta är faktiskt den första raden på nästa sida så jag måste dra 1 från det, okej. Och här erkänner jag att det här är otroligt billigt, gå ut till Kolumn 7 som är Kolumn G, ändra NumberFormat för att vara valuta, bara av den raden. Och gå sedan ut till kolumn 8 som är H och ändra den till en procentsats och gå vidare.Avsluta slutligen horisontellt eller en förhandsgranskning av en sidbrytning och gå tillbaka till normal vy.

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.

Åh hej, jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.

Nedladdning fil

Ladda ner exempelfilen här: Podcast2058.xlsm