Excel 2007: условное форматирование

Excel 2007: условное форматирование

 

Продолжая тему форматирования листов Excel 2007, мы переходим к важнейшему вопросу — средствам условного форматирования ячеек. Для бухгалтера эта тема особенно актуальна. Ведь условное форматирование относится к тем инструментам, которые не только решают оформительские задачи, но и помогают анализировать данные, контролировать ошибки в вычислениях. И в этом смысле Excel 2007 предлагает ряд новых и довольно интересных возможностей. Как ими воспользоваться, где применить те или иные инструменты, мы рассмотрим в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

 

Форматирование нужно не только для красивого оформления документов. Это его прямая, но не единственная задача. Бухгалтеру важно организовать визуальный контроль над правильностью данных. Удобно пользоваться таблицей, где «критические» (или ошибочные) значения Excel выделит хорошо заметным цветом или покажет на контрастном фоне. Приятно работать со списком значений, в котором специальным форматом помечены минимальные и максимальные элементы. А если бы удалось гибко связать «интенсивность» форматирования с содержимым ячейки, то оформительская функция превратилась бы в полноценное средство анализа данных! По-видимому, такого же мнения придерживались и разработчики Excel 2007. Созданный ими инструмент условного форматирования в полном объеме решает все перечисленные задачи и, более того, предлагает для этого несколько альтернативных вариантов.

 

Л

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

Вызываем закладку «

Главная» основного меню Excel 2007. Находим группу из трех иконок с названием «Стили». В первой иконке этой группы сосредоточены все возможности условного форматирования ячеек. Она так и называется: «Условное форматирование» (рис. 1). Иконка представляет собой список. Щелкаем на ней — откроется меню из восьми пунктов (рис. 2). В верхней части меню расположены два раздела. Они служат для определения условий наложения форматов и для форматирования минимальных и максимальных значений в блоке ячеек. Эти разделы называются «Правила выделения ячеек» и «Правила отбора первых и последних значений». Далее расположены три пункта меню для специального форматирования ячеек рабочего листа. Они предназначены для визуального выделения данных для их анализа или в презентационных целях. Так, пункт «Гистограммы» позволяет снабдить ячейки цветовыми градиентами, чтобы видеть соотношение величин в отформатированном списке. Аналогичную задачу решают два следующих пункта меню — «Цветовые шкалы» и «Наборы значков». Только для форматирования они используют вместо градиента фиксированный набор цветов или специальных символов. В нижней части меню сосредоточены три пункта:

1) «

Создать правило…» — универсальный инструмент для создания любых правил условного форматирования и всех возможных вариантов оформления;

2) «

Удалить правила» — позволяет стереть ранее созданные правила для ячеек, листа или всей таблицы;

3) «

Управление правилами…» — активизирует работу модуля «Диспетчер правил условного форматирования».

 

Ячейкам рабочего листа можно присвоить сразу несколько правил. Максимальное количество таких правил в Excel 2007 составляет 64, в предыдущих версиях программы этот параметр был равен трем. Кроме того, начиная с Excel 2007 условное форматирование можно указывать для несвязного блока ячеек. Ранее это было невозможно. Однако пора переходить к практической работе. Начнем мы с первого пункта меню — «

Правила выделения ячеек».

 

Создание правил для условного форматирования

Чтобы наглядно поработать с условным форматированием, нам понадобится исходный материал, т. е. таблица. Я выбрал для этой цели фрагмент прайс-листа из предыдущей статьи (рис. 3). Нашей первой задачей будет выделить специальным форматированием в прайс-листе позиции товаров, которые удовлетворяют определенному условию. Например, выделим все розничные цены, которые превышают величину 20 грн. Для этого делаем так:

1) на рабочем листе «

Прайс» выделяем блок ячеек «C2:C13»;

2) переходим на закладку «

Главная» основного меню;

3) в группе «

Стили» щелкаем на иконке «Условное форматирование». Раскроется список из 8 вариантов (рис. 2);

4) из него выбираем пункт «

Правила выделения ячеек». Появится меню из 8 пунктов, изображенное на рис. 4;

5) щелкаем левой кнопкой мыши на строке «

Больше…». Появится окно, изображенное на рис. 5;

6) в левой части окна расположено поле для ввода значения. Сюда печатаем число «

20»;

7) в правой части окна виден список с предопределенными вариантами форматирования. Щелкаем на списке левой кнопкой мыши — раскроется перечень из семи вариантов, как показано на рис. 5;

8) выбираем любой способ форматирования, например «

Светло-красная заливка и темно-красный текст». Изменения в прайсе показаны на рис. 6 (колонка «С»).

Важно!

Выбирая способ форматирования, внимательно следите за изменениями на рабочем листе. Excel 2007 устроен так, что показывает предполагаемый вид ячеек сразу после выбора варианта оформления из предложенного списка (рис. 5). Таким образом, вы сможете оценить результат форматирования, не покидая окна, изображенного на рис. 5.

Вследствие наших действий все значения цен, которые превышают 20 грн., отлично видны в третьей колонке прайса. Это ячейки «

C2» и «C4».

Оставим форматирование колонки «

С» без изменений и поэкспериментируем с другими разделами меню. В четвертой колонке прайса (столбец «D» с названием «МО») попробуем выделить мелкооптовые цены, которые попадают в диапазон от 3 до 10 грн. Для этого делаем так:

1) на рабочем листе выделяем блок ячеек «

D2: D13»;

2) переходим в раздел основного меню «

Главная». Щелчком на иконке «Условное форматирование» раскрываем меню, изображенное на рис. 2;

3) выбираем пункт «

Правила выделения ячеек». Появится меню, как на рис. 4;

4) выбираем вариант «

Между…». Появится окно, изображенное на рис. 7;

5) как и следовало ожидать, в этом окне два значения:

минимальная и максимальная границы интервала. Вводим величины «3» и «10». Теперь остается определить параметры форматирования;

6) щелкаем на списке в правой части окна. На этот раз стандартные способы оформления мы применять не будем и выберем пункт «

Пользовательский формат». Откроется стандартное окно форматирования, изображенное на рис. 8;

7) с этим окном мы уже знакомы. Переходим на закладку «

Заливка», выбираем из палитры желтый цвет. Все мелкооптовые цены, находящиеся в диапазоне от 3 до 10 грн., будут показаны на желтом фоне (рис. 6, колонка «МО»).

Совет

Работа с пользовательским форматом открывает практически неограниченные возможности при оформлении таблиц с использованием условного форматирования. Я советую обратить внимание на вариант заливки узором. Такой способ для бухгалтера очень удобен, ведь большинство документов приходится не только просматривать на экране, но и отправлять на печать. В этом случае цветной фон ячеек может сильно ухудшить читабельность документа. Примените светлую узорную заливку (например, «12,5 %-ый серый» или «6,25 %-ый серый»), и документ будет хорошо смотреться как на экране, так и на бумаге.

На практике часто приходится визуально анализировать таблицы, в которых данные представлены в

текстовой форме. Типичный пример — поиск товаров определенной модели или от конкретного производителя в длинном прайс-листе или каталоге. Быстро справиться с такой задачей поможет условное форматирование по правилу «Текст содержит…». Испытаем его на нашем примере:

1) выделяем на листе «

Прайс» блок ячеек «A2:A13» с наименованиями товаров;

2) вызываем инструмент условного форматирования, в меню «

Правила выделения ячеек» выбираем пункт «Текст содержит…»;

3) появится окно, аналогичное изображенному на рис. 5. В поле для ввода значения (в левой части окна) печатаем текст, например «

dvd»;

4) устанавливаем параметры форматирования. Я выбрал

пользовательский формат и узорную заливку серым;

5) нажимаем «

ОК». Все товары, в названии которых есть текст «dvd», получили узорную заливку и теперь хорошо заметны на общем фоне прайса. На рис. 6 это ячейки «A5», «A11» и «A13».

Посмотрим вкратце, что предлагают остальные пункты меню «

Правила выделения ячеек»:

— «

Больше…» (позволяет выборочно отформатировать ячейки, значения в которых превышают указанную величину);

— «

Меньше…» (накладывает условное форматирование на ячейки, в которых значения меньше заданной величины);

— «

Равно…» (форматирует ячейки, если значение в точности совпадает с указанным);

— «

Дата…» (эта возможность направлена на форматирование ячеек, где записаны даты);

— «

Повторяющиеся значения…» (позволяет выделить в таблице ячейки с одинаковыми значениями).

Думаю, что назначение перечисленных пунктов понятно из их названия. Хотелось бы дать комментарии только по поводу двух разделов меню «

Правила выделения ячеек»: «Дата…» и «Повторяющиеся значения…».

Раздел «

Дата…» работает с ячейками, в которых записаны даты. Форма диалога при работе с этим разделом меню такая же, как на рис. 5. Только вместо поля для ввода конкретного значения расположен список. В нем предлагается 10 вариантов: «Вчера», «Сегодня», «Завтра», «За последние 7 дней» и т. д. (рис. 9). Важным преимуществом такого способа форматирования является то, что Excel автоматически определяет значение текущей даты и по ней корректирует условия форматирования. Например, вы работаете с базой данных поступлений ТМЦ, в которой в отдельной колонке записана дата проведения операции. Наложите на колонку с датами условное форматирование, выбрав вариант «Дата…» и условие «Сегодня». Теперь каждый день вы будете видеть приходы за текущую дату. Для бухгалтера форматирование данных с учетом хронологии их регистрации — очень важный инструмент.

На практике бухгалтеру приходится иметь дело с большими таблицами. Справочники, реестры, базы данных проводок могут насчитывать сотни, тысячи и даже десятки тысяч записей. Именно при работе с такими объемами преимущества компьютера полностью проявляют себя, поэтому разработчики Excel 2007 уделили повышенное внимание средствам обработки данных. Более того, проблема работы с большими объемами данных нашла свое отражение даже в инструментах условного форматирования. В данном случае речь идет о правиле выбора повторяющихся значений. Попробуем применить его к нашему прайсу:

1. Выделяем на листе «

Прайс» колонку с оптовыми ценами (это блок ячеек «E2:E13»).

2. Вызываем инструмент условного форматирования, в меню «

Правила выделения ячеек» выбираем пункт «Повторяющиеся значения…».

3. Появится окно, изображенное на рис. 10. В этом окне поле для выбора значения представлено списком из двух элементов: «

повторяющиеся» и «уникальные». По умолчанию стоит «повторяющиеся».

4. Форматирование оставляем в положении «

Светло-красная заливка и темно-красный текст». Щелкаем на кнопке «ОК». Excel изменил формат у ячеек «E11» и «E13»: в них записана одинаковая оптовая цена 2,34 грн. Выбрав в качестве значения для поиска вариант «уникальные», Excel отформатирует в прайсе все ячейки, кроме «E11» и «E13».

Условное форматирование удобно использовать для анализа больших баз данных. Около месяца назад мне понадобилось найти причину расхождения в актах сверки с одним из контрагентов. Подробная база поставок и поступлений насчитывала около 4200 записей. Кропотливая работа нашего бухгалтера за терминалом «1С» в течение получаса успехом не увенчалась. Цифры упорно не сходились, и причина этого оставалась загадкой. Вооружившись Excel, проблему удалось решить за 3 минуты. Вначале базу отсортировали по

наименованиям товаров, чтобы получить товарные группы. Затем для каждой группы в отдельности я применил условное форматирование по правилу «Повторяющиеся значения…» с вариантом «уникальные». В данном случае форматирование применялось к колонке с ценой товара. После этого хватило одного беглого просмотра базы, чтобы увидеть две цены, которые отличались от остальных цен в пределах группы. Это и было причиной ошибки.

Совет

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

Последним разделом меню на рис. 4 является «

Другие правила…». Эту возможность мы рассмотрим позднее, когда начнем знакомиться с Диспетчером форматирования.

 

Удаление правил условного форматирования

К нашему прайсу мы применили четыре правила условного форматирования. Настало время их удалить. Для этого делаем так:

1. Выделяем на листе «

Прайс» блок ячеек с условным форматированием. В нашем случае это «A2:E13». Можно выделить просто колонки с «A» по «E».

2. Переходим на закладку «

Главная» и щелкаем на иконке «Условное форматирование».

3. Появится меню, как на рис. 2. Ставим указатель на раздел «

Удалить правила». Excel покажет дополнительное меню, изображенное на рис. 11.

4. В меню Excel предлагает четыре варианта: «

Удалить правила из выделенных ячеек», «Удалить правила со всего листа», «Удалить правила из этой таблицы», «Удалить правила из этой сводной таблицы». Выбираем «Удалить правила из выделенных ячеек» и нажимаем «ОК». Форматирование ячеек отменено, а прайс-лист приобрел первоначальный вид, как на рис. 3.

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

 

Условное форматирование и выбор части списка

При работе с данными часто возникает потребность найти максимальные и минимальные значения на основе заданной пороговой величины. Например, это могут быть 10 самых продаваемых позиций ТМЦ или 5 самых прибыльных товаров. Эти и подобные им задачи удобно решать инструментами условного форматирования с использованием правил отбора значений. Посмотрим, как это выглядит на практике:

1) открываем прайс-лист, сейчас в нем нет правил условного форматирования, так как мы их удалили;

2) выделяем ячейки с розничными ценами («

C2:C13»);

3) переходим на раздел меню «

Главная», щелкаем на иконке «Условное форматирование». Появится меню, как на рис. 2;

4) выбираем пункт «

Правила отбора первых и последних значений». Появится меню, изображенное на рис. 12;

5) в этом меню выбираем «

10 первых элементов...». Появится окно, изображенное на рис. 13;

6) слева в окне находится поле для ввода числа. Оно организовано в виде списка. Значение в этом поле можно изменить при помощи стрелок или просто напечатать его. Вводим значение «

5». Способ форматирования не меняем;

7) в колонке «

С» нашего прайса Excel выделит светло-красной заливкой пять максимальных розничных цен;

8) попробуем откорректировать цены в прайсе. Excel будет четко отслеживать все изменения и показывать пять самых дорогих позиций;

9) выделим ячейки «

C2:C13» и повторим все описанные действия, но в окне «Первые 10 элементов» укажем значение порога, равное «3». В прайсе по-прежнему будут выделены пять самых дорогих товаров! Вывод: применяя новое условие форматирования, не забывайте отменить предыдущее. В нашем последнем случае на ячейки «C2:C13» в прайс-листе мы наложили два условия: выделить пять самых дорогих позиций и выделить три самые дорогие позиции. При этом формат ячеек мы не изменили, поэтому в конечном счете увидим результат работы только первого условия (пять самых дорогих позиций). Это довольно распространенная ошибка при работе с условными форматами. В данном случае, чтобы увидеть работу обоих условий, нужно было использовать разное форматирование. Например, первые три значения показать на красном фоне, а первые пять — на зеленом.

Важно!

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

Покажем, как это работает на последнем примере:

1) удалите все правила с рабочего листа «

Прайс» (пункт «Удалить правила» в меню на рис. 2);

2) выделите ячейки «

C2:C13»;

3) перейдите на закладку «

Главная». Щелкните на иконке «Условное форматирование»;

4) выберите «

Правила отбора первых и последних значений». В появившемся меню укажите «10 первых элементов»;

5) в окне параметров введите значение порога «

5» и форматирование «Зеленая заливка и темно-зеленый текст»;

6) нажмите «

ОК», чтобы применить условное форматирование. Пять максимальных цен будут показаны на зеленом фоне;

7) не снимая выделения, повторите п. 3 — 5, но теперь значение порога укажите «

3», а формат ячеек установите в положение «Светло-красная заливка и темно-красный текст»;

8) нажмите «

ОК». В прайсе три максимальные цены показаны на красном фоне, а еще две (из пяти) останутся на зеленом. Это произошло потому, что первое правило имеет более общий характер, чем второе. Поэтому часть форматирования второе правило изменило, а часть — нет;

9) удалите все правила с рабочего листа «

Прайс»;

10) повторите описанные действия, только вначале укажите правило из п. 7 (для трех максимальных цен), а затем правило из п. 5 (для пяти максимальных цен). В результате в колонке розничных цен пять максимальных значений будут показаны на зеленом фоне.

Правило выделения пяти максимумов перекрыло работу правила для форматирования трех максимальных значений.

Совет

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

Теперь пройдем вкратце по оставшимся разделам меню «

Правила отбора первых и последних значений» (рис. 12):

1. «

Первые 10 %.» и «Последние 10 %.» выделяют первые и последние 10 % ячеек в указанном диапазоне рабочего листа. Значение процента можно изменить. Количество выделенных элементов зависит от количества ячеек в диапазоне ! А от содержимого в ячейке зависит, подпадет она под действие правила или нет. Алгоритм работы Excel в данном случае такой. Вначале он выполняет сортировку значений по возрастанию (или по убыванию). На экране эта сортировка не видна, все происходит «внутри», в рабочей области. Затем Excel считает общее количество элементов в списке, берет от этого количества 10 % и применяет форматирование к первым или последним ячейкам отсортированного списка. После этого он показывает результат на экране, но без сортировки.

2. «

10 последних элементов…» отформатирует десять последних элементов в списке исходя их значения. Обычно это минимальные элементы. Порог действия правила (по умолчанию он равен «10») можно изменить.

3. «

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

4. «

Другие правила…» открывает окно «Создание правил форматирования», о котором мы поговорим в следующей статье.

 

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

Тем временем я жду ваших писем, предложений и замечаний на

bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!