Note: The other languages of the website are Google-translated. Back to English

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

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

знайти пропущені значення 1

Знайдіть пропущені значення за допомогою 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 оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

знайти пропущені значення 2

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

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

=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 оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

знайти пропущені значення 3

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

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

=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 оскільки ви хочете, щоб він був динамічним. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

знайти пропущені значення 4

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

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

=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.

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

Функція Excel IF

Функція IF - одна з найпростіших і найкорисніших функцій у книзі Excel. Він виконує простий логічний тест, який залежно від результату порівняння, і повертає одне значення, якщо результат ІСТИНА, або інше значення, якщо результат ФАЛЬШЕ.

Функція Excel MATCH

Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.

Функція Excel VLOOKUP

Функція Excel VLOOKUP шукає значення за допомогою відповідності в першому стовпці таблиці і повертає відповідне значення з певного стовпця в тому ж рядку.

Функція Excel COUNTIF

Функція COUNTIF — це статистична функція в Excel, яка використовується для підрахунку кількості комірок, які відповідають критерію. Він підтримує логічні оператори (<>, =, > і <) і символи підстановки (? і *) для часткового збігу.


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

Знайдіть значення, що містить певний текст із символами підстановки

Щоб знайти першу відповідність, яка містить певний текстовий рядок у діапазоні в Excel, можна використовувати формулу INDEX і MATCH із символами підстановки — зірочкою (*) і знаком питання (?).

Часткова відповідність із VLOOKUP

Іноді вам знадобиться Excel для отримання даних на основі часткової інформації. Щоб вирішити проблему, можна використовувати формулу ВПР разом із символами підстановки – зірочкою (*) та знаком питання (?).

Приблизний збіг з INDEX та MATCH

Бувають випадки, коли нам потрібно знайти приблизні збіги в Excel, щоб оцінити продуктивність працівників, оцінити результати учнів, обчислити поштові витрати на основі ваги тощо. У цьому підручнику ми поговоримо про те, як використовувати функції INDEX та MATCH для отримання потрібні нам результати.

Шукати значення найближчого відповідності з кількома критеріями

У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі більш ніж одного критерію. Завдяки поєднанню функцій INDEX, MATCH та IF ви можете швидко це зробити в Excel.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL