Все формулы РФ

Excel и Google Workspace / Формулы Google Таблиц

QUERY в Google Таблицах: базовый SELECT

QUERY выполняет запрос к диапазону Google Таблиц на языке, похожем на SQL. Базовый SELECT выбирает нужные столбцы и строки по условию.

Опубликовано: Обновлено:

Формулы Google ТаблицGoogle ТаблицыОфисные расчетыКачество данныхЕсть историческая справка

Формула

$$=QUERY(A1:D100,"select A, C where B = 'Оплачен'",1)$$
Схема запроса SELECT и WHERE

SELECT выбирает столбцы, WHERE оставляет строки, а результат выводится отдельной таблицей.

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

Обозначения

$A1:D100$
исходный диапазон данных с заголовками
select A, C
часть запроса, выбирающая только столбцы A и C
$where B = 'Оплачен'$
условие отбора строк по столбцу B
$1$
число строк заголовков в исходном диапазоне, строка

Условия применения

  • В запросе столбцы обозначаются буквами Col1, Col2 или буквами A, B, C в зависимости от формы диапазона и контекста.
  • Текстовые значения внутри запроса обычно заключают в одинарные кавычки.
  • Нужно правильно указать число строк заголовков, чтобы QUERY не приняла заголовок за данные.

Ограничения

  • QUERY чувствительна к типам данных в столбцах: смешение дат, чисел и текста может дать неожиданный результат.
  • Синтаксис запроса отличается от обычных формул Google Таблиц, поэтому ошибки кавычек и пробелов встречаются часто.
  • Для очень простой выборки FILTER может быть понятнее, особенно для пользователей без опыта запросов.

Подробное объяснение

QUERY превращает диапазон Google Таблиц в маленький источник данных, к которому можно обратиться запросом. Базовый SELECT отвечает на вопрос какие столбцы вывести. WHERE отвечает на вопрос какие строки оставить. Вместе они позволяют построить компактный отчет из большой таблицы без ручного копирования и без скрытия строк в исходнике.

Главное отличие QUERY от FILTER - запрос записывается текстом. Это непривычно для обычных формул, но дает гибкость: можно выбрать только часть столбцов, добавить сортировку, группировку, агрегирование и подписи. Для первого шага достаточно понять структуру select A, C where B = 'Оплачен': вывести A и C там, где B равен нужному статусу.

Третий аргумент сообщает функции, сколько строк заголовков есть в диапазоне. Если указать 1, первая строка воспринимается как заголовок, а данные начинаются ниже. Это влияет на вывод и на обработку типов. В рабочих таблицах лучше включать заголовки в диапазон и явно указывать их количество, чтобы формула была понятнее.

QUERY особенно сильна в Google Таблицах, потому что может заменить несколько промежуточных шагов: фильтр, выбор столбцов, сортировку и иногда агрегирование. Но за гибкость приходится платить дисциплиной синтаксиса. Перед сложным запросом полезно собрать простую версию SELECT, убедиться, что она работает, и затем добавлять WHERE, ORDER BY или GROUP BY по одному элементу.

Как пользоваться формулой

  1. Выберите исходный диапазон вместе с заголовками, если они есть.
  2. Начните с простого запроса вида select A, B, чтобы проверить вывод столбцов.
  3. Добавьте where для отбора строк по статусу, дате, числу или тексту.
  4. Укажите третьим аргументом количество строк заголовков.
  5. Расширяйте запрос постепенно: сортировка и группировка только после проверки SELECT.

Историческая справка

QUERY в Google Таблицах связана не только с традицией электронных таблиц, но и с языками запросов к данным. Пользователи привыкли работать с диапазонами как с листом, а QUERY предлагает смотреть на диапазон как на таблицу данных: выбрать столбцы, отфильтровать строки, сгруппировать и отсортировать. Эта идея близка к SQL, но реализована через Google Visualization API Query Language. В истории Google Таблиц QUERY стала важной функцией для людей, которые строят отчеты без отдельной базы данных и без скриптов. Она показывает, как облачная таблица стала промежуточным инструментом между обычным листом и легкой аналитической витриной. Поэтому QUERY часто выбирают там, где обычные формулы уже плохо выражают структуру отчета.

Пример

В A1:D100 есть таблица заказов: дата, статус, сумма, менеджер. Нужно вывести только дату и сумму оплаченных заказов. Формула =QUERY(A1:D100,"select A, C where B = 'Оплачен'",1) берет диапазон с одной строкой заголовков, выбирает столбцы A и C и оставляет строки, где в столбце B написано Оплачен. Если нужно добавить сортировку по сумме, запрос можно расширить: select A, C where B = 'Оплачен' order by C desc. Проверка: сравнить результат с ручным фильтром по статусу и убедиться, что выводятся только нужные столбцы.

Частая ошибка

Частая ошибка - перепутать кавычки: сам запрос находится в двойных кавычках формулы, а текст внутри запроса - в одинарных. Вторая ошибка - забыть третий аргумент с количеством строк заголовков и получить странное поведение первой строки. Третья ошибка - использовать русские названия столбцов в SELECT вместо букв или Col-обозначений. Еще одна проблема - даты и числа, импортированные как текст: QUERY может сравнивать их не так, как ожидает пользователь.

Практика

Задачи с решением

Выбрать дату и сумму оплаченных заказов

Условие. В A1:D100 столбцы: дата, статус, сумма, менеджер. Нужно вывести только дату и сумму для статуса Оплачен.

Решение. Выбираем столбцы A и C, а строки фильтруем по B: =QUERY(A1:D100,"select A, C where B = 'Оплачен'",1).

Ответ. =QUERY(A1:D100,"select A, C where B = 'Оплачен'",1)

Добавить порог суммы

Условие. Нужно вывести дату, сумму и менеджера только для заказов с суммой больше 10000.

Решение. Выбираем A, C и D, условие ставим по C: =QUERY(A1:D100,"select A, C, D where C > 10000",1).

Ответ. =QUERY(A1:D100,"select A, C, D where C > 10000",1)

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

  • Google Docs Editors Help: QUERY function - https://support.google.com/docs/answer/3093343?hl=en
  • Google for Developers: Google Visualization API Query Language reference - https://developers.google.com/chart/interactive/docs/querylanguage
  • Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en

Связанные формулы

Excel и Google Workspace

FILTER в Google Таблицах: выбрать строки по условиям

$=FILTER(A2:D100,B2:B100="Оплачен",C2:C100>=10000)$

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

Excel и Google Workspace

IMPORTRANGE в Google Таблицах: данные из другой таблицы

$=IMPORTRANGE("spreadsheet_url","Лист1!A1:D100")$

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

Excel и Google Workspace

SUM в Google Таблицах: сумма диапазона

$=SUM(B2:B100)$

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