Использование формул в условном форматировании.
Встроенные правила условного форматирования покрывают большинство сценариев, с которыми можно столкнуться в работе. Но иногда этих правил недостаточно. В таком случае можно задать условие формулой.
При задании условного форматирования с помощью формул важно помнить:
1.Формула должна возвращать значения ИСТИНА, либо ЛОЖЬ.
Как и в других правилах, форматирование к ячейкам будет применяться, только если выполняется определённое условие. А выполнение условия определяется значениями ИСТИНА и ЛОЖЬ.
2.Формула пишется для левой верхней ячейки диапазона.
Excel считает, что формула написана для первой ячейки выделенного диапазона. Затем формула копируется на остальные ячейки.
При копировании формулы условного форматирования на остальные ячейки диапазона она будет вести себя также, как и обычная формула, скопированная в соседние ячейки. Поэтому важно помнить об использовании правильного типа ссылок – абсолютных и относительных.
Сложные формулы рекомендую сначала проверять на листе, а уже затем вставлять в настройки правил условного форматирования. Это сэкономит вам много нервов при проверке корректности работы формулы.
При задании условного форматирования с помощью формул важно помнить:
1.Формула должна возвращать значения ИСТИНА, либо ЛОЖЬ.
Как и в других правилах, форматирование к ячейкам будет применяться, только если выполняется определённое условие. А выполнение условия определяется значениями ИСТИНА и ЛОЖЬ.
2.Формула пишется для левой верхней ячейки диапазона.
Excel считает, что формула написана для первой ячейки выделенного диапазона. Затем формула копируется на остальные ячейки.
При копировании формулы условного форматирования на остальные ячейки диапазона она будет вести себя также, как и обычная формула, скопированная в соседние ячейки. Поэтому важно помнить об использовании правильного типа ссылок – абсолютных и относительных.
Сложные формулы рекомендую сначала проверять на листе, а уже затем вставлять в настройки правил условного форматирования. Это сэкономит вам много нервов при проверке корректности работы формулы.
Применение.
Выделение чисел больше заданного.
Выделение чисел больше заданного.
Рассмотрим простой пример с применением формул в условном форматировании. Предположим, есть диапазон с числами B2:D9. Нужно выделить только числа, которые больше 80.
Выделяем диапазон B2:D9:
Выделяем диапазон B2:D9:
Открываем окно Создание правила форматирования – выбираем Использовать формулу для определения форматируемых ячеек.
В окне нужно указать формулу, которая будет возвращать значение ИСТИНА для ячеек, в которых содержится число больше 80.
При этом помним, что формула применяется к левой верхней ячейке диапазона – в нашем случае B2.
Так и запишем: =B2>80.
При этом помним, что формула применяется к левой верхней ячейке диапазона – в нашем случае B2.
Так и запишем: =B2>80.
После этого зададим формат – точно также, как и с другими типами правил форматирования. Например, зададим другой цвет фона ячеек.
Теперь можно применить форматирование – жмём ОК и ячейки с числами больше 80 будут выделены цветом.
Теперь давайте посмотрим, что было бы, если бы мы применили формулу не для первой ячейки диапазона, а, например, для А1.
Если применить такое правило к диапазону B2:D9, форматирование «съедет» – цветом будут выделяться ячейки на 1 правее и на 1 ниже.
Относительные ссылки в формуле сместили условие форматирования.
Выделение ячеек, которые больше заданного числа – слишком простой пример, этого можно добиться и без использования формул в правилах форматирования. Давайте посмотрим, какие условия могут быть более полезными.
Выделение ячеек, которые больше заданного числа – слишком простой пример, этого можно добиться и без использования формул в правилах форматирования. Давайте посмотрим, какие условия могут быть более полезными.
Применение.
Разделение дней в общей таблице.
Разделение дней в общей таблице.
Предположим, есть таблица с историей продаж.
Данные сливаются, трудно сразу найти продажи за один день. Было бы славно добавить границы между разными днями. Сделать это можно с помощью формулы, которая будет проверять, совпадает ли день в текущей строке с днём в следующей.
Сначала выделим диапазон B3:D13 (строку заголовков выделять не нужно). При создании правила форматирования введём формулу =$B3<>$B4. Закрепляем столбец, потому что проверять нужно только дни.
Сначала выделим диапазон B3:D13 (строку заголовков выделять не нужно). При создании правила форматирования введём формулу =$B3<>$B4. Закрепляем столбец, потому что проверять нужно только дни.
Затем в окне Формат добавим нижнюю границу.
Правило форматирования будет выглядеть так:
Применяем правило – будут добавлены границы между строками с разными днями.
Остальные примеры рассмотрим в канале:
