Построение дaшборда в табличном процессоре MS Excel

Семинар: Построение дaшборда в табличном процессоре MS Excel

Задача на семинар: Необходимо сформировать аналитический отчет, которые отражает:

1. Динамику продаж

2. Долю филиалов в общих продажах

3. Суммарные продажи по товарам

4. Суммарные продажи по городам

5. Коэффициенты KPI

В исходном файле «Стройматериалы.xslx» лист «Данные» представлена статистика продаж. Необходимо проанализировать данные и построить дашборд.

Необходимо создать дополнительный лист «Дашборд», куда необходимо будет расположить все созданные объекты согласно задаче.

Каждую новую диаграмму/срез/временную шкалу/визуальные кнопки мы будем создавать на отдельном листе.

Динамика продаж:

1. Создать лист «Динамика продаж»

2. Вставить сводную таблицу с названием «Динамика продаж» со следующими полями (настройки полей ниже): «Вставка – Таблицы – Сводная таблица». Разместить на листе «Динамика продаж». Диаграмма, которая отражает динамику продаж должна быть построена в тыс.руб.

3. После настройки сводной таблицы, необходимо разместить сводную диаграмму.

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

«Вставка – Диаграммы – Сводная диаграмма – График с маркерами».

Рейтинг продаж товаров

1. Создать лист «Товары»

2. Вставить сводную таблицу «Товары» со следующими полями (настройки полей ниже): «Вставка – Таблицы – Сводная таблица». Разместить на листе «Товары»

Диаграмма, которая отражает рейтинг товаров должна быть построена в тыс.руб.

3. После настройки сводной таблицы, необходимо разместить сводную диаграмму.

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

«Вставка – Диаграммы – Сводная диаграмма – Линейчатая с группировкой». Разместить на листе «Товары».

Доля продаж по филиалам

1. Создать лист «Филиалы»

2. Вставить сводную таблицу «Филиалы» со следующими полями (настройки полей ниже): «Вставка – Таблицы – Сводная таблица». Разместить на листе «Филиалы»

Диаграмма, которая отражает доли филиалов должна быть построена в %.

3. После настройки сводной таблицы, необходимо разместить сводную диаграмму.

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

«Вставка – Диаграммы – Сводная диаграмма – Круговая диаграмма». Разместить на листе «По складу».

Коэффициенты KPI

1. Создать лист «KPI»

2. Разместить три визуальные кнопки на листе:

- Продажи (млн.руб)

- Средний чек (млн.руб)

- Количество продаж

3. Построить сводную таблицу «KPI».

4. Перевести руб. в тыс. руб.

5. Создать визуальные кнопки

Разместить фигуру прямоугольник и сделать ссылку на определенное значение.

Продажи по городам

1. Создать лист «Карта»

2. Построить сводную таблицу «Карта».

3. Создать таблицу следующего вида:

В столбце «Города» находится список уникальных городов, «X» и «Y» - координаты указанных городов на карте Подмосковья (заполним при построении диаграммы). Столбец «Сумма» заполняется с помощью функции ВПР() с последующей обработкой ошибок:

=ЕСЛИОШИБКА(ВПР(D2;A:B;2;0);0), D2- строка с конкретным городом, A:B – сводная таблица, из которой выводится сумма (второй столбец). Столбец «Подписи» содержит следующую формулу: =ТЕКСТ(D2;0)&" "&ТЕКСТ(G2;"# ##0"), она добавляет название города из таблицы, ставит пробел и выводит сумму продаж в этом городе в числовом формате с разделителями разрядов без знаков после запятой.

4. Вставить пузырьковую диаграмму со следующими настройками:

5. Вставить в качестве фона для диаграммы карту Московской области, задать значения осей координат от 0 до 100, после чего подобрать положение городов.

6. Закончить построение диаграммы: добавить подписи данных и название, убрать оси координат и сетку.

Вставка фильтров

Срез – это интерактивный элемент управления, который упрощает фильтрацию данных в сводной таблице (или сводной диаграмме).

Необходимо разместить два фильтра (продавцы, товары) и одну временную шкалу. («Вставка – Фильтры – Срез») на листе «Дашборд», а также настроить подключения ко всем сводным таблицам.

Все настройки дизайна и подключения к отчетам расположены на вкладке «Инструменты для среза – Параметры»

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

Сборка дашборда

На конечном этапе все скопированные элементы необходимо собрать в единую группу объектов, т.е. сформировать аналитический отчет.