Знайдіть пропущені значення
Бувають випадки, коли потрібно порівняти два списки, щоб перевірити, чи існує значення списку 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")
√ Примітка. Ви можете змінити значення «Відсутній», «Знайдено» на будь-які значення.
- пошукове_значення: Значення MATCH, яке використовується для отримання його позиції, якщо воно існує в пошук_діапазон або помилка #N/A, якщо ні. Тут йдеться про продукти у вашому списку.
- діапазон пошуку: Діапазон клітинок для порівняння з lookup_value. Тут відноситься до списку товарів постачальника.
Знайти якщо всі продукти у вашому списку є в списку вашого постачальника, скопіюйте або введіть формулу нижче в клітинку Н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")
√ Примітка. Ви можете змінити значення «Відсутній», «Знайдено» на будь-які значення.
- пошукове_значення: Значення VLOOKUP, яке використовується для отримання його позиції, якщо воно існує пошук_діапазон або помилка #N/A, якщо ні. Тут йдеться про продукти у вашому списку.
- діапазон пошуку: Діапазон клітинок для порівняння з lookup_value. Тут відноситься до списку товарів постачальника.
Щоб дізнатися, чи всі продукти у вашому списку існують у списку вашого постачальника, будь ласка, скопіюйте або введіть формулу нижче в клітинку 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")
√ Примітка. Ви можете змінити значення «Знайдено», «Відсутнє» на будь-які значення.
- діапазон пошуку: Діапазон клітинок для порівняння з lookup_value. Тут відноситься до списку товарів постачальника.
- пошукове_значення: Значення COUNTIF, яке використовується для повернення кількості його входжень пошук_діапазон. Тут йдеться про продукти у вашому списку.
Щоб дізнатися, чи всі продукти у вашому списку існують у списку вашого постачальника, будь ласка, скопіюйте або введіть формулу нижче в клітинку 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.
Супутні функції
Функція IF - одна з найпростіших і найкорисніших функцій у книзі Excel. Він виконує простий логічний тест, який залежно від результату порівняння, і повертає одне значення, якщо результат ІСТИНА, або інше значення, якщо результат ФАЛЬШЕ.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
Функція Excel VLOOKUP шукає значення за допомогою відповідності в першому стовпці таблиці і повертає відповідне значення з певного стовпця в тому ж рядку.
Функція COUNTIF — це статистична функція в Excel, яка використовується для підрахунку кількості комірок, які відповідають критерію. Він підтримує логічні оператори (<>, =, > і <) і символи підстановки (? і *) для часткового збігу.
Пов’язані формули
Знайдіть значення, що містить певний текст із символами підстановки
Щоб знайти першу відповідність, яка містить певний текстовий рядок у діапазоні в Excel, можна використовувати формулу INDEX і MATCH із символами підстановки — зірочкою (*) і знаком питання (?).
Часткова відповідність із VLOOKUP
Іноді вам знадобиться Excel для отримання даних на основі часткової інформації. Щоб вирішити проблему, можна використовувати формулу ВПР разом із символами підстановки – зірочкою (*) та знаком питання (?).
Приблизний збіг з INDEX та MATCH
Бувають випадки, коли нам потрібно знайти приблизні збіги в Excel, щоб оцінити продуктивність працівників, оцінити результати учнів, обчислити поштові витрати на основі ваги тощо. У цьому підручнику ми поговоримо про те, як використовувати функції INDEX та MATCH для отримання потрібні нам результати.
Шукати значення найближчого відповідності з кількома критеріями
У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі більш ніж одного критерію. Завдяки поєднанню функцій INDEX, MATCH та IF ви можете швидко це зробити в Excel.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
