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

Что такое Google Data Studio?

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

Основная функциональность функции Google Data Studio состоит из:

• Подсоединение к данным (практически любые источники)
• Визуализация данных (составление отчета)
• Организация доступа пользователей к отчету (с различными правами)

Преимущества Google Data Studio: бесплатная онлайн платформа, популярность, простота. Google Data Studio относится к BI системам (Business Intelligence) – это набор инструментов и программ для бизнеса, которые собирают данные из разных источников, анализируют их и представляют в наглядном виде. Конечно, анализирует данные не BI система и даже не Google Data Studio, а сам разработчик отчета.

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

Google Data Studio или MS EXCEL?

Если вашему клиенту или начальнику требуется наглядное представление в виде диаграмм рутинных, постоянно обновляющихся данных, поступающих с сайта, онлайн магазина или от сотрудников, клиентов, то пора переходить на BI систему. Если же вашему клиенту или начальнику нужны расчетные модели, статистический анализ проблемы или другая подобная аналитика, то Ваш выбор - EXCEL.

Чтобы извлечь пользу из Google Data Studio вам необходимо:

1. четко формулировать идею, которую вы хотите донести до аудитории при помощи отчета (диаграммы или дашборда). Обратите внимание, что непродуманные идеи порождают плохие отчеты и делают вас невостребованным разработчиком;
2. иметь опыт работы с различными источниками данных (таблицы, базы данных, данные Google Analytics, информация с сайтов и др.);
3. работать с интерфейсом Google Data Studio

Продвинуться по первому вопросу вам поможет книга Джина Желязны «Говори на языке диаграмм», со вторым – опыт работы с таблицами MS EXCEL.

С третьим вопросом вам, надеюсь поможет эта статья. В ходе изучения функционала Google Data Studio будем постоянно проводить параллели с MS EXCEL, в котором можно реализовать большинство отчетов наподобие тех, которые создаются в Google Data Studio.

Как будем изучать

Изобретать велосипед не будем – в сети множество бесплатных видео обзоров Google Data Studio для начинающих, которые можно использовать для изучения. Я выбрал серию из 8 уроков (частей) общей длительностью почти 2 часа от одной молодой, но толковой малазийки, которая структурированно и без пространных отступлений рассказывает о том, как сделать свой первый (и вполне приличный) отчет. Именно он и представлен на картинке выше.

Детально пересказывать видеоурок не буду, но в ходе его изучения предлагаю вам периодически прерываться и разбирать с помощью этой статьи особенности Google Data Studio (GDS) строя диаграмму в EXCEL 2016. Это поможет глубже разобраться как работает GDS путем сравнения с темой, в которой вы неплохо разбираетесь (имеется в виду, что нужно неплохо знать EXCEL, чтобы материал статьи был для вас полезен).

Фактически данная статья является дополнительным материалом к этому видеоуроку.

Получаем доступ к Google Data Studio

Для доступа Google Data Studio потребуется аккаунт Google. Для тех у кого его нет - нужно будет создать. Как это сделать - показано в https://support.google.com/accounts/answer/27441?hl=ru. Аккаунт дает доступ к множеству бесплатных сервисов Google, в том числе к Google Sheets (онлайн таблицы, которые во многом аналогичны EXCEL). Нам они тоже потребуются - через них мы будем подключаться к данным.

Ссылка на видеоурок https://www.youtube.com/watch?v=dsAVSsdYC10

Ссылка на отчет (только чтение, разумеется) https://lookerstudio.google.com/reporting/e8cb3269-a366-421c-845e-066b7a8be05d/page/5jajC

Ссылка на источник данных https://docs.google.com/spreadsheets/d/1J_zy0nX46BsvwNL4nsVpdYlOriYjxsGU8UpSQdKsioU/copy

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

Эта таблица состоит из 69 записей о продажах различных товаров (столбец SKU) определенным Покупателям (столбец Buyer) по заданной цене (столбец Price). При этом фиксируется дата продажи, дата оплаты и местоположение Покупателя. Таблицу можно выгружать из Google таблиц, она является файлом примера xlsx к данной статье (кнопка для скачивания внизу и вверху статьи). В этом файле приведены примеры диаграмм EXCEL, аналогичных диаграммам созданных в видеоуроке в среде Google Data Studio (GDS).

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

Часть 1 (Chapter 1). Знакомство с Google Data Studio (GDS)

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

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

Часть 2 (Chapter 2). Подключаемся к данным

Надеюсь, что сильный акцент учительницы – малазийки не помешал вам дойти до 2-й части урока 00:07:18 https://www.youtube.com/watch?v=dsAVSsdYC10&t=438s , где она шустро рассказывает про подключение к данным, в нашем случае – к таблице Google.

Если вы уже кликнули по ссылке на источник данных для этого видео урока https://docs.google.com/spreadsheets/d/1J_zy0nX46BsvwNL4nsVpdYlOriYjxsGU8UpSQdKsioU/copy , то в таблицах Google уже появился соответствующий файл:

Сами данные, с которыми мы будем работать находятся на 3-м листе Data.

Зайдите в GDS https://lookerstudio.google.com

Создайте пустой отчет (см. видеоурок в 00:07:28 или нажмите кнопку Create с плюсиком и потом нажмите Report).

Присоедините к отчету источник данных (см. видеоурок в 00:11:28), выбрав Google Sheets.

Авторизуйтесь в Google Sheets (Google Таблицах), нажав Authorize.

Сразу загрузится информация обо всех ваших Google таблицах, выберите наш файл источник и его лист Data.

Нажмите кнопку Add и GDS самостоятельно создаст в отчете новую таблицу и заполнит ее данными из таблицы (возьмет наиболее подходящие, по его мнению, данные).

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

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

Часть 3.1 Круговая диаграмма (Pie chart)

В 26:30 малазийка создаст первую диаграмму – ее выбор падет на круговую диаграмму (Pie Chart).

После добавления диаграммы GDS автоматически заполнит ее данными.

В качестве категории диаграммы (Dimensions, по-русски – Параметр, еще называют Категория) GDS выбрала текстовый столбец Buyer с кодами Покупателей (в столбце они повторяются), а в качестве значений (Metrics, по-русски - Показатель) GDS выбрала количество повторов этих кодов. Хотя уникальных кодов Покупателей много (30), GDS отобразила только 9 из них, остальные просуммировала и обозвала Others.

Чтобы понять, как эта диаграмма заполнилась данными из Google таблицы, воспроизведем эту же диаграмму в EXCEL.

Придется немного потрудиться, чтобы получить аналогичный результат в EXCEL. Нам придется:

• скопировать столбец Buyer в столбец O
• удалить дубликаты (вкладка Данные, группа Работа с данными) – получим список с уникальными кодами Покупателей
• подсчитать в ячейке R1 общее количество значений кодов =СЧЁТЗ(B2:B70)
• вычислить % повторов каждого кода =СЧЁТЕСЛИ($B$2:$B$70;O2)/$R$1
• отсортировать Покупателей по количеству повторов
• Отобрать первые 9, остальные повторы просуммировать
• Построить диаграмму

Как видно, GDS сделала достаточно много вычислений автоматически. Конечно, совсем не обязательно, что при построении диаграммы мы хотели подсчитать именно % продаж, сделанных конкретному Покупателю, но чем-то Google Studio должна была заполнить диаграмму!

Далее малазийка с помощью пару кликов заменяет категорию Покупатели на Код Товара (SKU), при этом в качестве значений теперь берутся цены товара (Price).

Теперь у нас диаграмма отображает % продаж каждого товара. Эта диаграмма войдет в окончательный отчет (сократится только количество отображаемых категорий).

Придется опять немного потрудиться, чтобы получить нужный результат в EXCEL (пару минут для опытного пользователя), результат приведен в файле примера.

Далее в этой части урока (34:33) диаграмма будет форматироваться. Настройку цвета отдельных кусочков круговой диаграммы в GDS, как мне показалось, сделать удобнее чем в EXCEL 2016 и, кроме, того цветовые решения по умолчанию кажутся более профессиональными.

Очевидно, что форматирование диаграмм выполняется совершенно по-разному в этих двух средах. Сложилось впечатление, что в GDS меню форматирования диаграмм разрабатывали профессиональные дизайнеры, а в EXCEL – инженеры.

Недостатком GDS является слишком блеклое меню Style. В EXCEL разные группы команд гораздо более различимы (кнопки настройки подписей данных и, например, кнопки настройки легенды диаграммы явно нельзя перепутать, чего не скажешь о Google Studio, где все выглядит одинаковым – следствие веб интерфейса).

Часть 3.2 Линейчатая диаграмма (Bar chart)

В 36:20 малазийка начнет создавать линейчатую диаграмму (Bar Chart).

Так как основе линейчатой и круговой диаграммы, созданной на предыдущем шаге, будут лежать те же самые данные, то достаточно просто скопировать круговую диаграмму и изменить ее тип. В EXCEL также присутствует аналогичная функция (после выделения диаграммы в меню Конструктор выберите команду «Изменить тип диаграммы»).

В GDS имеется возможность форматирования числа в виде компактной записи (Compact numbers). Числа больше 1000 можно отображать в виде 1K, например, 32,6K или 5М. Чтобы сделать аналогичное форматирование в EXCEL придется воспользоваться пользовательским форматом (встроенный формат компактной записи в EXCEL отсутствует).

Весьма удобным в GDS является возможность ограничения количества отображаемых на диаграмме категорий. Например, можно отобразить на диаграмме только 5 горизонтальных полосок, соответствующих категориям с максимальными значениями.

Чтобы добиться такого же результата в EXCEL придется менять таблицу с исходными данными.

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

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

Чтобы отменить фильтрацию – кликните по выбранной категории или нажмите вверху кнопку меню Сбросить (Reset).

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

Часть 3.3 Диаграмма Сводка (Scorecard)

В 41:40 малазийка начнет создавать диаграмму типа Сводка. Эта диаграмма выводит просто число – интегральный показатель неких значений. В данном случае создается поле с суммарными продажами.

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

Часть 3.4 Таблица с тепловой картой (Table with Heatmap)

В 47:50 будет создана таблица с данными. Особенной ее делает так называемая Тепловая карта (Heatmap).

Тепловая карта – это на языке MS EXCEL – условное форматирование.

Всего в таблице 4 столбца.

Столбец Coupon Code (код купона) является Параметром-категорией (Dimension), а по столбцам Price и Discount Amount ведется суммирование по условию. В последнем столбце подсчитывается количество Покупателей (Buyer) выбравших тот или иной купон.

Давайте построим таблицу EXCEL как в Google Data Studio. Это, кстати, не так просто, как кажется.

Чтобы получить первый столбец таблицы необходимо отобрать уникальные значения в столбце Coupon Code. Это можно сделать одной формулой массива

=ЕСЛИОШИБКА(ИНДЕКС($D$5:$D$73;ПОИСКПОЗ(0;СЧЁТЕСЛИ(Q$4:Q4;$D$5:$D$73);0));"")

Это реализовано в файле примера на листе Data (3).

Из-за того, что формула не чувствительна к регистру, то cyber2021 и CYBER2021 сольются в одно значение cyber2021 (будет выведено именно cyber2021, а не CYBER2021, т.к. именно cyber2021 стоит первым в столбце Coupon Code таблицы). В принципе можно вывести и оба значения (см. статью про отбор уникальных с учетом регистра), но не будем делать этого, т.к. малазийка все равно схлопнет оба значения в самом конце 5го урока (1:15:15).

Кроме того, при создании диаграммы в EXCEL нужно преодолеть еще одну сложность: исходный столбец Coupon Code содержит пустые ячейки. Лучше сразу их заменить в исходном столбце значением null чтобы далее при написании формул не иметь проблем. Массовую замену в несвязных диапазонах можно быстро сделать следующим образом:

• Выделите столбец Coupon Code на листе Data – диапазон D1:D70
• С помощью инструмента «Выделить группу ячеек» выделите пустые ячейки
• В строке формул введите null
• Нажмите CTRL+ENTER, пустые ячейки заполнятся значением null (подробнее см. статью про заполнение пустых ячеек)

Чтобы получить второй и третий столбец воспользуемся простой формулой сложения с условием=СУММЕСЛИ($D$5:$D$73;Q5;$C$5)

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

=СУММПРОИЗВ(($D$5:$D$73=Q5)/СЧЁТЕСЛИМН($D$5:$D$73;$D$5:$D$73;$B$5:$B$73;$B$5:$B$73))

Примечание: Если использовать формулу =СЧЁТЕСЛИ($D$5:$D$73;Q5), то получим общее количество продаж по тому или иному купону (к уникальным будут добавлены их повторы).

В итоге в EXCEL получим таблицу почти как в Google Data Studio (но без сортировки по столбцу Price).

Чтобы наконец сделать Тепловую карту необходимо последовательно выделить числовые столбцы и применить стандартное условное форматирование (вкладка Главная, группа Стили, выбрать Условное форматирование, далее Гистограммы и градиентная заливка).

В итоге получим желаемую таблицу с аналогом Тепловой карты.

Очевидно, что в Google Data Studio создать такую таблицу гораздо проще как новичку, так и уверенному пользователю EXCEL.

Примечание:

Если в таблице Google Data Studio вместо Тепловой карты выбрать Гистограмму (вместо Heatmap выбрать Bar), то можно настроить целевое значение – вертикальную линию, показывающую в каких строках мы достигли, например, целевых продаж.

В EXCEL 2016 что-то подобное можно сделать на диаграмме, но тогда придется пожертвовать возможностью использовать Условное форматирование.

Часть 4 Диаграмма динамических рядов (Time series)

С самого начала 4й части (58:00) начинается создание Диаграммы динамических рядов. По умолчанию, естественно, GDS выбирает в качестве оси Х данные из столбца с датами. В нашем случае это даты продажи (Sale Date). В качестве значений оси Y (Показатель - Metric) GDS выбирает количество одинаковых дат продаж.

Обратите внимание, что у дат, у которых нет продаж отображаются 0 значения.

Затем малазийка меняет значения оси Y на Price (сумму). Вроде все просто – видна динамика продаж во времени.

Попробуем воспроизвести эту диаграмму в EXCEL (см. лист Data(4.1) в файле примера). Можно использовать диаграмму типа Точечная или График (во втором случае убедитесь, что Типом оси выбраны Ось дат или Автовыбор). Но, получается не совсем то.

В отличие от GDS дни, когда нет продаж, нули не отображаются, а пропускаются. В итоге график выглядит по-другому. Кстати, это отличие стирается после того как даты будут агрегированы по месяцам (если в каждом месяце нет нулевых продаж).

Чтобы сделать график как в GDS придется переделать таблицу с данными (см. лист Data(4.2) в файле примера). Необходимо расширить ее, включив все даты в выбранном временном промежутке.

Агрегировать данные продаж по месяцам в GDS можно за нескольких кликов, изменив настройку Параметра (Dimension).

В EXCEL опять придется переделывать таблицу с данными (см. лист Data(4.3) в файле примера), причем изменение исходной таблицы потребует хорошего знания EXCEL. Тем не менее, в итоге можно получить практически идентичные диаграммы:

Географическая диаграмма (Geo Chart)

Последней диаграммой, рассматриваемой в 4-й части урока, будет Географическая диаграмма.

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

Если у вас EXCEL 2019, то проблем с построением аналогичной карты, не возникает: их можно строить как обычные диаграммы поскольку в EXCEL 2019 появился новый тип данных – географический.

В Excel 2016 во вкладке Вставка присутствует кнопка 3D-карта (3D-map), которая также позволяет строить карты. Но, для этого вам необходимо соединение с Интернетом для подключения к картографическому сервису Bing.

Часть 5 Вычисляемые поля (Calculated Field)

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

В видеоуроке предлагается создать простейшую формулу (Price-Discount Amount).

Если перейти в таблицу EXCEL с исходными данными, то эта формула эквивалентна формуле =C2-E2. Сумма значений столбца, полученного с помощью этой формулы, равна 1974,5.

В GDS, естественно, получается тот же результат (см. видеоурок 1:12:20, сумма по последней колонке таблицы).

Вычисляемые поля GDS так же, как и формулы EXCEL, могут производить математические операции с числами, изменять текстовые значения (перевод в верхний регистр, извлечение части из текстовой строки, конкатенация строк), а также содержать логические, проверочные выражения.

Часть 6 Элементы управления (Filters, Data Control)

С помощью элементов управления GDS фильтрует данные в отчете.

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

Сначала малазийка добавляет элемент управления Выпадающий список.

Как видно из видеоурока, возможности настройки Выпадающего списка существенно превышают функционал Выпадающего списка EXCEL (на основе элемента управления и на основе Проверки данных): в GDS можно легко создать списки со множественным выбором, добавить вторую колонку и отсортировать список по ней. И все это реализовано с интуитивно понятным интерфейсом.

Как известно, EXCEL фильтрация также доступна с помощью автофильтра и Расширенного фильтра. Выбирая элементом управления Выпадающий список, например, Страну (Delivery Country), GDS фактически отфильтровывает строки исходной таблицы не содержащие выбранную страну.

Еще один интересный элемент управления – Date Range Control (Диапазон дат). Фактически это календарь, который позволяет установить начальную и конечную дату для фильтрации данных в таблице.

Обратите внимание, что во всех предыдущих диаграммах, которые мы строили, Параметр: диапазон дат (Date Range Dimension) содержал ссылку на столбец с датами Sale Date.

Хотя в исходной таблице есть и второй столбец с датами (Date Paid), в котором значения дат могут быть отличными.

Элемент управления Date Range Control не привязан ни к какому из столбцов с датами, он лишь задает диапазон применительно к параметру Date Range Dimension, указанному в каждой диаграмме. Если 2 диаграммы привязаны к разным параметрам Date Range Dimension, то фильтрация строк для них будет производиться по разным столбцам. Используйте в параметре Date Range Dimension один и тот же столбец дат для всех диаграмм отчета (по крайней мере на одной странице). Отступать от этого правила, конечно можно, но это должно быть обосновано и не приводить к недопониманию пользователей вашего отчета.

Примечание: Как было сказано в части 3.2, любая диаграмма (кроме Сводка) в GDS интерактивна и позволяет фильтровать данные не хуже специализированного элемента управления, достаточно лишь кликнуть на нужную категорию в диаграмме.

Выводы:

• Google Data Studio позволяет настроить диаграммы гораздо проще чем EXCEL (пользователь будет больше думать о том КАКУЮ диаграмму ему сделать, нежели КАК ее сделать)
• Google Data Studio не уступает EXCEL в средствах по построению диаграмм, а может даже и превосходит
• Google Data Studio не является заменой EXCEL (функциональность программ совпадает только в части визуализации данных)
• Google Data Studio незаменим, если необходимо поделиться данными в удобной форме с другими пользователями (см. часть 8 видеоурока)