Загальна формула
=FILTER(list1,COUNTIF(list2,list1))
Резюме
Для порівняння двох списків та вилучення загальних значень можна скористатися формулою, заснованою на функціях ФІЛЬТР і РІЧНИК. У наведеному прикладі формула в F5 має вигляд:
=FILTER(list1,COUNTIF(list2,list1))
де list1 (B5: B15) і list2 (D5: D13) називаються діапазонами. Результат, значення, що відображаються в обох списках, поширюється на діапазон F5: F11.
Пояснення
Функція FILTER приймає масив значень та аргумент "включати", який фільтрує масив на основі логічного виразу або значення.
У цьому випадку масив подається у вигляді іменованого діапазону "list1", який містить усі значення в B5: B15. Аргумент include доставляється функцією COUNTIF, яка вкладена всередину FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF встановлюється з list2 як діапазон , а list1 як критерій . Оскільки ми даємо COUNTIF одинадцять значень критеріїв, COUNTIF повертає одинадцять результатів у такому масиві:
(1;1;0;1;0;1;0;1;0;1;1)
Зверніть увагу, що 1 відповідає елементам у списку2, які з’являються у списку1.
Цей масив доставляється безпосередньо до функції FILTER як аргумент "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Функція ФІЛЬТР фільтрує список1, використовуючи значення, надані COUNTIF. Значення, пов'язані з нулем, видаляються; інші цінності зберігаються.
Кінцевим результатом є масив значень, що існують в обох списках, який переходить у діапазон F5: F11.
Розширена логіка
У наведеній вище формулі ми використовуємо необроблені результати з COUNTIF як фільтр. Це працює, оскільки Excel обчислює будь-яке ненульове значення як TRUE, а нуль як FALSE. Якщо функція COUNTIF повертає кількість, більшу за 1, фільтр все одно працюватиме належним чином.
Щоб явно примусити результати TRUE і FALSE, ви можете використовувати "> 0" таким чином:
=FILTER(list1,COUNTIF(list2,list1)>0)
Видаліть дублікати або сортуйте
Щоб видалити дублікати, просто вкладіть формулу всередину функції UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Щоб відсортувати результати, вкладіться у функцію SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Значення списку відсутні в списку2
Щоб вивести значення в списку1, відсутніх у списку2, ви можете змінити логіку таким чином:
=FILTER(list1,COUNTIF(list2,list1)=0)
