Общая формула
=FILTER(list1,COUNTIF(list2,list1))
Резюме
Чтобы сравнить два списка и извлечь общие значения, вы можете использовать формулу, основанную на функциях ФИЛЬТР и СЧЁТЕСЛИ. В показанном примере формула в F5:
=FILTER(list1,COUNTIF(list2,list1))
где список1 (B5: B15) и список2 (D5: D13) - именованные диапазоны. В результате значения, которые появляются в обоих списках, попадают в диапазон F5: F11.
Объяснение
Функция FILTER принимает массив значений и аргумент "include", который фильтрует массив на основе логического выражения или значения.
В этом случае массив предоставляется как именованный диапазон «list1», который содержит все значения из B5: B15. Включают в себя аргумент поставляется функцией СЧЕТЕСЛИ, который вложен в ФИЛЬТР:
=FILTER(list1,COUNTIF(list2,list1))
СЧЁТЕСЛИ устанавливается со списком 2 в качестве диапазона и списком 1 в качестве критерия . Поскольку мы даем COUNTIF одиннадцать значений критериев, COUNTIF возвращает одиннадцать результатов в виде такого массива:
(1;1;0;1;0;1;0;1;0;1;1)
Обратите внимание, что 1 соответствуют элементам в list2, которые появляются в list1.
Этот массив доставляется непосредственно в функцию FILTER в качестве аргумента "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Функция FILTER фильтрует list1, используя значения, предоставленные COUNTIF. Значения, связанные с нулем, удаляются; остальные значения сохраняются.
Конечный результат - это массив значений, которые существуют в обоих списках, которые попадают в диапазон F5: F11.
Расширенная логика
В приведенной выше формуле мы используем необработанные результаты из СЧЁТЕСЛИ в качестве фильтра. Это работает, потому что Excel оценивает любое ненулевое значение как ИСТИНА, а ноль - как ЛОЖЬ. Если СЧЁТЕСЛИ возвращает число больше 1, фильтр по-прежнему будет работать правильно.
Для явного принудительного получения результатов ИСТИНА и ЛОЖЬ вы можете использовать "> 0" следующим образом:
=FILTER(list1,COUNTIF(list2,list1)>0)
Удалить дубликаты или отсортировать
Чтобы удалить дубликаты, просто вложите формулу в функцию UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Чтобы отсортировать результаты, используйте функцию SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Значения списка отсутствуют в list2
Чтобы вывести значения в списке 1, отсутствующие в списке 2, вы можете изменить логику следующим образом:
=FILTER(list1,COUNTIF(list2,list1)=0)
