Общая формула
=FILTER(list1,COUNTIF(list2,list1))
Резюме
Чтобы отфильтровать данные для извлечения совпадающих значений в двух списках, вы можете использовать функцию ФИЛЬТР и функцию СЧЁТЕСЛИ или СЧЁТЕСЛИМН. В показанном примере формула в F5:
=FILTER(list1,COUNTIF(list2,list1))
где список1 (B5: B16) и список2 (D5: D14) - именованные диапазоны. Результат, возвращаемый FILTER, включает только те значения в list1, которые появляются в list2 .
Примечание. ФИЛЬТР - это новая функция динамического массива в Excel 365.
Объяснение
Эта формула использует функцию ФИЛЬТР для извлечения данных на основе логического теста, построенного с помощью функции СЧЁТЕСЛИ:
=FILTER(list1,COUNTIF(list2,list1))
работая изнутри, функция СЧЁТЕСЛИ используется для создания фактического фильтра:
COUNTIF(list2,list1)
Обратите внимание, что мы используем list2 в качестве аргумента диапазона и list1 в качестве аргумента критерия. Другими словами, мы просим COUNTIF рассчитывать все значения в list1 , которые появляются в list2. Поскольку мы даем несколько значений критериям COUNTIF, мы возвращаем массив с несколькими результатами:
(1;1;0;1;0;1;0;0;1;0;1;1)
Обратите внимание, что массив содержит 12 счетчиков, по одному на каждое значение в списке list1 . Нулевое значение указывает на значение в списке 1 , которого нет в списке 2 . Любое другое положительное число указывает значение в списке1, которое находится в списке2 . Этот массив возвращается непосредственно в функцию FILTER в качестве аргумента включения:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Функция filter использует массив как фильтр. Любое значение в list1, связанное с нулем, удаляется, в то время как любое значение, связанное с положительным числом, сохраняется.
Результатом является массив из 7 совпадающих значений, которые попадают в диапазон F5: F11. Если данные изменяются, FILTER пересчитает и вернет новый список совпадающих значений на основе новых данных.
Несовпадающие значения
Чтобы извлечь несовпадающие значения из списка 1 (то есть значения в списке 1, которые не отображаются в списке 2 ), вы можете добавить функцию НЕ в формулу следующим образом:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Функция НЕ эффективно меняет результат СЧЁТЕСЛИ - любое ненулевое число становится ЛОЖЬ, а любое нулевое значение становится ИСТИНА. Результатом является список значений в list1 , которых нет в list2 .
С ИНДЕКСОМ
Можно создать формулу для извлечения совпадающих значений без функции ФИЛЬТР, но формула более сложная. Один из вариантов - использовать функцию ИНДЕКС в такой формуле:
Скопированная формула в G5:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Ядром этой формулы является функция ИНДЕКС, которая получает список1 в качестве аргумента массива. Большая часть оставшейся формулы просто вычисляет номер строки, используемый для сопоставления значений. Это выражение генерирует список относительных номеров строк:
ROW(list1)-ROW(INDEX(list1,1,1))+1
который возвращает массив из 12 чисел, представляющих строки в list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Они фильтруются с помощью функции ЕСЛИ и той же логики, которая использовалась выше в ФИЛЬТРЕ, на основе функции СЧЁТЕСЛИ:
COUNTIF(list2,list1) // find matching values
Результирующий массив выглядит так:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Этот массив доставляется непосредственно в функцию SMALL, которая используется для выборки следующего соответствующего номера строки, когда формула копируется в столбец. Значение k для МАЛЕНЬКОГО (думаю, nth) вычисляется с расширяющимся диапазоном:
ROWS($G$5:G5) // incrementing value for k
Функция ЕСЛИОШИБКА используется для перехвата ошибок, которые возникают, когда формула копируется и не хватает совпадающих значений. Другой пример этой идеи см. В этой формуле.
