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

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

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

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

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

01 из 04

Применение нескольких правил в Excel

Для одних и тех же данных можно применять более одного правила для проверки различных условий. Например, в бюджетных данных могут быть установлены условия, которые применяют форматирование изменений, когда расходуются определенные уровни, такие как 50%, 75% и 100% от общего бюджета.

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

Поиск данных, превышающих 25% и 50%

В следующем примере два специальных правила условного форматирования будут применяться к диапазону ячейки B2 в B5.

• Первое правило проверяет, были ли данные в ячейки A2 в A5 больше соответствующего значения в Би 2 в B5 более чем на 25%.
• Второе правило проверяет, не совпадают ли те же данные в A2: A5 превышает соответствующее значение в B2: B5 более чем на 50%.

Как видно на изображении выше, если выполнено одно из указанных выше условий, цвет фона ячейки или клеток в диапазоне B1: B4 изменится.

• Для данных, где разница превышает 25%, цвет фона ячейки изменится на зеленый.
• Если разница превышает 50%, цвет фона ячейки изменится на красный.

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

в третий шаг учебника добавят формулы к ячейкам С2: С4 которые показывают точную процентную разницу между значениями в ячейках A2: A5 а также B2: B5 чтобы проверить точность условных правил форматирования.

02 из 04

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

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

Условное форматирование для поиска более 25 процентов увеличения

= (A2-B2) / A2> 25%

1. основной момент ячейки B2 в B5 на листе.
2. Нажми на Главная вкладка из лента.
3. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
4. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
5. От Стиль: , выберите классический вариант.
6. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
7. Введите формула приведенные выше в предоставленное пространство.
8. Нажми на Формат с: выпадающий список, затем выберите Светло-красный наполнить темно-красным текстом вариант.
9. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.
10. На этом этапе цвет фона ячейки B3 а также B5 должен быть зеленым.

Условное форматирование для поиска более 50 процентов увеличения

= (A2-B2) / A2> 50%

основной момент ячейки B2 в B5 на листе.

1. Нажми на Главная вкладка из лента.
2. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
3. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
4. От Стиль: , выберите классический вариант.
5. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
6. Введите формула приведенные выше в предоставленное пространство.
7. Нажми на Формат с: выпадающий список, затем выберите Светло-зеленый Заполнить темным зеленым текстом вариант.
8. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.

Цвет фона ячейка B3 должен все же быть зеленым, указывая, что процентная разница между числами в ячейки A3 а также B3 составляет более 25 процентов, но меньше или равно 50 процентам. Цвет фона ячейка B5 следует изменить на красный, указав, что процентная разница между числами в ячейки A5 а также B5 составляет более 50 процентов.

03 из 04

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

Чтобы проверить правильность введенных правил форматирования, мы можем вводить формулы в ячейки С2: С5 который рассчитает точную процентную разницу между числами в диапазонах A2: A5 а также B2: B5.

= (A2-B2) / A2

1. Нажмите на ячейка C2 чтобы сделать его активной ячейкой.
2. Введите приведенную выше формулу и нажмите Войти на клавиатуре.
3. Ответ 10% должен появиться в ячейка C2, указывая, что число в ячейка A2 на 10% больше, чем число в ячейка B2.
4. Возможно, потребуется изменить форматирование на ячейка C2 чтобы отобразить ответ в процентах.
5. Использовать ручка заполнения скопировать формулу из ячейка C2 в ячейки C3 в C5.
6. Ответы для ячейки C3 в C5 должен составлять 30%, 25% и 60%.

Ответы в этих ячейках показывают, что созданные условные правила форматирования верны, поскольку разница между ячейки A3 а также B3 превышает 25 процентов, а разница между ячейки A5 а также B5 составляет более 50 процентов.

Cell B4 не изменил цвет, поскольку разница между ячейки A4 а также B4 равен 25 процентам, и в нашем правиле условного форматирования указано, что для цвета фона для зеленого цвета требуется процент больше 25%.

04 из 04

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

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

В примере, используемом в этом руководстве, конфликты правил, поскольку оба правила используют один и тот же параметр форматирования - изменение цвета фона.

В ситуации, когда второе правило истинно (разница в значении больше 50 процентов между двумя ячейками), то верно и первое правило (разница в значении больше 25 процентов).

Приказ Excel о приоритете

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

Применяемое правило определяется порядком приоритета Excel, в котором указано, что правило, которое выше в списке в диалоговом окне «Диспетчер правил условного форматирования» имеет приоритет.

Как показано на рисунке выше, второе правило, используемое в этом руководстве, выше в списке и, следовательно, имеет приоритет над первым правилом. В результате цвет фона ячейки B5 изменяется на красный.

По умолчанию новые правила добавляются в верхнюю часть списка и, следовательно, имеют более высокий приоритет. Чтобы изменить порядок приоритета, используйте Вверх и вниз стрелки в диалоговом окне, как указано на изображении выше.

Применение неконфликтных правил

Если два или более условных правила форматирования не конфликтуют, оба применяются, когда условие, которое каждое правило тестирует, становится истинным.

Если первое правило условного форматирования в нашем примере отформатировало диапазон ячеек B2: B5 с зеленой рамкой вместо зеленого цвета фона, два условных правила форматирования не будут конфликтовать, так как оба формата могут применяться без вмешательства в другое.

В следствии, ячейка B5 будет иметь как зеленую границу, так и красный цвет фона, поскольку разница между числами в ячейки A5 а также B5 больше, чем 25 и 50 процентов.

Условное форматирование и регулярное форматирование

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

Если желтый цвет фона был первоначально применен к ячейки B2 в B5 в примере, как только были добавлены условные правила форматирования, только ячейки B2 а также B4 будет оставаться желтым. Поскольку введенные правила условного форматирования применяются к ячейкам B3 а также B5, их цвета фона будут меняться от желтого до зеленого и красного соответственно.