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