Всемогущая функция Query — подробное руководство > NetPeak - Независимость и осознанность
NetPeak Biz Tech    


Всемогущая функция Query — подробное руководство

Всемогущая функция Query — подробное руководство

< >

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.

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

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

Хочу построить аналитику лидов

Для работы с QUERY вам понадобятся базовые знания SQL. Для тех, кто не в курсе: пугаться не надо, функция QUERY на самом деле поддерживает самые простые возможности SQL.

Синтаксис QUERY

QUERY(данные; запрос; [заголовки])

Где:

  • данные — это диапазон ячеек, который будет служить базой данных для SQL-запроса;
  • запрос — текст SQL-запроса;
  • заголовки — необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.

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

В Google Docs, копию которого вы только что создали, существует несколько листов. Лист DB — это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL-запроса в функции Query

Любой SQL-запрос состоит из отдельных блоков, которые часто называют кляузами. В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:

  • select — перечисление полей, которые будут возвращены запросом;
  • where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обpaбатываемый запросом;
  • group by — содержит перечень полей, по которым вы хотите группировать результат;
  • pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
  • order by — отвечает за сортировку результатов;
  • limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
  • offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обpaбатывать запросом;
  • label — данная кляуза отвечает за название полей, возвращаемых запросом;
  • format — отвечает за формат выводимых данных;
  • options — дает возможность задавать дополнительные параметры вывода данных.

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

=query(DB!A1:L1143;\"select * limit 100\")

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100» содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100» мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order. Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select.

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

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

  • поле Date — столбец A;
  • поле Campaign — столбец B;
  • поле Sessions — столбец G.

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

Select A, B, G

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

В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date.

Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

WHERE (A >= date\\\'2015-10-22\\\' AND A <= date\\\'2015-10-25\\\') AND (B = \\\'Campaign_1\\\' OR B = \\\'Campaign_2\\\')

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

=query(DB!A1:L1143;\" Select A, B, G WHERE (A >= date\\\'2015-10-22\\\' AND A <= date\\\'2015-10-25\\\') AND (B = \\\'Campaign_1\\\' OR B = \\\'Campaign_2\\\')\")

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат:

Помимо обычных логических операторов (=, <, >) блок WHERE поддерживает дополнительные операторы фильтрации:

  • contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
  • starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
  • ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
  • matches — соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
  • like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by. По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Order by G desc

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

=query(DB!A1:L1143;\" SELECT A, B, G WHERE (A >= date\\\'2015-10-22\\\' AND A <= date\\\'2015-10-25\\\') AND (B = \\\'Campaign_1\\\' OR B = \\\'Campaign_2\\\') ORDER BY G DESC\")

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Переходим на лист Level_3 и усложняем задачу. В запросах вы можете не только делать выборки, но также проводить различные вычисления и агрегации данных. Для этого в SQL функции Query существует ряд агрегирующих функций и кляуза Group by. Агрегирующие функции:

ФункцияОписаниеПоддерживаемый тип данныхВозвращаемый тип данных
avg()Возвращает среднее значение для группыЧисловойЧисловой
count()Возвращает количество значений в группеЛюбойЧисловой
max()Возвращает максимальное значение для группыЛюбойАналогичный полю, к которому применяется
min()Возвращает минимальное значение для группыЛюбойАналогичный полю, к которому применяется
sum()Возвращает сумму значений в группеЧисловойЧисловой

Итак, давайте посчитаем данные по каждой кампании:

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

Для решения этой задачи нам понадобятся данные только из двух полей: Campaign (находится в столбце B) и Sessions (находится в столбце G). Все агрегирующие функции прописываются вместе со списком полей для вывода данных в кляузе Select. В случае применения агрегирующих функций все поля, к которым не применяется этот тип функций, являются группирующими полями. Их необходимо перечислить в кляузе Group by. Агрегирующие функции работают обязательно в паре с Group by. Описание кляузы Select будет следующим:

SELECT B, avg(G), max(G), min(G), count(G), sum(G)

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

Описание кляузы Group by очень простое:

GROUP BY B

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

=query(DB!A1:L1143;\"SELECTB,avg(G),max(G),min(G),count(G),sum(G)GROUP BY B\")

Получим следующий результат: В принципе, мы получили желаемый результат, но названия столбцов можно подкорректировать с помощью кляузы Label. Результат будет лучше отображаться, если мы отсортируем отчет по названию кампании. Описание кляузы Order by мы уже рассмотрели выше.

Для нужной сортировки достаточно добавить следующую строку после описания:

Order by B

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

Это будет выглядеть так:

LABEL B ‘Кампания’, avg(G) ‘Среднее’, max(G) ‘Максимальное’, min(G) ‘Минимальное’, count(G) ‘Количество’, sum(G) ‘Общая сумма’

Преобразованная формула:

=query(DB!A1:L1143;\" SELECT B, avg(G), max(G), min(G), count(G), sum(G) GROUP BY B ORDER BY B LABEL B \\\'Кампания\\\', avg(G) \\\'Среднее\\\', max(G) \\\'Максимальное\\\', min(G) \\\'Минимальное\\\', count(G) \\\'Количество\\\', sum(G) \\\'Общая сумма\\\'\")

А результат, возвращаемый формулой, выглядит так:

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

Округлим числа в столбце «Среднее» до двух знаков после запятой. Для округления выводимых данных до двух знаков после запятой маска должна выглядеть как ‘0.00’.

Описание кляузы Format

FORMAT avg(G) ‘0.00’

Соответственно, окончательная формула на листе Level_3 выглядит так:

=query(DB!A1:L1143; \"SELECT B, avg(G), max(G), min(G), count(G), sum(G) GROUP BY B ORDER BY B LABEL B \\\'Кампания\\\', avg(G) \\\'Среднее\\\', max(G) \\\'Максимальное\\\', min(G) \\\'Минимальное\\\', count(G) \\\'Количество\\\', sum(G) \\\'Общая сумма\\\' FORMAT avg(G) \\\'0.00\\\'\")

В результате:

Создание перекрестных таблиц (Pivot, скалярные функции)

Чтобы за считанные секунды с помощью функции QUERY создать перекрестную таблицу, следует добавить в запрос описание кляузы Pivot. Построим отчет, в котором в строках будет номер дня недели, в столбцах — тип устройства, а в качестве выводимых значений рассчитаем показатель отказов. Если вы внимательно изучили структуру базы данных, находящейся на листе DB, то наверняка заметили, что у нас нет поля, содержащего информацию о дне недели, как и поля, содержащего информацию о показателе отказов.

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

Скалярные функции

На момент написания статьи SQL в Google Таблицах поддерживает 14 скалярных функций.

ФункцияОписание
year()Возвращает год из «даты» или «даты и времени». Пример: year(date ‘2009-02-05’) вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
month()Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, февраль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date ‘2009-02-05’) вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату прибавьте 1, month(date \"2009-02-05\")+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
day()Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date ‘2009-02-05’) вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
hour()Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday ‘12:03:17\\\') вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
minute()Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday ‘12:03:17\\\') вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
second()Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday ‘12:03:17\\\') вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
millisecond()Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday ‘12:03:17.123\\\') вернет 123. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
quarter()Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответственно, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date ‘2009-02-05’) вернет 1. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
dayOfWeek()Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date ‘2015-11-10’) вернет 3, так как 10 ноября 2015 года — вторник. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
now()Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров. Тип возвращаемых данных: дата и время.
dateDiff()Возвращает разницу в днях между двумя датами. Пример: dateDiff(date ‘2008-03-13’ , date ‘2008-02-12’) вернет 29, так как 10 ноября 2015 года вторник. Запрашиваемые параметры: два параметра с типом «дата» или «дата и время». Тип возвращаемых данных: число.
toDateВозвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример:
  • toDate(date ‘2008-03-13’) вернет аналогичное значение в формате даты, ‘2008-03-13’.
  • toDate(dateTime‘2013-03-13 11:19:22’) вернет дату ‘2013-03-13’.
  • toDate(1234567890000) вернет дату ‘2009-02-13’.
Запрашиваемые параметры: один параметр с типом дата, дата и время или число. Тип возвращаемых данных: дата.
upper()Преобразует все значения в строке в верхний регистр. Пример: upper( ‘foo\\\') вернет строку ‘FOO’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.
lower()Преобразует все значения в строке в нижний регистр. Пример: upper( ‘Bar\\\') вернет строку ‘bar’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.

Арифметические операторы

ОператорОписание
+Сложение нескольких числовых значений
-Разница между числовыми значениями
/Деление числовых значений
*Умножение числовых значений

Для решения нашей задачи потребуется использовать скалярную функцию dayOfWeek для вычисления дня недели, а также арифметический оператор «/» для подсчета показателя отказов.

Давайте определим поля, которые будем использовать в запросе:

  1. Для вычисления дня недели нам потребуется данные поля Date в столбце A.
  2. Данные о типах устройств хранятся в поле Device category в столбце E.
  3. Для расчета показателя отказов потребуются данные полей Bounces и Sessions — в столбцах H и G.

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

SELECT dayOfWeek(A), sum(H)/sum(G)

Именно так будет выглядеть описание нужных нам полей. Теперь с помощью кляузы Group by сгруппируем строки по дням недели. Для этого допишем в запрос следующую строку:

GROUP BY dayOfWeek(A)

Уже видно, как меняется показатель отказов в зависимости от дня недели:

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

PIVOT E

Теперь запрос возвращает результат:

Нам остается только добавить последние штрихи: изменить названия столбцов и формат чисел с помощью пунктов LABEL и FORMAT.

Окончательная формула на листе Level_4:

=query(DB!A1:L1143;\" SELECT dayOfWeek(A), sum(H)/sum(G) GROUP BY dayOfWeek(A) PIVOT E LABEL dayOfWeek(A) \\\'День недели\\\', sum(H)/sum(G) \\\'\\\' FORMAT sum(H)/sum(G) \\\'0.00%\\\'\")

Получаем отчет:

Строкой LABEL sum(H)/sum(G) ‘’ мы убрали из подписей столбца надпись, содержащую формулу расчета. Строкой FORMAT sum(H)/sum(G) ‘0.00%’ мы передали процентный формат показателю отказов в отчете. В целом, описанного в примерах выше синтаксиса вполне достаточно, чтобы начать активно использовать функцию QUERY, но в завершении статьи хочу показать еще несколько интересных приемов, которые можно взять на вооружение.

Импорт данных с помощью QUERY из другой Google Таблицы

С помощью QUERY вы можете использовать в качестве базы данных другую Google Таблицу. Это можно сделать с помощью сочетания функций ImportRange и QUERY. Я создал новую Google Таблицу, в которую продублировал данные с листа DB из приведенного в начале статьи документа. Чтобы в качестве базы данных использовать данные из другой Google таблицы, в качестве первого аргумента функции Query выступит импортируемый функцией ImportRange диапазон.

Разница в том, что при написании запроса к данным, импортируемым функцией ImportRange, вместо названия столбцов мы указываем их порядковый номер в возвращаемом функцией ImportRange диапазоне. На листе DataImport перепишем запрос, представленный в Level_4 таким образом, чтобы он обращался к данным, находящимся в новой таблице на листе DB_Transfer. Синтаксис функции ImportRange достаточно прост:

IMPORTRANGE(ключ, диапазон)

Где ключ — часть URL Google Таблицы:

А диапазон — это ссылка на лист и (простите за каламбур) диапазон. В нашем случае диапазоном будет DB_Transfer!A1:L1143. Формула ImportRange:

importrange(“1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo”;”DB_Transfer!A1:L1143”)

Именно ее мы должны указать в качестве данных функции Query. Далее остается переписать запрос так, чтобы ссылаться на столбцы базы данных не по названию, а по порядковому номеру столбца. Определим, к каким столбцам мы обращались с помощью запроса на листе Level_4.

НазваниеСодержаниеНаименование в таблицеПорядковый номер
DateДатаA1
Device typeТип устройстваE5
SessionsКоличество сеансовG7
BouncesКоличество отказовH8

Текст запроса после замены названий столбцов на их порядковый номер:

SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7) GROUP BY dayOfWeek(Col1) PIVOT Col5 LABEL dayOfWeek(Col1) \\\'День недели\\\', sum(Col8)/sum(Col7) \\\'\\\' FORMAT sum(Col8)/sum(Col7) \\\'0.00%\\\'

Как видите, текст запроса пpaктически не изменился, но вместо столбца A мы теперь указываем Col1, вместо столбца E — Col5, вместо G — Col7 и вместо H, соответственно, Col8. Получаем формулу:

=query(IMPORTRANGE(\"1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo\";\"DB_Transfer!A1:L1143\") ;\" SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7) GROUP BY dayOfWeek(Col1) PIVOT Col5 LABEL dayOfWeek(Col1) \\\'День недели\\\', sum(Col8)/sum(Col7) \\\'\\\' FORMAT sum(Col8)/sum(Col7) \\\'0.00%\\\'\")

В качестве первого аргумента функции Query выступает функция ImportRange с ссылкой на ключ нужной Google таблицы, которую вы можете скопировать из URL Google Таблицы, и ссылки на диапазон, включающий название листа, а также первой и последней ячейки нужного диапазона.

Номера столбцов в запросе идут не со столбца A, а с того, который является первым в указанном вами диапазоне в функции ImportRange. Например, если бы в качестве импортируемого диапазона выступал DB_Transfer!C1:L1143, то данные из столбца C запрашивались ссылкой Col1, поскольку в импортируемом массиве этот столбец — первый.

Окончательную формулу вы можете посмотреть на листе DataImport.

Строим запрос на основе объединения данных из нескольких таблиц с одинаковой структурой

Ещё одна довольно мощная возможность функции QUERY — построение запрос на основе нескольких массивов данных.

Единственное условие для объединения данных — одинаковая структура входящих таблиц.

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

Массив — это виртуальная таблица, которая содержит строки и столбцы.

Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:

  • обратная косая черта «\» — разделяет столбцы. Например, {1 \ A}. Число 1 будет находится в верхней левой ячейке массива, буква «A» в ячейке справа. Так мы описали диапазон, содержащий два столбца и одну строку.
  • точка с запятой «;» используется для перехода на следующую строку. Возьмем {1;A}. Этот массив будет состоять из одного столбца и двух строк, в первой строке будет содержаться значение 1, во второй строке буква «A».

Таким образом вы можете два и более диапазона описать в одном массиве, например:

=query({Table1!A1:B5; Table2!A1:B5; Table3!A1:B5};\"SELECT * WHERE Col2 > 4\")

В данном случае мы обращаемся с запросом к трём диапазонам данных, находящимся на разных листах, объединив их с помощью «;» в массив так, что вторая таблица становится продолжением первой, а третья таблица — продолжением второй.

Посмотрите этот пример по ссылке.

Запрос с динамическими параметрами

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

А в тексте запроса — делать ссылки на ячейки, содержащие нужные данные. Например, мы можем динамически задать диапазон дат, который хотим вывести в динамическую таблицу, либо сделать возможность динамически добавлять и убирать различные поля результирующей таблицы. Посмотреть, как это выглядит, можете на листе DinamicQuery.

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

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

Укажите интересующий вас интервал дат в пределах от 24.09.2015 по 25.10.2015, поскольку данные, сгенерированные для тестовой базы и хранящиеся на листе DB, содержат только этот диапазон.

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

Во время изменения каких-либо параметров отчет под конструктором будет изменяться динамически.

Формула, которая изменяет запрос в зависимости от настроенных параметров, выглядит так:

=query(DB!A1:L1143;\" Select \"&join(\",\";filter(C7:C11;B7:B11=\"Да\"))&\" WHERE (A >= date\\\'\"&C2&\"-\"&D2&\"-\"&E2&\"\\\' AND A <= date\\\'\"&C3&\"-\"&D3&\"-\"&E3&\"\\\') GROUP BY \"&join(\",\";filter(C7:C11;B7:B11=\"Да\";D7:D11=\"Измерение\"))&\" LABEL \"&join(\",\";filter(E7:E11;B7:B11=\"Да\")))

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

Надеюсь, у меня получилось объяснить, как пользоваться одной из наиболее сложных и в тоже время полезных функций Google Таблиц. Если хотите познакомиться со всеми возможностями Google Таблиц, рекомендую обратить внимание на курс «Google Sheets» от Choice31: он будет полезен проджектам, маркетологам, финансовым аналитикам и всем, кто давно собирался разобраться в инструменте.



Комментарии:

Как привлечь дополнительный трафик с помощью SNDS-кампаний — кейсы Netpeak

Как привлечь дополнительный трафик с помощью SNDS-кампаний — кейсы Netpeak Как получить больше конверсионного трафика из поиска и не выйти за рамки KPI? Запустите SNDS-кампании...

22 06 2025 8:53:11

Как успешно вести блог в Instagram

Как успешно вести блог в Instagram Успешный блог помогает продавать товары и услуги, формировать сильный личный бренд, делиться своим опытом и задавать тренды. Осталось выбрать цель. Узнать как!...

21 06 2025 9:34:28

Дополнительные функции Zoom

Дополнительные функции Zoom Как обойти «Зал ожидания» и другие неочевидные фишки...

20 06 2025 22:10:21

Email моей мечты, или Как настроить корпоративную почту

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

19 06 2025 17:12:37

Как мы сделали аналитический инструмент для блога Netpeak

Как узнать, хорошую ли статью вы опубликовали в блоге...

18 06 2025 23:36:48

Эффективна ли стратегия «Целевая цена за конверсию»? Исследование Netpeak

Эффективна ли стратегия «Целевая цена за конверсию»? Исследование Netpeak Стоит ли подключать автоматические стратегии управления ставками? Мы проверили, как работает «Целевая цена за конверсию» в разных тематиках....

17 06 2025 7:52:22

Cемь улучшений Netpeak: бизнес-кластер, производство баннеров и более 20 разовых услуг

Cемь улучшений Netpeak: бизнес-кластер, производство баннеров и более 20 разовых услуг Решили составить список из семи улучшений. Что нового, Netpeak?...

16 06 2025 10:45:36

Правильный выбор площадки для продвижения бренда в социальных сетях

Правильно выбранная соц. площадка поможет раскрутить бизнес эффективно....

15 06 2025 1:54:12

Как продвигать премиум-продукты — аутрич кейс бренда High-End акустики Copra

Как продвигать премиум-продукты — аутрич кейс бренда High-End акустики Copra Кейс о том, как написать большинству дилеров премиальных акустических систем в Европе и ничего не продать. И почему это кейс, а не антикейс....

14 06 2025 12:52:34

Как бесплатно защитить книгу от копирования — инструкция для писателей

Как бесплатно защитить книгу от копирования — инструкция для писателей Как обезопасить себя от интернет-пиратов и защитить авторское право онлайн....

13 06 2025 10:58:11

Как отслеживать работу PBN и оперативно реагировать на изменения

Как отслеживать работу PBN и оперативно реагировать на изменения Как контролировать множество сайтов — полезные сервисы и советы для вебмастеров. Одно дело — несколько десятков сайтов, а что если сотни или тысячи? Как раз это подробно и разберем....

12 06 2025 20:16:55

Полезные сервисы для оптимизации удаленной работы

Полезные сервисы для оптимизации удаленной работы Обзор полезных сервисов и программ, которые увеличат эффективность работы из дома и улучшат организацию бизнес-комaнды...

11 06 2025 8:20:13

Продвижение мобильных игр — расшифровка терминов и аббревиатур

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

10 06 2025 9:19:43

Как email-рассылки принесли ceкc-шопу 20% дохода

Как email-рассылки принесли ceкc-шопу 20% дохода Кейс выполнимой миссии — отправили рассылку по «холодной» базе и обошли запреты почтовых сервисов на изображения 18+...

09 06 2025 3:22:21

Сколько стоил клик в Google Ads и Яндекс.Директ в Казахстане в четвертом квартале 2020 года — исследование Netpeak

Сколько стоил клик в Google Ads и Яндекс.Директ в Казахстане в четвертом квартале 2020 года — исследование Netpeak Данные Google по 6 миллионам кликов 16 городах страны и данные Яндекс по 520 346 кликам, в 54 городах страны в 22 тематиках в обоих рекламных системах. Узнать больше!...

08 06 2025 15:27:58

Контекстная реклама — что это, как выглядит, как работает.

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

07 06 2025 10:54:41

MVP в разработке позиционирования стартапа — на примере Serpstat

MVP в разработке позиционирования стартапа — на примере Serpstat Метод бoмжа в маркетинге. Как разработать позиционирование стартапа при ограниченных ресурсах...

06 06 2025 17:43:22

Как я работаю дома с двумя детьми — хроники безумного папы

Как я работаю дома с двумя детьми — хроники безумного папы Опыт сотрудника отдела маркетинга Netpeak. Дочке Маше — 10 лет, сыну Егору — почти 4 года. Мы по-прежнему не очень продуктивны в таких условиях, но все же накопили больше опыта, чем те люди, которые сейчас внезапно столкнулись с удаленной работой...

05 06 2025 17:31:48

Как работать с Excel в рекламной кампании — дайджест публикаций Алексея Селезнева

Дайджест-путеводитель по серии постов о работе с редакторами электронных таблиц для PPC-специалистов....

04 06 2025 21:27:56

Партнерские программы SaaS — как на них заработать, какими бывают партнерки

Партнерские программы SaaS — как на них заработать, какими бывают партнерки Партнерская программа — это когда сервис дает партнеру вознаграждение за приведенных клиентов. Получить деньги и/или другие плюшки можно также по реферальной программе. Как это работает? Разбираемся на примерах....

03 06 2025 17:21:40

Как правильно зарегистрировать сайт в поисковых системах Google, Яндекс, Bing, Mail.ru, Rambler и Yahoo

Как правильно зарегистрировать сайт в поисковых системах Google, Яндекс, Bing, Mail.ru, Rambler и Yahoo Бесплатная регистрация сайта в поисковых системах и каталогах – отличный способ увеличить объемы трафика и раскрутить собственный ресурс....

02 06 2025 17:13:49

Краткое руководство по системному управлению проектами в PPC

Краткое руководство по системному управлению проектами в PPC Как специалисту оптимизировать рабочее время, качественно развивать проекты и меньше нервничать...

01 06 2025 18:53:36

10 лучших книг о маркетинге

Это не автобиографические романы, не бесплатные учебники и не подборка книжек, чтобы скачать в epub. Это интересная топ литература о маркетинге, которую мы хотим вам посоветовать....

31 05 2025 14:24:56

Как вернуть органический трафик после неправильного переноса сайта

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

30 05 2025 0:31:32

Кейс avtokrisla.com: триггерное письмо «Заказ оформлен» как дополнительный источник прибыли

Кейс avtokrisla.com: триггерное письмо «Заказ оформлен» как дополнительный источник прибыли Многие недооценивают триггерное письмо после оформления покупки, а зря. И вот почему....

29 05 2025 21:22:31

Кейс по продвижению автомобильных шин: реклама в Google Ads и прайсах (Яндекс.Маркет и Hotline): ROMI 104%

Кейс по продвижению автомобильных шин: реклама в Google Ads и прайсах (Яндекс.Маркет и Hotline): ROMI 104% Объявление в тематике автошин — отличная тема для рекламы в интернете....

28 05 2025 7:22:12

Продвижение мобильного приложения — шпаргалка для строителей воронок продаж

Продвижение мобильного приложения — шпаргалка для строителей воронок продаж С помощью этой шпаргалки вы легко распределите акценты и начнете последовательно внедрять маркетинговую стратегию по продвижению мобильного приложения...

27 05 2025 15:14:22

Кукла с протезом, кальвадос Ремарка и страхование дворца: чем радовала и шокировала реклама соцсетей в январе 2021-го

Кукла с протезом, кальвадос Ремарка и страхование дворца: чем радовала и шокировала реклама соцсетей в январе 2021-го Какая интересная и трэшовая реклама встречалась в соцсетях в январе 2021-го? Давайте смотреть...

26 05 2025 15:15:55

Внутренняя кухня SEO: как Netpeak продвигает UniSender — первые 5 шагов

Внутренняя кухня SEO: как Netpeak продвигает UniSender — первые 5 шагов Начинаем серию постов в режиме реалити-шоу о подробностях продвижения крупного игрока бизнеса email- и sms-рассылок — компании UniSender....

25 05 2025 23:18:27

Что нужно понимать в SEO-отчетах, чтобы оценить эффективность продвижения

Что нужно понимать в SEO-отчетах, чтобы оценить эффективность продвижения Всё об оценке эффективности SEO-продвижения с инструментами и рекомендациями...

24 05 2025 0:35:42

Зачем нужен медиаплан и как его составить?

Зачем нужен медиаплан и как его составить? Чтобы и клиенту не стыдно было показать и свои ожидания не обмануть....

23 05 2025 1:46:35

Рэнд Фишкин: о трендах SEO и работе волшебника страны Moz

Рэнд Фишкин: о трендах SEO и работе волшебника страны Moz Экс-CEO, а теперь просто сотрудник и «волшебник страны Moz» Рэнд Фишкин поделился с читателями блога рассказом о своем видении будущего SEO, перспективах анонимизации сети и причудах американских клиентов....

22 05 2025 13:48:38

Анонимный покупатель на страже роста продаж — опыт агентства Netpeak

Анонимный покупатель на страже роста продаж — опыт агентства Netpeak И эксперт, и агент. Анонимный покупатель — специальная проверка бизнес-процессов до и во время рекламной кампании...

21 05 2025 20:46:17

Кейс по таргетированной рекламе в тематике «одежда, обувь, аксессуары»: ROMI 348%

Кейс по таргетированной рекламе в тематике «одежда, обувь, аксессуары»: ROMI 348% Женский и мужской таргет отличаются друг от друга. Кейс магазина обуви, одежды и аксессуаров....

20 05 2025 19:42:25

Как стать популярным в Instagram: инфографика

Как стать популярным в Instagram: инфографика Какой контент публиковать в Instagram, зачем ставить хештеги, какие активности интересны пользователям инсты, как ведут свой Инстаграм топовые бренды. Узнать больше!...

19 05 2025 1:22:39

Рекламный кабинет в Facebook — пять фишек для упрощения работы

Рекламный кабинет в Facebook — пять фишек для упрощения работы Возможности для специалистов по рекламе в Facebook, о которых знают далеко не все. Подробности — тут....

18 05 2025 21:44:26

Что нужно сделать перед созданием интернет-магазина

Что нужно сделать перед созданием интернет-магазина Планируете создание бизнеса, связанного с торговлей в интернете? Сначала прочитайте этот текст....

17 05 2025 22:27:53

Как вернуть украденный аккаунт Instagram

Как вернуть украденный аккаунт Instagram Короткий кейс и рекомендации по защите аккаунта от взлома....

16 05 2025 13:46:42

Как сделать в Google Web Designer анимированные баннеры для рекламных кампаний

Как сделать в Google Web Designer анимированные баннеры для рекламных кампаний Простая инструкция для новичков, как легко создать анимированные баннеры для рекламных кампаний с помощью бесплатного инструмента Google Web Designer. При создании баннера сервис предложит создать файл с нуля либо использовать шаблон. Узнайте обо всех возможностях!...

15 05 2025 15:17:39

Реклама в YouTube для интернет-магазина обуви — увеличиваем узнаваемость бренда и продажи

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

14 05 2025 8:51:19

Как продвигать мобильное приложение: опыт Андреаса Рётля на примере Journi

Как продвигать мобильное приложение: опыт Андреаса Рётля на примере Journi Андреас Рётль о продвижении Journi: как трое выпускников Стэнфордского университета создали приложение для сторителлинга с 25 000 пользователями в 100 странах....

13 05 2025 14:24:53

Как получить 184% прироста трафика и увеличить долю поискового трафика в три раза в gaming-тематике — кейс Wowcarry

Как получить 184% прироста трафика и увеличить долю поискового трафика в три раза в gaming-тематике — кейс Wowcarry Доля органического трафика увеличилась с 14% до 44%, а небрендового — на 184%....

12 05 2025 18:37:19

IOS 14: Что это значит для рекламодателей Facebook

IOS 14: Что это значит для рекламодателей Facebook Реклама будет не настолько персонализирована, как сейчас....

11 05 2025 21:48:54

Как работает Brand Lift — исследуем эффективность видеорекламы на YouTube

Как работает Brand Lift — исследуем эффективность видеорекламы на YouTube Недавно у меня закончилась видеокампания на ютуб, по которой удалось собрать исследование Brand Lift. Хочу поделиться инсайтами...

10 05 2025 5:39:57

Репутация компании: зачем вам Online Reputation Management

Репутация компании: зачем вам Online Reputation Management Идея ORM состоит в том, чтобы вызывать положительное мнение о вашей деятельности, работая с отзывами или комментариями, в результате чего больше людей будут покупать ваши товары или услуги. Узнать больше!...

09 05 2025 13:27:55

SEO для магазина лакокрасочных и строительных материалов — как мы увеличили объем бесплатного трафика на 463%

SEO для магазина лакокрасочных и строительных материалов — как мы увеличили объем бесплатного трафика на 463% Эта история о том, как за два года комплексный подход к поисковому продвижению (SEO на этапе разработке сайта и дальнейшие действия по оптимизации) помог увеличить рост бесплатного трафика на 463% или Что делать, если SEO не приносит результаты сразу...

08 05 2025 5:16:30

Как связать Яндекс.Директ с Microsoft Power BI

Чтобы создавать удобные отчеты, импортируйте данные из Яндекс.Директ в Microsoft Power BI с помощью язык R...

07 05 2025 21:27:44

Кейс по контекстной рекламе портала-каталога организаций: как оптимизация целевых страниц повлияла на поведенческие факторы

Кейс по контекстной рекламе портала-каталога организаций: как оптимизация целевых страниц повлияла на поведенческие факторы Портал-каталог организаций и его продвижение. В этом кейсе мы детальнее остановимся на выгодах для проекта от синергии усилий клиента и агентских специалистов по контекстной рекламе...

06 05 2025 16:52:25

40+ сервисов для работы с текстом — для копирайтеров, редакторов и других создателей контента

40+ сервисов для работы с текстом — для копирайтеров, редакторов и других создателей контента Подборка онлайн-платформ и программ для работы с текстом и изображениями....

05 05 2025 22:46:47

Лучшие дудл-игры от Google для обеденного перерыва

Лучшие дудл-игры от Google для обеденного перерыва Топ doodle games от Google — от менее достойных к самым крутым....

04 05 2025 0:56:41

Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::