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

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

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

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

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

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

• Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
• Применение пользовательских функций.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Замечания

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

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!