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