Интерактивная панель управленческих показателей компании в Excel

ПОСТАНОВКА ЗАДАЧИ

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

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

Наша задача — построить интерактивную панель главных управленческих показателей для компании, которая производит обувь и кожгалантерейные изделия (рис. 1).

Поставленную задачу решают в четыре этапа.

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

Этап 2. Прорабатывают алгоритмы трансляции данных из источников данных. В нашем случае сделаем это с помощью надстройки Excel Power Query.

Этап 3. На основании источников данных строят сводные таблицы. В статье рассмотрим именно такой вариант решения задачи — воспользуемся инструментом сводных таблиц и сводных диаграмм Excel. Это обеспечит обновление данных на интерактивной панели автоматизированно при изменении данных в подключенных источниках. Альтернативным решением может быть использование 1С, Power BI и других платформ.

Этап 4. На основании сводных таблиц на отдельном листе Excel с помощью вставки диаграмм создают интерактивную панель.

ИСТОЧНИКИ ДАННЫХ

Рассмотрим три источника данных для панели. Первый источник — таблица с показателями непосредственно в файле Excel, где будем настраивать дашборд. Как правило, такие таблицы небольшие (табл. 1).

Важное условие: таблица должна быть пригодной для построения сводной таблицы. Оптимально, если таблица плоская. Поэтому в табл. 1 (рис. 2) предусмотрены настройки:

• столбец «Аналитика» позволит сформировать сводную таблицу по группе (Расходы 1 уровень) или статье расходов (Расходы 2 уровень);
• графа «Группа» заполнена полностью. Для лаконичного представления информации лишние данные в ячейках скрыты белым шрифтом;
• вспомогательная графа (столбец Е) содержит сцепку данных: =A2&" "&C2. Это позволит сделать сортировку данных по порядку.

На основе табл. 1 формируют сводные таблицы с данными, которые интересуют руководство:

• расшифровка коммерческих расходов по статьям затрат. Сумма значительная — 4000 тыс. руб. за первый квартал;
• выполнение плана завоза основных материалов (натуральные кожи, экокожа, фурнитура) в стоимостном выражении за март. Общая сумма фактических расходов за квартал — 9730 тыс. руб.;
• план-факт по группам расходов укрупненно. Планировали 33 221 тыс. руб., фактические расходы составили 34 630 тыс. руб. Руководству важно показать виновников такого роста.

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

Таблица 2 достаточно усеченная, содержит только ключевые параметры. Если в источнике данных есть другие столбцы (например, наименование продукции, контрагент, менеджер, тип договора), их также можно использовать для построения интерактивной панели (при условии, что эти аналитики интересуют руководство).

Таблица 2 отформатирована как «умная», ей присвоено имя «ДЗ». Для решения задачи нам потребуется версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010–2013 с установленной бесплатной надстройкой Power Query от Microsoft.

Открываем вкладку Power Query (на примере Excel 2010) → Получение внешних данных → Из файла → Из Excel (рис. 3). В появившемся окне Навигатор (рис. 4) в левой части выбираем требуемый лист (в нашем случае это «ДЗ») и нажимаем кнопку «Изменить». Важная деталь: если нажать кнопку «Загрузить» (см. рис. 4), таблица сразу будет импортирована на лист в исходном виде, что не всегда удобно.

После нажатия кнопки «Изменить» в отдельном окне отобразится редактор запросов Power Query с данными из книги. Это позволит преобразовать таблицу в нужный вид. Функции Power Query позволяют:

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

Преобразуем дату погашения (см. табл. 2) из формата 01.02.2019 в месяц «Февраль» (рис. 5).

После завершения преобразований данные формируем в запрос. На вкладке Главная раскрываем пункт «Закрыть и загрузить». В появившемся окне «Загрузить в» выбираем «Только создать подключение» (рис. 6). Мы сформировали запрос, который на следующем этапе используем для создания сводных таблиц.

Рассмотрим третий источник данных — папку с файлами. Действуем аналогично. Открываем вкладку Power Query → Получение внешних данных → Из файла → Из папки (см. рис. 3). Используем папку «Дистрибьюторы». В этой папке три файла с идентичными таблицами (это важно!) по трем разным компаниям группы.