Генеричка формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Резиме
Да бисте истакли ћелије које садрже један од многих текстуалних низова, можете да користите формулу засновану на функцијама БРОЈ и ТРАЖЕЊЕ, заједно са функцијом СУМПРОДУЦТ. У приказаном примеру, условно форматирање примењено на Б4: Б11 заснива се на овој формули:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Објашњење
Радећи изнутра, овај део формуле претражује сваку ћелију у Б4: Б11 за све вредности у именованом опсегу „ствари“:
--ISNUMBER(SEARCH(things,B4)
Функција СЕАРЦХ враћа положај вредности ако је пронађена и и грешка #ВАЛУЕ ако није пронађена. За Б4, резултати се враћају у низу попут овог:
(8;#VALUE!;#VALUE!)
Функција ИСНУМБЕР мења све резултате у ТРУЕ или ФАЛСЕ:
(TRUE;FALSE;FALSE)
Двоструки негатив испред ИСУМБЕР форсира ТРУЕ / ФАЛСЕ на 1/0:
(1;0;0)
Функција СУМПРОДУЦТ затим збраја резултате, који се тестирају у односу на нулу:
=SUMPRODUCT((1;0;0))>0
Било који резултат који није нула значи да је пронађена најмање једна вредност, па формула враћа ТРУЕ, покрећући правило.
Занемарите празне ствари
Да бисте занемарили празне ћелије у именованом опсегу „ствари“, можете испробати модификовану формулу попут ове:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Ово функционише све док вредности текста које тестирате не садрже низ „ФАЛСЕ“. Ако се то деси, можете да проширите функцију ИФ да укључи вредност ако се зна да се лажно не појављује у тексту (тј. „Зззз“, „####“ итд.)
Опција осетљива на велика и мала слова
ПРЕТРАГА не разликује велика и мала слова. Да бисте проверили и случај, замените СЕАРЦХ са ФИНД на следећи начин:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Спречавање лажних подударања
Један од проблема овог приступа је што ћете можда видети погрешна подударања узрокована поднизовима који се појављују унутар дужих речи. На пример, ако покушате да подударите са „др“, можда ћете пронаћи и „Андреа“, „пиће“, „суво“ итд., Јер се у тим речима појављује „др“. То се дешава јер СЕАРЦХ аутоматски извршава подударање „садржи”.
За делимично решење можете додати простор око речи за претрагу (нпр. „Др“ или „др“) како бисте избегли да „др“ захвати у другој речи. Али ово неће успети ако се „др“ појави прво или последње у ћелији или се појави поред интерпункције. То се делимично може решити додавањем простора такође око оригиналног текста. Да бисте истовремено додали простор на почетак и на крај, можете испробати формулу попут ове:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Међутим, ово неће решити проблеме узроковане интерпункцијом.
Ако вам је потребно цјеловитије рјешење, једна опција је нормализација текста прво у помоћној колони, водећи рачуна да додате и водећи и пратећи простор. Тада можете да тражите читаве речи окружене размацима.
