В данном примере мы будем использовать данные из сводной таблицы в качестве критерия для условного форматирования значений Excel. Эта статья является продолжением для примера: Форматирование сводной таблицы Excel для планирования продаж.
Содержание
Подготовка плана продаж для условного форматирования
Создадим лист под названием «Шаблон анализа» как указано ниже на рисунке:
Важно чтобы в диапазоне A2:A5 находились имена тех самых клиентов, а в диапазоне B1:F1 – наименование тех самых товаров. В диапазон ячеек B6:G6 вводим формулы: =СУММ(B2:B5). А диапазон G2:G5 заполняется формулами: =СУММ(B2:F2).
Примечание. Для быстрого заполнения указанных выше диапазонов ячеек формулами удобно использовать комбинацию клавиш CTRL+Enter. Сначала выделяем диапазон B12:F12 так чтобы активной осталась ячейка B12 (то есть с нее начинаем выделять диапазон). Нажимаем клавишу F2 вводим формулу =СУММ(B2:B11) и нажимаем комбинацию горячих клавиш CTRL+Enter. Благодаря этому выделенный диапазон сам автоматически заполняется формулами и подставляет нужные ссылки в параметрах функции СУММ. Аналогично выполняем эти действия для автоматического заполнения формулами диапазона G2:G11.
Таблица для планирования продаж будет рассчитана на рост +5% по отношению к предыдущему году. Поэтому ее значения будут увеличены на 5%. Для того чтобы комфортно корректировать план следует записать предполагаемый уровень роста в отельную ячейку и присвоить ей имя. Ссылаясь в формулах на имя этой ячейки, мы можем изменять параметры плана с 5% на любой другой уровень роста. Для этого в ячейку H1 введем текст «Уровень роста», а в ячейку H2 вводим 5%. После чего не убирая курсор с ячейки H2 выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя» и в поле «Имя:» введите значение «урРост» (или просто введите слово «урРост» в поле имен при активной ячейке H2).
Так как данный лист будет содержат дополнительные анализы, то желательно убрать сетку с листа. Для этого снимите галочку с опции: «ВИД»-«Показ»-«Сетка»:
Шаблон для аналитического планирования продаж – готов.
Загрузка данных для шаблона анализа
Теперь подготовим данные:
- На новый лист скопируйте данные из таблицы ниже во фрейме:
- На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица»
- Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа.
- Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях:
Параметры полей значений в сводной таблице:
- в полю СТРОКИ – значения Клиент и Год;
- в полю КОЛОННЫ – значения Товар;
- в полю ЗНАЧЕНИЯ – Количество.
Автоматически созданная сводная таблица имеет весьма нечитабельный вид и очень плохо воспринимается для визуального анализа данных. При желании ее можно отформатировать сводную таблицу. В результате получаем примерно такую картинку:
Теперь, когда данные подготовлены возвращаемся на шаблон анализа. Автоматически заполним табличную часть, сложной формулой которая ссылается на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон табличной части B2:F11 так чтобы активной была ячейка B2. Потом нажмите клавишу F2 и введите формулу:
После ввода формулы нажмите комбинацию клавиш CTRL+Enter.
Данная формула использует функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(). Название данной функции говорит само за себя. В первом аргументе указывается поле для исходных данных (КОЛИЧЕСВТО). Во втором аргументе следует указать любой диапазон сводной таблицы (‘Сводный отчет’!$A$3). Третий аргумент содержит в себе пару поле/элемент которая описывает данные запроса (например, «Год»;2014). В данном примере год товара и клиента должен быть тот-же 2014. Выбор клиента и товара будет постоянно изменяться – соответственно. Поэтому следует их параметризировать смешанными ссылками: $A2 – для клиента и B$1– для товара.
В данном шаблоне изначально было запроектировано, что бюджетирование будет учитывать рост продаж для следующего года +5%. Поэтому в формуле мы умножаем функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на (1+урРост). Ведь рост — это имя ячейки, которая в данный момент содержит значение 5%. Вся эта формула помещается внутрь функции =ЕСЛИОШИБКА(). Благодаря ей все ошибочные результаты вычисления будут заменятся на значение 0. Например, если определенный клиент не покупал определенный товар в 2014 году, тогда формула возвращает значение ошибки #ЗНАЧ!. Но благодаря функции ЕСЛИОШИБКА мы просто получим 0.
Шаблон анализа с условным форматированием
На последнем этапе создания таблицы планирования бюджета продаж мы сконструируем механизм, который проинформирует пользователя о:
- Изменено значение продаж в соответствии с настройками (рост продаж +5% или больше).
- Изменено значение продаж на меньше установленного роста 5%.
Для первого условия формат значений будет изменен на зеленый шрифт, а для второго – красный. В обоих случаях будем применять условное форматирование чтобы экспонировать цветом изменения. Для этого:
- Выделите диапазон табличной части B2:F11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- В появившемся окне «Создание правила форматирования» активируйте опцию «Использовать формулу для определения форматируемых ячеек».
- В поле ввода вводим формулу: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Количество";’Сводный отчет’!$A$3;"Клиент";$A5;"Товар";E$1;"Год";2014)*(1+урРост)’ >
- Нажмите на кнопку «Формат», чтобы задать зеленый цвет для шрифта значений ячеек.
Теперь создаем второе правило для этого же диапазона табличной части. Выполняем все те же действия, только в третьем пункте вводим другую формулу:
А в четвертом пункте указываем красный цвет шрифта формата ячеек. Для предварительной проверки откройте «Диспетчере правил». Для этого выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»
В результате получаем эффективный конструктор для планирования бюджета продаж. Теперь мы видим, что в ячейке B2 слишком маленькое значение и в ячейке C2 соответственно:
В ячейке H2 можем изменять значения (например, вместо 5% вводим 7%) и после нажатия клавиши Enter сразу получаем новый отчет для анализа.
Все описанные таблицы и формулы можно рассмотреть более детально в готовом примере скачав ниже по ссылке файл Excel.
С выходом Excel 2007, компания Microsoft добавила некоторые дополнительные возможности условного форматирования, такие как гистограммы и наборы значков, которые позволяют наглядно показать относительные величины в этих ячейках.
Microsoft внесла изменения в порядок применения условного форматирования к сводным таблицам. Теперь у Вас появилось больше возможностей и больше гибкости при использовании условных форматов. В этой статье я покажу Вам, как применять условное форматирование к сводным таблицам и как оседлать всю мощь новых возможностей.
Как работает условное форматирование
В Excel 2007 и 2010, когда условное форматирование применяется к сводной таблице, оно применяется в большей степени к структуре сводной таблицы, а не к самим ячейкам. Поэтому, когда Вы работаете со сводной таблицей, допустим, передвигаете поля с места на место или отображаете данные различными способами, форматирование обновляется вслед за Вашими действиями. Все это, в сочетании с новыми форматами, делает условное форматирование очень удобным инструментом для использования вместе со сводными таблицами.
Как применить условное форматирование к сводной таблице
Мы рассмотрим условное форматирование на примере сводной таблицы, в которой представлен график приема 4-х ветеринаров за год. В таблице отображается число клиентов с разбивкой по кварталам и по месту приема (ферма или хирургия).
Чтобы сделать данные более наглядными, я выбираю значения Farm (Ферма) и Surgery (Хирургия) первого ветеринара с фамилией Browning, т.е. ячейки от B6 до E7. Выделив этот диапазон, я захожу на вкладку Home (Главная), нажимаю Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выбираю, какой использовать цвет. Эти действия форматируют выбранный диапазон таким образом, что в каждой ячейке появляется гистограмма, которая показывает относительное количество клиентов в каждом квартале и для каждого места приема.
На следующем рисунке мы применяем формат Data Bars (Гистограмма) к первому диапазону данных:
Чтобы применить это же форматирование к аналогичным данным для остальных ветеринаров, нужно выделить ранее отформатированный диапазон, кликнуть иконку Formatting Options (Настройки форматирования), которая появляется в правом нижнем углу диапазона, и выбрать третий из предложенных вариантов (см. рисунок ниже).
Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).
На этом рисунке показано, как применить одинаковое условное форматирование ко всем данным одного типа в нашей сводной таблице:
Теперь всегда, когда в процессе работы со сводной таблицей Вы будете скрывать или отображать данные, гистограммы в ячейках будут изменяться, чтобы отразить относительную величину значения в каждой видимой ячейке по сравнению со всеми остальными видимыми ячейками того же формата.
Гистограммы изменяются, когда меняются данные в сводной таблице, длина гистограммы зависит от данных во всех видимых ячейках:
Мы можем пойти еще дальше и сравнить итоговые значения, используя другое условие для форматирования. В данном случае я хочу сравнить итоги по каждому ветеринару с итогами остальных ветеринаров, поэтому выбираю ячейки от B5 до E5 – общее количество клиентов (по кварталам) ветеринара Browning. Создав в этом диапазоне гистограммы другого цвета, я могу сравнивать итоговые данные о клиентах, которых осматривал ветеринар Browning за эти четыре квартала.
Как и в прошлый раз появляется иконка Formatting Options (Параметры форматирования), при помощи которой мы сможем применить это же условное форматирование к итоговым данным по клиентам каждого ветеринара.
Здесь Вы видите гистограммы другого цвета в ячейках с итоговыми значениями, которые можно наглядно сравнить друг с другом:
Другие параметры
В некоторых случаях имеет смысл разделить условное форматирование, как было сделано в прошлом примере, применяя его только к ячейкам, содержащим данные одного уровня, чтобы разделить итоги и общие итоги (Grand Total). Но не всегда имеет смысл это делать.
Данные в следующей сводной таблице показывают среднее число клиентов, а не суммарное количество клиентов, поэтому Вы можете применить одинаковое условное форматирование ко всем ячейкам таблицы.
Для этого выделим диапазон B6:E6, перейдем Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выберем зелёный цвет для гистограмм. Далее в параметрах форматирования выберем второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает, как итоговые, так и общие итоговые (Grand Total) значения, которые, как и все данные в сводной таблице, являются средними значениями. Поэтому не будет нарушением сравнить их одинаковым образом.
Здесь все ячейки содержат средние значения, поэтому допустимо применить одно правило условного форматирования:
Перемещаем данные
Вернёмся к нашей исходной сводной таблице и начнем перемещать данные. Делая это, Вы заметите, что форматирование сохраняется на нужных местах. Мы передвинули поле Office (Место приема) в область Report Filter (Фильтры) и перенесли поле Quarter (Квартал) в область Row Labels (Строки), и при этом все фиолетовые гистограммы остались на своих местах.
Даже если структура таблицы изменена и поля передвинуты, условное форматирование сохраняет свое положение:
Правила форматирования с ограничениями
Конечно, бывают ситуации, когда не нужно применять условное форматирование ко всем диапазонам, а вместо этого требуется сравнить данные в более узком интервале. В нашем примере, мы хотим увидеть, в каком квартале каждый из наших ветеринаров показал наилучшие результаты, не зависимо от места приема.
Мы создадим отдельное правило условного форматирования для квартальных итогов каждого ветеринара, то есть нам нужно выделить и применить форматирование к ячейкам D7:D10 (потом D12:D15, потом D17:D19 и так далее). Затем используем правило Conditional Formatting > Top 10 Items (Условное форматирование > Первые 10 элементов), устанавливаем условие для 1 ячейки, формат оставляем предложенный по умолчанию. Вы можете либо скопировать это правило в соседний диапазон, либо создать для каждого индивидуально.
Чтобы скопировать форматирование, выделите одну или несколько ячеек с нужным форматом и нажмите Copy (Копировать). Затем выделите диапазон, в который нужно скопировать форматирование, и на вкладке Home (Главная) выберите Paste Special > Formats (Специальная вставка > Форматы).
В некоторых случаях может потребоваться сравнить данные внутри небольшой области, а не применять правило условного форматирования на всю группу несмежных диапазонов:
Условное форматирование в 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. Условное форматирование позволяет добиться весьма познавательных и важных результатов
Не спешите закрывать сводную таблицу и внимательно проанализируйте ее. В полученном представлении любой менеджер может легко отследить взаимосвязь между обшим доходом и выручкой за час. Так, например, менеджер рынка сбыта Даллас будет видеть, что его объем продаж наименьший, а выручка в час — наибольшая. Владея такой информацией, менеджер наверняка определит, что выручка за час, скорее всего, слишком высокая для его рынка. В противоположность ему менеджер с рынка Нью-Йорк будет знать, что его доход максимальный при самых минимальных выручках за час. Последняя характеристика вряд ли обрадует его, даже несмотря на лидерство по общему доходу.
Заметьте, что вы получили интерактивный отчет. Каждый менеджер сможет просматривать данные своих коллег, правильно фильтруя данные сводной таблицы.
