Как в экселе сделать выборку по цвету ячейки

Обновлено: 03.07.2024

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

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

В данной таблице нам нужно выделить цветом все транзакции по конкретному клиенту. Для переключения между клиентами будем использовать выпадающий список. Поэтому в первую очередь следует подготовить содержание для выпадающего списка. Нам нужны все Фамилии клиентов из столбца A, без повторений.

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

Выделите первый столбец таблицы A1:A19.
Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

Перед тем как выбрать уникальные значения из списка сделайте следующее:

Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.

Выборка ячеек из таблицы по условию в Excel:

Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

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

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

равно;
не равно;
больше;
больше или равно;
меньше.

Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».

Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

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

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

Урок: Функция автофильтр в Excel

Способ 2: применение формулы массива

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

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

=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000
На работе столкнулся с такой задачей - имеется таблица в Excel, в которой ведется табель выходов рабочих в цеху.

В таблице подсчитывается количество часов, фактически отработанных; часов переработки и часов сверх нормы. Так вот, необходимо сделать так, чтобы производилась автоматическая выборка ячеек таблицы по цвету заливки последних.

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

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

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

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

Сразу скажу, что изображения были сделаны в Excel 2007. В Excel 2010 все несколько по другому, но запутаться невозможно, если что.

Режим “Разработчик” в Excel

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

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11.

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

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert — Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.

Вставленные функции появятся в списке формул таблицы:

Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.

Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

Обе функции имеют одинаковый синтаксис и принимают три входных аргумента, первые два из которых обязательные, а третий — необязательный:

— диапазон с ячейками для подсчета
— ячейка-образец с цветом заливки
— или учитывает скрытые ячейки; , или опущен(по умолчанию) — скрытые ячейки не подсчитываются.

Функция подсчета количества ячеек

Синтаксис этой функции прост:

Функция подсчета суммы ячеек

Синтаксис этой функции следующий:

При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно воспользоваться либо “Мастером функций”, либо произвести указание диапазона ячеек и ячейку-критерий вручную.

Описание рабочей формулы

Готовый пример работы функции “CountByInteriorColor” можно посмотреть на рисунке “Табель выходов с зелеными ячейками”. В нем подсчет отработанного времени производится по следующей формуле:

Фактически эта формула получается такой (смотри строку №13 на рисунке):

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.

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

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

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

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

Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
Из всплывающего подменю выберите зеленый цвет.

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

Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:

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

Читайте также: