Це друга частина нашого мануала з функції 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)
Бажаєте отримувати дайджест статей?
