Формула на Excel: Проверете дали дадена клетка съдържа една от няколко стойности, но изключва други стойности
Да предположим, че има два списъка със стойности, искате да проверите дали клетката B3 съдържа една от стойностите в диапазон E3:E5, но в същото време не съдържа никакви стойности в диапазон F3:F4, както е показано на екранната снимка по-долу. Този урок ще предостави формула за бързо справяне с тази задача в Excel и ще обясни аргументите на формулата.
Обща формула:
| =(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0) |
Аргументи
| Text: the text string you want to check. |
| Include: the values you want to check if argument text contains. |
| Exclude: the values you want to check if argument text does not contain. |
Върната стойност:
Формулата връща 1 или 0. Когато клетката съдържа една от стойностите, които трябва да бъдат включени, и не съдържа никакви стойности, които трябва да бъдат изключени, тя връща 1 или връща 0. Тази формула, 1 и 0 се обработват като логически стойности ВЯРНО и НЕВЯРНО.
Как работи тази формула
Да предположим, че искате да проверите дали клетка B3 съдържа една от стойностите в диапазон E3:E5, но едновременно с това да изключите стойности в диапазон F3:F4, моля, използвайте формулата по-долу
| =(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) |
Натискане Въведете ключ, за да получите резултата от проверката.
Обяснение
Част 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) проверява дали клетката съдържа стойности в E3:E5
ТЪРСИ функция: функцията SEARCH връща позицията на първия знак от текстовия низ в друг, ако функцията SEARCH намери съответстващия текст, тя връща относителната позиция, ако не, връща #VALUE! грешка. Ето например формулата SEARCH($E$3:$E$5,B3) ще търси всяка стойност от диапазона E3:E5 в клетка B3 и връща местоположението на всеки текстов низ в клетка B3. Той ще върне резултат от масив като този: {1;7;12}.
Функция ISNUMBER: функцията ISNUMBER връща TRUE, когато клетка е число. Така ISNUMBER(SEARCH($E$3:$E$5,B3)) ще върне резултат от масив като {true,true,true}, тъй като функцията SEARCH намира 3 числа.
--ISNUMBER(SEARCH($E$3:$E$5,B3)) преобразува стойността TRUE в 1 и преобразува стойността FALSE в 0, така че тази формула променя резултата от масива на {1;1;1}.
SUMPRODUCT функция: използва се за умножаване на диапазони или сумиране на масиви и връща сумата от продуктите. The SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) връща 1+1+1=3.
Най-накрая сравнете лявата формула SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) и 0, докато резултатът от лявата формула е по-голям от 0, резултатът ще бъде TRUE или ще върне FALSE. Тук връща TRUE.
Част 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) проверява дали клетката не съдържа стойности във F3:F4
Формулата ТЪРСЕНЕ($F$3:$F$4,B3) ще търси всяка стойност в диапазона E3:E5 в клетка B3 и връща местоположението на всеки текстов низ в клетка B3. Той ще върне резултат от масив като този: {#VALUE!;#VALUE!}.
ISNUMBER(SEARCH($F$3:$F$4,B3)) ще върне резултат от масив като {false;false} тъй като функцията ТЪРСЕНЕ намира 0 число.
--ISNUMBER(SEARCH($F$3:$F$4,B3)) преобразува стойността TRUE в 1 и преобразува стойността FALSE в 0, така че тази формула променя резултата от масива на {0;0}.
SUMPRODUCT функция: използва се за умножаване на диапазони или сумиране на масиви и връща сумата от продуктите. The SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) връща 0+0=0.
Най-накрая сравнете лявата формула SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) и 0, докато резултатът от лявата формула е равен на 0, резултатът ще върне TRUE или ще върне FALSE. Тук връща TRUE.
Част 3: Множество две формули
=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)
=TRUE*TRUE
=1
Тази формула, 1 и 0 се обработват като логически стойности TRUE и FALSE.
Примерен файл
Кликнете, за да изтеглите примерен файл
Относителни формули
- Проверете дали дадена клетка съдържа определен текст
За да проверите дали дадена клетка съдържа някои текстове в диапазон A, но не съдържа текстовете в диапазон B, можете да използвате формула за масив, която комбинира функцията COUNT, SEARCH и AND в Excel - Проверете дали клетката съдържа едно от много неща
Този урок предоставя формула за проверка дали дадена клетка съдържа една от няколко стойности в Excel и обяснява аргументите във формулата и как работи формулата. - Проверете дали клетката съдържа едно от нещата
Да предположим, че в Excel има списък със стойности в колона E, искате да проверите дали клетките в колона B съдържат всички стойности в колона E и да върнете TRUE или FALSE. - Проверете дали клетката съдържа число
Понякога може да искате да проверите дали дадена клетка съдържа цифрови знаци. Този урок предоставя формула, която ще върне TRUE, ако клетката съдържа число, FALSE, ако клетката не съдържа число.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
