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

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

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

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

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

  • данные — это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
  • запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
  • заголовки — количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

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

  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 так:

  • QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
  • перед датой и перед апострофом нужно написать date;
  • можно и не делать ссылку на ячейку с датой, а написать ее сразу в 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”)

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

  • ‘Книги’!A1:C13 — исходный диапазон, таблица с продажами, книгами и тематиками.
  • SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
  • WHERE B = ‘”&A1&”‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
  • ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.

Результат:

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

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

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

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

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

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

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

Канал “Google Таблицы – это просто” в Телеграме

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

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

https://telegram.me/google_sheets

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

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

  2. Дмитрий says:

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

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

  3. Добрый день, подскажите как транспонировать табличку вида:
    Параметр Значение
    параметр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 штуки – не вариант
    Спасибо

      • Спасибо огромное! А группировка в гугл шитс по значениям столбца не подскажете как делается с подсчетом кол-ва значений?
        Например:
        значение1,
        значение2
        значение1
        значение3

        Должно выглядеть так:
        Значение1 – 2
        Значение2 – 1
        Значение3 – 1

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

          Дмитрий, а чем вас обычная сводная таблица не устраивает в данном случае?
          Она, как и в Excel, поможет сделать такую группировку по кол-ву.

          • Ренат а можете порекомендовать статью по группировке данных? Я только осваиваю эти функции.

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

            У меня пока по сводным ничего нет (да и в Google Таблицах они довольно простые). Можно посмотреть информацию о сводных в книгах и на сайтах по Excel, но я бы порекомендовал сразу попробовать построить сводную в Таблицах. Там все очень просто — на практике быстро разберетесь.

  4. Артем says:

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

    • Артем says:

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

    • Артем says:

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

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

      Артём, вопросы:
      – почему бы не сделать QUERY по A:P?
      – у вас числовые или текстовые данные?

      • Артем says:

        Ренат, не делаю A:P потому что слишком много лишних данных. Обрабатывать необходимо числовые значения

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

          Но вы же не будете грузить эти лишние данные с помощью QUERY.
          У меня бывают QUERY по диапазонам вроде A1:CZ. Все работает нормально, беру только те столбцы, которые нужны, на какие-то накладываю условия etc.

  5. Сергей says:

    Добрый день, Ренат. Подскажите, пожалуйста, как составить запрос 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 строка будет последним занесенным звонком. Для того, чтобы вывести только его, мы совмещаем формулу ИНДЕКС, СМЕЩ и номер этой строки, который мы посчитали выше через СЧЁТЗ и QUERY.

      =ИНДЕКС(QUERY($A$1:$C$5;”select A,B,C where A=12 and B = date ‘2016-12-12′”);СЧЁТЗ(QUERY($A$1:$C$5;”select A where A=12 and B = date ‘2016-12-12′”)))

      Вроде ничего сложного, попробуйте.

  6. Евгений says:

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

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

      Евгений, так с ходу сложно сказать, вроде бы в формуле все ОК. Точно ли нужен интервальный просмотр = 1 в ГПР’е? Можете сбросить скриншот или просто написать мне на renat@shagabutdinov.ru, посмотрим.

  7. Тахир says:

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

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

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

    Что не так?

  8. Тахир says:

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

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

      Тахир, можете сбросить пример? На словах очень сложно понять.

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

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

      Павел, есть еще OFFSET N, отступающий на N строк от начала. Например, LIMIT 10 OFFSET 100 выведет 101-110 записи из отобранных

      • Спасибо, но отчёт всё равно идёт сначала. Скажем у меня есть форма в которой ведётся учет движения денежных средств, и мне нужно вывести последние 5 операций. Способна ли на это Query?

Оставьте комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

CAPTCHA image
*