Query Google Таблиці: повний посібник

Query
Блог
Query Google Таблиці: повний посібник

Правило Парето говорить, що 20% зусиль приносять 80% результату. У контексті Google Таблиць це правило особливо актуальне: опанувавши функцію QUERY, ви зможете замінити десятки інших функцій і автоматизувати більшість рутинних операцій з даними.

QUERY - це потужний інструмент для обробки даних, який використовує спрощений синтаксис SQL. Уявіть, що ваша таблиця - це база даних, а QUERY дає змогу робити в ній практично будь-які вибірки та перетворення. При цьому вам не потрібно бути програмістом або фахівцем з баз даних - достатньо освоїти кілька базових принципів.

Переваги використання QUERY очевидні. По-перше, це швидкість: одна формула QUERY може замінити десятки звичайних формул. По-друге, гнучкість: ви можете легко модифікувати запит, змінюючи умови вибірки або спосіб обробки даних. По-третє, це продуктивність: QUERY працює набагато швидше, ніж комбінації стандартних функцій.

Базовий синтаксис QUERY

Основна структура функції QUERY має такий вигляд:

=QUERY(дані; "запит"; [заголовки])

Де:

дані - це діапазон клітинок, який буде оброблятися
запит - текст SQL-подібного запиту в лапках
заголовки - необов'язковий параметр, що вказує кількість рядків із заголовками

Розглянемо простий приклад. Припустимо, у нас є таблиця продажів з колонками: Дата, Товар, Кількість, Ціна. Щоб отримати список усіх продажів певного товару, ми можемо використовувати:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Ноутбук'")

Цей запит поверне всі рядки, де в колонці B (Товар) зазначено "Ноутбук".

Користувачі-початківці часто припускаються кількох типових помилок:

Забувають вказувати лапки навколо текстових значень в умовах. Правильно: "WHERE B = 'Ноутбук'", неправильно: "WHERE B = Ноутбук"
Плутають синтаксис звернення до стовпців. У QUERY можна використовувати або літерні позначення (A, B, C), або Col1, Col2, Col3, але не змішувати їх в одному запиті
Неправильно працюють із датами. Для порівняння дат необхідно використовувати конструкцію:

"WHERE A >= date '2024-02-19'"

Під час роботи з великими наборами даних важливо пам'ятати, що QUERY обробляє тільки явно вказаний діапазон. Якщо ви додаєте нові рядки за межами діапазону, вони не враховуватимуться в результатах. Тому краще завжди використовувати динамічні діапазони або формули типу:

=QUERY(A1:A; "SELECT A WHERE A is not null")

Функція QUERY також дає змогу комбінувати кілька умов за допомогою операторів AND та OR:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Ноутбук' AND C > 5")

Цей запит знайде всі продажі ноутбуків із кількістю понад 5 одиниць. Зверніть увагу на пробіли в запиті - вони необхідні для правильного поділу частин запиту.

Основні операції з даними

Функція QUERY надає широкий набір інструментів для роботи з даними. Почнемо з базової вибірки даних через оператор SELECT. Цей оператор дає змогу вказати, які саме стовпці ми хочемо отримати в результаті.

Найпростіший приклад вибірки всіх даних:

=QUERY(A1:E100; "SELECT A, B, C, D, E")

Можна використовувати символ "*" для вибору всіх стовпців:

=QUERY(A1:E100; "SELECT *")

Фільтрація даних здійснюється за допомогою оператора WHERE. Наприклад, для вибірки продажів за певний період:

=QUERY(A1:E100; "SELECT * WHERE A >= date '2024-01-01' AND A <= date '2024-01-01-31'")

Під час роботи з датами важливо використовувати правильний формат. QUERY підтримує кілька операторів порівняння:

= дорівнює

більше

< менше

= більше або дорівнює

<= менше або дорівнює
<> не дорівнює

Сортування результатів виконується за допомогою ORDER BY. Наприклад, сортування за датою у зворотному порядку:

=QUERY(A1:E100; "SELECT * ORDER BY A DESC")

Просунуті можливості

QUERY стає особливо потужним інструментом при використанні агрегатних функцій. Основні агрегатні функції включають:

SUM() - підсумовування значень:

=QUERY(A1:E100; "SELECT B, SUM(C) GROUP BY B")

AVG() - середнє значення:

=QUERY(A1:E100; "SELECT B, AVG(C) GROUP BY B")

COUNT() - підрахунок кількості значень:

=QUERY(A1:E100; "SELECT B, COUNT(C) GROUP BY B")

Для створення перехресних таблиць використовується оператор PIVOT. Наприклад, щоб побачити продажі за місяцями та категоріями:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(D) GROUP BY MONTH(A) PIVOT B")

Обчислювані поля створюються прямо в запиті. Наприклад, щоб порахувати загальну вартість:

=QUERY(A1:E100; "SELECT A, B, C, D, (C * D) WHERE C > 0")

Під час роботи з кількома діапазонами можна використовувати фігурні дужки для об'єднання даних:

=QUERY({Sheet1!A1:D100; Sheet2!A1:D100}; "SELECT Col1, SUM(Col4) GROUP BY Col1")

Особливо корисною функцією є можливість створення тимчасових розрахунків. Наприклад, для розрахунку відсотка від загального:

=QUERY(A1:E100; "SELECT B, SUM(C), SUM(C)/SUM(SUM(C)) OVER() GROUP BY B")

У цьому випадку OVER() вказує, що розрахунок має проводитися щодо всього набору даних. Такі обчислення особливо корисні при створенні аналітичних звітів, де потрібне порівняння із загальними показниками.

Для роботи з текстовими даними QUERY надає кілька корисних операторів:

CONTAINS - перевірка на вміст підрядка
STARTS WITH - перевірка початку рядка
ENDS WITH - перевірка закінчення рядка

Приклад використання текстового пошуку:

=QUERY(A1:E100; "SELECT * WHERE B CONTAINS 'iPhone' OR B STARTS WITH 'Mac'")

Залиште заявку

Вкажіть ваше ім'я та email, наші менеджери зв'яжуться з Вами найближчим часом

Форматування та оформлення результатів

Результати роботи QUERY можна зробити більш читабельними і зрозумілими за допомогою спеціальних операторів форматування. Ключовим інструментом тут виступає оператор LABEL, який дає змогу змінювати заголовки стовпців на більш зрозумілі назви.

Розглянемо приклад звіту з продажу з перейменуванням стовпців:

=QUERY(A1:E100; "SELECT A, SUM(B), AVG(C)
LABEL A 'Дата продажу',
SUM(B) 'Загальна сума',
AVG(C) 'Середній чек'")AVG(C) 'Середній чек'")

Для форматування числових значень використовується оператор FORMAT. Він дає змогу задати формат виведення для кожного стовпця. Наприклад, для відображення грошових значень і відсотків:

=QUERY(A1:E100; "SELECT A, B, B/SUM(B)
FORMAT B '#,##0.00 ₴',
B/SUM(B) '#.##%'")B/SUM(B) '#.##%'")

Під час роботи з датами часто потрібне групування за місяцями або кварталами. QUERY дає змогу це зробити за допомогою вбудованих функцій:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(B)
GROUP BY MONTH(A)
LABEL MONTH(A) 'Місяць',
SUM(B) 'Продажі за місяць'")SUM(B) 'Продажі за місяць'")

Практичні приклади використання

Розглянемо кілька реальних сценаріїв використання QUERY для вирішення бізнес-завдань.

Аналіз продажів за категоріями з динамікою зростання:

=QUERY(Sales!A1:E100; "SELECT
B,
SUM(C),
SUM(C) - LAG(SUM(C)) OVER()
GROUP BY B
LABEL
B 'Категорія',
SUM(C) 'Загальні продажі',
SUM(C) - LAG(SUM(C)) OVER() 'Зміна'")

Цей запит створює звіт, що показує загальні продажі за категоріями та їхню зміну щодо попереднього періоду.

Для автоматизації щоденних звітів можна використовувати динамічні дати:

=QUERY(Data!A1:E100; "SELECT A, SUM(B)
WHERE A >= date '"&TEXT(TODAY()-7; "yyyy-mm-dd")&"'
GROUP BY A
ORDER BY A DESC")

Цей запит автоматично показує дані за останні 7 днів.

Створення зведеної таблиці за менеджерами і продуктами:

=QUERY(Sales!A1:F100; "SELECT
C,
SUM(E)
GROUP BY C
PIVOT D
LABEL
C 'Менеджер'
FORMAT
SUM(E) '#,##0.00 ₴'")

Для великих наборів даних корисно створювати проміжні підсумки:

=QUERY(Data!A1:E100; "SELECT
B,
D,
SUM(E),
SUM(E)/SUM(SUM(E)) OVER()
GROUP BY B, D
LABEL
B'Регіон',
D'Продукт',
SUM(E) 'Продажі',
SUM(E)/SUM(SUM(E)) OVER() 'Частка від загальних продажів'
FORMAT
SUM(E) '#,##0.00',
SUM(E)/SUM(SUM(E)) OVER() '0.00%'")

Такий запит створює детальний звіт із розбивкою за регіонами та продуктами, включно з часткою кожної комбінації в загальних продажах.

Оптимізація та продуктивність

Під час роботи з великими наборами даних продуктивність QUERY стає критично важливим фактором. Таблиці, що містять понад 10000 рядків, можуть оброблятися значно повільніше, якщо запит складено неоптимально. Розглянемо основні прийоми оптимізації.

Перше правило - завжди обмежуйте діапазон даних тільки необхідними стовпцями. Замість вибірки всього діапазону A1:Z1000, вибирайте тільки потрібні стовпці. Наприклад, якщо вам потрібні тільки дані про продажі та дати, використовуйте:

=QUERY(A1:B1000; "SELECT A, B WHERE A is not null")

Для прискорення роботи з датами використовуйте попередню фільтрацію. Замість:

=QUERY(A1:E1000; "SELECT * WHERE A >= date '2024-01-01'")

Краще спочатку відфільтрувати дані за датою, а потім застосовувати додаткові умови:

=QUERY(FILTER(A1:E1000; A1:A1000 >= DATE(2024;1;1)); "SELECT Col1, Col2 WHERE Col2 > 0")

Під час роботи з великими обсягами даних важливо уникати надлишкових обчислень. Якщо вам потрібно провести кілька операцій з одними й тими самими даними, створіть проміжну таблицю за допомогою першого QUERY, а потім працюйте з нею:

=QUERY( QUERY(A1:E1000; "SELECT A, B, SUM(C) GROUP BY A, B"); "SELECT Col1, SUM(Col3) GROUP BY Col1" )

Висновок і поради

QUERY - це потужний інструмент, який може значно спростити роботу з даними в Google Таблицях. Ґрунтуючись на практичному досвіді, можна виділити кілька типових сценаріїв використання, де QUERY особливо ефективний.

Для щоденних звітів рекомендується створювати шаблони запитів із динамічними параметрами:

=QUERY(Sales!A1:E1000;
"SELECT A, B, SUM(C)
WHERE A >= date '"&TEXT(TODAY()-30; "yyyy-mm-dd")&"'
GROUP BY A, B
PIVOT B")=QUERY(Sales!A1:E1000; "SELECT A, B, SUM(C) WHERE A >= date '"&TEXT(TODAY()-30; "yyyy-mm-dd")&"' GROUP BY A, B PIVOT B")

QUERY відмінно комбінується з іншими функціями Google Таблиць. Наприклад, для створення динамічних графіків можна використовувати комбінацію QUERY і SPARKLINE:

=SPARKLINE(
QUERY(A1:B100; "SELECT B WHERE A >= date '"&TEXT(TODAY()-7; "yyyy-mm-dd")&"'")
)=SPARKLINE( QUERY(A1:B100; "SELECT B WHERE A >= date '"&TEXT(TODAY()-7; "yyyy-mm-dd")&"'") )

Під час побудови складних звітів корисно розбивати запити на кілька етапів:

Спочатку відфільтрувати дані:

=QUERY(Data!A1:E1000; "SELECT * WHERE A is not null")

Потім групувати результати:

=QUERY(FilteredData; "SELECT A, SUM(B) GROUP BY A")

І нарешті, форматувати виведення:

=QUERY(GroupedData; "SELECT * FORMAT Col2 '#,##0.00'")

Подальше вивчення QUERY рекомендується почати з освоєння складніших операцій:

Робота із вкладеними запитами
Використання віконних функцій
Створення складних умовних угруповань
Застосування регулярних виразів у фільтрах

Пам'ятайте, що ефективність QUERY залежить не тільки від правильності синтаксису, а й від структури ваших даних. Добре організовані дані з чіткою структурою дадуть змогу створювати простіші та продуктивніші запити.