Обща формула
=FILTER(list1,COUNTIF(list2,list1))
Обобщение
За да сравните два списъка и да извлечете общи стойности, можете да използвате формула, базирана на функциите FILTER и COUNTIF. В показания пример формулата във F5 е:
=FILTER(list1,COUNTIF(list2,list1))
където list1 (B5: B15) и list2 (D5: D13) са именувани диапазони. Резултатът, стойности, които се появяват в двата списъка, се разлива в диапазона F5: F11.
Обяснение
Функцията FILTER приема масив от стойности и аргумент "включва", който филтрира масива въз основа на логически израз или стойност.
В този случай масивът се предоставя като наименувания диапазон "list1", който съдържа всички стойности в B5: B15. Аргументът за включване се доставя от функцията COUNTIF, която е вложена във FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF е настроен с list2 като обхват и list1 като критерии . Тъй като даваме COUNTIF единадесет критериални стойности, COUNTIF връща единадесет резултата в масив като този:
(1;1;0;1;0;1;0;1;0;1;1)
Забележете, че 1 съответстват на елементи в list2, които се появяват в list1.
Този масив се доставя директно на функцията FILTER като аргумент "включва":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Списъкът на функциите за филтриране FILTER1 използва стойностите, предоставени от 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
За да изведете стойности в list1 липсващи от list2, можете да обърнете логиката по следния начин:
=FILTER(list1,COUNTIF(list2,list1)=0)
