Знайдіть пропущені значення

Бувають випадки, коли потрібно порівняти два списки, щоб перевірити, чи існує значення списку A в списку B в Excel. Наприклад, у вас є список продуктів, і ви хочете перевірити, чи є продукти у вашому списку в списку продуктів, наданому вашим постачальником. Щоб виконати це завдання, нижче ми перерахували три способи, не соромтеся вибирати той, який вам подобається.

Знайдіть пропущені значення за допомогою MATCH, ISNA та IF
Знайдіть відсутні значення за допомогою VLOOKUP, ISNA та IF
Знайдіть пропущені значення за допомогою COUNTIF і IF

Знайдіть пропущені значення за допомогою MATCH, ISNA та IF

Знайти якщо всі продукти у вашому списку є в списку вашого постачальника як показано на знімку екрана вище, ви можете спочатку використовувати функцію MATCH, щоб отримати позицію продукту зі свого списку (значення списку A) у списку постачальників (список B). MATCH поверне помилку #N/A, якщо продукт не знайдено. Потім ви можете передати результат до ISNA, щоб перетворити помилки #N/A на TRUE, тобто ці продукти відсутні. Функція IF поверне очікуваний результат.

Родовий синтаксис

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Примітка. Ви можете змінити значення «Відсутній», «Знайдено» на будь-які значення.

Знайти якщо всі продукти у вашому списку є в списку вашого постачальника, скопіюйте або введіть формулу нижче в клітинку Н6 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:

=ЯКЩО(ISNA(СПІВНІЧНИК(30002,$ B $ 6: $ B $ 10,0)),"Відсутня","Знайдено")

Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:

=ЯКЩО(ISNA(СПІВНІЧНИК(G6,$ B $ 6: $ B $ 10,0)),"Відсутня","Знайдено")

√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, тобто пошук_діапазон формула не зміниться під час переміщення чи копіювання формули в інші клітинки. Однак до них не додано жодних знаків долара lookup_value оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

Пояснення формули

Тут ми використовуємо формулу нижче як приклад:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

• MATCH(G8,$B$6:$B$10,0): Тип збігу 0 змушує функцію MATCH повертати числове значення, яке вказує позицію першого збігу 3004, значення в комірці G8, в масиві $ B $ 6: $ B $ 10. Однак у цьому випадку MATCH не зміг знайти значення в масиві пошуку, тому він поверне значення # N / A помилка
• ISNA(MATCH(G8,$B$6:$B$10,0)) = ISNA(# N / A): ISNA працює, щоб з’ясувати, чи є значення помилкою «#N/A» чи ні. Якщо так, функція поверне TURE; Якщо значення не є помилкою «#N/A», воно поверне FALSE. Отже, ця формула ISNA повернеться TURE.
• ЯКЩО (ISNA(MATCH(G8,$B$6:$B$10,0)),"Відсутня","Знайдено") = IF(ІСТИНА,"Зникло безвісти","Знайдено"): Функція IF поверне Missing, якщо порівняння, зроблене ISNA і MATCH, є TRUE, інакше поверне Found. Отже, формула повернеться Відсутній.

Знайдіть відсутні значення за допомогою VLOOKUP, ISNA та IF

Щоб дізнатися, чи всі продукти у вашому списку існують у списку вашого постачальника, ви можете замінити функцію MATCH вище на VLOOKUP, оскільки вона працює так само, як MATCH, і повертає помилку #N/A, якщо значення не існує в інший список, або ми кажемо, що він відсутній.

Родовий синтаксис

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Примітка. Ви можете змінити значення «Відсутній», «Знайдено» на будь-які значення.

Щоб дізнатися, чи всі продукти у вашому списку існують у списку вашого постачальника, будь ласка, скопіюйте або введіть формулу нижче в клітинку H6 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:

=ЯКЩО(ISNA(VLOOKUP(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Відсутня","Знайдено")

Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:

=ЯКЩО(ISNA(VLOOKUP(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Відсутня","Знайдено")

√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, тобто пошук_діапазон формула не зміниться під час переміщення чи копіювання формули в інші клітинки. Однак до них не додано жодних знаків долара lookup_value оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

Пояснення формули

Тут ми використовуємо формулу нижче як приклад:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

• VLOOKUP(G8,$B$6:$B$10,1,НЕПРАВДА): Діапазон_пошук ПОМИЛКОВИЙ змушує функцію VLOOKUP шукати та повертати значення, яке точно відповідає 3004, значення в комірці G8. Якщо значення lookup_value 3004 існує в 1st стовпець масиву $ B $ 6: $ B $ 10, VLOOKUP поверне це значення; Інакше він поверне значення помилки #N/A. Тут 3004 не існує в масиві, отже, результат буде # N / A.
• ISNA(VLOOKUP(G8,$B$6:$B$10,1,НЕПРАВДА)) = ISNA(# N / A): ISNA працює, щоб з’ясувати, чи є значення помилкою «#N/A» чи ні. Якщо так, функція поверне TURE; Якщо значення не є помилкою «#N/A», воно поверне FALSE. Отже, ця формула ISNA повернеться TURE.
• ЯКЩО (ISNA(VLOOKUP(G8,$B$6:$B$10,1,НЕПРАВДА)),"Відсутня","Знайдено") = IF(ІСТИНА,"Зникло безвісти","Знайдено"): Функція IF поверне відсутнє, якщо порівняння, зроблене ISNA і VLOOKUP, має значення TRUE, інакше поверне значення Found. Отже, формула повернеться Відсутній.

Знайдіть пропущені значення за допомогою COUNTIF і IF

Щоб дізнатися, чи всі продукти у вашому списку є в списку вашого постачальника, ви можете скористатися простішою формулою з функціями COUNTIF і IF. Формула використовує той факт, що Excel оцінюватиме будь-яке число, крім нуля (0), як TRUE. Отже, якщо значення існує в іншому списку, функція COUNTIF поверне кількість його входжень у цей список, тоді IF прийме число як TURE; Якщо значення не існує в списку, функція COUNTIF поверне 0, а IF прийме його як FALSE.

Родовий синтаксис

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Примітка. Ви можете змінити значення «Знайдено», «Відсутнє» на будь-які значення.

Щоб дізнатися, чи всі продукти у вашому списку існують у списку вашого постачальника, будь ласка, скопіюйте або введіть формулу нижче в клітинку H6 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:

=ЯКЩО(КІЛЬКИ($ B $ 6: $ B $ 10,30002),"Знайдено","Пропав безвісти")

Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:

=ЯКЩО(КІЛЬКИ($ B $ 6: $ B $ 10,G6),"Знайдено","Пропав безвісти")

√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, тобто пошук_діапазон формула не зміниться під час переміщення чи копіювання формули в інші клітинки. Однак до них не додано жодних знаків долара lookup_value оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

Пояснення формули

Тут ми використовуємо формулу нижче як приклад:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

• COUNTIF($B$6:$B$10;G8): Функція COUNTIF підраховує, скільки разів це робить 3004, значення в клітинці G8, з’явиться в масиві $ B $ 6: $ B $ 10. Мабуть, 3004 не існує в масиві, отже, результат буде 0.
• ЯКЩО (COUNTIF($B$6:$B$10;G8),"Знайдено","Відсутня") = IF(0,"Знайдено","Пропав безвісти"): Функція IF оцінюватиме 0 як FALSE. Отже, формула повернеться Відсутній, значення, яке повертається, коли перше збільшення має значення FALSE.

Супутні функції

Пов’язані формули