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

Что такое функция QUERY?

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

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

диапазон — область ячеек, из которой берутся данные (например, Лист1!A1:C100).
запрос, строка, написанная на языке запросов (SELECT, WHERE, GROUP BY, ORDER BY и т.д.).
заголовки — необязательный параметр, указывающий, сколько строк в начале диапазона считать заголовками.

Основные возможности QUERY:

Выбор конкретных столбцов (SELECT).
Фильтрация строк по условиям (WHERE).
Сортировка (ORDER BY).
Группировка и агрегация (GROUP BY, SUM, AVG, MAX, MIN).
Объединение нескольких диапазонов.

Простой пример: выбор книг по тематике

Представим, что у нас есть лист Книги с колонками:

A — Название книги
B — Тематика
C — Год издания

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

=QUERY(Книги!A1:C;
 "SELECT A, C WHERE B = '" & E2 & "' ORDER BY C DESC";
 1)

Что делает эта формула?

Берёт данные из диапазона Книги!A1:C.
Выбирает столбцы A (название) и C (год).
Фильтрует строки, где столбец B равен значению из E2 (выбранная тематика).
Сортирует результат по году издания по убыванию.
Параметр 1 указывает, что первая строка диапазона содержит заголовки.

Как использовать QUERY для предсказаний

Хотя QUERY сама по себе не строит регрессионные модели, она отлично подготавливает данные для последующего анализа с помощью встроенных статистических функций Google Sheets (ЛИНЕЙНАЯ РЕГРЕССИЯ, ПРЕДСКАЗ, КОРРЕЛ и др.). Ниже показан типичный workflow:

Сбор и очистка данных – с помощью QUERY выбираем нужные столбцы, фильтруем аномалии, сортируем по времени.
Агрегация – группируем по дням, неделям или месяцам, считаем суммы, средние значения.
Построение прогноза – используем функцию ПРЕДСКАЗ (линейная регрессия) или ТЕНДЕНЦИЯ для extrapolating будущих значений.
Визуализация – строим график на основе полученных прогнозных данных.

Пример: прогноз ежемесячных продаж

Исходная таблица Продажи содержит:

A — Дата (формат ДД.ММ.ГГГГ)
B — Сумма продаж

Шаг 1. Выбираем данные за последние 12 месяцев и приводим их к формату «Месяц‑Год»:

=QUERY(Продажи!A:B;
 "SELECT TEXT(A, 'YYYY-MM') AS Month, SUM(B) AS Total
 WHERE A >= DATE(YEAR(TODAY)-1, MONTH(TODAY), 1)
 GROUP BY TEXT(A, 'YYYY-MM')
 ORDER BY Month";
 1)

Шаг 2. Получаем два столбца: Month (текст) и Total (число). Чтобы использовать их в линейной регрессии, преобразуем месяц в порядковый номер:

=ARRAYFORMULA(
 {QUERY(Продажи!A:B;
 "SELECT TEXT(A, 'YYYY-MM') AS Month, SUM(B) AS Total
 WHERE A >= DATE(YEAR(TODAY)-1, MONTH(TODAY), 1)
 GROUP BY TEXT(A, 'YYYY-MM')
 ORDER BY Month";
 1),
 SEQUENCE(ROWS(QUERY(Продажи!A:B;
 "SELECT TEXT(A, 'YYYY-MM') AS Month, SUM(B) AS Total
 WHERE A >= DATE(YEAR(TODAY)-1, MONTH(TODAY), 1)
 GROUP BY TEXT(A, 'YYYY-MM')
 ORDER BY Month";
 1)),1)}
)

Теперь у нас есть таблица с двумя колонками: порядковый номер месяца и сумма продаж. Шаг 3. Прогнозируем продажи на следующий месяц с помощью ПРЕДСКАЗ:

=ПРЕДСКАЗ(
 13, // следующий порядковый номер
 C2:C13, // известные X (номера месяцев)
 D2:D13) // известные Y (продажи)

Результат, ожидаемая сумма продаж за 13‑й месяц (то есть за следующий месяц после последнего известного). При необходимости можно построить прогноз на несколько периодов вперёд, просто меняя первый аргумент функции.

Полезные советы по работе с QUERY

Экранирование одинарных кавычек – если в условии WHERE используется текст, содержащий одинарную кавычку, её нужно удвоить: WHERE B = '''O''Reilly'''.
Использование именованных диапазонов – упрощает формулы и делает их более читаемыми.
Динамические диапазоны – сочетайте QUERY с функцией FILTER или INDIRECT, чтобы автоматически подхватывать новые строки.
Ограничение количества строк – добавьте LIMIT N в запрос, если нужны только первые N записей.
Комбинирование с другими функциями – результат QUERY можно сразу передать в СУММ, СРЗНАЧ, ВПР и т.д.

Функция QUERY в Google Sheets — это настоящая «магия чисел», позволяющая превратить сырые таблицы в мощный аналитический инструмент без необходимости изучать сложные языки программирования. Сочетая её с возможностями статистических функций (ПРЕДСКАЗ, ТЕНДЕНЦИЯ, КОРРЕЛ и др.), вы можете строить простые, но эффективные прогнозы, которые помогут принимать обоснованные решения в бизнесе, образовании или личных проектах.

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

Удачной работы с числами и пусть ваши прогнозы будут точными!