Рассказываем, почему функция QUERY - полезный и популярный инструмент Google Таблиц, способный решать самые разные задачи: от перестановки столбцов местами до полноценной программы, которая динамически меняется в зависимости от пользовательских настроек.

ПРОЕКТ РАЗРАБОТЧИКОВ HELPEXCEL.PRO

QUERY - cамая мощная функция Google Таблиц

Даже базовый функционал QUERY может перекрыть огромную часть работы с таблицами, автоматизировать процессы. Заполнять их вручную больше не придется. Давайте разберемся в основных функциях 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… <ссылка_на_вашу_таблицу>/edit# gid=0";
"'Лист5'!B2:G5" )};"SELECT Col1, Col2, Col3")

В качестве диапазона можно использовать IMPORTRANGE, обрабатывая данные из
другого файла. IMPORTRANGE принимает либо полную ссылку, либо ID файла: =QUERY ({IMPORTRANGE («<ссылка_на_вашу_таблицу>» ;«'
Лист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 в окне формулы, чтобы переносить строки и сделать функцию более читаемой.

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

На этом все!
Наверняка у вас появились вопросы
Задавайте их нам в любое время. Мы обязательно ответим на каждый.
Вместе с этим часто читают

Еще больше полезных материалов вы можете найти в нашем Telegram-канале:

Хотите обсудить свой проект?

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

Для обсуждения вашей задачи напишите нам в WhatsApp. Проведем аудит и предложим решение.

Нужна таблица для бизнеса?
*Большинство решений бесплатные!