Excel VBA Macro за сортиране на данни. Не ми харесва как макрорекордерът създава допълнителен код за сортиране. Сортирането в Excel VBA трябва да бъде лесно. Един ред код, с колоната за сортиране, по какъв начин (възходящ или низходящ) и има ли заглавие.
Гледам видео
- Рекордерът на макроси на Excel не се справя добре със сортирането на записи.
- При условие, че данните ви могат да бъдат избрани чрез Ctrl + * (известен като текущия регион)
- При условие, че не сортирате по цвят или икона или повече от три нива
- Използвайте метода на старото училище Range (). CurrentRegion.Sort в Excel
Видео транскрипция
Научете Excel от MrExcel Podcast, епизод 2093: Сортиране с VBA
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Днешният въпрос от Джеймс в Хънтсвил. Джеймс, използвах макрорекордера, за да запиша действието на сортиране на данни. Така че, да кажем, че Джеймс трябваше да сортира тези данни по сектори и клиенти. И така, излизате тук в раздела Изглед, Макроси, Записване на нов макрос, SortMyReports, Клавишна комбинация Ctrl - ще напиша там Shift + S и щракнете върху OK. Добре, тогава от тук правим: Данни, Сортиране и искаме да кажем, че искаме да сортираме по сектор и след това да добавим ниво и да сортираме по клиент и да щракнем върху OK. Долу в долния ляв ъгъл щракваме Stop Recording. Добре, ето го. Изглежда работеше, нали?
Но тук е проблемът: утре ще имате повече данни или по-малко данни или, знаете ли, каквото и да било. И този записан макрос е просто ужасен. Нека да разгледаме, ще направя Alt + F8 и ще разгледам SortMyReport, ще редактирам това. Добре, и това е всичко, което те записаха, така че SortFields.Clear, и след това те създадоха нов сорт с SortFields.Add и те са кодирани твърдо, има 568 реда и всички неща.
Сега подреждайки назад през деня, бях наистина много лесен. Добре, а след това в Excel 2007 те добавиха Сортиране по икона, Сортиране по цвят, Сортиране по цвят на шрифта, възможността да има 15 нива на сортиране и всичко просто наистина, наистина лудо. Така че, вече не използвам записания макрос. Просто отивам в олдскул.
Сега ще се върна към Excel. Ето правилата за това сортиране от старата школа да работи, добре. Заглавие над всяка колона: това заглавие трябва да е на един ред, а не на два реда. Ако имате заглавия там горе и е добре да имате заглавия там. Трябва ви напълно празен ред между заглавията и първото заглавие. Ако имате бележки от дясната страна: жена ви се обажда със списък с хранителни стоки: „Хей, скъпа, спри на път за вкъщи. Вземете мляко, яйца и водка. " Трябва да има напълно празна колона между вашите данни и това. И ако в долната част има бележки за шаблон, уверете се, че между последния бит данни и тези бележки има напълно празен ред.
Цялата ми цел е да можем да стигнем до която и да е клетка: клетката в горния ляв ъгъл тези данни и натиснете Ctrl + * и тя ще избере данните за сортиране. Сега ще натисна Ctrl +. което ни отвежда до този ъгъл и след това Ctrl +. ще ни отведе до долния десен ъгъл, Ctrl +. ни отвежда в долния ляв ъгъл. Добре, така че ако Ctrl + * правилно избере вашите данни, всичко е чудесно. Ако поставите списъка си с хранителни стоки в колона H и ние видим, че идваме тук и Ctrl + *, добре, сега сортираме списъка с хранителни стоки като част от нещата и вашият списък с хранителни стоки ще се обърка. Или ще отменим: ако този ред не е тук, сега правим Ctrl + *, вижте ние - сега те ще бъдат безразборни, защото вече нямат никакви заглавия, нали?
Така че, ако ще използвате моя код, уверете се, че всички тези правила са верни: няма сортиране на цвета ми, няма сортиране на моята икона, 3 или по-малко нива на сортиране. Отмени, добре. И така, ето какво знаем: знаем, че всеки ден данните ни ще започват в A5. Ако не знаем колко реда или колко - добре, дори колко колони можем да имаме. Не мога да си представя ситуация, в която колоните да се разменят, но със сигурност броят на редовете ще се промени. Така че Alt + F11, просто ще започнем от тази клетка в горния ляв ъгъл. Така че Range, в моя случай е "A5" .CurrentRegion. Текущият регион е това страхотно свойство на сградата, което казва, че ще натиснем Ctrl + Shift + * и всичко, което е включено там, ще бъде сортирано. И ние го правим. .Сортирай, добре.
Ето какво е това. Ако искате да направите сортирането на едно ниво е лесно: Key1: =. : = и ние просто казваме, че ще бъде Range - О, забравям какво е. Беше Сектор, къде е Сектор? Секторът е в колона С. Така че C5 в моя случай, Range („C5“) и след това, Order1: = xlAscending. Натиснах там стрелката надолу и след това Tab. Добре сега, можех да продължа да излизам вдясно, но няма да го правя. Ще отида на нов ред, така че интервалът, подчертаване, за да отидете на нов ред, продължава този ред на код, нали? И ако имам сортиране от второ ниво: Key2: = и в този случай искам да сортирам по клиент, който е в колона D, така че D5. И тогава, Order2: xlAscending. Красив.
Нямам сортиране от трето ниво, но ако го имате, това ще бъде Key3 и след това Order3. И тогава този следващ, този, който трябва да направите, е Header, нали? И така, Header: = xlGuess, тук ще получите много проблеми. И така ще кажем xlYes там, определено като Header. Дори в старите времена, Macro Recorder би използвал xlGuess. Мразя Excel да се досеща.
Това е. Един ред код, това е всичко, което трябва да направите и ще работи с повече редове, по-малко редове. Това е красиво, красиво нещо. Добре, така че ще се върнем тук в Excel. Ctrl + Shift + S все още е нещо, което е присвоено. Сега - Ако току-що преминахте към VBA и въведете това сами, можете да отидете на Alt + F8, да намерите името на вашия макрос, да щракнете върху Опции и да въведете Ctrl + Shift + S там или дори да го присвоим на Клавиш за бърз достъп тук на лентата с инструменти за бърз достъп. Щракнете с десния бутон, Персонализирайте лентата с инструменти за бърз достъп, където избирам от нашите макроси. Имам макрос, наречен SortMyReport, щракнете върху Добавяне - мразя малката схема на процеса там. Ще модифицираме това и бих се радвал там да има някаква ситуация от А до Я, но разбира се няма. Може би онази стрелка, която знае, кой знае, просто изберете нещо.Вълшебната 8-топка, не знам. Ще избера този малък човек тук, щракнете върху OK, щракнете върху OK. Добре, така че сега нашите данни са сортирани по дата, аз избирам - и няма значение какво ще избера. Винаги ще се връща назад и ще го сортира от A5, щраквам върху малкото момче и данните ми вече са сортирани по сектори, в сектор, по клиент. Той работи чудесно, нали?
Така че, ако сте фен на Macro Recorder, искрено ви пожелавам най-сърдечно. Но кодът на Macro Recorder в наши дни за сортиране-сортиране във VBA е просто много по-опростен; за да се върнете назад, просто използвайте по същество този, един ред код.
Е, това обикновено е мястото, където се опитвам да ви накарам да купите тази книга, но днес мисля, че трябва да разгледате тази книга: Excel 2016 VBA и макроси от Трейси и аз. Еха! Виж това. Не разбрах, че има версия на друг език. Ще ви изведем изцяло нагоре по кривата на обучение на макроси от записа на първия ви макрос до кода, от който се нуждаете.
Е, просто обобщение за днес: Excel Macro Recorder не върши добра работа със запис, сортиране: при условие че данните ви могат да бъдат избрани с помощта на Ctrl + *, който е известен като текущия регион, при условие че не сортирате като цвят или икона или повече от три нива, просто използвайте метода на старото училище Range (). CurrentRegion.Sort в VBA, за да сортирате.
Искам да благодаря на Джеймс за изпращането на този въпрос. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2093.xlsm
