Как сделать фильтр по цвету в excel

Добавил пользователь Alex
Обновлено: 05.10.2024

Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.

Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

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

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Public Function ColorFill(MyCell As Range)

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

Вернемся в Excel и применим новую функцию ColorFill — либо непосредственно введем формулу в ячейку, либо вызовем ее с помощью мастера функций (выбрав из категории Определенные пользователем).
В дополнительном столбце прописываем код заливки ячейки:

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

Функция цвета текста ячейки на VBA

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

Public Function ColorFont(MyCell As Range)

Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее применения аналогичен примеру рассмотренному выше.

Замечания

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

Они не работают с ячейками, в которых заливка определяется условным форматированием;
При изменении раскраски ячейки в Excel формулы автоматически не пересчитываются, в связи с этим пересчет нужно сделать самостоятельно (Shift + F9 для пересчета формул только на активном листе, F9 — для всей книги).

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

Сортировка по цвету ячеек

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

Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:

Ожидаемый результат изображен ниже на рисунке:

Фильтр по цвету ячеек

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

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

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

Из этой краткой инструкции Вы узнаете, как быстро сортировать по цвету заливки и шрифта ячейки на листе в Excel 2010 и 2013.

В одной из недавних статей мы рассматривали различные способы, как в Excel узнать количество ячеек определённого цвета и вычислить сумму значений в них. Если Вы не упустили шанс прочитать ту статью, у Вас, вероятно, возник вопрос, почему мы не уделили внимание сортировке и фильтрации ячеек по их цвету. Причина в том, что сортировка ячеек по цвету в Excel требует немного другого подхода. Далее мы рассмотрим этот вопрос подробнее.

Сортировка по цвету заливки ячейки
Сортировка по цвету шрифта
Сортировка по значку
Фильтрация ячеек в Excel по цвету заливки

Сортировка в Excel по цвету заливки ячейки

Выделите свою таблицу или диапазон ячеек.
На вкладке Главная (Home) в разделе Редактирование (Editing) нажмите кнопку Сортировка и фильтр (Sort & Filter) и в появившемся меню выберите Настраиваемая сортировка (Custom Sort).
В диалоговом окне Сортировка (Sort) настройте параметры слева направо:
- Укажите Столбец (Column), по которому нужно выполнить сортировку (в нашем примере это столбец Delivery);
- В поле Сортировка (Sort On) выберите Цвет ячейки (Cell Color);
- Установите цвет ячеек, которые должны быть вверху;
- В крайнем правом выпадающем списке выберите Сверху (On top).

В нашей таблице вверх переместились строки со значением Past Due (столбец Delivery), за ними следуют строки со значениями Due in и в конце – строки со значением Delivered. Все в точности, как мы настроили в диалоговом окне Сортировка (Sort).

Совет: Если ячейки раскрашены в большое количество различных цветов – не обязательно создавать правило для каждого. Настройте правила сортировки только для тех цветов, которыми отмечены действительно важные данные (в нашем примере это строки со значением Past Due), а все остальные оставьте как есть.

Если требуется отобрать ячейки только одного цвета, то можно воспользоваться ещё более быстрым способом. Кликните по стрелке автофильтра рядом с заголовком столбца, в котором нужно произвести сортировку. В открывшемся меню нажмите Сортировка по цвету (Sort by Color) и выберите, какого цвета ячейки должны оказаться вверху, а какие – внизу. Кстати, выбрав в этом меню пункт Пользовательская сортировка (Custom Sort), как показано на рисунке ниже, можно быстро перейти к диалоговому окну Сортировка (Sort).

Сортировка ячеек в Excel по цвету шрифта

Сортировка ячеек в Excel по цвету шрифта выполняется точно так же, как сортировка по цвету заливки. Снова используем инструмент Настраиваемая сортировка (Custom Sort), который находится на вкладке Главная (Home) в меню Сортировка и фильтр (Sort & Filter), но на этот раз в поле Сортировка (Sort On) выбираем Цвет шрифта (Font Color).

Если требуется отобрать ячейки с одним определённым цветом шрифта, то можно снова использовать автофильтр Excel:

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

Сортировка ячеек по значку

Настроим, для примера, условное форматирование с использованием наборов значков по столбцу Qty., где указано количество заказов (см. рисунок ниже):

Как видите, мы отметили крупные заказы (с количеством более 6) красными значками, средние заказы отмечены жёлтыми значками и самые маленькие – зелёными значками. Если нужно расположить самые важные заказы в начале списка, воспользуемся инструментом Настраиваемая сортировка (Custom Sort), как это было описано выше, и настроим сортировку по признаку – Значок ячейки (Cell Icon).

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

Как в Excel фильтровать ячейки по цвету заливки

Если нужно отфильтровать ячейки в заданном столбце по цвету заливки, используйте инструмент Фильтр по цвету (Filter by Color), доступный в Excel 2010 и 2013.

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

Создайте вспомогательный столбец в конце таблицы или рядом со столбцом, в котором будете настраивать фильтр. Мы назовём его Filter by color.
Во вторую ячейку только что добавленного столбца введите формулу:

Здесь F – это столбец, содержащий разукрашенные ячейки, и по которому нужно настроить фильтр, а GetCellColor() – пользовательская функция со следующим кодом VBA:

Скопируйте эту формулу во все ячейки столбца Filter by color.
Примените автофильтр Excel и затем выберите нужные цвета по их кодам в выпадающем меню автофильтра.

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

В этой заметке разберемся с тем, как сортировать в Excel по цвету ячейки.

Во-первых, стоит сказать, что в Excel есть два способа сортировки по цвету — сортировка по цвету ячейки и сортировка по цвету текста. Оба варианта могут быть использованы.

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

Чтобы отсортировать таблицу по цвету необходимо выделить любую ячейку диапазона (1), который требуется отсортировать и из меню Сортировка и фильтр (2) выбрать Настраиваемая сортировка (3).

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

Далее указать вариант сортировки (5) — Цвет ячейки или Цвет шрифта. И из выпадающего списка Порядок необходимо выбрать один из цветов (6).

Эти цвета Эксель автоматически берет из таблицы, нам остается лишь указать нужный. В завершение необходимо указать направление сортировки (7), то есть сверху или снизу будут находиться ячейки с выбранным нами цветом.

Далее добавляем уровнь, нажатием соответствующей кнопки и повторяем те же действия для следующего цвета этого же столбца.

Уровни означают следующее — после того, как данные таблицы будут отсортированы по цвету первого уровня, они будут сортироваться по второму цвету и так далее.

"Странный этот мир, где двое смотрят на одно и то же, а видят полностью противоположное." © Агата Кристи

MS Office и VBA Рубрика содержит интересные решения, малоизвестные функции и возможности, надстройки и макросы, в общем, все то, что может сделать вашу работу в пакете программ MS Office (в первую очередь - Excel, Word, Access) более эффективной.

Фильтр по цвету в Excel 2003

0.0 (0) | 13637 | 0

Уже, кажется, так давно работаю с Excel 2007 и выше, что иногда возвращаясь по необходимости к древнему Excel 2003, удивляешься почему там так многого нет. Одной из таких вещей является фильтр по цвету ячейки.

Те, кто имеют дело с анализом массивов данных в Excel, очень часто для удобства подкрашивают необходимые ячейки в разные цвета, чтобы структурно выделить какие-то моменты или просто выделить лишь то, что важно. Логичным завершением таких манипуляций является сортировка или фильтрация по выделенным цветам. К несчастью в Excel 2003 этот момент не реализован, в отличии от Excel 2007 и выше, где возможна и сортировка, и фильтр по цвету в пару кликов через инструментарий Автофильтра.

Чтобы все-таки воспользоваться возможностями ПК в анализе с помощью фильтрации цветов даже в Excel 2003 можно применить совсем маленькую пользовательскую функцию - ColorIndex.

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

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

Как это заставить работать?)

Для этого откройте редактор Visual Basic в Excel через меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда код выше представленной функции.

После этого можно закрыть редактор Visual Basic, вернуться в табличку Excel и, выделив любую пустую ячейку, вызвать созданную функцию ColorIndex через меню Вставка - Функция - категория Определенные пользователем (Insert - Function - User defined) и в качестве аргумента укажите ячейку, цвет заливки которой хотите получить в виде цифрового кода. Или просто пропишите в строке формулы: "=ColorIndex()", а между скобками укажите ссылку на нужную ячейку с цветом.

Применительно к спискам, такая функция позволит легко сортировать и фильтровать ячейки по цвету заливки.

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

А что если. хочется фильтр по цвету текста.

Это вполне реализуемо аналогичным способом, только код будет чуточку другой:

А функция соответственно будет называться ColorIndexF.

PS К минусам этих функций можно отнести только:

1. Они не могут определить цвета используемые в условном форматировании, только те цвета, которые "проставленны" вручную.

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

О сайте

"Понемногу обо всем и все, о немногом" - именно такой слоган, по-видимому, является наилучшим определением тематики блога. Здесь пишу о том, что для меня интересно или важно, собственно, поэтому разброс тематик очень широк – от размышлений на философские темы и смешных историй, до конкретных инструкций или анализа событий.

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

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

Вопрос дизайна – это актуальные тренды, пошаговые и видео-уроки в фотошопе, необходимые плагины для фоторедакторов, векторные и PSD исходники, PNG иконки и GIF анимации, кириллические шрифты с засечками и без засечек, заливки (паттерны) и градиенты.

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

MS Office и VBA – эта рубрика содержит интересные решения, малоизвестные функции и возможности, надстройки и макросы, в общем, все то, что может сделать вашу работу в пакете программ MS Office (в первую очередь - Excel, Word, Access, PowerPoint) более эффективной.

Прочие офисные программы – рубрика о программах для ведения учета (конфигурации, платформы, внешние отчеты для 1C), сдачи отчетности (MeDoc, БестЗвіт) и статистического анализа данных (SPSS), также здесь можно найти обзоры программного обеспечения для работы с периферийными устройствами. Свернуть

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

Важно: Часть содержимого этого раздела может быть неприменима к некоторым языкам.

Данные можно быстро фильтровать на основании визуальных условий, таких как цвет шрифта, цвет ячейки или наборы значков. Можно также фильтровать ячейки по наличию в них формата, примененных стилей или условного форматирования.

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

На вкладке Данные нажмите кнопку Фильтр.