Функція QUERY: повний гайд із використання з прикладами | Бізнес-школа Laba (Лаба)
Для відстеження статусу замовлення - авторизуйтесь
Введіть код, який був надісланий на пошту Введіть код із SMS, який був надісланий на номер
anastasiiasytar@gmail.com
Код дійсний протягом 2 хвилин Код з SMS дійсний протягом 2 хвилин
Ви впевнені, що хочете вийти?
Сеанс завершено
На головну

Пошук

Зміст

Функція QUERY: повний гайд із використання з прикладами, ЧАСТИНА 1

Як вижати максимум з QUERY — однієї з найпотужніших функцій гугл-таблиць.

cover-642d6e80d1325875008598.png

Чому 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.

Рекомендуємо прочитати:

img-635638e3f23fa582655937.png

Як використовувати функцію VLOOKUP в Excel

Читати

Якщо ви використовуєте імпорт, то замість 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.

Бажаєте отримувати дайджест статей?

Один лист з найкращими матеріалами за місяць. Підписуйтесь, аби нічого не проґавити.
Дякуємо за вашу підписку!
Курс з теми:
«Excel для бізнесу»
Програми
Веде Олексій Вощак
16 квітня 14 травня
Олексій Вощак