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

Описанное ранее условное форматирование с помощью вводимых вручную форматов позволяет установить цвет текста в клетке в зависимости от некоторых простых условий, относящихся только к форматируемой клетке.

В Excel имеется средство условного форматирования, дающие возможность управлять цветом текста и заливки, видом рамки, шрифта, подчеркиванием. Само условие может быть сложным и зависеть от значения не только текущей, но и других ячеек. Начальное окно Мастера форматирования (доступно через меню Формат+Ус­ловное форматирование) приведено на рис.3.4-1. Условий может быть установлено до трех. Окно формирования каждого следующего условия может быть вызвано при помощи, имеющейся здесь кнопки А также>>. Само условие может быть двух видов. Первый (показан на рисунке) позволяет задать значения, относительно которого проверяется форматируемая клетка. Второй позволяет указать логическую формулу любой сложности (слово значение заменяется на слово формула).

Пусть надо выделить цветом фона содержимое F8 в зависимости от диапазона, в котором оно находится, если оно:

от 10 до 20 – зеленым, если от 21 до 100 – красным, и если больше 100 – желтым.

Нам понадобятся три условия следующего вида (здесь и далее интерфейс Мастера показан схематически):

Условие 1

значение между 10 и 20 – установить зеленый фон

Условие 2

значение между 21 и 100 – красный фон

Условие 3

значение больше 100 – желтый фон

 

 

Другой пример. Положим, требуется выделить дату (например, содержащуюся в D12), соответствующую выходному дню. Ниже показаны функции, которые можно включить в условия в зависимости оттого, что именно вы хотите выявить (субботу, воскресенье, и субботу и воскресенье).

=ДЕНЬНЕД(D12;2)=7 – только воскресенье,

=ДЕНЬНЕД(D12;2)=6 – только суббота,

=ИЛИ(ДЕНЬНЕД(D12;2)=7;ДЕНЬНЕД(D12;2)=6) – оба выходных, или проще =ДЕНЬНЕД(D12;2)>=6.

Таким образом, в окне Мастера нужно внести, например, выражение

Условие 1

формула =ДЕНЬНЕД(D12;2)>=6

 

Еще пример. Пусть нам известны сроки (начало/конец) работы сотрудников на некотором (например, на вредном) участке производства в мае месяце и число часов, отработанных ими по суткам. Удобно такие даты выделить в таблице рамкой и/или заливкой (фрагмент таблицы приведен на рис. 3.4-2), что позволит оператору быстро ввести число отработанных часов в нужные клетки и найти их сумму. Для этой цели установим в D3 условный формат:

	A	B	C	D	E	F	G	H	I	J
	Рабочий график
	Ф.И.О.	Начало	Конец	01.май	02.май	03.май	04.май	05.май	06.май	07.май
	Петр	02.май	05.май
	Иван	04.май	12.май
	Олег	01.май	04.май
	Всего
			Рис. 3.4-2

Условие 1

формула =И(D$2>=$B3;D$2<=$C3),

распространив его на остальные клетки таблицы. Здесь выявляется факт того, что заголовок столбца, содержащий текущую дату (D2) находится в диапазоне от начальной (B3) до конечной (C3) дат, интересующего диапазона.

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

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

Контроль ввода

При работе с данными во часто критически важным является правильность их ввода. Например, далеко не все равно, введете ли вы название организации в форме “АО Туризм” или “Туризм АО” или просто “Туризм”. Если предполагается последующая сортировка, группировка или другое обобщение данных, эти слова система сочтет именами разных организаций и подведет по ним три разных итога. В Excel имеются средства контроля ввода данных, которые мы можем закрепить за определенными ячейками таблицы. Они доступны через меню Данные+Проверка, открывающее окно Проверка вводимых значений, состоящее из трех вкладок (рис. 3.5-1).

Вкладка Параметры (на переднем плане) определяет условия, проверяемые системой при вводе. Во вкладке Сообщение для ввода формируется подсказка при входе в контролируемую клетку. Во вкладке Сообщение об ошибке формируется сообщение, предъявляемое пользователю, если он ввел неправильное значение.

Важнейшей является вкладка Параметры. Здесь нужно задать тип (Тип данных) контролируемой величины (рис. 3.5-2). Для большинства типов данных мы можем задать граничные значения, выбрав одно из отношений (параметр Значение на рис. 3.5-3).

Рис. 3.5-1

 

Положим в некоторую ячейку нужно ввести дату рождения работника предприятия. Ясно, что он не может быть моложе 16 лет и старше 80-ти. Вкладка Параметры, заполненная для данного случая, как раз и изображена на рис. 3.5-1. Флаг Распространить изменения на другие ячейки с тем же условием устанавливает режим, когда изменение условий для одной клетки вызывает идентичные изменения в других с теми же условиями.

Установка флага Игнорировать пустые ячейки подавляет вывод сообщения об ошибке при вводе пустого значения в контролируемую ячейку рабочего листа (оно не будет проверяться).

Пусть также, мы хотим, чтобы при входе в клетку предъявлялась подсказка, изображенная на рис. 3.5-4. Эта цель может быть достигнута следующим заполнением вкладки Сообщение для ввода (рис. 3.5-5).

Вкладка Cообщение об ошибке (рис. 3.5-6) определяет реакцию системы на ошибочный ввод. Сообщения об ошибке может быть трех видов (перечислены в порядке их “строгости”):

Останов. Жесткая реакция системы. Возможны два действия пользователя – повтор (правильный), ввода Повторить или полный отказ от ввода Отмена.

Предупреждение. Пользователь может игнорируя предупреждение Да оставить вве­денное значение, вернуться для исправления ввода Нет или совсем отказаться от него Отмена.

Сообщение – Пользователь может оставить введенные данные Ок, или отказаться от них Отмена.

Мы выберем реакцию Останов (рис. 3.5-6), которая, при неверном вводе, выдаст сообщение (см. рис. 3.5-7).

А В   ФАМИЛИЯ               Иванов А.А.       Петров В.С.       Кузин А.И.     Рис. 3.5-8

Усложним пример. Сделаем чтобы анализируемое условие реагировало на естественное течение времени и его не приходилось бы изменять каждый год (предыдущее решение годилось только для 1999г.). Для этого придется выбрать Тип данных: Другой во вкладке Пара метры. Теперь в поле Формула можно вставить условие вида

 

(СЕГОДНЯ()-C2)/364>16; СЕГОДНЯ()-C2)/364<80),

которое обеспечит нам контроль даты рождения (находится в С2) в зависимости от текущего времени (от СЕГОДНЯ()). Здесь число дней, прошедших от даты рождения до текущей даты делится на 364 (т.е. вычисляется возраст) и сравнивается с 16 и 80.

Другой пример. Положим, в ячейку А2 требуется вводить фамилии сотрудников отдела (Иванов А.А., Петров В.С., Кузин А.И. и т.д.) строго в определенном формате. Здесь нужно установить Тип данных: Список в окне Параметры и внести в поле Источник или адрес блока, где находятся фамилии, или сами эти фамилии:

Источник: