Как перечислить все комбинации значений из двух столбцов, разделенных точкой с запятой, в Excel/Sheets?
Возможно, это вопрос для новичка, но после безуспешных поисков я решил обратиться за помощью, хотя у меня такое ощущение, что ответ уже совсем рядом.
Я работаю над глоссарием в Excel/Таблицах, где каждая строка содержит список связанных терминов в двух столбцах: столбец A (английские термины) и столбец B (испанские термины). Точки с запятой разделяют термины в каждом столбце. Например:
Мне нужно вывести все возможные комбинации (например, декартово произведение) терминов из столбца A и столбца B в одной строке в отдельных строках. Результат для приведенного выше примера должен быть таким:
Можете ли вы помочь мне с формулой или любым другим методом достижения этого в Excel?
Например, я попробовал функцию SPLIT, которая делит текст вокруг указанного символа или строки и помещает каждый фрагмент в отдельную ячейку в строке, но это не то, что я на самом деле ищу, и было бы чрезвычайно утомительно переставлять вручную для сотен или тысяч значений.
Ответы:
В Google Таблицах:
=let(
get_, lambda(a, filter(a, len(A2:A), len(B2:B))),
split_, lambda(a, split(a, "; ", false)),
pairs, map(get_(A2:A), get_(B2:B), lambda(en, es,
torow(sort(split_(en) & "→" & tocol(split_(es))))
)),
sort(split(tocol(pairs, 1), "→"))
)
Видеть let(), лямбда(), фильтр(), Split(), карта(), torow(), tocol() и sort().
С Microsoft 365:
REDUCE используется для накопления объединенных строк, DROP начального значения, когда оно не понадобится в дальнейшем.
=LET(
last_data_cell, B50000,
data, A1:INDEX(B1:last_data_cell, COUNTA(B1:last_data_cell)),
comb_one, LAMBDA(acc, row,
LET(
lang2rows, TEXTSPLIT(INDEX(data, row, 2), , ";"),
VSTACK(
acc,
DROP(
REDUCE(
"Append combinations for single row",
TEXTSPLIT(INDEX(data, row, 1), ";"),
LAMBDA(acc, _l1,
VSTACK(acc, HSTACK(EXPAND(_l1, ROWS(lang2rows), , _l1), lang2rows))
)
),
1
)
)
)
),
TRIM(REDUCE(TAKE(data, 1), SEQUENCE(ROWS(data) - 1, , 2), comb_one))
)
В Excel с использованием Office 365 это может быть следующий подход:
=LET(SPLIT,LAMBDA(range,_c1,_c2,
LET(
dlm,"; ",
_i1,INDEX(range,,_c1),
t,TEXTSPLIT(TEXTAFTER(dlm&_i1,dlm,SEQUENCE(,MAX(1+LEN(_i1)-LEN(SUBSTITUTE(_i1,";",""))))),dlm),
SORTBY(
HSTACK(
TOCOL(t,2),
TOCOL(IF(LEN(t),INDEX(range,,_c2)),2)),
(-1*(_c1>_c2))*{1,2}))),
SPLIT(SPLIT(A2:B4,1,2),2,1))
Вы также можете создать лямбду именованного диапазона под названием SPLIT, которая использует аргументы range (оба столбца), _c1 индекс столбца разделяемого диапазона, _c2 индекс столбца другого столбца:
LAMBDA(range,_c1,_c2,LET(dlm,"; ",_i1,INDEX(range,,_c1),t,TEXTSPLIT(TEXTAFTER(dlm&_i1,dlm,SEQUENCE(,MAX(1+LEN(_i1)-LEN(SUBSTITUTE(_i1,";",""))))),dlm),
SORTBY(HSTACK(TOCOL(t,2),TOCOL(IF(LEN(t),INDEX(range,,_c2)),2)),(-1*(_c1>_c2))*{1,2})))
И тогда вы можете просто использовать: =SPLIT(SPLIT(A2:B4,1,2),2,1)
Или та же логика, что и опубликованное решение Google Sheets:
=LET(a,A2:A4,b,B2:B4,REDUCE(A1:B1,SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(TEXTAFTER(TOCOL("|"&TEXTSPLIT(INDEX(a,y),,"; ")&"|"&TEXTSPLIT(INDEX(b,y),"; ")),"|",{1,2}),"|")))))