Хорошо, вот решение, которое работает, но может привести к повреждению мозга при его настройке. Я построил это шаг за шагом, вычисляя один набор вещей, которые затем использовались в следующих вычислениях. Получив работающую модель, я работал в обратном направлении, подставляя фактические формулы для ссылок на ячейки, чтобы все формулы относились только к вашим фактическим спискам, а не к промежуточным вычислениям. Формулы грибные. Фактически, с первой попытки были получены формулы, которые превышали емкость ячейки. Я разделил его на два стола, первый кормил вторым. Таблицы очень большие, и вы бы пошли в бешенство, пытаясь заставить все ссылки на ячейки указывать на правильные места, чтобы заполнять формулы в двух направлениях по всей таблице. Поэтому я добавил несколько косвенных ссылок, чтобы формулы можно было просто скопировать и вставить, и они будут работать без ручной очистки. К сожалению, это привело к довольно большим формулам.
Я объясню это как пример, расположенный в определенных местах таблицы. Если вам нужно найти фрагменты в другом месте, отредактируйте все ссылки на строки и столбцы в первой ячейке, а затем скопируйте и вставьте, чтобы заполнить таблицы. Для вашего же удобства настройте несколько известных примеров, чтобы вы могли проверить, работают ли первые несколько строк и столбцов в каждой таблице, прежде чем заполнять все это. Возьмите пару профилактических препаратов аспирина, и мы начнем.
Это основано на вашем Списке A в столбцах от A до F с данными, начинающимися в строке 1 (2000 строк). Список B находится в столбцах с H по J, а данные начинаются со строки 1 (30 строк).
Первая таблица начинается в L1. Эта таблица создает список позиций записей списка B в записях списка A. Например:
Position: 1 2 3 4 5 6
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the entry in this table will be: 1 2 5 (stored as a single number: 125)
Если запись списка B не соответствует записи списка A, в ячейке будет # N/A. Макет этой таблицы выглядит следующим образом:
[L] [M] [N] [O]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Вы должны фактически поместить номера строк в качестве заголовков столбцов в строке 2 столбцов с M по AP и в качестве меток строк в столбце L. Это то, что формулы используют в качестве указателей. Существует 30 столбцов данных, по одному для каждой строки записей списка B, и у вас будет 2000 строк, представляющих записи в списке A, начиная со строки 3. Каждая ячейка таблицы отражает запись списка B против записи списка A. Это формула для М3:
=MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
+MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)
Я разбил формулу здесь, чтобы сделать ее более читабельной, но это все одна формула. Убедитесь, что он работает в M3 - N4 с некоторыми образцами данных, а затем скопируйте и вставьте, чтобы заполнить таблицу.
Вторая таблица начинается в AR1. Эта таблица структурирована таким же образом:
[AR] [AS] [AT] [AU]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Эта таблица работает аналогично первой - каждая ячейка представляет результаты записи списка B против записи списка A. Эта таблица содержит ваш остаток. Таким образом, в примере, который я привел для первой таблицы, остаток будет 359:
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the remainder is: 3 5 9
Формула, которая идет в ячейке AS3:
=IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))
Каждая ячейка в этой таблице будет содержать либо остаток, либо нулевой символ, если совпадений не было.
Вы хотели получить сводку результатов для каждой записи Списка А. Поскольку каждая строка таблицы представляет запись списка A, сводка может идти в конце каждой строки таблицы. 30 столбцов таблицы заканчиваются на столбце BV, поэтому результаты находятся в столбце BW. Формула для BW3 будет:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
Вместо того, чтобы показывать здесь все 30 терминов, здесь показаны только первые два и последний. Следуйте той же схеме, чтобы добавить остальные. Он строит строку результата, объединяя результаты каждого совпадения. Если в ячейке есть значение, он добавляет пробел перед следующим значением. Если вы хотите использовать другой разделитель, измените пробел на что-то другое, например, запятую. Скопируйте эту формулу вниз столбец BW для всех строк.
Это, вероятно, не самое полезное место для результатов. Как только у вас все заработало, вы можете перемещать вещи. На самом деле, если вы перемещаете что-либо, у вас может быть массовая очистка ссылок на ячейки. Было бы более разумно просто создать нужный вывод в другом месте и использовать ссылки на ячейки для ссылки на то, что уже настроено.
