Я пробовал использовать различные комбинации функций, но, похоже, нет конкретной функции, которая могла бы возвращать не искомые/совпадающие значения. Делать это вручную занимает дни из-за большого объема данных, которые мне нужно отсортировать.
Я хочу, чтобы MS Excel 2003 извлек остаток списка A на основе списка B.
*Список A содержит 2000 наименований, список B — всего 10–30 наименований максимум.
Список А
№ 1----1 2 3 4 5 6 (каждая цифра размещается в 1 ячейке, всегда 6 цифр)
№ 2----1 1 2 3 4 5 (каждая цифра размещается в 1 ячейке, всегда 6 цифр)
№ 3----1 3 4 5 6 7 (каждая цифра размещается в 1 ячейке, всегда 6 цифр)
Список B
№ 1----1 2 3 (каждая цифра размещается в 1 ячейке, всегда 3 цифры)
№ 2----1 1 4 (каждая цифра размещается в 1 ячейке, всегда 3 цифры)
№ 3----2 3 5 (каждая цифра размещается в 1 ячейке, всегда 3 цифры)
Например:
В списке A найти совпадения (если есть) на основе входных данных из списка B и вернуть остаток в качестве выходных данных. Если совпадений не найдено, выходные данные не требуются.
Список А
№1----1 2 3 4 5 6 (каждая цифра размещается в 1 ячейке, всегда 6 цифр)
На основе списка B
№ 1----1 2 3 (совпадение найдено (присутствуют 1&2&3), затем я выбираю остаток вручную # # # 4 5 6 или = 456)
№ 2----1 1 4 (совпадение не найдено (отсутствуют 1&1&4), нет вывода)
№ 3----2 3 5 (совпадение найдено (присутствуют 2&3&5), затем я выбираю 1 # # 4 # 6 или вывод = 146)
Я решил, что использую функцию СЧЁТ для подсчёта частоты каждой цифры от 0 до 9 в каждом элементе списка A, а затем использую функцию ЕСЛИ и И (определяющую, какие и сколько цифр требуется для того, чтобы считаться совпадением), чтобы узнать, какие элементы списка B соответствуют списку A.
Итак, для каждого элемента в списке A мне нужно, чтобы Excel прогнал весь список B, а вывод мог бы варьироваться от отсутствия вывода до максимум 3 выводов.
Мне также нужна возможность изменять значения в списке B, не изменяя формулу, используемую для поиска, чтобы я мог с легкостью использовать один и тот же рабочий лист повторно.
До сих пор все мои попытки использовать другие функции не смогли извлечь остаток так, как я хочу. Если у вас есть какие-либо предложения, пожалуйста, научите меня.
решение1
Хорошо, вот решение, которое работает, но может вызвать у вас повреждение мозга при его настройке. Я строил его шаг за шагом, вычисляя один набор вещей, которые затем использовались в следующих вычислениях. Как только у меня появилась рабочая модель, я работал в обратном направлении, заменяя фактические формулы ссылками на ячейки, чтобы все формулы ссылались только на ваши фактические списки, а не на промежуточные вычисления. Формулы разрастались как грибы. Фактически, первая попытка дала формулы, которые превысили емкость ячеек. Я разделил ее на две таблицы, первая из которых подпитывала вторую. Таблицы очень большие, и вы бы сошли с ума, пытаясь заставить все ссылки на ячейки указывать на нужные места, чтобы заполнить формулы в двух направлениях по всем таблицам. Поэтому я добавил несколько косвенных ссылок, чтобы формулы можно было просто скопировать и вставить, и они работали без ручной очистки. К сожалению, это дало несколько довольно больших формул.
Я объясню это на примере, расположенном в определенных местах электронной таблицы. Если вам нужно найти части в другом месте, отредактируйте все ссылки на строки и столбцы в первой ячейке, а затем скопируйте и вставьте, чтобы заполнить таблицы. Для собственного спокойствия настройте несколько известных примеров, чтобы вы могли проверить, что первые несколько строк и столбцов в каждой таблице работают, прежде чем заполнять все это. Примите пару профилактических таблеток аспирина, и мы начнем.
Это основано на вашем списке 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. Это формула для M3:
=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. Поскольку каждая строка таблицы представляет запись списка A, сводку можно разместить в конце каждой строки таблицы. 30 столбцов таблицы заканчиваются столбцом BV, поэтому результаты находятся в столбце BW. Формула для BW3 будет следующей:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
Вместо того, чтобы показывать все 30 терминов, здесь показаны только первые два и последний. Следуйте той же схеме, чтобы добавить остальные. Он создает строку результата, объединяя результаты каждого совпадения. Если в ячейке есть значение, он добавляет пробел перед следующим значением. Если вам нужен другой разделитель, измените пробел на что-то другое, например, на запятую. Скопируйте эту формулу вниз по столбцу BW для всех строк.
Это, вероятно, не самое полезное место для результатов. Как только все заработает, вы сможете перемещать вещи. На самом деле, если вы что-то переместите, у вас может произойти массовая очистка ссылок на ячейки. Было бы разумнее просто создать нужный вам вывод в другом месте и использовать ссылки на ячейки для ссылки на то, что уже настроено.