Kører samlet i sidefod - Excel-tip

Kan Excel udskrive et samlet løbende i sidefoden for hver side? Det er ikke indbygget, men en kort makro løser problemet.

Se video

• Mål: Udskriv kategori, der kører i alt og% af kategori nederst på hver udskrevet side
• Problem: intet i Excel-brugergrænsefladen kan fortælle en formel, at du er nederst på en udskrevet side
• Ja, du kan "se" sideskiftene, men formler kan ikke se dem
• Mulig løsning: Brug en makro
• Strategi: Tilføj det samlede antal og% af kategorien for hver række. Skjul på alle rækker.
• Løb i alt for kategoriformel: =IF(A6=A5,SUM(F6,G5),SUM(F6))
• % af kategoriformel: =G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844)
• Hvis din projektmappe er gemt som XLSX, skal du foretage en Gem som for at gemme som XLSM
• Hvis du aldrig har brugt makroer, skal du ændre makrosikkerhed
• Hvis du aldrig har brugt makroer, skal du vise fanen Udvikler
• Skift til VBA
• Indsæt et modul
• Indtast koden
• Tildel makroen til en figur
• Når sidestørrelsen ændres, skal du køre nulstillingsmakroen

Videoudskrift

Lær Excel fra Podcast, afsnit 2058: Kører i alt i slutningen af ​​hver side

Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål sendt af Wiley: Wiley ønsker at vise en løbende samlet indtægt og procentdel af kategori i den sidste række på hver udskrevet side. Så Wiley har udskrevet rapporter her med tonsvis af optegnelser, flere sider for hver kategori derovre i kolonne A. Og når vi kommer til slutningen af ​​udskriftssiden, leder Wiley efter en total her, der viser den samlede indtægt, kører total inden for denne kategori og derefter procentdel af 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 ved sideskiftet, hvor vi bliver færdige med kategori A, samlet total for kategorien og det samlede beløb 100%. Okay, og da Wiley spurgte mig om dette, var jeg som: “Åh nej, vi gør ikke - der 's ingen måde i sidefoden at sætte en løbende total. ” Okay, så dette er ganske vist en forfærdelig billig snyderi, og jeg opfordrer alle, der ser dette på YouTube, hvis du har en bedre måde, skal du under alle omstændigheder nævne det i kommentarerne, okay? Og så er min idé lige derude i kolonne G og H for at skjule den samlede løb og procentdelen af ​​kategori i hver enkelt række. Okay, og så bruger vi en makro til at opdage, om vi er i slutningen af ​​siden.re i slutningen af ​​siden.re i slutningen af ​​siden.

Okay, så de to formler, som vi vil have her, siger, hej, hvis denne kategori er lig med den forrige kategori. Så hvis A6 = A5 så tag SUMMET af denne indtægt, så det er i F6 og det foregående løbende total deroppe i G5. Nu, fordi jeg bruger SUM-funktionen her, fejler dette ikke, hvis vi nogensinde vil prøve at tilføje kørende total. Ellers vil vi bare være i en helt ny kategori, så når vi skifter fra A til B, tager vi bare SUM af værdien til venstre for os, som jeg bare kunne have sat F6 der. Men her er vi, du ved, for sent. Og så procentdel af kategori, denne bliver forfærdeligt ineffektiv. Vi tager indtægterne på denne række divideret med SUM for alle indtægter, hvor kategorien er lig med A6. Så disse er alle kategorier,dette er kategorien i denne række, og tilføj derefter den tilsvarende celle fra alle rækker. Selvfølgelig, $ tegn - 1, 2, 3, 4 $ tegn der. Ingen $ tegn i A6 og 4 $ tegn derinde. Okay, så viser vi dette tal som et tal, måske 1000 separator, skal du klikke på OK og derefter her som en procentdel med en sådan decimal. Okay, og vi kopierer denne formel ned til alle celler. BAM, sådan, okay. Men nu er målet her at sikre, at vi kun ser disse totaler, når vi kommer til sideskiftet. Okay, det er lige der. Det er et automatisk sideskift og derefter senere, når vi skifter fra slutningen af ​​A til B, et manuelt sideskift. Så dette manuelle sideskift her er anderledes end et automatisk sideskift.og vi viser dette tal som et tal, måske 1000 separator, klik på OK, og derefter her som en procentdel med en sådan decimal. Okay, og vi kopierer denne formel ned til alle celler. BAM, sådan, okay. Men nu er målet her at sikre, at vi kun ser disse totaler, når vi kommer til sideskiftet. Okay, det er lige der. Det er et automatisk sideskift og derefter senere, når vi skifter fra slutningen af ​​A til B, et manuelt sideskift. Så dette manuelle sideskift her er anderledes end et automatisk sideskift.og vi viser dette tal som et tal, måske 1000 separator, klik på OK, og derefter her som en procentdel med en sådan decimal. Okay, og vi kopierer denne formel ned til alle celler. BAM, sådan, okay. Men nu er målet her at sikre, at vi kun ser disse totaler, når vi kommer til sideskiftet. Okay, det er lige der. Det er et automatisk sideskift og derefter senere, når vi skifter fra slutningen af ​​A til B, et manuelt sideskift. Så dette manuelle sideskift her er anderledes end et automatisk sideskift.Men nu er målet her at sikre, at vi kun ser disse totaler, når vi kommer til sideskiftet. Okay, det er lige der. Det er et automatisk sideskift og derefter senere, når vi skifter fra slutningen af ​​A til B, et manuelt sideskift. Så dette manuelle sideskift her er anderledes end et automatisk sideskift.Men nu er målet her at sikre, at vi kun ser disse totaler, når vi kommer til sideskiftet. Okay, det er lige der. Det er et automatisk sideskift og derefter senere, når vi skifter fra slutningen af ​​A til B, et manuelt sideskift. Så dette manuelle sideskift her er anderledes end et automatisk sideskift.

Okay, nu vil du bemærke heroppe, at denne fil er gemt som en XLSX-fil, fordi det er sådan, Excel vil gemme filer. XLSX er den ødelagte filtype, der ikke tillader makroer, ikke? Værste filtype i verden. Så spring ikke dette trin over eller dette. Alt dit arbejde herfra og ude går tabt. Gem som, og vi gemmer ikke som en Excel-projektmappe, men som en makroaktiveret projektmappe eller som en binær projektmappe eller som en XLS. Jeg går med en Macro-Enabled Workbook. Hvis du ikke gør det trin, er du ved at miste resten af ​​det arbejde, du udfører. Okay, og hvis du aldrig har kørt makroer før, skal vi højreklikke og sige Tilpas båndet. Herovre i højre side skal du vælge feltet for Developer, der giver dig en Developer-fane. Når du har fanen Udvikler, kan vi gå til Makrosikkerhed,som standard vil det være herop Deaktiver alle makroer, og fortæl mig ikke, at du har deaktiveret hele makroerne. Du vil skifte ned til den anden, på den måde når vi åbner filen, vil vi sige, ”Hej, der er makroer her. Oprettede du disse? Er du okay med dette? ” Og du kan sige, Aktivér makroerne. OK, klik på OK.

Nu skal vi skifte til den visuelle grundeditor. Hvis du aldrig har brugt en visuel grundlæggende før, skal du starte med denne helt grå skærm, gå til Vis og Project Explorer. Her er en liste over alle de åbne projektmapper. Så jeg har Solver-tilføjelsesprogrammet, min personlige makro-projektmappe, og her er den projektmappe, som jeg arbejder på. Sørg for, at denne projektmappe er valgt, skal du indsætte, modul. Indsæt, modul får et dejligt stort tomt, hvidt lærred her. Okay, og så skriver du denne kode. Okay nu, vi bruger et objekt her kaldet HPageBreak, et vandret sideskift. Og fordi jeg ikke bruger dette meget, måtte jeg erklære det her som en variabel, som et objekt HPB, på den måde ville jeg være i stand til at se de valg, der er tilgængelige for mig i hver enkelt. I orden,finde ud af, hvor den sidste række med data er i dag, så jeg bruger kolonne A, jeg går til slutningen af ​​kolonne A - A1048576. Dette er en L her og ikke en 1, dette er en L. Alle skruer det op. L som i Excel. Det lyder som Excel. Få det? Excel op. Så gå til A1048576, tryk på End-tasten og Pil op-tasten for at komme til den sidste række. Find ud af, hvilken række det er. Og så i kolonne G og H, og hvis du ser dette, skal du se på dine Excel-data og finde ud af, hvor dine to nye kolonner er, okay. Jeg ved ikke, hvor mange kolonner du har. Måske er dine nye kolonner forbi i I og J, eller måske er de i C og D. Jeg ved ikke, find ud af, hvor de er, og vi skal skjule alle disse rækker, okay. Så i mit tilfælde startede det fra G6, det er det første sted, hvor vi har et nummer:H og så sammenkæder jeg den sidste række, som vi har i dag, ved hjælp af et talformat på tre semikoloner, der skjuler dataene.

Okay, så denne næste, jeg lærte den næste fra opslagstavlen. Hvis du ikke sætter det aktive vindue i Preview-tilstand for sideskift, før du kører denne kode, fungerer denne kode ikke. Det fungerer for nogle af sideskiftene, men ikke alle sideskift, så du skal midlertidigt vise sideskiftene. Og så en løkke her: For hver er dette min objektvariabel - HPB i ActiveSheet.HPageBreaks. Find ud af den sidste række, okay? Så for dette objekt, for sideskiftet, find ud af placeringen, find ud af rækken. Og dette er faktisk den første række på næste side, så jeg er nødt til at trække 1 fra det, okay. Og så her indrømmer jeg, at dette er utroligt billigt, gå ud til kolonne 7, som er kolonne G, skift NumberFormat til at være valuta, bare af den række. Og gå derefter ud til kolonne 8, som er H, og skift den til en procentdel, og gå derefter videre.Afslut til sidst vandret eller en forhåndsvisning af sideskift og gå tilbage til normal visning.

Okay, så det er vores kode. Jeg vil arkivere, lukke og vende tilbage til Microsoft Excel. Jeg vil have en nem måde at køre dette på, så jeg skal indsætte, vælge en god form her. Jeg vælger et afrundet rektangel, tegner mit højre rundt om et rektangel i, Sidelayout, går til Effekter, vælger effekterne til Office 2007. Og så her på fanen Format har vi en god måde at tilføje lidt glød til det, okay .

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, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.

Download fil

Download eksempelfilen her: Podcast2058.xlsm