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

Рубрики:
Комментарии
18 Цепочка комментария
33 Ответы по цепочке
0 Последователи
 
Популярнейший комментарий
Цепочка актуального комментария
20 Авторы комментариев
Tim

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

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

Tim

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

Дмитрий

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

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

Dmitry

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

EN

Сергей, если никаких критериев к которым можно привязаться нет (например порядкового номера записи) то можно сделать так: 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 строка будет… Подробнее »

Евгений

Дбрый вечер Ренат.
Делаю простой калькулятор в таблице гугл.
Необходимо подтянуть стоимость из прайс-листа которое отвечает введенным данным.
Пробую вот такую конструкцию:
=ГПР(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. Но у меня ничего не получилось.

Pavel

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

Анатолий

Добрый вечер , подскажите, пожалуйста, как можно сделать, есть 5-10 столбиков с названием первой ячейки "загрузка", как эти все данные собрать в один столбец. Например разгрузка и вниз пошли адреса, в другом столбце так же, уже другие адрреса...

Ренат Шагабутдинов

Анатолий, можно, например, формулой массива такого вида:
={C1:C3;D1:D3}
См. скриншот: http://prntscr.com/hxwp3h

Анатолий

Ренат, спасибо , конечно, но я немного не правильно выразил свою мысль. Путем формы всегда добавляются новые строки. При добавлении в форме нового заказчика появляются новые столбцы "загрузка", 2 "разгрузка". При добавлении нового заказа происходит добавление новых строк. так вот все загрузки должны собираться в одном столбце, а разгрузки во втором https://docs.google.com/spreadsheets/d/1MGGYpXnQLT1ck_oMCTCr07xOdGSWwkA2-fIrxLUyR4c/edit#gid=704999679. Так вот нужна формула , которая автоматически, при появлении нового столбца с первой строкой загрузка, брала оттуда данные и помещала в столбец все загрузки.

Ренат Шагабутдинов

Анатолий, можно вот такой функцией:
=sort({ЕСЛИ(H1="Загрузка";H2:H;"");ЕСЛИ(J1="Загрузка";J2:J;"")};1;1)

Что не есть очень-то изящное решение, конечно, потому что надо заранее прописывать ЕСЛИ для каждого столбца в файле, и если их будет очень много, то формула будет длинной.
Но пока не придумалось, как это автоматизировать, еще поразмышляю.
Ссылка на формулу:
https://docs.google.com/spreadsheets/d/1_w15jFZWrxFgkB2exW8lJSmN0k3u_Bf2_tCIGAoRk4I/edit#gid=704999679&range=F16

Анатолий

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

Анатолий

Форма для ввода данных заказа https://docs.google.com/forms/d/15CHBpNEDmjV815DEm50I_ZcbjOPFI9qdtTQQSF4Ixyk/edit

Елена

Уважаемый Ренат, добрый день!

Вы не могли бы подсказать, как можно в гугл-таблице сгруппировать несколько колонок (как в Exel)?
Вычислять ничего не нужно, просто таблица с текстовыми очень большая, и не все колонки нужны всё время. Скрывать не очень удобно в таких случаях.

Ренат Шагабутдинов

Елена, группировки столбцов пока нет.
Можно на отдельном листе с помощью QUERY вывести часть столбцов, которые нужны.

Ренат Шагабутдинов

Елена, добрый день.
Наконец-то группировка появилась, как и несколько других новых опций. Подробнее мы писали в этом обзоре: https://medium.com/@grclubpoker/в-этой-статье-детально-разберем-a579c5a41d71

Игорь

Добрый день! Возможно ли "прикрутить" функцию QUERY к выпадающему списку (точнее, к проверке вводимых данных, при помощи которой такой список создается)? Очень хочется показывать пользователю понятное ему текстовое описание вводимого значения, а вставлять в итоге в ячейку само значение, что удобно для логики приложения. Попытка решить "в лоб", выбрав правило "Ваша формула", ничего не дала - это правило не подразумевает создания выпадающего списка, только проверку. Попытка выбрать правило "Диапазон значений", охватив этим диапазоном оба столбца (например, ID и Description), дает в выпадающем списке чересполосицу типа: id1 descr1 id2 descr2 ... причем, выбрать можно любую из строк. В Access подобная задача… Подробнее »

Ренат Шагабутдинов

Игорь, боюсь, никак красиво в Таблицах это не решить:
- Проверка данных действительно не переваривает формулы (возможно, пока);
- в два столбца тоже не получится. Если только через "костыли" делать: пользователю давать выбирать Description из одного столбца, а в соседнюю ячейку тянуть ВПР или ИНДЕКС+ПОИСКПОЗ ID, который потом будет как-то использоваться в других формулах... Но это совсем не элегантно, увы.

Иван

Привет!

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

Вот как тут: ' " &A1& " '

Почему мы просто не можем напрямую обратится к ячейке, а заносим ее в формулу вот таким образом ' " & ?

Спасибо!

Татьяна

Привет! Почему-то select выбирает только один столбец, если указать select * или select A, B , то формула не работает, выдаёт ошибку. В чем может быть причина?

Андрей

Добрый день
Вывожу в одну таблицу 3 массива (с 3 листов), отбирают некоторые столбцы и сортирую по убыванию по сумме сделки
При этом, хочу сам задавать сумму сортировки, то есть чтобы выводились сделки по сумме больше величины, подписанной в ячейке
=QUERY({'Петров'!A:J;'Иванова'!A:J;'Евсеева'!A:J};"SELECT Col1,Col3,Col2,Col5,Col7,Col8 where Col7 > '"J1"' ORDER BY Col7 DESC")
Col7 это столбец с суммой, а J1 ячейка на сводном листе, куда хочу вводить цифру - больше чем например 500000, но получаю ошибку.
Подскажите, а чем проблема?

Николай

Ренат, добрый день. Вопрос: если низ массива заранее неизвестен, и нужно получить его значение из ячейки, какой синтаксис использовать.
Например, =QUERY('Книги'!A1:C'"&D4&"'......
В D4 находится число для определения низа диапазона. Через приведенную конструкцию не работает...

Ренат Шагабутдинов

Николай, добрый день! Ну вы, судя по следующим комментариям, уже свой вопрос решили 😉 Странно ожидать мгновенного ответа в комментариях к статье, конечно.
Но другим может быть полезно, так что напишу, что задача решается через ДВССЫЛ / INDIRECT - функцию, которая превращает текстовую строку в ссылку на диапазон. Собираем ссылку из текста и ссылки на ячейку с числом строк в диапазоне в INDIRECT:
INDIRECT("A1:C"&D4)
И потом используем это в QUERY:
=QUERY(INDIRECT("A1:C"&D4); "Select ...")

Николай

Вопрос решил самостоятельно, пока автор что-то ответит 10 раз сам сделаешь

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