Vba excel сортировка массива
expression A variable that represents a Range object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
Key1 | Optional | Variant | Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted. |
Order1 | Optional | XlSortOrder | Determines the sort order for the values specified in Key1. |
Key2 | Optional | Variant | Second sort field; cannot be used when sorting a PivotTable. |
Type | Optional | Variant | Specifies which type of elements are to be sorted within a PivotTable. Specify xLSortLabels to sort by the labels targeted by the experession Range in the Row/Colum Labels areas of the PivotTable's range, or xLSortValues when Key1 additionally targets a cell in the Values or Sub/Grand Totals areas using R1C1 notation. |
Order2 | Optional | XlSortOrder | Determines the sort order for the values specified in Key2. |
Key3 | Optional | Variant | Third sort field; cannot be used when sorting a PivotTable. |
Order3 | Optional | XlSortOrder | Determines the sort order for the values specified in Key3. |
Header | Optional | XlYesNoGuess | Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header. |
OrderCustom | Optional | Variant | Specifies a one-based integer offset into the list of custom sort orders. |
MatchCase | Optional | Variant | Set to True to perform a case-sensitive sort, False to perform a non-case-sensitive sort; cannot be used with PivotTables. |
Orientation | Optional | XlSortOrientation | Specifies if the sort should be by row (default) or column. Set xlSortColumns value to 1 to sort by column. Set xlSortRows value to 2 to sort by row (this is the default value). |
SortMethod | Optional | XlSortMethod | Specifies the sort method. |
DataOption1 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key1; does not apply to PivotTable sorting. |
DataOption2 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key2; does not apply to PivotTable sorting. |
DataOption3 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key3; does not apply to PivotTable sorting. |
Return value
Example
This example gets the value of the color of a cell in column A by using the ColorIndex property, and then uses that value to sort the range by color.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:
Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:
Expression – выражение, возвращающее объект Worksheet, например:
Расшифровка кода
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 для сортировки диапазона по первому столбцу с параметрами по умолчанию:
Массивы – это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в VBA Excel, как и другие переменные, объявляются с помощью операторов Dim и Public. Для изменения размерности динамических массивов используется оператор ReDim. Массивы с заранее объявленной размерностью называют статическими.
Одномерные массивы
Объявление одномерных (линейных) статических массивов в VBA Excel:
В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.
По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1». Вместо верхнего индекса можно использовать переменную.
Многомерные массивы
Объявление многомерных статических массивов в VBA Excel аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:
Третий массив состоит из 10000 элементов — 10×10×10×10.
Динамические массивы
Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:
Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:
Переопределять размерность динамических массивов в процессе работы программы можно неоднократно, как по количеству измерений, так и по количеству элементов в измерении.
При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:
Обратите внимание!
Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.
Максимальный размер
Размер массива – это произведение длин всех его измерений. Он представляет собой общее количество элементов, содержащихся в данный момент в массиве.
По информации с сайта разработчиков, максимальный размер массивов зависит от операционной системы и доступного объема памяти. Использование массивов, размер которых превышает объем доступной оперативной памяти компьютера, приводит к снижению скорости, поскольку системе необходимо выполнять запись данных и чтение с диска.
Использование массивов
Приведу два примера, где не обойтись без массивов.
1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.
2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.
Функции Array, LBound, UBound
Функция Array
Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Ключ 1 | Необязательный | Variant | Указывает первое поле сортировки в виде имени диапазона (string) или объекта Range ; определяет значения для сортировки. |
Заказ 1 | Необязательный | XlSortOrder | Определяет порядок сортировки для значений, указанных в key1. |
Ключ 2 | Необязательный | Variant | Второе поле сортировки; не может использоваться при сортировке сводной таблицы. |
Type | Необязательный | Variant | Указывает тип элементов, которые должны быть отсортированы в сводной таблице. Укажите xLSortLabels для сортировки по меткам, на которые нацелен диапазон experession в областях меток строк и столбцов диапазона сводной таблицы, или xLSortValues , если Key1 дополнительно нацелен на ячейку в областях значений или вложенных или общих итогов с помощью нотации R1C1. |
Заказ 2 | Необязательна | XlSortOrder | Определяет порядок сортировки для значений, указанных в key2. |
Ключ 3 | Необязательный | Variant | Третье поле сортировки; не может использоваться при сортировке сводной таблицы. |
Заказ 3 | При необходимости | XlSortOrder | Определяет порядок сортировки для значений, указанных в key3. |
Header | При необходимости | XlYesNoGuess | Указывает, содержит ли первая строка сведения о заголовке. Значение по умолчанию — xlNo; укажите xlGuess, если Excel попытаться определить заголовок. |
OrderCustom | Необязательный | Variant | Задает однона основе целочисленного смещения в списке пользовательских заказов сортировки. |
MatchCase | Необязательный | Variant | Задайте значение True для выполнения сортировки с учетом регистра, False — для сортировки без учета регистра; не может использоваться со сводными таблицами. |
Orientation | При необходимости | XlSortOrientation | Указывает, должна ли сортировка быть по строке (по умолчанию) или столбцу. Для сортировки по столбцу задайте для значения xlSortColumns значение 1. Задайте значение xlSortRows 2 для сортировки по строкам (это значение по умолчанию). |
SortMethod | При необходимости | XlSortMethod | Указывает метод сортировки. |
DataOption1 | При необходимости | XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в key1; не применяется к сортировке сводной таблицы. |
DataOption2 | Необязательна | XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в key2; не применяется к сортировке сводной таблицы. |
DataOption3 | Необязательна | XlSortDataOption | Указывает способ сортировки текста в диапазоне, указанном в key3; не применяется к сортировке сводной таблицы. |
Возвращаемое значение
Пример
Этот пример получает значение цвета ячейки в столбце A с помощью свойства ColorIndex , а затем использует это значение для сортировки диапазона по цвету.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Операция сортировки упорядочивает элементы последовательности на основе одного или нескольких атрибутов. Первый критерий сортировки выполняет первичную сортировку элементов. Указав второй критерий поиска, можно сортировать элементы внутри каждой группы первичной сортировки.
На следующем рисунке показаны результаты операции сортировки в алфавитном порядке в последовательности символов.
Далее перечислены методы стандартных операторов запроса, которые выполняют сортировку данных.
Методы
Имя метода | Описание | синтаксис выражения запроса Visual Basic | Дополнительные сведения |
---|---|---|---|
OrderBy | Сортировка значений в возрастающем порядке. | Order By | Enumerable.OrderBy |
Примеры синтаксиса выражений запросов
Примеры основной сортировки
Основная сортировка по возрастанию
В следующем примере показано использование предложения Order By в запросе LINQ для сортировки строк в массиве по длине строки в порядке возрастания.
Основная сортировка по убыванию
В следующем примере показано использование предложения Order By Descending в запросе LINQ для сортировки строк по их первой букве в порядке убывания.
Примеры дополнительной сортировки
Дополнительная сортировка по возрастанию
В следующем примере показано использование предложения Order By в запросе LINQ для выполнения основной и дополнительной сортировки строк в массиве. Строки сортируются основным образом по длине и дополнительно — по первой букве строки; в обоих случаях в возрастающем порядке.
Дополнительная сортировка по убыванию
В следующем примере показано использование предложения Order By Descending в запросе LINQ для выполнения основной сортировки по возрастанию и дополнительной сортировки по убыванию. Строки сортируются основным образом по длине и дополнительно — по первой букве строки.
Читайте также: