Функция QUERY - мощнейший инструмент Гугл таблиц, способный решать самые разные задачи: от перестановки столбцов местами до полноценной программы, динамически меняющейся в зависимости от пользовательских настроек.
ПРОЕКТ РАЗРАБОТЧИКОВ HELPEXCEL
QUERY - cамая мощная функция Гугл таблиц
Начало работы

Если кратко, функция QUERY принимает данные, обрабатывает их и выводит на лист.
Синтаксис функции выглядит так:

QUERY(данные; запрос; [заголовки])
Аргумент
Описание
Данные
Один или несколько диапазонов данных, с которыми она будет работать, например:
'Лист1'!A1:D или {'Лист1'!A1:D;'Лист2'!A1:D;'Лист3'!A1:D}
Запрос
Непосредственно "код программы" - набор условий, по которым формируется вывод данных. Например:
"SELECT *" или "SELECT A WHERE C>0"
Заголовки (НЕОБЯЗАТЕЛЬНО)
Количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен "-1", его значение вычисляется автоматически в зависимости от содержимого данных

Запросы могут быть следующими:

Запрос
Описание
select
перечисление полей, которые будут возвращены запросом
where
содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
group by
содержит перечень полей, по которым будет сгруппирован результат
pivot
строит перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы
order by
сортировка результатов
limit
предел количеству строк, возвращаемых запросом
offset
число первых строк, которые не надо обрабатывать запросом
label
отвечает за название полей, возвращаемых запросом
format
отвечает за формат выводимых данных
options
дополнительные параметры вывода данных
Для начала откройте файл со всеми примерами по ссылке и создайте его копию
Простой пример с одним условием

Пример 1: =QUERY('Лист1'!B2:F;"SELECT B WHERE C='Одежда'")

Функция вернет:

Товар

Куртка
Кроссовки
Шорты
Кепка

Используя диапазон 'Лист1'!B2:F функция QUERY вернула столбец B, если в столбце С указано "Одежда".
Если данные берутся с текущего листа, то диапазон можно указать как B2:F


WHERE позволяет создавать и более сложные запросы, например:

=QUERY({B2:F};"SELECT Col3, Col1, Col5 WHERE(Col5 >= 10000 AND Col5 <= 20000) AND (Col3 = 'Петров' OR Col3 = 'Сидоров')"; 1)

Пример 2: =QUERY('Лист5'!B2:F;"SELECT B WHERE C='Одежда'";0)

Функция вернет:

Куртка
Кроссовки
Шорты
Кепка

Мы добавили количество заголовочных строк, а именно 0, и теперь они не отображаются.

Внешняя ячейка для настройки QUERY

Пример 3: =QUERY('Лист5'!B2:F;"SELECT B WHERE C='"&H2&"' ORDER BY F DESC"; 0)

Этот пример полностью аналогичен =QUERY('Лист5'!B2:F;"SELECT B WHERE C='Инвентарь' ORDER BY F"; 0) за исключением того, что теперь значение столбца С можно менять с помощью внешней ячейки, например, с помощью выпадающего списка. Так же добавился запрос ORDER BY, который сортирует результат по колонке F. DESC - необязательный параметр, который меняет порядок на противоположный.

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

Пример 4: =QUERY({'Лист5'!B2:F};"SELECT Col2, Col1, Col5"; 1)

Этим способом мы поменяли порядок столбцов и убрали часть из них. Это бывает необходимо для функций по типу ВПР (VLOOKUP), которые делают поиск по первому столбцу диапазона. Так же обратите внимание на фигурные скобки вокруг диапазона - они позволяют не только объединить несколько диапазонов в один, но и использовать обращение к столбцам по порядковому номеру. В нашем случае Col1 это B - первый столбец диапазона.

Вычисления в QUERY

Пример 5: =QUERY('Лист5'!B2:F11;"SELECT D, AVG(F), count(B) GROUP BY D")

Так мы узнаем среднюю цену покупки, а также количество товаров, купленных покупателем, или узнаем минимальную и максимальную цену в разделах "Одежда" и "Инвентарь".

GROUP BY группирует значения в выбранной колонке и, кроме того, для GROUP BY есть агрегирующие функции:

Функция
Описание
Поддерживаемый тип данных
Возвращаемый тип данных
avg()
Возвращает среднее значение для группы
Числовой
Числовой
count()
Возвращает количество значений в группе
Любой
Числовой
max()
Возвращает максимальное значение для группы
Любой
Аналогичный полю, к которому применяется
min()
Возвращает минимальное значение для группы
Любой
Аналогичный полю, к которому применяется
sum()
Возвращает минимальное значение для группы
Числовой
Числовой

Пример 6: =QUERY('Лист5'!B2:F11;"SELECT D, AVG(F), count(B) GROUP BY D
LABEL D 'Покупатель', AVG(F) 'Средняя цена', count(B) 'Кол-во наименований'
FORMAT avg(F) '0.00'"
)

Всё тоже самое, что и в предыдущем примере, но с улучшениями. LABEL для переименования заголовков, а с помощью FORMAT округлили результат. Таким образом пример 5 стал выглядеть лучше.

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

В зеленой ячейке P2 мы через запятую указываем номера колонок, SPLIT в Q2 разбивает их на ряд цифр, MATCH в P3 ищет пустую ячейку и возвращает номер колонки, что в паре с ADRESS дает нам диапазон значений, которые вернул SPLIT. Далее JOIN в P4 делает строку вида "Col3, Col4, Col1" который и используется в QUERY. Промежуточные формулы в P4 и P5 вставляем в QUERY и получаем пример 7.

А если вспомнить, что чекбокс может возвращать произвольные значения (число вместо TRUE и ничего вместо FALSE), то можно избавиться от SPLIT и отмечать нужные колонки чекбоксами.

Пример 7: =QUERY({'Лист5'!B2:F11};"SELECT "&"Col"&JOIN(",Col";INDIRECT(ADDRESS(2;MATCH(P2;$A2:$2;-1)+1;1)&":"&ADDRESS(2;MATCH(;$A2:$2;-1);1))); 1)

Пример 8: =QUERY({B2:F11};"SELECT Col3, sum(Col5) GROUP BY Col3 PIVOT Col2")

Возвращает Col3 (Имена), суммируя Col5 (Цена), группируя по Col3 (Имена), создав перекрестную таблицу на основе Col2 (Тип товара). В итоге мы видим, что именно и на какую сумму купил каждый покупатель.

Пример 9: =QUERY({'Лист5'!B2:F11};"SELECT Col3, sum(Col5)-(SUM(Col5)/100*SUM(Col4)) GROUP BY Col3 PIVOT Col2 LABEL sum(Col5)-(SUM(Col5)/100*SUM(Col4)) ''")

То же самое, но с учетом скидки. Как видите, в QUERY можно производить вычисления, а не только возвращать уже существующие данные.

Даты

Также в QUERY есть набор функций для работы с датами и не только:

Функция
Описание
year()
Возвращает номер года из «даты» или «даты и времени». Пример: year(date '2009-02-05') вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время.
Тип возвращаемых данных: число.
month()
Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, февраль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date '2009-02-05') вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату, прибавьте 1, month(date "2009-02-05")+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время.
Тип возвращаемых данных: число.
day()
Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date '2009-02-05') вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время.
Тип возвращаемых данных: число.
hour()
Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday '12:03:17') вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время.
Тип возвращаемых данных: число.
minute()
Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday '12:03:17') вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время.
Тип возвращаемых данных: число.
second()
Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday '12:03:17') вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время.
Тип возвращаемых данных: число.
millisecond()
Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday '12:03:17.123') вернет 123.
Запрашиваемые параметры: один параметр с типом время или дата и время.
Тип возвращаемых данных: число.
quarter()
Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответственно, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date '2009-02-05') вернет 1.
Запрашиваемые параметры: один параметр с типом дата или дата и время.
Тип возвращаемых данных: число.
dayOfWeek()
Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date '2015-11-10') вернет 3, так как 10 ноября 2015 года — вторник. Запрашиваемые параметры: один параметр с типом дата или дата и время.
Тип возвращаемых данных: число.
now()
Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров.
Тип возвращаемых данных: дата и время.
dateDiff()
Возвращает разницу в днях между двумя датами. Пример: dateDiff(date '2008-03-13' , date '2008-02-12') вернет 29, так как 10 ноября 2015 года вторник.
Запрашиваемые параметры: два параметра с типом «дата» или «дата и время».
Тип возвращаемых данных: число.
toDate
Возвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример:
  • toDate(date '2008-03-13') вернет аналогичное значение в формате даты, '2008-03-13'
  • toDate(dateTime'2013-03-13 11:19:22') вернет дату '2013-03-13'
  • toDate(1234567890000) вернет дату '2009-02-13'
Запрашиваемые параметры: один параметр с типом дата, дата и время или число.
Тип возвращаемых данных: дата.
upper()
Преобразует все значения в строке в верхний регистр. Пример: upper( 'foo') вернет строку 'FOO'. Запрашиваемые параметры: один параметр с текстовым типом данных.
Тип возвращаемых данных: текст.
lower()
Преобразует все значения в строке в нижний регистр. Пример: upper( 'Bar') вернет строку 'bar'. Запрашиваемые параметры: один параметр с текстовым типом данных.
Тип возвращаемых данных: текст.

Пример 10: =QUERY({'Лист5'!B2:G11};"SELECT dayOfWeek(Col1), Col4, Col2, Col6 WHERE Col6>8000"; 1)

Добавим колонку с датой и посмотрим, в какие дни недели люди покупают наиболее дорогие товары. dayOfWeek возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее.

В итоге мы видим, что самые дорогие покупки происходят в пятницу и субботу. А используя & из третьего примера можно выводить данные за выбранные даты, используя выпадающие списки с номерами месяцев и годов:
=QUERY({'Лист5'!B2:G11};"SELECT Col1, Col4, Col2, Col6 WHERE month(Col1)="&Y21&" LABEL Col1 'только за "&Y21+1&" месяц' "; 1)

QUERY и IMPORTRANGE

Пример 11: =QUERY({IMPORTRANGE("https://docs.google.
com/spreadsheets/d/1ZnWDQnGRLq...
OE4y9OtrAUU6uWlxC8iOLDw/edit#gid=0";
"'Лист5'!B2:G5"
)};"SELECT Col1, Col2, Col3")

В качестве диапазона можно использовать IMPORTRANGE, обрабатывая данные из
другого файла. IMPORTRANGE принимает либо полную ссылку, либо ID файла: =QUERY({IMPORTRANGE("1ZnWDQnGRLqJ1pIzYRkJTOE4y9OtrAUU6uWlxC8iOLDw";"'
Лист5'!«2:G5"
)};"SELECT Col1, Col2, Col3")

Как вы помните, фигурные скобки позволяют использовать несколько диапазонов.
То же самое относится и к IMPORTRANGE. Можно указать несколько файлов, используя
несколько IMPORTRANGE=QUERY({IMPORTRANGE("ID";"диапазон");IMPORTRANGE("ID";"диапазон");IMPORTRANGE
("ID";"диапазон")};"SELECT Col1, Col2, Col3")

Обратите внимание, что такая формула вернет ошибку доступа к файлу, если доступ не открыт хотя бы к одному из них. Это решается применением =IMPORTRANGE("ID";"диапазон") для каждого из указанных файлов. При вводе такой функции и наведении на нее появится окно с разрешением на доступ.

Как видите, QUERY больше, чем просто функция. Это целая программа, которая может быть как очень маленькой и простой, так и огромной. Используйте Ctrl+Enter в окне формулы, чтобы переносить строки и сделать функцию более читаемой.

На этом все!
Хотите самостоятельно разбираться в Гугл таблицах?
Быстро. Грамотно. Полезно.
Пройдите новый усовершенствованный курс по Гугл таблицам от наших разработчиков

Если остались вопросы, пишите в наш Телеграмм-чат

Обзоры наших работ
На основе настоящих кейсов мы создаем готовые шаблоны таблиц
и публикуем в открытый доступ.
Обзоры наших работ
На основе настоящих кейсов мы создаем готовые шаблоны таблиц
и публикуем в открытый доступ.
Показать ещё