Всемогутня функція Query - докладний посібник

1. синтаксис QUERY
2. План SQL-запиту в функції Query
3. Hello World для функції Query (Select)
4. Використовуємо фільтри і сортування (Where, Order by)
5. Агрегує функції, угруповання даних і перейменування стовпців (Group by, Label)
6. Опис кляузи Format
7. Створення перехресних таблиць (Pivot, скалярні функції)
8. скалярні функції
9. арифметичні оператори
10. Імпорт даних з допомогою QUERY з іншої Google Таблиці
11. Будуємо запит на основі об'єднання даних з декількох таблиць з однаковою структурою
12. Запит з динамікою

Наша взаимовыгодная связь https://banwar.org/

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

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

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

відправити заявку

Для роботи з QUERY вам знадобляться базові знання SQL. Для тих, хто не в курсі: лякатися не треба, функція QUERY насправді підтримує найпростіші можливості SQL.

синтаксис QUERY

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

де:

• дані - це діапазон комірок, який буде служити базою даних для SQL-запиту;
• запит - текст SQL-запиту;
• заголовки - необов'язковий аргумент, в якому ви можете вказати, скільки перших рядків масиву містять заголовки.

Для максимального сприйняття подальшої інформації пропоную відкрити і скопіювати собі наступну Google Таблицю . Для того, щоб створити копію, скористайтеся меню «Файл» і виберіть у ньому пункт «Створити копію».

У Докса, копію якого ви тільки що створили, існує кілька листів. Лист DB - це база даних, до якої ми звертатиметься за допомогою функції QUERY. Листи Level містять приклади, які ми будемо розглядати в цій статті. C кожним новим рівнем приклад буде ускладнюватися.

План SQL-запиту в функції Query

Будь-SQL-запит складається з окремих блоків, які часто називають кляузами. У SQL для функції Query закладений синтаксис мови запитів API візуалізації Google, який підтримує такі кляузи:

• select - перерахування полів, які будуть повернуті запитом;
• where - містить перелік умов, за допомогою яких буде відфільтрований масив даних, що обробляється запитом;
• group by - містить перелік полів, за якими ви хочете групувати результат;
• pivot - допомагає будувати перехресні таблиці, використовуючи значення одного стовпця як назви стовпців фінальної таблиці;
• order by - відповідає за сортування результатів;
• limit - за допомогою цієї частини запиту ви можете задати межа кількості рядків, що повертаються запитом;
• offset - за допомогою цієї кляузи ви можете задати число перших рядків, які не треба обробляти запитом;
• label - дана інтрига відповідає за назву полів, що повертаються запитом;
• format - відповідає за формат даних, що виводяться;
• options - дає можливість ставити додаткові параметри виведення даних.

Hello World для функції Query (Select)

Перейдемо на лист Level_1 і подивимося формулу в комірці A1.

= Query (DB! A1: L1143; "select * limit 100")

Частина формули «DB! A1: L1143» відповідає за базу даних, з якої ми будемо робити вибірку. Друга частина «select * limit 100» містить безпосередньо текст запиту. Символ «*» в даному випадку означає повернення всіх полів, що містяться в базі даних. За допомогою «limit 100» ми обмежуємо висновок даних в 100 рядків максимум. Це приклад найпростішого запиту. Ми вибрали 100 перших рядків з бази даних. Це свого роду «Hello world» для функції Query.

Використовуємо фільтри і сортування (Where, Order by)

Переходимо на лист Level_2. Виберемо тільки деякі потрібні нам поля і поставимо умови фільтрації і сортування. Наприклад, використовуємо дані тільки по кампаніям Campaign_1 і Campaign_2 за період 22-25 жовтня 2015 року. Відсортуємо їх в порядку убування за сумою сеансів. Для фільтра і сортування в текст запиту необхідно додати опис кляуз Where і Order. Для виведення в результуючу таблицю описаного вище прикладу нам знадобляться поля Campaign, Date і Sessions. Саме їх і потрібно перерахувати в кляузи Select.

У нашому випадку дані, розташовані на аркуші DB, і звернення до певних полях прописуються як назва стовпців аркуша. Таким чином, потрібні поля розташовується в наступних стовпцях:

• поле Date - стовпець A;
• поле Campaign - стовпець B;
• поле Sessions - стовпець G.

Відповідно, частина запиту, що відповідає за перелік виведених в результаті даних, буде виглядати так:

Select A, B, G

Далі в запиті йде інтрига Where. При написанні запиту кляузи обов'язково повинні розташовуватися в такому порядку, в якому були описані в першому розділі цієї статті. Після оголошення Where нам необхідно перерахувати умови фільтрації.

В даному випадку ми фільтруємо дані за назвою кампанії (Campaign) і датою (Date). Ми використовуємо кілька умов фільтрації. У тексті запиту між усіма умовами повинен стояти логічний оператор OR або AND. Фільтрація по датах трохи відрізняється від фільтрації по числовим і текстовим значенням, для її застосування необхідно використовувати оператор Date.

Частина запиту, що відповідає за фільтрацію даних, буде виглядати так:

WHERE (A & gt; = date'2015-10-22 'AND A & lt; = date'2015-10-25') AND (B = 'Campaign_1' OR B = 'Campaign_2')

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

= Query (DB! A1: L1143; "Select A, B, G WHERE (A & gt; = date'2015-10-22 'AND A & lt; = date'2015-10-25') AND (B = 'Campaign_1 'OR B =' Campaign_2 ') ")

Ви можете скопіювати її та вставити, наприклад, на новий лист документа, який використовується в якості прикладу в цьому пості, і отримаєте наступний результат:

Крім звичайних логічних операторів (=, <,>) блок WHERE підтримує додаткові оператори фільтрації:

• contains - перевіряє вміст певних символів в рядку. Наприклад, WHERE A contains 'John' поверне в фільтр все значення з шпальти A, в яких зустрічається John, наприклад, John Adams, Long John Silver;
• starts with - фільтрує значення по префіксу, тобто перевіряє символи на початку рядка. Наприклад, starts with 'en' поверне значення engineering і english;
• ends with - фільтрує значення по закінченню рядка. Наприклад, рядок 'cowboy' буде повернута конструкцією «ends with 'boy'» або «ends with 'y'»;
• matches - відповідає регулярному виразу. Наприклад: where matches '. * Ia' поверне значення India і Nigeria.
• like - спрощена версія регулярних виразів, перевіряє відповідності рядка заданому вираженню з використанням символів підстановки. На даний момент like підтримує два символу підстановки: «%» означає будь-яку кількість будь-яких символів в рядку, і «_» - означає один будь-який символ. Наприклад, «where name like 'fre%'» буде відповідати рядкам 'fre', 'fred', і 'freddy'.

Запит вже отфильтровал дані за певний період і залишив тільки потрібні нам кампанії. Залишається тільки впорядкувати результат по спадаючій залежно від кількості сеансів. Сортування в даних запитах здійснюється традиційно для SQL за допомогою кляузи Order by. За синтаксису вона досить проста: необхідно лише перерахувати поля, за якими потрібно впорядкувати результат, а також вказати порядок сортування. За замовчуванням - порядок asc, тобто по зростанню. Якщо вкажете після назва поле параметр desc, запит поверне результат в порядку убування зазначених в кляузи Order by полів.

У нашому випадку за фільтрацію відповідатиме рядок в тексті запиту:

Order by G desc

Відповідно, остаточний результат формули на аркуші Level_2, вирішальний потрібну нам задачу, виглядає так:

= Query (DB! A1: L1143; "SELECT A, B, G WHERE (A & gt; = date'2015-10-22 'AND A & lt; = date'2015-10-25') AND (B = 'Campaign_1 'OR B =' Campaign_2 ') ORDER BY G DESC ")

Тепер ви вмієте за допомогою найпростішого SQL синтаксису і функції QUERY фільтрувати і сортувати дані.

Агрегує функції, угруповання даних і перейменування стовпців (Group by, Label)

Переходимо на лист Level_3 і ускладнюємо завдання. У запитах ви можете не тільки робити вибірки, але також проводити різні обчислення і агрегації даних. Для цього в SQL функції Query існує ряд агрегуються функцій і інтрига Group by. Агрегує функції:

ФункціяОписПідтримуваний тип даних,що повертається тип даних

avg () Повертає середнє значення для групи Числовий Числовий count () Повертає кількість значень в групі Будь Числовий max () Повертає максимальне значення для групи Будь Аналогічний полю, про якого йдеться min () Повертає мінімальне значення для групи Будь Аналогічний полю, про якого йдеться в sum () Повертає суму значень в групі Числовий Числовий

Отже, давайте порахуємо дані по кожній кампанії:

• середньодобова кількість сеансів;
• максимальну кількість сеансів за добу;
• мінімальна кількість сеансів за добу;
• кількість днів, коли за кампанії був здійснений хоча б один сеанс;
• сума всіх сеансів по кожній кампанії за весь період.

Для вирішення цього завдання нам знадобляться дані тільки з двох полів: Campaign (знаходиться в стовпці B) і Sessions (знаходиться в стовпці G). Все агрегує функції прописуються разом зі списком полів для виведення даних в кляузи Select. У разі застосування агрегуються функцій все поля, до яких не застосовується цей тип функцій, є групуються полями. Їх необхідно перерахувати в кляузи Group by. Агрегує функції працюють обов'язково в парі з Group by. Опис кляузи Select буде наступним:

SELECT B, avg (G), max (G), min (G), count (G), sum (G)

Далі необхідно згрупувати дані: в нашому випадку потрібно угруповання тільки по одному полю Campaign, але ви можете здійснювати угруповання по будь-якій кількості стовпців.

Опис кляузи Group by дуже просте:

GROUP BY B

У кляузи досить вказати тільки стовпець B, що містить інформацію про назву кампанії. Тому що він єдиний, до якого ми не застосували ніякої агрегує функції. Наша формула:

= Query (DB! A1: L1143; "SELECT B, avg (G), max (G), min (G), count (G), sum (G) GROUP BY B")

Отримаємо наступний результат: В принципі, ми отримали бажаний результат, але назви стовпців можна підкоригувати за допомогою кляузи Label. Результат буде краще відображатися, якщо ми відсортуємо звіт за назвою кампанії. Опис кляузи Order by ми вже розглянули вище.

Для потрібної сортування досить додати наступний рядок після опису:

Order by B

Щоб перейменувати стовпці в таблиці, яку повертає запит, необхідно додати опис кляузи Label. Синтаксис досить простий: спочатку вказуєте виведений стовпець або функцію, яка буде повертати значення в результуючу таблицю, і далі в одинарних лапках вказуєте потрібну назву.

Це буде виглядати так:

B 'Кампанія', avg (G) 'Середнє', max (G) 'Максимальна', min (G) 'Мінімальна', count (G) 'Кількість', sum (G) 'Загальна сума'

Перетворена формула:

= Query (DB! A1: L1143; "SELECT B, avg (G), max (G), min (G), count (G), sum (G) GROUP BY B ORDER BY B LABEL B 'Кампанія', avg (G) 'Середнє', max (G) 'Максимальна', min (G) 'Мінімальна', count (G) 'Кількість', sum (G) 'Загальна сума' ")

А результат, що повертається формулою, виглядає так:

Всі поля названі відповідним опису кляузи Label чином. Останнє, що ріже око в яку повертатимуть таблиці, - формат, в якому виводяться дані в стовпці «Середнє». Для коригування форматів, виведених запитом даних, потрібно описати кляузу Format. Її опис схоже з описом Label, але замість назви поля слід прописати маску виведення даних (також в одинарних лапках).

Округлимо числа в стовпці «Середнє» до двох знаків після коми. Для округлення даних, що виводяться до двох знаків після коми маска повинна виглядати як '0.00'.

Опис кляузи Format

FORMAT avg (G) '0.00'

Відповідно, остаточна формула на аркуші Level_3 виглядає так:

= Query (DB! A1: L1143; "SELECT B, avg (G), max (G), min (G), count (G), sum (G) GROUP BY B ORDER BY B LABEL B 'Кампанія', avg (G) 'Середнє', max (G) 'Максимальна', min (G) 'Мінімальна', count (G) 'Кількість', sum (G) 'Загальна сума' FORMAT avg (G) '0.00' ")

В результаті:

Створення перехресних таблиць (Pivot, скалярні функції)

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

Щоб обчислити день тижня, доведеться скористатися однією з безлічі скалярних функцій. В нашій базі є вся необхідна інформація для розрахунку показника відмов. Далі досить просто застосувати арифметичний оператор «Розподіл».

скалярні функції

На момент написання статті SQL в Google Таблицях підтримує 14 скалярних функцій.

ФункціяОпис

year () Повертає номер року з «дати» або «дати і часу». Приклад: year (date '2009-02-05') поверне 2009. Запрошувані параметри: один параметр з типом дата або дата і час. Тип повертаються даних: число. month () Повертає номер місяця з «дати» або «дати і часу». Але в даному випадку січня буде повертати 0, феврваль 1 і так далі. Початком відліку для номера місяця є 0. Приклад: month (date '2009-02-05') поверне 1. Щоб функція повернула номер місяця в звичному вигляді до її результату додайте 1, month (date "2009-02-05") + 1 поверне 2. Запрошувані параметри: один параметр з типом дата або дата і час. Тип повертаються даних: число. day () Повертає номер дня у місяці з «дати» або «дати і часу». Приклад: day (date '2009-02-05') поверне 5. Запрошувані параметри: один параметр з типом дата або дата і час. Тип повертаються даних: число. hour () Повертає номер години в день з «дати і часу» або «часу». Приклад: hour (timeofday '12: 03: 17 ') поверне 12. Запрошувані параметри: один параметр з типом час або дата і час. Тип повертаються даних: число. minute () Повертає номер хвилини в годині з «дати і часу» або «часу». Приклад: minute (timeofday '12: 03: 17 ') поверне 3. Запрошувані параметри: один параметр з типом час або дата і час. Тип повертаються даних: число. second () Повертає номер секунди в хвилині з «дати і часу» або «часу». Приклад: second (timeofday '12: 03: 17 ') поверне 17. Запрошувані параметри: один параметр з типом час або дата і час. Тип повертаються даних: число. millisecond () Повертає номер мілісекунди в секунді з «дати і часу» або «часу». Приклад: millisecond (timeofday '12: 03: 17.123 ') поверне 123. Запрошувані параметри: один параметр з типом час або дата і час. Тип повертаються даних: число. quarter () Повертає номер кварталу в році з «дати і часу» або «часу». Базовим значенням або початком відліку є 1, соответствено, для першого кварталу функція поверне значення 1, для другого 2 і так далі. Приклад: quarter (date '2009-02-05') поверне 1. Запрошувані параметри: один параметр з типом дата або дата і час. Тип повертаються даних: число. dayOfWeek () Повертає номер дня тижня в тижні з «дати» або «дати і часу». Початком тижні вважається неділю, для неділі функція поверне значення 1, для понеділка 2 і так далі. Приклад: dayOfWeek (date '2015-11-10') поверне 3, тому що 10 листопада 2015 року - вівторок. Запитувані параметри: один параметр з типом дата або дата і час. Тип повертаються даних: число. now () Повертає поточну дату і час у часовому поясі GTM. Запитувані параметри: не вимагає введення параметрів. Тип повертаються даних: дата і час. dateDiff () Повертає різницю в днях між двома датами. Приклад: dateDiff (date '2008-03-13', date '2008-02-12') поверне 29, тому що 10 листопада 2015 року вівторок. Запитувані параметри: два параметра з типом «дата» або «дата і час». Тип повертаються даних: число. toDate Повертає перетворене в дату значення з «дати» або «дати і часу» або «числа». приклад:

• toDate (date '2008-03-13') поверне аналогічне значення в форматі дати, '2008-03-13'.
• toDate (dateTime'2013-03-13 11:19:22 ') поверне дату' 2013-03-13 '.
• toDate (1234567890000) поверне дату '2009-02-13'.

один параметр з типом дата, дата і час або число. Тип повертаються даних: дата. upper () Перетворює всі значення в рядку у верхній регістр. Приклад: upper ( 'foo') поверне рядок 'FOO'. Запитувані параметри: один параметр з текстовим типом даних. Тип повертаються даних: текст. lower () Перетворює всі значення в рядку в нижній регістр. Приклад: upper ( 'Bar') поверне рядок 'bar'. Запитувані параметри: один параметр з текстовим типом даних. Тип повертаються даних: текст.

арифметичні оператори

+ Додавання декількох числових значень - Різниця між числовими значеннями / Розподіл числових значень * Множення числових значень

Давайте визначимо поля, які будемо використовувати в запиті:

1. Для обчислення дня тижня нам буде потрібно дані поля Date в стовпці A.
2. Дані про типи пристроїв зберігаються в поле Device category в стовпці E.
3. Для розрахунку показника відмов будуть потрібні дані полів Bounces і Sessions - в стовпчиках H і G.

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

SELECT dayOfWeek (A), sum (H) / sum (G)

Саме так буде виглядати опис потрібних нам полів. Тепер за допомогою кляузи Group by згрупуємо рядки по днях тижня. Для цього допишемо в запит наступний рядок:

GROUP BY dayOfWeek (A)

Вже видно, як змінюється показник відмов залежно від дня тижня:

Щоб побудувати перехресну таблицю, досить прописати кляузу Pivot із зазначенням стовпчика, значення з якого будуть додані у вигляді стовпців. У нашому випадку це стовпець E, тому що саме він містить інформацію про тип пристроїв. Дописуємо рядок:

PIVOT E

Тепер запит повертає результат:

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

Остаточна формула на аркуші Level_4:

= Query (DB! A1: L1143; "SELECT dayOfWeek (A), sum (H) / sum (G) GROUP BY dayOfWeek (A) PIVOT E LABEL dayOfWeek (A) 'День тижня', sum (H) / sum ( G) '' FORMAT sum (H) / sum (G) '0.00%' ")

Отримуємо звіт:

Рядком LABEL sum (H) / sum (G) '' ми прибрали з підписів стовпчика напис, що містить формулу розрахунку. Рядком FORMAT sum (H) / sum (G) '0.00%' ми передали процентний формат показником відмов у звіті. В цілому, описаного в прикладах вище синтаксису цілком достатньо, щоб почати активно використовувати функцію QUERY, але в завершенні статті хочу показати ще кілька цікавих прийомів, які можна взяти на озброєння.

Імпорт даних з допомогою QUERY з іншої Google Таблиці

За допомогою QUERY ви можете використовувати в якості бази даних іншу Google Таблицю. Це можна зробити за допомогою поєднання функцій ImportRange і QUERY. Я створив нову Google Таблицю , В яку продублював дані з листа DB з наведеного на початку статті документа . Щоб в якості бази даних використовувати дані з іншої Google таблиці, як перший аргумент функції Query виступить імпортований функцією ImportRange діапазон.

Різниця в тому, що при написанні запиту до даних, що імпортуються функцією ImportRange, замість назви стовпців ми вказуємо їх порядковий номер у повернутому функцією ImportRange діапазоні. На аркуші DataImport перепишемо запит, поданий в Level_4 таким чином, щоб він звертався до даних, що знаходяться в нової таблиці на аркуші DB_Transfer. Синтаксис функції ImportRange досить простий:

IMPORTRANGE (ключ, діапазон)

Де ключ - частина URL Google Таблиці:

А діапазон - це посилання на лист і (вибачте за каламбур) діапазон. У нашому випадку діапазоном буде DB_Transfer! A1: L1143. Формула ImportRange:

importrange ( "1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo"; "DB_Transfer! A1: L1143")

Саме її ми повинні вказати в якості даних функції Query. Далі залишається переписати запит так, щоб посилатися на стовпці бази даних не за назвою, а за порядковим номером стовпця. Визначимо, до яких стовпчиках ми зверталися за допомогою запиту на аркуші Level_4.

Date Дата A 1 Device type Тип пристрою E 5 Sessions Кількість сеансів G 7 Bounces Кількість відмов H 8

Текст запиту після заміни назв стовпців на їх порядковий номер:

SELECT dayOfWeek (Col1), sum (Col8) / sum (Col7) GROUP BY dayOfWeek (Col1) PIVOT Col5 LABEL dayOfWeek (Col1) 'День тижня', sum (Col8) / sum (Col7) '' FORMAT sum (Col8) / sum (Col7) '0.00%'

Як бачите, текст запиту практично не змінився, але замість стовпчика A ми тепер вказуємо Col1, замість стовпчика E - Col5, замість G - Col7 і замість H, відповідно, Col8. Отримуємо формулу:

= Query (IMPORTRANGE ( "1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo"; "DB_Transfer! A1: L1143"); "SELECT dayOfWeek (Col1), sum (Col8) / sum (Col7) GROUP BY dayOfWeek (Col1) PIVOT Col5 LABEL dayOfWeek (Col1) ' день тижня ', sum (Col8) / sum (Col7)' 'FORMAT sum (Col8) / sum (Col7)' 0.00% ' ")

Як перший аргумент функції Query виступає функція ImportRange з посиланням на ключ потрібної Google таблиці, яку ви можете скопіювати з URL Google Таблиці, і посилання на діапазон, що включає назву листа, а також першої і останньої позиції потрібного діапазону.

Остаточну формулу в роботі ви можете подивитися на аркуші DataImport.

Будуємо запит на основі об'єднання даних з декількох таблиць з однаковою структурою

Ще одна досить потужна можливість функції QUERY - побудова запит на основі декількох масивів даних.

Єдина умова для об'єднання даних - однакова структура вхідних таблиць.

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

Масив - це віртуальна таблиця, яка містить рядки і стовпці.

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

• зворотна коса риска «\» - розділяє стовпці. Наприклад, {1 \ A}. Число 1 буде знаходиться в правій лівій клітинці масиву, буква «A» в осередку справа. Так ми описали діапазон, що містить два стовпці і один рядок.
• крапка з комою «;» використовується для переходу на наступний рядок. Візьмемо {1; A}. Цей масив буде складатися з одного стовпчика і двох рядків, в першому рядку буде міститися значення 1, у другому рядку буква «A».

Таким чином ви можете два і більше діапазону описати в одному масиві, наприклад:

= Query ({Table1! A1: B5; Table2! A1: B5; Table3! A1: B5}; "SELECT * WHERE Col2> 4")

В даному випадку ми звертаємося із запитом до трьох діапазонах даних, що знаходяться на різних аркушах, об'єднавши їх за допомогою «;» в масив так, що друга таблиця стає продовженням першої, а третя таблиця - продовженням другої.

Подивіться цей приклад за ПОСИЛАННЯ .

Запит з динамікою

Синтаксис запитів в функції QUERY складний для непідготовленого користувача. Тому ви можете додати на робочий лист різні інтерактивні елементи у вигляді списку, створеного за допомогою функції «Перевірка даних».

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

Ви можете змінити все поля, зафарбовані зеленим кольором, і таким чином вибрати цікавий діапазон дат, ввести зручні для вас назву полів, а також позначити, які з п'яти запропонованих полів потрібно вивести в звіт.

Вкажіть Вас інтервал дат в межах від 24.09.2015 по 25.10.2015, оскільки дані, згенеровані для тестової бази і зберігаються на аркуші DB, містять тільки цей діапазон.

Далі в конструкторі звітів ви можете змінити назву полів і воно буде відображатися у фінальній таблиці. Також можете вказати, які поля потрібно вивести в звіт. Ще раз нагадаю, що необхідно вказати як мінімум одну міру і один вимір.

Під час зміни будь-яких параметрів звіт під конструктором буде змінюватися динамічно.

Формула, яка змінює запит в залежності від розумних налаштувань, виглядає так:

= Query (DB! A1: L1143; "Select" & amp; join ( ","; filter (C7: C11; B7: B11 = "Так")) & amp; "WHERE (A & gt; = date '" & amp; C2 & amp ; "-" & amp; D2 & amp; "-" & amp; E2 & amp; " 'AND A & lt; = date'" & amp; C3 & amp; "-" & amp; D3 & amp; "-" & amp; E3 & amp; " ') GROUP BY" & amp ; join ( ","; filter (C7: C11; B7: B11 = "Так"; D7: D11 = "Вимірювання")) & amp; "LABEL" & amp; join ( ","; filter (E7: E11; B7 : B11 = "Так")))

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

Готовий відповідати на питання в коментарях :)