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

Обновлено: 06.07.2024

В статье разберем 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), которая сортирует и объединяет массив обратно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

Сортирующее дерево

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

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

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

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

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

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

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

Рисунок с операциями сортировки в алфавитном порядке.

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

Методы

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

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

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

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

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

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

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

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

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

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

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

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


Существуют разные способы сортировки данных в Microsoft Excel. Под вкладкой Excel Data находится значок сортировки, откуда вы можете быстро отсортировать данные и получить результаты. Почему тогда нужно писать сложный код VBA?

Вы должны помнить это каждый раз, коды VBA существуют для автоматизации вещей. Предположим, у вас есть данные, которые время от времени обновляются. Может быть, каждый день, еженедельно, ежемесячно и т. Д., И вам нужны ваши данные определенным образом. В таких случаях можно использовать функцию VBA SORT, которая станет удобным инструментом для вашей справки.

VBA имеет метод Range.Sort для сортировки данных. Где Range указывает диапазон ячеек, которые мы хотим отсортировать в порядке возрастания или убывания.

Синтаксис для Range.Sort приведен ниже:


  • Ключ - столбец / диапазон, который нужно отсортировать. Ex. Если вы хотите отсортировать ячейки A1: A10, вы должны упомянуть Range (A1: A10)
  • Порядок - это параметр, который позволяет сортировать данные в порядке возрастания или убывания.
  • Заголовок - это параметр, который указывает, имеет ли ваш столбец / диапазон заголовки или нет.

Этих трех параметров достаточно для нашей работы. Однако есть некоторые другие параметры, такие как MatchCase, SortMethod и т. Д., Которые вы всегда можете изучить и посмотреть, как они работают.

Как использовать функцию сортировки Excel VBA?

Мы узнаем, как сортировку VBA можно использовать для сортировки столбца без заголовков, столбца с заголовками и нескольких столбцов с примерами в Excel.

Вы можете скачать этот шаблон VBA Sort Excel здесь - Шаблон VBA Sort Excel

Функция сортировки VBA - пример № 1

Сортировка одного столбца без заголовка

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


Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую sup-процедуру в модуле и создайте макрос.

Код:


Шаг 2: Используйте функцию Range.Sort для сортировки этого столбца в порядке возрастания.

Код:


Здесь вы даете диапазон, начиная с ячейки A1 до последней использованной / непустой ячейки (см. Функцию .End (xlDown)) для функции Range.Sort.

Шаг 3: Теперь введите значения аргумента.

Код:


Как мы уже обсуждали ранее, Key, Order и Header являются важными и необходимыми аргументами, которые необходимо предоставить. В качестве начального диапазона столбца мы указали Range («A1»), который нам нужно отсортировать. Порядок предоставляется в порядке возрастания, а заголовок - как нет (что означает, что столбец не имеет заголовка).

Этот код проверяет все непустые ячейки, начиная с A1, а затем сортирует их в порядке возрастания, начиная с ячейки A1.

Шаг 4: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

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

Функция сортировки VBA - пример № 2

Сортировка одной колонки с заголовком

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


Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую подпроцедуру в новой модели для хранения макроса.

Код:


Код:


Шаг 3: Используйте Range («A1»). Сортируйте перед приведенной выше строкой кода, чтобы сделать ее функцией сортировки.

Код:


Шаг 4: Введите Key1 как Range («A1») для сортировки данных из ячейки A1, Order1, чтобы отсортировать данные в порядке возрастания или убывания, и Header как « Да», чтобы система знала, что первая строка является заголовком в вашем данные.

Код:


Шаг 5: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

Здесь данные из примера № 2 данной рабочей книги Excel сортируются в порядке возрастания, учитывая, что они имеют заголовок. Это означает, что при сортировке этих данных первая строка (которая содержит имя Emp ) игнорируется, поскольку она рассматривается как заголовок для этих данных в столбце A.

Вы также можете отсортировать те же данные в порядке убывания алфавитов. Все, что вам нужно сделать, это изменить порядок с возрастания на убывание.

Шаг 6: Измените order1 на нисходящий, чтобы отсортировать данные в порядке убывания.

Код:


Шаг 7: Запустите код и посмотрите вывод, как показано ниже.

Вы можете видеть, что данные отсортированы в порядке убывания.

Функция сортировки VBA - пример № 3

Сортировка нескольких столбцов с заголовками

До сих пор мы рассмотрели, как сортировать данные одного столбца в порядке возрастания или убывания (без заголовка и с заголовком). Что если у вас есть данные, которые нужно отсортировать по нескольким столбцам? Можно ли написать код для того же?

Ответ: «Да, конечно, это можно сделать!»

Предположим, у вас есть данные, указанные ниже:


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

Шаг 1: Определите новую подпроцедуру для добавления макроса в новый модуль.

Код:


Шаг 2: Используйте оператор With… End With, чтобы добавить несколько условий сортировки в одном цикле.

Код:


Шаг 3: Теперь используйте SortFields.Add, чтобы добавить несколько условий сортировки в одном листе.

Код:


Шаг 4: Определите диапазон листов для сортировки и заголовок На следующем шаге.

Код:


Шаг 5: Используйте .Apply, чтобы применить все это под оператором with и закрыть цикл, пишущий End With.

Код:


Шаг 6: Запустите этот код, нажав F5 или кнопку Run вручную, и увидите результат.

В этом коде ActiveSheets.Sort помогает системе идентифицировать лист, по которому должны быть отсортированы данные. SortFields.Add позволяет добавлять два условия сортировки с их порядком (по возрастанию в обоих случаях). SetRange позволяет системе установить диапазон от A1 до C13. Вы также можете увеличить этот диапазон. Операторы Apply позволяют системе применять все изменения, сделанные в цикле With.

Наконец, вы получите данные, которые отсортированы по имени Emp сначала, а затем по местоположению.

То, что нужно запомнить

  • Под сортировкой VBA вы можете создавать именованные диапазоны вместо ссылок на ячейки и использовать их. Ex. Если вы создали именованный диапазон для ячейки A1: A10 как «EmpRange», вы можете использовать его в Range.Sort, например Range («EmpRange»).
  • Вы можете сортировать данные как по возрастанию, так и по убыванию, как в Excel.
  • Если вы не уверены, есть ли у ваших данных заголовок или нет, вы можете использовать xlGuess в разделе заголовка, чтобы система могла угадать, является ли первая строка данных заголовком или нет.

Рекомендуемые статьи

Это было руководство по сортировке Excel VBA. Здесь мы обсудили VBA Sort и как использовать Excel VBA Sort Function вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

В Excel уже есть несколько способов быстрой сортировки данных.

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


Тогда зачем вам знать, как это сделать с помощью VBA?

Знание того, как сортировать данные с помощью VBA, может быть полезным, если оно включено в ваш код. Например, предположим, что вы получаете набор данных ежедневно / еженедельно, который необходимо отформатировать и отсортировать в определенном порядке.

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

Кроме того, если вы создаете информационные панели Excel, вы можете вывести возможности сортировки Excel на новый уровень, позволив пользователю сортировать данные, просто дважды щелкнув заголовок (как показано ниже).

Я расскажу, как создать это позже в этом уроке. Давайте сначала быстро разберемся с основами.

Понимание метода Range.Sort в Excel VBA

При сортировке с использованием VBA вам необходимо использовать в своем коде метод Range.Sort.

«Диапазон» - это данные, которые вы пытаетесь отсортировать. Например, если вы сортируете данные в формате A1: A10, тогда «Диапазон» будет иметь значение «Диапазон» («A1: A10»).

Вы также можете создать именованный диапазон и использовать его вместо ссылок на ячейки. Например, если я создаю именованный диапазон «DataRange» для ячеек A1: A10, я также могу использовать Range («DataRange»).

С помощью метода сортировки вам необходимо предоставить дополнительную информацию через параметры. Ниже приведены ключевые параметры, которые вам необходимо знать:

  • Ключ - здесь нужно указать столбец, который нужно отсортировать. Например, если вы хотите отсортировать столбец A, вам нужно использовать ключ: = Range («A1»)
  • Заказ - здесь вы указываете, хотите ли вы сортировку по возрастанию или по убыванию. Например, если вам нужна сортировка по возрастанию, вы должны использовать Order: = xlAscending
  • Заголовок - здесь вы указываете, есть ли у вашего набора данных заголовки или нет. Если у него есть заголовки, сортировка начинается со второй строки набора данных, иначе она начинается с первой строки. Чтобы указать, что у ваших данных есть заголовки, вы будете использовать Header: = xlYes

Хотя этих трех достаточно в большинстве случаев, вы можете узнать больше о параметрах в этой статье.

Теперь давайте посмотрим, как использовать метод Range.Sort в VBA для сортировки данных в Excel.

Сортировка одного столбца без заголовка

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


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

Обратите внимание, что я указал диапазон данных вручную как Range («A1: A12»).

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

Обратите внимание, что вместо Range («A1: A12») я использовал Range («A1», Range («A1»). End (xlDown)).

Это проверит последнюю последовательно заполненную ячейку в столбце и включит ее в сортировку. Если есть пробелы, он будет рассматривать данные только до первой пустой ячейки.

Вы также можете создать именованный диапазон и использовать этот именованный диапазон вместо ссылок на ячейки. Например, если именованный диапазон - DataSet, ваш код теперь будет таким, как показано ниже.

Теперь позвольте мне быстро объяснить параметры, используемые в приведенных выше примерах:

  • Key1: = Range («A1») - указывает A1, чтобы код знал, какой столбец сортировать.
  • Order1: = xlAscending - Указан порядок как xlAscending. Если вы хотите, чтобы он располагался в порядке убывания, используйте xlDescending.
  • Заголовок: = xlNo - указывает, что заголовков нет. Это также значение по умолчанию. Таким образом, даже если вы опустите это, ваши данные будут отсортированы, учитывая, что у них нет заголовков.

Не знаете, где разместить этот код VBA и как запустить макрос? Прочтите это руководство!

Сортировка одного столбца с заголовком

В предыдущем примере у набора данных не было заголовка.

Если у ваших данных есть заголовки, вам необходимо указать это в коде, чтобы сортировка могла начинаться со второй строки набора данных.

Предположим, у вас есть набор данных, как показано ниже:


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

Обратите внимание, что я создал именованный диапазон - DataRange и использовал этот именованный диапазон в коде.

Сортировка нескольких столбцов с заголовками

До сих пор в этом руководстве мы видели, как отсортировать отдельный столбец (с заголовками и без них).

А что, если вы хотите отсортировать по нескольким столбцам.

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


Вот код, который сортирует несколько столбцов за один раз.

Ниже представлен результат, который вы получите.


В приведенном выше примере данные сначала сортируются по коду состояния (столбец A). Затем в данных кода состояния он снова сортируется по магазину (столбец B). Этот порядок определяется кодом, в котором вы его упоминаете.

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

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

Что-то вроде того, что показано ниже:

Ниже приведен код, который позволит вам это сделать:

Обратите внимание, что я создал именованный диапазон («DataRange») и использовал его в коде вместо использования ссылок на ячейки.

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

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

Обратите внимание, что двойной щелчок - это триггер, позволяющий Excel запускать указанный код. Эти триггеры, такие как двойной щелчок, открытие книги, добавление нового рабочего листа, изменение ячейки и т. Д., Называются событиями и могут использоваться для запуска макросов в Excel. Вы можете узнать больше о событиях Excel VBA здесь.

Куда поставить этот код?

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

  • Щелкните правой кнопкой мыши вкладку листа.
  • Щелкните Просмотреть код.
  • Вставьте код в окно кода листа, на котором находятся ваши данные.

А что, если вы хотите отсортировать первые два столбца («Штат» и «Магазин») в порядке возрастания, а столбец «Продажи» - в порядке убывания.

Вот код, который это сделает:

В приведенном выше коде он проверяет, является ли ячейка, по которой дважды щелкнули, заголовком Продажи или нет. Если да, то он присваивает значение xlDescending переменной SortOrder, иначе делает его xlAscending.

Теперь давайте сделаем еще один шаг вперед и покажем визуальный маркер (стрелка и цветная ячейка) в заголовке при сортировке.

Что-то вроде того, что показано ниже:

Чтобы получить это, я добавил новый рабочий лист и внес в него следующие изменения (вы можете загрузить файл примера и следить за ним):

  • Изменил имя нового листа на «BackEnd».
  • В ячейке B2 введите символ стрелки (для этого перейдите во вкладку «Вставить» и выберите параметр «Символ»).
  • Скопируйте и вставьте заголовки из набора данных в ячейку A3: C3 на листе «Backend».
  • Используйте следующую функцию в ячейке A4: AC4:
  • Остальные ячейки будут автоматически заполнены кодом VBA, если вы дважды щелкните заголовки, чтобы отсортировать столбец.

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


Теперь вы можете использовать приведенный ниже код для сортировки данных, дважды щелкнув заголовки. Если дважды щелкнуть заголовок, в тексте заголовка автоматически появится стрелка. Обратите внимание, что я также использовал условное форматирование, чтобы выделить ячейку.

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

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