Условное форматирование в сводных таблицах

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

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

Рис. 1. Исходная сводная таблица

Скачать заметку в формате Word или pdf, примеры в формате Excel

Сначала выделите все ячейки, которые подлежат условному форматированию; в нашем примере С4:С17. Перейдите на вкладку ленты Главная в группу Стили и щелкните на кнопке Условное форматирование (рис. 2).

Рис. 2. Для значений сводной таблицы выберите условное форматирование в виде гистограммы

В области сводной таблицы появятся гистограммы наряду со значениями в поле Сумма по полю Объем продаж2. Чтобы отобразить одну лишь гистограмму, выполните следующие действия.

1. Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
2. В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
3. Установите флажок Показывать только столбец (рис. 3).

Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы

В ячейки добавляется набор гистограмм, соответствующих хранящимся в них значениям (рис. 4). Немного напоминает горизонтальную гистограмму, не правда ли? Самое удивительное, что при фильтрации данных (например, рынков сбыта), осуществляемой в области ФИЛЬТРЫ, гистограммы динамически обновляются в соответствии с набором выбранных рынков сбыта. Вы можете переименовать заголовки столбцов сводной диаграммы, а также выбрать по своему усмотрению ширину столба Гистограмма.

Рис. 4. Гистограммы условного форматирования

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

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

• 10 первых элементов;
• первые 10%;
• 10 последних элементов;
• последние 10%;
• выше среднего;
• ниже среднего.

Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная, щелкните на кнопке Условное форматирование и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (см. рис. 2).

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

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

Рис. 5. Исходная сводная таблица

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

Рис. 6. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

• к выделенным ячейкам;
• ко всем ячейкам, содержащим значения «Объем продаж». Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Рекомендуется применять этот вариант в ходе анализа данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
• ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта». Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта; при этом исключаются промежуточные и общие итоги. Его лучше всего использовать для анализа отдельных значений.

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

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

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

Рис. 7. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

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

Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Измените описание правила. Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата значение Наборы значков. В раскрывающемся списке Стиль значка выберите стиль, применяемый для выполнения анализа. Стиль, заданный на рис. 7, идеально подходит в случаях, когда сводную таблицу невозможно полностью разукрасить разными цветами. В заданной конфигурации настроек программа будет добавлять разные значки, распределяя значения в ячейках по трем следующим категориям: >=67, >=33 и <33. Учтите, что в вашем конкретном случае граничные значения категорий можно легко изменить до необходимого уровня. В нашем сценарии выбраны значения, заданные по умолчанию. Щелкните ОК, чтобы применить условное форматирование к сводной таблице. Как видно на рис. 8, в сводную таблицу добавляются значки для быстрого определения категории, которой соответствует каждое значение.

Рис. 8. Условное форматирование применено к сводной таблице

Теперь примените такое же условное форматирование к полю Средняя выручка за час (рис. 9).

Рис. 9. Условное форматирование позволяет добиться весьма познавательных и важных результатов

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

 

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 6.