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

Обновлено: 21.11.2024

Макрос быстрой сортировки VBA — это быстрый способ сортировки массивов VBA. Макрос быстрой сортировки VBA в этом руководстве намного эффективнее макроса пузырьковой сортировки VBA, который я показывал на прошлой неделе, поэтому его можно использовать для быстрой сортировки как небольших, так и больших одномерных массивов VBA.

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

Пример — быстрая сортировка VBA

Сверхбыстрый макрос быстрой сортировки

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

Такой макрос легко скопировать и вставить, но создать его самостоятельно сложнее. Чтобы помочь вам создавать макросы, подобные этому, мы создали бесплатный комплект разработчика VBA и написали Большую книгу макросов Excel VBA, полную сотен готовых макросов, которые помогут вам освоить файловый ввод-вывод, массивы, строки и многое другое. скопируйте ниже.

Учебник — Быстрая сортировка VBA

О быстрой сортировке

Алгоритм быстрой сортировки известен как алгоритм "разделяй и властвуй". Во-первых, он идентифицирует опорное значение, которое представляет собой не что иное, как случайный элемент внутри вашего массива. Макрос примера предполагает самый центральный элемент, но это может быть любой элемент. После этого он разделяет массив, перемещая элементы, которые больше, чем ваше значение поворота, в одну сторону вашего массива и перемещая элементы, которые меньше, чем ваше значение поворота, в другую сторону. Это разделяющая часть «разделяй и властвуй».

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

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

Хотя в наихудшем случае производительность алгоритма быстрой сортировки пропорциональна N^2 (где N – количество элементов в массиве), как и в случае с пузырьковой сортировкой, она редко занимает так много времени. На самом деле, средняя производительность классифицируется как сложность порядка N*log(N) вместо N^2. Это намного быстрее, чем пузырьковая сортировка! Я докажу это через некоторое время.

Как я уже говорил ранее, пример макроса вращается вокруг среднего элемента. Технически это немного медленнее, чем поворот вокруг крайнего левого или крайнего правого элемента, но снижает вероятность деградации алгоритма до числа итераций N^2, если вы случайно попытаетесь отсортировать массив, который уже почти отсортирован.

Зачем использовать алгоритм быстрой сортировки VBA

Потому что алгоритм быстрой сортировки VBA обычно до смешного быстрее, чем алгоритм пузырьковой сортировки! Какие еще причины вам нужны? Правда, его немного сложнее понять, а рекурсивный характер усложняет программирование, но поиск хорошего алгоритма быстрой сортировки, подобного тому, что описан в этом руководстве, может сэкономить вам и вашему коду огромное количество времени.

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

Быстрая сортировка VBA и пузырьковая сортировка VBA

Я создал одномерный массив VBA из 25 000 элементов, заполненных случайными числами от 0 до 10 000. Сначала я отсортировал массив, используя алгоритм пузырьковой сортировки VBA из урока прошлой недели. На моей машине пузырьковая сортировка заняла целых 22,64 секунды.

Затем я взял тот же самый массив и отсортировал его с помощью алгоритма быстрой сортировки VBA, представленного в этом руководстве. Различия были поразительны. Метод быстрой сортировки занял всего 0,07 секунды.

Макрос быстрой сортировки VBA был в 323 раза быстрее, чем макрос пузырьковой сортировки VBA!

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

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

Эталон быстрой сортировки VBA и пузырьковой сортировки

< td style="text-align: center">1000

N	Быстрая сортировка	Пузырьковая сортировка
25 000	0,070	22,643
10 000	0,027	3,551
5000	0,016	0,891
0.004	0,059

Время (в секундах) для сортировки массивов VBA размером N

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

Алгоритм быстрой сортировки сложен, поэтому, скорее всего, вам не захочется программировать его с нуля. Чтобы быстро внедрить алгоритм быстрой сортировки в свои проекты Excel, добавьте макрос быстрой сортировки в избранное в wellsrPRO и выбирайте его на ленте Excel всякий раз, когда вам это нужно.

Как использовать макрос быстрой сортировки VBA

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

Обратите внимание, что строка Call Quicksort имеет три аргумента. Первый аргумент — это ваш одномерный массив MyData. Второй и третий аргументы — это нижняя и верхняя границы вашего массива. «Нижняя граница и верхняя граница» — это еще один способ сказать «первый элемент и последний элемент» вашего массива. Эти аргументы необходимы, так как быстрая сортировка является рекурсивным алгоритмом. Я не могу просто установить границы, потому что алгоритм быстрой сортировки должен разделять и властвовать, определяя свои собственные опорные точки и разделы. В моем примере я использовал функции LBound и Ubound для определения границ в первый раз. Сценарий VBA делает все остальное как часть своей рекурсивной логики.

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

Идеи для приложений

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

Вы определенно хотите использовать быструю сортировку VBA вместо пузырьковой сортировки при работе с большими массивами. Как вы планируете использовать этот макрос сортировки? Дайте мне знать в разделе комментариев!

Прежде чем уйти, поделитесь этой статьей со своими друзьями в Facebook и Twitter. Социальные сети — отличный способ расширить сайт и привлечь больше пользователей, поэтому я искренне ценю, когда вы делитесь моими статьями с друзьями на своей любимой платформе! :)

Когда вы будете готовы вывести свой VBA на новый уровень, подпишитесь, заполнив форму ниже.

Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.

Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.

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

Настройка примера

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

Сортировка массива от A до Z

Мне нравится использовать функцию, а не подпрограмму для сортировки массива. Функция представляет собой повторно используемый фрагмент кода, он может стать частью вашего «основного» модуля, который вы можете скопировать в любой проект VBA. Эта функция принимает в качестве переменной массив и выводит тот же массив, отсортированный в алфавитном порядке.

Сортировка массива от Z до A

Реверсивная функция для сортировки массива Z-A
Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

Не забывайте:

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

Вам нужна помощь в адаптации этого к вашим потребностям?

Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.

Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

Этот форум перенесен в раздел вопросов и ответов Майкрософт. Посетите Microsoft Q&A, чтобы публиковать новые вопросы.

Вопрос:

Вопрос

Есть ли способ сортировать массивы в VBA без предварительного копирования их на лист Excel, и если нет, то почему?

Все ответы

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

Реализованное решение ниже

Заранее спасибо, Чиприан ЛУПУ

Я опубликовал ответ о сортировке массивов в стеке пару лет назад, и код приведен ниже. Вы можете найти его в Google или Bing как StackOverlow [VBA] «Сортировка многомерного массива в VBA» и получить там полезные обсуждения и альтернативные решения для кодирования.

<р>. Именно по этой причине нет встроенной «Сортировки»: VBA и семейство языков VB представляют собой уровень «последней мили» для управления пользовательским интерфейсом и представления обработанных данных из других инструментов и приложений, которые, как предполагается, сделали тяжелую работу. поднятие объемных данных и задач, требующих большого количества вычислений.

Тем не менее, иногда вам нужно делать это «внутри компании». Есть несколько отличных руководств по коду по сортировке и два хороших ответа, размещенных здесь, в MSDN: однако вы, как правило, не получаете ответы на двумерные массивы — ожидаемый массив из диапазона Excel — и вы не получаете достаточно «очистка» и защитное кодирование ненужных данных, с которыми вы столкнетесь в электронных таблицах:

Ваш код будет вызывать это для (скажем) сортировки массива по столбцу 2 как:

И мои любимые ошибки:

1: получение варианта '.value' из диапазона одной ячейки. Это скаляр, а не двумерный массив

2: Получение варианта из параметра, который вы объявили и благословили святой водой как вариант и **знаете**, что это вариант, который произошел из диапазона Excel. Это объект Range, и он выдаст ошибку типа, когда вы попытаетесь обработать его как вариант:

<р>. Я оставлю проблему несмежных диапазонов в качестве упражнения для читателя.

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

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

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

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

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

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

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

Это руководство охватывает:

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

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

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

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

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

Ключ — здесь вам нужно указать столбец, который вы хотите отсортировать. Например, если вы хотите отсортировать столбец A, вам нужно использовать key:=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.
Header:= xlNo — указано, что заголовков нет. Это также значение по умолчанию. Таким образом, даже если вы опустите это, ваши данные будут отсортированы с учетом отсутствия заголовков.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как показано ниже:

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

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

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

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

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

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

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

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

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

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

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

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

Как показано ниже:

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

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

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

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

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

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