Форматирование сводной таблицы excel

Форматирование сводной таблицы excel

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

то на первый взгляд всё сработает:

Правило будет выглядеть так

Однако, если вы обновите сводную таблицу, то форматирование исчезнет! А правило неожиданно станет таким:

В чём тут дело? Оказывается при создании правила УФ нельзя совместно выделять и обычные ячейки и ячейки сводной таблицы. Встаньте на любую ячейку сводной таблицы из области значений и начните создавать правило УФ:

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

Обратите внимание как выглядит правило УФ — добавляется иконка сводной таблицы.

Подобное УФ сохранит свою работоспособность даже, если вы поле Город перетащите из раздела СТРОКИ в раздел КОЛОННЫ сводной таблицы. Вот так:

Это всё, что я имел сказать про условное форматирование сводных таблиц 🙂

Разработка макета и формата сводной таблицы

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

Важно: Вкладка Работа со сводными таблицами на ленте содержит две вкладки: Анализ (в Excel 2013 и более поздних версиях) или Параметры (Excel 2010 и Excel 2010) и Конструктор. Обратите внимание, что в соответствующих процедурах, описанных в этой статье, указываются обе вкладки, Анализ и Параметры.

Изменение формы макета сводной таблицы

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

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

Табличная форма. В этой форме отображается один столбец для каждого поля и выделяется место для заголовков полей.

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

Приведение сводной таблицы к сжатой, структурной или табличной форме

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.

На вкладке Конструктор в группе Макет нажмите кнопку Макет отчета и выберите один из указанных ниже вариантов.

Чтобы связанные данные не выходили по горизонтали за пределы экрана (это позволяет меньше пользоваться прокруткой), выберите команду Показать в сжатой форме.

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

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

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

Изменение способа отображения подписей элементов в форме макета

В сводной таблице выберите поле строки.

На ленте откроется вкладка Работа со сводными таблицами.

Также в форме структуры или табличной форме можно дважды щелкнуть поле строки и перейти к действию 3.

На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

В диалоговом окне Параметры поля откройте вкладку Разметка и печать и в разделе Макет выполните одно из указанных ниже действий.

Чтобы элементы полей отображались в форме структуры, установите переключатель в виде структуры.

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

Чтобы элементы полей отображались в табличной форме, установите переключатель в виде таблицы.

Изменение расположения полей в сводной таблице

Чтобы получить макет в нужном виде, можно добавлять и удалять поля, а также изменять их положение с помощью списка полей сводной таблицы.

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

Если нужные поля отсутствуют в списке полей, возможно, требуется обновить сводную таблицу, чтобы отобразить новые поля, вычисляемые поля, оценки, вычисляемые оценки и измерения, добавленные с момента выполнения последней операции. На вкладке Параметры в группе Данные нажмите кнопку Обновить.

Дополнительные сведения о работе со списком полей сводной таблицы см. в статье Упорядочение полей сводной таблицы с помощью списка полей.

Добавление полей в сводную таблицу

Выполните одно или несколько из указанных ниже действий.

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

По умолчанию текстовые поля добавляются в область Названия строк, числовые поля — в область Значения, а иерархии даты и времени OLAP — в область Названия столбцов.

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

Щелкните имя поля и, удерживая нажатой кнопку мыши, перетащите его из раздела полей в одну из областей раздела макета.

Копирование полей в сводной таблице

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

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

Повторите действие 1 столько раз, сколько нужно копий поля.

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

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

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

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

Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

Изменение положения полей в сводной таблице

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

Форматирование сводной таблицы Excel 2007

В этой статье мы рассмотрим способы форматирования сводной таблицы — то есть, как её настроить согласно своим вкусам и предпочтениям.

Вы можете тренироваться на собственных сводных таблицах, или скачать для тренировок наш образец (простая таблица.xlsx).

При создании новой сводной таблицы Excel автоматически именует её столбцы и заголовки. Однако это легко исправить — достаточно отредактировать ячейку заголовка столбца или таблицы. Например, мы переименовали предыдущий заголовок таблицы:

в более понятный:

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

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

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

Группа Макет вкладки Конструктор содержит кнопки, которые позволяют настроить сам макет нашей таблицы, а именно — Макет отчета, Промежуточные итоги, Общие итоги и Пустые строки.

Команда Макет отчета предлагает три варианта:

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

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

Промежуточные итоги — эта команда используется указания того, как нужно выводить промежуточные итоги: в начале группы, в конце, или не выводить вообще.

Команда Общие итоги предлагает вывести общие итоги только для строк, только для столбцов, для строк и для столбцов одновременно, или не выводить их вообще.

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

а вот так после:

В начало страницы

В начало страницы

Форматирование сводной таблицы Excel для планирования продаж

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

Настройка сводной таблицы в Excel

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

1. Клиент – ID клиента, полное или короткое наименование.
2. Товар – наименование товара или наименование группы товаров.
3. Период – промежуток времени с единицами измерения (годы, кварталы, месяцы, дни), за который были собраны статистические показатели.
4. Количество – количественные данные по продажам в соответствии с единицами измерения (шт., кг., л. и т.п.).
5. Сумма – количество умножено на цены проданных товаров (данный показатель необходим для проведения сложных вычислений при планировании).

Конечно же, можно добавить еще больше показателей, все зависит от степени детальности плана бюджетирования. Период времени прогнозирования при планировании в большинстве случаев берется на год. Поэтому период, за который собирались статистически данные по продажам должен быть не менее чем за 1 предыдущий год. Это позволит учитывать сезонность продаж.

Пример таблицы с статистическими данными скопируйте из этого фрейма на пустой рабочий лист Excel:

На основе исходных данных построим сводную таблицу, в которую подтянем данные таблицы планирования. Если Ваши исходные данные находятся во внешней базе данных таких как SQL или Access, то можно сразу построить сводную таблицу подключившись непосредственно к внешним источникам данных. Но если данные уже экспортированы в таблицу Excel (так как в нашем случаи), тогда выделите диапазон ячеек A1:E61 и выберите инструмент: «ВСТАВКА»-«Сводная таблица». В появившемся окне «Создание сводной таблицы» просто Нажмите кнопку ОК не меня параметров, установленных по умолчанию.

Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа.

Чтобы задать новое имя для новой сводной таблицы, сделайте ее активной и выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Сводная таблица». И в поле «Имя:» введите текст «стАнализ». Потом просто нажмите клавишу Enter для подтверждения нового имени:

Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях:

Параметры полей значений в сводной таблице:

• в полю СТРОКИ – значения Клиент и Год;
• в полю КОЛОННЫ – значения Товар;
• в полю ЗНАЧЕНИЯ – Количество.

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

Параметры сводной таблицы Excel

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

1. Активация. Перейдите курсором на любую ячейку в области сводной таблицы, чтобы сделать ее активной и получить доступ к дополнительной панели инструментов «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ».
2. Преобразование в классический вид. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Сводная таблица»-«Параметры». В появившемся окне перейдите на закладку «Вывод» и активируйте галочкой опцию «Классический макет сводной таблицы (разрешено перетаскивание полей)».
3. Скрытие промежуточных итогов. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Макет»-«Промежуточные итоги»-«Не показывать промежуточные суммы».
4. Изменение стиля. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Стили»-«Очистить». Благодаря этому действию теперь видны линии границ, разделяющие клиентов.
5. Переименование названий столбцов. Текст в названии столбца «Сумма по полю…» можно отредактировать прямо в ячейке. Важно чтобы новые названия столбцов не совпадали с названиями полей. Для этого можно их просто взять в скобки «», ведь для Excel это уже разные значения.
6. Разделение разрядов для тысяч. Перейдите курсором на любую ячейку со значением в столбце количество и выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Активное поле»-«Параметры поля». В появившемся окне «Параметры поля значений» на вкладке «Операция» нажмите на кнопку «Числовой формат». Откроется окно «Формат ячеек» где нужно в левой коленке выбрать опцию «Числовой», а в правой – активировать галочкой опцию «Разделитель групп разрядов ()» и установить значение 0 в поле «Число десятичных знаков:». ОК на всех открытых окнах.
7. Зафиксировать строки в таблице. Перейдите курсором в ячейку C5 и выберите инструмент: «Вид»-«Окно»-«Закрепить области». Теперь при горизонтальной или вертикальной прокрутке рабочего листа, пользователю будут всегда доступны основные показатели и инструменты управления сводной таблицей.

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

Сводные таблицы Excel

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

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

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

Рис 6.25. Создайте простую сводную таблицу

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

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

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

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

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

Рис. 6.27. Условные гистограммы добавляются с помощью всего нескольких щелчков

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

• 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.

Рис. 6.28. В этой сводной таблице отображаются поля Объем продаж, Период продаж (в часах) и вычисляемое поле, определяющее значение выручки за час

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

Рис. 6.29. Диалоговое окно Создание правила форматирования

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

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

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

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

Рис. 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.

Рис. 6.31. Выберите в раскрывающемся меню Стиль формата значение Наборы значков

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

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

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

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

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

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