Намерете липсващите стойности
Има случаи, когато трябва да сравните два списъка, за да проверите дали стойност от списък 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 грешка, ако не. Тук се отнася за продуктите във вашия списък.
- диапазон_за_търсене: Диапазонът от клетки за сравнение с търсена_стойност. Тук се отнася до продуктовия списък на доставчика.
Да открия ако всички продукти във вашия списък съществуват в списъка на вашия доставчик, моля, копирайте или въведете формулата по-долу в клетка H6 и натиснете Въведете за да получите резултата:
=АКО(ISNA(МАЧ(30002,$B$6:$B$10,0)),"Липсва","Намерено")
Или използвайте препратка към клетка, за да направите формулата динамична:
=АКО(ISNA(МАЧ(G6,$B$6:$B$10,0)),"Липсва","Намерено")
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава диапазон_за_търсене във формулата няма да се промени, когато преместите или копирате формулата в други клетки. Въпреки това няма добавени знаци за долар търсена_стойност тъй като искате да е динамично. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу.
Обяснение на формулата
Тук използваме формулата по-долу като пример:
=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 ще се върне Туре.
- АКО(ISNA(MATCH(G8,$B$6:$B$10,0)),"Липсва","Намерено") = IF(TRUE,"Липсващи","Намерени"): Функцията 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 грешка, ако не. Тук се отнася за продуктите във вашия списък.
- диапазон_за_търсене: Диапазонът от клетки за сравнение с търсена_стойност. Тук се отнася до продуктовия списък на доставчика.
За да разберете дали всички продукти от вашия списък съществуват в списъка на вашия доставчик, моля, копирайте или въведете формулата по-долу в клетка H6 и натиснете Въведете за да получите резултата:
=АКО(ISNA(VLOOKUP(30002,$B$6:$B$10,1,FALSE)),"Липсва","Намерен")
Или използвайте препратка към клетка, за да направите формулата динамична:
=АКО(ISNA(VLOOKUP(G6,$B$6:$B$10,1,FALSE)),"Липсва","Намерен")
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава диапазон_за_търсене във формулата няма да се промени, когато преместите или копирате формулата в други клетки. Въпреки това няма добавени знаци за долар търсена_стойност тъй като искате да е динамично. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу.
Обяснение на формулата
Тук използваме формулата по-долу като пример:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- VLOOKUP(G8,$B$6:$B$10,1,НЕВЯРНО): Диапазонът_търсене FALSE принуждава функцията 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 ще се върне Туре.
- АКО(ISNA(VLOOKUP(G8,$B$6:$B$10,1,НЕВЯРНО)),"Липсва","Намерено") = IF(TRUE,"Липсващи","Намерени"): Функцията IF ще върне Missing, ако сравнението, направено от 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")
√ Забележка: Можете да промените „Намерени“, „Липсващи“ на всякакви стойности, както ви е необходимо.
- диапазон_за_търсене: Диапазонът от клетки за сравнение с търсена_стойност. Тук се отнася до продуктовия списък на доставчика.
- търсена_стойност: Стойността COUNTIF, използвана за връщане на броя на нейните срещания в диапазон_за_търсене. Тук се отнася за продуктите във вашия списък.
За да разберете дали всички продукти от вашия списък съществуват в списъка на вашия доставчик, моля, копирайте или въведете формулата по-долу в клетка H6 и натиснете Въведете за да получите резултата:
=АКО(БРОЕМЕ($B$6:$B$10,30002),"Намерени","Липсващи")
Или използвайте препратка към клетка, за да направите формулата динамична:
=АКО(БРОЕМЕ($B$6:$B$10,G6),"Намерени","Липсващи")
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава диапазон_за_търсене във формулата няма да се промени, когато преместите или копирате формулата в други клетки. Въпреки това няма добавени знаци за долар търсена_стойност тъй като искате да е динамично. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу.
Обяснение на формулата
Тук използваме формулата по-долу като пример:
=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. Той извършва прост логически тест, който в зависимост от резултата от сравнението, и връща една стойност, ако резултатът е TRUE, или друга стойност, ако резултатът е FALSE.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията VLOOKUP на Excel търси стойност чрез съвпадение в първата колона на таблица и връща съответната стойност от определена колона в същия ред.
Функцията COUNTIF е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий. Поддържа логически оператори (<>, =, > и <) и заместващи знаци (? и *) за частично съвпадение.
Свързани формули
Търсене на стойност, съдържаща конкретен текст със заместващи знаци
За да намерите първото съвпадение, което съдържа определен текстов низ в диапазон в Excel, можете да използвате формула INDEX и MATCH със заместващи знаци - звездичка (*) и въпросителен знак (?).
Има моменти, когато имате нужда от Excel, за да извлечете данни въз основа на частична информация. За да разрешите проблема, можете да използвате формула VLOOKUP заедно със заместващи знаци - звездичка (*) и въпросителен знак (?).
Приблизително съвпадение с INDEX и MATCH
Има моменти, когато трябва да намерим приблизителни съвпадения в Excel, за да оценим представянето на служителите, да оценим резултатите на учениците, да изчислим пощенските разходи въз основа на теглото и т.н. В този урок ще говорим как да използваме функциите INDEX и MATCH, за да извлечем резултати, от които се нуждаем.
Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
