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

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

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

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

Сначала выделите все поле Объем продаж в области значений. После выделения объема для каждого периода Торговый период перейдите на вкладку ленты Главная и щелкните на кнопке Условное форматирование (Conditional Formatting), находящейся в группе Стили (Styles), как показано на рис. 6.26.

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

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

В следующем списке приведены готовые сценарии условного форматирования:

• 10 первых элементов (Top Nth Items);
• первые 10% (Top Nth %);
• 10 последних элементов (Bottom Nth Items);
• последние 10% (Bottom Nth %);
• выше среднего (Above Average);
• ниже среднего (Below Average).

Как видите, Excel 2010 содержит сценарии с наиболее распространенными критериями условного форматирования.

Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная (Ноmе), щелкните на кнопке Условное форматирование (Conditional Formatting) группы Стили (Styles) и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (Clear Rules^Clear Rules from this PivotTable).

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

В этом сценарии мы попытаемся отследить взаимосвязь между общим объемом продаж и выручкой за час. Основная идея заключается в применении условного форматирования, облегчающего поиск различий и совпадений. Поместите курсор в столбец Объем продаж. Перейдите на вкладку Главная (Ноmе) и щелкните на кнопке Условное форматирование (Conditional Formatting). Выберите команду Создать правило (New Rule). На экране появится диалоговое окно Создание правила форматирования (New Formatting Rule), показанное на рис. 6.29.

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

• к выделенным ячейкам (Selected Cells). Условное форматирование применяется ко всем выделенным ячейкам.
• ко всем ячейкам, содержащим значения «Объем продаж» (All Cells Showing «Sales_Amount» Values). Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Лучше всего применять этот вариант при анализе данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
• ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (All Cells Showing «Sales_Amount» Values for «Market»). Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта (исключая промежуточные и общие итоги). Его идеально использовать при анализе отдельных значений.

Названия команд Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отображают названия полей, содержащихся в области столбцов и активных элементов данных.

В нашем примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта», как показано на рис. 6.30.

В разделе Выберите тип правила (Select a Rule Туре) укажите правило, согласно которому будет применяться условное форматирование.

• Форматировать все ячейки на основании их значений (Format All Cells Based on Their Values). Этот переключатель позволяет применять условное форматирование к ячейкам, содержимое которых соответствует заданному диапазону значений. Таким образом, значения в заданном диапазоне сравниваются с уже введенными в ячейках. Лучше всего применять его для определения отклонений в большом наборе данных.
• Форматировать только ячейки, которые содержат (Format Only Cells That Contain). Данный переключатель применяется для условного форматирования ячеек, удовлетворяющих определенным условиям. Обратите внимание на то, что в этом случае значения в ячейках не сравниваются с другими значениями. Чаще всего используется при сравнении всего набора данных с заранее заданной характеристикой.
• Форматировать только первые и последние значения (Format Only Top or Bottom Ranked Values). Играет важную роль при применении условного форматирования только к первым или последним нескольким значениям набора данных.
• Форматировать только значения, которые находятся выше или ниже среднего (Format Only Values That Are Above or Below the Average). Этот переключатель позволяет применять условное форматирование к значениям, большим или меньшим среднего значения, рассчитанного для набора данных.
• Использовать формулу для определения форматируемых ячеек (Use a Formula to Determine Which Cells to Format). В этом варианте определение значений, к которым применяется условное форматирование, осуществляется согласно пользовательской формуле. Если значение ячейки, подставленное в формулу, приводит к получению результата true (истина), то к такой ячейке применяется условное форматирование. Если же возвращается результат false (ложь), то условное форматирование к ячейке не применяется.

Гистограммы, цветовые шкалы и наборы значков применяются только в случае, если выделенные ячейки форматируются на основе введенных в них значений. Это означает, что для использования указанных индикаторов необходимо установить первый переключатель — Форматировать все ячейки на основании их значений (Format All Cells Based on Their Values).

В нашем сценарии мы будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений. Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Изменение описания правила (Edit the Ruie Description). Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата (Format Style) значение Наборы значков (Icon Sets).

В раскрывающемся списке Стиль значка (Icon Style) выберите значение 3 знака. Такой стиль значков идеально подходит в случаях, когда сводную таблицу невозможно полностью разукрасить разными цветами. В текущий момент диалоговое окно Создание правила форматирования должно выглядеть так, как показано на рис. 6.31.

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

>=67, >=33 и <33

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

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

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

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