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

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

Статья обновлена 01.02.2024.

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

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

Как зафиксировать строку в Google Sheets

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

Функция «Закрепить» в таблицах позволяет фиксировать строки и столбцы, чтобы они всегда оставались видимыми при прокрутке. Это удобно при работе с большими объемами данных, где важно видеть заголовки или ключевые показатели. Закрепить можно как одну, так и несколько строк/столбцов. Для этого нужно выбрать нужные ячейки, перейти на вкладку «Вид» и выбрать «Закрепить строки» или «Закрепить столбцы».

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

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

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

Что вы найдете:

• первый лист: диаграммы, построенные на основе данных из «Базы данных»;
• формулы: позволяют увидеть, как работает документ;
• третий лист: «База данных» с исходными данными.

Используйте этот пример, чтобы:

• изучить различные типы диаграмм;
• понять, как подтягиваются данные из «Базы данных» в диаграммы и как работают формулы;
• использовать готовые решения для своих задач;
• научиться создавать свои диаграммы с помощью формул в Google Sheets.

Вы можете скопировать документ себе, чтобы редактировать его и экспериментировать с диаграммами.

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

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

График или диаграмму можно создать для любого диапазона данных таблицы.

• выберите мышью нужную область;
• перейдите в раздел «Вставка» — «Диаграмма»;
• в меню, которое раскроется справа, в разделе «Дополнительные» — «Серия» задайте цвет.

Аналогично, если нужно поменять цвет в уже построенном графике.

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

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

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

• выберите диапазон или столбец, в котором хотите чередовать цвета;
• перейдите в раздел «Формат» и выберите «Чередование»;
• в открывшемся меню выберите желаемые цвета для чередования.

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

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

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

Чекбокс (флажок) – это интерактивный элемент, позволяющий управлять отображением данных на графике. При нажатии на него происходит активация/деактивация соответствующего ряда данных.

Можно строить график автоматически на основе данных, выбранных чекбоксом.

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

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

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

Как это сделать?

1. Выберите диапазон, столбец, строку или ячейку, где хотите добавить чек-боксы.
2. Перейдите на вкладку «Вставка».
3. Нажмите кнопку «Флажок».

Результат:

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

Но как строить график или диаграму при нажатии на чекбокс? При помощи формул. Откройте документ-пример, ссылку на который мы давали выше. Подвиньте график в сторону, и увидите там такую картину:

Если вы выберете один из чек-боксов, содержание в соответствующей ячейке меняется с FALSE на TRUE. Из заданного диапазона копируются данные, на их основе строится график.

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

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

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

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

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

1. Выберите из выпадающего списка год, данные за который хотите отобразить (2018 или 2019).
2. Отметьте чекбоксы, соответствующие рядам данных, которые хотите включить в диаграмму (MQL, SQL или Сделки, чтобы увидеть динамику нужных вам показателей).
3. Нажмите кнопку «Построить диаграму».

Как сделать выпадающий список? Выбирите «Данные» — «Проверка данных» — «Значение из списка» или «Значение из диапазона». И укажите адрес нужных ячеек. Обратите внимание: не значение или текст, а диапазон.

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

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

В нашем примере из выпадающего списка на визуализации можно выбрать:

• 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.

Правила форматирования данных в Google Sheets

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

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

Как настроить окрашивание?

• выбирите значения диапазона, которые нужно окрашивать;
• перейдите в раздел «Формат» — «Условное форматирование».

В нашем случае для лидов MQL и SQL мы прописали два правила:

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

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

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

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

Для такой визуализации действует правило, похожее на описанное выше. Но градация уже не двух, а трехцветная.

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

Чтобы настроить такое окрашивание, в разделе «Формат» — «Условное форматирование» добавьте еще одно правило для желтого цвета.

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

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

Выводы

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