Функция транспонирования и объединения в Excel

У меня есть такая таблица данных в excel.

Каждый мастер-код объединяет мой идентификатор клиента.

Master Code        Client ID
1                  172371;255712
1                  172371;C00322
2                  172408;287222;C00413
2                  172408;287222
3                  172931;265681;C01791
3                  172931;265681
3                  173131;267217;C02187

Я хотел бы вернуть такие результаты в столбце «Объединить все». Мне нужна только первая строка мастер-кода, которая уже содержит идентификаторы всех клиентов. Но если основной код строки дублируется, как в примере, это не имеет значения.

Master Code     Client ID                Merge all
1           172371;255712              172371;255712;C00322
1           172371;C00322              172371;255712;C00322
2           172408;287222;C00413       172408;287222;C00413
2           172408;287222              172408;287222;C00413
3           172931;265681;C01791       172931;265681;C01791;267217;173131
3           172931;265681              172931;265681;C01791;267217;173131
3           173131;267217;C02187       172931;265681;C01791;267217;173131

Есть ли способы добиться этого в Excel? Реально ли успеть?

Какая версия Экселя? Если у вас Windows O365, вы можете сделать это с помощью формул. В противном случае VBA или Power Query обеспечат более простое решение. Также, пожалуйста, объясните результаты Merge All для Master Code 3. Вы возвращаете только два кода из последней строки и в порядке, обратном их вводу.

Ron Rosenfeld 11.12.2020 03:58

Привет, @Ron Rosenfeld, да, у меня Windows O365. Порядок возврата здесь не имеет значения. Спасибо за вашу помощь!

Simon GIS 14.12.2020 01:57

Но это не объясняет, почему вы не возвращаете C02187 код 3

Ron Rosenfeld 14.12.2020 02:12
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
3
65
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Если предположить, что ваш столбец «Объединить все» ошибочен, поскольку не показывает C02187 вместо Master Code 3, тогда следующая формула возвращает желаемые результаты.

Обратите внимание, что я сделал диапазон данных Table и использую структурированные ссылки. Я думаю, что это более эффективно (динамические именованные диапазоны) и проще для понимания. Но вы можете изменить его на обычные ссылки, если хотите.

Единственная проблема с этим методом заключается в том, что если какой-либо из ваших идентификаторов клиента полностью состоит из цифр и начинается с нуля, начальные нули будут удалены. Если это может быть проблемой, Power Query является альтернативным решением.

=TEXTJOIN(";",TRUE,UNIQUE(FILTERXML("<t><s>" &SUBSTITUTE(TEXTJOIN("</s><s>",TRUE,FILTER([Client ID],[Master Code]=[@[Master Code]])),";","</s><s>")&"</s></t>","//s")))

  • FILTER возвращает только Client ID для соответствия Master Code
  • SUBSTITUTE и внутренний TEXTJOIN создают XML, в котором каждый идентификатор в отфильтрованной таблице является отдельным узлом.
  • FILTERXML извлекает каждый узел отдельно.
  • UNIQUE удаляет дубликаты (хотя это можно сделать с аргументом xPath вместо этого для тех, у кого нет UNIQUE.
  • Внешний TEXTJOIN соединяет его обратно, разделяя его точкой с запятой.

Другие вопросы по теме