используя как QUERY, так и FILTER вместе в одном выражении?

Я надеюсь, что кто-то может мне помочь; Я создаю несколько электронных таблиц, чтобы помочь с отслеживанием времени. У меня есть список задач со столбцами для критериев, включая дату, затраченное время, категорию работы и клиента.

Я хочу отфильтровать эти данные по месяцам, поэтому, например, я хотел бы знать, сколько времени я потратил на переписку за один месяц. Это означает, что мне нужно выбрать все строки, где категория = «переписка» и где все даты относятся к одному указанному месяцу. На данный момент мне приходится использовать запрос, который выводит в промежуточную таблицу, а затем запускать функцию фильтра в этой таблице, чтобы вывести в мою конечную таблицу. Вот мои две функции:

=QUERY( 'Task List'!A4:F , "select A, B, E, F where C = 'Correspondence'" )

это дает мне первую таблицу только со строками, в которых категория «Переписка». Затем на этой таблице я должен запустить следующую функцию:

=filter(J4:M,J4:J>=date(2015,4,1),J4:J<=date(2015,4,31))

Чтобы получить только строки из этого месяца апреля. Если возможно, я хотел бы удалить промежуточную таблицу (которая не служит никакой другой цели и просто загромождает мой лист).

Можно ли объединить эти утверждения и сделать процесс за один шаг?

Спасибо.


google-sheets google-query-language
person Inigo    schedule 11.04.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это действительно возможно.

Поскольку вы не указали, в каком столбце должны быть найдены даты (в «сырых» данных), я предположил для этого примера, что даты находятся в столбце F. Самый простой способ — использовать функцию МЕСЯЦ(). Однако при использовании в query() эта функция считает январь месяцем 0. Вот почему я добавил +1. Посмотрите, работает ли это?

=QUERY( 'Task List'!A4:F , "select A, B, E, F where C = 'Correspondence' and month(F)+1 =4 ")
person JPV    schedule 11.04.2015
comment
Спасибо, это потрясающе! Именно то, что я искал! Еще одна маленькая вещь, если вы не возражаете: вместо того, чтобы жестко кодировать месяц в запросе, как мне сослаться на значение из конкретной ячейки? (Я предполагаю, что мне нужно как-то избежать номера мобильного телефона) - person Inigo; 11.04.2015
comment
Чтобы ответить на этот вопрос, кажется, что =QUERY( 'Task List'!A5:F , "select A, B, E, F where C='Correspondence' and month(A)+1 = "&G1 ) выполняет свою работу. - person Inigo; 11.04.2015

arrow_upward
0
arrow_downward

Я пришел к этому вопросу, когда мне нужно было фильтровать по weeknum() и year(), а также запрашивать по contains(). Может быть полезно объединить функции запроса и фильтрации для аналогичных, но более динамичных потребностей в сопоставлении даты и текста. Если, например, OP нужно было показать эти данные по неделям, это недоступно в Язык запросов Google.

Функция фильтра не имеет функции содержимого, поэтому вы ограничены текстом с точным совпадением или использованием Reg-Ex. В Query Lanuague нет функций Weeknum.

Комбинация фильтра и запроса может быть полезна в сценарии, аналогичном этому вопросу, но с динамической временной шкалой (без жестко установленного месяца или даты, такой как скользящая временная шкала) и где текст, который вы соответствуете, не является точным (когда вам нужно использовать функцию содержит из запроса язык). Вот пример объединения фильтра и запроса в таблицах Google.

=(sum(Filter(QUERY(FB!$A:$Z, "select Q where B contains 'Apple'"), Weeknum (QUERY(FB!$A:$Z, "select E where B contains 'Apple'")) = Weeknum($A8))))

В этом примере я запросил экспорт данных рекламы Facebook для любых сообщений, содержащих слово «Apple» в заголовке и где Weeknum() соответствует текущим неделям на моем листе, чтобы собрать еженедельные данные из нескольких источников в одну таблицу для построения отчеты, требующие минимального обновления по мере истечения временной шкалы.

Он выбирает Q (расходы), где B (название) содержит Apple, а Weeknum (E) соответствует номеру недели в текущей строке листа (A8). Я нашел это полезным много раз. Запрос + пример таблицы фильтров здесь.

Если бы OP хотел динамически извлекать эту информацию по мере того, как шли месяцы, если бы столбец A содержал месяцы, чтобы формула могла быть вытянута вперед, и автоматически извлекала бы данные из данных запроса, отфильтрованных путем сопоставления месяца месяц.

=(sum(Filter(QUERY( 'Task List'!A:Z , "select A, B, E, F, J where C contains 'Correspondence'" ), Month(QUERY( 'Task List'!A4:F , "select J where C contains 'Correspondence'" )) = Month('$A2'))))
person Greggory Wiley    schedule 18.02.2020