Формат значения переменной ячейки в функции запроса

Я знаю, что формат можно использовать в функции запроса (например, Query (A1: G2, Select * Format C 'MM / DD / YYY')), но я застрял в том, как использовать его в моем более сложном запросе. По сути, я хочу, чтобы все выбранные столбцы были отформатированы в даты ММ / ДД / ГГГГ, но я не могу сделать это в самой ячейке, потому что этот запрос является частью функции запроса переменной, которая извлекает данные на основе ввода пользователя, и только ЭТИ извлечения столбцов имеют формат даты, все остальные извлечения представляют собой простые числа или текст. Я попытался вставить формат в себя, но он никогда не работает, или результат дает мне ложное считывание IfError, что совпадений не найдено. Кто-нибудь может помочь?

IFERROR(QUERY({Training!A3:AP},"select Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Col40,Col41,Col42 where "&TEXTJOIN(" and ", 1, IF(Lower(B3)<>"", "Lower(Col1) contains '"&Lower(B3)&"'", ),IF(Lower(B4)<>"", "Lower(Col2) contains '"&Lower(B4)&"'", ),If(Lower(B5)<>"", "Lower(Col6) contains '"&Lower(B5)&"'", ),IF(Lower(B7)<>"", "Lower(Col9) contains '"&Lower(B7)&"'", ),If(B6<>"",Vlookup(B6,Classes!G2:H,2,False)&" contains '"&B6&"'",),), 0), "No Matches Found")))

google-sheets formatting google-query-language array-formulas google-sheets-query
person J.Raab    schedule 06.08.2020    source источник
comment
поделитесь копией своего листа   -  person player0    schedule 06.08.2020
comment
Привет, игрок0, docsheets/docs.google.com/spread функция находится на вкладке Поиск в A10   -  person J.Raab    schedule 06.08.2020
comment
ответ обновлен ...   -  person player0    schedule 07.08.2020

Ответы (2)


arrow_upward
1
arrow_downward

format в QUERY глючит. использовать этот:

=ARRAYFORMULA(IF(B2="", "Please Select Criteria",
 IF(B2="Licenses", IFERROR(QUERY({Licensing!A3:D, 
 IF(Licensing!E3:E="",,TEXT(Licensing!E3:E,  "dd/mm/yyyy")), Licensing!F3:F, 
 IF(Licensing!G3:G="",,TEXT(Licensing!G3:G,  "dd/mm/yyyy")), Licensing!H3:H, 
 IF((Licensing!I3:AQ<>"")*(MOD(COLUMN(I3:AQ)+1, 4)=0), 
                       TEXT(Licensing!I3:AQ, "dd/mm/yyyy"), Licensing!I3:AQ)},
 "select "&JOIN(",", "Col"&ROW(3:7), "Col"&FILTER(ROW(9:43), NOT(MOD(ROW(9:43), 4)=0)))&" 
  where "&TEXTJOIN(" and ", 1, "1=1", 
 IF(LOWER(B3)="",,"lower(Col1)  contains '"&LOWER(B3)&"'"),
 IF(LOWER(B4)="",,"lower(Col2)  contains '"&LOWER(B4)&"'"),
 IF(LOWER(B5)="",,"lower(Col6)  contains '"&LOWER(B5)&"'"),
 IF(LOWER(B7)="",,"lower(Col10) contains '"&LOWER(B7)&"'"),
 IF(B6="",,"Col"&MATCH(B6, Licensing!2:2, 0)&" is not null")), 0), "No Matches Found"),

 IFERROR(QUERY({Training!A3:D, 
 IF(Training!E3:E="",, TEXT(Training!E3:E,  "dd/mm/yyyy")), Training!F3:F, 
 IF(Training!G3:AP="",,TEXT(Training!G3:AP, "dd/mm/yyyy"))},
 "select "&JOIN(",", "Col"&ROW(3:42))&" 
  where "&TEXTJOIN(" and ", 1, "1=1", 
 IF(LOWER(B3)="",,"lower(Col1) contains '"&LOWER(B3)&"'"),
 IF(LOWER(B4)="",,"lower(Col2) contains '"&LOWER(B4)&"'"),
 IF(LOWER(B5)="",,"lower(Col6) contains '"&LOWER(B5)&"'"),
 IF(B6="",,"Col"&MATCH(B6, Training!2:2, 0)&" is not null")), 0), "No Matches Found"))))

введите описание изображения здесь

person player0    schedule 06.08.2020
comment
Holy Hell Player, ты снова вытащил мою задницу из канавы. Мне понадобится чертова неделя, чтобы пройти через это, чтобы действительно понять, как именно у вас все настроено, но это здорово. Кстати, это была немного измененная функция из другого вопроса, который вы помогли мне с ноябрем прошлого года, так что вы уже дважды сотворили для меня волшебство. Как и раньше, я ценю это. - person J.Raab; 07.08.2020

arrow_upward
0
arrow_downward

Это может не помочь, но я считаю, что проблема в том, что предложение FORMAT в функции QUERY не отменяет формат ячейки в области вывода. Я провел некоторое тестирование и обнаружил, что с помощью следующей упрощенной версии вашей формулы я могу изменить формат вывода, например, с «мм / дд / гггг» на «гггг-мм-дд».

"select Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11 
where "&TEXTJOIN(" and ", 1,
IF(Lower(B3)<>"", "Lower(Col1) contains '"&Lower(B3)&"'", ),
IF(Lower(B4)<>"", "Lower(Col2) contains '"&Lower(B4)&"'", ),
If(Lower(B5)<>"", "Lower(Col6) contains '"&Lower(B5)&"'", ),
IF(Lower(B7)<>"", "Lower(Col9) contains '"&Lower(B7)&"'", ),
If(B6<>"",Vlookup(B6,Classes!G2:H,2,False)&" contains '"&B6&"'",),
) & " Format Col9 'dd-yyyy-mm', Col11 'dd-yyyy-mm' ", 0)

Но это сработало, только если ячейка / столбец был отформатирован как автоматический. И что еще хуже, я пока не могу достоверно воспроизвести поведение. На другой копии листа формат не изменится.

В любом случае, если вы хотите увидеть, как это работает, вот ссылка вашего листа. Может быть, у кого-то еще будет более четкое объяснение проблемы и способы ее решения.

person kirkg13    schedule 06.08.2020