QUERY. Функция для создания запросов в Google-Таблицах

Спасибо Евгению Намоконову за помощь в подготовке материала.

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

Синтаксис функции:

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

Итак, правила формирования запросов:

  1. Запрос указывается в кавычках.
  2. В запросе используются ключевые слова:
    1. SELECT — определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
    2. WHERE — ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
    3. GROUP BY — группирует значения по заданным полям.
    4. PIVOT — позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
    5. ORDER BY — задает сортировку. Например: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
    6. LIMIT — ограничивает количество возвращаемых строк. Например: «LIMIT 50».
    7. OFFSET — пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
    8. FORMAT — определяет формат определенных столбцов по заданному шаблону.
    9. LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году'». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».

Справка от Google по языку запросов API находится по ссылке: https://developers.google.com/chart/interactive/docs/querylanguage

Рассмотрим несколько примеров применения QUERY на практике.

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

Из простой исходной таблицы будем формировать список книг по тематике:

При этом тематику будем выбирать из выпадающего списка на отдельном листе:

Функция QUERY для решения этой задачи будет выглядеть следующим образом:

=QUERY(Книги‘!A1:C;«SELECT A, C WHERE B = ‘»&A1&«‘ ORDER BY C DESC»;1)

Мы извлекаем данные из столбцов A и C в диапазоне ‘Книги’!A1:C. Фильтруем данные по столбцу B (тематике) этого диапазона по выбранному критерию из выпадающего списка в ячейке A1. Сортируем по убыванию по столбцу C исходного диапазона и добавляем к нашей выборке заголовки (последний аргумент функции QUERY = 1).

Группируем данные с помощью GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.

Таблица, с которой мы будем работать:

Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.

Начнем с GROUP BY, текст функции будет таким:

=QUERY(‘Книги‘!A1:C6;»select B, sum(C) group by B»)

Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().

Результат нашей формулы:

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

Группировка с помощью PIVOT.

Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы.

Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.

Добавим еще один столбец для группировки.

Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.

Строим сводную таблицу со средними/максимальными значениями по тематикам

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

QUERY(Книги‘!A1:D;«SELECT avg(C), avg(D) pivot B»;1)

Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).

Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):

Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):

Или отобразить и среднее, и максимум, но только по столбцу D:

SELECT avg(D), max(D)

Кейс «Считаем средний чек, выбирая данные с определенной даты»

На скриншоте массив данных, с которым мы будем работать:

Наша задача: отобрать строки с продажами начиная с 1 апреля и посчитать по ним средний чек, используя количество клиентов, то есть получить среднее взвешенное.

Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:

Правильно использовать дату в формуле QUERY так:

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

Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):

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

Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:

Наконец, совмещаем формулы:

Все работает, ура! 53 (этот результат видно на всплывающей подсказке в верхнем левом углу) — средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.

Кейс «QUERY и выпадающий список»

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

Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных.

В Правилах выбираем Значение из списка, перечисляем все наши тематики через запятую и нажимаем Сохранить:

Список получился вот таким:

В соседнюю ячейку А2 впишем следующую формулу:

=QUERY(Книги‘!A1:C13,«SELECT A, C WHERE B = ‘»&A1&«‘ ORDER BY C DESC»)

И разберем ее по частям:

Результат:

Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.

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

Query по нескольким диапазонам данных

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

Диапазоны указываются через точку с запятой в фигурных скобках:

=QUERY({Диапазон 1; Диапазон 2; Диапазон 3; Диапазон 4};…

Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N — номер столбца.

Канал «Google Таблицы — это просто» в Телеграме

Спасибо Евгению Намоконову за помощь в подготовке кейсов для этой статьи. Мы с Евгением ведем канал в Телеграме по Google Таблицам.

Регулярно публикуем новые кейсы и советы. Заглядывайте в гости:

https://telegram.me/google_sheets

Рубрики:
Комментарии

Отправить ответ

Уважаемый Ренат,
Вы не сталкивались с проблемой использования в Query символов «» при работе с датами? Например При запросе выручки в диапазоне дат.

=QUERY (Данные; «select Прибыль, where (дата>01.01.2001)»)

Символов больше и меньше

Ренат, пытаюсь вытянуть целую строку которую необходимо найти по 1му числовому значению из ячейки но мне формула грузит только строку с наименованием столбцов и на этом все. Формула =QUERY(IMPORTRANGE(«»;»‘!A:AJ»);»Select Col1, Col2, Col3 Where Col2=»&C5&»»)

Подскажите, может что-то еще нужно добавить чтобы грузились не только название столбцов но и найденная строка?

Добрый день, подскажите как транспонировать табличку вида: Параметр Значение параметр1 100 параметр2 200 параметр3 300 параметр1 500 параметр2 100 параметр3 200 и.тд 1000 строк по 3 параметра Нужно ее транспонировать по 3 строки вида Параметр1 Параметр2 Параметр3 100 200 300 400 500 600 Транспонировать вручную по 3 штуки — не вариант Спасибо

Добрый день, Ренат. Подскажите пожалуйста как с помощью этой функции вывести значение с «разорванного» интервала, что-то вроде этого:
=unique(QUERY(A:B;»select A where B=’ ‘ and A!=»(QUERY(P:P;»select P»))))
Может быть посоветуете какую другую функцию. Заранее спасибо!

Т.е. мне нужно вывести пустые значения которые есть в столбце А, кроме тех которые уже есть в столбце Р.

Точнее вывести значения столбца А, которым соответствует пустая ячейка столбца B, кроме тех что уже есть в столбце P.

Добрый день, Ренат. Подскажите, пожалуйста, как составить запрос QUERY для следующей задачи.
Есть таблица звонков (Время_звонка, Номер, и несколько различных Параметров).
Требуется получить эти параметры по каждому номеру за какой-нибудь день по последнему звонку (звонков от одного номера в день может быть несколько, а интересует последний). Т.е. получить таблицу: День, Номер, Параметры (от последнего звонка в день).

Сергей, если никаких критериев к которым можно привязаться нет (например порядкового номера записи) то можно сделать так: 1) сначала формулой QUERY, выведем какой-нибудь столбец (например, столбец А) по звонкам на нужный номер (столбец А) за определенный день (столбец В) =QUERY($A$1:$C$5;»select A where A=12 and B = date ‘2016-12-12′») 2) добавляем формулу СЧЁТЗ (COUNTA), чтобы посчитать кол-во выведенных строк =СЧЁТЗ(QUERY($A$1:$C$5;»select A where A=12 and B = date ‘2016-12-12′»)) 3) получившаяся цифра — это кол-во строк в массиве, который выведет формула QUERY по звонкам на определенный номер в определенную дату. Если получится 5, например, то это 5 строк и 5 строка будет… Read more »

Дбрый вечер Ренат.
Делаю простой калькулятор в таблице гугл.
Необходимо подтянуть стоимость из прайс-листа которое отвечает введенным данным.
Пробую вот такую конструкцию:
=ГПР(B7;QUERY(price!A2:M;»select * where A='»&B5&»‘»;1);2;1)
Прай-лист горизонтальный. С помощью QUERY подтягваю нужный диапазон. А ГПР должен вычислить подходящее значение.
Что-то не получается. Где может быть ошибка?

Дбрый вечер Ренат.
Делаю простой калькулятор в таблице гугл.
Необходимо подтянуть стоимость из прайс-листа которое отвечает введенным данным.
Пробую вот такую конструкцию:
=ГПР(B7;QUERY(price!A2:M;»select * where A='»&B5&»‘»;1);2;1)
Прай-лист горизонтальный. С помощью QUERY подтягваю нужный диапазон. А ГПР должен вычислить подходящее значение.
Что-то не получается. Где может быть ошибка?

Ренат, добрый день.

Разбираюсь с QUERY, не так часто приходится с ней сталкиваться, поэтому забывается.
Чтобы освежить, сделал аналог таблицы про книги, которая в 6 строк, и пытаюсь вставить формулу =QUERY(‘Книги‘!A1:C6;”select B, sum(C) group by B”)

но система выдает ошибку #VALUE! c текстом — Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:NO_COLUMN: B

Что не так?

Отбой, нашел ошибку.

Добрый день, все таки есть вопрос.
Можно пример, когда в QUERY мы используем суммирование по 2 параметрам, то есть заменяем формулу SUMIFS?
Пробовал компоновать group by, sum() и where. Но у меня ничего не получилось.

Подскажите как вывести последние строки диапазона. Limit выводит первые строки, может ли он выводить промежуточные или последние?

Темы, на которые я пишу: