Блог

Вот моя проблема, у меня есть таблица с двумя столбцами: ссылки на продукты и соответствующие идентификаторы уведомлений:

  | A       | B      | C      | D      |
---------------------------------------
1| Product | Notice |        |        |
2| p1      | n1     |        |        |
3| p2      | n2     |        |        |
4| p3      | n3     |        |        |
5|         |        |        |        |
6|         |        | p1, p3 | =...   |
  

(редактировать: в моем реальном приложении столбцы «ссылки на продукты» и «идентификаторы уведомлений» расположены не рядом, а разделены другими столбцами)

Скажем, в другой ячейке (например, C6) у меня есть разделенный запятыми список ссылок на продукты, p1, p3 и мне нужна формула для вывода соответствующих идентификаторов уведомлений, т.Е. n1, n3 в данном случае, в ячейке D6.

Важно: По разным причинам я не могу использовать VBA, мне нужна стандартная формула массива Excel.

Вот что я могу сделать на данный момент:

1. с помощью FILTERXML функции я могу разделить список, разделенный запятыми, на массив: FILTERXML("<t><s>" amp; SUBSTITUTE(C6, ", ", "</s><s>") amp; "</s></t>", "//s")

2. с помощью TEXTJOIN функции я могу объединить массив в строку.

3. Я могу извлечь единственное совпадение с помощью комбинации функций INDEX и MATCH , например:

 =IF(ISERROR(MATCH("p3"; A:A; 0)); "not found"; INDEX(B:B; MATCH("p3"; A:A; 0)))
  

(что для меня бесполезно, поскольку ссылки в столбце A снова уникальны)

(Кстати, я не знаю, есть ли лучший способ обработать ошибку, вызванную MATCH , когда совпадение не найдено)

4. Я могу извлечь и объединить элементы столбца B, соответствующие нескольким совпадениям, с одной ссылкой в столбце A с помощью (формула массива активируется с помощью Ctrl Shift Enter):

 {=TEXTJOIN(", "; TRUE; IF(A:A="p2"; B:B; ""))}
  

(что для меня бесполезно, поскольку ссылки в столбце A снова уникальны)

Вкратце: я могу найти и объединить несколько совпадений с одной ссылкой, но я не могу найти и объединить одно уникальное совпадение с несколькими ссылками (что я хочу сделать).

Неудачные попытки

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

• Объединение 1, 2 и 4 (с использованием OR логического массива совпадений):

 {=TEXTJOIN(", "; TRUE; IF(OR(A:A=FILTERXML("<t><s>" amp; SUBSTITUTE(C6, ", ", "</s><s>") amp; "</s></t>", "//s")); B:B; ""))}
  

или (используя SUM для логического массива совпадений):

 {=TEXTJOIN(", "; TRUE; IF(SUM(A:A=FILTERXML("<t><s>" amp; SUBSTITUTE(C6, ", ", "</s><s>") amp; "</s></t>", "//s")); B:B; ""))}
  

Здесь я не уверен, как обрабатывать различные массивы, которые рассматриваются в IF (столбец A и список ссылок, приведенный FILTERXML ).).

• Объединение 1, 2 и 3:

 {=TEXTJOIN(", "; TRUE; INDEX(B:B; MATCH(FILTERXML("<t><s>" amp; SUBSTITUTE(C6, ", ", "</s><s>") amp; "</s></t>", "//s"); A:A; 0)))}
  

Здесь я не уверен, как обрабатывать (i) снова рассмотренные различные массивы (столбец A и список ссылок, заданный FILTERXML ), (ii) ошибку, вызванную MATCH , когда совпадение не найдено, (iii) ссылки на массив, переданные INDEX функции.

Хороший вопрос. Если у вас просто Excel 2019, возможно, вы могли бы использовать:

Формула в E1 :

 =TEXTJOIN(", ",,IFERROR(VLOOKUP(FILTERXML("<t><s>"amp;SUBSTITUTE(D1,", ","</s><s>")amp;"</s></t>","//s"),A:B,2,FALSE),""))
  

Если у вас есть Excel O365, то, возможно:

 =TEXTJOIN(", ",,XLOOKUP(FILTERXML("<t><s>"amp;SUBSTITUTE(D1,", ","</s><s>")amp;"</s></t>","//s"),A:A,B:B,"",0))
  

Попробуйте:

 =TEXTJOIN(",",TRUE,VLOOKUP(FILTERXML("<t><s>" amp; SUBSTITUTE(C6,",","</s><s>")amp;"</s></t>","//s"),tblProd[[Product]:[Notice]],COLUMNS(tblProd[[Product]:[Notice]]),FALSE))
  

Я использовал таблицы и структурированные ссылки, хотя вы можете изменить это на обычную адресацию, если вам абсолютно необходимо, но я думаю, что с таблицами и автоматически настраиваемыми ссылками это будет проще поддерживать.

Поскольку вы не знали расстояние между Product столбцом и Notice колонкой, я построил массив и получил Column Number аргумент для VLOOKUP использования COLUMNS функции