Vba excel сортировка по цвету
Обновлено: 07.07.2024
Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:
Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:
Expression – выражение, возвращающее объект Worksheet, например:
Расшифровка кода
1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.
Объект Sort – это объект, представляющий сортировку диапазона данных.2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.
Объект 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 для сортировки диапазона по первому столбцу с параметрами по умолчанию:
выражение: переменная, представляющая объект 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 и обратная связь.
Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?
При проведении анализа данных сортировка и фильтрация помогают структурировать, упорядочивать данные или производить поиск.
К примеру, ячейки или диапазоны данных можно окрашивать в различные цвета с целью выделения конкретных данных, которые в последствии можно дополнительно проанализировать.
При этом фильтрация позволяет показать только те данные, которые удовлетворяют заданным критериям, и соответственно, скрыть те данные, которые не подходят по условию.
Сортировка же показывает все данные, однако упорядочивает их согласно заданным критериям.
Стандартный фильтр и сортировка по цвету в Excel
Предположим, что у нас имеется таблица с наименованиями и выручкой по различным продуктам, где определенные категории дополнительно выделены цветом заливки ячейки и текста:
Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:
Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.
С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.
В результате синие ячейки расположились вверху таблицы, однако остальные идут вразнобой и не упорядочены.
На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:
Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:
Сортировка и фильтр по цвету с помощью функций
Как мы выяснили стандартным способом отфильтровать данные по нескольким цветам не получится, однако это ограничение можно обойти с помощью создания дополнительного параметра с цветом заливки (или текста), по которому далее и будем сортировать или фильтровать данные.
Функция цвета заливки ячейки на VBA
Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:
Ранее в Podcast 2093 я показывал простую сортировку VBA, которая работает, если вы не выполняете сортировку по цвету. Сегодня Neeta просит VBA отсортировать данные Excel по цвету.
Самое сложное в сортировке с помощью VBA - выяснить, какие цветовые коды RGB вы используете. В 99% случаев вы не выбирали цвет, вводя значения RGB. Вы выбрали цвет в раскрывающемся списке Excel.
Большинство людей выбирают цвет заливки или шрифта в этом раскрывающемся списке.
И хотя вы можете использовать «Заливка», «Больше цветов», «Пользовательский», чтобы узнать, что выбранный цвет - RGB (112,48,160), это будет проблемой, если у вас много цветов.
Коды RGB скрыты в этом диалоговом окне.
Итак - я предпочитаю включать регистратор макросов и позволять регистратору макросов определять код. Код, созданный регистратором макросов, никогда не бывает идеальным. Вот видео, показывающее, как использовать макрорекордер при сортировке по цвету.
Стенограмма видео
Изучите Excel из подкаста, эпизод 2186: VBA Sort by Color.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос отправлен на YouTube. У меня было видео о том, как сортировать с помощью VBA, и они хотели отсортировать по цвету с помощью VBA, что намного сложнее. Я сказал: «Почему бы тебе просто не включить макрорекордер и посмотреть, что произойдет?» И, к сожалению, макрорекордер, вы знаете, приближает нас, но не до конца.
Итак, просмотр, макросы, запись макроса, «HowToSortByColor», сохранение макроса в этой книге - идеально. Щелкните ОК. Хорошо, теперь программа записи макросов запущена, мы перейдем на вкладку «Данные» и скажем «Сортировка». Мы собираемся использовать диалоговое окно «Сортировка», и мы его построим, хорошо? Итак, мы собираемся сказать, что хотим добавить уровень «Сортировка по вишне», но не «Сортировка по значениям ячеек»; мы собираемся выполнить сортировку по цвету ячейки - цвет ячейки - это цвет заливки - и мы хотим поставить красный цвет сверху, а затем скопировать этот уровень и поставить второй желтый; а затем мы добавим новый уровень - мы перейдем к столбцу D, столбцу даты - Сортировка по цвету ячейки, сначала красный, скопируем этот уровень, желтый, а затем сюда; а затем здесь, в Elderberry, в столбце E, есть несколько синих шрифтов, я не хочу видеть, как это выглядело,поэтому мы добавим это как Сортировка по цвету шрифта с синим сверху; а затем, если все это связано с отсутствием цветов вообще, мы добавим один последний уровень только в столбец A - Значения ячеек, от наибольшего к наименьшему; и нажмите ОК.
Хорошо, а теперь пара вещей - не пропускайте этот следующий шаг - прямо сейчас ваш файл, я гарантирую, вы сохранены как xlsx. Это прекрасное время, чтобы выполнить «Файл», «Сохранить как» и сохранить его как xlsm или xlsb. Если вы этого не сделаете, вся ваша работа до этого момента будет потеряна при сохранении этого файла. Они удалят макросы всего, что хранится в xlsx. Хорошо?
Итак, мы остановили там запись, а затем мы хотим посмотреть наши макросы. Итак, вы можете сделать это с помощью View, Macros - View, Macros - и найти макрос, который мы только что записали - HowToSortByColor - и нажать Edit. Хорошо, вот наш макрос, и когда я смотрю на него, проблема в том, что сегодня у нас 25 строк плюс заголовок. Итак, это идет вниз к строке 26. И они жестко запрограммировали, что они всегда будут смотреть вниз до строки 26.
Но когда я думаю об этом, особенно по сравнению со старым VBA для сортировки, нам не нужно указывать весь диапазон - только одну ячейку в столбце. Так что везде, где есть столбец C26, я собираюсь уменьшить его, чтобы просто сказать: «Эй, нет, посмотрите на первую ячейку в этом столбце». Итак, E2, а затем, здесь, A2. Итак, в моем случае у меня было 1, 2, 3, 4, 5, 6 уровней сортировки - 6 вещей, которые нужно изменить.
И затем это та часть, которую записывает макрос, очень, очень плохо, это то, что он будет постоянно сортировать только строки 26. Так что я собираюсь это изменить. Я собираюсь сказать: «Послушайте, начните с диапазона A21 и расширьте его до .CurrentRegion». Давайте взглянем на Excel и посмотрим, что он делает. Итак, если бы я просто выбрал любую одну ячейку - A1 или что-то еще - и нажал Ctrl + *, он выберет текущую область. Хорошо, давай сделаем это. Здесь, начиная с середины, Ctrl + *, и что это делает, он расширяется во всех направлениях, пока не достигнет края электронной таблицы, наверху электронной таблицы или у правого края данных или нижнего края данных . Итак, говоря A1 .CurrentRegion, это все равно что перейти к A1 и нажать Ctrl + *. Хорошо? Итак, здесь вы должны это изменить. Теперь все остальное в макросе в порядке; Это'все будет работать. У них есть SortOnCellColor, SortOnFontColor и xlSortOn. Мне не о чем беспокоиться; все, что мне нужно сделать, это заглянуть сюда и увидеть, что они жестко запрограммировали регион, который собирались использовать для диапазона, жестко запрограммировали, как далеко они зашли, и это не нужно жестко запрограммировать. И с помощью этого простого шага, изменив эти шесть элементов и седьмой элемент, у нас есть кое-что, что должно работать.
А теперь давайте проведем тест. Вернемся сюда в Excel и добавим несколько новых строк внизу. Я просто поставлю туда 11, а мы добавим пару красных - красный, желтый, а затем сюда синий. Хорошо. Итак, если мы запустим этот код - запустим этот код, я щелкну здесь и нажмите кнопку «Выполнить» - а затем вернусь, мы должны увидеть, что этот элемент 11 стал верхним элементом красного цвета, он появился там в желтый, и он проявляется в синем, так что все работает отлично. Почему он попал в топ? Поскольку случилось так, что последняя сортировка - это столбец A, и поэтому, когда есть ничья, он смотрит на столбец A как на средство разрешения конфликтов. Итак, этот код работает.
Чтобы научиться писать на VBA, я вместе с Трейси Сирстад написали серию книг, посвященных Excel, VBA и MACROS. Есть выпуск на 2003, 2007, 2010, 2013 и 2016 годы; скоро 2019. Хорошо, найдите версию, которая соответствует вашей версии Excel, и это поможет вам в обучении.
Подведение итогов: Сегодняшний выпуск - Как использовать VBA для сортировки по цвету. Самый простой способ сделать это, тем более что вы не знаете, какие коды RGB использовались для каждого из цветов - вы просто выбрали красный, вы не знаете, что такое код RGB, и вы не хотите идти смотреть it up - включите регистратор макросов, используя View, Macros, Record New Macro. После того, как вы закончите сортировку, нажмите Остановить запись - она находится в нижнем левом углу - Alt + F8, чтобы увидеть список макросов, или Просмотр, Макросы, Просмотр макроса - вкладка Просмотр, Макросы и затем Просмотреть макросы - это сбивает с толку. PВыберите свой макрос и нажмите «Изменить», и в любое время, когда вы увидите C2 для некоторых номеров диапазона, просто измените его так, чтобы он указывал на строку 2. И затем, где они указывают диапазон для сортировки, Range («A1»), CurrentRegion, развернется. Хорошо.
Что ж, эй, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз для еще одной сетевой трансляции от.
В видео я установил шестиуровневую сортировку. Конечное диалоговое окно показано здесь:
Сортировать по красному, желтому в C, красному, желтому в d, синему по e, цифрам в a
В тот день, когда мне довелось записать макрос, у меня было 23 строки данных плюс заголовок. В макросе было семь мест, которые жестко запрограммировали количество строк. Их нужно отрегулировать.
Для каждого уровня сортировки есть такой код:
Это глупо, что средство записи макросов указывает C2: C24. Вам нужно указать только одну ячейку в столбце, поэтому измените первую строку выше на:
Сделайте аналогичное изменение для каждого уровня сортировки.
Ближе к концу записанного макроса у вас есть записанный код для фактической сортировки. Все начинается так:
Вместо того, чтобы сортировать только A1: E24, измените код, чтобы он начинался с A1 и распространялся на текущий регион. (Текущий регион - это то, что вы получите, если нажмете Ctrl + * из ячейки).
Последний код, показанный на видео:
Вероятно, ваша книга сохранена с расширением XLSX. Выполните «Сохранить как», чтобы изменить расширение на XLSM или XLSB. Все макросы, сохраненные в XLSX, удаляются.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
Создатели Excel решили, начиная от 2007-ой версии ввести возможность сортировки данных по цвету. Для этого послужило поводом большая потребность пользователей предыдущих версий, упорядочивать данные в такой способ. Раньше реализовать сортировку данных относительно цвета можно было только с помощью создания макроса VBA. Создавалась пользовательская функция и вводилась как формула под соответствующим столбцом, по которому нужно было выполнить сортировку. Теперь такие задачи можно выполнять значительно проще и эффективнее.
Сортировка по цвету ячеек
Пример данных, которые необходимо отсортировать относительно цвета заливки ячеек изображен ниже на рисунке:
Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:
- Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
- Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
- Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
- Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.
Ожидаемый результат изображен ниже на рисунке:
Аналогичным способом можно сортировать данные по цвету шрифта или типу значка которые содержат ячейки. Для этого достаточно только указать соответствующий критерий в секции «Сортировка» диалогового окна настройки условий.
Фильтр по цвету ячеек
Аналогично по отношению к сортировке, функционирует фильтр по цвету. Чтобы разобраться с принципом его действия воспользуемся тем же диапазоном данных, что и в предыдущем примере. Для этого:
- Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
- Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
- Из всплывающего подменю выберите зеленый цвет.
В результате отфильтруються данные и будут отображаться только те, которые содержать ячейки с зеленым цветом заливки:
Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:
Как всегда, Excel нам предоставляет несколько путей для решения одних и тех же задач. Пользователь выбирает для себя самый оптимальный путь, плюс необходимые инструменты всегда под рукой.
Читайте также: