Как сделать выборку в excel из списка с условным форматированием

Условное форматирование в Excel

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

  1. Основы условного форматирования в Excel
  2. Где находится условное форматирование в Эксель?
  3. Как сделать условное форматирование в Excel?
  4. Как создать правило
  5. Условное форматирование по значению другой ячейки
  6. Как применить несколько правил условного форматирования к одной ячейке
  7. Как редактировать правило условного форматирования
  8. Как копировать правило условного форматирования
  9. Как удалить условное форматирование

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

  • Каким ячейкам вы хотите задать формат;
  • По каким условиям будет присвоен формат.

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

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

Условное форматирование в Excel - выделение диапазона данных

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:
  • Условное форматирование - правило меньше

    Также, доступны следующие условия:

    1. Значения больше или равны какому-либо значению;
    2. Выделять текст, содержащий определенные буквы или слова;
    3. Выделять цветом дубликаты;
    4. Выделять определенные даты.
    • Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

    Форматирование ячеек в Excel меньше чем

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

    Пользовательский формат условного форматирования в Эксель

    • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

    Пользовательский формат ячеек

    • По завершении настроек нажмите кнопку “ОК”.

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

    условное форматирование отрицательных значений

    Как создать правило

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

    • Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

    Создание собственного правила условного форматирования в Excel

    • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

    условное форматирование на основе значений между числами

    • Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

    условное форматирование по собственному правилу

    Условное форматирование по значению другой ячейки

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

    Для создания условия по значению другой ячейки выполним следующие шаги:

    • Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
    • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

    условное форматирование в Excel по значению другой ячейки

    • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

    Изменение правила ячейки по значению другой ячейки

    • В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

    Условное форматирование в Excel по значению другой ячейки 2

    • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

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

    На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

    Применение условного форматирования по значению другой ячейки в Эксель

    Как применить несколько правил условного форматирования к одной ячейке

    Возможно применять несколько правил к одной ячейке.

    Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

    Для применения нескольких условий к одной ячейке выполним следующие действия:

    • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

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

    • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

    Условное форматирование в Эксель с несколькими условиями

    Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

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

    Как редактировать правило условного форматирования

    Для редактирования присвоенного правила выполните следующие шаги:

    • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
    • Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

    Изменение правила ячейки по значению другой ячейки

    • После внесения изменений нажмите кнопку “ОК”.

    Как копировать правило условного форматирования

    Для копирования формата на другие ячейки выполним следующие действия:

    • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
    • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

    Как удалить условное форматирование

    Для удаления формата проделайте следующие действия:

    • Выделите ячейки;
    • Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

    удаление правил условного форматирования в Эксель

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

    Как сделать выборку в Excel из списка с условным форматированием

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

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

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

    История взаиморасчетов.

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

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

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

    Только уникальные записи.

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

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

    Вставить 2 строки.

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

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

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

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

    выпадающих список.

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

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

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

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

    Зеленая заливка.

    Готово.

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

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

    Ссылка на основную публикацию