Добавление условного форматирования в Excel позволяет применять различные параметры форматирования к ячейке или диапазону ячеек, которые соответствуют заданным вами условиям. Установка таких условий может помочь организовать электронную таблицу и упростить ее сканирование. Параметры форматирования, которые вы можете использовать, включают изменение цвета шрифта и фона, стили шрифта, границы ячеек и добавление числового форматирования к данным.
Excel имеет встроенные параметры для часто используемых условий, таких как поиск чисел, которые больше или меньше определенного значения, или поиск чисел, которые выше или ниже среднего значения. В дополнение к этим предустановленным параметрам вы также можете создавать собственные правила условного форматирования с помощью формул Excel.
Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Microsoft 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 или более процентов.
Функция будет выглядеть так:
=(A2-B2)/A2>25%
- Выделите ячейки с B2 по B5 на рабочем листе.
- Нажмите на вкладку Главная на ленте.
- Нажмите значок Условное форматирование на ленте , чтобы открыть раскрывающийся список.
-
Выберите Новое правило, чтобы открыть диалоговое окно Новое правило форматирования.
- В разделе Выбор типа правила щелкните последний вариант: Использовать формулу для определения форматируемых ячеек.
- Введите формулу, отмеченную выше, в пустое место под Отформатируйте значения, где эта формула верна:
- Нажмите кнопку Формат, чтобы открыть диалоговое окно. Нажмите на вкладку Заливка и выберите цвет.
- Нажмите OK, чтобы закрыть диалоговые окна и вернуться к рабочему листу.
- Цвет фона ячеек B3 и B5 должен измениться на выбранный вами цвет.
Теперь мы применим условное форматирование, чтобы найти увеличение на 50 или более процентов. Формула будет выглядеть так:
- Повторите первые пять шагов выше.
- Введите формулу, приведенную выше, в поле ниже Отформатируйте значения, где эта формула верна:
- Нажмите кнопку Формат, чтобы открыть диалоговое окно. Перейдите на вкладку Заливка и выберите цвет, отличный от того, что вы делали в предыдущем наборе шагов.
- Нажмите OK, чтобы закрыть диалоговые окна и вернуться к рабочему листу.
Цвет фона ячейки B3 должен оставаться прежним, указывая на процентную разницу между числами в ячейках A3 иB3 больше 25 процентов, но меньше или равно 50 процентам. Цвет фона ячейки B5 должен измениться на новый выбранный вами цвет, указывающий на процентную разницу между числами в ячейках A5 и B5 больше 50 процентов.
Проверка правил условного форматирования
Чтобы проверить правильность введенных правил условного форматирования, мы можем ввести формулы в ячейки C2:C5, которые будут вычислять точную процентную разницу между числами в диапазонахA2:A5 и B2:B5.
Формула в ячейке C2 выглядит так:
=(A2-B2)/A2
- Нажмите на ячейку C2, чтобы сделать ее активной ячейкой.
- Введите приведенную выше формулу и нажмите клавишу Enter на клавиатуре.
- Ответ 10% должен появиться в ячейке C2, указывая на то, что число в ячейке A2 на 10% больше, чем число в ячейка B2.
- Возможно, потребуется изменить форматирование ячейки C2, чтобы ответ отображался в процентах.
- Используйте дескриптор заполнения, чтобы скопировать формулу из ячейки C2 в ячейки C3 в C5.
- Ответы для ячеек C3 по C5 должны быть 30%, 25% и 60%.
Ответы в этих ячейках показывают, что правила условного форматирования точны, поскольку разница между ячейками A3 и B3 больше 25 процентов, а разница между ячейками A5 и B5 больше 50 процентов.
Ячейка B4 не изменила цвет, поскольку разница между ячейками A4 и B4 равна 25 процентов, а наше правило условного форматирования указывало, что для изменения цвета фона требуется процентное значение больше 25 процентов.
Порядок приоритета условного форматирования
При применении нескольких правил к одному и тому же диапазону данных Excel сначала определяет, не конфликтуют ли правила. Конфликтующие правила - это те, в которых параметры форматирования не могут быть применены к одним и тем же данным.
В нашем примере правила конфликтуют, поскольку оба используют один и тот же параметр форматирования - изменение цвета фона ячейки.
В ситуации, когда верно второе правило (разница в значениях между двумя ячейками более 50 процентов), то верно и первое правило (разница в значениях более 25 процентов).
Поскольку ячейка не может иметь одновременно два разных цвета фона, Excel необходимо знать, какое правило условного форматирования следует применять.
Порядок приоритета Excel гласит, что правило, которое находится выше в списке в диалоговом окне «Диспетчер правил условного форматирования», применяется первым.
Как показано на изображении выше, второе правило, используемое в этом руководстве, находится выше в списке и, следовательно, имеет приоритет над первым правилом. В результате цвет фона ячейки B5 - зеленый.
По умолчанию новые правила располагаются вверху списка; чтобы изменить порядок, используйте кнопки со стрелками Вверх и Вниз в диалоговом окне.
Применение неконфликтующих правил
Если два или более правила условного форматирования не конфликтуют, применяются оба, когда условие, которое проверяет каждое правило, становится истинным.
Если первое правило условного форматирования в нашем примере форматирует диапазон ячеек B2:B5 с оранжевой рамкой вместо оранжевого цвета фона, два правила условного форматирования не будут конфликт, так как оба формата могут применяться, не мешая друг другу.
Условное форматирование против обычного форматирования
В случае конфликта между правилами условного форматирования и параметрами форматирования, примененными вручную, правило условного форматирования всегда имеет приоритет и будет применяться вместо любых параметров форматирования, добавленных вручную.
