Наводим красоту в Google Таблицах: лайфхаки по визуализации данных

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

 
В Ringostat работа с Google Sheets ежедневно происходит у каждого отдела. Чтобы сотрудники не тратили по 10-15 минут для вникания в данные, в наших дашбордах сразу настраивается визуализация. Здесь мы не будем рассматривать базовые моменты, как создать таблицу в Google Docs и т. д. В этой статье мы поделимся приемами, которые были нам полезны чаще всего и пригодятся всем, кто работает с данными.

Как зафиксировать строку в Гугл Таблице

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

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

Таким образом в Google таблицах можно закрепить шапку, которая будет отображаться при прокрутке документа.

Как оформить диаграммы

Мы не будем рассматривать детально, как рисовать графики Google Spreadsheets, потому что это делается несложно и описывается в мануале Google. Мы же покажем, как их можно сделать максимально наглядными.

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

Для понимания принципов работы подобных документов — рекомендуем статью «Дашборд для отдела маркетинга: описание настройки и шаблон».

Выбор цветовой гаммы

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

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

Чередование цветов

Когда цвета ячеек чередуются, информацию воспринимать немного проще. Для этого нужно выбрать нужный диапазон или столбец, зайти в раздел Формат — Чередование и выбрать нужные цвета.

Создать диаграмму по нажатию чекбокса

Чекбокс или флажок — это квадратная область, при нажатии на которую проставляется маркер. Например, галочка. Можно сделать так, чтобы график автоматически строился в зависимости от того, какие чекбоксы активированы.

Приведем пример. В документе, приведенном выше, есть такие показатели:

• MQL — все поступившие лиды, по которым до этого еще не было открытых сделок;
• SQL — лиды, которых отдел продаж посчитал качественными: посетитель обратился с релевантным запросом, это не спам, менеджер смог связаться с человеком и т. д.;
• Won — выгранные сделки;
• Lost — проигранные сделки;
• Spam — мусорные лиды.

Допустим, мы хотим посмотреть, как за год изменялось количество MQL, сравнить их с числом SQL и выигранных сделок. Чтобы это сделать, мы делаем всего три клика, и получаем готовый график:

Как это сделать? Выбираем мышью нужный диапазон, столбец, строку или ячейку. Нажимаем на вкладку Вставка — Флажок, и вся выбранная область преобразуется в чек-боксы.

Чтобы при нажатии на них строились диаграммы, нужно использовать формулы. Если в документе-примере вы подвинете график в сторону, то увидите под ним примерно такую картину:

Когда выбирается один из чек-боксов, соответствующая ячейка в обведенном красным участке меняется на TRUE. Данные копируются из заданного диапазона, и таким образом строится график.

Нижний ряд цифр содержит формулу с указанием строки, ее статусом и диапазоном. Например:

=IF(A3=TRUE;ARRAYFORMULA(C3:O3))

Чтобы лучше понимать, как работать с формулами, рекомендуем статью «Базовые советы для удобной работы в Google Spreadsheets».

Визуализация по временному отрезку

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

Как сделать выпадающий список google docs? Выбираем Данные — Проверка данных — Значение из списка или Значения из диапазона и прописываем ячейки. Когда прописываем формулу, то ставим адрес ячейки, а не значение или текст.

Процент и абсолютное значение на одной диаграмме

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

• Leads Amount — количество лидов;
• Conversion — конверсия: из MQL в SQL, из SQL в выигранные сделки, из MQL в выигранные сделки.

Выбираем строку, в которой будет открываться выпадающий список. Как в примере выше, выбираем Данные — Проверка данных — Значение из списка и прописываем параметры.

Формула так же «спрятана» под диаграммой, как в предыдущих примерах.

Ее особенность в том, что если в строке с выпадающим списком написано Leads Amount, то она берет диапазон, который включает количество лидов. Если Conversion — то тот, где указана конверсия.

=IF(A10=»Leads Amount»;ARRAYFORMULA(TO_PURE_NUMBER($C$18:$O$23));

(IF(A10=»Conversion»;ARRAYFORMULA(TO_PERCENT($C$27:$O$30));»0″)))

Данные должны быть в автоматическом формате. Если поставить число или процент, она срабатывать не будет. В самой формуле вам нужно прописать преобразование в абсолютное значение или процент — как на примере to_pure_number, to_percent.

Как работать с правилами форматирования данных

Окрашивание ячеек

Можно сделать так, чтобы ячейки окрашивались в зависимости от того, какое значение в них введено. Условия могут быть противоположными. Например, чем больше лидов, тем лучше, в этом случае ячейки нужно окрашивать зеленым. А вот если много проигранных сделок и мусорных лидов, то это уже плохо. Это желательно выделить красным.

Выбираем значения диапазона, которые нужно окрашивать Формат — Условное форматирование. В нашем случае для лидов MQL и SQL мы прописали два правила:

• красным подчеркиваются значения, которые меньше, чем D2 — значение по первому месяцу;
• зеленым подчеркиваются значения, которые равны или больше.

Формула автоматически меняется и растягиваться с ходом времени. Для окрашивания ячеек по проигранным сделкам и мусорным лидам логика обратная.

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

Окрашивание в зависимости от критичности

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

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

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

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

Выводы

1. Если ваш документ в Google Таблицах занимает более одного экрана, закрепите его «шапку» — первые несколько строк и, при необходимости, правый боковой столбец.
2. В базовых вопросах о таблицах ориентируйтесь на официальную справку Google, а сложные задачи спрашивайте в профильных сообществах. 
3. Чтобы текст в таблице был читаемый, вибирайте светлые цвета для заливки ячеек.
4. Чтобы создавать графики по нажатию чекбоксов или визуализировать диаграммы по временному отрезку, используйте в качестве шаблона наш дашборд из статьи «Дашборд для отдела маркетинга: описание настройки и шаблон».
5. Чтобы ячейки сами окрашивались в нужные цвета и сами выделяли нестандартные данные, задайте правила форматирования данных по этой инструкции.