Загальна формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Резюме
Щоб протестувати клітинку, щоб побачити, чи містить вона одну з багатьох рядків, можна скористатися формулою, заснованою на функціях ПОШУК, ІЗНОМКА та ПІДСУМОК. Формула в C5, скопійована вниз, є:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
де речами є названий діапазон E5: E9.
Пояснення
Ми хочемо , щоб перевірити кожну клітинку B5: B11 , щоб побачити , якщо вона містить будь-який з рядків в іменований діапазон речей (E5: E9). Формула, яку ми використовуємо в C5, скопійована нижче, така:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ця формула базується на формулі (пояснена тут), яка перевіряє клітинку на наявність одного підрядка. Якщо комірка містить підрядок, формула повертає TRUE. Якщо ні, формула повертає FALSE:
ISNUMBER(SEARCH(things,B5))
Однак у цьому випадку ми надаємо SEARCH список рядків. Оскільки в речах є 5 рядків , SEARCH повертає 5 результатів у такому масиві:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Коли SEARCH знаходить рядок, він повертає позицію цього рядка. Якщо SEARCH не знаходить рядок, він повертає #VALUE! помилка. Оскільки "жовтий" з'являється як перше слово в B5, ми бачимо 1. Оскільки інші рядки не знайдені, інші 4 елементи є помилками.
Цей масив повертається безпосередньо до функції ISNUMBER. Потім ISNUMBER повертає масив значень TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Якщо в масиві є хоча б одна ІСТИНА, ми знаємо, що комірка містить принаймні одну з рядків, яку ми шукаємо. Найпростіший спосіб перевірити TRUE - це скласти всі значення разом. Ми можемо зробити це за допомогою SUMPRODUCT, але спочатку нам потрібно примусити значення TRUE / FALSE до 1s і 0s з подвійним негативом (-), як це:
--ISNUMBER(SEARCH(things,B5))
Це дає новий масив, що містить лише 1 і 0:
(1;0;0;0;0)
доставляється безпосередньо в SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Маючи лише один масив для обробки, SUMPRODUCT додає елементи в масив і повертає результат. Будь-який ненульовий результат означає, що ми маємо "хіт", тому ми додаємо> 0, щоб примусити остаточний результат ІСТИНИ або ФАЛИ:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
З жорстко закодованим списком
Не потрібно використовувати діапазон для списку рядків для пошуку. Ви також можете використовувати константу масиву. Наприклад, щоб перевірити наявність "червоного", "синього" та "зеленого", ви можете скористатися такою формулою:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Запобігання помилковим збігам
Однією з проблем цього підходу є те, що ви можете отримати помилкові збіги з підрядків, які з’являються всередині довших слів. Наприклад, якщо ви намагаєтесь відповідати "dr", ви також можете знайти "Andrea", "drink", "dry" тощо, оскільки в цих словах з'являється "dr". Це відбувається тому, що ПОШУК автоматично виконує збіг "містить".
Для швидкого злому ви можете додати простір навколо пошукових слів (наприклад, "dr", або "dr"), щоб уникнути вловлювання "dr" в іншому слові. Але це не вдасться, якщо "dr" з'явиться першим або останнім у клітинці або з'явиться з пунктуацією.
Якщо вам потрібне більш точне рішення, одним із варіантів є нормалізація тексту спочатку у допоміжній колонці, подбавши також про те, щоб додати провідний та кінцевий пробіл. Потім ви використовуєте формулу на цій сторінці з отриманим текстом.
