Карьерный рост аналитика. Статья Алексея Колоколова

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

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

Для чего это нужно

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

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

Как работает такой дашборд

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

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

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

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

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

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

Интерактивные отчеты за 3 часа

Быстрый и удобный анализ данных

Красивые и понятные аналитические отчеты за 1 месяц

Дашборд в Power BI с нуля до профи
ОНЛАЙН-КУРС

Как создать дашборд в Гугл Таблицах

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

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

• KPI всех отделов продаж по сумме, выполнению плана, числу сделок и конверсии;
• динамику продаж по разным периодам;
• объемы продаж по всем менеджерам вместе и по каждому в отдельности;
• объемы и доли продаж по регионам;
• эффективность разных каналов реализации.
Анализ
Пример дашборда в Гугл Таблице
Для начала создадим новую google таблицу. Как это делать, вы наверняка знаете, но на всякий случай покажем на скриншотах. Присваиваем ей название: в нашем случае это будет «Дашборд 2».
Заранее подготовим 2 листа файла: они пригодятся в процессе.

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

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

Создаем макет

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

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

Заходим в «Вставить диаграмму». На листе появляется пустой прямоугольник, в котором мы будем помещать графики и другие визуальные элементы — он называется плиткой.
Дашборд2
Каждую следующую плитку можно добавлять простым копированием. Вновь созданные элементы легко редактировать по размеру и месту расположения на листе. Линии-подсказки помогут выровнять эти блоки для будущих визуализаций.
Лист1
При разработке макета учитывайте, что Гугл Таблицы, как и в Excel, «бесконечны»: располагайте плитки так, чтобы они умещались на одном экране и будущему пользователю отчета не приходилось скроллить, чтобы его просмотреть.
Таким образом располагаем все плитки, на которых будут диаграммы — это и есть макет. Не забывайте оставить место для заголовка.
Лист2

Подключаем данные из разных Гугл Таблиц

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

• номера заказов;
• каналы продаж;
• продуктовые сегменты;
• фамилии менеджеров;
• наименования заказов;
• продавцы;
• города;
• факт;
• план;
• даты заключения сделок;
• даты оплат;
• статусы.
Перед объединением информации из разных таблиц необходимо привести данные к нужному виду, подходящему для автоматической обработки. В нашем случае количество столбцов и их наименования должны совпадать: только так на готовом дашборде мы сможем сравнивать нужные показатели.
Лайфхаки Excel для создания дашбордов

Работа с Power Query и Power Pivot

Быстрая подготовка данных для отчета

Результат за 4 дня: из грязи — в Excel-князи

Обработка данных в Excel
ОНЛАЙН-КУРС
В созданный заранее лист «Объединенная таблица» начинаем подключать наши источники. Делаем это с помощью:

• функций 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")
  1. QUERY выполняет SQL запросы. Ниже указан ее синтаксис. Эта функция позволит нам «склеить» таблицы из разных источников на одном листе, разместить их друг под другом без пропусков и пустых строк между ними.
Пример1
2. Функция IMPORTRANGE перетащит выбранные нами диапазоны ячеек из каждой таблицы-источника на лист «Объединенная таблица». Происходит это при помощи «ключей» — уникальных наборов символов в ссылке на таблицу-источник.
Пример2
Каждый «ключ» располагается в 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-ой ячейке первого столбца пишем нашу формулу. Для удобства мы разделили ее, чтобы было лучше видно каждую функцию, но всю формулу нужно вписать в одну ячейку.
=QUERY({
IMPORTRANGE("1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk";"Лист1!A1:N");
IMPORTRANGE("1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0";"Лист1!A2:N")};
"select * where Col1 is not null order by Col10")
Вставка
2. Появились «склеенные» воедино таблицы из разных источников. Между ними нет пропусков и пробелов.
Данные

Создаем сводную таблицу и делаем выборки данных

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

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

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

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

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

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

Добавляем карточки KPI

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

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

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

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

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

грамотная визуализация любых данных

наглядные отчеты для вашего руководства

Интерактивные отчеты, которые понравятся директору

Дашборд в Excel с нуля
ОНЛАЙН-КУРС

Создаем диаграммы

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

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

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

Настраиваем диаграммы

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

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

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

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

Как настроить обновление данных

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

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

Чего избегать при разработке дашборда в Гугл Таблицах

Какие еще сервисы можно использовать для наглядных отчетов

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

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

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

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

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

Подведем итоги

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

По итогу проделанной работы, можно сказать: создание дашбордов в Гугл Таблицах под силу большинству людей со средним знанием табличных функций и электронных ячеек.
Вам понравилась статья?
Если вы хотите получать новости и полезные материалы Института бизнес-аналитики первыми, на подпишитесь на рассылку в форме ниже. Надеюсь, статья была вам полезной и подарила парочку инсайтов.
Читайте также
Подпишись на рассылку и получи в подарок «Каталог лучших отраслевых дашбордов»!

Хочешь получать актуальные статьи о визуализации данных?