Фильтрация по датам в функции QUERY

Google-таблицы

Если вы когда-нибудь пытались отфильтровать столбец даты в функции 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)

Но, к сожалению, результатом выполнения такого запроса является пустое место:

Фильтрация по датам в функции 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 в строку запроса, чтобы получилось:

Рекомендуем курс 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)

Теперь при выполнении функции возвращаются правильные, отфильтрованные данные:

Фильтрация по датам в функции QUERY

Ссылка на дату в ячейке

В этом случае формула будет проще, потому что нам не нужна функция 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

Оцените статью, пожалуйста
Добавить комментарий