Фільтрування за датами у функції QUERY

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

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

У цій статті ми розглянемо це питання докладніше і покажемо, як правильно складати формули функції Query для фільтрації за датами.

Проблема

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

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

У цій статті ми розглянемо це питання докладніше і покажемо, як правильно складати формули функції Query для фільтрації за датами.

=QUERY(Data!$A$1:$H$136;»select C, B where B > '1/1/2000′»;1)

Але, на жаль, результатом виконання такого запиту є пусте місце:

Фільтрування за датами у функції QUERY

Зверніть увагу, що у формулах знаками роздільника в російській версії Google-таблиць будуть точки з комою;

Якщо видалити одинарні лапки навколо дати і повторити спробу, ми отримаємо помилку #VALUE! або #ЗНАЧ!, тому що функція запиту не може порівняти:

Фільтрування за датами у функції QUERY

Але що робити?

Жоден з цих стандартних форматів не спрацював, тому що дати не мають правильного формату для використання функції Query.

Правильний синтаксис для дат у функції Query

Відповідно до документації з мови функції Query, необхідно додати ключове слово date і переконатися, що дата має формат yyyy-mm-dd для того, щоб використовувати її як фільтр у пункті WHERE нашої функції Query.

Залишимо на якийсь час осторонь функцію Query і розглянемо рядок «select…».

Новий синтаксис, який будемо використовувати, виглядає так:

date_column > date '2000-01-01'

Завдання полягає в тому, щоб зробити текстову формулу для створення синтаксису всередині функції запиту.

Спочатку розберемося з текстовою функцією, починаючи з необхідної дати 1/1/2000 і так далі:

Перетворимо її на формат порядкового номера за допомогою функції DATEVALUE():

=DATEVALUE(«1/1/2000»)

Результатом виконання функції є число:

36526

Потім функція TEXT() перетворює його на формат, необхідний для формули Query, тут потрібно вказати формат «yyyy-mm-dd»:

=TEXT(DATEVALUE(“1/1/2000″),”yyyy-mm-dd”)

Результатом виконання функції є дата у потрібному форматі:

2000-01-01

Потім додаємо одинарні лапки навколо нового формату дати, використовуючи синтаксис “'”. І нарешті, вставляємо слово date у рядок запиту, щоб вийшло:

“select C, B where B > date '”&TEXT;(DATEVALUE(“1/1/2000″),”yyyy-mm-dd”)&”'»

виконання дає бажаний результат:

select C, B where B > date '2000-01-01'

На цьому синтаксичне завдання вирішено!

Тепер можемо підставити цей рядок у другий аргумент нашої функції Query, і вона все зробить сама.

У цьому випадку я взяв таблицю даних про польоти космічних кораблів Space Shuttle з Вікіпедії, що містить стовпець дат запуску.

Я використовував функцію IMPORTHTML() для імпорту цієї таблиці у свої Таблиці Google, на вкладку з назвою “Data” в діапазоні A1:H136. Посилання на набір даних та таблицю наведено наприкінці статті.

Виконання запиту повертає всі польоти Space Shuttle після 1 січня 2000 року. Виведену в попередньому розділі формулу, вставляємо в другий аргумент функції:

= QUERY (Data! ;1)

Тепер під час виконання функції повертаються правильні, відфільтровані дані:

Фільтрування за датами у функції QUERY

Посилання на дату в осередку

В цьому випадку формула буде простішою, тому що нам не потрібна функція DATEVALUE. Припустимо, що є дата в осередку A1, яку ми хочемо використовувати у нашому фільтрі, тоді формула стає такою:

=QUERY(Data!$A$1:$H$136;”select C, B where B > date '»&TEXT;

Приклад фільтрації між двома датами

Тут все просто, достатньо розширити формулу, додавши другу дату після ключового слова AND:

=QUERY(Data!$A$1:$H$136;»select C, B where B > date '» & TEXT; B1,»yyyy-mm-dd»)&»'»;1)

Використання сьогоднішньої дати як фільтр

Підставте функцію TODAY() або СЬОГОДНІ() в нашу формулу:

=QUERY(Data!$A$1:$H$136;»select C, B where B > date '»&TEXT;

Чи можна побачити приклад таблиці?

Так, ви можете це зробити тут.

Автор матеріалу : Ben Collins

Переклад : редакція ExcelExcel

nBook
Додати коментар