Kann Excel für jede Seite eine laufende Summe in der Fußzeile drucken? Es ist nicht eingebaut, aber ein kurzes Makro löst das Problem.
Schau Video
- Ziel: Druckkategorie mit laufender Summe und% der Kategorie am Ende jeder gedruckten Seite
- Problem: Nichts in der Excel-Benutzeroberfläche kann eine Formel darüber informieren, dass Sie sich am Ende einer gedruckten Seite befinden
- Ja, Sie können die Seitenumbrüche "sehen", aber Formeln können sie nicht sehen
- Mögliche Lösung: Verwenden Sie ein Makro
- Strategie: Addieren Sie die laufende Summe und% der Kategorie für jede Zeile. Verstecke dich in allen Reihen.
- Laufende Summe für Kategorie Formel:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % der Kategorie Formel:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Wenn Ihre Arbeitsmappe als XLSX gespeichert ist, führen Sie ein Speichern unter aus, um sie als XLSM zu speichern
- Wenn Sie noch nie Makros verwendet haben, ändern Sie die Makrosicherheit
- Wenn Sie noch nie Makros verwendet haben, zeigen Sie die Registerkarte Entwickler an
- Wechseln Sie zu VBA
- Fügen Sie ein Modul ein
- Tippe den Code ein
- Weisen Sie dieses Makro einer Form zu
- Führen Sie das Reset-Makro aus, wenn sich die Seitengröße ändert
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2058: Laufende Summe am Ende jeder Seite
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage von Wiley: Wiley möchte in der letzten Zeile jeder gedruckten Seite eine laufende Gesamtsumme des Umsatzes und des Prozentsatzes der Kategorie anzeigen. Also hat Wiley hier Berichte mit Tonnen und Tonnen von Datensätzen gedruckt, mehrere Seiten für jede Kategorie dort in Spalte A. Und wenn wir bis zum Ende der Druckseite kommen, sucht Wiley hier nach einer Summe, die den Gesamtumsatz anzeigt. laufende Summe innerhalb dieser Kategorie und dann Prozentsatz der Kategorie. Sie sehen also, dass wir dort bei 9,7% liegen, wenn ich zu Seite 2 - 21.1, Seite 3 - 33.3 und so weiter gehe. Und beim Seitenumbruch, wo wir mit Kategorie A fertig sind, Gesamtsumme für die Kategorie und Gesamtsumme 100%. Okay, und als Wiley mich danach fragte, sagte ich: "Oh nein, wir sind nicht da."Es gibt keine Möglichkeit in der Fußzeile, eine laufende Summe anzugeben. “ Okay, das ist zugegebenermaßen ein schrecklich billiger Cheat und ich ermutige jeden, der dies auf YouTube sieht. Wenn Sie einen besseren Weg haben, erwähnen Sie dies bitte auf jeden Fall in den Kommentaren, okay? Meine Idee ist es also, in den Spalten G und H die laufende Summe und den Prozentsatz der Kategorie in jeder einzelnen Zeile auszublenden. Okay, und dann verwenden wir ein Makro, um festzustellen, ob wir am Ende der Seite sind.Sie befinden sich am Ende der Seite.Sie befinden sich am Ende der Seite.
Okay, also sagen die beiden Formeln, die wir hier wollen, hey, wenn diese Kategorie gleich der vorherigen Kategorie ist. Wenn also A6 = A5 ist, nehmen Sie die Summe dieser Einnahmen, also in F6 und die vorherige laufende Summe dort oben in G5. Da ich hier die SUMME-Funktion verwende, tritt kein Fehler auf, wenn wir jemals versuchen würden, eine laufende Summe hinzuzufügen. Andernfalls befinden wir uns nur in einer brandneuen Kategorie. Wenn wir also von A nach B wechseln, nehmen wir einfach die Summe des Werts links von uns, den ich einfach dort F6 hätte setzen können. Aber hier sind wir zu spät. Und dann Prozentsatz der Kategorie, diese wird schrecklich ineffizient sein. Wir nehmen die Einnahmen in dieser Zeile geteilt durch die Summe aller Einnahmen, bei denen die Kategorie gleich A6 ist. Das sind also alle Kategorien,Dies ist die Kategorie in dieser Zeile und addiert dann die entsprechende Zelle aus allen Zeilen. Natürlich $ Zeichen - 1, 2, 3, 4 $ Zeichen dort. Keine $ Zeichen in A6 und 4 $ Zeichen dort. Okay, und wir zeigen diese Zahl als Zahl, vielleicht 1000 Trennzeichen, klicken Sie auf OK und dann hier als Prozentsatz mit einer solchen Dezimalstelle. Okay, und wir werden diese Formel in alle Zellen kopieren. BAM, so, in Ordnung. Aber jetzt ist das Ziel hier sicherzustellen, dass wir diese Summen nur sehen, wenn wir zum Seitenumbruch kommen. Okay, es ist genau dort. Das ist ein automatischer Seitenumbruch und später, wenn wir vom Ende von A nach B wechseln, ein manueller Seitenumbruch. Dieser manuelle Seitenumbruch hier unterscheidet sich also von einem automatischen Seitenumbruch.und wir zeigen diese Zahl als Zahl, vielleicht 1000 Trennzeichen, klicken auf OK und dann hier als Prozentsatz mit einer solchen Dezimalstelle. Okay, und wir werden diese Formel in alle Zellen kopieren. BAM, so, in Ordnung. Aber jetzt ist das Ziel hier sicherzustellen, dass wir diese Summen nur sehen, wenn wir zum Seitenumbruch kommen. Okay, es ist genau dort. Das ist ein automatischer Seitenumbruch und später, wenn wir vom Ende von A nach B wechseln, ein manueller Seitenumbruch. Dieser manuelle Seitenumbruch hier unterscheidet sich also von einem automatischen Seitenumbruch.und wir zeigen diese Zahl als Zahl, vielleicht 1000 Trennzeichen, klicken auf OK und dann hier als Prozentsatz mit einer solchen Dezimalstelle. Okay, und wir werden diese Formel in alle Zellen kopieren. BAM, so, in Ordnung. Aber jetzt ist das Ziel hier sicherzustellen, dass wir diese Summen nur sehen, wenn wir zum Seitenumbruch kommen. Okay, es ist genau dort. Das ist ein automatischer Seitenumbruch und später, wenn wir vom Ende von A nach B wechseln, ein manueller Seitenumbruch. Dieser manuelle Seitenumbruch hier unterscheidet sich also von einem automatischen Seitenumbruch.Aber jetzt ist das Ziel hier sicherzustellen, dass wir diese Summen nur sehen, wenn wir zum Seitenumbruch kommen. Okay, es ist genau dort. Das ist ein automatischer Seitenumbruch und später, wenn wir vom Ende von A nach B wechseln, ein manueller Seitenumbruch. Dieser manuelle Seitenumbruch hier unterscheidet sich also von einem automatischen Seitenumbruch.Aber jetzt ist das Ziel hier sicherzustellen, dass wir diese Summen nur sehen, wenn wir zum Seitenumbruch kommen. Okay, es ist genau dort. Das ist ein automatischer Seitenumbruch und später, wenn wir vom Ende von A nach B wechseln, ein manueller Seitenumbruch. Dieser manuelle Seitenumbruch hier unterscheidet sich also von einem automatischen Seitenumbruch.
Okay, jetzt werden Sie hier oben feststellen, dass diese Datei als XLSX-Datei gespeichert wird, da Excel auf diese Weise Dateien speichern möchte. XLSX ist der fehlerhafte Dateityp, der keine Makros zulässt, oder? Der schlechteste Dateityp der Welt. Überspringen Sie diesen oder jenen Schritt also nicht. Alle Ihre Arbeit von hier und draußen geht verloren. Speichern unter, und wir speichern nicht als Excel-Arbeitsmappe, sondern als makrofähige Arbeitsmappe oder als binäre Arbeitsmappe oder als XLS. Ich werde mit Macro-Enabled Workbook gehen. Wenn Sie diesen Schritt nicht ausführen, verlieren Sie den Rest Ihrer Arbeit. Okay, und wenn Sie noch nie zuvor Makros ausgeführt haben, klicken Sie mit der rechten Maustaste und sagen "Band anpassen". Wählen Sie hier auf der rechten Seite das Feld für Entwickler aus, um eine Registerkarte für Entwickler zu erhalten. Sobald Sie die Registerkarte Entwickler haben, können Sie zur Makrosicherheit wechseln.Standardmäßig wird es hier oben sein. Deaktivieren Sie alle Makros und sagen Sie mir nicht, dass Sie die gesamten Makros deaktiviert haben. Sie möchten zum zweiten wechseln. Wenn wir die Datei öffnen, sagen wir: „Hey, hier gibt es Makros. Hast du diese erstellt? Bist du damit einverstanden? " Und Sie können sagen: Aktivieren Sie die Makros. Okay, klicken Sie auf OK.
Jetzt wechseln wir zum Visual Basic-Editor. Wenn Sie noch nie ein Visual Basic verwendet haben, beginnen Sie mit diesem vollständig grauen Bildschirm, gehen Sie zu Ansicht und zum Projektexplorer. Hier ist eine Liste aller geöffneten Arbeitsmappen. Ich habe also das Solver-Add-In, meine persönliche Makro-Arbeitsmappe, und hier ist die Arbeitsmappe, an der ich arbeite. Stellen Sie sicher, dass diese Arbeitsmappe ausgewählt ist. Führen Sie Einfügen, Modul aus. Einfügen, Modul erhält hier eine schöne große leere, weiße Leinwand. Okay, und dann geben Sie diesen Code ein. Okay, jetzt verwenden wir hier ein Objekt namens HPageBreak, einen horizontalen Seitenumbruch. Und weil ich das nicht oft benutze, musste ich es hier oben als Variable deklarieren, als Objekt-HPB, damit ich die Auswahlmöglichkeiten sehen kann, die mir in jedem einzelnen zur Verfügung stehen. In Ordung,Finde heraus, wo sich die letzte Zeile mit Daten heute befindet. Wenn ich also Spalte A verwende, gehe ich zum Ende von Spalte A - A1048576. Dies ist hier ein L und keine 1, dies ist ein L. Jeder vermasselt das. L wie in Excel. Es klingt wie Excel. Kapiert? Excel auf. Gehen Sie also zu A1048576, drücken Sie die End-Taste und die Aufwärtspfeiltaste, um zur letzten Zeile zu gelangen. Finde heraus, welche Zeile das ist. Und dann in den Spalten G und H, und wenn Sie dies sehen, müssen Sie sich Ihre Excel-Daten ansehen und herausfinden, wo sich Ihre beiden neuen Spalten befinden. Ich weiß nicht, wie viele Spalten Sie haben. Vielleicht sind deine neuen Spalten in I und J vorbei, oder vielleicht sind sie in C und D. Ich weiß nicht, finde heraus, wo diese sind und wir werden all diese Zeilen verstecken, okay. In meinem Fall ging es also von G6 aus, das ist der erste Ort, an dem wir eine Nummer haben:H und dann verkette ich die letzte Zeile, die wir heute haben, mit einem Zahlenformat von drei Semikolons, das die Daten verbirgt.
Okay, dann das nächste, ich habe das nächste vom Message Board gelernt. Wenn Sie das aktive Fenster nicht in den Seitenumbruch-Vorschaumodus versetzen, bevor Sie diesen Code ausführen, funktioniert dieser Code nicht. Es funktioniert für einige Seitenumbrüche, aber nicht für alle Seitenumbrüche, sodass Sie die Seitenumbrüche vorübergehend anzeigen müssen. Und dann hier eine Schleife: Für jede ist dies meine Objektvariable - HPB In ActiveSheet.HPageBreaks. Finde die letzte Reihe heraus, okay? Finden Sie für dieses Objekt für den Seitenumbruch die Position und die Zeile heraus. Und dies ist eigentlich die erste Zeile der nächsten Seite, also muss ich 1 davon abziehen, okay. Und dann gebe ich zu, dass dies unglaublich billig ist. Gehen Sie zu Spalte 7, Spalte G, und ändern Sie das NumberFormat in Währung, nur in dieser Zeile. Und dann gehen Sie zu Spalte 8, die H ist, und ändern Sie sie in einen Prozentsatz und gehen Sie als nächstes.Beenden Sie schließlich die horizontale Vorschau oder eine Seitenumbruchvorschau und kehren Sie zur normalen Ansicht zurück.
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 hey, ich möchte dir dafür danken, dass du vorbeigekommen bist. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2058.xlsm
