Как сделать дашборд в Гугл Таблице — шаблоны и процесс создания

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

Именно в таких случаях на помощь может прийти визуальный отчет в Google таблицах. Сервис бесплатный, простой в освоении и не требующий глубоких знаний и навыков в IT.

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

Как и в случае с Excel, для dashboard google таблиц можно использовать «Срезы», которые позволят оперативно фильтровать данные по разным категориям, например:

• период отчета (год, месяц);
• отделы;
• менеджеры;
• регионы и так далее.

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

По сравнению с Excel, создание дашбордов в Гугл Таблицах имеет серьезное отличие. Оно заключается в возможности доступа по ссылке всем заинтересованным лицам — ознакомиться с визуализацией можно через обычный браузер.
Тем не менее разработка Microsoft более зрелая, так как находится в работе намного дольше предложения от Google и поэтому имеет несколько преимуществ:

• работает с большим количеством скриптов и макросов;
• удобнее и привычнее для многих пользователей;
• предлагает заметно большее количество вариантов визуализаций.

Конечно, сервис от Google не сможет заменить специальные инструменты для бизнес-аналитики, например, платформу Power BI. Он не приспособлен для профессиональной визуализации и имеет ограниченную функциональность. Тем не менее для небольшого массива данных и простых отчетов вполне может подойти.

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

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

• KPI всех отделов продаж по сумме, выполнению плана, числу сделок и конверсии;
• динамику продаж по разным периодам;
• объемы продаж по всем менеджерам вместе и по каждому в отдельности;
• объемы и доли продаж по регионам;
• эффективность разных каналов реализации.

Для начала создадим новую google таблицу. Как это делать, вы наверняка знаете, но на всякий случай покажем на скриншотах. Присваиваем ей название: в нашем случае это будет «Дашборд 2».

Заранее подготовим 2 листа файла: они пригодятся в процессе.

• «Объединенная таблица» — здесь будем «склеивать» разные Гугл Таблицы.
• «Дашборд» — для макета будущего дашборда: его мы и будем наполнять данными из сводных таблиц.

На вкладке «Сводная таблица 2» у нас будет рабочая зона для формирования показателей к визуализациям. Создавать ее не нужно — она появится автоматически после объединения источников данных, останется только присвоить название.

Для начала нужно определить цели и задачи будущей панели и понять, какие данные она должна содержать. Стоит учитывать, что информация на дашборде располагается определенным образом:

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

Заходим в «Вставить диаграмму». На листе появляется пустой прямоугольник, в котором мы будем помещать графики и другие визуальные элементы — он называется плиткой.

Каждую следующую плитку можно добавлять простым копированием. Вновь созданные элементы легко редактировать по размеру и месту расположения на листе. Линии-подсказки помогут выровнять эти блоки для будущих визуализаций.

При разработке макета учитывайте, что Гугл Таблицы, как и в Excel, «бесконечны»: располагайте плитки так, чтобы они умещались на одном экране и будущему пользователю отчета не приходилось скроллить, чтобы его просмотреть.
Таким образом располагаем все плитки, на которых будут диаграммы — это и есть макет. Не забывайте оставить место для заголовка.

Для создания отчета мы будем использовать показатели отделов продаж двух филиалов компании. Допустим, эти данные располагаются в разных таблицах: разберемся, как объединить их в одном месте.
Итак, у нас есть файлы «Таблица-источник 1» и «Таблица-источник 2» с идентичным содержимым и структурой. В столбцах располагаются:

• номера заказов;
• каналы продаж;
• продуктовые сегменты;
• фамилии менеджеров;
• наименования заказов;
• продавцы;
• города;
• факт;
• план;
• даты заключения сделок;
• даты оплат;
• статусы.

В созданный заранее лист «Объединенная таблица» начинаем подключать наши источники. Делаем это с помощью:

• функций QUERY и IMPORTRANGE;
• оператора консолидации { };
• SQL запроса «select * where Col1 is not null order by Col10».

Вся формула объединения будет выглядеть так:

=QUERY({
IMPORTRANGE("1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk";"Лист1!A1:N");
IMPORTRANGE("1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0";"Лист1!A2:N")};
"select * where Col1 is not null order by Col10")

2. Функция IMPORTRANGE перетащит выбранные нами диапазоны ячеек из каждой таблицы-источника на лист «Объединенная таблица». Происходит это при помощи «ключей» — уникальных наборов символов в ссылке на таблицу-источник.

Каждый «ключ» располагается в URL-адресе между двумя слешами: первый — после буквы «d», второй стоит последним в адресе ссылки. На примере случайной ссылки это будет выглядеть так (ключ зачеркнут красным).

https://drive.google.com/file/d/1wYt815zLgSPhfh5FJbQgttjueoLVMr3H/view

В функции IMPORTRANGE указываем нужный лист из файла-источника: с него и будем тянуть необходимые диапазоны ячеек в «Объединенную таблицу».

Название вкладки (у нас это «Лист1») в формуле IMPORTRANGE указываем в кавычках, а в конце ставим восклицательный знак. Получается следующее:

IMPORTRANGE("1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk";"Лист1!A1:N")

3. Фигурные скобки {... ; … ; ...} — это оператор консолидации, который удерживает перетаскиваемые таблицы вместе. Он также участвует в формуле.

QUERY({
IMPORTRANGE("1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk";"Лист1!A1:N");
IMPORTRANGE("1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0";"Лист1!A2:N")}

4. Запрос SQL — это наборы команд для работы с табличными базами данных.

Сортировку проводим по столбцу 10 — в нем указаны даты оплат.

Вот каким будет процесс объединения источников.

1. На листе «Объединенная таблица» в 1-ой ячейке первого столбца пишем нашу формулу. Для удобства мы разделили ее, чтобы было лучше видно каждую функцию, но всю формулу нужно вписать в одну ячейку.

2. Появились «склеенные» воедино таблицы из разных источников. Между ними нет пропусков и пробелов.

Дашборд google sheets, как и любой другой, нуждается в показателях.

На листе «Объединенная таблица» ставим курсор на свободную ячейку. Переходим в «Вставка» → «Создать сводную таблицу».

Проверяем «Диапазон данных»: как и ранее, указываем первый столбец и его 1-ую строку (без заголовка) и последний столбец (без указания последней строки). Выбираем «Новый лист» — «Создать». Назовем его «Сводная таблица 2» — сюда соберем показатели для первого визуального элемента.

Теперь — выборки данных. На листе со сводной таблицей выделяем ячейку со значением — справа появляется панель для редактирования. Здесь выбираем показатель «Продажи факт, тыс.руб», и он перемещается в выделенную ячейку.

Чтобы рассчитать выполнение плана, нужно разделить «Продажи факт» на «Продажи план». Для этого в сводной таблице копируем ячейку с «фактом» и вставляем рядом. Далее в редакторе («Значения») выбираем второй показатель — «Продажи план, тыс.руб.» — он появляется рядом.

Теперь в следующей ячейке записываем через знак «=» обычную формулу деления. И получаем показатель выполнения плана — остается только подписать полученный результат.

Подобным образом формируем показатель «Конверсия»: число сделок со статусом «Закрыто» делим на общее количество.

Дальше копируем первую сводную таблицу, ставим в свободное место, удаляем в ней старые настройки, выбираем «Значения» → «Продажи факт, тыс.руб.». В поле «Суммировать по» вместо «SUM» кликаем по «COUNT» в списке — это сложит в столбце количество ячеек, которые будут указаны в фильтре. В «Фильтре» отмечаем «Закрыто» и нажимаем «ОК».

Точно так же считаем общее количество сделок и делением получаем конверсию.

В подготовленный заранее макет вставляем данные для карточек KPI:

• прибыль продаж;
• выполнение плана;
• количество сделок;
• конверсия сделок.

Для этого двойным щелчком по нужной плитке открываем справа «Редактор диаграмм»:

• в поле «Тип диаграммы» выбираем столбчатую → «Сводка»;
• указываем ячейку со значением для первой карточки KPI;
• отмечаем адрес ячейки (Сводная таблица 2’!A2) и нажимаем «ОК».

Показатель появился на нужной плитке KPI. Для редактирования единиц измерения встаем на соответствующую ячейку, переходим в меню «Формат» → «Числа» и выбираем, например, российский рубль.

Для дополнительных настроек визуалов дважды щелкаем по нужной плитке — справа появляется поле «Редактор диаграмм», в котором доступны «Настройки» и «Дополнительные».

Так будет выглядеть лист с готовыми карточками KPI.

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

На листе «Сводная таблица 2» копируем ячейки с данными первой карточки KPI и вставляем в любое свободное место на листе. Выбираем нужную ячейку в скопированной сводной таблице. В появившемся справа редакторе удаляем значения предыдущего визуального элемента.

Выбираем для будущего графика «Динамика продаж» строки для оси X и параметр «Значения» для Y. Для корректного вывода дат по оси X сгруппируем их по году и месяцу.

«Итог» в полученной сводной таблице просто удаляем, на визуализации он нам не нужен. Все эти действия проиллюстрированы ниже — просто листайте картинки.

Переходим на лист «Дашборд», двойным щелчком выделяем нужный элемент на макете. В «Редакторе диаграмм» выбираем тип визуализации, указываем адреса ячеек нашей сводной таблицы с соответствующего листа. Выбираем значение для оси X и параметр для оси Y. Получаем диаграмму динамики продаж.

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

Двойным щелчком по любому элементу вызываем «Редактор диаграмм». В поле «Основные значения» доступно редактирование шрифтов для графиков и другие настройки. Для отображения значений находим там же пункт «Серии», ставим отметку «Ярлыки данных», а в поле «Расположение» указываем «В конце столбца».

Аналогично собираем и настраиваем оставшиеся плитки о продажах:

• по менеджерам;
• по регионам;
• по каналам.

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

Данные в dashboard google sheets обновляются автоматически при добавлении новой информации в любую из подключенных таблиц. Покажем на примере, как это происходит.

В «Таблицу-источник 1» добавим еще одну строку. Мы ее просто скопируем, только изменим «Дату сделки», так как фильтр в формуле «QUERY» у нас написан по дате. Новая информация сразу же появится в файле с дашбордом: и на листе «Объединенная таблица», куда и подтягиваются данные из источников, и на визуализациях.

Конечно, дашборд google таблицы — не единственный (и не самый лучший) инструмент для разработки информационной аналитической панели. Вариантов существует множество: они различаются и по своей функциональности, и по назначению, и по стоимости использования.

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

● Google Data Studio — подходящий инструмент для маркетологов, использующих рекламные сервисы Google. Это бесплатный продукт, который имеет достаточные для маленьких компаний возможности визуализации и дополнительных настроек.

● Microsoft Power BI — самая известная система бизнес-аналитики, много лет входит в число лучших в мире. Считается наиболее адаптированной для работы с большими массивами данных.

● Tableau — еще одна популярная BI-платформа с широкими возможностями кастомных визуализаций. Интерфейс интуитивно понятен, разобраться с построением дашборда будет несложно. Это платный инструмент.

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

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