Загальна формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Резюме
Щоб виділити комірки, що містять одну з багатьох текстових рядків, можна використовувати формулу, засновану на функціях ISNUMBER та SEARCH, разом із функцією SUMPRODUCT. У наведеному прикладі умовне форматування, застосоване до B4: B11, базується на цій формулі:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Пояснення
Працюючи зсередини назовні, ця частина формули здійснює пошук у кожній клітинці B4: B11 для всіх значень у названому діапазоні "речей":
--ISNUMBER(SEARCH(things,B4)
Функція SEARCH повертає позицію значення, якщо знайдено, і помилку #VALUE, якщо не знайдено. Для B4 результати повертаються у такий масив:
(8;#VALUE!;#VALUE!)
Функція ISNUMBER змінює всі результати на TRUE або FALSE:
(TRUE;FALSE;FALSE)
Подвійний мінус перед ISNUMBER змушує TRUE / FALSE до 1/0:
(1;0;0)
Потім функція SUMPRODUCT підсумовує результати, які перевіряються проти нуля:
=SUMPRODUCT((1;0;0))>0
Будь-який ненульовий результат означає, що знайдено принаймні одне значення, тому формула повертає TRUE, запускаючи правило.
Ігноруйте порожні речі
Щоб ігнорувати порожні клітинки в названому діапазоні "речей", ви можете спробувати модифіковану формулу, як це:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Це працює, поки текстові значення, які ви перевіряєте, не містять рядка "FALSE". Якщо вони трапляються, ви можете розширити функцію IF, включивши значення, якщо хибне, як відомо, не зустрічається в тексті (тобто "zzzz", "####" тощо)
Параметр, чутливий до регістру
ПОШУК не враховує регістр. Щоб перевірити також випадок, замініть ПОШУК на ЗНАЙТИ так:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Запобігання помилковим збігам
Однією з проблем цього підходу є те, що ви можете бачити помилкові збіги, спричинені підрядками, які з’являються всередині довших слів. Наприклад, якщо ви намагаєтесь відповідати "dr", ви також можете знайти "Andrea", "drink", "dry" тощо, оскільки в цих словах з'являється "dr". Це відбувається тому, що ПОШУК автоматично виконує збіг "містить".
Для часткового виправлення ви можете додати простір навколо пошукових слів (наприклад, "dr" або "dr"), щоб уникнути вловлювання "dr" в іншому слові. Але це не вдасться, якщо "dr" з'явиться першим або останнім у клітинці або з'явиться поруч із розділовими знаками. Цю проблему можна частково вирішити, додавши пробіл також навколо оригінального тексту. Щоб додати простір до початку та кінця обох одночасно, ви можете спробувати формулу, подібну до цієї:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Однак це не усуне проблем, спричинених пунктуацією.
Якщо вам потрібне більш повне рішення, одним із варіантів є нормалізація тексту спочатку у допоміжній колонці, подбавши також про те, щоб додати провідний та кінцевий пробіл. Тоді ви можете шукати цілі слова, оточені пробілами.
