1.7. Условное форматирование

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

Для установления условных форматов следует выделить необходимые ячейки и в меню Формат выбрать команду Условное форматирование. (см. рис. 3). Чтобы в качестве условия форматирования использовать значения выделенных ячеек, нужно выбрать параметр значение и операцию сравнения, а затем ввести значение в необходимое поле. Можно ввести заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=). Формула используется в качестве критерия форматирования для оценки данных или условий, отличных от значений выделенных ячеек. Слева из поля выбирается параметр формула, а затем в поле справа вводится формула. Формула должна принимать логическое значение ИСТИНА или ЛОЖЬ. Для указания типа шрифта, его цвета, подчеркивания, рамки и затенение ячеек или узоров следует нажать кнопку Формат.

Рис. 3. Окно условного форматирования

Выбранные форматы будут применены, только если значение ячейки отвечает поставленному условию или формула принимает значение ИСТИНА.

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

Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо определить имя для данных на другом листе или в другой книге либо ввести ссылку на эти данные в ячейке активного листа, затем сослаться на указанную ячейку или имя в формуле. Например, чтобы оценить данные, содержащиеся в ячейке A5 на Листе1 книги BOOK1.xls, в ячейку активного листа нужно ввести следующую ссылку, включая знак равенства (=): =[BOOK1.xls]ЛИСТ1!$A$5

При помощи формулы можно также оценивать критерии, не основанные на данных листа. Например, формула =ДЕНЬНЕД("12.5.99")=7 принимает значение ИСТИНА, если дата 12 мая 1999 г. является воскресеньем. Значение ячейки никак не влияет на истинность условия только в том случае, если в формуле нет специальных ссылок на форматируемые выделенные ячейки. Если же формула ссылается на выделенные ячейки, то в нее необходимо ввести ссылки на ячейки. Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вставляет абсолютные ссылки, а если необходимо, чтобы ссылки для каждой ячейки в выделенном диапазоне были согласованы, следует использовать относительные ссылки. Относительные ссылки определяют положение ячейки относительно положения ячейки формулы, а абсолютные ссылки всегда указывают на конкретные ячейки. В случае относительной адресации, например, если ввести формулу =B1+B2 в ячейку В4, MS Excel интерпретирует ее, как “прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше”. Если скопировать формулу =B1+B2 из ячейки В4 в С4, MS Excel также интерпретирует формулу, как “прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше”. Таким образом, формула в ячейке С4 примет вид =C1+C2. Если при копировании формул необходимо сохранить ссылку на конкретную ячейку или область, то следует воспользоваться абсолютной адресацией. Для ее задания необходимо перед именем столбца и перед номером строки ввести символ $. Например: $В$4 или $С$2:$F$48 и т. д. Можно использовать также смешанную адресацию, в случае которой символ $ ставится только там, где он необходим. Например: В$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой – нет.

Даты и время рассматриваются как числа. Например, при сравнении содержимого ячейки с датой 7 января 2001, дата будет представлена в виде числа 36898.

Пример 1. Применить условное форматирование к диапазону ячеек A2:C4 (см. рис. 4). Требуется установить формат ячейки – шрифт курсив, зеленый, если ее значение составляет 30 и более процентов от суммы значений по строке и полужирный синий для ячеек из указанного диапазона, если значение составило менее 20 процентов от суммы. В формуле определяется относительная часть (номер строки), чтобы каждая ячейка из диапазона А2:С4 сравнивалась с суммой соответствующей строки.

	Значение ячейки	Формула	Форматы
Условие 1	Больше или равно	=СУММ($A2:$d2)*0.3	Курсив зеленый
Условие 2	Меньше или равно	=СУММ(($A2:$d2)*0.2	Полужирный фиолетовый

Рис. 4. Пример условного форматирования с использованием значения

Пример 2. Выделить синим шрифтом четные числа из диапазона A2:С4. (см. рис.5). К указанному диапазону применяем условный формат (четные числа определяем при помощи функции ОСТАТ: все числа делятся на 2 и, если остаток равен 0, то формула принимает значение ИСТИНА).

	Формула	Форматы
Условие 1	=ОСТАТ(A2;2)=0	Полужирный курсив синий

Эта формула должна оценивать все ячейки в диапазоне. При вводе такой формулы в диалоговом окне Условное форматирование рекомендуется вводить ссылку только на активную ячейку в выделенном диапазоне. MS Excel устанавливает ссылки для других ячеек относительно активной.

Рис. 5. Условное форматирование с использованием формул