Обща формула
=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", "####" и т.н.)
Опция, чувствителна към малки и големи букви
ТЪРСЕНЕ не е чувствително към регистъра За да проверите и случая, заменете SEARCH с FIND така:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Предотвратяване на фалшиви съвпадения
Един проблем при този подход е, че може да видите фалшиви съвпадения, причинени от поднизове, които се появяват в по-дълги думи. Например, ако се опитате да съчетаете „dr“, можете също да намерите „Andrea“, „drink“, „dry“ и т.н., тъй като „dr“ се появява вътре в тези думи. Това се случва, защото SEARCH автоматично изпълнява съвпадение „съдържа“.
За частично коригиране можете да добавите интервал около думите за търсене (т.е. „dr“ или „dr“), за да избегнете улавянето на „dr“ с друга дума. Но това ще се провали, ако „dr“ се появи първо или последно в клетка или се появи до пунктуацията. Това може да бъде адресирано частично чрез добавяне на пространство също около оригиналния текст. За да добавите място към началото и края едновременно, можете да опитате формула като тази:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Това обаче няма да отстрани проблеми, причинени от пунктуацията.
Ако се нуждаете от по-цялостно решение, една от опциите е да нормализирате текста първо в помощна колона, като внимавате да добавите и водещо и последващо пространство. След това можете да търсите цели думи, заобиколени от интервали.
