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

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

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

Не хотите разбираться в непонятных формулах?

Запишитесь на консультацию. Мы подберем для вас решение и научим пользоваться

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

Пример 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, и теперь они не отображаются.

Пример 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 - первый столбец диапазона.

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

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

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

Пример 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 и отмечать нужные колонки чекбоксами.

Пример 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 есть набор функций для работы с датами и не только:

Пример 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)

Пример 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. Проведем аудит и предложим решение.