QUERY в Google Sheets — руководство

Что такое QUERY

Функция QUERY выполняет SQL-подобные запросы к диапазону таблицы в Google Sheets. Коротко: она принимает диапазон данных и строку-запрос, где вы можете использовать SELECT, WHERE, ORDER BY, GROUP BY, LABEL и другие ключевые слова для точного извлечения нужных строк и столбцов.

Определение в одну строчку: QUERY — это средство выбирать, фильтровать и агрегировать табличные данные в стиле SQL без внешней базы данных.

Синтаксис

=QUERY(data, query, [headers])

• data — диапазон ячеек (например, A2:C100 или Sheet1!A1:E).
• query — строка с SQL-подобным выражением (например, “SELECT A, B WHERE C > 500 ORDER BY A”).
• [headers] — необязательный параметр; число строк заголовков в начале диапазона. 1 означает, что первая строка — заголовки.

Важно: строка query всегда берётся в кавычки и чувствительна к синтаксису Google Sheets QUERY (не полная реализация SQL).

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

Фильтрация данных

Допустим, в столбце C хранится сумма продаж, и вы хотите строки с продажами больше $500. Если данные начинаются с A2:

=QUERY(A:C, "SELECT * WHERE C > 500")

Этот запрос вернёт все строки, где значение в C больше 500.

Сортировка данных

Чтобы отсортировать диапазон по имени продукта (столбец A) по алфавиту:

=QUERY(A2:C7, "SELECT * ORDER BY A ASC")

Несколько условий

Фильтрация по количеству и сумме одновременно:

=QUERY(A2:C7, "SELECT * WHERE B > 30 AND C > 500")

Запросы между листами

Чтобы вытянуть данные с другого листа, добавьте имя листа и восклицание перед диапазоном.

=QUERY(Sheet1!A1:C, "SELECT A, B, C WHERE C > 75", 1)

Результаты появятся в текущем листе, начиная с той ячейки, куда вы вставили формулу.

Работа с типами данных: текст, числа и даты

• Текст сравнивайте в кавычках: WHERE A = ‘Apple’.
• Числа не в кавычках: WHERE C > 500.
• Даты используются как даты Sheets; для явного сравнения применяйте toDate или преобразуйте ячейки в формат даты.

Пример сравнения дат, если в столбце D даты:

=QUERY(A:D, "SELECT A,B WHERE D > date '2023-01-01'")

(Формат даты — ГГГГ-ММ-ДД)

Группировка и агрегаты

QUERY поддерживает GROUP BY и агрегатные функции (SUM, COUNT, AVG, MAX, MIN).

Пример — суммировать продажи по продуктам:

=QUERY(A:C, "SELECT A, SUM(C) WHERE C IS NOT NULL GROUP BY A ORDER BY SUM(C) DESC", 1)

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

Совместное использование с другими функциями

• IMPORTRANGE — для запросов к внешним документам: сначала подключите диапазон, затем QUERY.

=QUERY(IMPORTRANGE("", "Sheet1!A:C"), "SELECT Col1, Col2 WHERE Col3 > 100")

• ARRAYFORMULA — для распространения вычислений.
• VLOOKUP/INDEX+MATCH — когда нужен поиск по одиночному ключу, но QUERY лучше для сложных условий и агрегатов.

Типичные ошибки и как их исправлять

• Ошибка синтаксиса в query: проверьте кавычки и пробелы (например, “ORDER BY” неразрывный с пробелом).
• Неправильные имена столбцов при IMPORTRANGE: используйте Col1, Col2 и т.д., если диапазон не содержит заголовков.
• Формат даты: используйте ‘YYYY-MM-DD’ в выражениях date.
• Неожиданное поведение из-за пустых строк: указывайте точный диапазон или фильтруйте NULL.

Совет: при отладке временно используйте простой SELECT * и добавляйте условия по одному.

Подсказки по производительности

• Ограничьте диапазон, не используйте целые столбцы (A:C) без необходимости.
• Избегайте многократных IMPORTRANGE в одной таблице; старайтесь импортировать один раз и делать QUERY к результату.
• Используйте headers (третьий параметр) корректно, чтобы избежать лишней обработки заголовков.

Когда QUERY не лучший выбор

• Если нужно динамически менять критерии с UI-слайдеров, проще использовать FILTER вместе с ссылкой на ячейку с условием.
• Для сложных вычислений по строкам иногда удобнее Apps Script или Google BigQuery при больших объёмах данных.

Шаблон методологии запроса (шаги)

1. Определите цель: какие столбцы и условия нужны.
2. Выберите минимальный диапазон данных.
3. Составьте базовый запрос: SELECT *.
4. Добавьте WHERE, затем ORDER BY, затем GROUP BY/агрегаты.
5. Проверяйте результат и оптимизируйте диапазон.

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

• Базовый фильтр с заголовком:

=QUERY(A1:C100, "SELECT A, B WHERE C > 500", 1)

• Сумма по категории:

=QUERY(A1:C100, "SELECT A, SUM(C) WHERE C IS NOT NULL GROUP BY A", 1)

• IMPORTRANGE + QUERY:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/ID", "Sheet1!A:C"), "SELECT Col1, Col3 WHERE Col3 > 100")

Чек-листы по ролям

Аналитик:

• Определить набор колонок и типы данных.
• Проверить примеры значений.
• Тестировать запрос на подмножества данных.

Администратор:

• Контролировать доступы к внешним документам (для IMPORTRANGE).
• Следить за производительностью таблицы.

Разработчик отчётов:

• Оборачивать QUERY в другие формулы при необходимости.
• Документировать используемые диапазоны и предположения о заголовках.

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

• Результирующие строки соответствуют условиям WHERE.
• Формула работает на полном диапазоне данных без ручных правок.
• Производительность приемлема (время отклика таблицы — незначительное при обычном объёме).

Диаграмма выбора: QUERY или FILTER

flowchart TD
  A[Нужен SQL-подобный фильтр или агрегат?] -->|Да| B[Использовать QUERY]
  A -->|Нет, простая фильтрация| C[Использовать FILTER]
  B --> D{Данные из другого файла?}
  D -->|Да| E[IMPORTRANGE + QUERY]
  D -->|Нет| F[QUERY на локальном листе]

Часто встречающиеся кейсы и обходы

• Пустые строки мешают группировке: добавьте WHERE ColX IS NOT NULL.
• Не распознаётся тысячный разделитель: убедитесь, что ячейки в формате Число без лишних символов.
• Столбцы с пробелами в заголовке: используйте номера колонок (Col1) или оберните заголовки в " только внутри LABEL.

Безопасность и конфиденциальность

При использовании IMPORTRANGE или запросов между листами проверьте, что у получателя есть доступ только к допустимым данным. Для чувствительных данных рекомендуется не использовать общие ссылки и управлять доступом через права Google Workspace.

Резюме

Функция QUERY — гибкий инструмент для извлечения, фильтрации, сортировки и агрегирования данных в Google Sheets. Это хороший выбор, когда нужны SQL-подобные операции без выхода из таблицы. Начните с простых запросов и постепенно добавляйте условия, группировки и объединения.

Важное: всегда уточняйте диапазон и заголовки, особенно при использовании IMPORTRANGE.