Query Google Таблицы: полное руководство

Query
Блог
Query Google Таблицы: полное руководство

Правило Парето гласит, что 20% усилий приносят 80% результата. В контексте Google Таблиц это правило особенно актуально: освоив функцию QUERY, вы сможете заменить десятки других функций и автоматизировать большинство рутинных операций с данными.

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

Преимущества использования QUERY очевидны. Во-первых, это скорость: одна формула QUERY может заменить десятки обычных формул. Во-вторых, гибкость: вы можете легко модифицировать запрос, меняя условия выборки или способ обработки данных. В-третьих, это производительность: QUERY работает намного быстрее, чем комбинации стандартных функций.

Базовый синтаксис QUERY

Основная структура функции QUERY выглядит следующим образом:

=QUERY(данные; "запрос"; [заголовки])

Где:

данные – это диапазон ячеек, который будет обрабатываться
запрос – текст SQL-подобного запроса в кавычках
заголовки – необязательный параметр, указывающий количество строк с заголовками

Рассмотрим простой пример. Допустим, у нас есть таблица продаж с колонками: Дата, Товар, Количество, Цена. Чтобы получить список всех продаж определенного товара, мы можем использовать:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Ноутбук'")

Этот запрос вернет все строки, где в колонке B (Товар) указан "Ноутбук".

Начинающие пользователи часто допускают несколько типичных ошибок:

Забывают указывать кавычки вокруг текстовых значений в условиях. Правильно: "WHERE B = 'Ноутбук'", неправильно: "WHERE B = Ноутбук"
Путают синтаксис обращения к столбцам. В QUERY можно использовать либо буквенные обозначения (A, B, C), либо Col1, Col2, Col3, но не смешивать их в одном запросе
Неправильно работают с датами. Для сравнения дат необходимо использовать конструкцию:

"WHERE A >= date '2024-02-19'"

При работе с большими наборами данных важно помнить, что QUERY обрабатывает только явно указанный диапазон. Если вы добавляете новые строки за пределами диапазона, они не будут учитываться в результатах. Поэтому лучше всегда использовать динамические диапазоны или формулы типа:

=QUERY(A1:A; "SELECT A WHERE A is not null")

Функция QUERY также позволяет комбинировать несколько условий с помощью операторов AND и OR:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Ноутбук' AND C > 5")

Этот запрос найдет все продажи ноутбуков с количеством больше 5 единиц. Обратите внимание на пробелы в запросе – они необходимы для правильного разделения частей запроса.

Основные операции с данными

Функция QUERY предоставляет широкий набор инструментов для работы с данными. Начнем с базовой выборки данных через оператор SELECT. Этот оператор позволяет указать, какие именно столбцы мы хотим получить в результате.

Простейший пример выборки всех данных:

=QUERY(A1:E100; "SELECT A, B, C, D, E")

Можно использовать символ "*" для выбора всех столбцов:

=QUERY(A1:E100; "SELECT *")

Фильтрация данных осуществляется с помощью оператора WHERE. Например, для выборки продаж за определенный период:

=QUERY(A1:E100; "SELECT * WHERE A >= date '2024-01-01' AND A <= date '2024-01-31'")

При работе с датами важно использовать правильный формат. QUERY поддерживает несколько операторов сравнения:

= равно

больше

< меньше

= больше или равно

<= меньше или равно
<> не равно

Сортировка результатов выполняется с помощью ORDER BY. Например, сортировка по дате в обратном порядке:

=QUERY(A1:E100; "SELECT * ORDER BY A DESC")

Продвинутые возможности

QUERY становится особенно мощным инструментом при использовании агрегатных функций. Основные агрегатные функции включают:

SUM() - суммирование значений:

=QUERY(A1:E100; "SELECT B, SUM(C) GROUP BY B")

AVG() - среднее значение:

=QUERY(A1:E100; "SELECT B, AVG(C) GROUP BY B")

COUNT() - подсчет количества значений:

=QUERY(A1:E100; "SELECT B, COUNT(C) GROUP BY B")

Для создания перекрестных таблиц используется оператор PIVOT. Например, чтобы увидеть продажи по месяцам и категориям:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(D) GROUP BY MONTH(A) PIVOT B")

Вычисляемые поля создаются прямо в запросе. Например, чтобы посчитать общую стоимость:

=QUERY(A1:E100; "SELECT A, B, C, D, (C * D) WHERE C > 0")

При работе с несколькими диапазонами можно использовать фигурные скобки для объединения данных:

=QUERY({Sheet1!A1:D100; Sheet2!A1:D100}; "SELECT Col1, SUM(Col4) GROUP BY Col1")

Особенно полезной функцией является возможность создания временных расчетов. Например, для расчета процента от общего:

=QUERY(A1:E100; "SELECT B, SUM(C), SUM(C)/SUM(SUM(C)) OVER() GROUP BY B")

В этом случае OVER() указывает, что расчет должен производиться относительно всего набора данных. Такие вычисления особенно полезны при создании аналитических отчетов, где требуется сравнение с общими показателями.

Для работы с текстовыми данными QUERY предоставляет несколько полезных операторов:

CONTAINS - проверка на содержание подстроки
STARTS WITH - проверка начала строки
ENDS WITH - проверка окончания строки

Пример использования текстового поиска:

=QUERY(A1:E100; "SELECT * WHERE B CONTAINS 'iPhone' OR B STARTS WITH 'Mac'")

Оставьте заявку

Укажите ваше имя и email, наши менеджеры свяжутся с Вами в ближайшее время

Форматирование и оформление результатов

Результаты работы QUERY можно сделать более читабельными и понятными с помощью специальных операторов форматирования. Ключевым инструментом здесь выступает оператор LABEL, который позволяет изменять заголовки столбцов на более понятные названия.

Рассмотрим пример отчета по продажам с переименованием столбцов:

=QUERY(A1:E100; "SELECT A, SUM(B), AVG(C)
LABEL A 'Дата продажи',
SUM(B) 'Общая сумма',
AVG(C) 'Средний чек'")AVG(C) 'Средний чек'")

Для форматирования числовых значений используется оператор FORMAT. Он позволяет задать формат вывода для каждого столбца. Например, для отображения денежных значений и процентов:

=QUERY(A1:E100; "SELECT A, B, B/SUM(B)
FORMAT B '#,##0.00 ₴',
B/SUM(B) '#.##%'")B/SUM(B) '#.##%'")

При работе с датами часто требуется группировка по месяцам или кварталам. QUERY позволяет это сделать с помощью встроенных функций:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(B)
GROUP BY MONTH(A)
LABEL MONTH(A) 'Месяц',
SUM(B) 'Продажи за месяц'")SUM(B) 'Продажи за месяц'")

Практические примеры использования

Рассмотрим несколько реальных сценариев использования QUERY для решения бизнес-задач.

Анализ продаж по категориям с динамикой роста:

=QUERY(Sales!A1:E100; "SELECT
B,
SUM(C),
SUM(C) - LAG(SUM(C)) OVER()
GROUP BY B
LABEL
B 'Категория',
SUM(C) 'Общие продажи',
SUM(C) - LAG(SUM(C)) OVER() 'Изменение'")

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

Для автоматизации ежедневных отчетов можно использовать динамические даты:

=QUERY(Data!A1:E100; "SELECT A, SUM(B)
WHERE A >= date '"&TEXT(TODAY()-7;"yyyy-mm-dd")&"'
GROUP BY A
ORDER BY A DESC")

Этот запрос автоматически показывает данные за последние 7 дней.

Создание сводной таблицы по менеджерам и продуктам:

=QUERY(Sales!A1:F100; "SELECT
C,
SUM(E)
GROUP BY C
PIVOT D
LABEL
C 'Менеджер'
FORMAT
SUM(E) '#,##0.00 ₴'")

Для крупных наборов данных полезно создавать промежуточные итоги:

=QUERY(Data!A1:E100; "SELECT
B,
D,
SUM(E),
SUM(E)/SUM(SUM(E)) OVER()
GROUP BY B, D
LABEL
B 'Регион',
D 'Продукт',
SUM(E) 'Продажи',
SUM(E)/SUM(SUM(E)) OVER() 'Доля от общих продаж'
FORMAT
SUM(E) '#,##0.00',
SUM(E)/SUM(SUM(E)) OVER() '0.00%'")

Такой запрос создает подробный отчет с разбивкой по регионам и продуктам, включая долю каждой комбинации в общих продажах.

Оптимизация и производительность

При работе с большими наборами данных производительность QUERY становится критически важным фактором. Таблицы, содержащие более 10000 строк, могут обрабатываться значительно медленнее, если запрос составлен неоптимально. Рассмотрим основные приемы оптимизации.

Первое правило – всегда ограничивайте диапазон данных только необходимыми столбцами. Вместо выборки всего диапазона A1:Z1000, выбирайте только нужные столбцы. Например, если вам нужны только данные о продажах и датах, используйте:

=QUERY(A1:B1000; "SELECT A, B WHERE A is not null")

Для ускорения работы с датами используйте предварительную фильтрацию. Вместо:

=QUERY(A1:E1000; "SELECT * WHERE A >= date '2024-01-01'")

Лучше сначала отфильтровать данные по дате, а затем применять дополнительные условия:

=QUERY(FILTER(A1:E1000; A1:A1000 >= DATE(2024;1;1)); "SELECT Col1, Col2 WHERE Col2 > 0")

При работе с большими объемами данных важно избегать избыточных вычислений. Если вам нужно провести несколько операций с одними и теми же данными, создайте промежуточную таблицу с помощью первого QUERY, а затем работайте с ней:

=QUERY( QUERY(A1:E1000; "SELECT A, B, SUM(C) GROUP BY A, B"); "SELECT Col1, SUM(Col3) GROUP BY Col1" )

Заключение и советы

QUERY – это мощный инструмент, который может значительно упростить работу с данными в Google Таблицах. Основываясь на практическом опыте, можно выделить несколько типичных сценариев использования, где QUERY особенно эффективен.

Для ежедневных отчетов рекомендуется создавать шаблоны запросов с динамическими параметрами:

=QUERY(Sales!A1:E1000;
"SELECT A, B, SUM(C)
WHERE A >= date '"&TEXT(TODAY()-30;"yyyy-mm-dd")&"'
GROUP BY A, B
PIVOT B")=QUERY(Sales!A1:E1000; "SELECT A, B, SUM(C) WHERE A >= date '"&TEXT(TODAY()-30;"yyyy-mm-dd")&"' GROUP BY A, B PIVOT B")

QUERY отлично комбинируется с другими функциями Google Таблиц. Например, для создания динамических графиков можно использовать комбинацию QUERY и SPARKLINE:

=SPARKLINE(
QUERY(A1:B100; "SELECT B WHERE A >= date '"&TEXT(TODAY()-7;"yyyy-mm-dd")&"'")
)=SPARKLINE( QUERY(A1:B100; "SELECT B WHERE A >= date '"&TEXT(TODAY()-7;"yyyy-mm-dd")&"'") )

При построении сложных отчетов полезно разбивать запросы на несколько этапов:

Сначала отфильтровать данные:

=QUERY(Data!A1:E1000; "SELECT * WHERE A is not null")

Затем группировать результаты:

=QUERY(FilteredData; "SELECT A, SUM(B) GROUP BY A")

И наконец, форматировать вывод:

=QUERY(GroupedData; "SELECT * FORMAT Col2 '#,##0.00'")

Дальнейшее изучение QUERY рекомендуется начать с освоения более сложных операций:

Работа с вложенными запросами
Использование оконных функций
Создание сложных условных группировок
Применение регулярных выражений в фильтрах

Помните, что эффективность QUERY зависит не только от правильности синтаксиса, но и от структуры ваших данных. Хорошо организованные данные с четкой структурой позволят создавать более простые и производительные запросы.