Допустим, есть таблица с подробными данными по всем статьям блога:
И хочется проанализировать различные срезы, найти ответы на подобные вопросы:
- Сколько статей опубликовали в тот или иной месяц?
- Какой формат и раздел в среднем лучше конвертирует читателей?
- Кто пишет более трафиковые статьи?
- Какие самые посещаемые материалы по маркетингу за квартал?
- Что за тексты в аутсайдерах по проценту конверсии?
Можно постоянно возиться с фильтрами, сортировкой и формулами, а можно заморочиться один раз — и сделать кастомный дашборд. Плюс такой конструктор отчетов легко расшарить — разберется даже джун.
Рассказываю пошагово и подробно, как сделать такую табличку — с помощью COUNTIF, SUM, AVERAGE, TEXT, IFS и мощнейшей QUERY. Ну и проверки данных.
Гораздо легче изучать тему, если сразу все смотреть в Google Таблицах.
Держите шаблон конструктора со всеми данными и формулами. Чтобы что-то редактировать, заберите документ себе через «Файл» → «Создать копию».
Вся статистика вымышленная, все совпадения с реальными данными — случайные.
Дисклеймер: эту статью стоит воспринимать в основном не как инструкцию, а как кейс или обучалку. Задача контент-аналитики, которую я здесь разобрал, довольно узкая. Но те же принципы и формулы можно использовать для самых разных решений. Например, сделать конструктор для подробного анализа личных финансов. Чтобы быстро и удобно анализировать таблицу для ведения бюджета и смотреть: сколько трат было на одежду за квартал, какой средний чек в продуктовом магазине
1. Продумать дизайн, составить прототип
Прежде чем начинать работать с формулами и данными, стоит подготовить что-то вроде ТЗ. Понять, где что будет находиться, что куда выводиться.
Я решил разместить все настройки слева, сводку с общими и средними показателями по выборке — справа, а под ней уже расположить подробный отчет: все строки из нашей базы данных, которые соответствуют указанным параметрам.
В итоге получился такой прототип:
Самый первый столбец посвящен неким кляузам — в разделе о QUERY подробно расскажу про них.
2. Настроить проверку данных
Прежде чем перейти к великой и ужасной QUERY, можно настроить проверку данных. Когда пользователь выбирает что-то из выпадающего списка, а не вписывает в ячейку сам. Это удобно, а еще защищает от ошибок и поломки всей таблицы.
«Автор». Разбираю на примере первого параметра:
- Кликнуть на ячейку C3.
- В главном меню выбрать «Данные» → «Настроить проверку данных».
- Оставить по умолчанию «Значение из диапазона».
- Указать или выбрать соответствующий диапазон. Это первый столбец на листе с данными, не считая заголовка.
- В пункте «Для неверных данных» поставить «Запрещать ввод данных».
Небольшие пояснения.
Конструктор отчетов — на листе «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, где расписываются все части запроса. И защитить диапазоны, где не предусматривается редактирование — тогда таблицу можно спокойно шерить.
Потеряли ссылку — вот шаблон конструктора со всеми данными и формулами. Чтобы что-то менять и экспериментировать, заберите документ себе через «Файл» → «Создать копию». Вся статистика в документе вымышленная, все совпадения с реальными данными — случайные.
на журнал
