Когда работаем в Excel - создаем таблицы, проводим расчеты, часто для наглядности проводим заливку отдельных строк или ячеек как для себя, так и для других, чтоб полученные результаты были наглядными. Но эксель нам предоставляет хороший инструмент, чтоб всякий раз не раскрашивать наши ячейки, а установить правила, при выполнении которых ячейка меняет цвет (или цвет шрифта). В статье опишу на примере и тем самым проведу очередной бесплатный урок по Excel. А тема урока - УСЛОВНОЕ ФОРМАТИРОВАНИЕ.
Общее описание инструмента "условное форматирование"
Я лично на практике очень часто применяю автоматическое раскрашивание ячеек и диапазонов в своих отчетах. Делаю это как и для себя, так и для других, когда создаю таблицы для заполнения разными пользователями. Особенно когда ведешь регулярный отчет этот инструмент помогает выделять сразу значимые для моменты по результатам расчетов.
Для меня также это не просто автоматизация, а еще и красота и удобный дизайн отчета. Мне очень нравится, когда сухие цифры вдруг начинают сиять, а не просто смотреть на черно-белые данные.
Располагается данный инструмент на панели ГЛАВНАЯ:
При активации инструмента перед нами раскроется список возможностей его применения. Он достаточно обширный.
Я в данном примере не буду отдельно останавливаться на каждом пункте. Честно сказать рекомендую Вам самостоятельно все проверить как работает на ваших списках. Для этого просто выделяйте ваши данные и просто переходите от пункта к пункту и смотрите на результат. Что-то Вам обязательно понравится и пригодится на практике.
В этой же статье я расскажу как задать свое личное условие для изменений в отчетах. Будем использовать два метода и получать разные визуальные результаты.
Пример для закрашивания одной ячейки по условию
У нас есть таблица Excel, где пользователи вводят значения (да или нет) из выпадающего списка (о нем пишу я здесь).
Так вот нужно, чтоб по результатам ввода выделялись ячейки со значением "да" - закрашивались зеленым цветом, а ответы "нет" - синим.
Что делаем.
1. Можно сразу выделить диапазон ячеек или задать параметры потом. Я в примеру сделаю это сразу, потому что размер моего списка определен.
2. Активирую инструмент условного форматирования и ищу кнопку "создать правило".
Перед нами появится большое диалоговое окно, в котором можно подобрать свой необходимый тип правил. В нашем случае отметим тип - "форматировать только ячейки, которые содержат".
Внизу нужно настроить наше правило. Задаем условия:
Выбираем "значение ячейки" "равно" и в поле вручную вводим ответ "да".
Кстати, можно сделать еще по-другому:
Выбрала в 1 поле "текст" затем "содержит" - условие "да".
Далее переходим к настройкам цвета по кнопке "формат".
Перед нами раскроется диалоговое окно по форматированию ячеек.
Тут я настрою только заливку цветом.
По такому же принципу можно настроить следующее условие для слова нет. Или можно пойти другим способом. Выбрать - управление правилами:
У нас появится диспетчер правил.
Как видите, диапазон применения разный получается при разных способах настройки условий. Если выделение диапазона делаем изначально, то он автоматически будет распознан, если его нет, тогда надо его потом корректировать. Но в моем примере я решу все просто - скопирую его :).
Ну и в итоге любуемся результатом:
Что делать, если хочется раскрасить не одну ячейку, а строку списка?
Да, на самом деле иногда просто одной выделенной цветом ячейки для нас не достаточно. И нам все же хочется отметить цветом всю строку. Тут нужно немножечко глубже зайти в правила условного форматирования, но это не сложно.
Действуем так. Вызываем наше окно настройки правил любым удобным способом и выбираем последний тип правил "использовать формулу для определения форматируемых ячеек". Кстати, в основном все свои правила я строю через этот параметр. Тут можно установить любую формулу, по результатам которой будет ответ истина или ложь. Тогда при условии истина - форматирование будет меняться по заданным параметрам.
Но при его настройке следует четко следить за диапазонами и правилами установления относительных и абсолютных ссылок (о них можно почитать здесь).
Иногда после настройки правила, при установлении относительных ссылок, диапазоны смещаются. Надо возвращаться и править руками.
Вот в моем примере, когда я ввела формулу со ссылкой на ячейку Е4, автоматически она стала закрепленной $, что означает применение абсолютной ссылки. При использовании данного параметра в условном форматировании, значило бы, что при изменении только в одной ячейке Е4 менялось закрашивание ячеек. Чтоб это исправить, я снимаю закрепление по строке ($E4), оставляя закрепление по столбцу Е.
При настройке данного правила устанавливаю изменение не заливки ячейки, а цвета шрифта, чтоб было заметно как на листе работают сразу несколько установленных правил.
После установки обязательно проверяю корректность ссылок, формул, диапазонов.
После изменений диапазона и нажатия кнопки ОК в моем случае не пошло сразу все гладко. Правила работали не корректно.
Для выявления ошибки возвращаемся в диспетчер и анализируем:
Как видите, после нажатия ок при первичном вводе наша формула поменяла диапазон. В этом и скрыта ошибка. Надо править и обратно возращать формулу $Е4. Нажимаем кнопку "изменить правило" и корректируем.
Также обратите внимание, что диапазон применения условного форматирования и указан всей таблицы.
И в итоге у меня получилось все:
Вот так все устроено.
Итоги:
- Ввод правил условного форматирования может быть задан разными способами: либо по определенным типам, которые уже есть в инструменте, либо с помощью вводимой самостоятельно формулы.
- При установке правил с помощью своей формулы надо помнить:
- формула должна давать результат "Истина" или "Ложь";
- при вводе формул внимательно прописывать абсолютные и относительные ссылки - может быть неожиданный результат при ошибке;
- следить за поведением Экселя :) - не позволять изменять диапазоны, всегда отслеживать при первичном вводе правил. Корректировать при необходимости вручную.
И последнее: не бояться экспериментировать. Только на практике вы поймете, что для вас удобнее))
Если информация была полезной, не забудьте подписаться на канал. Всех обнимаю!