Мощная функция QUERY в Google таблицах. Основные операторы и примеры использования.
Введение
Функция QUERY в Google таблицах – это инструмент, который позволяет извлекать подмножество данных из основного набора данных.
Функция QUERY использует язык запросов SQL. Это означает, что для использования функции, потребуются знания синтаксиса SQL, который выглядит немного сложнее, чем встроенные функции Google таблиц, но однажды поняв его, вы начнете очень быстро обрабатывать данные этой функцией.
QUERY помогает фильтровать, группировать и сортировать данные – все это необходимо для организации эффективных отчетов.
Видео: Функция QUERY в Google таблицах.
В обзорном видео ниже, мы демонстрируем возможности функции QUERY в гугл таблицах и рассматриваем подробнее работу с основными операторами SELECT, WHERE, GROUP BY, PIVOT, ORDER BY и LABEL.
Синтаксис функции:
QUERY(данные; запрос; [заголовки])
где:
данные — это диапазон ячеек, который содержит наши данные;
запрос — строка, содержащая запрос, который мы хотим выполнить со своими данными;
заголовки — необязательный аргумент, в котором мы можем указать, количество строк заголовков поверх нашего набора данных
Рассмотрим основные операторы QUERY, с которыми мы чаще всего сталкиваемся на практике - SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LABEL.
Эти операторы можно использовать как по отдельности, так и комбинировать. Но порядок имеет значение. Если использовать сначала GROUP BY, а затем WHERE, то Google таблицы выдадут ошибку и формула не будет функционировать.
Оператор SELECT
SELECT – это оператор, который определяет столбцы, которые мы хотим отобразить в отчете.
Если мы хотим выбрать все столбцы, то необходимо использовать *, но если нужны только некоторые столбцы, потребуется перечислить их названия.
Допустим, у нас есть таблица, содержащая данные о работниках, имеющих следующие столбцы: имя, фамилия, стаж, образование, дата рождения и должность.
Указав функцию:
=QUERY(A1:F21;"SELECT A,B,F")
мы получим соответствующие столбцы.
Указав *,
=QUERY(A1:F21;"SELECT *")
функция выведет все столбцы из диапазона.
Иногда может потребоваться собрать данные с нескольких диапазонов, в таком случае диапазоны необходимо заключить в фигурные скобки.
=QUERY({A1:F21;'Лист2'!A2:F5};"SELECT *")
Без фигурных скобок мы получим ошибку. Если мы добавили фигурные скобки, то обращения к столбцам по букве (как рассмотрели выше) работать не будут. В таком случае, необходимо обращаться к номеру столбца.
Col1 – это, в нашем примере столбец А, Col2 – столбец В и так далее.
=QUERY({A1:F21;'Лист2'!A2:F5};"SELECT Col1,Col2,Col6")
Оператор WHERE
WHERE – это оператор, который фильтрует столбцы на основе указанных условий.
Здесь можно использовать операторы сравнения, такие как "=", "<>", "<", и ">".
Рассмотрим, несколько примеров:
Пример 1: мы хотим выбрать только тех работников, кто занимает должность Аналитик:
=QUERY(A1:F21 ; "SELECT * WHERE F = 'Аналитик'")
Пример 2: мы хотим выбрать только тех работников, кто рожден в 1990 году:
=QUERY(A1:F21 ; "SELECT * WHERE E >= date '"&ТЕКСТ("01/01/1990";"yyyy-mm-dd")&"' AND E <= date '"&ТЕКСТ("01/01/1991";"yyyy-mm-dd")&"'")
Пример 3: мы хотим выбрать только тех работников, кто рожден в 1990 году и занимает должность Аналитик или Бухгалтер:
=QUERY(A1:F21 ; "SELECT * WHERE E >= date '"&ТЕКСТ("01/01/1990";"yyyy-mm-dd")&"' AND E <= date '"&ТЕКСТ("01/01/1991";"yyyy-mm-dd")&"' AND (F = 'Аналитик' OR F = 'Бухгалтер')")
Обратите внимание на взятое в скобки условие OR, без скобок мы получим абсолютной другой результат.
Пример 4: мы хотим выбрать только тех работников, чье имя начинается на "А".
=QUERY(A1:F21 ; "SELECT A, B, F WHERE A LIKE 'А%'")
Если нам необходимо выбрать условие из конкретной ячейки, то мы не сможем просто указать ее адрес, потребуется определенный синтаксис.
Например, нам необходимо вывести всех сотрудников с высшим образованием. При этом у нас есть отдельная ячейка, в которой указано условие.
Если мы укажем так:
=QUERY(A1:F21; "SELECT * WHERE D = H1")
то получим ошибку.
Синтаксис QUERY требует заключать привычные нам ссылки на ячейки в двойные кавычки и амперсанты.
=QUERY(A1:F21 ; "SELECT * WHERE D = '"&H1&"'")
Оператор GROUP BY
GROUP BY – это оператор, который позволяет группировать ряды данных по определенному столбцу и вычислять агрегатные функции над значениями в каждой группе.
Это полезно, когда мы хотим сделать сводку данных по определенной категории.
Агрегатные функции: SUM, MIN, MAX, AVG и COUNT.
Пример: посчитаем, сколько работников в какой должности.
=QUERY(A:F ; "SELECT F, count(F) GROUP BY F")
Чтобы избавиться от пустых строк, добавим условие «F is not Null»:
=QUERY(A:F; "SELECT F, count(F) WHERE F is not Null GROUP BY F")
Оператор PIVOT
PIVOT – это оператор, который позволяет создавать сводную таблицу.
Пример: создадим сводную таблицу с должностями по строкам и видами образования по столбцам:
=QUERY(A1:F21 ; "SELECT F, count(F) GROUP BY F PIVOT D")
В этом примере мы выбираем столбец F для представления строк сводной таблицы, столбец D для представления столбцов сводной таблицы и ведем подсчет значений в столбце F для отображения внутри сводной таблицы.
Оператор ORDER BY
Оператор ORDER BY в функции QUERY сортирует данные в таблице по заданному столбцу.
Например, если нужно отсортировать список сотрудников по фамилии, можно использовать запрос:
=QUERY(A1:F21; "SELECT * ORDER BY B")
Добавляя ASC или DESC можно регулировать порядок.
ASC – сортировка во возрастанию или от А до Я (по умолчанию)
=QUERY(A1:F21; "SELECT ORDER BY B ASC")
DESC – сортировка по убыванию или от Я до А
=QUERY(A1:F21; "SELECT ORDER BY B DESC ")
С помощью оператора LIMIT мы можем ограничить количество возвращаемых строк.
Например, выведем трех сотрудников с наивысшим стажем:
=QUERY(A1:F21; "SELECT * ORDER BY B DESC LIMIT 3")
Оператор LABEL
Оператор LABEL в функции QUERY позволяет изменить названия столбцов таблицы.
Пример, выведем столбцы А и В без заголовка:
=QUERY(A1:F21 ; "SELECT A, B LABEL A'',B'' ")
Пример, выведем все столбцы, и изменим заголовок только для столбца А:
=QUERY(A1:F4 ; "SELECT * LABEL A 'Имя сотрудника'")
Менять названия можно как для имеющихся столбцов, так и для новых столбцов, которые мы получаем в результате агрегирования.
Например, сгруппируем данные по виду образования и поменяем заголовок для столбца с подсчетом количества:
=QUERY(A1:F21 ; "SELECT D, count(D) GROUP BY D LABEL count(D) 'Количество'")
Функция QUERY без проблем сочетается с другими встроенными функциями в Google таблицах. Наиболее часто, на практике, ее можно встретить с функцией IMPORTRANGE.
Заключение
Функция QUERY является мощным инструментом для работы с данными в гугл таблицах. Она позволяет выполнять различные операции с данными, такие как выборка, сортировка и группировка.
Используйте ее для упрощения своей работы!
Помощь с автоматизацией рабочих процессов
Мы занимаемся автоматизацией бизнес-процессов как в Excel и Google таблицах для малого бизнеса, так и более масштабной разработкой CRM-систем, под запросы конкретного бизнеса.
На связи, чтобы обсудить все детали и подобрать правильные инструменты для решения Вашей задачи:
WhatsApp: +7 (919) 882-93-67
Telegram: birdyx_ru
E-mail: mail@birdyx.ru
А также, у нас Вы можете пройти бесплатные онлайн курсы по MS Excel с заданиями.