Гуин има 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, череша в E1, … Фигура в H1, след това Guava, започваща в C2 и т.н. Тогава отговорих на въпроса, използвайки формули. Можете да гледате това старо видео: тук.
Първата стъпка е да разберете колко реда се побират на вашата отпечатана страница. Не пропускайте тази стъпка. Преди да започнете с макроса, трябва да направите всички тези неща:
- Задайте полетата в раздела Page Layout на лентата
- Ако искате заглавията ви от ред 1 да се повтарят на всяка страница, използвайте Layout Page, Rews to Repeat at Top и посочете 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 изберете Insert, Module
-
Копирайте следния код и го поставете в прозореца на кода
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. Ако вашите данни започват в колона C вместо колона A, бихте променили това:
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 реда заглавия, вашите данни ще започват в A4. Променете този ред:
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 или щракнете върху иконата Run, както е показано по-долу.
Върнете се обратно в Excel. Трябва да видите резултати като този:
Уверете се, че фамилното име на страница 1 колона E е правилно последвано от името в страница 1 колона I.
Гледам видео
Тези стъпки са обяснени в това видео:
Видео транскрипция
Научете Excel за подкаст, епизод 2194: Опаковане на колони.
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Днешният въпрос, изпратен от Гуен. Гуен гледа видео 984, което се нарича Sneaming Columns. Това е от преди години и всъщност използвах формула, за да го реша тогава, но този проблем с близнаците е по-сложен.
Така тя има работен лист с три колони с около 15 000 реда. и трябва да направи всяка страница шест колони. И така, на първата страница, тези 60 клетки; и след това до него, следващите 60 клетки. Сега Гуен разбра, че може да побере около 60 реда. Но за всеки друг, който гледа това, най-важната част тук е да разберете колко реда, защото наистина ще объркате нещата, ако направите някоя от тези промени след факта.
Добре, така че за мен това, което ще направя, е да дойда тук за оформление на страницата, ще заявя, че тези седем колони ще бъдат моята област за печат - Област за печат, Задаване на област за печат Ще отида в Print Titles и ще кажа, че „Редове за повторение отгоре“ е 1: 1. Ще отида … Всъщност бих искал да използвам Margins тук - Margins, Narrow и след това обратно в Page Setup, Header / Footer и да избера какъвто и да е моят, знаете, Custom Footer трябва да бъде - Поверително . Направете всички тези настройки, всичко, което някога ще промените първо. Добре? Защото това ще промени броя на редовете на страница.
Сега ще напиша номер 1 тук, това просто ще бъдат някои временни данни. Ще задържа клавиша Ctrl и ще хвана дръжката за попълване и ще сляза, докато не се уверя, че съм минал първата страница по този начин. И тогава просто ще направим предварителен преглед - Ctrl + P, Показване на предварителен преглед - и ще забележите, че имам 46 реда, които се побират на първата страница. И нека просто проверим, отидете на втората страница - така 46 плюс 46 е 92, така че получаваме 46 реда на страница, 46 реда на страница. Това число е невероятно важно - 46. Всъщност ще го запиша тук, само за да не забравя - 46 реда на страница.
Добре, сега ще реша това днес с макрос; обратно във видео 984, използвах някои сложни формули, за да го направя, но днес един макрос се чувства по-добре. Ако никога преди не сте използвали макроси, не се плашете. Ето как започваме: Натискаме Alt + F11 - Alt + F11 - това отваря този екран и всъщност, първия път, когато отворите Alt + F11, това ще бъде просто голям сив екран - вероятно много като това, като онова. Така че искате да кажете: View, Project Explorer, Намерете вашата работна книга тук и кажете Insert Module - Вече го направих - и това, което ще получим - и това, което получаваме - е бял екран. И тук, в този бял екран, ще въведете този код, нали? Думата "Sub", което означава, че това е подпрограма, и след това всяко име, което искате - аз го наричам WrapThem, там няма интервали,така че просто задръжте всичко заедно - отворена и затваряща скоба. След това ще създадем променлива: FinalRow = Cells (Rows.Count, 1). End и тези четири букви тук са XL, а не X1 - всички го прецакват, XL. И можете да го въведете във всички главни букви, ако искате, но те ще го върнат към този формат, където L изглежда като 1 - не поставяйте 1 там. Rows.Per.Page - и тук поставяте каквото число сте измислили. Сега за мен е 46; за Гуен звучи като 60. И след това следващият ред, където искаме да отидат първите данни, е Ред 2, а след това следващата колона - 1, 2, 3, 4, 5 - е Колона 5.И можете да го въведете във всички главни букви, ако искате, но те ще го върнат към този формат, където L изглежда като 1 - не поставяйте 1 там. Rows.Per.Page - и тук поставяте каквото число сте измислили. Сега за мен е 46; за Гуен звучи като 60. И след това следващият ред, където искаме да отидат първите данни, е Ред 2, а след това следващата колона - 1, 2, 3, 4, 5 - е Колона 5.И можете да го въведете във всички главни букви, ако искате, но те ще го върнат към този формат, където L изглежда като 1 - не поставяйте 1 там. Rows.Per.Page - и тук поставяте каквото число сте измислили. Сега за мен е 46; за Гуен звучи като 60. И след това следващият ред, където искаме да отидат първите данни, е Ред 2, а след това следващата колона - 1, 2, 3, 4, 5 - е Колона 5.
Добре, затова го настроих. И тогава, останалата част от това ще бъде много, много родово. ще работи с, знаете ли, всякакъв набор от данни за размер: For I (това е променлива) = 2 To FinalRow (това е колко редове имахме) Стъпка (това означава всеки път през цикъла, който ще увеличим с) RowsPerPage (което в случая е 46, за случая на Гуен ще бъде 60). Ще кажем: Клетки (NextRow, NextCol) - така че следващият ред ще бъде 2, колона 5--. и това е подчертаване там) Ще бъде равно на клетки (1, 1) - така че каквото и да е в ред 2 запетая 1, колона 1 -. И тогава, това, което трябва да направим, е, че трябва да сме малко умни тук, след като поставим първите 46 по 46 реда, по 3 колони.
Къде да отидем по-нататък? Там, нали? Така че, ако в момента следващата колона сочи към колона E, добре, тогава следващата ми трябва да отида до колона I. Аз съм деветата колона. Добре. Затова казваме NextCol = 5. Но ако не сме … NextCol = 5, това означава, че NextCol = 9. След това ще върнем следващата група обратно в колона E и NextRow ще бъде = каквото и да е предишният ред беше, + 46. И след това следващия път … сега тук, нека просто преминем през това, не е нужно да го изпълнявате една стъпка наведнъж. Но ще направя това с F8 - само за да видя какво ще получим тук.
И така, това, което научихме, е, че последният ред е реален - 15 582. Предстои да напишем на ред 2, колона 5. И така: За I = 2 към FinalRow. Първият път през, аз ще бъда равен на 2. Ще кажем, че ред 2, колона 5, ще бъде равен на ред 2, колона 1 - 46 реда, 3 колони. Искам да стартирам това с F8. Ще погледнем тук в електронната таблица и ще видим, че се оказа, че първите 46 са дошли в тази област. Добре. Но ще оставим това да работи отново. Добре.
Сега, втори път през цикъла, аз скочих от 2 на 48. Добре. И така, този път ще тичаме към ред 2, колона 9 и ще получаваме данни от ред 48. Добре, сега нека проверим това тук. И така, това, което виждаме, е Анди Хартли - това работи чудесно - тук долу в края, Кели Фъргюсън. Но следващият човек трябва да бъде Lue Rahman - Rahman - и това работи, и то отива до Lue Harvey, точно там. Добре. Сега това, което се надяваме следващия път, е да вземем Барб Дейвисън. Ще натисна F8 още няколко пъти, ето следващия и поглеждаме и сега пише в ред 48. Добре. И това е Барб Дейвисън и изглежда работи. В този момент съм доволен от него, просто ще щракна тичам.
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.
Хей, искам да благодаря на Гуен за изпратения въпрос, искам да ти благодаря, че се отби. Ще се видим следващия път за поредното излъчване от.
