Если вы когда-нибудь пытались отфильтровать столбец даты в функции Query в Google Таблицах, то знаете, насколько сложной может стать эта задача.
Если кратко, то проблема возникает потому, что даты в Google Таблицах на самом деле хранятся как порядковые номера, но функция Query требует дату как строковый литерал в формате yyyy-mm-dd, иначе она не может выполнить фильтр сравнения.
В данной статье мы рассмотрим этот вопрос более подробно и покажем, как правильно составлять формулы функции Query для фильтрации по датам.
Проблема
Если вы когда-нибудь пытались отфильтровать столбец даты в функции Query в Google Таблицах, то знаете, насколько сложной может стать эта задача.
Если кратко, то проблема возникает потому, что даты в Google Таблицах на самом деле хранятся как порядковые номера, но функция Query требует дату как строковый литерал в формате yyyy-mm-dd, иначе она не может выполнить фильтр сравнения.
В данной статье мы рассмотрим этот вопрос более подробно и покажем, как правильно составлять формулы функции Query для фильтрации по датам.
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
=QUERY(Data!$A$1:$H$136;»select C, B where B > ‘1/1/2000′»;1)
Но, к сожалению, результатом выполнения такого запроса является пустое место:
Если удалить одинарные кавычки вокруг даты и повторить попытку, то мы получим ошибку #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 в строку запроса, чтобы получилось:
Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.
«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!$A$1:$H$136;»select C, B where B > date ‘»&TEXT;(DATEVALUE(«1/1/2000″);»yyyy-mm-dd»)&»‘»;1)
Теперь при выполнении функции возвращаются правильные, отфильтрованные данные:
Ссылка на дату в ячейке
В этом случае формула будет проще, потому что нам не нужна функция DATEVALUE. Предположим, что есть дата в ячейке A1, которую мы хотим использовать в нашем фильтре, тогда формула становится такой:
=QUERY(Data!$A$1:$H$136;»select C, B where B > date ‘»&TEXT;(A1,»yyyy-mm-dd»)&»‘»;1)
Пример фильтрации между двумя датами
Здесь все просто, достаточно расширить нашу формулу, добавив вторую дату после ключевого слова AND:
=QUERY(Data!$A$1:$H$136;»select C, B where B > date ‘»&TEXT;(A1,»yyyy-mm-dd»)&»‘ and B <= date ‘»&TEXT;(B1,»yyyy-mm-dd»)&»‘»;1)
Использование сегодняшней даты в качестве фильтра
Подставьте функцию TODAY() или СЕГОДНЯ() в нашу формулу:
=QUERY(Data!$A$1:$H$136;»select C, B where B > date ‘»&TEXT;(TODAY(),»yyyy-mm-dd»)&»‘»;1)
Можно ли посмотреть пример таблицы?
Да, вы можете сделать это здесь.
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Автор материала: Ben Collins
Перевод: редакция ExcelExcel
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
