Запретить SPLIT преобразовывать текст в числа в Google Sheets при использовании в ARRAYFORMULA?
У меня есть такие строки данных:
a|b|1|c|d
a|b|1, 2|c|d
e|f||g|h
Я использую BYROW, чтобы разделить их:
=BYROW(
A2:A,
LAMBDA(
row,
SPLIT(
row,
"|",
,
FALSE
)
)
)
Это работает, но затем ячейки, которые ВЫГЛЯДЯТ как числа, преобразуются в числа. Это нарушает формулу QUERY, которую я пытаюсь обернуть.
=QUERY(
BYROW(
A2:A,
LAMBDA(
row,
SPLIT(
row,
"|",
,
FALSE
)
)
),
"SELECT * WHERE Col3 IS NOT NULL"
)
query не любит смешанные типы данных в одном столбце (Запрос игнорирует строковое (нечисловое) значение), поэтому приведенная выше функция возвращает все строки, но не возвращает значение в ячейках, которые не имеют числа. Итак, вот результат, который я получаю:
Но ожидаемый результат:
Я пришел к выводу, что это связано с тем, что SPLIT преобразуется в число. Я не могу использовать TO_TEXT, потому что в некоторых столбцах есть даты, которые я хочу преобразовать.
Ответы 2
Возможно, в качестве альтернативы вы можете просто использовать filter(), чтобы не попасть в помеху, связанную со смешанными запросами типов данных:
=let(Σ,byrow(A2:A,lambda(row,split(row,"|",,0))),
filter(Σ,choosecols(Σ,3)<>""))
вместо ошибочного запроса попробуйте неинвазивный:
=LET(x, BYROW(A2:A, LAMBDA(r, SPLIT(r, "|",, ))), FILTER(x, INDEX(x,,3)<>""))
но если вы действительно хотите запрос:
=INDEX(LET(x, QUERY(BYROW(SUBSTITUTE(A2:A, ",", "×"),
LAMBDA(r, SPLIT(r, "|",, )))&"", "where Col3 is not null", ),
IF((IFERROR(x*1) = "")+(x = ""), SUBSTITUTE(x, "×", ","), IF((x*1)>40000, TEXT(x, "dd/mm/yyyy"), x*1))))