Добавление условного форматирования в Excel позволяет применять различные параметры форматирования к ячейке или диапазону ячеек, которые удовлетворяют заданным вами условиям. Установка таких условий может помочь организовать вашу электронную таблицу и упростить сканирование. Параметры форматирования, которые вы можете использовать, включают изменения шрифта и цвета фона, стили шрифта, границы ячеек и добавление форматирования чисел к данным.
В Excel есть встроенные параметры для часто используемых условий, таких как поиск чисел, которые больше или меньше определенного значения, или поиск чисел, которые выше или ниже среднего значения . В дополнение к этим предварительно заданным параметрам вы также можете создавать собственные правила условного форматирования с помощью формул Excel .
Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
Применение нескольких условий в Excel
Вы можете применить более одного правила к одним и тем же данным, чтобы проверить различные условия. Например, для бюджетных данных могут быть установлены условия, которые применяют изменения форматирования при достижении определенных уровней расходов, таких как 50%, 75% и 100% от общего бюджета.
При таких обстоятельствах Excel сначала определяет, конфликтуют ли различные правила, и, если это так, программа следует установленному порядку приоритета, чтобы определить, какое правило условного форматирования применить к данным.
Поиск данных, которые превышают 25% и 50% увеличивается
В следующем примере два пользовательских правила условного форматирования будут применяться к диапазону ячеек от B2 до B5 .
- Первое правило проверяет, не превышают ли данные в ячейках A2: A5 соответствующее значение в B2: B5 более чем на 25%.
- Второе правило проверяет, превышает ли те же данные в A2: A5 соответствующее значение в B2: B5 более чем на 50%.
Как видно на изображении выше, если выполняется любое из вышеприведенных условий, цвет фона ячейки или ячеек в диапазоне B1: B4 изменится.
- Для данных, где разница превышает 25%, цвет фона ячейки изменится на зеленый.
- Если разница превышает 50%, цвет фона ячейки изменится на красный.
Правила, использованные для выполнения этой задачи, будут введены в диалоговом окне « Новое правило форматирования » . Начните с ввода данных образца в ячейки с A1 по C5, как показано на рисунке выше.
В заключительной части урока мы добавим формулы к ячейкам C2: C4, которые показывают точную процентную разницу между значениями в ячейках A2: A5 и B2: B5 ; это позволит нам проверить точность правил условного форматирования.
Установка правил условного форматирования
Сначала мы применим условное форматирование, чтобы найти 25-процентное или более значительное увеличение.
Функция будет выглядеть так:
= (А2-В2) / А2> 25%
-
Выделите ячейки с B2 по B5 на листе.
-
Нажмите на вкладке Главная в ленте .
-
Нажмите на значок условного форматирования на ленте, чтобы открыть раскрывающийся список.
-
Выберите « Новое правило», чтобы открыть диалоговое окно « Новое правило форматирования ».
-
В разделе « Выберите тип правила» выберите последний параметр: используйте формулу, чтобы определить ячейки для форматирования.
-
Введите формулу, указанную выше, в поле под значениями формата, где эта формула верна
-
Нажмите кнопку « Формат» , чтобы открыть диалоговое окно. Нажмите вкладку Fill и выберите цвет.
-
Нажмите кнопку ОК, чтобы закрыть диалоговые окна и вернуться к рабочему листу.
-
Цвет фона ячеек B3 и B5 должен измениться на цвет, который вы выбрали.
Теперь мы применим условное форматирование, чтобы найти увеличение на 50 или более процентов. Формула будет выглядеть так:
-
Повторите первые пять шагов выше.
-
Введите приведенную выше формулу в поле ниже Значения формата, где эта формула истинна:
-
Нажмите кнопку « Формат» , чтобы открыть диалоговое окно. Перейдите на вкладку «Заливка» и выберите цвет, отличный от предыдущего.
-
Нажмите кнопку ОК, чтобы закрыть диалоговые окна и вернуться к рабочему листу .
Цвет фона ячейки B3 должен оставаться неизменным, указывая на то, что разница в процентах между числами в ячейках A3 и B3 больше 25 процентов, но меньше или равна 50 процентам. Цвет фона ячейки B5 должен измениться на новый выбранный вами цвет, указывая на то, что разница в процентах между числами в ячейках A5 и B5 превышает 50 процентов.
Проверка правил условного форматирования
Чтобы убедиться в правильности введенных правил условного форматирования, мы можем ввести формулы в ячейки C2: C5, которые будут вычислять точную процентную разницу между числами в диапазонах A2: A5 и B2: B5 .
Формула в ячейке C2 выглядит следующим образом:
= (А2-В2) / А2
-
Нажмите на ячейку C2, чтобы сделать ее активной.
-
Введите приведенную выше формулу и нажмите клавишу Enter на клавиатуре.
-
Ответ 10% должен появиться в ячейке C2 , указывая, что число в ячейке A2 на 10% больше, чем число в ячейке B2 .
-
Возможно, потребуется изменить форматирование в ячейке C2, чтобы отобразить ответ в процентах.
-
Используйте маркер заполнения, чтобы скопировать формулу из ячейки C2 в ячейки C3 до C5 .
-
Ответы на клетки С3 до С5 должны составлять 30%, 25% и 60%.
Ответы в этих ячейках показывают, что правила условного форматирования точны, поскольку разница между ячейками A3 и B3 превышает 25 процентов, а разница между ячейками A5 и B5 превышает 50 процентов.
Ячейка B4 не изменила цвет, потому что разница между ячейками A4 и B4 равна 25 процентам, и наше правило условного форматирования указывало, что для изменения цвета фона требовался процент, превышающий 25 процентов.
Порядок приоритета для условного форматирования
Когда вы применяете несколько правил к одному и тому же диапазону данных, Excel сначала определяет, конфликтуют ли правила. Конфликтующие правила — это те, в которых параметры форматирования нельзя применять к одним и тем же данным .
В нашем примере правила конфликтуют, поскольку оба используют один и тот же параметр форматирования — изменение цвета ячейки фона.
В ситуации, когда второе правило истинно (разница в значении составляет более 50 процентов между двумя ячейками), тогда первое правило (разница в значении превышает 25 процентов) также верно.
Поскольку ячейка не может одновременно иметь два разных цветовых фона, Excel необходимо знать, какое правило условного форматирования следует применять.
В порядке приоритета Excel указано, что правило, которое находится выше в списке в диалоговом окне «Диспетчер правил условного форматирования», применяется в первую очередь.
Как показано на рисунке выше, второе правило, используемое в этом руководстве, находится выше в списке и, следовательно, имеет приоритет над первым правилом. В результате цвет фона ячейки B5 становится зеленым.
По умолчанию новые правила идут в начало списка; чтобы изменить порядок, используйте кнопки со стрелками вверх и вниз в диалоговом окне.
Применение не конфликтующих правил
Если два или более правил условного форматирования не конфликтуют, оба применяются, когда условие, которое проверяет каждое правило, становится истинным.
Если первое правило условного форматирования в нашем примере форматирует диапазон ячеек B2: B5 с оранжевой рамкой вместо оранжевого цвета фона, два правила условного форматирования не будут конфликтовать, поскольку оба формата могут применяться без вмешательства друг в друга.
Условное форматирование против обычного форматирования
В случае противоречий между правилами условного форматирования и параметрами форматирования, применяемыми вручную, правило условного форматирования всегда имеет приоритет и будет применяться вместо любых добавленных вручную параметров форматирования.