Как в экселе оставить только выделенные цветом строки
В Microsoft Office Excel, начиная с версии 2007 года, появилась возможность сортировки и фильтрации ячеек табличного массива по цвету. Такая функция позволяет быстрее ориентироваться в таблице, повышает ее презентабельность и эстетичность. В данной статье будут рассмотрены основные способы фильтрации информации в Excel по цвету.
Особенности фильтрации по цвету
Прежде чем переходить к рассмотрению способов фильтрации данных по цвету, необходимо проанализировать преимущества, которые дает такая процедура:
- Структурирование и упорядочение информации, что позволяет выделить нужный фрагмент таблички и быстро найти его в большом диапазоне ячеек.
- Выделенные цветом ячейки с важной информацией в дальнейшем можно будет проанализировать.
- Фильтрация по цвету выделяет сведения, удовлетворяющие заданным критериям.
Как отфильтровать данные по цвету с помощью встроенной в Excel опции
Алгоритм фильтрации информации по цвету в табличном массиве Excel подразделяется на следующие этапы:
- Выделить требуемый диапазон ячеек левой клавишей манипулятора и переместиться во вкладку «Главная», находящуюся в верхней панели инструментов программы.
- В появившейся области в подразделе Редактирование необходимо найти кнопку «Сортировка и фильтр» и развернуть ее, нажав на стрелочку внизу.
- В отобразившемся меню нажать по строчке «Фильтр».
- Когда фильтр добавится, то в названии столбцов таблицы появятся небольшие стрелочки. На этом этапе по любой из стрелок пользователю нужно кликнуть ЛКМ.
- После нажатия по стрелке в названии столбика отобразится похожее меню, в котором надо щелкнуть по строке Фильтр по цвету. Раскроется дополнительная вкладка с двумя доступными функциями: «Фильтр по цвету ячейки» и «Фильтр по цвету шрифта».
- В разделе «Фильтр по цвету ячейки» выбрать оттенок, по которому нужно отфильтровать исходную таблицу, нажав на него ЛКМ.
- Проверить результат. После проделывания вышеуказанных манипуляций в таблице останутся только ячейки с указанным ранее цветом. Остальные элементы пропадут, а табличка сократится.
Отфильтровать данные в массиве Excel можно вручную, удалив строки и столбцы с ненужными цветами. Однако пользователю придется потратить на выполнение этого процесса дополнительное время.
Если выбрать нужный оттенок в разделе «Фильтр по цвету шрифта», то в таблице останутся только строчки, текст шрифта в которых прописан выбранным цветом.
Обратите внимание! В Microsoft Office Excel у функции фильтрации по цвету есть существенный недостаток. Пользователь может выбрать только один оттенок, по которому будет производиться фильтрация табличного массива. Указать несколько цветов сразу невозможно.
Как в Excel отсортировать данные по нескольким цветам
С сортировкой по цветам в Экселе обычно проблем не возникает. Она выполняется аналогичным образом:
- По аналогии с предыдущим пунктом добавить фильтр к табличному массиву.
- Щелкнуть по стрелочке, появившейся в названии столбца, и в выпадающем меню выбрать пункт «Сортировка по цвету».
- Указать требуемый тип сортировки, к примеру выбрать нужный оттенок в колонке «Сортировка по цвету ячейки».
- После выполнения предыдущих манипуляций строчки таблицы с выбранным ранее оттенком будут располагаться на первом месте массива по порядку. Также можно отсортировать и остальные цвета.
Дополнительная информация! Отсортировать данные в табличке также можно с помощью функции «Настраиваемая сортировка», добавив несколько уровней по цветам.
Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции
Чтобы в Microsoft Office Excel выбрать фильтр для отображения сразу нескольких цветов в таблице, необходимо создать дополнительный параметров с оттенком заливки. По созданному оттенку данные в дальнейшем и будут фильтроваться. Пользовательская функция в Эксель создается по следующей инструкции:
- Зайти в раздел «Разработчик», который находится сверху главного меню программы.
- В открывшейся области вкладки кликнуть по кнопке «Visual Basic».
- Откроется встроенный в программу редактор, в котором потребуется создать новый модуль и прописать код.
Чтобы применить созданную функцию, необходимо:
Важно! Сортировка в Excel с помощью пользовательской функции выполняется аналогичным образом.
Заключение
Таким образом, в MS Excel можно быстро отфильтровать исходный табличный массив по цвету ячеек различными методами. Основные способы фильтрации и сортировки, которыми рекомендуется пользоваться при выполнении поставленной задачи, были рассмотрены выше.
Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?
При проведении анализа данных сортировка и фильтрация помогают структурировать, упорядочивать данные или производить поиск.
К примеру, ячейки или диапазоны данных можно окрашивать в различные цвета с целью выделения конкретных данных, которые в последствии можно дополнительно проанализировать.
При этом фильтрация позволяет показать только те данные, которые удовлетворяют заданным критериям, и соответственно, скрыть те данные, которые не подходят по условию.
Сортировка же показывает все данные, однако упорядочивает их согласно заданным критериям.
Стандартный фильтр и сортировка по цвету в Excel
Предположим, что у нас имеется таблица с наименованиями и выручкой по различным продуктам, где определенные категории дополнительно выделены цветом заливки ячейки и текста:
Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:
Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.
С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.
В результате синие ячейки расположились вверху таблицы, однако остальные идут вразнобой и не упорядочены.
На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:
Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:
Сортировка и фильтр по цвету с помощью функций
Как мы выяснили стандартным способом отфильтровать данные по нескольким цветам не получится, однако это ограничение можно обойти с помощью создания дополнительного параметра с цветом заливки (или текста), по которому далее и будем сортировать или фильтровать данные.
Функция цвета заливки ячейки на VBA
Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:
Скрыть строки на основе цвета ячейки с кодом VBA
Чтобы скрыть строки на основе определенного цвета фона, вам может помочь следующий код VBA, сделайте следующее:
1. Удерживайте ALT + F11 ключи, затем он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.
Код VBA: скрыть строки в зависимости от цвета ячейки:
3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о выборе ячеек, которые вы хотите использовать, см. снимок экрана:
4, Затем нажмите OK кнопки, все ячейки голубого цвета были скрыты сразу, см. снимок экрана:
Ноты:
1. В приведенном выше коде число 20 в этом сценарии Если xRg.Range ("A" & I) .Interior.ColorIndex = 20 Затем стоит порядковый номер цвета, вы можете изменить его по своему усмотрению.
2. Если вы не уверены в номере индекса цвета, вам следует применить следующий код, чтобы получить индекс цвета, который вы хотите знать:
А затем введите формулу: = GetColor (A2) чтобы вернуть конкретный номер индекса цвета фона в ячейке A2, вы измените A2 на другие по мере необходимости.
Скрыть строки на основе цвета ячейки с помощью Kutools for Excel
Если у вас есть Kutools for Excel, С его Выбрать ячейки с форматом вы можете сначала выбрать определенные цветные ячейки, а затем сразу скрыть их.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Kutools > Выберите > Выбрать ячейки с форматом, см. снимок экрана:
2. В Выбрать ячейки с форматом диалоговом окне выполните следующие операции:
(1.) Щелкните кнопка для выбора диапазона данных, который вы хотите использовать;
(2.) Затем щелкните Выбрать формат из ячейки кнопка для выбора определенной цветной ячейки, по которой вы хотите скрыть строки;
(3.) Все атрибуты конкретной ячейки были перечислены в списке, пожалуйста, просто отметьте Цвет фона под Заполнять .
3. Затем нажмите Ok кнопка, все ячейки с определенным цветом были выбраны сразу, а затем нажмите Главная > Формат > Скрыть и показать > Скрыть строки, см. снимок экрана:
4. И затем выбранные ячейки с определенным цветом скрываются, см. Снимок экрана:
Создатели Excel решили, начиная от 2007-ой версии ввести возможность сортировки данных по цвету. Для этого послужило поводом большая потребность пользователей предыдущих версий, упорядочивать данные в такой способ. Раньше реализовать сортировку данных относительно цвета можно было только с помощью создания макроса VBA. Создавалась пользовательская функция и вводилась как формула под соответствующим столбцом, по которому нужно было выполнить сортировку. Теперь такие задачи можно выполнять значительно проще и эффективнее.
Сортировка по цвету ячеек
Пример данных, которые необходимо отсортировать относительно цвета заливки ячеек изображен ниже на рисунке:
Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:
- Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
- Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
- Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
- Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.
Ожидаемый результат изображен ниже на рисунке:
Аналогичным способом можно сортировать данные по цвету шрифта или типу значка которые содержат ячейки. Для этого достаточно только указать соответствующий критерий в секции «Сортировка» диалогового окна настройки условий.
Фильтр по цвету ячеек
Аналогично по отношению к сортировке, функционирует фильтр по цвету. Чтобы разобраться с принципом его действия воспользуемся тем же диапазоном данных, что и в предыдущем примере. Для этого:
- Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
- Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
- Из всплывающего подменю выберите зеленый цвет.
В результате отфильтруються данные и будут отображаться только те, которые содержать ячейки с зеленым цветом заливки:
Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:
Как всегда, Excel нам предоставляет несколько путей для решения одних и тех же задач. Пользователь выбирает для себя самый оптимальный путь, плюс необходимые инструменты всегда под рукой.
Читайте также: