Функция QUERY в Google Sheets — руководство и практические примеры

Быстрые ссылки

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

Введение

Функция QUERY приносит в электронную таблицу возможности, похожие на базу данных: SELECT, WHERE, GROUP BY, ORDER BY и т.д. Если вы знакомы с SQL хотя бы на базовом уровне, QUERY покажется естественной и мощной. В этой статье подробно разберём синтаксис, основные приёмы, типичные ошибки и альтернативные подходы.

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

Использование функции QUERY

Основной формат формулы:

=QUERY(data, query, headers)

• data — диапазон ячеек, например “A2:D12” или “A:D”.
• query — строка с командой SELECT/WHERE и другими ключевыми словами (в кавычках).
• headers — необязательный аргумент: число строк верхнего заголовка в диапазоне (например, 1 или 2).

headers полезно указывать, если верхние строки представляют собой комбинированные заголовки.

Пример: у нас есть лист “Staff List” с колонками: идентификатор сотрудника, имя, фамилия, дата рождения, посещение тренинга (Yes/No).

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

=QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")

Эта формула выбирает столбцы A, B, C и E из диапазона A2:E12, где в столбце E стоит текст “No”.

Совет по динамическому диапазону: если вы хотите, чтобы список автоматически расширялся при добавлении сотрудников, укажите открытый диапазон по строкам:

=QUERY('Staff List'!A2:E, "SELECT A, B, C, E WHERE E = 'No'")

Так формула пропустит заголовок в A1 и будет работать при любом числе строк далее.

Частые ошибки при базовом использовании

• Неправильные кавычки внутри query: используйте одиночные кавычки для строк внутри двойных кавычек SQL, например “WHERE E = ‘No’”.
• Неправильный тип данных: даты и числа должны быть в ожидаемом формате, иначе сравнения не сработают.
• Пропущенный headers: если QUERY воспринимает первую строку как данные, а не как заголовок, результат может исказиться.

Продвинутые формулы QUERY

QUERY поддерживает логические операторы, сравнения, агрегаты и даже вложенные конструкции. Ниже — полезные приёмы с примерами.

Использование операторов сравнения

Добавим в список сотрудников колонку F — число наград. Чтобы вывести всех сотрудников с минимум одной наградой:

=QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0")

Оператор “>” фильтрует по значениям столбца F.

Использование AND и OR

Для комбинированных условий можно применять AND и OR. Пример: сотрудники, родившиеся с 1980 по 1989 год включительно:

=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'")

Здесь используются операторы >= и <= и ключевое слово DATE для корректного распознавания дат.

Если нужно исключить сотрудников из 80-х, используйте OR:

=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'")

Агрегация и GROUP BY

QUERY умеет группировать и подсчитывать. Пример: подсчитать, сколько сотрудников посещали/не посещали тренинг:

=QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E")

Это вернёт две строки: “Yes” с числом и “No” с числом.

Аналогично можно использовать SUM, AVG и т.д. — например, суммировать количество наград по группам или отделам.

Дополнительные ключевые слова

• ORDER BY — сортировка результатов.
• LIMIT — ограничение числа строк.
• OFFSET — смещение.
• LABEL — переименование колонок в выводе.
• FORMAT — форматирование чисел и дат в выводе.

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

=QUERY('Staff List'!A2:F, "SELECT A, B, C, F WHERE F > 0 ORDER BY F DESC LIMIT 10 LABEL F 'Awards'")

Работа с текстовыми и числовыми фильтрами

• Для поиска подстроки используйте LIKE: “WHERE B CONTAINS ‘Ann’” или “WHERE B LIKE ‘%Ann%’” (в QUERY синтаксис CONTAINS часто удобней).
• Для чисел следите за региональными форматами десятичных разделителей (в Google Sheets внутри QUERY требуется точка как разделитель в строке запроса).

Сравнение с альтернативными подходами

Когда выбрать QUERY, а когда — другие инструменты:

• FILTER — проще для простых условий и когда нужен динамический массив формул; но сложные группировки удобнее в QUERY.
• VLOOKUP/INDEX+MATCH — нужны для поиска по ключу; QUERY лучше для множественных фильтров и агрегирования.
• Сводные таблицы — удобны для интерактивного анализа и форматирования; QUERY хороша для автоматизированных выводов и формул.
• Apps Script — если нужны сложные трансформации, внешние API или циклы, используйте скрипты.

Mermaid-диаграмма выбора:

flowchart TD
  A[Нужно ли агрегировать?] -->|Да| B[Используйте QUERY]
  A -->|Нет| C[Нужен только фильтр/поиск]
  C -->|Одно условие| D[FILTER]
  C -->|По ключу| E[VLOOKUP / INDEX+MATCH]
  B --> F[Добавить GROUP BY / COUNT / SUM]
  F --> G[ORDER BY / LABEL по необходимости]

Паттерны использования и чеклист по ролям

Руководитель (HR/People): получить список сотрудников без тренинга и рассылка по ним.

• Шаги:
  • Указать динамический диапазон A2:E
  • Использовать WHERE E = ‘No’
  • Добавить ORDER BY по фамилии
  • Поставить LIMIT/OFFSET для постраничного вывода

Аналитик: агрегировать по отделам и считать среднюю зарплату (пример):

=QUERY('Payroll'!A2:F, "SELECT C, AVG(F) WHERE F IS NOT NULL GROUP BY C ORDER BY AVG(F) DESC")

SRE/администратор: автоматизировать отчёт — встроить формулу QUERY в лист отчётов и использовать Apps Script для экспорта CSV.

Шаблоны / полезные сниппеты

• Динамический список непрошедших тренинг:

=QUERY('Staff List'!A2:E, "SELECT A, B, C, E WHERE E = 'No' ORDER BY C")

• Подсчёт по категориям с переименованием:

=QUERY('Staff List'!A2:E, "SELECT E, COUNT(E) GROUP BY E LABEL COUNT(E) 'Count'")

• Диапазон с фильтром по дате с использованием TODAY(): (встроить значение даты через конкатенацию)

=QUERY('Staff List'!A2:E, "SELECT A, B, C WHERE D <= date '" & TEXT(TODAY(), "yyyy-MM-dd") & "'")

Обратите внимание: при объединении строк и функций Google Sheets используйте амперсанд & и TEXT для форматирования даты.

Когда QUERY даёт неверный результат — отладка

• Убедитесь, что диапазон данных не содержит смешанных типов (текст + число в одной колонке).
• Проверьте корректность заголовков и аргумента headers.
• Если даты не распознаются, преобразуйте их с помощью VALUE или используйте вспомогательный столбец с форматом YYYY-MM-DD.
• Для сложных строковых операций временно добавляйте колонку-помощник с UPPER() или TRIM(), затем проводите выборку по этой колонке.

Показатели зрелости: от новичка до продвинутого

• Уровень 1 — базовые SELECT и WHERE, динамический диапазон.
• Уровень 2 — логические условия AND/OR, сравнения дат, ORDER BY.
• Уровень 3 — GROUP BY, агрегаты (COUNT/SUM/AVG), LABEL и FORMAT.
• Уровень 4 — сочетание QUERY с ARRAYFORMULA, сложные динамические диапазоны, автоматизация через Apps Script.

Советы по производительности

• Уменьшайте диапазон данных, если возможно (например, A2:F1000 вместо A:F), это возвращает результаты быстрее.
• Для больших таблиц используйте промежуточные сводные таблицы или предобработку столбцов.
• Если QUERY часто пересчитывается, рассмотрите кэширование результатов в отдельный лист и обновление по расписанию через Apps Script.

Примеры ошибок и обходные пути

• Проблема: “#VALUE!” при использовании QUERY с датами. Решение: убедитесь, что внутри строки запроса даты в формате yyyy-MM-dd и что в диапазоне действительно даты, а не текст.
• Проблема: нечёткое сравнение строк с апострофами. Решение: экранируйте или замените апострофы, либо используйте другое поле-идентификатор.

Критерии приёмки

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

Краткая сводка — что нужно запомнить

• QUERY — мощный инструмент для SQL-подобных операций в Google Sheets.
• Используйте динамические диапазоны (A2:E) для автоматического обновления.
• Для сложной агрегации и группировок QUERY часто проще и эффективнее, чем набор FILTER + ARRAYFORMULA.

Важно: если вы работаете с региональными форматами дат и чисел, проверяйте, как Google Sheets интерпретирует значения в вашем аккаунте и используйте TEXT/VALUE при необходимости.

Глоссарий (одна строка каждый)

• SELECT — выбирает столбцы для вывода.
• WHERE — фильтрует строки по условию.
• GROUP BY — группирует строки для агрегирования.
• ORDER BY — сортирует вывод.
• LABEL — задаёт пользовательские имена столбцов в результате.

Дополнительные ресурсы и альтернативы

• Используйте FILTER для простых динамических фильтров.
• Для интерактивного анализа используйте сводные таблицы (Pivot Table).
• Для сложной автоматизации и экспорта данных — Google Apps Script.

Сводка: QUERY экономит время и код при работе с таблицами — от фильтрации и поиска до агрегации и отчётности. Начните с простых SELECT/WHERE и постепенно добавляйте GROUP BY, LABEL и FORMAT для чистых, готовых к использованию отчётов.