Функция QUERY в Google Таблицы

Функция QUERY в Google Таблцы представляет собой мощный инструмент для выполнения запросов к данным в электронных таблицах. С её помощью вы можете извлекать, фильтровать и анализировать информацию из таблиц, используя язык структурированных запросов, который очень похож на SQL (язык запросов API визуализации Google).

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

Простой пример использования функция QUERY в Google Таблицы

Синтаксис

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

=QUERY(data; query; [headers])

Список аргументов

Данные (data) - диапазон ячеек, который вы хотите запросить.

Дапрос (query) - запрос, написанный на языке запросов API визуализации Google, аналогичном SQL.

[Заголовки] ([headers]) - необязательный параметр, указывающий, есть ли заголовки столбцов в вашем диапазоне, значение по умолчанию -1.

Возвращаемое значение

Диапазон данных, соответствующих условиям запроса.

Пример

Давай на практике посмотрим пару примеров, как работает функция QUERY в Google Таблицы. Для начала подготовим таблицу с данными к которым мы будем обращаться с помощью нашей функции.

Таблица с данными к которым мы будем обращаться с помощью функции QUERY в Google Таблицы

Создадим новый лист (Лист2), и напишем наш первый запрос используя один оператор SELECT. Запросм будет звучать: выбери столбцы Артикул, Наименование, Наличие и Цена.

=QUERY('Лист1'!A:H;"SELECT F, G, E, H";-1)

Пример использования функции QUERY в Google Таблицы с использование оператора SELECT

Давайте дополним наш запрос оператором WHERE: выбери столбцы Артикул, Наименование, Наличие и Цена, где наличие больше 50.

=QUERY('Лист1'!A:H;"SELECT F, G, E, H WHERE E > 50";-1)

Пример использования функции QUERY в Google Таблицы с использование оператора SELECT и WHERE

Давайте дополнительно отсортируем получаемы данные на предыдущем шаге по убыванию с помощью оператора ORDER BY (значение по возрастанию ASC, по убыванию DESC): Выбери столбцы Артикул, Наименование, Наличие и Цена, где наличие больше 50 и сортировку сделай по полю наличие по убыванию.

=QUERY('Лист1'!A:H;"SELECT F, G, E, H WHERE E > 50 ORDER BY E DESC";-1)

Пример использования функции QUERY в Google Таблицы с использование оператора SELECT, WHERE и ORDER BY

Мы поняли, что нам не нужны все записи, а необходимы только 10 строк с максимальным количеством(Наличие), для этого нам поможет оператор LIMIT: Выбери столбцы Артикул, Наименование, Наличие и Цена, где наличие больше 50 и сортировку сделай по полю наличие по убыванию ограничение 10 записей.

=QUERY('Лист1'!A:H;"SELECT F, G, E, H WHERE E > 50 ORDER BY E DESC LIMIT 10";-1)

Пример использования функции QUERY в Google Таблицы с использование оператора SELECT, WHERE, ORDER BY и LIMIT

Мы видим, что столбец с количеством, как-то по странному назван "Наличие", давайте его переименуем в "Кол-во". Нам для этого потребуется оператор LABEL, но тут есть нюанс, LABEL не работает с выборкой определенных полей. Нам в начале надо выбрать все поля, переименовать, как нам необходимо, а потом с помощью второй вложенной функцией QUERY по идентификаторам столбцов (идетификаторы это номера столбцов с приставкой Col, пример Col1, Col2, Col3 и тд.) оставить необходимые столбцы.

Первая функция с использование оператора LABEL, будет выглядить:

=QUERY('Лист1'!A:H;"SELECT * LABEL E 'Кол-во'";-1)

Вторая функция с изменение латинский букв (название столбцов) F, G, E, H на идентификаторы столбцов Col6, Col7, Col5, Col8 выглядит:

=QUERY('Лист1'!A:H;"SELECT Col6, Col7, Col5, Col8 WHERE Col5 > 50 ORDER BY Col5 DESC LIMIT 10";-1)

Нам надо объединеть наши функции, для это во второй нашей функции в первом аргументе указываем первую нашу функцию:

=QUERY(QUERY('Лист1'!A:H;"SELECT * LABEL E 'Кол-во'";-1);"SELECT Col6, Col7, Col5, Col8 WHERE Col5 > 50 ORDER BY Col5 DESC LIMIT 10";-1)

Пример использования функции QUERY в Google Таблицы с использование оператора LABEL, SELECT, WHERE, ORDER BY и LIMIT

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

И так выбирать, отбрать, сортировать, ограничивать выбор и переименовывать заголовки столбцов наших таблицы мы науичились. Теперь давайте посмотрим, как можно использовать функции агрегирования: SUM(), AVG(), MIN(). Функций агрегирования больше, с ними можно подробнее ознакомиться на официальной старнице справочника по языку запросов API визуализации Google: https://developers.google.com/chart/interactive/docs/querylanguage?hl=ru

Мы рассмотрим, только парочку, чтобы понимать, как это всё работает.

Давайте посчитаем сколько всего у нас в наличии количество по Группа3, какое среднее количество и какое минимально.

На просто языке запрос будет звучать: Выбери Столбец 3 (Группа3), получи сумму столбца 5, получи среднее значение столбца 5, получи минимальное значние столбца 5 и сгруппируй эти значния по столбцу 3.

В написании функции мы будем использовать не название столбцов (C, E) а идентификаторы столбцов Col3, Col5. Принцип один и тот же, просто не будем ограничеваться одним синтаксисом, будем использовать функцию по максимуму.

=QUERY('Лист1'!A:H;"SELECT Col3, SUM(Col5), AVG(Col5), MIN(Col5) GROUP BY Col3";-1)

Пример использования функции QUERY с использованием встроенных функций агрегирования в Google Таблицы

В полученных данных у нас присутствует, какая-то странная пустая строка. Это из-за того, чтобы в первом аргументе функции мы указали столбцы целиком, а не ограничивали диапозон, например Лист1'!A1:H1000. Если бы мы сделали ограничение диапозона, то при добавлении новых строк, нам бы постоянном приходилось менять диапазон в нашей функции QUERY, чтобы этого не делать мы указали столбцы целиком.

Как нам избавиться от этой пустой строки? Мы просто используем оператор WHERE где Col1 не равно пусто:

=QUERY('Лист1'!A:H;"SELECT Col3, SUM(Col5), AVG(Col5), MIN(Col5) WHERE Col3 <> '' GROUP BY Col3";-1)

Пример использования функции QUERY с использованием встроенных функций агрегирования и оператора WHERE в Google Таблицы

Мы с вами познакомились с основным функционал функции QUERY в Google Таблицы и рассмотрели пару примеров, которые могут встретиться на практике. Более подробную информацию по языку запросов API визуализации Google смотрите в официальном справочнике: https://developers.google.com/chart/interactive/docs/querylanguage?hl=ru

Функция QUERY является удобным инструментом для обработки данных в Google Таблицы, но для максимально эффективного использования требуется время на изучение и понимания синтаксиса языка запросов.