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

В данном примере мы будем использовать данные из сводной таблицы в качестве критерия для условного форматирования значений 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).

Так как данный лист будет содержат дополнительные анализы, то желательно убрать сетку с листа. Для этого снимите галочку с опции: «ВИД»-«Показ»-«Сетка»:

Шаблон для аналитического планирования продаж – готов.



Загрузка данных для шаблона анализа

Теперь подготовим данные:

1. На новый лист скопируйте данные из таблицы ниже во фрейме:
2. На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица»

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

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

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

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

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

Теперь, когда данные подготовлены возвращаемся на шаблон анализа. Автоматически заполним табличную часть, сложной формулой которая ссылается на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон табличной части B2:F11 так чтобы активной была ячейка B2. Потом нажмите клавишу F2 и введите формулу:

После ввода формулы нажмите комбинацию клавиш CTRL+Enter.

Данная формула использует функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(). Название данной функции говорит само за себя. В первом аргументе указывается поле для исходных данных (КОЛИЧЕСВТО). Во втором аргументе следует указать любой диапазон сводной таблицы (‘Сводный отчет’!$A$3). Третий аргумент содержит в себе пару поле/элемент которая описывает данные запроса (например, «Год»;2014). В данном примере год товара и клиента должен быть тот-же 2014. Выбор клиента и товара будет постоянно изменяться – соответственно. Поэтому следует их параметризировать смешанными ссылками: $A2 – для клиента и B$1– для товара.

В данном шаблоне изначально было запроектировано, что бюджетирование будет учитывать рост продаж для следующего года +5%. Поэтому в формуле мы умножаем функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на (1+урРост). Ведь рост — это имя ячейки, которая в данный момент содержит значение 5%. Вся эта формула помещается внутрь функции =ЕСЛИОШИБКА(). Благодаря ей все ошибочные результаты вычисления будут заменятся на значение 0. Например, если определенный клиент не покупал определенный товар в 2014 году, тогда формула возвращает значение ошибки #ЗНАЧ!. Но благодаря функции ЕСЛИОШИБКА мы просто получим 0.

Шаблон анализа с условным форматированием

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

1. Изменено значение продаж в соответствии с настройками (рост продаж +5% или больше).
2. Изменено значение продаж на меньше установленного роста 5%.

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

1. Выделите диапазон табличной части B2:F11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

2. В появившемся окне «Создание правила форматирования» активируйте опцию «Использовать формулу для определения форматируемых ячеек».

3. В поле ввода вводим формулу:
4. Нажмите на кнопку «Формат», чтобы задать зеленый цвет для шрифта значений ячеек.

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

А в четвертом пункте указываем красный цвет шрифта формата ячеек. Для предварительной проверки откройте «Диспетчере правил». Для этого выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

В результате получаем эффективный конструктор для планирования бюджета продаж. Теперь мы видим, что в ячейке B2 слишком маленькое значение и в ячейке C2 соответственно:

В ячейке H2 можем изменять значения (например, вместо 5% вводим 7%) и после нажатия клавиши Enter сразу получаем новый отчет для анализа.

Скачать шаблон с условным форматированием из сводной таблицы

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