Чем прекрасна функция QUERY? На мой взгляд это очень удобный конструктор по выборке и компоновки данных. Так же это более гибкий инструмент в постройке сводных таблиц. Да-да, в случае обработки большого массива изначальных данных QUERY работает быстрее чем стандартный Pivot table.
А еще QUERY прекрасно дружит с ImportRange что позволяет делать на Google Sheets полноценные базы данных.
Но, обо всём по порядку. Для начала работы понадобятся непосредственно данные, которые нужно обработать. У меня есть пример таблицы с продажами — по датам, менеджерам, товарам и городам:
К примеру я хочу получить все продажи по городу «Уфа». Первое: получаю вообще все данные.
Чтобы в дальнейшем не писать необходимый диапазон ячеек, создам именованный диапазон «DataBase»:
И напишу формулу:
=QUERY(DataBase;"SELECT *")SELECT используется для указания возвращаемых столбцов и их порядка. Если это предложение не указано или если используется select *, возвращаются все столбцы таблицы источника данных в их исходном порядке.
Результат функции QUERY будет таким же как и исходная таблица. Мне же надо получить таблицу с данными по «Уфе»
=QUERY(DataBase;"SELECT * WHERE F = 'Уфа'")Получаю:
WHERE используется для возврата только тех строк, которые соответствуют указанному условию.
Условие WHERE поддерживает простые операторы сравнения: <=, <, >, >=, =, !=, <>. При этом , оба оператора сравнения != <> означают не равно. Строки сравниваются по лексическому значению. Сравнение с нулем выполняется с использованием значения null или не равно null.
А если мне нужна просто сводка «итого» (но не по сумме, а вообще всех продаж) по городу в разрезе дат и товара, то, зная что Select возвращает необходимый порядок, функцию можно переписать так:
=QUERY(DataBase;"SELECT F,B,E,G WHERE F = 'Уфа'")И результат:
А теперь, упорядочиваю по менеджеру
=QUERY(DataBase;"SELECT F,B,E,G WHERE F = 'Уфа' ORDER BY G")Получаю:
order by используется для сортировки строк по значениям в указанных столбцах. После указанной строки можно указать asc — для упорядочивания от А до Я или desc — для упорядочивания от Я до А.
Усложню базовую задачу. Из результата мне нужен только Сидоров и Петров:
=QUERY(DataBase;"SELECT F,B,E,G WHERE F = 'Уфа' AND G CONTAINS 'ров' ORDER BY G desc")Что у них общего? Фамилия заканчивается на «ров». CONTAINS — это совпадение подстроки. Целое содержит часть истинно, если часть находится где-нибудь внутри целого.
Ну и под конец первой записи, собираю данные в суммовых значениях:
=QUERY(DataBase;"SELECT F,B,G, sum(E) WHERE F='Уфа' GROUP BY F,B,G")Результат:
Ненаглядно…. Менеджер Михайлов в начале и в конце. Упорядочивать уже умею:
=QUERY(DataBase;"SELECT F,B,G, sum(E) WHERE F='Уфа' GROUP BY F,B,G ORDER BY G")
На этом пока всё. В следующих частях я поговорю о сложных техниках работы с QUERY.