Excel и Google Workspace / Формулы Google Таблиц
QUERY в Google Sheets с условием where
QUERY выбирает из диапазона A1:D20 только столбцы A и D для строк, где столбец B равен "Оплачен". Последний аргумент 1 сообщает функции, что в исходном диапазоне есть одна строка заголовков.
Формула
Обозначения
- $A1:D20$
- исходная таблица вместе с заголовками
- select A,D
- команда вернуть только столбцы A и D
- $where B = 'Оплачен'$
- условие отбора строк по значению в столбце B
- $1$
- количество строк заголовков в исходном диапазоне
Условия применения
- Запрос пишется внутри кавычек и использует синтаксис Google Visualization API Query Language.
- Текстовые значения внутри условия берутся в одинарные кавычки.
- Если диапазон начинается с обычных столбцов листа, в запросе можно обращаться к ним как A, B, C и D.
Ограничения
- QUERY чувствительна к синтаксису запроса: пропущенная кавычка или неверное имя столбца приводит к ошибке.
- Для диапазонов, созданных массивными литералами, часто используются имена Col1, Col2 вместо букв A, B.
- Даты в QUERY требуют специального синтаксиса date 'yyyy-mm-dd', обычная текстовая дата может не сработать как дата.
Подробное объяснение
QUERY превращает диапазон в небольшую табличную выборку. Команда select A,D говорит, какие столбцы должны попасть в результат.
Часть where B = 'Оплачен' отбирает только строки с нужным статусом. Остальные строки исходного диапазона просто не выводятся.
Последний аргумент 1 важен для заголовков. Он помогает функции понять, что первая строка A1:D1 содержит названия полей, а не обычные данные.
QUERY полезна тем, что объединяет несколько действий в одной формуле: выбор столбцов, фильтрацию, сортировку, группировку и подписи. Поэтому она часто заменяет длинные цепочки FILTER, SORT и отдельных вспомогательных столбцов.
При этом QUERY требует аккуратного синтаксиса. Если запрос становится слишком сложным, его лучше собирать постепенно: сначала select, затем where, затем order by или group by.
QUERY использует текст запроса, похожий на упрощенный SQL. Часть select выбирает столбцы для вывода, а where задает условие отбора строк. При диапазоне A1:D20 столбцы в запросе обозначаются буквами A, B, C и D, а третий аргумент показывает количество строк заголовка. Ошибка в кавычках или регистре текстового статуса часто приводит к пустому выводу.
Как пользоваться формулой
- Укажите исходный диапазон вместе с заголовками.
- Напишите запрос в двойных кавычках.
- В select перечислите нужные столбцы.
- В where задайте условие отбора строк.
- Укажите количество строк заголовков последним аргументом.
Историческая справка
QUERY стала одной из самых мощных функций Google Sheets, потому что принесла в таблицы стиль работы, похожий на SQL-запросы.
Для специалистов, которые ведут данные в облачных таблицах, это открыло способ строить отчеты без отдельной базы данных и без скриптов.
Функция особенно популярна в связке с IMPORTRANGE: данные подтягиваются из другого файла, а QUERY сразу оставляет только нужные строки и столбцы.
Несмотря на непривычный синтаксис, QUERY часто экономит место на листе и уменьшает количество вспомогательных формул.
Появление QUERY сблизило электронные таблицы с подходом баз данных: данные можно не только фильтровать и сортировать, но и описывать выборку текстовым запросом. Это полезно для небольших отчетных моделей, где отдельная база данных еще не нужна.
Историческая линия формулы
QUERY в Google Sheets основана на языке запросов Google Visualization API. В таблицах она стала практическим инструментом выборки и преобразования диапазонов. QUERY является функцией Google Sheets и опирается на идею декларативного запроса к табличным данным. Исторически ее смысл связан с языками запросов и офисной аналитикой, а не с одной авторской формулой.
Пример
Дано: A1:D5 содержит заголовки Номер, Статус, Клиент, Сумма. В B2:B5 статусы: Оплачен, Новый, Оплачен, Отменен. В D2:D5 суммы: 1500, 3000, 7200, 400. Формула =QUERY(A1:D5,"select A,D where B = 'Оплачен'",1) вернет две строки и два столбца: номера оплаченных заказов и их суммы 1500 и 7200. Проверка: если в столбце B хранится статус, а в D сумма, запрос вернет только выбранные столбцы A и D для оплаченных строк. Строки со статусом «Новый» не попадут в результат, даже если сумма большая. Если добавить условие `and D > 1000`, выборка станет уже: останутся только оплаченные заказы выше порога.
Частая ошибка
Главная ошибка: перепутать одинарные и двойные кавычки. Весь запрос должен быть в двойных кавычках, а текст "Оплачен" внутри where обычно пишется в одинарных. Вторая ошибка: указать неверное число заголовков, из-за чего первая строка данных воспринимается как заголовок. Третья ошибка: применять буквы A, B, C к массиву, где Google Sheets ожидает Col1, Col2.
Практика
Задачи с решением
Выбрать оплаченные суммы
Условие. В A1:D4 есть заголовки, статусы в B: Оплачен, Новый, Оплачен, суммы в D: 1000, 2000, 3500. Нужно вернуть номер и сумму только оплаченных строк.
Решение. Запрос select A,D where B = 'Оплачен' выберет столбцы A и D только для первой и третьей строк данных.
Ответ. Вернутся две строки с суммами 1000 и 3500
Исправить отсутствие заголовка
Условие. Диапазон начинается с A2:D20 и не содержит строки заголовков. Нужно выполнить такой же запрос.
Решение. Последний аргумент меняют на 0: =QUERY(A2:D20,"select A,D where B = 'Оплачен'",0).
Ответ. Используйте 0 строк заголовков
Дополнительные источники
- Google Docs Editors Help: QUERY function - https://support.google.com/docs/answer/3093343?hl=en
- Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
- Dan Bricklin: VisiCalc and the origins of spreadsheets - https://www.bricklin.com/visicalc.htm
Связанные формулы
Excel и Google Workspace
FILTER в Google Sheets с несколькими условиями
FILTER возвращает только те строки диапазона A2:D20, где статус в B2:B20 равен "Оплачен", а сумма в D2:D20 больше 1000. Результат разливается на лист как динамический массив.
Excel и Google Workspace
UNIQUE в Google Sheets для списка без повторов
UNIQUE возвращает список уникальных значений из диапазона A2:A100 в том порядке, в котором они впервые встречаются. Функция помогает быстро получить справочник клиентов, товаров, городов или категорий из длинной таблицы.
Excel и Google Workspace
SORT в Google Sheets для сортировки диапазона
SORT возвращает строки диапазона A2:D20, отсортированные по четвертому столбцу в порядке убывания. Исходная таблица не меняется, а результат выводится как отдельный динамический массив.
Excel и Google Workspace
IMPORTRANGE в Google Sheets для данных из другого файла
IMPORTRANGE импортирует диапазон Лист1!A1:D20 из другой таблицы Google Sheets. В первом аргументе указывают ссылку или ключ файла, во втором аргументе указывают имя листа и диапазон.