Обща формула
(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))
Обобщение
За да тествате клетка, за да видите дали съдържа определени думи, но не и други, можете да използвате формула на масив, базирана на функциите COUNT и SEARCH, увити във функцията AND.
В показания пример формулата в C5 е:
(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))
Тази формула връща TRUE, когато B5 съдържа някоя от думите в посочения диапазон inc и нито една от думите в посочения диапазон без .
Това е формула на масив и трябва да се въведе с Control + Shift + Enter.
Обяснение
Тази формула разчита на функцията AND за тестване на две условия едновременно:
- Броят на думите от посочения диапазон inc е> 0
- Брой думи от именувания диапазон exc е = 0
Ако и двете условия са TRUE, формулата връща TRUE. Ако едно от условията е FALSE, формулата връща FALSE.
Тестът за множество думи се извършва с помощта на функцията SEARCH с помощта на COUNT.
Когато SEARCH получи повече от един елемент за търсене, той връща масив от резултати, по един на елемент. Когато се намери съвпадение, SEARCH връща позицията на мача. Когато не бъде намерено съвпадение, SEARCH връща грешката #VALUE. Функцията COUNT получава брой числа в масива, върнат от SEARCH. COUNT игнорира грешки по подразбиране.
В показания пример формулата се решава в стъпки като тази
=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE
С твърдо кодирани стойности
Няма изискване да използвате диапазон за вашия списък с неща. Ако търсите само малък брой неща, можете да използвате списък във формат на масив, който се нарича константа на масива. Например, за да тествате червено, синьо или зелено, но изключете розово и оранжево, можете да използвате:
=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)
Тази версия не изисква контрол + shift + въвеждане на синтаксис на формула на масив.
