13 подписчиков

[EXCEL] #2. Dashboard без VBA

29 февраля 202029 фев 2020

5 мин

Оглавление

Подключение базы данных на основе Excel файла
Подключение
Создание Gauge ("спидометр") диаграммы

В этой статье мы рассмотрим:

Подключение базы данных на основе Excel файла;
Создание Gauge ("спидометр") диаграммы.

Подключение базы данных на основе Excel файла

Как я писал ранее, Excel поддерживает много форматов баз данных, таким форматом является и сам Excel.

Для использования в качестве базы данных Excel необходимо знать следующее:

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

2. Возможно некорректное определение типа данных. Из-за данной проблемы Excel определяет числовые данные как текст, в связи с этим какие-либо математические действия становятся недоступными. На практике встречался с данной проблемой очень часто, поэтому советую использовать в качестве базы данных MS Access или SQL.

Пример некорректного определения типа данных

3. Excel база данных должна быть открыта другим пользователем или настроен многопользовательский доступ.

Подключение

Для того, чтобы подключить внешний источник данных необходимо:

Открываем "Мастер сводных таблиц и диаграмм".
На шаге 1 указываем пункты: "во внешнем источнике данных" и "сводная таблица".
На шаге 2 нажимаем "Получить данные...".
В следующем окне выбираем тип соединения "Excel Files*".
В появившемся окне указываем путь до вашей базы данных.
Выбираем нужную таблицу и столбцы для импорта.
Пункт "отбор данных" можно пропустить.
В пункте "порядок сортировки" указываем, по какому из столбцов необходимо сортировать данные; если этого делать не нужно, пропускаем.
В пункте "заключительный шаг" выбираем "Вернуть данные в Microsoft Excel".
Нажимаем "Готово".
После подключения базы данных к файлу Excel, вас вернет к шагу 2, и напротив кнопки "Получить данные..." будет написано "Данные получены".
Переходим на следующий шаг и выбираем место создания сводной таблицы.

Создание Gauge ("спидометр") диаграммы

Gauge ("спидометр") диаграммы представляют из себя две круговые диаграммы расположенные друг на друге.

Для начала нужно создать таблицу с данными для диаграммы, отображающей сектора (зоны, деления) нашего "спидометра", и таблицу с данными для диаграммы, отображающей стрелку.

Данные для диаграмм будем брать из следующих таблиц:

Сектора диаграммы можно задать фиксировано, но т.к. мы создаем Dashboard сектора должны быть динамическими. Так же можно сделать комбинированный вариант:

Красный сектор (будет задана фиксировано) = 5

Желтый сектор (среднее кол-во продаж) = ОКРУГЛВВЕРХ(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$K$2;"Товар";DATA_Gauge!$B$3);0)-B3

Зеленый сектор (максимальное кол-во продаж) = ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$D$2;"Товар";DATA_Gauge!$B$3)-B4-B3

Скрытый сектор = B5+B4+B3

Положение стрелки = ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$A$2;"Дата";DATA_Gauge!$A$4;"Товар"; DATA_Gauge!$B$3)

Толщина стрелки = 0

Скрытый сектор = (2*B6)-B8

Далее разберем формулы.

Желтый сектор (среднее кол-во продаж)

ОКРУГЛВВЕРХ(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$K$2;"Товар";DATA_Gauge!$B$3);0)-B3

ОКРУГЛВВЕРХ () - округление числа до ближайшего большего по модулю.

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1; поле2; элем2]; …) - функция GetPivotData возвращает видимые данные из сводной таблицы.

В нашем случае:

поле_данных - "Кол-во продаж" ;
сводная таблица - SR Продаж;
поле "Товар" - ссылка на заголовок столбца таблицы "Сумма продаж" (данная ссылка позволяет динамически менять наименование товара в функции получения данных);
вычитаем значение красного сектора (-B3), т.к. сектор в диаграмме начинается с места окончания предыдущего сектора.

Зеленый сектор (максимальное кол-во продаж)

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$D$2;"Товар";DATA_Gauge!$B$3)-B4-B3

В нашем случае:

поле_данных - "Кол-во продаж" ;
сводная таблица - MAX Продаж;
поле "Товар" - ссылка на заголовок столбца таблицы "Сумма продаж" (данная ссылка позволяет динамически менять наименование товара в функции получения данных);
так же вычитаем значение красного и желтого секторов (-B4-B3).

Скрытый сектор

B5+B4+B3

Скрытый сектор диаграммы равен сумме видимых секторов.

Положение стрелки

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Кол-во продаж";DATA_Gauge!$A$2;"Дата";DATA_Gauge!$A$4;"Товар"; DATA_Gauge!$B$3)

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1; поле2; элем2]; …) - функция GetPivotData возвращает видимые данные из сводной таблицы.

В нашем случае:

поле_данных - "Кол-во продаж" ;
сводная таблица - Сумма продаж;
поле "Дата" - ссылка на первую строку столбца Дата таблицы "Сумма продаж" (данная ссылка позволяет динамически менять дату в функции получения данных );
поле "Товар" - ссылка на заголовок столбца таблицы "Сумма продаж" (данная ссылка позволяет динамически менять наименование товара в функции получения данных).

Вставляем кольцевую диаграмму через меню "Вставка" -> "Диаграммы".

Открываем окно выбора данных для диаграмм через меню "Работа с диаграммами"

Добавим два ряда:

После добавления данных для диаграммы нажимаем "ОК". Получаем следующую диаграмму:

Кольцевая диаграмма с двумя рядами данных

Нажимаем ПКМ на диаграмме и в появившемся меню выбираем "Изменить тип диаграммы...". Выбираем раздел "Комбинированная" и для каждого ряда данных выбираем "Кольцевая". Галочку "Вспомогательная ось" переключаем на ряд "Стрелка".

В получившейся диаграмме выделяем кольцо и нажимаем ПКМ. Выбираем пункт "Формат ряда данных".

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

Тоже самое делаем и для второго ряда, чтобы обе диаграммы были расположены одинаково.

В результате должна получиться такая диаграмма:

Теперь выделяем круг диаграммы и, нажимая клавиши "стрелки", перемещаемся по секторам диаграмм.

Для ряда "Стрелка" (первая диаграмма) необходимо скрыть 1 и 3 сектора. Для ряда "Сектора" (вторая диаграмма) необходимо скрыть только 4 сектор.

Первый сектор в первой диаграмме (Имеет синий цвет в цветовой схеме "Office 2007-2010")

Отключаем заливку первого сектора первой диаграммы

Отключаем границы первого сектора первой диаграммы

Теперь настраиваем вид стрелки. Находим ряд "Стрелка" и выделяем 2 сектор. Отключаем заливку. Граница: "Сплошная линия", "Ширина: 2 пт". Переходим в "Параметры ряда" и изменяем параметр "Диаметр отверстия, в % от общего диаметра" на 0%.

Если сделали все правильно, то получите следующую диаграмму ("спидометр"):

Отключаем легенду диаграммы и включаем название.

Название диаграммы сделаем динамическим. Для этого в ячейке A1 пропишем следующую формулу:

=СЦЕПИТЬ("Подажи товара: ";DATA_Gauge!$B$3)

DATA_Gauge!$B$3 - ссылка на заголовок столбца таблицы "Сумма продаж".

Выделяем название диаграммы. Переходим в строку формул и ставим символ "=", выделяем ячейку А1. Нажимаем Enter.

Теперь можно изменить цвета секторов диаграммы в соответствии с требуемыми (в моем случае: красный, жёлты, зелёный).

Готово! Получилась Gauge диаграмма с динамическим получением данных.

В следующей статье рассмотрим элементы управления: Поле со списком, Флажок, Счетчик, Список, Переключатель, Полоса прокрутки.