#google-sheets #google-sheets-formula
#google-sheets #google-sheets-formula
Вопрос:
(Из-за политики моей организации я не могу предоставить общий доступ к образцу листа.)
У меня есть два листа с такими данными:
Лист1
Лист2
Теперь, на листе 1, в столбце C, я ищу, ARRAYFORMULA который будет искать значение в Sheet1!A:A путем сопоставления подстроки в Sheet2!A:A после удаления пробелов из Sheet2!A:A , а затем возвращает значение из строки в Sheet2!B:B .
Я придумал не- ARRAYFORMULA формулу для получения данных по строкам (в Sheet1!C:C ниже). Но мои реальные наборы данных содержат тысячи строк, и я не хочу копировать / вставлять формулу в каждую из них.
Каждая строка содержит формулу, которая выглядит следующим образом:
=TEXTJOIN(
", "
, TRUE
, IFNA(
FILTER(
Sheet2!B:B
, Sheet2!B:B <> ""
, Sheet2!A:A <> ""
, NOT(
ISERROR(
SEARCH(
REGEXREPLACE(Sheet2!A:A, " ", "")
, A2
)
)
)
)
, "not found"
)
)
Я попытался преобразовать это в ARRAYFORMULA in Sheet1!B2 , но это не дало ожидаемого результата. Формула такова:
=ArrayFormula(
TEXTJOIN(
", "
, TRUE
, IFNA(
FILTER(
Sheet2!B:B
, Sheet2!B:B <> ""
, Sheet2!A:A <> ""
, NOT(
ISERROR(
SEARCH(
REGEXREPLACE(Sheet2!A:A, " ", "")
, A2:A
)
)
)
)
, "not found"
)
)
)
Комментарии:
1. Не могли бы вы рассмотреть возможность использования Apps Script для этого?
2. Нет. Не удается по разным причинам.
3. @IMTheNachoMan Извините за неправильный ответ. Не могли бы вы обновить свое заявление о проблеме, чтобы упомянуть часть подстроки?
4. Только что сделал. Извините — не уверен, как я это пропустил.
Ответ №1:
Хорошо, я наконец-то заставил его работать с подстрокой и SEARCH .
=Array_Constrain(TRANSPOSE(ArrayFormula(REGEXREPLACE(REGEXREPLACE(SPLIT(TEXTJOIN(", ",1,{ArrayFormula(IFERROR(HLOOKUP("Value",Sheet2!B:B,ArrayFormula(Transpose(SEQUENCE(COUNTA(Sheet2!A2:A),1,2))*(SEARCH(TRANSPOSE(SUBSTITUTE(FILTER(Sheet2!A2:A,LEN(Sheet2!A2:A))," ",)),FILTER(A2:A,LEN(A2:A)))>0)),0))),ArrayFormula(IF(SEQUENCE(COUNTA(A2:A)),";",""))}),", ;",0,0),"^, ",),"^$","not found"))),COUNTA(A2:A),1)
«Читаемая» версия:
=Array_Constrain(
TRANSPOSE(
ArrayFormula(REGEXREPLACE(
REGEXREPLACE(
SPLIT(
TEXTJOIN(
", ",
1,
{
ArrayFormula(IFERROR(
HLOOKUP(
"Value",
Sheet2!B:B,
ArrayFormula(
Transpose(
SEQUENCE(COUNTA(Sheet2!A2:A),1,2)
)*
(SEARCH(
TRANSPOSE(
SUBSTITUTE(
FILTER(
Sheet2!A2:A,
LEN(Sheet2!A2:A)
),
" ",
)
),
FILTER(A2:A,LEN(A2:A))
)>0)
),
0
)
)),
ArrayFormula(IF(
SEQUENCE(COUNTA(A2:A)),
";",
""
))
}
),
", ;",
0,
0
),
"^, ",
),
"^$",
"not found"
))
),
COUNTA(A2:A),
1
)