Декартово произведение с конкатенацией и фильтрацией строк?

Question

Я придумал задачу для своих учеников в Excel, но сам не могу ее решить.

Пожалуйста, смотрите изображение для деталей:

В столбцах A и C имеются базовые таблицы с компонентами, из которых будут создаваться пары.

В столбцах E и F есть таблица с запрещенными комбинациями.

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

Как заставить Excel автоматически создавать такой список самым простым способом, без промежуточных ячеек или программирования VBA?

Answer 1

Если вы можете использовать функцию TEXTJOIN тогда проще вернуть все возможности в одной ячейке, например, с помощью этой формулы массива

=TEXTJOIN(", ",1,IF(ISNA(MATCH(A$2:A$4&" "&TRANSPOSE(C$2:C$4),E$2:E$3&" "&F$2:F$3,0)),A$2:A$4&" "&TRANSPOSE(C$2:C$4),""))

подтверждено с помощью CTRL+SHIFT+ENTER

или если они должны быть в отдельных ячейках, то эта формула массива может быть использована в H2 скопированы

=TRIM(MID(TEXTJOIN(REPT(" ",1000),1,IF(ISNA(MATCH(A$2:A$4&" "&TRANSPOSE(C$2:C$4),E$2:E$3&" "&F$2:F$3,0)),A$2:A$4&" "&TRANSPOSE(C$2:C$4),"")),1000*(ROWS(H$2:H2)-1)+1,1000))

также подтверждается сочетанием клавиш CTRL+SHIFT+ENTER

Использование 1000 немного произвольно - это значение должно быть больше, чем общая длина всех подходящих строк