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

07.03.201918 262AAA

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

В 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 или .

Яндекс Вордстат: как пользоватьсяКак настроить welcome-цепочки писем в MailChimp: пошаговый мануал17 августа 2016

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

Чтобы создать такой список, выполните следующие действия:

1. Выберите ячейки, в которые нужно добавить раскрывающийся список.
2. Нажмите Данные > Проверка данных.
3. В раскрывающемся меню напротив пункта «Правила» выберите Значения из диапазона или Список значений.
   • Если вы выбрали «Значения из диапазона», укажите ячейки, которые станут пунктами раскрывающегося списка. Примечание. Когда вы изменяете данные в выбранном диапазоне, список обновляется автоматически.
   • Если вы выбрали «Список значений», введите нужные пункты через запятую, например «персики,сливы,абрикосы,вишня». Не ставьте пробелы после запятых.
4. По умолчанию список будет раскрываться при нажатии кнопки со стрелкой в правой части ячейки. Чтобы убрать стрелку, снимите флажок «Отображать в ячейке кнопку для просмотра списка».
5. Нажмите Сохранить. Теперь пользователь будет видеть список указанных вами значений непосредственнпри редактировании ячейки.

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

Узнайте о нас больше:

Мы предлагаем широкий спект услуг по сайтам и компьютерной поддержке

Создание сайтов

Мы разрабатываем как простые сайты — визитки, так и индивидуальные проекты. Основная система разработки — CMS Drupal.

Узнать подробнее

Продвижение сайтов

Одно из основных направлений работы нашей компании — продвижение сайтов в Саратове и области. Мы поможем Вам с SEO — продвижением сайтов и настройкой и ведением контекстной рекламы в Яндекс Директ.

Бесплатная SEO-консультация

Компьтерная поддержка

Мы оказываем услуги компаниям и частным лицам в области настройки компьютерного оборудования:

• Настройка роутеров и локальных сетей
• Установка Windows
• Оптимизация работы компьютеров
• Лечение вирусов
• Восстановление данных с жестких дисков

Узнать подробнее

Хотите освоить урок, как сделать раскрывающийся список в Google Таблицах, которые являются отличной и равноценной заменой Microsoft Excel? Создание его пригодится в том случае, например, если вы работаете с другими пользователями в общем файле и они могут вводить в ячейку ошибочные данные или что-то, что нарушает формулу.

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

Что можно подготовить:

• значения из диапазона или списка;
• число;
• текст;
• дата;
• формула;
• флажок.

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

КАК СДЕЛАТЬ РАСКРЫВАЮЩИЙСЯ СПИСОК

Далее перейдите в меню «Данные» на Панели инструментов вверху. Выберите команду «Проверка данных…».

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

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

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

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

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

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

Используемые источники:

• https://blog.ringostat.com/ru/navodim-krasotu-v-google-tablitsah-layfhaki-po-vizualizatsii-dannyh/
• https://raybin.ru/kak-sozdat-raskryvayushchiysya-spisok-v-yacheyke-google-docs-tablicy.html
• https://liwihelp.com/ofis/kak-sdelat-raskryvajushhijsja-spisok.html