Excel как настроить фильтр по цветам

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

В Microsoft Office Excel, начиная с версии 2007 года, появилась возможность сортировки и фильтрации ячеек табличного массива по цвету. Такая функция позволяет быстрее ориентироваться в таблице, повышает ее презентабельность и эстетичность. В данной статье будут рассмотрены основные способы фильтрации информации в Excel по цвету.

Особенности фильтрации по цвету

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

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

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

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

1. Выделить требуемый диапазон ячеек левой клавишей манипулятора и переместиться во вкладку «Главная», находящуюся в верхней панели инструментов программы.
2. В появившейся области в подразделе Редактирование необходимо найти кнопку «Сортировка и фильтр» и развернуть ее, нажав на стрелочку внизу.

Варианты сортировки и фильтрации табличных данных в Excel

1. В отобразившемся меню нажать по строчке «Фильтр».

В окне выбора нужно нажать на кнопку «Фильтр»

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

Появившееся стрелочки в заголовки столбцов таблицы после добавления фильтра

1. После нажатия по стрелке в названии столбика отобразится похожее меню, в котором надо щелкнуть по строке Фильтр по цвету. Раскроется дополнительная вкладка с двумя доступными функциями: «Фильтр по цвету ячейки» и «Фильтр по цвету шрифта».

Варианты фильтрации в Эксель. Здесь можно выбрать любой цвет, который нужно расположить на верху таблицы

1. В разделе «Фильтр по цвету ячейки» выбрать оттенок, по которому нужно отфильтровать исходную таблицу, нажав на него ЛКМ.
2. Проверить результат. После проделывания вышеуказанных манипуляций в таблице останутся только ячейки с указанным ранее цветом. Остальные элементы пропадут, а табличка сократится.

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

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

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

Обратите внимание! В Microsoft Office Excel у функции фильтрации по цвету есть существенный недостаток. Пользователь может выбрать только один оттенок, по которому будет производиться фильтрация табличного массива. Указать несколько цветов сразу невозможно.

Как в Excel отсортировать данные по нескольким цветам

С сортировкой по цветам в Экселе обычно проблем не возникает. Она выполняется аналогичным образом:

1. По аналогии с предыдущим пунктом добавить фильтр к табличному массиву.
2. Щелкнуть по стрелочке, появившейся в названии столбца, и в выпадающем меню выбрать пункт «Сортировка по цвету».

Выбор сортировки по цвету

1. Указать требуемый тип сортировки, к примеру выбрать нужный оттенок в колонке «Сортировка по цвету ячейки».
2. После выполнения предыдущих манипуляций строчки таблицы с выбранным ранее оттенком будут располагаться на первом месте массива по порядку. Также можно отсортировать и остальные цвета.

Финальный результат сортировки ячеек по цвету в табличном массиве

Дополнительная информация! Отсортировать данные в табличке также можно с помощью функции «Настраиваемая сортировка», добавив несколько уровней по цветам.

Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции

Чтобы в Microsoft Office Excel выбрать фильтр для отображения сразу нескольких цветов в таблице, необходимо создать дополнительный параметров с оттенком заливки. По созданному оттенку данные в дальнейшем и будут фильтроваться. Пользовательская функция в Эксель создается по следующей инструкции:

1. Зайти в раздел «Разработчик», который находится сверху главного меню программы.
2. В открывшейся области вкладки кликнуть по кнопке «Visual Basic».
3. Откроется встроенный в программу редактор, в котором потребуется создать новый модуль и прописать код.

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

Чтобы применить созданную функцию, необходимо:

1. Вернуться на рабочий лист Excel и создать два новый столбца рядом с исходной таблицей. Их можно назвать «Цвет ячейки» и «Цвет текста» соответственно.

Созданные вспомогательные столбцы

1. В первом столбце написать формулу «=ColorFill()». В скобках указывается аргумент. Нужно кликнуть по ячейке с любым цветом в табличке.

Формула в столбце «Цвет ячейки»

1. Во втором столбике указать тот же самый аргумент, но только с функцией «=ColorFont()».

Формула в столбце «Цвет текста»

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

Получившееся данные после растягивания формулы

1. Добавить фильтр в табличный массив по рассмотренной выше схеме. Данные будут отсортированы по цвету.

Важно! Сортировка в Excel с помощью пользовательской функции выполняется аналогичным образом.

Заключение

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

Источник