Query no column ошибка

Это вторая часть нашего мануала по функции 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)