7 формул условного форматирования в excel

Самое приятное в условном форматировании — это то, что в нем можно использовать формулы. И работать с формулами очень просто.

Ваша формула должна быть логической формулой, а результат должен быть ИСТИНА или ЛОЖЬ. Если формула возвращает TRUE, вы получите форматирование, а если FALSE, то ничего. Дело в том, что с помощью формул можно получить максимальную отдачу от условного форматирования.

Да, это правда. В приведенном ниже примере мы использовали формулу в CF, чтобы проверить, меньше ли значение в ячейке 1000 или нет.

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

Действия по применению условного форматирования с формулами

Шаги по применению CF с помощью формул довольно просты:

1. Выберите диапазон, к которому нужно применить условное форматирование.
2. Добавьте формулу в текст условия.
3. Укажите формат, который будет применяться при выполнении условия.

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

• Сначала выберите диапазон, к которому вы хотите применить условное форматирование.

• После этого перейдите на вкладку «Главная» ➜ «Стили» ➜ «Условное форматирование» ➜ «Новое правило» ➜ Используйте формулу, чтобы определить, какую ячейку форматировать.

• Теперь в поле « Формат значений, где формула верна » введите формулу ниже.

=E5<1000

• Следующее — указать формат, который нужно применить, для этого нажмите кнопку «Формат» и выберите формат.

• В конце нажмите ОК.

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

1. Используйте формулу на основе другой ячейки

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

Когда проход меньше 75%, он будет выделен красным.

2. Условное форматирование с помощью IF

Всякий раз, когда я думаю об условиях, первое, что приходит на ум, — это использование функции ЕСЛИ. И самое приятное в этой функции то, что она идеально вписывается в условное форматирование. Позвольте мне показать вам пример:

Здесь мы использовали ЕСЛИ для создания условия, и условие заключается в том, что когда количество «Выполнено» в диапазоне B3:B9 равно количеству задач в диапазоне A3:A9, тогда появится окончательный статус.

3. Условное форматирование с несколькими условиями

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

А мы использовали простое сочетание СИ-И, чтобы выделить месяцы, когда температура достаточно приятная. Месяцы, когда температура колеблется от 15 до 35 градусов по Цельсию, будут красочными.

Аналогичным образом вы также можете использовать if с функцией или .

4. Выделите альтернативные строки с помощью условного форматирования.

Чтобы выделить каждую альтернативную строку, вы можете использовать следующую формулу nCF.

=INT(MOD(СТРОКА(),2))

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

=INT(MOD(СТРОКА()+1,2))

Формулу того же типа можно использовать и для столбцов (нечетных и четных).

=INT(MOD(СТОЛБЕЦ(),2))

И для четных столбцов.

=INT(MOD(СТОЛБЕЦ()+1,2))

5. Выделите ячейки с ошибками с помощью CF.

Теперь перейдем к другому примеру, где мы проверим, содержит ли ячейка ошибку или нет. Нам нужно просто вставить формулу в условное форматирование, которая может проверить условие и вернуть результат как ИСТИНА или ЛОЖЬ. Вы даже можете проверять ячейки на наличие чисел, текста или некоторых конкретных значений.

6. Создайте контрольный список с условным форматированием

Теперь давайте добавим к интеллекту немного творчества. Вы уже научились использовать формулу на основе другой ячейки. Здесь мы связали флажок с ячейкой B1, а затем связали B1 с формулой, используемой в условном форматировании для ячейки A1.

Теперь, если вы установите флажок , значение ячейки B1 станет TRUE и ячейка A1 получит свое условное форматирование [ зачеркивание ].

Вынос

1. Ваша формула должна быть логической формулой, которая приводит к ИСТИНЕ или ЛОЖНОМУ результату.
2. Старайтесь не перегружать данные условным форматированием.
3. Всегда используйте относительные и абсолютные ссылки в буквальном смысле.

Пример файла

Загрузите этот образец файла здесь , чтобы узнать больше.