Как с помощью только функций достать ВСЕ данные из таблицы через ";" по ключу? не используя надстроек
Есть две таблицы
| код товара | LUX |
|---|---|
| ZP-1015 | |
| ZP-1016 | |
| ZP-1017 | |
| ZP-1018 | |
| ZP-1019 |
| код товара | LUX |
|---|---|
| ZP-1015 | 907.11.6 |
| ZP-1016 | 907.11.7 |
| ZP-1017 | 907.11.8 |
| ZP-1018 | 907.11.9 |
| ZP-1019 | 907.11.10 |
| ZP-1015 | 907.11.11 |
| ZP-1016 | 907.11.12 |
| ZP-1017 | 907.11.13 |
| ZP-1018 | 907.11.14 |
| ZP-1019 | 907.11.15 |
Нужно все артикулы LUX из заполненной таблицы, по ключу "код товара" добавить в незаполненную таблицу, сложность в том что в заполненной таблице есть дубли, значит в некоторых ячейках незаполненной таблицы должно быть несколько артикулов сразу, использовать "=фильтр" не вариант потому что вторые артикулы переносятся на нижние ячейки, нужно все артикулы в одной ячейке через ";"
текст задания: используя данные листа LUX, необходимо заполнить таблицу на листе "Задание 4" с помощью необходимых для этого формул и отобразить все кода LUX
Важно что нужно это сделать только с помощью формул и на чистом excel без надстроек
Так как в столбце "код товара" второй таблицы значения повторяются, использовать функцию ВПР, будет неверно потому что не все значения из столбца "LUX" попадут в первую таблицу.
Пробовать использовать функцию "ФИЛЬТР" =ФИЛЬТР(LUX!B:B;LUX!A:A=A2)
получилось примерно следующее
| код товара | LUX |
|---|---|
| ZP-1015 | #ПЕРЕНОС! |
| ZP-1016 | #ПЕРЕНОС! |
| ZP-1017 | #ПЕРЕНОС! |
| ZP-1018 | 907.11.9 |
| 907.11.14 |
Перенос пишет потому что вывод формулы должен занять ячейку с формулой и следующую под ней, а там другая формулы так что выдает ошибку. (добавить пустые строки не вариант изменять таблицу нельзя)
Ответы (1 шт):
Да, задачу можно решить одной формулой, применив группирование GROUPBY по ключу и конкатенацию по полю значений. Например, если разделитель между значениями LUX одного кода товара не принципиален, то для соединения можем применить ARRAYTOTEXT (в локализации по умолчанию разделителем будет запятая, в русскоязычной версии вероятно будет точка с запятой):
=GROUPBY(A.:.A, B.:.B, ARRAYTOTEXT, 1, 0)
Здесь:
A.:.A- область значений по столбцу кода товара исходной таблицы включая заглавие (замените на своё значение)B.:.B- область значений по столбцу LUX исходной таблицы включая заглавие (замените на своё значение)- ARRAYTOTEXT - аггрегирующая функция (не требует дополнительных аргументов);
1- четвёртым аргументом указываем наличие заглавий у столбцов (в этом варианта я исходил из того, что они есть, поэтому поставил единицу, т.е. заглавия есть и их нужно исключить; ставьте ноль, если заглавий нет)0- пятым аргументом указываем, нужны ли строки общей суммы (ноль значит не нужно)
Чтобы контролировать конкатенацию через TEXTJOIN, понадобиться LAMBDA:
=GROUPBY(A.:.A, B.:.B, LAMBDA(arr, TEXTJOIN(";", TRUE, arr)), 1, 0)
Внутри TEXTJOIN первым аргументом идет разделитель, вторым - указание игнорировать пустые ячейки, а третьим - соединяемые ячейки.
Дальше применяем фильтрацию к сгруппированным данным:
=LET(grp, GROUPBY(исходные_коды, исходные_lux, ARRAYTOTEXT, 1, 0),
VLOOKUP(колонка_целевых_кодов, grp, 2, FALSE))
Здесь:
- третий аргумент
VLOOKUP- номер поля сгруппированных данных, которое возвращается (нумерация с единицы, поэтому2, чтобы вернутьLUX) - четвёртый аргумент
VLOOKUP- как искать (FALSEзначит точное совпадение; имеет значение, если по целевому коду ничего не найдено)
Для большой исходной таблицы и малого набора целевых кодов можно попробовать сначала фильтровать, потом группировать:
=LET(
code, A2:A11, _1, "Колонка исходных товарных кодов",
lux, B2:B11, _2, "Колонка исходных значений LUX",
reference, E2:E7, _3, "Целевые коды товаров",
data, FILTER(HSTACK(code, lux), NOT(ISNA(XMATCH(code, reference)))),
grp, GROUPBY(CHOOSECOLS(data, 1), CHOOSECOLS(data, 2), ARRAYTOTEXT, 0, 0),
VLOOKUP(reference, grp, 2, FALSE)
)
