QUERY в Google Таблицах

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

Пример

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

Товар

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

Используя диапазон ‘Лист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)

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

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

Этот пример полностью аналогичен =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 есть агрегирующие функции:

Пример 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 стал выглядеть лучше.

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

Пример повышенной сложности. С помощью & из третьего примера и нескольких функций мы можем динамически менять извне то, какие именно колонки будет возвращать 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)

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»;»диапазон») для каждого из указанных файлов. При вводе такой функции и наведении на нее появится окно с разрешением на доступ.

Источник: https://www.helpexcel.pro/query

Найти максимальное значение в каждой из групп и вывести списком

=QUERY(QUERY(N:O;«SELECT Col1,MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1»;0);«SELECT Col1, Col2 OFFSET 1»;0)