Поиск нескольких значений в другой таблице по ячейке, содержащей несколько значений с разделителями

329
Lucio

У меня есть две таблицы, которые выглядят так:

enter image description here

Я бы хотел, чтобы Excel использовал значение в ID company associatedтаблице 2 (зеленый), найдите его в таблице 1 (оранжевый) и верните значение из Company nameили несколько значений, когда несколько компаний связаны с одним человеком в таблице 2.

Какое будет лучшее решение?

1

1 ответ на вопрос

5
robinCTS

Вот формула, которая будет работать в Excel 2016, как есть. В более ранних версиях Excel требуется UDF для поли-заполнения TEXTJOIN(). (См. Этот пост для основного.)


Worksheet Screenshot

Массив введите ( Ctrl+ Shift+ Enter) следующую формулу F2и скопируйте-вставьте / заполните в остальную часть столбца:

{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}

Обратите внимание, что эта формула работает только в том случае, если значения в столбце Aфактически хранятся в виде чисел. Для текстовых значений --MID(…)формула должна быть заменена на TRIM(MID(…)).

Предварительно подтвержденная формула выглядит следующим образом:

{= TEXTJOIN( ";", TRUE, INDEX( (B:B), N(IF(1, MATCH( --MID( SUBSTITUTE(E2,";",REPT(" ",99)), 99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1) +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))), 99 ), (A:A), 0 ) )) ) )}

Заметки:

  • Предварительно подтвержденная формула действительно работает, если введена.
  • (A:A)Квадратные скобки в предварительно проверенной версии необходимы, чтобы заставить A:Aих оставаться на своей собственной линии. То же самое относится и к (B:B).


Для Excel 2016 (только для Windows) должна работать следующая более простая формула:

{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}

Как и предыдущая формула, она работает только со значениями, хранящимися в виде чисел. Для текстовых значений просто удалите --из формулы.

Привет RobinCTS, спасибо за предоставленный ответ, он работает потрясающе. Последний вопрос, связанный с вышеизложенным: как у меня есть формула для добавления разделителя между именами, скажем точка с запятой между названием компании? Очень признателен. Lucio 5 лет назад 0
@Lucio Хм, это уже так. Вот для чего первый `;` в формуле. Вы также можете изменить его на любую другую строку-разделитель. PS Если вы действительно оценили мою помощь, как только вы достигнете 15+ репутации, не забудьте вернуться и поднять мой ответ ;-) robinCTS 5 лет назад 0
На первый взгляд кажется, что я получаю только 1 значение для каждой ячейки в столбце F (1 название компании на ячейку в столбце F). Я ожидал, по крайней мере, несколько ячеек с несколькими значениями, разделенными точкой с запятой. Это возможно, но очень маловероятно, учитывая набор данных, с которым я работаю. Я тщательно исследую файл и вернусь к вам. PS: сделаем. Lucio 5 лет назад 0
@Lucio Вам нужно ввести массив в массив, используя `Ctrl` +` Shift` + `Enter`, а не просто` Enter`. (Вот почему есть фигурные скобки - чтобы напомнить вам об этом ;-)) PS Это не является причиной вашей проблемы, но для дальнейшего использования я добавил информацию к ответу, касающуюся чисел, хранящихся в виде текста, по сравнению с числами, хранящимися как фактические. Числа PPS Я также немного упростил непритретифицированную первую формулу. (Предварительно подтвержденная версия была уже более простой версией.) robinCTS 5 лет назад 0
Сработало 100%. Спасибо тебе за пояснение. Lucio 5 лет назад 0
Есть ли способ, что эта формула может вернуть текстовое значение, расположенное в столбце E? Если столбец E содержит сочетание чисел и текстовых значений, может ли формула вернуть имя, связанное с числом, и текстовое значение? Пример: E3 содержит «2; 3; Джонни» -> Формула возвращает «B; C; Джонни». Заранее спасибо. Lucio 5 лет назад 0
@Lucio Yes. The solution is too long to fit in a comment, though,and is not really suitable to add to this answer. You need to post a new question, linking it back to this question for context. Make sure the new question is self contained though. You can ping me when you've done that by posting a comment here. robinCTS 5 лет назад 0
thank you, I posted a new question here: https://superuser.com/questions/1334872/excel-textjoin-formula Lucio 5 лет назад 0

Похожие вопросы