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

Olga Feoktistova
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5,00 out of 5)
Loading ... Loading ...
A A A

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

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

 

Очевидно, но важно: закрепление строк

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

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

 

Диаграммы

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

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

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

 

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

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

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

 

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

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

 

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

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

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

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

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

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

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

 

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

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

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

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

 

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

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

 

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

 

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

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

  • 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 дней.

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

 

Категория

Если вы нашли ошибку - выделите её и нажмите Ctrl + Enter или .