Краткие сведения

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

Значения выделенных ячеек можно сравнивать с константой или с результатами вычислений по формуле. Чтобы определить условие форматирования при оценке данных в ячейках, не входящих в выделенный диапазон, или при проверке нескольких критериев, можно использовать логическую формулу. Если был выбран параметр формула, то указанная формула должна принимать значения ИСТИНА (1) или ЛОЖЬ (0). Формула должна начинаться со знака равенства (=). Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо указать имя для данных на другом листе или в другой книге либо ввести ссылку на эти данные в ячейке активного листа, а затем сослаться на указанную ячейку или имя в формуле.

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

При помощи формулы можно также оценивать критерии, не основанные на данных листа. Например, формула =ДЕНЬНЕД("12.5.99")=1 принимает значение ИСТИНА, если дата 12 мая 1999 г. является воскресеньем. Значение ячейки никак не влияет на истинность условия только в том случае, если в формуле нет специальных ссылок на форматируемые выделенные ячейки. Если же формула ссылается на выделенные ячейки, то в нее необходимо ввести ссылки на ячейки.

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

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

В приведенном ниже примере условные форматы, примененные к ячейкам B3:E4, используются для анализа вклада ежеквартальных вложений в общий годовой итог. Если вложения, сделанные в течение квартала, составили 30 и более процентов от общего годового итога, то результаты отображаются полужирным шрифтом на сером фоне. Если же вложения, сделанные в течение квартала, составили 20 и менее процентов, то результаты отображаются полужирным белым шрифтом на черном фоне.

Выполните команду Формат / Условное форматирование… Заполните поля диалогового окна, как на рисунке 24. Для добавления второго условия нажмите кнопку А также >>.

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

В формуле определяется относительная часть (номер строки) ссылки на ячейку $F3, чтобы каждая ячейка из диапазона B3:E4 сравнивалась с соответствующим значением итога в столбце F. Результат форматирования представлен на рисунке 25.

Рис. 25. Результаты форматирования

Теперь рассмотрим пример задания условного форматирования с помощью формулы. Для предыдущего примера выделите полужирным шрифтом на сером фоне все числа, кратные двум. На рисунке 26 приведена формула, которая должна оценивать все ячейки в диапазоне.

Рис. 26. Задание условного форматирования с помощью формулы

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

Рис. 27. Результат форматирования

Усложним задачу. Теперь нужно выделить полужирным шрифтом на сером фоне числа, кратные двум, но не кратные трем. На рисунке 28 показан пример заполнения диалогового окна.

Рис. 28. Логическая формула в условном форматирование

Введенная логическая формула И должна возвращать значение ИСТИНА или ЛОЖЬ. В ней фигурирует относительный адрес активной ячейки, входящей в блок. Результат форматирования представлен на рисунке 29.