Формула Excel: Знайти та отримати відсутні значення -

Зміст

Загальна формула

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))

Резюме

Щоб порівняти два списки та витягнути відсутні значення з одного списку в інший, ви можете використовувати формулу масиву на основі INDEX та MATCH. У наведеному прикладі останнє значення у списку B - у клітинці D11. Формула в D12, скопійована вниз, така:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

де "повним" є названий діапазон B5: B15.

Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.

Пояснення

Працюючи зсередини, ядром цієї формули є внутрішній вираз MATCH:

ISNA(MATCH(complete,$D$5:D11,0)

Тут функція MATCH використовується для порівняння всіх "повних" значень із частковим списком. Іменований діапазон "завершено" використовується для значень підстановки, а частковий список - як масив підстановки. Однак зауважте, що частковий список вводиться як розширюваний діапазон, який закінчується "на одну клітинку вище" комірки формули. Це дозволяє частковому списку розширюватись, включаючи нові значення, як вони з’являються під вихідним списком.

Результатом MATCH є масив чисел та # N / A помилок, де числа представляють значення у повному списку, які існують у частковому списку; а помилки представляють відсутні значення:

(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)

Функція ISNA використовується для перетворення цих результатів у масив значень TRUE і FALSE. У цьому масиві TRUE відповідає відсутнім значенням, а FALSE відповідає існуючим значенням:

(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

Функція ISNA повертає цей масив до зовнішнього MATCH як масив підстановки. Функція MATCH завжди повертає перше знайдене збіг, тому збіг поверне позицію (рядок) першого знайденого відсутнього значення. Цей результат повертається до INDEX як номер рядка, а названий діапазон "завершено" надається як масив.

У комірці D12 першим знайденим відсутнім значенням є "ківі" у рядку 2, тому маємо: