Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
- Выделите первую ячейку диапазона с условным форматированим.
- Откройте меню инструмента, нажмите «Управление правилами».
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
- 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
- 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
Применим к нему «УФ» — «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Все очень просто. Трюки в эксель.. Условное форматирование 3
" data-medium-file="https://i0.wp.com/vseochenprosto.ru/wp-content/uploads/2017/03/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3Urok.png?fit=300%2C300&ssl=1" data-large-file="https://i0.wp.com/vseochenprosto.ru/wp-content/uploads/2017/03/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3Urok.png?fit=300%2C300&ssl=1" />Я снова рад приветствовать Вас уважаемые коллеги на занятиях по изучению огромных возможностей Exсel. Сегодня мы продолжим изучение функций условного форматирования и рассмотрим Условное форматирование при нескольких условиях. Создать правило. Условное форматирование строки по значению ячейки. Информационное окно. В имеющейся базе данных нам к примеру, необходимо выбрать числа, значение которых превышают три определенных порога (10, 20, 40). Для примера используем имеющийся у нас исходный диапазон чисел A1:A15, выделяем его, определяемся с условиями выделения информации (желтый цвет–числа > 10, зеленый цвет– числа > 20, красный цвет– числа > 40) и задаем правило форматирования. И так:
Условное форматирование при нескольких условиях
Рассмотрим несколько вариантов решения данной задачи.
1 вариант
Мы уже ранее изучали применение данного правила, но тем не менее идем по порядку. Напоминаю – исходный диапазон у нас выделен. Переходим в меню «Условное форматирование», выбираем «Правила выделения ячеек» и задаем условие — «больше»
Все очень просто. Условное форматирование 3
В выпавшем окне в левом поле задаем параметр, «10», а в правом, выбираем способ отображения данных в которых значение, больше установленного нами числа (желтый цвет)
Все очень просто. Условное форматирование 3.15
Нажимаем кнопку «ok» и повторяем эту процедуру для другого порога «20», только цвет выбираем (зеленый). И то же самое проделываем для третьего правила числа «40» и цвет естественно выбираем (красный)В результате получаем таблицу с выделенными данными по заданным нами условиям форматирования
Все очень просто. Условное форматирование 3.16
Попробуйте применить эту функцию в копиях своих наработок, кстати, параметры отображения (способы заливки, выбор цвета) Вы можете определять сами. Вот один из вариантов:
Все очень просто. Условное форматирование 3.1
И их очень много. Как это сделать? я обязательно расскажу в одном из следующих уроков.
2 вариант
Имея выделенный диапазон,заходим в меню «Условное форматирование» и выбираем «создать правило»
Все очень просто. Условное форматирование 3.3
В выпавшем окне, в разделе «выбор типа правил» выбираем «Использовать формулу для определения форматируемых ячеек».
Все очень просто. Условное форматирование 3.17
В разделе «измените описание правил» в строке формул ставим относительную или смешанную ссылку, как описано в предыдущем уроке (см. тут) или прописываем вручную, как показано в примере:
Все очень просто. Условное форматирование 3.18
Затем после относительной ссылки в формулу ставим знак «>» и цифру «10», чуть ниже нажимаем кнопку «формат», для выбора палитры отображения (в нашем случае «желтый цвет». Нажимаем «ok»
Все очень просто. Условное форматирование 3.19
Далее проделываем те же операции для значения «20» и выбираем естественно «зеленый» цвет И далее задаем правило для последнего значения, числа «40» и выбираем красный цвет. Вот и все
Все очень просто. Условное форматирование 3.2
Теперь, для того чтобы закрепить материал, попробуйте применить изученную функцию в своих наработках там, где по вашему эта «примочка» могла бы быть полезной.
Создать правило
Немного отвлечемся от изучения вариантов применения функций условного форматирования при нескольких условиях и заглянем во внутренние настройки данного сервиса. Вы уже заметили, при выборе пункта «создать правило» в выпавшем окне (в верхней части) «выберете тип правила», мы видим целый список предлагаемых правил.
Все очень просто. Условное форматирование 3.20
В нижней части «измените описание правила» мы можем выбрать стиль формата, значение (условие) и конечно палитру (цвет). Хочу сразу сказать, что содержание нижней части окна зависит от выбранного типа правил. Полистайте и по изучайте предлагаемые варианты. На изученных нами примерах или на каких-то своих наработках попробуйте применение стилей, поэкспериментируйте со значениями и вариантами отображения информации.
Условное форматирование даты
Ну а я тем временем, опишу еще одну замечательную функцию. Зачастую наши базы с данными содержат колонку «Дата». И как раз по дате нам необходимо к примеру найти какую то информацию. Для примера берем столбец с датами. Выделяем выбранный диапазон. в главном меню открываем функцию «условное форматирование» / «правило выделения ячеек» / «дата»
Все очень просто. Условное форматирование 3.4
В левой части выпавшего окна выбираем из предлагаемого списка условие, по которому необходимо выделить даты.
Все очень просто. Условное форматирование 3.5
К примеру (на текущей недели). Жмем «ok» и получаем вот такой результат
Все очень просто. Условное форматирование 3.21
Кстати мы можем задать второе условия для данной функции и выделить его другим цветом. Например, (на предыдущей недели) и цвет зеленый и далее задаем третье правило и выбираем цвет, отличный от выбранных.
Все очень просто. Условное форматирование 3.22
Условное форматирование строки по значению ячейки
Условием для данной функции УФ является выделение строки по определенному значению в ячейке. Для примера возьмем какую ни будь таблицу с данными (в нашем случае я буду использовать сведения по ремонтам оборудования)
Все очень просто. Условное форматирование 3.23
Определимся с условиями выделения информации, к примеру, нам необходимо что бы, сведения где еще ремонт не закончился (Р), отображались красным цветом. А там, где работы завершены (З) – зеленым И так зададим условие для завершенных ремонтов. Выделяем диапазон со всеми данными ремонтной таблицы. Заходим в меню «условного форматирования», выбираем «создать правило»
Все очень просто. Условное форматирование 3.26
В выпавшем меню выбираем тип правила (кликаем 1 раз ЛКМ по названию правила) – «использовать формулу для определения форматируемых ячеек» и в строке для формул забиваем логическую функцию, как показано на примере.
Все очень просто. Условное форматирование 3.24
Далее задаем формат отображения и жмем «ok» И обратите внимание, что ссылка на строку у нас в формуле – абсолютная, а ссылка на ячейку – смешанная. Затем по тому-же принципу задаем правило для не завершенных ремонтов и цвет выбираем «красный». И в «диспетчере правил условного форматирования» это выглядит так:
Все очень просто. Условное форматирование 3.27
А наша таблица будет иметь вот такой вид:
Все очень просто. Условное форматирование 3.25
Условное форматирование с использованием формул
Если до этого нам предлагался выбор каких-то стандартных правил условного форматирования, то возможности этой функции на мой взгляд, просто не ограничены. Ведь с помощью формул и макросов в Excel решаются любые проблемы, и мы обязательно на дальнейших занятиях будем изучать применение этих вкусняшек. Использование формул в условном форматировании дает возможность устанавливать правила для ячейки ссылаясь на другую ячейку, на другой лист или даже на другую книгу (при условии ее открытия). Составлять условия (задавать правила) с использованием логических, математических, инженерных и других формул. Одним словом, здесь столько хорошего, что я даже затрудняюсь решить, с какого примера начать обучение. Начнем как всегда с простого:
Простой пример
Используем имеющийся у нас ряд чисел. Наша условие — выделить из всего списка «нечетные» числа. Для этого выделяем выбранный диапазон, открываем меню «Условное форматирование» — создать правило и в верхней части выпавшего окна выбираем «использовать формулу для определения форматируемых ячеек», а в нижней части вставляем формулу как показано в примере и задаем формат выделения.
Все очень просто. Условное форматирование 3.8
Нажимаем «ok» и результат должен быть таким:
Все очень просто. Условное форматирование 3.6
Ну и в конце нашего сегодняшнего занятия разберем пример посложней, создадим информационное окно напротив имеющейся базы с данными.
Информационное окно
Использовать будем все тот же диапазон чисел. К примеру, колонка с итоговыми цифрами (в нашем случае диапазон A1:A15) имеет постоянно меняющиеся данные в которых мы должны контролировать определенные параметры. Зададим сразу предельные значения: до 30 – это «норма», свыше 30 необходимо взять под «внимание», ну а свыше 40 – это «перебор» или «сверх нормы», а теперь в ячейке B1 прописываем вот такую формулу: =ЕСЛИ(A1>40;»сверхнормы»;ЕСЛИ(A1>30;»внимание»;ЕСЛИ(A1 Все очень просто. Условное форматирование 3.9
" data-medium-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=300%2C169&ssl=1" data-large-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=1024%2C576&ssl=1" /> Нажимаем «Enter» и в окне B1 должны в нашем случае увидеть «норма»,
Все очень просто. Условное форматирование 3.10
затем протягиваем формулу до B15 и должны увидеть следующее:
Все очень просто. Условное форматирование 3.11
Затем для каждого из предельных значений мы должны создать правило условного форматирования, порядок таков: Выделяем диапазоне B1:B15, в меню «условного форматирования» выбираем «создать правило».
Все очень просто. Условное форматирование 3.12
Затем в выпавшем окне выбираем правило, создаем условие и задаем формат для значения «норма», как показано на скриншоте.
Все очень просто. Условное форматирование 3.14
Затем повторяем эту операцию для значения «внимание» и «сверх нормы» и в диспетчере задач мы должны получить вот такую картину:
Все очень просто. Условное форматирование 3.13
Вот и все. Уважаемые коллеги, не бойтесь экспериментировать и самое главное старайтесь сразу применять полученные знания в каких то своих наработках. Причем, выбирать условия и создавать для них правила вы так же можете по своему усмотрению. Мои примеры это, только указание пути к неограниченным возможностям Excel. Ну а на следующем занятии мы с Вами будем использовать формулы и конечно-же условное форматирование зависящее от баз данных хранящихся на других (скрытых) листах. Такая функция очень удобна, когда доступ посторонних к данным нежелателен или просто необходимо создать зависимость информации от базы данных хранящихся на другом листе. Одним словом, возможностей использования формул, макросов, ссылок и применение здесь же функций условного форматирования в Excel, дает нам просто неограниченные возможности. Нужно просто не боятся экспериментировать и у Вас Все получится.
С уважением 
Спасибо Вам, что поделились статьей в социальных сетях!
В этом уроке мы рассмотрим основы применения условного форматирования в Excel.
С его помощью мы можем выделять цветом значения таблиц по заданным критериям, искать дубликаты, а также графически “подсвечивать” важную информацию.
Основы условного форматирования в Excel
Используя условное форматирование, мы можем:
- закрашивать значения цветом
- менять шрифт
- задавать формат границ
Применять его возможно как на одну, так и на несколько ячеек, строк и столбцов. Производить настройку формата мы можем с помощью условий. Далее мы на практике разберем как это делать.
Где находится условное форматирование в Эксель?
Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:
Как сделать условное форматирование в Excel?
При применении условного форматирования системе необходимо задать две настройки:
- Каким ячейкам вы хотите задать формат;
- По каким условиям будет присвоен формат.
Ниже, мы рассмотрим как применить условное форматирование. Представим, что у нас есть таблица с динамикой курса доллара в рублях за год. Наша задача выделить красным цветом те данные, в которых курс снижался предыдущему месяцу. Итак, выполним следующие шаги:
- В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:
- Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
- Правила выделения
- Правила отбора первых и последних значений
- Гистограммы
- Цветовые шкалы
- Наборы значков
Также, доступны следующие условия:
- Значения больше или равны какому-либо значению;
- Выделять текст, содержащий определенные буквы или слова;
- Выделять цветом дубликаты;
- Выделять определенные даты.
- Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:
- Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:
- Во всплывающем окне формата укажите:
- цвет заливки
- цвет шрифта
- шрифт
- границы ячеек
- По завершении настроек нажмите кнопку “ОК”.
Ниже пример таблицы с применением условного форматирования по заданным нами параметрам. Данные с отрицательными значениями выделены красным цветом:
Как создать правило
Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:
- Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:
- Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:
- Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:
Условное форматирование по значению другой ячейки
На примерах выше мы задавали формат ячейкам, на основе их собственных значений. В Excel возможно задавать формат, на основе значений из других ячеек. Например, в таблице с данными курса доллара мы можем выделить цветом ячейки по правилу. Если курс доллара ниже чем в предыдущем месяце, то значение курса в текущем месяце будет выделено цветом.
Для создания условия по значению другой ячейки выполним следующие шаги:
- Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
- Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.
- Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:
- В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.
- Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:
На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:
Как применить несколько правил условного форматирования к одной ячейке
Возможно применять несколько правил к одной ячейке.
Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.
Для применения нескольких условий к одной ячейке выполним следующие действия:
- Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:
- Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:
Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.
Как редактировать правило условного форматирования
Для редактирования присвоенного правила выполните следующие шаги:
- Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
- Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:
- После внесения изменений нажмите кнопку “ОК”.
Как копировать правило условного форматирования
Для копирования формата на другие ячейки выполним следующие действия:
- Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
- Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:
Как удалить условное форматирование
Для удаления формата проделайте следующие действия:
- Выделите ячейки;
- Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:
