Это вторая часть нашего мануала по функции QUERY в Google Sheets. В первой части мы расписали ее основной функционал, конструкции, синтаксис, формулы импорта, разные способы сортировки, группирования и агрегации данных.
Вторая часть посвящена кляузе WHERE и типичным ошибкам, которые могут возникать в работе с QUERY. В этот раз мы снова взяли в качестве примера данные о топовых фильмах из Википедии, как и в первой части, — и исследовали различные примеры их использования с помощью QUERY.
Для удобства все формулы будут сохранены в гугл-таблице. Чтобы получить полное понимание возможностей функции, рекомендуем прочитать обе части мануала.
Функция WHERE в формуле QUERY
Мы начнем с простых шагов, а в конце создадим таблицу на вкладке Country + Subgenre.
Шаг 1. Базовые критерии, которые нужно знать для работы с WHERE
Возьмем тот же самый источник из Википедии с фильмами
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");"Select * ")
Напоминаю, что у нас есть:
1) Rank — ранг в формате Текст от 1. до 100. (не число, потому что есть точка в конце)
2) 10th anniversary list (2007) — название фильма в формате Текст
3) Director — режиссер в формате Текст
4) Year — год в формате Число
5) Production companies — компания, формат Текст
6) Change from 1998 — количество изменений в формате Число
Обратите внимание! В конце каждой колонки указан ее формат (текст или число).
Как и в языках программирования, мы имеем дело с разным распознаванием введенных данных в формулу. Например, если мы хотим сказать, что:
Col4 = 1967
то не ставим одинарные кавычки, потому что наш Year существует в формате Число.
Наш Director — текст, поэтому если мы планируем вывести значения, где режиссер = Стивен Спилберг, то наш запрос будет обрамлен в кавычки:
Col3 = 'Steven Spielberg'
Если мы работаем с датой, то добавляем метку date и оборачиваем в одинарные кавычки:
Col60 = date'2023-05-01'
Символы сравнения, которые существуют
и поддерживаются в WHERE:
* Если интересно узнать больше о matches, перечень здесь
Возвращаемся к нашей задаче. Например, мы хотим получить таблицу, где режиссер Стивен Спилберг:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"Select * where Col3 = 'Steven Spielberg'")
А теперь давайте выведем таблицу, где год = 1946:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"Select * where Col4 = 1946")
А теперь — таблицу, где могла быть задействована кинокомпания 20th Century Fox. Поскольку некоторые фильмы делают несколько компаний, мы поставим like ‘%20th Century-Fox%’, потому что она может быть написана как первой, так и последней:
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/AFI%27s_100_Years...100_Movies_(10th_Anniversary_Edition)";"Table");
"Select * where Col5 like '%20th Century-Fox%'")
Шаг 2. Работа с динамическими показателями
Здесь уделим внимание работе с динамическими (изменяемыми) параметрами. На практике редко можно встретить ситуацию, в которой нужно статически указать, что год = 1946 или кинокомпания = 20th Century Fox.
В QUERY мы можем задать значение, содержащееся в ячейке. То есть год = не 1946, а год = значению, которое мы введем в ячейку А2.
Например, создадим таблицу clicks:
В этой таблице мы имеем значения по Country — Clicks — Date.
Выведем уникальные страны с помощью формулы
=UNIQUE(A2:A)
получаем уникальные значения по атрибуту Country:
➡️ Теперь создадим динамическую таблицу, которая будет выводить значения в зависимости от выбранной страны.
Создадим выпадающий список значений Country. В гугл-таблицах это делается через Данные — Проверка данных.
Справа появится панель «Проверка данных» — жмем «Добавить новое», категория — «Выпадающее меню (из диапазона)» — выбираем диапазон данных, в котором ранее мы просчитали уникальные значения для Country:
Теперь у нас есть выпадающий список с параметрами в клеточке В2:
Чтобы задать значение клеточки в query, нужно использовать незнакомый синтаксис.
Если мы введем
=QUERY(clicks!A:C;" SELECT * WHERE A = B2")
то увидим ошибку:
Для работы с клетками таблицы в query мы используем вот такой синтаксис формулы:
=QUERY(clicks!A:C;" SELECT * WHERE A = '"&B2&"'")
То есть В2 обернута в одинарные кавычки (‘ ‘), потом двойные (“ “), а потом еще в амперсанд (&&).
Если отделить пробелами, вот как это выглядит: ‘ “ & B2 & ” ’
Получаем такую таблицу, которая будет изменяться в зависимости от выбранного значения в клетке В2:
➡️ Задача следующая — доработать формулу в гугл-таблицах, чтобы, если в клетке В2 не было введено никакое значение, выводило весь список.
В чем необходимость такой задачи? Ранее мы указали, что А должно равняться значению в ячейке В2
(WHERE A = '"&B2&"')
так что если клетка В2 будет пустой (B2 is null), то и А будет равняться значению «пусто», или null — и значения выведены в таблицу не будут.
Сделаем это с помощью функции IF.
Нам нужно, чтобы если значение В2 = пусто, то не выводило ничего, а если не пусто, то выводило В2. То есть так:
=IF(B2 = "";; "WHERE A = '"&B2&"' ")
А теперь подставляем в формулу QUERY:
=QUERY(clicks!A:C;" SELECT * "&IF(B2 = "";; "WHERE A = '"&B2&"' "))
Теперь таблица выглядит так, если значение не выбрано:
И так, если выбрано:
➡️ Задача следующая — добавить фильтрацию по дате.
Работа с датами в query будет немного отличаться. Сделаем 2 отдельные ячейки для ввода данных, в которых в ячейке В3 будет содержаться минимальная дата (date start), а в В4 — максимальная (date end):
Для дат мы используем такой синтаксис:
C <= date'"&TEXT(B4;"yyyy-mm-dd")&"'
То есть значение даты в формате TEXT(B4;"yyyy-mm-dd") оборачиваем в ту же конструкцию: одинарные кавычки + двойные кавычки + амперсанды (‘ “ & значение & ” ’).
После этого еще, как и раньше для даты, мы оборачиваем в параметр date.
Формула имеет такой вид:
=QUERY(clicks!A:C;" SELECT * WHERE A = '"&B2&"' AND C >= date'"&TEXT(B3;"yyyy-mm-dd")&"' AND C <= date'"&TEXT(B4;"yyyy-mm-dd")&"'")
А таблица для страны Турция за период с 2023-02-15 по 2023-03-01 — вот такой:
➡️ Следующая задача — вывести значения по стране, дате старта, дате окончания, в которых поле ячейки — не пустое.
Таблица Clicks содержит пустые (или null) значения в колонке Clicks — нам нужно их исключить:
Для работы с пустыми клетками будем использовать синтаксис is not null.
Если мы зададим условие, где колонка В не равняется пустому значению, то есть
where B <> “”
то увидим вот такую ошибку:
Значения null обрабатываются в таблице не так, как другие. То есть если бы вы хотели указать, что значение в таблице не равно 100, то конструкция выше была бы абсолютно корректной. Но с null нужно только через is not null, если вам необходимо вывести значения, не равные пустоте, и is null — если равны.
Правильная формула имеет вот такой вид:
=QUERY(clicks!A:C;" SELECT * WHERE A = '"&B2&"' AND C >= date'"&TEXT(B3;"yyyy-mm-dd")&"' AND C <= date'"&TEXT(B4;"yyyy-mm-dd")&"' AND B IS NOT NULL")
И вот таблица:
➡️ Теперь создадим таблицу из ссылки https://en.wikipedia.org/wiki/List_of_horror_films_of_2023, которая будет фильтроваться по таким ключам:
-
Country — ячейка В2
- Subgenre — ячейка В3
Примечание: если значение пустое, то выводить все, если нет — то значения фильтра.
Сначала сделаем подготовку. На листе preparing я отдельно вывела уникальные значения для нашего выпадающего списка, как мы делали раньше:
Если вы заметили, значения колонки Subgenre содержат квадратные скобки с цифрами, что достаточно неудобно для фильтрации.
Я обработала данные и вывела их в уникальные значения с помощью такой формулы:
=UNIQUE(QUERY(ARRAYFORMULA(SPLIT(query(importhtml("https://en.wikipedia.org/wiki/List_of_horror_films_of_2023";"Table");"Select Col5 ");"["));"SELECT Col1 OFFSET 1"))
После этого в формуле таблицы вместо знака равно (=) мы будем использовать contains.
Итак, мы имеем таблицу из Википедии, и чтобы выполнить задание, нам нужно просчитать 4 варианта развития событий:
1 — когда Country и Subgenre пустые
2 — когда Country пустое, а Subgenre — нет
3 — когда Country не пустое, а Subgenre пустое
4 — когда Country и Subgenre не пустые
В нашей импортированной таблице Country = Col4, Subgenre = Col5
Формула IF:
#1. Зададим условие: если Country пустое, то Country = B2:
IF(B2 = “”; “”; "WHERE Col4 = '"&B2&"'")
#2. Добавим в нашу формулу проверку: если Country пустое, то пустое ли Subgenre? Если так, то пусто, если нет, то = В3 (то есть прорабатываем пункты 1–3 нашего ТЗ):
IF(B2 = “”; IF(B3="";"";"WHERE Col5 contains '"&B3&"'"); "WHERE Col4 = '"&B2&"'")
#3. Добавляем последний пункт — когда все не пусто:
IF(B2="";
IF(B3="";"";"WHERE Col5 contains '"&B3&"'");
IF(B3="";"WHERE Col4 = '"&B2&"'";"WHERE Col4 = '"&B2&"' AND Col5 contains '"&B3&"'"))
Наша формула имеет такой вид:
=query(importhtml("https://en.wikipedia.org/wiki/List_of_horror_films_of_2023";"Table");"Select * "&
IF(B2="";
IF(B3="";"";"WHERE Col5 contains '"&B3&"'");
IF(B3="";"WHERE Col4 = '"&B2&"'";"WHERE Col4 = '"&B2&"' AND Col5 contains '"&B3&"'"))&
IF(AND(B2="";B3="");"OFFSET 1";"")
Гугл-Таблица выглядит так:
Весь бизнес-контент в удобном формате
6 самых распространенных ошибок в QUERY
#1. #REF: результат массива не развернут, иначе он переписал бы данные в B3.
Результат не будет введен, если в ячейке, в которой должно быть содержание таблицы QUERY, введено что-то лишнее. Просто удалите это.
#2. #REF: не удается найти диапазон или таблицу для импортированного диапазона.
Эта ошибка свидетельствует об ошибке ввода данных диапазона или листа. Проверьте правильность данных.
#3. #VALUE: не удается проанализировать строку запроса для "Параметр 2 функции QUERY": NO_COLUMN: col4
Как мы писали в предыдущей статье, если введено col1 вместо Col1, то будет ошибка, как на картинке выше.
#4. #VALUE: не удается проанализировать строку запроса для "Параметр 2 функции QUERY": NO_COLUMN: Col7
Это означает, что, например, вы ввели диапазон с А по В — то есть 2 колонки, а в запросе select пытаетесь что-то сделать с колонкой G.
В таком случае следует изменить диапазон с A:B на A:G. QUERY будет обрабатывать только данные, которые вы вводили в диапазоне ранее.
#5. #VALUE: не удается проанализировать строку запроса для "Параметр 2 функции QUERY": ADD_COL_TO_GROUP_BY_OR_AGG: Col3
Если есть агрегация, то должно быть и группирование по всем значениям, не содержащим агрегацию.
#6. QUERY сжимает первые строки в одну строку заголовков. Вы встретитесь с ситуациями, когда пишете формулу и в конце вас ждет результат, в котором в первой строке импортировано сразу 3 строки.
Например, вот так это выглядит:
Для решения этой проблемы функция QUERY содержит опциональный компонент в конце формулы:
QUERY(диапазон; запрос; [заголовки])
Из гугл-справки: «Заголовки — [НЕОБЯЗАТЕЛЬНО] — количество заглавных строк в верхней части раздела данных. Если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержания данных».
На практике эту проблему можно решить, добавив в конце единицу. Вот так будет выглядеть формула:
=QUERY(A:B;" SELECT * "; 1)
Хотите получать дайджест статей?
