16.12.2022
1984
Как сделать интерактивный дашборд / конструктор отчетов в Google Таблицах фото

Как сделать интерактивный дашборд / конструктор отчетов в Google Таблицах

1984
Без фильтров и ручного редактирования формул. На примере контент-аналитики блога.
Содержание:

    Допустим, есть таблица с подробными данными по всем статьям блога:

    И хочется проанализировать различные срезы, найти ответы на подобные вопросы:

    • Сколько статей опубликовали в тот или иной месяц?
    • Какой формат и раздел в среднем лучше конвертирует читателей?
    • Кто пишет более трафиковые статьи?
    • Какие самые посещаемые материалы по маркетингу за квартал?
    • Что за тексты в аутсайдерах по проценту конверсии?

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

    Рассказываю пошагово и подробно, как сделать такую табличку — с помощью COUNTIF, SUM, AVERAGE, TEXT, IFS и мощнейшей QUERY. Ну и проверки данных.

    Гораздо легче изучать тему, если сразу все смотреть в Google Таблицах.

    Держите шаблон конструктора со всеми данными и формулами. Чтобы что-то редактировать, заберите документ себе через «Файл» → «Создать копию».

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

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

    1. Продумать дизайн, составить прототип

    Прежде чем начинать работать с формулами и данными, стоит подготовить что-то вроде ТЗ. Понять, где что будет находиться, что куда выводиться.

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

    В итоге получился такой прототип:

    Самый первый столбец посвящен неким кляузам — в разделе о QUERY подробно расскажу про них.

    2. Настроить проверку данных

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

    «Автор». Разбираю на примере первого параметра:

    1. Кликнуть на ячейку C3.
    2. В главном меню выбрать «Данные» → «Настроить проверку данных».
    3. Оставить по умолчанию «Значение из диапазона».
    4. Указать или выбрать соответствующий диапазон. Это первый столбец на листе с данными, не считая заголовка.
    5. В пункте «Для неверных данных» поставить «Запрещать ввод данных».

    Небольшие пояснения. 

    Конструктор отчетов — на листе «Report», а лист с данными по блогу — на листе «Data». Поэтому ссылки разные. 

    Выбран открытый диапазон — Data!A2:A, а не Data!A2:A86 — чтобы таблица корректно работала, когда будут добавлены новые данные.

    Абсолютную ссылку — Data!$A$2:$A вместо Data!A2:A — автоматически ставит сам Google Sheets. Но такие знаки полезно добавлять и во многих других случаях. Абсолютная ссылка отличается тем, что не меняется при переносе в другую ячейку, протягивании формулы.

    «Раздел» и «Формат». По такому же принципу настроить проверку данных в Report! C4 и Report! C5. Только они будут выбирать значения из диапазонов Data!$B$ 2:$B и Data!$C$ 2:$C соответственно.

    «От даты» и «До даты». В ячейках, где указываем даты, как минимум нужно настроить формат. То есть выделить C6 и C7, и в главном меню кликнуть на «Формат» → «Числа» → «Дата». Сразу можно указать какие-то значения по умолчанию — тогда при редактировании появится всплывающий календарь.

    Хотя тут тоже не помешает проверка данных — чтобы никто не пытался вписать обычное число или текст.

    «Сортировка по столбцу». Здесь тоже нужно настроить проверку данных, но диапазон будет Data!$G$ 1:$I$ 1, то есть берем просто названия столбцов. Можно, конечно, подать их и обычным списком, но значения из диапазона надежнее — на случай, если захочется поменять названия столбцов.

    «Способ сортировки». Для Report! C9 вариант другой — нужно указать значения «По возрастанию» и «По убыванию» списком.

    «Лимит». Здесь тоже лучше настроить проверку — указать, что вводить можно только числа, например, от 0 до 1 000.

    3. Разобраться с QUERY

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

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

    Запрос QUERY состоит из нескольких блоков (кляуз). Удобнее делать их в разных ячейках, а потом соединять — чтобы проще было редактировать и искать ошибки.

    SELECT

    Указывает, какие столбцы из указанного диапазона нужно выводить.

    В «Детализации» я решил показывать только часть данных с листа Data — «Название», «Ссылка», «Дата», «Визиты», «Конверсии» и «% конверсии». Потому что информация по авторам, форматам и разделам и так будет указана в настройках слева.

    Следовательно, в запросе пишу «SELECT D, E, F, G, H, I» — именно в этих столбцах лежат искомые данные на соседнем листе.

    Для удобства анализа и правок все кляузы (команды, блоки запроса QUERY) решил разрабатывать в столбце A. В дальнейшем, если формула работает корректно, этот столбец можно просто скрыть и закрыть для редактирования.

    WHERE

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

    «Автор». Допустим, нужно найти все статьи Василисы. Для этого необходимо взять только те данные из диапазона Data! A:I, где ячейки в столбце A соответствуют значению «Василиса». Значит, кляуза получится такой: «WHERE A = 'Василиса'» (текстовые строки внутри SQL-запроса передаются в одинарных кавычках).

    Чтобы составить эту часть запроса, можно было бы просто к строке «WHERE A = ‘» добавить значение из ячейки Report! C3, а в конце закрывающую кавычку «'». Но, помимо имеющихся авторов, есть дополнительный вариант — пустая строка, которая должна выводить статьи всех авторов сразу. И в таком случае запрос «WHERE A = ‘'» дал бы нулевые результаты. Поэтому нужно добавить формулу IF, которая будет учитывать такой вариант и корректировать запрос в QUERY.

    Небольшие пояснения.

    С помощью функции IF проверяю ячейку Report!C3. Если она пустая, отдаю строку «WHERE A <> ‘’». 

    В таком случае условие для SQL-запроса — взять данные, где ячейки в столбце A не равны пустой строке, то есть содержат хоть какое-то значение. Так QUERY и покажет статьи всех авторов.

    А вот если C3 не равно пустой строке, то IF вернет другое значение. Как раз по схеме, о которой говорили выше. Соединить несколько строк в одну помогает оператор конкатенации — амперсанд «&». Главное, не запутаться в двойных и одинарных кавычках: последние передают текстовые данные внутри запроса QUERY.

    «Раздел» и «Формат». Здесь похожий принцип, только нужно:

    • Проверять на пустые строки C4 и C5 листа Report. И брать оттуда значения соответственно.
    • Фильтровать столбцы B и C листа Data.
    • Использовать логический оператор AND в тексте запроса, так как это дополнительное условие WHERE. Эту кляузу уже указали на предыдущем шаге, повторно ее прописывать не надо.

    «От даты» и «До даты». Тут тоже продолжаются условия для WHERE с логическими операторами AND, но немного сложнее.

    Значения в ячейках C6 и C7 — в числовом формате. А запрос QUERY нужно передавать в текстовом. Поэтому даты нужно сначала перевести в текст — для этого есть соответствующая функция TEXT.

    Чтобы обработать строки с датами, QUERY нужно передать их в формате «yyyy-mm-dd», а не как нам обычно удобно читать и писать. И еще для этого нужен специальный оператор — date.

    Поэтому формула принимает такой вид:

    Но если оставить формулу в таком виде, QUERY не даст никаких результатов в том случае, когда ячейка C7 окажется пустой. Потому что пустая строка преобразуется в странную дату «1899−12−30» — естественно, тогда никто никаких статей не писал. Лучше вложить всю эту конструкцию в IF, чтобы предусмотреть такой вариант.

    ORDER BY

    Эта кляуза QUERY отвечает за сортировку.

    «Сортировка по столбцу». Допустим, нужно отсортировать статьи блога по посещаемости, то есть количеству визитов. «Визиты» — это столбец G на листе Data, значит, нужно написать запрос «ORDER BY G». Все просто.

    Столбцов с количественными показателями всего три — «Визиты, «Конверсии» и «% конверсии». Еще может быть, что ячейка Report! C8 пуста — сортировка не нужна. С помощью функции IFS можно учесть все варианты и прописать соответствующие запросы для QUERY.

    В этой формуле я сравниваю C8 с ячейками на соседнем листе Data, где указаны названия, но можно и просто прописать эти значения текстом. Если истинно первое условие — в C8 ничего нет — то и IFS вернет пустую строку:

    «Способ сортировки». Здесь я прописываю дополнительный необязательный параметр ORDER BY, который показывает, как сортировать — по возрастанию (ASC) или по убыванию (DESC).

    Формула усложняется, так как нужно учесть значения в соседней ячейке C8, где указан столбец для сортировки. Если эта ячейка пустая, то есть сортировка не нужна, то и добавлять «ASC» или «DESC» не нужно — иначе будет ошибка. А если нет ничего в ячейке C9, то тоже нужно вернуть пустую строку.

    LIMIT

    Ограничивает количество выводимых результатов.

    Допустим, мы хотим увидеть топ-10 статей по указанным параметрам. Тогда в конце запроса достаточно добавить «LIMIT 10».

    С этой кляузой все просто, но я решил еще задействовать функцию ISNUMBER — если в C10 не число, то нужно выводить просто пустую строку и не ломать формулу. Хотя с этим хорошо справляется проверка данных, которую настроил на втором шаге.

    Итоговая формула

    Готово! Теперь нужно объединить все фрагменты, которые получились в столбце A, в единый запрос. Для этого достаточно оператора конкатенации — амперсанда «&» — и пробелов, чтобы отделить кляузы друг от друга.

    А в ячейке под заголовком «Детализация» будет сама формула QUERY. Запрос беру из ячейки A13, где объединяются разные кляузы. А последний параметр — «1» — показывает, что первая строка в диапазоне Data! A:I — это заголовки, а не данные по статьям блога. Их обрабатывать не нужно.

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

    4. Составить формулы для расчета общих и средних

    Самое сложное позади, с блоком «Сводка» проще. Но есть некоторые нюансы, связанные с тем, что данные в Report! E10:J постоянно меняются.

    «Количество статей». Нужно посчитать количество ячеек — допустим, в открытом диапазоне Report! F10:F, — при условии, чтобы в ячейках есть какие-то значения. Для этого подойдет функция COUNTIF:

    «Общее число визитов». Для подсчета можно использовать SUM, в качестве аргумента передать диапазон H10: H.

    «Общее число конверсий». Аналогично, только диапазон — I10: I.

    «Среднее число визитов». Здесь формула AVERAGE, ей тоже нужно передать один аргумент — диапазон H10: H.

    «Среднее число конверсий». Аналогично, только другой диапазон — I10: I.

    «Средний % конверсии». Тоже AVERAGE, но с диапазоном J10: J.

    По умолчанию в этой ячейке может появиться десятичная дробь, а не процент. В таком случае нужно кликнуть на нее и выбрать в меню «Формат» → «Числа» → «Процент».

    5. Навести красоту и еще раз все перепроверить

    На скриншотах — уже оформленная таблица, а если делать с нуля, то нужно еще потратить немного времени на наведение красоты. Где-то увеличить размер шрифта, где-то объединить ячейки, где-то добавить выделение и убрать числа после запятой.

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

    Если с кляузами и QUERY все ок, можно скрыть столбец Report! A:A, где расписываются все части запроса. И защитить диапазоны, где не предусматривается редактирование — тогда таблицу можно спокойно шерить.

    Потеряли ссылку — вот шаблон конструктора со всеми данными и формулами. Чтобы что-то менять и экспериментировать, заберите документ себе через «Файл» → «Создать копию». Вся статистика в документе вымышленная, все совпадения с реальными данными — случайные.

    Подпишитесь
    на журнал
    Чтобы знать о выходе новых статей