Каждый менеджер, бухгалтер или даже самый обычный пользователь не раз сталкивался с великолепными возможностями Excel 2010, позволяющей быстро и удобно работать с таблицами. Данная версия программы отличается расширенными возможностями условного форматирования сводных таблиц, что улучшает весь рабочий процесс.
Мы имеем функционал визуализации, предоставляющий возможность использовать в ячейках гистограммы, разнообразные значки и всевозможные цветовые шкалы. Теперь можно легко создать окошко в стиле управляющей консоли, что облегчает поиск информации. Также теперь этот тип форматирования начал успешно применятся в сводных таблицах, точнее говоря – к их структуре (а не только к данным). Описанный ниже способ подойдет даже тем, кто просто собирается купить новую стиральную машинку и выбирает модель.
Приведем простейший пример сводной таблицы, описывающий объемы продаж какого-то товара по регионам:
Теперь, опираясь на эти данные, создадим графический отчет объемов продаж за каждый временной период, чтобы облегчить восприятие информации. Естественно, можно воспользоваться сводной диаграммой, но хорошо подходит и условное форматирование в Excel 2010.
Самый простой вариант – использовать цветовые шкалы. Для этого выделяем поле «Объем продаж», охватывая все периоды. Осталось открыть вкладку «Главная», где нажимаем кнопку «Условное форматирование» (если вы вдруг используете английскую версию, то данная функция называется «Conditional Formatting»). Наведите курсор на «Гистограммы».
Имеющийся набор градиентной и сплошной заливки, по сути своей, напоминает стандартную горизонтальную гистограмму. И если сейчас использовать фильтрацию данных любой графы в пределах области действия фильтра, то мы заметим динамическое изменение гистограмм.
Excel 2010 уже имеет значительное количество полностью готовых сценариев форматирования, значительно экономящих рабочее время. Для создания описанных выше гистограмм, кстати, программа применяет алгоритм, самостоятельно определяющий максимальное и минимальное значения диапазона выбранных ячеек и только потом форматирует их, опираясь на обработанную информацию.
Итак – готовые сценарии, способные помочь в большинстве ситуаций:
— первые 10 элементов;
— последние 10 элементов;
— первые 10%;
— последние 10%;
— больше среднего;
— меньше среднего.
Удаление уже используемого условного форматирования в Excel 2010 происходит по следующей схеме: в сводной таблице переходим на вкладку «Главная», нажимаем на «Условное форматирование», далее «Стили», и в выпадающем меню используем команду «Удалить правила» — «Удалить правила из этой сводной таблицы» (в английском варианте – «Clear Rules» и «Clear Rules from this PivotTable»).
Впрочем, можно легко создавать собственные правила форматирования.
Данная таблица – усложненный вариант первой, так что сразу переходим к примеру. Давайте отследим объем продаж и выручку за час. Мы будем использовать условное форматирование Excel 2010 для ускорения поиска совпадений и различий. Выделяем «Объем продаж». Далее по стандартной процедуре активируем сценарий («Главная» — «Условное форматирование»), но выбираем не готовый вариант, а функцию «Создать правило» (или «New Rule»).
Именно здесь можно определить ячейки, на которые будете применяться условное форматирование в Excel 2010, тип используемого правила и, собственно, параметры форматирования. Сначала задаются ячейки, и здесь есть достаточно простой выбор:
— выделенные («Selected Cells»);
— входящие в столбец «Объем продаж» («All Cells Showing «Sales_Amount» Values»), включая промежуточные и общие итоги. Данный вариант, кстати, хорошо подходит для анализа тех данных, которые требуют определения среднего, процентного соотношения или иными величинами, так или иначе являющимися разными уровнями одной величины;
— входящие в категорию «Объем продаж» только для «Рынка сбыта» («All Cells Showing «Sales_Amount» Values for «Market»»). Данный вариант полностью исключает общие и промежуточные итоги, что удобно для анализа некоторых отдельных значений.
Отметим, что команды «Объем продаж», «Рынок сбыта» при создании правил меняются в зависимости от имеющихся рабочих таблиц.
По нашему примеру самым выгодным является вариант «3», поэтому используется такой вариант:
При выборе правила (раздел «Выбрать правило» или «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»).
В списке «Стиля значка» остается выбрать значение «3 знака» Это хорошо подойдет, если имеющуюся таблицу невозможно полностью раскрасить. В результате в окошке у нас должно получиться следующее:
При такой конфигурации Excel самостоятельно будет добавлять в ячейки значки, при этом следуя функции:
>=67, >=33 и <33.
Это позволит распределить значки сразу по 3 категориям, что визуально облегчит получение информации при работе с таблицей.
Добавим, что в каждом случае граничные значения могут легко изменяться, а в примере просто использованы значения «по умолчанию».
Осталось только применить наши правила к таблице. В результате она изменится вот так:
Осталось применить это же правило к столбцу «Выручка за час».
Но не спешите сразу же закрывать пример – лучше его проанализировать. Ведь любой более-менее опытный менеджер сразу же заметит взаимосвязь между выручкой за час и общим доходом. Например, отвечающий за рынок сбыта в Далласе определит, что у него объем продаж является минимальным, хоть выручка за час и является самой большой. Это позволяет прийти к выводу, что для его рынка выручка за час слишком высока.
У его коллеги из Нью-Йорка, между тем, ситуация иная. При его высоком общем уровне дохода часовая выручка является минимальной. И это уже является проблемой, несмотря на большой отрыв в плане общего дохода.
Для чего было последнее пояснение? Просто для наглядного примера, насколько условное форматирование в Excel 2010 облегчает восприятие разной информации. Обладая необходимыми навыками в создании фильтров форматирования, можно быстро получать необходимую информацию.