Η Gwynne έχει 15 χιλιάδες σειρές δεδομένων σε τρεις στήλες. Θα ήθελε να εκτυπώσει τα δεδομένα με 6 στήλες ανά σελίδα. Για παράδειγμα, τα πρώτα 50 ονόματα στο A2: C51 και στη συνέχεια τα επόμενα 50 ονόματα στο E2: G51. Στη συνέχεια, μετακινήστε τις τρίτες 50 σειρές στο A52: C101 και ούτω καθεξής.
Αντί να το λύσω με τύπους, θα χρησιμοποιήσω λίγο Excel VBA για να αναδιατάξω τα δεδομένα.
Η μακροεντολή VBA θα αφήσει τα δεδομένα στο A: C. Θα εμφανιστεί μια κενή στήλη στο D. Τα νέα δεδομένα θα εμφανίζονται στο D: F, κενή στήλη στο G, νέα δεδομένα στο H: J.
Σημείωση
Πριν από σχεδόν 10 χρόνια, απάντησα σε μια ερώτηση για το πώς να φτιάξω 1 στήλη σε 6 στήλες. Στην περίπτωση αυτή, τα δεδομένα ταξινομήθηκαν οριζόντια, με την Apple στο C1, την μπανάνα στο D1, το Cherry στο E1,… το Fig στο H1, μετά τη Γκουάβα ξεκινώντας από το C2 και ούτω καθεξής. Τότε, απάντησα στην ερώτηση χρησιμοποιώντας τύπους. Μπορείτε να παρακολουθήσετε αυτό το παλιό βίντεο: εδώ.
Το πρώτο βήμα είναι να μάθετε πόσες σειρές ταιριάζουν στην εκτυπωμένη σελίδα σας. Μην παραλείψετε αυτό το βήμα. Πριν ξεκινήσετε με τη μακροεντολή, πρέπει να κάνετε όλα αυτά τα πράγματα:
- Ορίστε τα περιθώρια στην καρτέλα Διάταξη σελίδας της Κορδέλας
- Εάν θέλετε οι επικεφαλίδες σας από τη σειρά 1 να επαναλαμβάνονται σε κάθε σελίδα, χρησιμοποιήστε τη διάταξη σελίδας, τις σειρές για επανάληψη στην κορυφή και καθορίστε το 1: 1
- Καθορίστε τυχόν κεφαλίδες και υποσέλιδα που θα εμφανίζονται σε κάθε σελίδα.
- Αντιγράψτε τις επικεφαλίδες από A1: C1 έως E1: G1.
- Αντιγράψτε τις επικεφαλίδες από A1: C1 έως I1: K1.
- Καθορίστε το E: K ως το εύρος εκτύπωσης
- Συμπληρώστε τους αριθμούς 1 έως 100 στο E2: E101 με
=ROW()-1
Μόλις όλες οι ρυθμίσεις της σελίδας σας είναι σωστές, χρησιμοποιήστε το Ctrl + P για να εμφανίσετε το έγγραφο Προεπισκόπηση εκτύπωσης. Εάν είναι απαραίτητο, κάντε κλικ στο πλακίδιο Εμφάνιση προεπισκόπησης εκτύπωσης στο μέσο της οθόνης. Στην προεπισκόπηση εκτύπωσης, βρείτε τον τελευταίο αριθμό σειράς στη σελίδα 1. Στην περίπτωσή μου, είναι 46. Αυτός θα είναι ένας σημαντικός αριθμός στο μέλλον.
Για να δημιουργήσετε τη μακροεντολή, ακολουθήστε τα εξής βήματα:
- Αποθηκεύστε το βιβλίο εργασίας σας ως νέο όνομα σε περίπτωση που κάτι πάει στραβά. Για παράδειγμα: MyWorkbookTestCopy.xlsx
- Πατήστε alt = "" + F11 για να ανοίξετε το πρόγραμμα επεξεργασίας VBA
- Από το μενού VBA, επιλέξτε Εισαγωγή, Ενότητα
-
Αντιγράψτε τον παρακάτω κώδικα και επικολλήστε στο παράθυρο κώδικα
Sub WrapThem() ' the following line says XLUP not x1up ! FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' Change 46 to match your Rows Per Page RowsPerPage = 46 NextRow = 2 NextCol = 5 For i = 2 To FinalRow Step RowsPerPage Cells(NextRow, NextCol).Resize(RowsPerPage, 3).Value = _ Cells(i, 1).Resize(RowsPerPage, 3).Value If NextCol = 5 Then NextCol = 9 Else NextCol = 5 NextRow = NextRow + RowsPerPage End If Next i End Sub - Βρείτε τη γραμμή που λέει
RowsPerPage = 46και αντικαταστήστε τις 46 με τον αριθμό σειρών που βρήκατε στην προεπισκόπηση εκτύπωσης.
Ακολουθούν μερικά άλλα πράγματα που ίσως χρειαστεί να αλλάξετε ανάλογα με τα δεδομένα σας:
Η FinalRow =γραμμή αναζητά την τελευταία καταχώρηση στη στήλη 1. Εάν τα δεδομένα σας ξεκίνησαν στη στήλη Γ αντί στη στήλη Α, θα αλλάζατε αυτό:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Rowσ 'αυτό
FinalRow = Cells(Rows.Count, 3).End(xlUp).RowΣε αυτό το παράδειγμα, η πρώτη θέση για τα νέα δεδομένα θα είναι το κελί E2. Αυτή είναι η σειρά 2, στήλη 5. Εάν έχετε πέντε γραμμές τίτλων και τα νέα σας δεδομένα πρόκειται να ξεκινήσουν στο G6, θα αλλάζατε NextRow = 2σε NextRow = 6. Αλλαγή NextCol = 5σε NextCol = 7(επειδή η στήλη G είναι η 7η στήλη).
Σε αυτό το παράδειγμα, τα δεδομένα ξεκινούν σε Α2 (αμέσως μετά τις επικεφαλίδες στη σειρά 1). Εάν έχετε 3 γραμμές επικεφαλίδων, τα δεδομένα σας θα ξεκινούν από το Α4. Αλλαγή αυτής της γραμμής:
For i = 2 To FinalRow Step RowsPerPageσε αυτήν τη γραμμή:
For i = 4 To FinalRow Step RowsPerPageΟι στήλες εξόδου μου εμφανίζονται στη στήλη E (5η στήλη) και στη στήλη I (9η στήλη). Ας υποθέσουμε ότι έχετε τέσσερις στήλες δεδομένων. Τα αρχικά δεδομένα είναι σε B: E. Τοποθετήστε το πρώτο σετ στηλών σε G: J και L: O. Το G είναι η 7η στήλη. Το L είναι η 12η στήλη. Στο ακόλουθο κείμενο, αλλάξτε 3 σε 4 σε δύο θέσεις, επειδή έχετε 4 στήλες αντί για 3. Αλλάξτε 5 έως 7 σε δύο θέσεις, επειδή η πρώτη στήλη εξόδου είναι G αντί για E. Αλλάξτε 9 έως 12, επειδή η δεύτερη στήλη εξόδου είναι L αντί για Ι.
Αλλάξτε αυτό:
Cells(NextRow, NextCol).Resize(RowsPerPage, 3).Value = _ Cells(i, 1).Resize(RowsPerPage, 3).Value If NextCol = 5 Then NextCol = 9 Else NextCol = 5 NextRow = NextRow + RowsPerPage End Ifσ 'αυτό:
Cells(NextRow, NextCol).Resize(RowsPerPage, 4).Value = _ Cells(i, 1).Resize(RowsPerPage, 4).Value If NextCol = 7 Then NextCol = 12 Else NextCol = 7 NextRow = NextRow + RowsPerPage End IfΤώρα είστε έτοιμοι να εκτελέσετε τη μακροεντολή. Αποθηκεύστε το βιβλίο εργασίας για τελευταία φορά.
Στο παράθυρο VBA, κάντε κλικ οπουδήποτε μέσα στη μακροεντολή. Στο παρακάτω σχήμα, ο δρομέας είναι αμέσως μετά Sub WrapThem(). Κάντε κλικ στο πλήκτρο F5 ή κάντε κλικ στο εικονίδιο Εκτέλεση όπως φαίνεται παρακάτω.
Επιστροφή στο Excel. Θα πρέπει να βλέπετε αποτελέσματα ως εξής:
Βεβαιωθείτε ότι το επώνυμο στη σελίδα 1 στη στήλη Ε ακολουθείται σωστά από το πρώτο όνομα στη σελίδα 1 στήλη I.
Δες το βίντεο
Αυτά τα βήματα εξηγούνται σε αυτό το βίντεο:
Μεταγραφή βίντεο
Μάθετε το Excel για Podcast, επεισόδιο 2194: Αναδίπλωση στηλών.
Γεια σας, καλώς ήρθατε στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση, που εστάλη από τον Gwen. Ο Gwen παρακολουθεί το βίντεο 984, το οποίο ονομάστηκε Sneaking Columns. Αυτό είναι από χρόνια πριν και στην πραγματικότητα χρησιμοποίησα έναν τύπο για να το λύσω τότε, αλλά αυτό το πρόβλημα των διδύμων είναι πιο περίπλοκο.
So she has a three column worksheet with around 15,000 rows. and needs to make each page six columns. So, on the first page, these 60 cells; and then next to it, the next 60 cells. Now, Gwen has figured out that she can fit about 60 rows. But for anyone else watching this, the most important part here is to figure out how many rows because you'll really screw things up if you make any of these changes after the fact.
Alright, so for me, what I'm going to do is I'm going to come here to page layout, I'm gonna declare that these seven columns are going to be my print area-- Print Area, Set Print Area. I'm going to go into Print Titles and say that “Rows to repeat at top” is 1:1. I'm going to go… Actually, I'd like to use Margins here-- Margins, Narrow, and then back in the Page Setup, Header/Footer, and choose whatever my, you know, Custom Footer should be-- Confidential. Do all of the those settings, anything you're ever going to change first. Alright? Because that's going to change the number of rows per page.
Now, I'm going to type in the number 1 here, this is just going to be some temporary data. I'm going to hold down the Ctrl key and grab the Fill handle, and go down until I'm sure I'm past the first page like that. And then, we'll just do a Print Preview-- Ctrl+P, Show Print Preview-- and you'll notice that I have 46 rows that fit on the first page. And let's just check, go to the second page-- so 46 plus 46 is 92, so we're getting 46 rows per page, 46 rows per page. That number is incredibly important-- 46. In fact, I'm going to write it down over here just so I don't forget-- 46 rows per page.
Alright, now, I'm going to solve this today with a Macro; back in video 984, I used some complex formulas to do it, but today a macro feels better. If you've never used macros before don't be intimidated. Here's how we start: We press Alt+F11-- Alt+F11-- that brings open this screen and actually, the very first time that you open Alt+F11, it's going to be just a big gray screen-- probably a lot like this-- like that. So you want to say, View, Project Explorer, Find your workbook here, and say Insert Module-- I've already done that-- and what we'll get-- and what we get-- is a white screen. And over here in this white screen, you're going to type this code, alright? The word "Sub" which means that this is a subroutine, and then any naming you want-- I call it WrapThem, no spaces there, so just jam everything together-- open and closing parenthesis. Then we're we're going to create a variable: FinalRow = Cells(Rows.Count, 1).End, and these four letters here are XL, not X1-- everybody screws this up, XL. And you can type it in all caps if you want but they're going to change it back to that format where the L looks like a 1-- don't put a 1 there. Rows.Per.Page-- and this is where you put whatever number you figured out. Now, for me it's 46; for Gwen, it sounds like it's 60. And then, the next row where we want the first data to go is Row 2, and then the next column-- 1, 2, 3, 4, 5-- is Column 5.
Alright, so I set this up. And then, the rest of this is going to be very, very generic. it's going to work with, you know, any size data set: For I (it's a variable) = 2 To FinalRow (that's how many rows we had) Step (that means every time through the loop we're going to increase by) RowsPerPage (which in this case is 46, for Gwen's case it's going be 60). We're going to say: Cells(NextRow, NextCol) -- so, next row's going to be 2, Column 5-- .Resize(RowsPerPage, 3) -- resize 46 rows, 3 columns-- .Value = _ (and that's an underscore there) It's going to be equal to Cells(1, 1) -- so whatever is in Row 2 comma 1, Column 1-- .Resize(RowsPerPage, 3).Value. And then, what we have to do is, we have to be a little bit clever here about after we paste the first 46 times 46 rows, by 3 columns.
Where do we go next? There, right? So, if currently, the next column is pointing to Column E, well, then I need the next one to go to Column I. I is the ninth column. Alright. So that's why we say NextCol = 5. But if we're not… NextCol = 5 that means our NextCol = 9. Then we're going to reset the next group back to Column E and the NextRow is going to be = whatever the previous row was, + 46. And then next time… now here, let's just walk through this, you don't have to run it one step at a time. But I'm going to do that with F8-- just to see what we get here.
And so, what we've learned, is the final row is real-- 15,582. We're about to write to row 2, column 5. And so: For I = 2 To FinalRow. The first time through, I is going to be equal to 2. We're going to say that Row 2, Column 5, is going to be equal to Row 2, Column 1-- 46 rows, 3 columns. I want to run this with F8. We'll look over here in the spreadsheet and we'll see that it turned out those first 46 came to this area. Alright. But, we're going to let this run again. Alright.
Now, the second time through the loop, the I has jumped up from 2 to 48. Alright. And so this time, we're going to be running to Row 2, Column 9, and we're going to be getting data from Row 48. Alright, now let's go check this one right here. So, what we see is Andy Hartley-- that works great-- down here at the end, Kelly Ferguson. But the next person should be Lue Rahman-- Rahman-- and that works, and it goes down to Lue Harvey, right there. Alright. Now, what we're hoping next time, is we get Barb Davison. I'll press F8 few more times, here's the next one and we look, and it's now writing to Row 48. Alright. And it's Barb Davison, and it appears to be working. At this point, I'm happy with it, I'm just going to click run.
And, actually, you don't have to go-- if you're not creating a video to explain this to somebody-- you don't have to go through and press F8; you could just come up here, click inside WrapThem, click run, and that fast it will take your data and wrap it into two columns.
Now, some things I see here-- Surname isn't wide enough, that should not affect our page layout, I'm hoping. And when I do Print Preview, I now have 170 pages. Data there, Page 2, Page 3, Page 4. Now, if we would change the margins at this point, everything is going to be screwed up-- it's going to be horrible. That's why it's really, really important, right up front, you have to do all of your page layout things before you calculate that 46. Now, of course, at this point, Save your workbook with a new name, alright? We don't want to destroy the personal workbook. And then you can delete columns A through D, and you have your results.
Now, if you want to learn about macros-- macros are incredibly powerful-- we probably could have solved this with a formula. And, certainly, the me from 10 years ago solved it with a formula, but at this point in my life, just a simple little 15 line macro is a lot easier. This book, by Tracy Syrstad and myself, will teach you all about macros.
Alright, wrap-up for this Episode: How to wrap 3 columns of data in 2 sets of columns per page. The super important step, you have to do all the page setup things first, Rows to Repeat at Top, Margins, Header/Footer, and then just type some numbers-- 1 through whatever-- I use the Fill handle with control; go to Print Preview, How many rows per page; switch over to Alt+F11; Insert a module and then type the code that I showed you in the video; click run. And most of the time, I advise people to save your workbook as xlsm, but in this case this was a one-time thing, I'm suspecting. So if you're, you know, just want to have that macro disappear, keep it as xlsx, save the file, it'll warn you that you're about to lose your macro. That's probably okay, because we've solved the problem well.
Hey, I want to thank Gwen for sending that question in, I want to thank you for stopping by. We'll see you next time for another netcast from.
