Загальна формула
(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))
Резюме
Щоб перевірити клітинку, щоб побачити, чи містять певні слова, але не інші, ви можете використовувати формулу масиву, засновану на функціях COUNT і SEARCH, обгорнутих у функцію AND
У наведеному прикладі формула в С5 має вигляд:
(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))
Ця формула повертає значення TRUE, коли B5 містить будь-яке зі слів у названому діапазоні inc та жодне зі слів у зазначеному діапазоні без .
Це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
Ця формула покладається на функцію І для тестування двох умов одночасно:
- Кількість слів із названого діапазону inc > 0
- Кількість слів із названого діапазону exc дорівнює = 0
Якщо обидві умови мають значення TRUE, формула повертає значення TRUE. Якщо будь-яка умова має значення FALSE, формула повертає FALSE.
Перевірка кількох слів проводиться за допомогою функції ПОШУК за допомогою COUNT.
Коли ПОШУК отримує більше одного елемента для пошуку, він повертає масив результатів, по одному на елемент. Коли збіг знайдений, ПОШУК повертає позицію збігу. Коли збігу не знайдено, SEARCH повертає помилку #VALUE. Функція COUNT отримує кількість чисел у масиві, що повертається пошуком. 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 + введення синтаксису формули масиву.
