Найти конечный результат на основе двух наборов критериев

Question

Я пытаюсь определить совпадения между столбцом 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

Таким образом, на # 1 может быть 3546 совпадений, но из этих совпадений только 450, которые разделяют и # 1, и # 2. Это как сценарий IF/THEN. Я думаю, что в Excel это вариант vlookup, но я не могу заставить его работать. Я также думаю, что запрос Access мог бы работать, сравнивая лист 1 с листом 2 и вытягивая лист 3, который показывает совпадения между столбцом A листа1 и столбцом A листа 2 и столбцом B листа 1 и столбцом B. листа 2

Answer 1

Если я правильно понимаю ваш вопрос, у вас есть таблица значений, начинающаяся в столбце A и продолжающаяся, по крайней мере, до столбца K. Я предполагаю, что вы хотите вернуть таблицу, которая включает два набора строк в следующем порядке:

1. Те, в которых совпадают как значения в столбцах D и K, так и значения в столбцах A и H.
2. Те, в которых совпадают только значения в столбцах 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))

Работая от центра формулы наружу, функция SMALL вычисляет n-е наименьшее из только что назначенных чисел, то есть наименьшее, затем второе наименьшее и т.д. Функция MATCH вычисляет, сколько строк в столбце M это число и соответствующая строка в вашей таблице данных - найдена. Наконец, функция INDEX возвращает значение данных на несколько строк в столбце A.

Наконец, скопируйте формулу в ячейку Y2, чтобы получить первую полную строку данных, и скопируйте эти формулы по числу строк, показанных в ячейке M1.