Почему QUERY? Потому что это одна из самых мощных функций гугл-таблиц с обширным функционалом, который можно комбинировать и изменять по вашему желанию. Зная хотя бы базовый функционал QUERY, вы можете перекрыть ею огромную часть взаимодействия с таблицами. А еще оптимизируете и автоматизируете процессы, откажетесь от ручных данных и отчетов.
По своему синтаксису QUERY напоминает язык запросов SQL, и в ней правда есть где экспериментировать — она может быть простой, а может состоять из более чем 10 строк.
В этом мануале мы взяли в качестве примера данные о фильмах из Википедии и исследовали различные примеры их использования с помощью QUERY. Я буду предлагать решения, которыми лично пользуюсь в работе.
Например, вам нужно отфильтровать данные или создать динамическую «выпадашку», которая будет сортировать список по статической или динамической дате, названием либо ограничить данные (режиссер = Кевин Смит). В таком случае QUERY пригодится. Финальный результат нашей работы вы найдете по ссылке (в этом файле приведены все примеры последующих формул). Let's go!
Содержание мануала по QUERY:
#1. Основной функционал, конструкции и синтаксис
#2. Формулы импорта — например, выгрузка таблицы из википедии или импортирование данных из другой таблицы
#3. ORDER BY — сортировка по значению
#4. LIMIT/OFFSET — выведем топ-5 для вашей таблицы
#5. Переименование колонки таблицы с помощью LABEL
#6. GROUP BY — группировка и агрегация данных (складывать, умножать)
#1. Основной функционал, построение конструкции и синтаксис QUERY
Базовый синтаксис выглядит так:
=QUERY(диапазон данных; “SELECT (вызыватели и ключевые слова)”)
Стандартный вызов QUERY выглядит так:
=QUERY(A:B; “SELECT * ”)
где А:В – это диапазон, а «*» — вызов всех колонок из таблицы. В нашем случае SELECT * равен SELECT А, В — то есть все колонки из нашего диапазона.
При вызове импорта колонки будут называться не А и В, а Col1 и Col2. Мы можем писать ключевые слова любым регистром — можно QUERY, а можно query, так же SELECT, или select. Но что касается значений — условия четкие, только верхним регистром A и B или только Col1 и Col2.
Для ограничения колонок, например, нам нужна только колонка А, запрос выглядел бы так:
=QUERY(A:B; "SELECT А")
Для примера — вот так это выглядит:
А конечный вид, то есть после нажатия Enter — так:
После SELECT * или ввода необходимых колонок SELECT A либо SELECT Col1 начинается ввод других ключевых слов.
Формула будет работать и без них. Другие ключи — дополнительные аспекты, которые расширяют ваши возможности.
Вы можете использовать их по отдельности, а можете комбинировать. Но порядок имеет значение. Если использовать сначала ORDER BY, а затем WHERE, то гугл-таблицы обозначат это как ошибку и формула не будет функционировать.
#2. Формулы импорта в QUERY
Если нужно вывести таблицу с другого ресурса или из другой таблицы, функция QUERY поддерживает стандартные веб-функции гугл-таблиц для этого (скрин взят из гугл справки):
Наиболее распространенной из них является IMPORTRANGE, которая позволяет импортировать данные из другой google-таблицы.
Формулы импорта находятся в блоке «диапазон» QUERY.
Синтаксис
IMPORTRANGE = IMPORTRANGE("код ссылки"; "диапазон")
где код ссылки это:
А диапазон выглядит как название листа, из которого будет импорт, затем восклицательный знак и колонки без пробелов, например:
Basics! A:B — лист с названием Basics, колонки с А по В.
Пример финального вида формулы:
=QUERY(IMPORTRANGE("1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE";"Basics!A:B"); "SELECT *")
Важно: после написания формулы она подсветится красным.
Так и должно быть. Нажимаем Enter и смотрим на сообщение:
«Нужно подключить эти электронные таблицы. Когда целевая таблица впервые собирает данные из новой исходной таблицы, необходимо предоставить разрешение».
Нажимаем «Разрешить доступ» и получаем импортированную таблицу.
Если вы хотите использовать несколько таблиц или диапазонов — это можно сделать с помощью ARRAYFORMULA. Однако не забывайте, что данных не будет, если не открыт доступ к таблицам. При использовании формулы массива (array) такого сообщения об ошибке не будет, данные просто не будут выведены. Вам необходимо отдельно открыть доступ к каждой таблице, а затем группировать их в 1 диапазон.
Формула IMPORTHTML похожа на IMPORTRANGE. Вот пример, который мы будем использовать в статье:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_horror_films_of_2023";"Table"); "Select * ")
Как вы видите, разница в том, что здесь мы вставляем полную ссылку на источник, из которого получаем необходимую таблицу. Вы можете скопировать линк из формулы и посмотреть ее составляющие.
* Выше, в описании QUERY, я обращала внимание, что при использовании стандартного диапазона, то есть
=QUERY(A:D; "SELECT А, C, D")
вы используете название колонки, то есть конкретную букву — А, C, D
Если вы используете импорт, то вместо A будет Col1, вместо C — Col3, вместо D — Col4. И не забывайте о регистре.
Введете
=QUERY(IMPORTRANGE("1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE";"Basics!A:B"); "SELECT A")
будет ошибка.
Введете
=QUERY(IMPORTRANGE("1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE";"Basics!A:B"); "SELECT col1")
или
=QUERY(IMPORTRANGE("1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE";"Basics!A:B"); "SELECT COL1")
будет ошибка.
Правильный вариант:
=QUERY(IMPORTRANGE("1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE";"Basics!A:B"); "SELECT Col1")
*Выше я говорила об импорте из нескольких таблиц. Основное условие — таблицы должны быть схожи по структуре.
Цель — создать 1 общую таблицу из table1, table2, table3.
Делаем отдельный импорт для каждой таблицы, чтобы предоставить доступ и, как выше, нажимаем «Разрешить доступ».
Затем заключаем все это в фигурные скобки и получаем результат.
Вот так выглядит формула:
=QUERY(
{IMPORTRANGE("1b_gxNwr7KM7HNL4gdMCu-EnclI_TcfmcoF7PaSTCVGQ";"movie!A:E");
IMPORTRANGE("1NK0BCRgfAUch8oX6cwgFaVpACjeoRHsgUSH2c3Rjzc0";"movie!A:E");
IMPORTRANGE("11AEZOjU_QQ-j8Zfw1tRtjeCtkCJ8-SGqKd8jzBkWR20";"movie!A:E")};
"SELECT * ")
И вот так — таблица:
#3. Блок сортировки таблицы по критериям (лист ORDER BY нашей таблицы)
Здесь мы будем отталкиваться от топа фильмов: AFI's 100 Years…100 Movies — 10th Anniversary Edition (источник — Википедия). В своих таблицах вам не обязательно брать другой источник или другую таблицу. Диапазон может быть любым удобным для вашего использования.
Так что начало формулы мы возьмем из предыдущего блока:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select *")
С помощью SELECT * мы вывели все данные и теперь начинаем их сортировать.
Мы имеем:
- Rank — ранг в формате Текст от 1. до 100. (не число, потому что у нас есть точка в конце)
- 10th anniversary list (2007) — название фильма в формате Текст
- Director — режиссер в формате Текст
- Year — в формате Число (обратите внимание, что именно здесь формат не дата, а само число. Можете попытаться перевести его в формат Дата на вкладке «формат» шапки гугл-таблиц и посмотреть, что будет)
* по желанию, при использовании такой формулы =DATE(D3;1;1), где D3= число «1941», а «1» и «1» — это номер месяца и дня соответственно, вы можете превратить наш год в формате Число в Дату - Production companies — компания, формат Текст
- Change from 1998 — количество изменений в формате Число
А теперь отсортируем нашу таблицу по колонке Year. Мы можем сделать это с помощью ORDER BY. Этот артикул поддерживает значение DESC — на убывание, а ASC — на возрастание (используется по умолчанию).
Итак, Цель 1: указанный диапазон данных отсортировать по году создания на убывание:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * ORDER BY Col4 desc")
Цель 2: Отсортировать по году создания на убывание и по режиссеру на возрастание (помним, что на возрастание, то есть ASC, используется по умолчанию, поэтому его можно не писать).
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * ORDER BY Col4 desc, Col3")
Мы просто добавили запятую и дописали, что еще нам нужно отсортировать.
#4. Функции LIMIT/OFFSET в QUERY
Продолжаем использовать наш список из топ-100 фильмов за 100 лет.
Напоминаю, наша базовая формула выглядит так:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select *")
- LIMIT — для ограничения количества строк. Имейте в виду, что первая строчка (заголовков таблицы) не считается.
Попробуйте задать формуле такой вид и посмотреть, что будет:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * LIMIT 0")
А теперь такой:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * LIMIT 1")
- OFFSET — говорит, что считать нужно не с первой строки, а, например, с 3-й. В нашей таблице есть колонка Ранга, поэтому вы можете посмотреть результат самостоятельно, как и что выводится.
Займемся целью вывести 2 строки, начиная с 4-й (то есть limit 2 offset 3 — в этом случае третья строка не будет выводиться, offset 3 будет начинаться с 4-й строки таблицы):
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * LIMIT 2 OFFSET 3")
И наша таблица выглядит так:
В нашем случае для построения таблицы «Топ-5 фильмов» удачным решением будет использование комбинации LIMIT + ORDER BY. То есть мы сортируем по определенному параметру и выводим первые значения.
Весь бизнес-контент в удобном формате
Цель: вывести 5 последних фильмов из нашего списка.
Здесь мы используем сортировку на убывание с помощью ORDER BY, а затем ограничение количества строк с помощью LIMIT — и наша формула будет выглядеть следующим образом:
=QUERY(
IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"SELECT *
ORDER BY Col4 DESC
LIMIT 5")
А таблица — так:
#5. Переименование колонок с помощью LABEL
Рассмотрим на предыдущем примере. Смысл состоит в том, что QUERY создает не обычную таблицу, а целый массив. И если вы хотите изменить название колонки, то просто переписать вручную не получится.
Наше название 10th anniversary list (2007) — довольно длинное, так что можно обойтись обычным movie. Перепишем ее: добавляем новое название и получаем ошибку.
«Результат массива не развернут, иначе он переписал бы данные в B2».
Так что удаляем название movie и добавляем в конце нашей формулы LABEL Col2 'movie'
Заодно заменим Production companies на companies, а Change from 1998 на changes.
Формула:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"SELECT *
LABEL Col2 'movie', Col5 'companies', Col6 'changes'")
Таблица:
#6. Функция GROUP BY в QUERY
В этом блоке разберем группирование данных после проведения агрегации над ними.
Для примера имеем такой набор данных:
Наша цель — вывести таблицу, где будут подытожены данные из столбца «Количество» по названию:
То есть мы подытожили строки по полю «Количество» и прописали, что группировка должна быть по параметру «Название».
Получаем вывод — GROUP BY не имеет смысла без агрегации, а агрегация не сработает без GROUP BY.
Поддерживаемые функции агрегации:
Из предыдущего примера из таблицы с колонками «Название» и «Количество» давайте выведем формулу:
=QUERY(A:B;”Select A, sum(B), count(B), avg(B), max(B), min(B) GROUP BY A ")
* в нашем примере было только 2 колонки, из которых 1 агрегирована, а другая сгруппирована. В другой ситуации, где, например, у вас будет 10 колонок и 1 из них будет агрегирована, необходимо будет группировать по всем остальным 9 колонкам. Если вы уже пошли по этому пути, то нельзя оставлять ни одну колонку без агрегации или группировки.
Хотите получать дайджест статей?
Проработаем еще один пример.
Цель: вывести топ-10 режиссеров, чьи фильмы встречались наибольшее количество раз в нашем списке из 100 лучших лент за 100 лет в порядке убывания. В итоге должны получиться 2 колонки с названием Director и Count:
=QUERY(
IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"SELECT Col3, COUNT(Col3)
GROUP BY Col3
ORDER BY COUNT(Col3) DESC
LIMIT 10
LABEL Col3 'Director', COUNT(Col3) 'Count'
")
В результате мы получили такую таблицу:
Наша формула выглядит все более громоздкой. Чтобы не потеряться в своем коде, рекомендую разделять его на отдельные блоки, чтобы значения не терялись. Вы можете сделать абзац в формуле с помощью клавиш Alt + Enter.
Следует отметить, что символы:
«+» — суммирование
«-» — вычитание
«/» — деление
«*» — умножение
поддерживаются и не подпадают под группировку по предыдущей логике, потому что мы обрабатываем имеющееся число в существующей ячейке.
На сегодня все. Но мы планируем вторую часть гайда, где разберем кляузу WHERE и топ ошибок в работе с функционалом QUERY.
Хотите получать дайджест статей?
