Vba excel combobox сортировка
Если вы хотите начать писать макросы на VBA (да и на любом другом языке), то помимо знания самого языка программирования хорошем подспорьем в работе будет знание и умение писать алгоритмы. Как и в любом другом деле для этого потребуется практика, много практики. В этой статье мы разберем алгоритмы сортировки, знание их спрашивают практически на любом собеседование на должность программиста, да и студентов часто мучают ими. Изучите их, это хороший опыт и практика для начинающих!
О чем пойдет речь
В статье разберем 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 раз.
Синтаксис полного кода 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 для сортировки диапазона по первому столбцу с параметрами по умолчанию:
ComboBox представляет из себя комбинацию двух элементов управления: текстового поля (TextBox) и списка (ListBox), поэтому его еще называют «комбинированным списком» или «полем со списком». Также ComboBox сочетает в себе свойства этих двух элементов управления.
Изначально комбинированный список прорисовывается на форме в виде текстового поля с кнопкой для отображения раскрывающегося списка. Далее по тексту будем использовать слово «поле» в значении текстового поля в составе элемента управления ComboBox, а словосочетание «раскрывающийся список» – в значении списка в составе элемента управления ComboBox.
Поле со списком используется в тех случаях, когда необходимо добавить в форму информацию, которая заранее известна, а ее отдельные позиции можно сгруппировать в список, а также для ручного ввода с клавиатуры или вставки из буфера обмена, если необходимое значение в списке отсутствует.
Элемент управления ComboBox незаменим при больших списках. При списках из нескольких позиций его можно заменить на ListBox, который отображает позиции для выбора сразу после загрузки формы, не требуя дополнительных действий от пользователя.
Свойства поля со списком
Свойство | Описание |
---|---|
AutoSize | Автоподбор размера комбинированного поля. True – размер автоматически подстраивается под длину выбранной или введенной строки. False – размер элемента управления определяется свойствами Width и Height. |
AutoTab | Включение автоматической табуляции – передачи фокуса следующему элементу управления при достижении максимального числа символов при значениях свойства MaxLenght > 0. True – автоматическая табуляция включена, False – выключена. |
ColumnCount | Указывает количество столбцов в раскрывающемся списке. Значение по умолчанию = 1. |
ColumnHeads | Добавляет строку заголовков в раскрывающийся список. True – заголовки столбцов включены, False – заголовки столбцов выключены. Значение по умолчанию = False. |
ColumnWidths | Ширина столбцов в раскрывающемся списке. Значения для нескольких столбцов указываются в одну строку через точку с запятой (;). |
ControlSource | Ссылка на ячейку для ее привязки к элементу управления ComboBox. |
ControlTipText | Текст всплывающей подсказки при наведении курсора на элемент управления. |
Enabled | Доступ пользователя к полю и раскрывающемуся списку. True – доступ разрешен, False – доступ запрещен*. Значение по умолчанию = True. |
Font | Шрифт, начертание и размер текста в поле. |
Height | Высота элемента управления ComboBox. |
Left | Расстояние от левого края внутренней границы пользовательской формы до левого края комбинированного списка. |
List | Позволяет заполнить ComboBox данными из одномерного или двухмерного массива, а также обращаться к отдельным элементам раскрывающегося списка по индексам для записи и чтения. |
ListIndex | Номер выбранной пользователем строки в раскрывающемся списке. Нумерация начинается с нуля. Если ничего не выбрано, ListIndex = -1. |
ListRows | Количество видимых строк в раскрытом списке. Если общее количество строк больше ListRows, появляется полоса прокрутки. Значение по умолчанию = 8. |
Locked | Запрет на отображение раскрывающегося списка, ввод и редактирование данных в поле. True – ввод и редактирование запрещены**, False – ввод и редактирование разрешены. Значение по умолчанию = False. |
MatchRequired | Задает проверку вводимых в поле строк с элементами списка. True – проверка включена (допускается ввод только строк, совпадающих с элементами списка), False – проверка выключена (допускается ввод любых строк). Значение по умолчанию = False. |
MaxLenght | Максимальная длина строки в поле. Значение по умолчанию = 0, что означает – ограничений нет. |
RowSource | Источник строк для раскрывающегося списка (адрес диапазона на рабочем листе Excel). |
TabIndex | Целое число, определяющее позицию элемента управления в очереди на получение фокуса при табуляции. Отсчет начинается с 0. |
Text | Текстовое содержимое (значение) поля (=Value). |
TextAlign | Выравнивание текста в поле: 1 (fmTextAlignLeft) – по левому краю, 2 (fmTextAlignCenter) – по центру, 3 (fmTextAlignRight) – по правому краю. |
Top | Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края комбинированного списка. |
Value | Текстовое содержимое (значение) поля (=Text). |
Visible | Видимость поля со списком. True – ComboBox отображается на пользовательской форме, False – ComboBox скрыт. |
Width | Ширина элемента управления. |
* При Enabled в значении False пользователь не может раскрывать список, а также вводить или редактировать данные в поле.
** Для элемента управления ComboBox действие свойства Locked в значении True аналогично действию свойства Enabled в значении False.
В таблице перечислены только основные, часто используемые свойства поля со списком. Еще больше доступных свойств отображено в окне Properties элемента управления ComboBox, а все методы, события и свойства – в окне Object Browser.
Вызывается Object Browser нажатием клавиши «F2». Слева выберите объект ComboBox, а справа смотрите его методы, события и свойства.
Свойства BackColor, BackStyle, BorderColor, BorderStyle отвечают за внешнее оформление комбинированного списка и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления ComboBox на проекте пользовательской формы.
Способы заполнения ComboBox
Используйте метод AddItem для загрузки элементов в поле со списком по одному:
Создайте пользовательскую форму UserForm1 и разместите на ней поле со списком ComboBox1. Используйте метод AddItem для заполнения элемента управления значениями:
Скопируйте код и запустите его выполнение, на открывшейся форме раскройте поле со списком, в результате увидите, что элемент управления ComboBox1 заполнен соответствующими значениями:
Заполнение ComboBox значениями из массива
Для заполнения элемента управления ComboBox значениями из массива будем использовать свойство поля со списком List и функцию Array:
Результат выполнения кода будет таким же, как и на предыдущем изображении.
Таким же образом можно использовать не только функцию Array, но и переменную массива, предварительно объявленную и заполненную значениями:
Заполнение ComboBox значениями из ячеек
Для заполнения поля со списком значениями из диапазона ячеек рабочего листа будем использовать свойство комбинированного списка RowSource, предварительно заполнив диапазон «A1:A5» активного листа уже известными значениями:
Чтобы присвоить элементу управления ComboBox значения из диапазона ячеек любого рабочего листа, добавьте ссылку на него перед наименованием диапазона, например, замените «A1:A5» на «Лист1!A1:A5», и поле со списком будет заполнено значениями ячеек «A1:A5», расположенных на листе с именем «Лист1». Имя листа берется из наименования ярлыка.
Очистка ComboBox от значений
Очистить ComboBox можно как от всех значений сразу, так и удалить один или несколько значений (по одному) из списка.
Очистка ComboBox от всего списка значений:
Очистка ComboBox от одного значения:
где Index — порядковый номер элемента в списке ComboBox, нумерация начинается с нуля.
Автоматическое заполнение ComboBox
Предполагается, что, при открытии пользовательской формы или рабочего листа с ComboBox, поле со списком должно быть уже заполнено значениями. Как это сделать?
Заполнение ComboBox на форме
Автоматическое заполнение элемента управления ComboBox на пользовательской форме осуществляется с помощью процедуры UserForm_Initialize, размещенной в модуле этой формы:
Заполнение ComboBox на листе
Автоматическое заполнение элемента управления ComboBox из коллекции «Элементы ActiveX» на рабочем листе осуществляется с помощью процедуры Workbook_Open, размещенной в модуле «ЭтаКнига»:
Более подробно о заполнении данными ComboBox можно прочитать в статье о заполнении ListBox, так как оба эти элемента управления используют одинаковые способы заполнения.
Иногда возникает необходимость заполнения элементов управления ListBox и ComboBox уникальными значениями из диапазона ячеек с повторяющимся содержимым. Смотрите, как отсортировать уникальные элементы из списка с помощью объектов Collection и Dictionary.
Вы можете скачать файл Excel с представленными выше примерами. Файл упакован в ZIP-архив. Для проверки работоспособности кода, непосредственно в редакторе VBA помещайте курсор внутри тела каждой процедуры и нажимайте кнопку «Run Sub».
12 комментариев для “VBA Excel. ComboBox — заполнение поля со списком”
Не получился ни один из предложенных вариантов( С чем это может быть связано?
Спасибо за примеры, на одном все получилось. Чуток лишнее удалил и все ОК.
Потому что пример привязан к тестовому объекту.
Я тоже не мог понять, при отсутствии навыков.
Но вот четко к полю привязку по примеру удалось сделать.
Аналогично в powerpoint вставка поля со списком
Операция сортировки упорядочивает элементы последовательности на основе одного или нескольких атрибутов. Первый критерий сортировки выполняет первичную сортировку элементов. Указав второй критерий поиска, можно сортировать элементы внутри каждой группы первичной сортировки.
На следующем рисунке показаны результаты операции сортировки в алфавитном порядке в последовательности символов.
Далее перечислены методы стандартных операторов запроса, которые выполняют сортировку данных.
Методы
Имя метода | Описание | синтаксис выражения запроса Visual Basic | Дополнительные сведения |
---|---|---|---|
OrderBy | Сортировка значений в возрастающем порядке. | Order By | Enumerable.OrderBy |
Примеры синтаксиса выражений запросов
Примеры основной сортировки
Основная сортировка по возрастанию
В следующем примере показано использование предложения Order By в запросе LINQ для сортировки строк в массиве по длине строки в порядке возрастания.
Основная сортировка по убыванию
В следующем примере показано использование предложения Order By Descending в запросе LINQ для сортировки строк по их первой букве в порядке убывания.
Примеры дополнительной сортировки
Дополнительная сортировка по возрастанию
В следующем примере показано использование предложения Order By в запросе LINQ для выполнения основной и дополнительной сортировки строк в массиве. Строки сортируются основным образом по длине и дополнительно — по первой букве строки; в обоих случаях в возрастающем порядке.
Дополнительная сортировка по убыванию
В следующем примере показано использование предложения Order By Descending в запросе LINQ для выполнения основной сортировки по возрастанию и дополнительной сортировки по убыванию. Строки сортируются основным образом по длине и дополнительно — по первой букве строки.
Читайте также: