Якщо ви коли-небудь намагалися відфільтрувати стовпець дати у функції 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)
Але, на жаль, результатом виконання такого запиту є пусте місце:
Зверніть увагу, що у формулах знаками роздільника в російській версії Google-таблиць будуть точки з комою;
Якщо видалити одинарні лапки навколо дати і повторити спробу, ми отримаємо помилку #VALUE! або #ЗНАЧ!, тому що функція запиту не може порівняти:
Але що робити?
Жоден з цих стандартних форматів не спрацював, тому що дати не мають правильного формату для використання функції 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)
Тепер під час виконання функції повертаються правильні, відфільтровані дані:
Посилання на дату в осередку
В цьому випадку формула буде простішою, тому що нам не потрібна функція 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
