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