Предположим, поставлена задача построить следующие диаграммы: - показать динамику (по отчетным датам) за 2010-2011гг. сумм общего основного долга USD по каждой из групп просрочки основного долга (ОД) 60+ (т.е. группы 60...90, 91...120, 121...150, и т.д.), рассчитанной по FIFO; - на отчетную дату показать суммы просроченного основного долга USD по группам просрочки ОД FIFO (1..30, 31..60, 61..90, 90+) в разрезах банковских продуктов; Желательно создать единый фильтр (Sliсer) по Центрам финансовых услуг (ЦФУ) и подключить его к обеим диаграммам. В результате должна получиться информационная панель вида:
В Excel 2010 создадим новый лист и выполним подключение к имеющемуся OLAP-кубу. Создадим сводную таблицу, на основе которой затем построим диаграмму № 1. В область фильтров сводной таблицы поместим иерархию "Год-квартал-месяц-дата" измерения "Отчетные даты" (отфильтруем 2010 и 2011 годы), иерархию "Группа 0-60-60плюс" измерения "Группы просрочки ОД FIFO" (отфильтруем элемент "60+"), вспомогательное измерение "_Правила оформления" (вспомогательные измерения - это служебные измерения, не привязанные ни к одной из группе мер).
В область строк сводной таблицы перетянем иерархию "Год-месяц-дата из измерения "Отчетные даты". В область столбцов сводной таблицы поместим иерархию "Группы" измерения "Группы просрочки ОД FIFO", раскроем иерархию до уровня "Группа просрочки" и скроем уровень "Группа 0-30-60-90-90плюс" (находясь на измерении, щелчок правой кнопкой мыши -> в контекстном меню пункт "Показать / скрыть поля"). В область значений сводной таблицы поместим показатель "Общий остаток ОД" из папки показателей "Остатки\Балансовые".
Теперь создадим сводную таблицу для будущей диаграммы № 2. В область фильтров сводной таблицы поместим иерархию "Год-квартал-месяц-дата" измерения "Отчетные даты" (отфильтруем элемент "20.05.2011"), вспомогательное измерение "_Правила оформления". В область строк сводной таблицы перетянем иерархию "Группы продуктов-продукты" измерения "Банковские продукты". В область столбцов сводной таблицы поместим иерархию "Группы" измерения "Группы просрочки ОД FIFO". В область значений сводной таблицы поместим показатель "Остаток просроченного ОД" из папки показателей "Остатки\Балансовые". Для обеих таблиц в контекстном меню (находясь на таблице, щелчок правой кнопкой мыши -> пункт меню "Параметры сводной таблицы…") установим классический макет, запретим автоматическое изменение ширины столбцов при обновлении, а также определим горизонтальное (для экономии места на экране) расположение фильтров отчета строк в 3 полях.
В обеих сводных таблицах задействуем вспомогательное измерение "_Правила оформления", отметив элемент "выделение цветом согласно группам просрочки ОД FIFO", а для первой (слева) сводной таблицы ещё отметим элементы "округление до тысяч сумм показателей", "показывать аббревиатуры округлений сумм". С помощью базового функционала Excel установим формат ячеек всего листа: поменьше шрифт, выравнивание по центру, перенос по словам, границы (рамки) нужных ячеек. Выше над таблицами вставим пустые строки для размещения наших диаграмм, в результате должна получиться следующая картина:
Позиционируем курсор на первой сводной таблице, и для неё создаем график с областями накопления:
Аналогично для сводной таблицы №2 создаем гистограмму с накоплением:
В каждой диаграмме: - вставим подписи вертикальных осей: главное меню "Макет" -> "Название осей" -> "Название основной вертикальной оси" -> "Повернутое название" (см. предыдущий пример создания графических отчётов); - вставим заголовки диаграммы: главное меню "Макет" -> "Название диаграммы" -> "Название по центру с перекрытием"; - главное меню "Макет" -> "Легенда" -> "Добавить легенду снизу" - легенда будет размещена внизу под графиком (затем легенду можно отбуксировать в желаемое место).
Между таблицами вставим пустые колонки про запас и скроем их на тот случай, если в левой таблице увеличится количество столбцов, она не будет перекрывать правую таблицу. Чтобы зафиксировать размеры и позиции диаграмм, воспользуемся контекстным меню (щелчок правой кнопкой мыши на границе диаграммы) -> "Формат области диаграммы…" -> "Свойства" -> "Не перемещать и не изменять размеры".
Группы просрочки целесообразнее упорядочить в обратном порядке, поскольку: - будет видно, какой объем составляют вместе, например, просрочка 360 и 360+ ; - при прямом порядке сортировки в случае роста объема наихудшей просрочки соответствующая ей полоса графика будет расширяться, но падать вниз, при обратном порядке сортировки зрительное восприятие графика улучшается.
Воспользуемся следующим приемом: выделим какой-либо элемент уровня "Группа просрочки", поскольку это ключевой атрибут измерения, то у него есть свойства, среди которых посредством контекстного меню выберем для отображения "Начало диапазона дней"; затем выделим любой элемент свойства и отсортируем колонки в обратном порядке (сортировка от Я до А); используя базовый функционал Excel - формат ячеек, "спрячем" элементы свойства – установим белый шрифт на белом фоне.
Упорядочивание можно сделать проще: щелчок правой кнопкой мыши на группе просрочки -> в контекстном меню выбираем "Сортировка" -> "Дополнительные параметры сортировки…" -> "Вручную" -> затем вручную поменять порядок столбцов, захватывая и перетаскивая ячейку заголовка каждой колонки.
Правее графиков вставим срезы: главное меню "Вставка" -> "Срез" -> выбираем атрибут "ЦФУ" иерархии "Подразделения - ЦФУ" измерения "ЦФУ". Аналогично вставим срез по атрибуту "Валюта" измерения "_Валюты конверсии". Свяжем наши срезы со сводными таблицами: выделяем срез -> щелчок правой кнопкой мыши -> в контекстном меню выбираем пункт "Подключения к сводной таблице…" -> в диалоговом окне отмечаем все сводные таблицы -> нажимаем кнопку "OK". Настроим оформление срезов. Выделим срез "Валюта эквивалента" -> в главном меню выбираем "Параметры" -> определяем количество столбцов в панели среза, высоту и ширину строк. Через пункт контекстного меню среза "Настройка среза…" переопределяем его заголовок в "Валюта эквивалента".
Итак, мы создали информационную панель, включив в неё два диаграммы схожей тематики. При выборе элементов срезов автоматически исполняются запросы к OLAP-серверу, после чего будут обновлены все связанные сводные таблицы и графики.
Важно не только уметь правильно извлечь и подготовить данные, но наглядно их визуализировать. Построение графиков, и тем более информационных панелей (Dashboard) требует навыков, опыта. Есть отдельные книги, посвященные данной тематике.
Остановимся на некоторых моментах:
- в зависимости от задачи и структуры данных следует выбирать наиболее подходящий вид графика. Так, говоря о динамике и структуре нескольких категорий, оптимален тип графика "с областями и накоплением"; говоря о сравнительном анализе многих категорий (например, филиалы) и их структуре (например, группы просрочки по кредитам) – столбцевая гистограмма с накоплением;
- диаграммы должны иметь содержательные заголовки, отражающие цель графика, оси координат - подписи; в заголовках не нужно констатировать / дублировать текст подписей осей;
- для слишком больших числовых значений следует использовать округление, например, до тысяч, миллионов, не забывая об отображении аббревиатур округлений;
- использовать единую цветовую палитру (в приоритете – корпоративно принятую), цвет тоже может нести важную смысловую нагрузку, визуализировать дополнительное измерение;
- информационные панели должны оформляться так, чтобы была сведена к минимуму необходимость прокрутки экрана по горизонтали / вертикали (оптимально – обзор панели одним взглядом), для чего задействовать штатные средства форматирования Excel (размер шрифта, высоту строк, ширину столбцов, перенос слов и т.д.);
- необходимо учитывать зрительную особенность человека: самая воспринимая область – левый верхний квадрант экрана -> здесь должна размещаться наиболее важная информация, наименее воспринимая область – правый нижний квадрант экрана;
- диаграммы не должны быть перегружены излишними вспомогательными метками (например, элементами фильтров).