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

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

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

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

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

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

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

Где находится пункт условное форматирование в Excek

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

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

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

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

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

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

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

Условное форматирование - правило меньше

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

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

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

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

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

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

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

  • Когда мы ввели все настройки отображения форматирования ячеек жмем кнопку “Ок”.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Так как система по-умолчанию фиксирует указанную ссылку в знак “$”, это означает, что при протягивании этого формата на другие ячейки, они будут сравниваться с указанной в первый раз ячейкой.

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

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

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

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

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

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

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

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

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

Вы можете применять несколько правил условного форматирования в Excel к одной ячейке.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Выделим диапазон ячеек, с которого мы хотим удалить условное форматирование;
  • Нажмем на пункт меню “Условное форматирование” на панели инструментов => кликнем по пункту “Удалить правила” и в раскрывающемся меню выберем метод удаления, наиболее подходящий для нашей ситуации:

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Подпишись на 

рассылку лайфхаков

в Excel!

Мы не делимся вашими данными ни с кем!