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