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

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

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

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

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

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

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

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

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

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

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

При применении условного форматирования системе нужно задать две опции:

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

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

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

Перейдем на вкладку “Основная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем перечне вы увидите несколько типов формата на выбор:
- Правила выделения
- Правила отбора первых и крайних значений
- Гистограммы
- Цветовые шкалы
- Наборы значков
Также, доступны последующие условия:
1. Значения больше либо равны какому-либо значению;
2. Выделять текст, содержащий определенные буковкы либо слова;
3. Выделять цветом дубликаты;
4. Выделять определенные даты.
- Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, потому что нам необходимо выделить цветом отрицательные значения. В выпадающем перечне справа выберем формат отвечающих условиям:
- Для присвоения формата вы сможете применять пред настроенные цветовые палитры, также сделать свою гамму. Для этого кликните по пт:
- Во всплывающем окне формата укажите:
  - цвет заливки
  - цвет шрифта
  - шрифт
  - границы ячеек
  - По окончании опций нажмите клавишу “ОК”.
  Ниже пример таблицы с применением условного форматирования по данным нами характеристикам. Данные с отрицательными значениями выделены красноватым цветом:
  
  Как сделать правило
  
  Если пред настроенные условия не подступают, вы сможете создавать свои правила. Для опции проделаем последующие шаги:
  - Выделим спектр данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем перечне выберем пункт “Новое правило”:
  - Во всплывающем окне нам необходимо избрать тип используемого правила. В нашем примере нам подойдет тип “Форматировать лишь ячейки, которые содержат”. Опосля этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:
  - Кликнем на клавишу “Формат” и зададим формат, как мы это делали в примере выше. Нажмите клавишу “ОК”:
  Условное форматирование по значению иной ячейки
  
  На примерах выше мы задавали формат ячейкам, на базе их собственных значений. В Excel может быть задавать формат, на базе значений из остальных ячеек. К примеру, в таблице с данными курса бакса мы можем выделить цветом ячейки по правилу. Если курс бакса ниже чем в прошлом месяце, то значение курса в текущем месяце будет выделено цветом.
  
  Для сотворения условия по значению иной ячейки выполним последующие шаги:
  - Выделим первую ячейку для предназначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
  - Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Избираем формат. Жмем клавишу “ОК”.
  - Повторно выделим левой кнопкой мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на клавишу “Поменять правило”:
  - В поле слева всплывающего окна “очистим” ссылку от знака “$”. Жмем клавишу “ОК”, а потом клавишу “Применить”.
  - Сейчас нам необходимо присвоить настроенный формат на другие ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, потом в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:
  На снимке экрана ниже цветом выделены данные, в каких курс валюты стал ниже к предшествующему периоду:
  
  Как применить несколько правил условного форматирования к одной ячейке
  
  Может быть использовать несколько правил к одной ячейке.
  
  К примеру, в таблице с прогнозом погоды мы желаем закрасить различными цветами характеристики температуры. Условия выделения цветом: если температура выше 10 градусов – зеленоватым цветом, если выше 20 градусов – желтоватый, если выше 30 градусов – красноватым.
  
  Для внедрения нескольких критерий к одной ячейке выполним последующие деяния:
  - Выделим спектр с данными, к которым мы желаем применить условное форматирование => кликнем по пт “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем 1-ое условие (если больше 10, то зеленоватая заливка). Такие же деяния повторим для всякого из критерий (больше 20 и больше 30). Не глядя на то, что мы применили три правила, данные в таблице закрашены зеленоватым цветом:
  - Кликнем на всякую ячейку с присвоенным форматированием. Потом, опять кликнем по пт “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к наименьшему и напротив первых 2-ух поставим галочку “Приостановить, если правда”. Этот пункт дозволяет не использовать другие правила к ячейке, при согласовании первому. Потом кликнем клавишу “Применить” и “ОК”:
  Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в согласовании с нашими критериями.
  
  Как редактировать правило условного форматирования
  
  Для редактирования присвоенного правила сделайте последующие шаги:
  - Выделить левой кнопкой мыши ячейку, правило которой вы желаете отредактировать.
  - Перейдите в пункт меню панели инструментов “Условное форматирование”. Потом, в пункт “Управление правилами”. Щелкните левой кнопкой мыши по правилу, которое вы желаете отредактировать. Кликните на клавишу “Поменять правило”:
  - Опосля внесения конфигураций нажмите клавишу “ОК”.
  Как копировать правило условного форматирования
  
  Для копирования формата на остальные ячейки выполним последующие деяния:
  - Выделим спектр данных с примененным условным форматированием. Кликнем по пт на панели инструментов “Формат по эталону”.
  - Левой кнопкой мыши выделим спектр, к которому желаем применить скопированные правила формата:
  Как удалить условное форматирование
  
  Для удаления формата проделайте последующие деяния:
  - Выделите ячейки;
  - Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пт “Удалить правила”. В раскрывающемся меню изберите способ удаления:
  Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!
  
  Спасибо, весьма нужный веб-сайт!
  Вопросец:
  Есть таблица с остатками на складе. Крайний столбец “остаток” – это формула “приход” минус “выдали”.
  Пробую по вашей статье сделать правило автоматической расцветки строчки со значением “0” в ячейке “остаток”. Но при разработке условного форматирования выдаёт ошибку – ячейка уже содержит формулу, а не попросту число. К тому же окрашиваются лишь ячейки с необходимым значением, а не вся строчка. Есть метод решить такую делему?
  
  Выделение строк таблицы в EXCEL зависимо от условия в ячейке
  
  Если значение в ячейке удовлетворяет определенному юзером условию, то при помощи Условного форматирования можно выделить эту ячейку (к примеру, поменять ее фон). В данной нам статье пойдем далее — будем выделять всю строчку таблицы, содержащую эту ячейку.
  
  Пусть в спектре А6:С16 имеется таблица с списком работ, сроками выполнения и статусом их окончания (см. файл примера ).
  
  Задача1 — текстовые значения
  
  Нужно выделить цветом строчку, содержащую работу определенного статуса. К примеру, если работа не начата, то строчку будем выделять красноватым, если работа еще не завершена, то сероватым, а если завершена, то зеленоватым. Выделять строчки будем при помощи правил Условного форматирования .
  
  Решение1
  
  Сделаем маленькую табличку со статусами работ в спектре Е6:Е9 .
  
  Выделим спектр ячеек А7:С17 , содержащий список работ, и установим через меню Основная/ Цвет заливки фон заливки красноватый (предполагаем, что все работы вначале находятся в статусе Не начата ).
  
  Убедимся, что выделен спектр ячеек А7:С17 ( А7 обязана быть активной ячейкой ). Вызовем команду меню Условное форматирование/ Сделать правило / Употреблять формулу для определения форматируемых ячеек .
  - в поле « Форматировать значения, для которых последующая формула является настоящей » необходимо ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Направьте внимание на использоване смешанных ссылок ;
  - надавить клавишу Формат ;
  - избрать вкладку Заливка ;
  - избрать сероватый цвет ;
  - Надавить ОК.
  ВНИМАНИЕ : Снова обращаю внимание на формулу =$C7=$E$8 . Обычно юзеры вводят =$C$7=$E$8 , т.е. вводят излишний знак бакса.
  
  Необходимо сделать подобные деяния для выделения работ в статусе Завершена . Формула в этом случае будет смотреться как =$C7=$E$9 , а цвет заливки установите зеленоватый.
  
  В итоге наша таблица воспримет последующий вид.
  
  Примечание : Условное форматирование перекрывает обыденный формат ячеек. Потому, если работа в статусе Завершена, то она будет выкрашена в зеленоватый цвет, не глядя на то, что ранее мы установили красноватый фон через меню Основная/ Цвет заливки .
  
  Как это работает?
  
  В файле примера для пояснения работы механизма выделения строк, сотворена доборная таблица с формулой =$C7=$E$9 из правила Условного форматирования для зеленоватого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и на право.
  
  Как видно из рисунка, в строчках таблицы, которые выделены зеленоватым цветом, формула возвращает значение ИСТИНА.
  
  В формуле применена относительная ссылка на строчку ($C7, перед номером строчки нет знака $). Отсутствие знака $ перед номером строчки приводит к тому, что при копировании формулы вниз на 1 строчку она меняется на =$C8=$E$9 , потом на =$C9=$E$9 , позже на =$C10=$E$9 и т.д. до конца таблицы (см. ячейки G8 , G9 , G10 и т.д.). При копировании формулы на право либо на лево по столбцам, конфигурации формулы не происходит, конкретно потому цветом выделяется вся строчка.
  
  В случае затруднений можно потренироваться на примерах, приведенных в статье Условное форматирование в MS EXCEL .
  
  Прием с доборной таблицей можно использовать для тестирования всех формул Условного форматирования .
  
  Советы
  
  При вводе статуса работ принципиально не допустить опечатку. Если заместо слово Завершен а , к примеру, юзер введет Завершен о , то Условное форматирование не сработает.
  
  Чтоб исключить неправильный ввод используйте идеи из статьи Ввод данных из перечня значений. Часть1. Выпадающий перечень . В файле примера для ввода статусов работ применен аналогичный Выпадающий перечень .
  
  Чтоб стремительно расширить правила Условного форматирования на новейшую строчку в таблице, выделите ячейки новейшей строчки ( А17:С17 ) и нажмите сочетание кнопок CTRL+D . Правила Условного форматирования будут скопированы в строчку 17 таблицы.
  
  Задача2 — Даты
  
  Представим, что ведется журнальчик посещения сотрудниками научных конференций (см. файл примера лист Даты ).
  
  К огорчению, столбец Дата посещения не отсортирован и нужно выделить дату первого и крайнего посещения всякого сотрудника. К примеру, сотрудник Козлов 1-ый раз поехал на конференцию 24.07.2009, а крайний раз — 18.07.2015.
  
  Поначалу сделаем формулу для условного форматирования в столбцах В и E. Если формула возвратит значение ИСТИНА, то соответственная строчка будет выделена, если ЛОЖЬ, то нет.
  
  В столбце D сотворена формула массива = МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая описывает наивысшую дату для определенного сотрудника.
  
  Примечание: Если необходимо найти наивысшую дату вне зависимости от сотрудника, то формула существенно упростится = $B7=МАКС($B$7:$B$16) и формула массива не пригодится.
  
  Сейчас выделим все ячейки таблицы без заголовка и сделаем правило Условного форматирования . Скопируем формулу в правило (ее не надо вводить как формулу массива!).
  
  Сейчас представим, что столбец с датами отсортировали и требуется выделить строчки у каких даты посещения попадают в определенный спектр.
  
  Для этого используйте формулу =И($B23>$E$22;$B23
  
  Для ячеек Е22 и Е23 с граничными датами (выделены желтоватым) применена абсолютная адресация $E$22 и $E$23. Т.к. ссылка на их не обязана изменяться в правилах УФ (Ультрафиолетовое излучение — электромагнитное излучение, занимающее диапазон между фиолетовой границей видимого излучения и рентгеновским излучением) для всех ячеек таблицы.
  
  Для ячейки В22 применена смешанная адресация $B23, т.е. ссылка на столбец В не обязана изменяться (для этого стоит перед В символ $), а вот ссылка на строчку обязана изменяться зависимо от строчки таблицы (по другому все значения дат будут сравниваться с датой из В23 ).
  
  Таковым образом, правило УФ (Ультрафиолетовое излучение — электромагнитное излучение, занимающее диапазон между фиолетовой границей видимого излучения и рентгеновским излучением) к примеру для ячейки А27 будет смотреться =И($B27>$E$22;$B27 , т.е. А27 будет выделена, т.к. в данной нам строке дата из В27 попадает в обозначенный спектр (для ячеек из столбца А выделение все равно будет выполняться зависимо от содержимого столбца В из той же строчки — в этом и состоит «мистика» смешанной адресации $B23).
  
  А для ячейки В31 правило УФ (Ультрафиолетовое излучение — электромагнитное излучение, занимающее диапазон между фиолетовой границей видимого излучения и рентгеновским излучением) будет смотреться =И($B31>$E$22;$B31 , т.е. В31 не будет выделена, т.к. в данной нам строке дата из В31 не попадает в обозначенный спектр.
  
  Выделение ячеек цветом (цветная заливка).
  
  Для большинства юзеров программки Excel выделение ячеек необходимым цветом не составляет особенного труда, но на исходных шагах исследования интерфейса программки могут возникать вопросцы по заливке ячеек цветом.
  
  Разглядим несколько методов заливки цветом подходящих ячеек в программке Excel.
  
  1-ый — самый обычной метод, пользоваться особым инвентарем для заливки на панели резвого доступа.
  
  Инструмент заливки ячеек размещен на панели резвого доступа во вкладке «Основная» в разделе «Шрифт» . Чтоб закрасить ячейку, следует ее выделить левой клавишей мыши и избрать подходящий цвет на панели в разделе «Шрифт» > «Цвет заливки».
  
  2-ой метод подступает тем, кто по каким-то причинам удалил обозначенный выше инструмент из панели резвого доступа.
  
  Чтоб окрасить ячейки в подходящий цвет следует выполнить последующие деяния:
  1. Выделить ячейки левой клавишей мыши;
  2. Кликнуть по выделению правой клавишей мыши;
  3. В показавшемся контекстном меню кликнуть по строке «Формат ячеек…»;
  4. В показавшемся окне формы опций следует избрать вкладку «Заливка»;
  5. На вкладке заливка Вы сможете избрать понравившийся Для вас колер из предложенных либо избрать личный нажав на клавишу «Остальные цвета…» ;
  6. Опосля нажатия клавиши «ОК» ячейки приобретут подходящую заливку.
  3-ий метод – заливка, зависящая от заполнения ячеек (условная).
  
  Заливка по условиям заполнения ячейки производится при помощи функции «Условное форматирование». Клавиша условного форматирования находится на вкладке «Основная».
  
  Разглядим условное форматирование по последующим аспектам:
  
  Ячейки, имеющие значение меньше «5» залить зеленоватым цветом, выше «5» – красноватым.
  
  Чтоб настроить условное форматирование ячеек следует выполнить последующие деяния:
  1. Выделить нужные ячейки левой клавишей мыши;
  2. Избрать и надавить клавишу «Условное форматирование» во вкладке «Основная» панели резвого доступа;
  3. Избрать правило форматирования — указать условие и цвет заливки. (для нашего примера следует сделать два правила: 1) меньше 5 – зеленоватая заливка; 2) больше 5 — красноватая)
  4. Надавить «Enter».
  Условное форматирование строчки в Excel
  
  Пример условного форматирования всей строчки на листе Excel зависимо от содержимого одной ячейки в данной нам строке.
  
  Условие примера
  1. Заливка строчки зеленоватым фоном, если в третьей ячейке (столбец «C») данной нам строчки содержится значение «Зеленоватый».
  2. Заливка строчки голубым фоном, если в третьей ячейке (столбец «C») данной нам строчки содержится значение «Голубой».
  Решение примера
  
  1. Правило условного форматирования будет использовано ко всему рабочему листу Excel, если перед его созданием выделить весь лист. Стремительно это можно создать, нажав на треугольник в верхнем левом углу на пересечении наименований строк и столбцов.
  
  2. Жмем клавишу «Условное форматирование» на ленте инструментов «Основная» и избираем ссылку «Сделать правило…»:
  
  3. В окне «Создание правила форматирования» избираем строчку «Употреблять формулу для определения форматируемых ячеек»:
  
  4. В поле «Форматировать значения, для которых последующая формула является настоящей» вставляем условие =$C1=»Зеленоватый» . Дальше, нажав клавишу «Формат…», на вкладке «Заливка» избираем зеленоватый цвет и жмем клавишу «OK»:
  
  5. Опосля выбора заливки и возврата к форме «Создание правила форматирования» жмем клавишу «OK».
  
  6. Повторяем шаги 1-5, лишь на 4 шаге в поле «Форматировать значения, для которых последующая формула является настоящей» вставляем условие =$C1=»Голубой» , и на вкладке «Заливка» избираем голубой цвет:
  
  7. Жмем клавишу «Условное форматирование» на ленте инструментов «Основная» и избираем ссылку «Управление правилами…»:
  
  8. В открывшемся окне «Диспетчер правил условного форматирования» можно просмотреть и отредактировать сделанные правила:
  
  9. Вводим в ячейки столбца «C» наименования цветов и смотрим результаты условного форматирования всей строчки:
  
  Форматирование части строчки в Excel
  
  Пример условного форматирования части строчки на листе Excel зависимо от содержимого одной либо 2-ух ячеек в данной нам строке.
  
  Условие примера
  1. Заливка строчки желтоватым фоном, если в третьей ячейке (столбец «C») данной нам строчки содержится значение «Да».
  2. Заливка строчки сероватым фоном, если в четвертой ячейке (столбец «D») данной нам строчки содержится значение «Нет».
  3. Заливка строчки красноватым фоном, если в третьей ячейке (столбец «C») данной нам строчки содержится значение «Да», а в четвертой ячейке (столбец «D») – значение «Нет».
  4. Заливка применяется к 5 первым ячейкам хоть какой строчки.
  Решение примера
  
  1. Выделяем 1-ые 5 столбцов, чтоб задать спектр, к которому будут применяться создаваемые правила условного форматирования:
  
  2. Создаем 1-ое правило: условие – =$C1=»Да» , цвет заливки – желтоватый:
  
  3. Создаем 2-ое правило: условие – =$D1=»Нет» , цвет заливки – сероватый:
  
  4. Создаем третье правило: условие – =И($C1=»Да»;$D1=»Нет») , цвет заливки – красноватый:
  
  5. Проверяем сделанные правила в «Диспетчере правил условного форматирования». Лицезреем, что спектры, к которым используются правила, отобразились правильно:
  
  6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строчки:
  
  Направьте внимание на то, что в спектре, к которому используются правила условного форматирования, недозволено перетаскивать ячейки. Это нарушит его абсолютный адресок в правилах, и работать функция будет не так, как задумано. Копирование, вставка и копирование перетягиванием ячеек не влияют на абсолютный адресок спектра.
  
  Если все-же абсолютный адресок спектра нарушен, поправить его можно конкретно в «Диспетчере правил условного форматирования».
  
  Скачать файл Excel с примерами. На первом листе реализовано условное форматирование всей строчки, на втором – ее части.
  
  Интересно почитать: Как в excel в ячейке поставить тире