Сортиране на данни в Excel VBA
Excel има отлично средство за сортиране на диапазон от таблични данни, използвайки лентата на предния край на Excel и в един момент вероятно ще искате да използвате тази функционалност във вашия VBA код. За щастие, това е много лесно да се направи.
Диалоговият прозорец отпред се намира, като щракнете върху иконата „Сортиране“ в групата „Сортиране и филтриране“ на раздела „Данни“ на лентата на Excel. Първо трябва да изберете диапазон от таблични данни.
Можете също да използвате Alt-A-S-S, за да покажете диалоговия прозорец за персонализирано сортиране.
Методът за сортиране е значително подобрен в по -новите версии на Excel. Сортирането по -рано беше ограничено до три нива, но сега можете да въведете толкова нива, колкото ви е необходимо, и това важи и в рамките на VBA.
Можете да включите всички предлагани функции за сортиране в диалоговия прозорец Сортиране на Excel във вашия VBA код. Функцията за сортиране в Excel е бърза и по -бърза от всичко, което можете да напишете сами във VBA, така че се възползвайте от функционалността.
Имайте предвид, че когато правите сортиране във VBA, параметрите за сортиране остават същите в диалоговия прозорец за сортиране отпред. Те също се запазват при запазване на работната книга.
Ако потребител избере същия диапазон от таблични данни и кликне върху иконата за сортиране, той ще види всичките ви параметри, които са въведени от вашия VBA код. Ако искат да направят нещо като собствен дизайн, тогава ще трябва първо да изтрият всички нива на сортиране, което ще бъде много досадно за тях.
Също така, ако не променяте параметрите в кода си и разчитате на стойности по подразбиране, може да откриете, че потребителят е направил промени, които ще се отразят във вашия VBA сортиране, и може да даде неочаквани резултати, които може да са много трудни за отстраняване на грешки .
За щастие, има метод Clear във VBA, за да зададете отново всички параметри за сортиране, така че потребителят да види чист диалогов прозорец за сортиране
| 1 | Работни листове ("Sheet1"). Sort.SortFields.Clear |
Добра практика е да изчистите параметрите за сортиране във VBA преди и след приключване на сортирането.
Практическо използване на метода за сортиране във VBA
Когато табличните данни се импортират в Excel, те често са в много случаен ред. Може да бъде импортиран от CSV (разделени със запетая стойности) файл или може да дойде от връзка към база данни или уеб страница. Не можете да разчитате, че той е в определен ред от един импорт до друг.
Ако представяте тези данни на потребител във вашия работен лист, на потребителя може да му е трудно да разгледа и разбере огромно количество данни, които по ред са навсякъде. Те може да искат да групират данните или да изрежат и поставят определени части от тях в друго приложение.
Може също да искат да видят например най -високоплатения служител или служителя с най -дълъг стаж.
Използвайки метода Сортиране във VBA, можете да предложите опции, които да позволят лесното сортиране за потребителя.
Примерни данни за демонстриране на сортиране на Excel с VBA
Първо се нуждаем от някои примерни данни, които да бъдат въведени в работен лист, така че кодът да може да демонстрира всички налични средства във VBA.
Копирайте тези данни в работен лист (наречен „Sheet1“) точно както е показано.
Имайте предвид, че са използвани различни цветове на фона на клетките и цветове на шрифта, тъй като те могат да се използват и като параметри за сортиране. Сортирането с помощта на цветове на клетки и шрифтове ще бъде демонстрирано по -късно в статията. Също така имайте предвид, че в клетка E3 името на отдела е с малки букви.
Не се нуждаете от вътрешността на клетката и цветовете на шрифта, ако не искате да използвате примерите за сортиране по цвят на клетката и шрифта.
Записване на макрос за VBA сортиране
VBA кодът за сортиране може да стане доста сложен и понякога може да е добра идея да направите сортирането в предния край на Excel и да запишете макрос, за да ви покаже как работи кодът.
За съжаление, функцията за запис може да генерира огромно количество код, защото задава почти всеки наличен параметър, въпреки че стойностите по подразбиране за много параметри са приемливи за вашата операция за сортиране.
Това обаче ви дава много добра представа за това какво включва писането на код за сортиране на VBA и едно от предимствата е, че записаният код винаги ще работи за вас. Вашият собствен код може да се нуждае от тестване и отстраняване на грешки, за да работи правилно.
Не забравяйте, че за операция, извършена във VBA, няма функция за отмяна, така че е добра идея да направите копие на табличните данни в друг работен лист, преди да започнете да пишете кода за сортиране.
Като пример, ако сте направили просто сортиране на примерните данни по -горе, сортирайки по Employee, записът ще генерира следния код:
| 123456789101112131415161718 | Подмакрос1 ()Обхват ("A1: E6"). ИзберетеActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, Ред: = xlAcecending, DataOption: = xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). СортиранеДиапазон .SetRange ("A1: E6").Хедера = xlДа.MatchCase = False.Ориентация = xlTopToBottom.SortMethod = xlPinYin.ПриложиКрай сEnd Sub |
Това е доста голяма част от кода и голяма част от него е ненужна поради използваните параметри по подразбиране. Ако обаче имате времеви натиск за завършване на проект и имате нужда от бързо работещ код, можете лесно да го поставите във вашия собствен VBA код.
Ако обаче искате да направите кода си разбираем и по -елегантен, тогава има и други опции.
VBA код, за да направите сортиране на едно ниво
Ако искате да сортирате примерния код въз основа на служител само както преди, когато записвате макрос, кодът е много прост:
| 1234567 | Sub SingleLevelSort ()Работни листове ("Sheet1"). Sort.SortFields.ClearДиапазон ("A1: E6"). Клавиш за сортиране1: = Диапазон ("A1"), Заглавие: = xl ДаEnd Sub |
Това е далеч по -лесно за разбиране от записания код, защото приема настройките по подразбиране, например сортиране възходящо, така че няма нужда да задавате параметрите на стойности по подразбиране. Това предполага, че сте използвали предварително изявление „Изчистване“.
Методът „Изчистване“ се използва първоначално, за да се гарантира, че всеки параметър за сортиране за този работен лист се връща към стойностите по подразбиране. Потребителят може предварително да е задал параметрите на различни стойности или по -ранно сортиране във VBA може да ги е променило. Важно е да започнете от позиция по подразбиране, когато сортирате, в противен случай лесно можете да завършите с неправилни резултати.
Методът Clear не нулира параметъра Header и е препоръчително да го включите в кода си, в противен случай Excel може да се опита да отгатне дали има заглавен ред или не.
Пуснете този код срещу примерните данни и вашият работен лист ще изглежда така:
VBA код за многостепенна сортировка
Можете да добавите толкова нива на сортиране, колкото е необходимо в кода ви. Да предположим, че искате да сортирате първо по отдел, а след това по начална дата, но във възходящ ред за отдела и низходящ ред за начална дата:
| 12345678 | Sub MultiLevelSort ()Работни листове ("Sheet1"). Sort.SortFields.ClearДиапазон ("A1: E6"). Клавиш за сортиране1: = Диапазон ("E1"), Ключ2: = Диапазон ("C1"), Заглавие: = xl Да, _Поръчка1: = xlНарастваща, Поръчка2: = xlНаходящаEnd Sub |
Обърнете внимание, че сега в оператора за сортиране има два ключа (Key1 и Key2). Ключ1 (колона на отдел Е) се сортира първо и след това ключ2 (колона Начална дата С) се сортира въз основа на първото сортиране.
Има и два параметъра за поръчка. Order1 асоциира с Key1 (отдел) и Order2 свързва с Key2 (начална дата). Важно е да се гарантира, че ключовете и поръчките се поддържат в крак един с друг.
Пуснете този код срещу примерните данни и вашият работен лист ще изглежда така:
Колоната отдел (E) е във възходящ ред, а колоната Начална дата (C) е в низходящ ред.
Ефектът от този вид е най -забележим, когато погледнете Джейн Халфакър (ред 3) и Джон Съдърланд (ред 4). И двамата са във финансите, но Джейн Халфакър започна преди Джон Съдърланд и датите са показани в низходящ ред.
Ако диапазонът от таблични данни може да бъде с всякаква дължина, можете да използвате обекта UsedRange, за да определите диапазона за сортиране. Това ще работи само ако в работния лист има само табличните данни, тъй като всякакви стойности извън данните ще дадат неправилни резултати за броя редове и колони.
| 1234567 | Sub MultiLevelSort ()Работни листове ("Sheet1"). Sort.SortFields.ClearРаботни листове ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlДа, _Поръчка1: = xlНарастваща, Поръчка2: = xlНаходящаEnd Sub |
Това предотвратява проблема, ако използвате метода „Край (xlDown)“, за да определите диапазона за сортиране. Ако има празна клетка в средата на данните, тогава нищо след празната клетка няма да бъде включено, докато UsedRange се спуска до последната активна клетка в работния лист.
Сортиране по цвят на клетката
От Excel 2007 вече е възможно сортиране по цвета на фона на клетка, което осигурява огромна гъвкавост при проектирането на кода за сортиране във VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Работни листове ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Поръчка: = xlАсцендиращо, DataOption: = xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). СортиранеДиапазон .SetRange ("A1: E6").ПриложиКрай сEnd Sub |
Този код ще сортира диапазона от примерни данни (A2: A6) въз основа на цвета на фона на клетката. Обърнете внимание, че сега има допълнителен параметър, наречен „SortOn“, който има стойността на „xlSortOnCellColor“.
Обърнете внимание, че параметърът „SortOn“ може да се използва само от обект на работен лист, а не от обект на диапазон.
Поради това кодът е по -сложен, отколкото за сортиране, използващо стойности на клетки.
Този код използва ключова стойност за сортирането, която обхваща целия диапазон от данни, но можете да посочите отделни колони като ключ за сортиране на цвета на фона и да използвате множество нива, както е показано по -рано.
След като стартирате този код, вашият работен лист ще изглежда така:
Сортиране по цвят на шрифта
Функцията за сортиране в Excel VBA предлага още по -голяма гъвкавост, тъй като можете да сортирате по цветове на шрифта:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Работни листове ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)С ActiveWorkbook.Worksheets ("Sheet1"). СортиранеДиапазон .SetRange ("A1: E6").Хедера = xlДа.Ориентация = xlTopToBottom.ПриложиКрай сEnd Sub |
Кодът за сортиране по цвят на шрифта е много по -сложен, отколкото за цвета на фона на клетката. Параметърът „SortOn“ сега съдържа стойността на „xlSortOnFontColor“.
Обърнете внимание, че трябва да посочите ориентацията като „xlTopToBottom“ и трябва да посочите цвят, по който да сортирате. Това е посочено в термините RGB (червено, зелено, черно) със стойности от 0 до 255.
След като стартирате този код срещу примерните данни, вашият работен лист ще изглежда така:
Сортирането с помощта на цветове във VBA е много по-сложно от многостепенното сортиране, но ако кодът ви за сортиране няма да работи (което може да се случи, ако параметър липсва или не сте въвели кода правилно), винаги можете да се върнете към записа макрос и интегриране на записания код във вашия VBA.
Използване на други параметри при VBA сортиране
Има редица незадължителни параметри, които можете да използвате във вашия VBA код, за да персонализирате сортирането си.
СОРТИРАНЕ
SortOn избира дали сортирането ще използва стойности на клетки, цветове на фона на клетката или цветове на шрифта на клетката. Настройката по подразбиране е Cell Values.
| 1 | SortOn = xlSortOnValues |
Поръчка
Поръчката избира дали сортирането ще се извършва във възходящ или низходящ ред. По подразбиране е Възходящ.
| 1 | Поръчка = xl Възходящо |
DataOption
DataOption избира как да се сортират текстът и числата. Параметърът xlSortNormal сортира отделно числови и текстови данни. Параметърът xlSortTextAsNumbers третира текста като числови данни за сортирането. По подразбиране е xlSortNormal.
| 1 | DataOption = xlSortNormal |
Заглавка
Header избира дали диапазонът от таблични данни да има ред за заглавие или не. Ако има заглавен ред, не искате това да се включва в сортирането.
Стойностите на параметрите са xlYes, xlNo и xlYesNoGuess. xlYesNoGuess оставя на Excel да определи дали има ред на заглавка, което лесно може да доведе до непоследователни резултати. Използването на тази стойност не се препоръчва.
Стойността по подразбиране е XNo (няма ред за заглавка в данните). При импортирани данни обикновено има ред на заглавка, затова се уверете, че сте задали този параметър на xlДа.
| 1 | Заглавие = xlДа |
MatchCase
Този параметър определя дали сортирането е чувствително към регистъра или не. Стойностите на опциите са True или False. Ако стойността е False, тогава малките стойности се считат за същите като големите. Ако стойността е True, тогава сортирането ще покаже разликата между главни и малки букви в сортирането. Стойността по подразбиране е False.
| 1 | MatchCase = False |
Ориентация
Този параметър определя дали сортирането ще се извърши надолу през редовете или във всички колони. Стойността по подразбиране е xlTopToBottom (сортиране по редове). Можете да използвате xlLeftToRight, ако искате да сортирате хоризонтално. Стойности като xlRows и xlColumns не работят за този параметър.
| 1 | Ориентация = xlTopToBottom |
SortMethod
Този параметър се използва само за сортиране на китайски езици. Той има две стойности, xlPinYin и xlStroke. xlPinYin е стойността по подразбиране.
xlPinYin сортира, използвайки фонетичния ред за сортиране на китайски символи. xlStroke сортира по броя на ударите във всеки знак.
Ако записвате макрос за сортиране, този параметър винаги ще бъде включен в кода и може би сте се чудили какво означава това. Въпреки това, освен ако не се занимавате с данни на китайски, те са малко полезни.
| 1 | SortMethod = xlPinYin |
Използване на събитие с двойно щракване за сортиране на таблични данни
Във всички функционалности, които Microsoft включи в методите за сортиране за VBA, тя не включваше просто средство за двукратно щракване върху заглавието на колона и сортиране на цялата таблична информация въз основа на тази конкретна колона.
Това е наистина полезна функция и е лесно да напишете кода, за да го направите.
| 12345678910111213141516171819202122232425262728293031323334 | Частен под работен лист_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)„Предполага се, че данните започват от клетка А1„Създайте три променливи за улавяне на избраната целева колона и максималната колона и ред от _'табличните данниDim Col As Integer, RCol As Long, RRow As Long„Проверете дали потребителят е щракнал двукратно върху заглавния ред - ред 1, в противен случай излезте от subАко Target.Row 1 След това излезте от Sub„Уловете максималните редове в табличния диапазон от данни, като използвате обекта„ UsedRange “RCol = ActiveSheet.UsedRange.Columns.Count„Уловете максималните колони в табличния диапазон от данни, като използвате обекта„ UsedRange “RRow = ActiveSheet.UsedRange.Rows.Count„Проверете дали потребителят не е щракнал двукратно върху колона извън диапазона от таблични данниАко Target.Column> RCol След това излезте от Sub„Заснемете колоната, върху която потребителят е щракнал двукратноCol = Target.Column„Изчистете предишните параметри на сортиранеActiveSheet.Sort.SortFields.Clear„Сортирайте табличния диапазон, както е дефинирано от максималните редове и колони от обекта„ UsedRange “„Сортирайте табличните данни, като използвате колоната, щракната два пъти върху потребителя като ключ за сортиранеActiveSheet.Range (клетки (1, 1), клетки (RCol, RRow)). Ключ за сортиране1: = клетки (1, колона), заглавка: = xlДа„Изберете клетка A1 - това е, за да се гарантира, че потребителят не е оставен в режим на редактиране, след като сортирането е _'завършенActiveSheet.Range ("A1"). ИзберетеEnd Sub |
Този код трябва да бъде поставен върху събитието с двойно щракване върху листа, съдържащ табличните данни. Можете да направите това, като щракнете върху името на работния лист в прозореца на Project Explorer (горния ляв ъгъл на екрана VBE) и след това изберете „Работен лист“ в първото падащо меню в прозореца с кодове. Изберете „BeforeDoubleClick“ във второто падащо меню и след това можете да въведете кода си.
Обърнете внимание, че в този код не се кодират твърдо кодирани имена, диапазони или препратки към клетки, с изключение на преместването на курсора към клетка A1 в края на кода. Кодът е предназначен да получи цялата необходима информация от координатите на клетката, върху които потребителят е щракнал двукратно, и размера на табличния диапазон от данни.
Няма значение колко голям е диапазонът от таблични данни. Кодът все още ще вземе цялата необходима информация и може да се използва за данни, съхранявани навсякъде в работната ви книга, без да се налага да кодирате твърдо стойностите.
Единственото предположение е, че има табличен ред в табличните данни и че диапазонът от данни започва от клетка A1, но началната позиция за диапазона от данни може лесно да се промени в кода.
Всеки потребител ще бъде подходящо впечатлен от тази нова функционалност за сортиране!
Разширяване на функцията за сортиране с помощта на VBA
Microsoft позволи огромна гъвкавост при сортирането, използвайки широк спектър от параметри. В рамките на VBA обаче можете да продължите по -нататък.
Да предположим, че искате да сортирате всякакви стойности с удебелен шрифт в горната част на данните си. Няма начин да направите това в Excel, но можете да напишете VBA кода, за да го направите:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()„Създайте променливи, които да съдържат броя редове и колони за табличните данниDim RRow As Long, RCol As Long, N As Long„Изключете актуализирането на екрана, така че потребителят да не вижда какво се случва - може да види _„ценностите се променят и се чудя защоApplication.ScreenUpdating = False'Уловете броя колони в табличния диапазон от данниRCol = ActiveSheet.UsedRange.Columns.Count'Уловете броя редове в табличния диапазон от данниRRow = ActiveSheet.UsedRange.Rows.Count„Итерация през всички редове в табличния диапазон от данни, без да се обръща внимание на заглавния редЗа N = 2 За RRow„Ако клетката има удебелен шрифт, поставете водеща стойност 0 срещу стойността на клеткатаАко ActiveSheet.Cells (N, 1). Font.Bold = Истина тогаваActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueКрай АкоСледва N„Изчистете всички предишни параметри за сортиранеActiveSheet.Sort.SortFields.Clear'Сортирайте табличния диапазон от данни. Всички стойности с водеща стойност 0 ще се преместят в горната частActiveSheet.Range (клетки (1, 1), клетки (RCol, RRow)). Ключ за сортиране1: = клетки (1, 1), заглавка: = xlДа„Итерация през всички редове в табличния диапазон от данни, без да се обръща внимание на заглавния редЗа N = 2 За RRow„Ако клетката има удебелен шрифт, премахнете водещата стойност 0 от стойността на клетката на _'възстановяване на първоначалните стойностиАко ActiveSheet.Cells (N, 1). Font.Bold = Истина тогаваActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Край АкоСледва N'Включете отново актуализирането на екранаApplication.ScreenUpdating = ВярноEnd Sub |
Кодът определя размера на табличния диапазон от данни, като използва обекта „UsedRange“ и след това прави повторение през всички редове в него. Когато се намери удебелен шрифт, пред стойността на клетката се поставя начална нула.
След това се извършва сортиране. Тъй като сортирането е във възходящ ред, всичко с нула отпред ще отиде в началото на списъка.
След това кодът прави повторение през всички редове и премахва водещите нули, възстановявайки данните до първоначалните им стойности.
Този код сортира, използвайки удебелени шрифтове като критерий, но лесно можете да използвате други характеристики на клетката по същия начин, например курсив, размер на точка на текста, шрифт с подчертаване, име на шрифт и т.н.
