Я пытаюсь определить совпадения между столбцами D и K, И ЗАТЕМ, из этих совпадений, какие из них имеют совпадения в столбцах A и H. В основном столбцы D (ABC) и A (123) связаны, и K (ABC) и H (123) связаны. Так что из совпадений между D (ABC) и K (ABC), какие из них также имеют совпадения между A(123) и H(123). Я знаю, что столбцы D и K имеют много совпадающих значений, но меня волнует только то, какие из этих совпадений также имеют совпадения в соответствующих им столбцах A и H.
Итак, я хочу получить следующий список результатов матча (конечный результат): 1. D и K, ЗАТЕМ 2. A и H
Таким образом, может быть 3546 совпадений по #1, но из этих совпадений только 450 имеют и #1, и #2. Это похоже на сценарий IF/THEN. Я думаю, что в Excel это опция vlookup, но я не могу заставить ее работать. Я также думаю, что запрос Access мог бы работать, сравнивая лист 1 с листом 2 и вытягивая лист 3, который показывает совпадения между листом 1, столбцом A и листом 2, столбцом A и листом 1, столбцом B и листом 2, столбцом B.
решение1
Если я правильно понял ваш вопрос, у вас есть таблица значений, начинающихся со столбца A и продолжающихся как минимум до столбца K. Я предполагаю, что вы хотите вернуть таблицу, которая включает два набора строк в следующем порядке:
- Те, в которых совпадают как значения в столбцах D и K, так и значения в столбцах A и H.
- Те, в которых совпадают только значения в столбцах A и H.
Предположим, например, что ваши данные находятся в диапазоне A2:K1001, тогда ниже приведен один из способов получить то, что вам нужно. Он использует один вспомогательный столбец.
В ячейке M2 введите эту формулу и скопируйте ее вниз до ячейки M1001:
=IF(A2<>H2,2,IF(D2=K2,0,1))*ROWS($A$2:$A$1001)+ROWS($A$2:A2)
Формула присваивает номер каждой строке. Если совпадают оба столбца D и K и A и H, то число находится в диапазоне от 1 до 1000; если совпадают только столбцы A и H, то число находится в диапазоне от 1001 до 2000; в противном случае число находится в диапазоне от 2001 до 3000.
Затем введите следующую формулу в ячейку M1. Она вычисляет количество строк, которые вы хотите вернуть (то есть с соответствующими совпадающими значениями в столбцах D и K и A и H):
=COUNTIF($M$2:$M$1001,"<"&ROWS($M$2:$M$1001)*2+1)
Затем введите в ячейку N2 формулу, которая при копировании по горизонтали и вернет интересующие вас строки данных.
=INDEX(A$2:A$1001,MATCH(SMALL($M$2:$M$1001,ROWS($M$2:M2)),$M$2:$M$1001,0))
Работая от центра формулы наружу, функция НАИМЕНЬШИЙ вычисляет n-ное наименьшее из чисел, которое было только что назначено, то есть наименьшее, затем второе наименьшее и т. д. Функция ПОИСКПОЗ вычисляет, на сколько строк ниже столбца M находится это число — и соответствующая строка в вашей таблице данных. Наконец, функция ИНДЕКС возвращает значение данных, на сколько строк ниже столбца A.
Наконец, скопируйте формулу в ячейку Y2, чтобы получить первую полную строку данных, и скопируйте эти формулы вниз на количество строк, показанное в ячейке M1.