Сортировка данных в таблице на рабочем листе Excel средствами VBA. Sort и SortField, объекты и методы. Примеры сортировки данных в диапазоне.
Синтаксис сортировки
Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:
|
1 2 3 4 5 6 7 8 9 |
With Expression.Sort .SortFields.Clear .SortFields.Add Key, SortOn, Order, DataOption .SetRange [Range] .Header = [xlGuess, xlYes, xlNo] .MatchCase = [True, False] .Orientation = [xlTopToBottom, xlLeftToRight] .Apply End With |
Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:
|
1 2 3 4 5 6 |
With Expression.Sort .SortFields.Clear .SortFields.Add Key .SetRange [Range] .Apply End With |
Expression – выражение, возвращающее объект Worksheet, например:
|
1 2 3 4 |
ActiveSheet Worksheets ("Лист1") ActiveWorkbook.Worksheets ("Лист1") Workbooks("Книга1.xlsm").Worksheets ("Лист1") |
Расшифровка кода
1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.
2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.
3. .SortFields.Add Key, SortOn, Order, DataOption – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.
Параметры метода Add объекта SortFields:
Key – обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.
SortOn – необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).
Значения, которые может принимать SortOn:
| Константа | Значение | Описание |
|---|---|---|
| SortOnValues | 0 | сортировка по значению (значение по умолчанию) |
| SortOnCellColor | 1 | сортировка по цвету ячейки |
| SortOnFontColor | 2 | сортировка по цвету шрифта |
| SortOnIcon | 3 | сортировка по иконке* |
* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.
Order – необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).
Значения, которые может принимать Order:
| Константа | Значение | Описание |
|---|---|---|
| xlAscending | 1 | сортировка по возрастанию (значение по умолчанию) |
| xlDescending | 2 | сортировка по убыванию |
DataOption – необязательный параметр, который задает способ сортировки текста.
Значения, которые может принимать DataOption:
| Константа | Значение | Описание |
|---|---|---|
| xlSortNormal | 0 | числовые и текстовые данные сортируются отдельно (значение по умолчанию) |
| xlSortTextAsNumbers | 1 | текстовые данные рассматриваются для сортировки как числовые |
4. .SetRange [Range] – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.
5. .Header = [xlGuess, xlYes, xlNo] – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).
Значения, которые может принимать свойство Header:
| Константа | Значение | Описание |
|---|---|---|
| xlGuess | 0 | Excel сам определяет, есть ли строка заголовков |
| xlYes | 1 | строка заголовков есть, сортировка ее не затрагивает |
| xlNo | 2 | строки заголовков нет (значение по умолчанию) |
6. .MatchCase = [True, False] – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.
Значения, которые может принимать свойство MatchCase:
| Константа | Значение | Описание |
|---|---|---|
| False | 0 | регистр не учитывается (значение по умолчанию) |
| True | 1 | сортировка с учетом регистра |
7. .Orientation = [xlTopToBottom, xlLeftToRight] – свойство Orientation объекта Sort задает ориентацию для сортировки.
Значения, которые может принимать свойство Orientation:
| Константа | Значение | Описание |
|---|---|---|
| xlTopToBottom | 1 | сортировка по стокам (значение по умолчанию) |
| xlLeftToRight | 2 | сортировка по столбцам |
8. .Apply – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.
Примеры сортировки
Таблица для примеров
Сортировка по одному столбцу
Краткая запись кода VBA Excel для сортировки диапазона по первому столбцу с параметрами по умолчанию:
|
1 2 3 4 5 6 7 8 |
Sub Primer1() With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range("A2") .SetRange Range("A2:C7") .Apply End With End Sub |
Полная запись, но тоже с параметрами по умолчанию:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub Primer2() With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("A2:C7") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .Apply End With End Sub |
Результат сортировки:
Сортировка по двум столбцам
Код VBA Excel для сортировки исходной таблицы по первому и второму столбцам с параметрами по умолчанию:
|
1 2 3 4 5 6 7 8 9 |
Sub Primer3() With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range("A2") .SortFields.Add Key:=Range("B2") .SetRange Range("A2:C7") .Apply End With End Sub |
Результат сортировки:
Применение сортировки ко второму столбцу (добавление еще одного объекта SortField) не нарушает сортировку первого – в первом столбце меняются местами только ячейки с одинаковыми значениями.
Как добавить новую строку в таблицу с последующей сортировкой всех записей, вы можете ознакомиться в статье: Добавление строки в таблицу с сортировкой.