Загальна формула
=FILTER(list1,COUNTIF(list2,list1))
Резюме
Щоб фільтрувати дані для вилучення відповідних значень у двох списках, можна скористатися функцією ФІЛЬТР і функцією СЧІСЛЕННЯ або ЧИСЛЕННЯ. У наведеному прикладі формула в F5 має вигляд:
=FILTER(list1,COUNTIF(list2,list1))
де list1 (B5: B16) і list2 (D5: D14) називаються діапазонами. Результат, повернутий FILTER, включає лише значення в list1, які відображаються в list2 .
Примітка: ФІЛЬТР - це нова функція динамічного масиву в 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 як аргумент include:
=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. Результат - список значень у списку1 , яких немає в списку2 .
За допомогою INDEX
Можна створити формулу для вилучення відповідних значень без функції ФІЛЬТР, але формула є більш складною. Одним із варіантів є використання функції 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 використовується для фіксації помилок, які виникають при копіюванні формули та закінченні відповідних значень. Інший приклад цієї ідеї див. У цій формулі.
