В этой заметке я расскажу об использовании формул в условном форматировании и о том, как основываясь на значении ячейки форматировать другие ячейки или даже целые диапазоны. Например, как по значению в ячейке выделить всю строку, в которой ячейка находится.
В первую очередь разберемся с формулами при создании правил условного форматирования.
В предыдущих заметках (1 часть, 2 часть, 3 часть) я рассказал о стандартных шаблонах, которые доступны в Excel, однако использование формул предоставляют намного более широкие возможности.
Условное форматирование с помощью формулы
Рассмотрим следующий пример.
Есть зарплатная ведомость и нужно выделить все оклады свыше 35000 рублей. В одной из предыдущих заметок мы уже решали подобную задачу и сумма в 35000 задавалась непосредственно в правиле, что очень неудобно, ведь может потребоваться постоянно изменять значения для анализа таблицы. В такой ситуации придется каждый раз корректировать правило.
Именно поэтому интересующее нас значение вынесем в отдельное поле.
Затем выделим диапазон с окладами (1) и создадим собственное правило условного форматирования (3). При задании условия (4) мышью укажем нужные ячейки в таблице (5). Затем зададим само форматирование (6), например, изменим фон на красный.
Такая формула работать не будет и, скорее всего, вы уже догадываетесь почему. По умолчанию в формулу подставляются абсолютные ссылки на ячейки и поэтому в итоге мы не увидим никакого форматирования, так как самая первая ячейка диапазона (С6) не удовлетворяет заданному условию, а из-за абсолютных ссылок в нем условие не будет изменяться для последующих ячеек.
Абсолютным и относительным ссылкам я посвятил отдельное очень подробное видео, поэтому если вы еще с ними незнакомы или не очень понимаете их суть, то обязательно сначала изучите его. Без понимания абсолютной и относительной адресации в Excel невозможно использовать условное форматирование.
Ну а чтобы было более понятно дополним таблицу вспомогательным столбцом. Условное форматирование производится, так сказать, в фоновом режиме, поэтому, вынеся на свет его подноготную, мы сможем понять, что же произошло.
Итак, скопируем формулу из правила условного форматирования и вставим ее в столбец рядом. Раскопируем формулу по диапазону.
Мы видим, что все значения одинаковы и являются ложью. Также пробежав по столбцу со значениями в строке формул увидим неизменную формулу с абсолютными ссылками. То есть весь столбец зависит от значения его первой ячейки (С6), в чем легко убедиться, изменив ее значение на удовлетворяющее условию.
Теперь весь столбец стал красным, хотя далеко не все значения удовлетворяют условию.
Давайте приведем формулу во вспомогательном столбце к должному виду. Абсолютной должна остаться только ссылка на ячейку из поля с условием, чтобы она не изменялась при копировании формулы по диапазону. Ссылка на ячейку столбца с данными должна остаться относительной. Размножим формулу и увидим верный результат.
Интересующие нас значения будут ИСТИНОЙ, а значит в случае с условным форматированием такая ячейка ему подвергнется.
Осталось лишь скопировать формулу и изменить правило условного форматирования (1). Сразу можно изменить и само условное форматирование, поменяв фон на менее едкий, а также сделав шрифт полужирным (2).
Все работает!
Наша задача была довольно простой, но при задании условий формулы могут быть довольно громоздкими - содержать множество вложений и дополнительных функций. По этой причине я бы рекомендовал всегда предварительно создавать вспомогательный столбец и тестировать работу формулы на нем.
Когда все отлажено и работает верно можно просто скопировать формулу во вновь созданное правило условного форматирования, а сам вспомогательный столбец удалить.
Усложним немного задачу.
Как задать условное форматирование для всей строки
Пока условным форматированием мы выделяли только ячейку со значением. Давайте выделим всю строку, в которой находится оклад, удовлетворяющий нашему условию. Фактически нам нужно будет отформатировать три ячейки строки.
Для лучшего понимания происходящего скопируем заголовки исходной таблицы рядом и создадим формулу. Нужно, чтобы значение оклада из ячейки строки было больше заданного в соответствующем поле. Это значение изменяться не должно, поэтому фиксируем его с помощью клавиши F4.
Но что нужно сделать с первой ссылкой? Если оставить все как есть и размножить формулу на такой же по размеру диапазон, как и у исходной таблицы, то получим вот такой результат.
Формула не работает, так как в одной строке значения должны быть одинаковыми, но это не так.
Проанализировав формулу увидим, что относительная ссылка на ячейку не работает, так как ссылка «сползает» в соседнюю ячейку. То есть нам нужно сделать ссылку на ячейку с окладом смешанной - запретить перемещение по столбцам и оставить только возможность смещения по строкам.
Теперь все работает так, как должно.
Скопируем формулу, выделим весь диапазон значений исходной таблицы и создадим правило условного форматирования. Вставим формулу и зададим необходимый формат.
Условное форматирование выделяет всю строку таблицы целиком и можно удалить вспомогательную таблицу.
Более подробно о формулах в условном форматировании смотрите в видео:
________________________________________
Ссылки на мои ресурсы по Excel
★ Серия видеокурсов "Microsoft Excel Шаг за Шагом"