Якщо вам необхідно оперувати інформацією в Google Таблицях, функція QUERY стане вашим надійним помічником! Вона забезпечує потужний інструмент для пошуку, що нагадує роботу з базами даних, прямо у вашій електронній таблиці. Завдяки їй ви можете легко знаходити та фільтрувати дані у будь-якому потрібному вам форматі. Давайте розглянемо, як ефективно використовувати цю функцію.
Застосування функції QUERY
Функція QUERY не є складною для розуміння, особливо якщо у вас є досвід роботи з базами даних за допомогою SQL. Структура типової функції QUERY подібна до запитів SQL, що дозволяє здійснювати пошук в даних Google Таблиць, наче ви працюєте з базою даних.
Формула для використання функції QUERY виглядає так: =QUERY(дані, запит, заголовки). Вам потрібно замінити “дані” діапазоном клітинок (наприклад, “A2:D12” або “A:D”), а “запит” – вашим пошуковим запитом.
Необов’язковий аргумент “заголовки” вказує кількість рядків, які слід вважати заголовками у верхній частині діапазону даних. Наприклад, якщо у вас є заголовок, що займає два рядки, як-от “Ім’я” в A1 і “Прізвище” в A2, то вкажіть це, щоб QUERY правильно інтерпретувала перші два рядки як єдиний заголовок.
Розглянемо приклад: припустимо, у вас є аркуш Google Таблиць під назвою “Список співробітників”, де зберігається інформація про працівників, включаючи їхні імена, ідентифікаційні номери, дати народження та відвідування обов’язкового навчання.
На іншому аркуші ви можете застосувати формулу QUERY для отримання списку усіх співробітників, які не відвідували обов’язкове навчання. Цей список може включати ідентифікаційні номери, імена, прізвища та інформацію про відвідування тренінгу.
Для отримання такого списку на основі наведених вище даних ви можете використати формулу: =QUERY('Список співробітників'!A2:E12, "SELECT A, B, C, E WHERE E = 'Ні'"). Це запитує дані з діапазону A2 до E12 на аркуші “Список співробітників”.
Як і в типовому запиті SQL, функція QUERY дозволяє вибирати стовпці для відображення (SELECT) та визначати умови пошуку (WHERE). У нашому випадку, вона поверне стовпці A, B, C та E, а також покаже усі рядки, де значення у стовпці E (“Відвідане навчання”) дорівнює “Ні”.
Як показано вище, чотири співробітники зі списку не відвідали навчання. QUERY надала цю інформацію, а також відповідні стовпці, щоб показати їхні імена та ідентифікаційні номери в окремому списку.
В даному прикладі використано конкретний діапазон даних. Ви можете змінити його, щоб запитувати всі дані у стовпцях від A до E. Це дасть вам можливість додавати нових співробітників до списку, а формула QUERY буде автоматично оновлюватися при додаванні нових даних або зміні статусу відвідування навчання.
Відповідною формулою для цього буде: =QUERY('Список співробітників'!A2:E, "SELECT A, B, C, E WHERE E = 'Ні'"). Ця формула ігнорує початкову назву “Співробітники” в клітинці A1.
Якщо ви додасте до початкового списку 11-го співробітника, який не відвідував навчання (наприклад, Крістін Сміт), формула QUERY автоматично оновиться та відобразить нового співробітника.
Розширені можливості QUERY
Функція QUERY є досить універсальною. Вона дозволяє використовувати логічні оператори (наприклад, І та АБО) або інші функції Google (наприклад, COUNT) в рамках пошукового запиту. Ви також можете використовувати оператори порівняння (більше, менше тощо), щоб знаходити значення в певному діапазоні.
Використання операторів порівняння з QUERY
Ви можете застосовувати QUERY з операторами порівняння (наприклад, менше, більше або дорівнює), щоб фільтрувати дані. Для цього додамо до нашого аркуша “Список співробітників” додатковий стовпець (F) із кількістю нагород, які отримав кожен співробітник.
За допомогою QUERY ми можемо знайти всіх співробітників, які отримали хоча б одну нагороду. Формула для цього буде: =QUERY('Список співробітників'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0").
Тут оператор “більше ніж” (>) використовується для пошуку значень вище нуля у стовпці F.
Як показано на зображенні, функція QUERY повернула список із восьми співробітників, які отримали одну або більше нагород. З 11 співробітників троє не отримали жодної нагороди.
Використання І та АБО з QUERY
Логічні оператори І та АБО чудово працюють у рамках формули QUERY, дозволяючи додавати кілька умов для пошуку даних.
Щоб продемонструвати роботу оператора І, давайте спробуємо знайти дані між двома датами. Використовуючи наш приклад зі списком співробітників, ми можемо вивести список усіх, хто народився з 1980 по 1989 рік.
Це також передбачає використання операторів порівняння, таких як “більше або дорівнює” (>=) та “менше або дорівнює” (<=).
Формула для цього буде: =QUERY('Список співробітників'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' AND D <= DATE '1989-12-31'").
Як бачимо, цим умовам відповідають три працівники: 1980, 1986 та 1983 років народження.
Ви також можете використовувати оператор АБО для отримання подібних результатів. Якщо ми візьмемо ті самі дані, але змінимо умови та використаємо АБО, то зможемо виключити всіх працівників, які народилися у 1980-х роках.
Формула для цього буде: =QUERY('Список співробітників'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' OR D < DATE '1980-1-1'").
З перших 10 співробітників троє народилися у 1980-х роках. Наведений приклад показує решту сімох, які народилися до або після виключеного періоду.
Використання COUNT з QUERY
Замість простого пошуку та повернення даних, ви можете комбінувати QUERY з іншими функціями, такими як COUNT, для маніпуляції даними. Наприклад, припустимо, нам потрібно підрахувати кількість співробітників у нашому списку, які відвідували і не відвідували обов’язкове навчання.
Для цього ви можете поєднати QUERY з COUNT таким чином: =QUERY('Список співробітників'!A2:E12, "SELECT E, COUNT(E) group by E").
Фокусуючись на стовпці E (“Відвідане навчання”), функція QUERY використовувала COUNT, щоб порахувати, скільки разів зустрічається кожне значення (“Так” або “Ні”). З нашого списку, шестеро співробітників пройшли навчання, а четверо – ні.
Ви можете легко змінити цю формулу та використовувати її з іншими функціями Google, такими як SUM.