Vba excel сортировка массива

Обновлено: 07.07.2024

выражение: переменная, представляющая объект Range.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Key1	Необязательный	Variant	Указывает первое поле сортировки в качестве имени диапазона (String) или Объекта Range; определяет значения, которые необходимо сортировать.
Order1	Необязательный	XlSortOrder	Определяет порядок сортировки для значений, указанных в Key1.
Key2	Необязательный	Variant	Поле второго сортировки; не может использоваться при сортировке pivotTable.
Type	Необязательный	Variant	Указывает, какие элементы следует сортировать.
Order2	Необязательный	XlSortOrder	Определяет порядок сортировки для значений, указанных в Key2.
Key3	Необязательный	Variant	Поле третьего сортировки; не может использоваться при сортировке pivotTable.
Order3	Необязательный	XlSortOrder	Определяет порядок сортировки для значений, указанных в Key3.
Header	Необязательный	XlYesNoGuess	Указывает, содержит ли первая строка сведения о загонах. xlNo — это значение по умолчанию; укажите xlGuess, если Excel попытаться определить заголовок.
OrderCustom	Необязательный	Variant	Указывает одностандартное смещение в список пользовательских заказов сортировки.
MatchCase	Необязательный	Variant	Set to True to perform a case-sensitive sort, False to perform a non-case-sensitive sort; не может использоваться с pivotTables.
Orientation	Необязательный	XlSortOrientation	Указывает, должен ли сорт быть строкой (по умолчанию) или столбцом. Установите значение xlSortColumns для сортировки по столбцам. Установите значение xlSortRows до 2 для сортировки по строке (это значение по умолчанию).
SortMethod	Необязательный	XlSortMethod	Указывает метод сортировки.
DataOption1	Необязательный	XlSortDataOption	Указывает, как сортировать текст в диапазоне, указанном в Key1; не применяется к сортировке PivotTable.
DataOption2	Необязательный	XlSortDataOption	Указывает, как сортировать текст в диапазоне, указанном в Key2; не применяется к сортировке PivotTable.
DataOption3	Необязательный	XlSortDataOption	Указывает, как сортировать текст в диапазоне, указанном в Key3; не применяется к сортировке PivotTable.

Возвращаемое значение

Пример

В этом примере используется значение цвета ячейки в столбце A с помощью свойства ColorIndex, а затем используется это значение для сортировки диапазона по цвету.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Объявление одномерных (линейных) статических массивов в 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 из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).

В статье разберем 9 видов сортировок, рассмотрим суть этих алгоритмов. Скорости, сложность алгоритмов и практическое их применение оставим за скобками. Задача статьи показать, что одну и туже задачу можно решать различными способами, показать практическое применение языка VBA и помочь начинающим в его освоении.

Подготовительный этап

Перед тем как начинать писать алгоритмы немного подготовимся. Создадим общую константу n для хранения размера массивов. Вставим на лист диаграмму, чтобы отслеживать как все работает. В коде объявим объект нашей диаграммы, на которой будем просматривать ход процесса сортировки. Чтобы не дублировать код в каждом алгоритме сортировки мы будем использовать процедуру инициализации Init().

Чтобы наша диаграмма с результатом не подвисала и обновлялась напишем такую функцию.

В качестве массивов будем использовать диапазон ячеек A1:Y1. Напишем еще одну коротенькую процедуру для перемешивания этого массива, точнее заполнения его числами от 1 до 25 в случайном порядке.

Теперь все готово, давайте писать алгоритмы сортировки.

Сортировка пузырьком

Пузырьковая сортировка (или сортировка простыми обменами) пожалуй самый неэффективный алгоритм сортировки и в тоже время пожалуй самый известный.

Суть алгоритма в прохождении в цикле по всем элементами массива и в попарном сравнении текущего элемента со следующим. Если текущий элемент массива больше (для сортировки по возрастанию и меньше для сортировки по убыванию) чем следующий, то эти два элемента меняются друг с другом местами. Ход алгоритма смотрите на следующей диаграмме.

Вот код сортировки данным алгоритмом на VBA. Еще стоит обратить внимание на переменную Flag она служит индикатором того, что массив досрочно отсортирован и можно заранее выйти из цикла и сократить вычислительные ресурсы.

Далее описана процедура Swap для перестановки ячеек местами. После перестановки ячеек вызывается процедура ChartRefresh обновления диаграммы.

Сортировка перемешиванием

Этот алгоритм является разновидностью пузырьковой сортировки. Также этот алгоритм называют Шейкерной сортировкой или двунаправленной. Основное отличие от обычной сортировки пузырьком в том, что массив сначала просматривается слева направо и максимальный элемент перемещается вправа, а после мы проходим по массиву справа налево (от последнего отсортированного элемента) и наименьший элемент перемещается влево. Вот на графике отчетливо это видно.

Алгоритм немного больше, но по сложности аналогичный, вот его код на VBA.

Сортировка выбором

Тоже достаточно простой алгоритм сортировки. Суть его заключается в поиске минимального значения (максимального для сортировки по убыванию) и обмене найденного значения с первым неотсортированным значением. Т.е. нашли первое минимальное значение, поменяли его с первым элементом, нашли второе минимальное - поменяли со вторым элементом. График получается следующий:

Объединение сортировки пузырьком и сортировки выбором

Можно ускорить алгоритм сортировки пузырьком объединив его с алгоритмом сортировки выбором. Для этого нужно определять минимальный элемент во внутреннем цикле и после каждого прохода по списку обменивать найденный минимальный элемент с первым неотсортированным слева. Таким образом, мы сокращаем в 2 раза число перестановок, но при этом увеличиваем в 2 раза число сравнений.

Код отличается только 2 строчками:

Сортировка вставками

Вот определение сортировки с википедии

Это алгоритм, в котором элементы входной последовательности просматриваются по одному, и каждый новый поступивший элемент размещается в подходящее место среди ранее упорядоченных элементов.

Другими словами мы во внешнем цикле проходим по всем элементам массива, а во внутреннем цикле сравниваем правый элемент с уже отсортированными элементами слева и перемещаем его при необходимости. Вот как это выглядит визуально:

Код тоже думаю окажется для вас достаточно простым.

Гномья сортировка

Визуально отличия от сортировки вставками нет, однако в код совершенно другой так как нет никаких вложенных циклов. Алгоритм в цикле проходит по всем элементам, сравнивая текущий элемент с предыдущим. Если элементы стоят верно переходит к следующему, если нет, меняет их местами и переходит к предыдущему элементу.

Сортировка слиянием

Простые алгоритмы сортировки мы разобрали, теперь давайте рассмотрим более сложные виды сортировок. Хотя главное понять суть алгоритма и его реализация уже не будет казаться сложной.

Суть алгоритма сортировки слиянием состоит в том, чтобы разбить исходный массив на более мелкие массивы, отсортировать каждый по отдельности, а после объединить результаты.

Для этого первоначальный массив разбивается на 2 части пополам (ну или почти пополам если количество нечетное), каждая половинка разбивается еще пополам и так до тех пор, пока мы не получим массивы состоящие из 1 элемента. После прохождения процедуры разбивки на части, слияние каждой части и ее сортировка. Например, массив содержит числа 5 2 1 3 4. Разбиваем его на две части: 5,2,1 и 3,4 . Первую часть 5,2,1 разбиваем еще на две части 5,2 и 1. Далее 5,2 еще на две части 5 и 2. А теперь идем обратно, сортируем и сливаем массивы. Получается 2,5 и 1, объединим дальше - 1,2,5 , последняя итерация отсортирует исходный массив 1 2 3 4 5. При слиянии учитывается тот факт, что массивы уже отсортированы по отдельности, поэтому объединение проходит быстрее.

Вот визуализация работы алгоритма:

Код состоит из двух частей. Первая MergeSort - рекурсивная функция разделения массивов, т.е. эта функция запускает саму себя. Это происходит до тех пор, пока размер массива больше 1, иначе запускается функция MergeSort для каждой из частей.

После того как массивы разобьются запускается функция Merge(left, right), которая сортирует и объединяет массив обратно.

В качестве сортировки и объединения можно использовать различные алгоритмы, например такой. Если кто-то предложит более изящное решение - пишите в комментариях.

Так как функция у нас рекурсивная, то первый ее запуск необходимо сделать из отдельной процедуры, вот так:

Быстрая сортировка

Алгоритм быстрой сортировки - один из самых быстрых и эффективных и часто используется в практике. При этом он достаточно простой.

Суть алгоритма в следующем:

Выбрать из массива опорный элемент. Например, взять элемент в середине массива (в целом это может быть любой из элементов).
Сравнить остальные элементы массива с выбранным опорным элементов и разбить массив на 2 части:
- элементы, которые меньше или равны опорному элементу;
- элементы, которые больше опорного.
Далее пункты 1 и 2 повторяются рекурсивно для каждой части массива, до тех пор пока размер части состоит из более чем 1 элемента.

На визуализации к сожалению что-то разглядеть сложно. Алгоритм достаточно быстро отрабатывает:

Вот код данного алгоритма на VBA.

Запуск рекурсивной функции быстрой сортировки запустим из отдельного метода.

Пирамидальная сортировка

Пирамидальная сортировка или как еще ее называют "Сортировка кучей" использует в своем алгоритме двоичное дерево.

Это такое дерево, для которого выполнены следующие условия:

Значение в любой вершине не меньше, чем значения её потомков.
Длина веток дерева не отличается друг от друга более чем на 1 слой.
Последний слой заполняется слева направо без «дырок».

Вот пример дерева, которое можно найти на википедии:

Это дерево можно представить в виде следующего массива, где для любого элемента A[i] потомками являются элементы A[2i] и A[2i+1].

Т.е. для каждого элемента кучи справедливы следующие условия: A[i] >= A[2i] и A[i] >= A[2i+1].

Алгоритм пирамидальной сортировки состоит из следующих шагов:

Построение массива в виде двоичного дерева.
Исключение корня дерева (максимального значения массива) из массива и перенос его в конец последовательности.
После исключения корня дерево перестраивается и его корень опять отсекается и переносится в конец.
Так происходит до тех пор, пока вся последовательность не отсортируется.

Вот визуальное отображение выполнения этого алгоритма:

Ниже код пирамидальной сортировки на VBA. Который формирует двоичную кучу и корень этой кучи переносит в конец последовательности. Так происходит n раз.

Операция сортировки упорядочивает элементы последовательности на основе одного или нескольких атрибутов. Первый критерий сортировки выполняет первичную сортировку элементов. Указав второй критерий поиска, можно сортировать элементы внутри каждой группы первичной сортировки.

На следующем рисунке показаны результаты операции сортировки в алфавитном порядке в последовательности символов.

Далее перечислены методы стандартных операторов запроса, которые выполняют сортировку данных.

Методы

Имя метода	Описание	Visual Basic Синтаксис выражения запроса	Дополнительные сведения
OrderBy	Сортировка значений в возрастающем порядке.	Order By	Enumerable.OrderBy

Примеры синтаксиса выражений запросов

Примеры основной сортировки

Основная сортировка по возрастанию

В следующем примере показано использование предложения Order By в запросе LINQ для сортировки строк в массиве по длине строки в порядке возрастания.

Основная сортировка по убыванию

В следующем примере показано использование предложения Order By Descending в запросе LINQ для сортировки строк по их первой букве в порядке убывания.

Примеры дополнительной сортировки

Дополнительная сортировка по возрастанию

В следующем примере показано использование предложения Order By в запросе LINQ для выполнения основной и дополнительной сортировки строк в массиве. Строки сортируются основным образом по длине и дополнительно — по первой букве строки; в обоих случаях в возрастающем порядке.

Дополнительная сортировка по убыванию

В следующем примере показано использование предложения Order By Descending в запросе LINQ для выполнения основной сортировки по возрастанию и дополнительной сортировки по убыванию. Строки сортируются основным образом по длине и дополнительно — по первой букве строки.

Читайте также: