Μπορεί το Excel να εκτυπώσει ένα τρέχον σύνολο στο υποσέλιδο για κάθε σελίδα; Δεν είναι ενσωματωμένο, αλλά μια σύντομη μακροεντολή θα λύσει το πρόβλημα.
Δες το βίντεο
- Στόχος: Σύνολο εκτύπωσης κατηγορίας εκτύπωσης και% κατηγορίας στο κάτω μέρος κάθε εκτυπωμένης σελίδας
- Πρόβλημα: τίποτα στη διεπαφή χρήστη του Excel δεν μπορεί να ενημερώσει τον τύπο ότι βρίσκεστε στο κάτω μέρος μιας εκτυπωμένης σελίδας
- Ναι, μπορείτε να "δείτε" τα διαλείμματα της σελίδας, αλλά οι τύποι δεν μπορούν να τα δουν
- Πιθανή λύση: Χρησιμοποιήστε μια μακροεντολή
- Στρατηγική: Προσθέστε το τρέχον σύνολο και% κατηγορίας για κάθε σειρά. Απόκρυψη σε όλες τις σειρές.
- Σύνολο εκτέλεσης για τύπο κατηγορίας:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % του τύπου κατηγορίας:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Εάν το βιβλίο εργασίας σας έχει αποθηκευτεί ως XLSX, κάντε ένα Save As για αποθήκευση ως XLSM
- Εάν δεν έχετε χρησιμοποιήσει ποτέ μακροεντολές, αλλάξτε την ασφάλεια μακροεντολών
- Εάν δεν έχετε χρησιμοποιήσει ποτέ μακροεντολές, εμφανίστε την καρτέλα Προγραμματιστής
- Μετάβαση σε VBA
- Εισαγάγετε μια ενότητα
- Πληκτρολογήστε τον κωδικό
- Εκχωρήστε αυτήν τη μακροεντολή σε σχήμα
- Καθώς αλλάζει το μέγεθος της σελίδας, εκτελέστε τη μακροεντολή επαναφοράς
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, επεισόδιο 2058: Σύνολο εκτέλεσης στο τέλος κάθε σελίδας
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση που έστειλε ο Wiley: Ο Wiley θέλει να δείξει ένα τρέχον σύνολο εσόδων και ποσοστό κατηγορίας στην τελευταία σειρά κάθε εκτυπωμένης σελίδας. Έτσι, η Wiley έχει εκτυπώσει αναφορές εδώ με τόνους και τόνους δίσκων, πολλές σελίδες για κάθε κατηγορία εκεί πάνω στη στήλη A. Και όταν φτάσουμε στο τέλος της σελίδας εκτύπωσης, ο Wiley ψάχνει ένα σύνολο εδώ που δείχνει συνολικά έσοδα, τρέχει συνολικά μέσα σε αυτήν την κατηγορία και στη συνέχεια ποσοστό της κατηγορίας. Και έτσι, μπορείτε να δείτε ότι είμαστε στο 9,7% εκεί, όταν πηγαίνω στη σελίδα 2 - 21.1, σελίδα 3 - 33.3 και ούτω καθεξής. Και στο διάλειμμα της σελίδας όπου ολοκληρώνουμε την κατηγορία Α, το συνολικό σύνολο για την κατηγορία και το συνολικό συνολικό 100%. Εντάξει, και όταν ο Wiley με ρώτησε για αυτό, ήμουν σαν, «Ω όχι, δεν υπάρχει-»Δεν υπάρχει τρόπος στο υποσέλιδο να βάλεις ένα σύνολο τρεξίματος. " Εντάξει, οπότε αυτό είναι βεβαίως ένα φρικτό φτηνό εξαπάτημα και ενθαρρύνω όποιον το παρακολουθεί στο YouTube, αν έχετε έναν καλύτερο τρόπο, παρακαλώ, με κάθε τρόπο, να το αναφέρετε στα σχόλια, εντάξει; Και έτσι, η ιδέα μου είναι ακριβώς εκεί έξω στις στήλες G και H, για να κρύψω το τρέχον σύνολο και το ποσοστό της κατηγορίας σε κάθε σειρά. Εντάξει, και στη συνέχεια χρησιμοποιούμε μια μακροεντολή για να εντοπίσουμε εάν είμαστε στο τέλος της σελίδας.στο τέλος της σελίδας.στο τέλος της σελίδας.
Εντάξει, οπότε λένε οι δύο τύποι που θέλουμε εδώ, γεια, αν αυτή η κατηγορία είναι ίδια με την προηγούμενη κατηγορία. Αν λοιπόν, A6 = A5, πάρτε το SUM των εσόδων, οπότε αυτό είναι στο F6 και το προηγούμενο τρέξιμο συνολικά εκεί στο G5. Τώρα, επειδή χρησιμοποιώ εδώ τη συνάρτηση SUM, αυτό δεν κάνει λάθος αν προσπαθούσαμε ποτέ να προσθέσουμε συνολικό τρέξιμο. Διαφορετικά, απλώς πρόκειται να βρεθούμε σε μια ολοκαίνουργια κατηγορία, οπότε όταν αλλάζουμε από το Α στο Β, θα πάρουμε απλώς το SUM της τιμής στα αριστερά μας, το οποίο θα μπορούσα να βάλω εκεί το F6 εκεί. Αλλά εδώ είμαστε, ξέρετε, πολύ αργά. Και μετά το ποσοστό της κατηγορίας, αυτό θα είναι τρομερά αναποτελεσματικό. Παίρνουμε τα έσοδα σε αυτήν τη σειρά διαιρούμενο με το άθροισμα όλων των εσόδων όπου η κατηγορία ισούται με A6. Αυτές είναι λοιπόν όλες οι κατηγορίες,Αυτή είναι η κατηγορία σε αυτήν τη σειρά και, στη συνέχεια, προσθέστε το αντίστοιχο κελί από όλες τις σειρές. Φυσικά, $ σημάδια - 1, 2, 3, 4 $ σημάδια εκεί. Δεν υπάρχουν πινακίδες $ στο A6 και 4 $ πινακίδες εκεί. Εντάξει, και θα δείξουμε αυτόν τον αριθμό ως αριθμό, ίσως 1000 διαχωριστικό, κάντε κλικ στο OK και, στη συνέχεια, εδώ ως ποσοστό με ένα δεκαδικό ψηφίο σαν αυτό. Εντάξει και θα αντιγράψουμε αυτόν τον τύπο σε όλα τα κελιά. BAM, έτσι, εντάξει. Αλλά τώρα ο στόχος εδώ είναι να βεβαιωθούμε ότι βλέπουμε αυτά τα σύνολα μόνο όταν φτάσουμε στο διάλειμμα της σελίδας. Εντάξει, είναι εκεί. Αυτό είναι ένα αυτόματο διάλειμμα σελίδας και αργότερα όταν αλλάζουμε από το τέλος του Α στο Β, μια χειροκίνητη αλλαγή σελίδας. Έτσι, αυτή η μη αυτόματη αλλαγή σελίδας εδώ είναι διαφορετική από μια αυτόματη αλλαγή σελίδας.και θα δείξουμε αυτόν τον αριθμό ως Αριθμό, ίσως 1000 διαχωριστικό, κάντε κλικ στο OK και μετά εδώ ως ποσοστό με ένα δεκαδικό ψηφίο σαν αυτό. Εντάξει και θα αντιγράψουμε αυτόν τον τύπο σε όλα τα κελιά. BAM, έτσι, εντάξει. Αλλά τώρα ο στόχος εδώ είναι να βεβαιωθούμε ότι βλέπουμε αυτά τα σύνολα μόνο όταν φτάσουμε στο διάλειμμα της σελίδας. Εντάξει, είναι εκεί. Αυτό είναι μια αυτόματη αλλαγή σελίδας και αργότερα όταν αλλάζουμε από το τέλος του Α στο Β, μια χειροκίνητη αλλαγή σελίδας. Έτσι, αυτή η μη αυτόματη αλλαγή σελίδας εδώ είναι διαφορετική από μια αυτόματη αλλαγή σελίδας.και θα δείξουμε αυτόν τον αριθμό ως έναν αριθμό, ίσως 1000 διαχωριστικό, κάντε κλικ στο OK και μετά εδώ ως ποσοστό με ένα δεκαδικό ψηφίο σαν αυτό. Εντάξει και θα αντιγράψουμε αυτόν τον τύπο σε όλα τα κελιά. BAM, έτσι, εντάξει. Αλλά τώρα ο στόχος εδώ είναι να βεβαιωθούμε ότι βλέπουμε αυτά τα σύνολα μόνο όταν φτάσουμε στο διάλειμμα της σελίδας. Εντάξει, είναι εκεί. Αυτό είναι μια αυτόματη αλλαγή σελίδας και αργότερα όταν αλλάζουμε από το τέλος του Α στο Β, μια χειροκίνητη αλλαγή σελίδας Έτσι, αυτή η μη αυτόματη αλλαγή σελίδας εδώ είναι διαφορετική από μια αυτόματη αλλαγή σελίδας.Αλλά τώρα ο στόχος εδώ είναι να βεβαιωθούμε ότι βλέπουμε αυτά τα σύνολα μόνο όταν φτάσουμε στο διάλειμμα της σελίδας. Εντάξει, είναι εκεί. Αυτό είναι μια αυτόματη αλλαγή σελίδας και αργότερα όταν αλλάζουμε από το τέλος του Α στο Β, μια χειροκίνητη αλλαγή σελίδας. Έτσι, αυτή η μη αυτόματη αλλαγή σελίδας εδώ είναι διαφορετική από μια αυτόματη αλλαγή σελίδας.Αλλά τώρα ο στόχος εδώ είναι να βεβαιωθούμε ότι βλέπουμε αυτά τα σύνολα μόνο όταν φτάσουμε στο διάλειμμα της σελίδας. Εντάξει, είναι εκεί. Αυτό είναι μια αυτόματη αλλαγή σελίδας και αργότερα όταν αλλάζουμε από το τέλος του Α στο Β, μια χειροκίνητη αλλαγή σελίδας. Έτσι, αυτή η μη αυτόματη αλλαγή σελίδας εδώ είναι διαφορετική από μια αυτόματη αλλαγή σελίδας.
Εντάξει, τώρα θα παρατηρήσετε εδώ ότι αυτό το αρχείο αποθηκεύεται ως αρχείο XLSX επειδή έτσι το Excel θέλει να αποθηκεύσει αρχεία. Το XLSX είναι ο κατεστραμμένος τύπος αρχείου που δεν επιτρέπει μακροεντολές, σωστά; Ο χειρότερος τύπος αρχείου στον κόσμο. Επομένως, μην παραλείψετε αυτό το βήμα ή αυτό. Όλη η δουλειά σας από εδώ και έξω θα χαθεί. Αποθήκευση ως και πρόκειται να αποθηκεύσουμε όχι ως βιβλίο εργασίας του Excel αλλά ως βιβλίο εργασίας με δυνατότητα μακροεντολής ή ως δυαδικό βιβλίο εργασίας ή ως XLS. Θα πάω με το βιβλίο εργασίας με δυνατότητα Macro. Εάν δεν το κάνετε αυτό, πρόκειται να χάσετε την υπόλοιπη δουλειά που κάνετε. Εντάξει, και στη συνέχεια, εάν δεν έχετε εκτελέσει μακροεντολές στο παρελθόν, θα κάνουμε δεξί κλικ και θα πούμε Προσαρμογή της κορδέλας. Εδώ στη δεξιά πλευρά, επιλέξτε το πλαίσιο για προγραμματιστή, που θα σας φέρει μια καρτέλα προγραμματιστή. Μόλις έχετε την καρτέλα Προγραμματιστής, μπορούμε να μεταβούμε στο Macro Security,από προεπιλογή θα είναι εδώ Απενεργοποίηση όλων των μακροεντολών και μην μου πείτε ότι έχετε απενεργοποιήσει ολόκληρες τις μακροεντολές. Θέλετε να μεταβείτε στο δεύτερο, με αυτόν τον τρόπο όταν ανοίγουμε το αρχείο, θα πούμε, "Γεια, υπάρχουν μακροεντολές εδώ. Τα δημιουργήσατε; Είσαι εντάξει με αυτό; " Και μπορείτε να πείτε, Ενεργοποιήστε τις μακροεντολές. Εντάξει, κάντε κλικ στο OK.
Τώρα, θα μεταβούμε στον οπτικό βασικό επεξεργαστή. Εάν δεν έχετε χρησιμοποιήσει ποτέ οπτικό βασικό στο παρελθόν, θα ξεκινήσετε με αυτήν την εντελώς γκρίζα οθόνη, μεταβείτε στο View και στο Project Explorer. Ακολουθεί μια λίστα με όλα τα ανοιχτά βιβλία εργασίας. Έχω λοιπόν το πρόσθετο Solver, το προσωπικό μου βιβλίο εργασίας μακροεντολών και εδώ είναι το βιβλίο εργασίας στο οποίο εργάζομαι. Βεβαιωθείτε ότι αυτό το βιβλίο εργασίας είναι επιλεγμένο, κάντε Εισαγωγή, Ενότητα. Εισαγάγετε, η ενότητα θα πάρει ένα ωραίο μεγάλο κενό, λευκό καμβά εδώ. Εντάξει, και μετά θα πληκτρολογήσετε αυτόν τον κωδικό. Εντάξει τώρα, χρησιμοποιούμε ένα αντικείμενο που ονομάζεται HPageBreak, μια οριζόντια αλλαγή σελίδας. Και επειδή δεν το χρησιμοποιώ πολύ, έπρεπε να το δηλώσω εδώ ως μεταβλητή, ως αντικείμενο HPB, με αυτόν τον τρόπο θα μπορούσα να δω τις επιλογές που μου είναι διαθέσιμες σε κάθε μία. Καλώς,μάθετε πού βρίσκεται η τελευταία σειρά με δεδομένα, οπότε χρησιμοποιώ τη στήλη Α, πηγαίνω στο τέλος της στήλης Α - A1048576. Αυτό είναι L εδώ και όχι 1, αυτό είναι L. Όλοι το βιδώνουν. L όπως στο Excel. Ακούγεται σαν το Excel. Λήψη; Excel επάνω. Έτσι, μεταβείτε στο A1048576, πατήστε το πλήκτρο τερματισμού και το πλήκτρο επάνω βέλους για να φτάσετε στην τελευταία σειρά. Βρείτε ποια είναι η σειρά. Και, στη συνέχεια, στις στήλες G και H, και αν το παρακολουθείτε, πρέπει να ρίξετε μια ματιά στα δεδομένα του Excel και να μάθετε πού βρίσκονται οι δύο νέες στήλες σας, εντάξει. Δεν ξέρω πόσες στήλες έχετε. Ίσως οι νέες στήλες σας έχουν τελειώσει στο I και J, ή ίσως είναι στο C και D. Δεν ξέρω, καταλάβω πού είναι και θα κρύψουμε όλες αυτές τις σειρές, εντάξει. Έτσι, στην περίπτωσή μου, ξεκίνησε από το G6, αυτό είναι το πρώτο μέρος όπου έχουμε έναν αριθμό:H και μετά ολοκληρώνω την τελευταία σειρά που έχουμε σήμερα χρησιμοποιώντας μια αριθμητική μορφή τριών ερωτηματικών που θα κρύψει τα δεδομένα.
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.
Ω, γεια, θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2058.xlsm
