Обща формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Обобщение
За да тествате клетка, за да видите дали тя съдържа един от многото низове, можете да използвате формула, базирана на функциите SEARCH, ISNUMBER и SUMPRODUCT. Формулата в 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 в масива, знаем, че клетка съдържа поне един от низовете, които търсим. Най-лесният начин да проверите за TRUE е да добавите всички стойности заедно. Можем да направим това със SUMPRODUCT, но първо трябва да принудим стойностите TRUE / FALSE на 1s и 0s с двоен отрицателен (-) по следния начин:
--ISNUMBER(SEARCH(things,B5))
Това дава нов масив, съдържащ само 1s и 0s:
(1;0;0;0;0)
доставени директно на SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Само с един масив за обработка, SUMPRODUCT добавя елементите в масива и връща резултат. Всеки ненулев резултат означава, че имаме "хит", така че добавяме> 0, за да принудим краен резултат от TRUE или FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
С твърдо кодиран списък
Не е необходимо да използвате диапазон за списъка с низове, който да търсите. Можете също да използвате константа на масив. Например, за да проверите за „червено“, „синьо“ и „зелено“, можете да използвате формула като тази:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Предотвратяване на фалшиви съвпадения
Един проблем при този подход е, че може да получите фалшиви съвпадения от поднизове, които се появяват в по-дълги думи. Например, ако се опитате да съчетаете „dr“, можете също да намерите „Andrea“, „drink“, „dry“ и т.н., тъй като „dr“ се появява вътре в тези думи. Това се случва, защото SEARCH автоматично прави съвпадение „съдържа“.
За бърз хак можете да добавите място около думите за търсене (т.е. „dr“ или „dr“), за да избегнете улавянето на „dr“ с друга дума. Но това ще се провали, ако „dr“ се появи първо или последно в клетка или се появи с пунктуация.
Ако се нуждаете от по-точно решение, една от възможностите е да нормализирате текста първо в помощна колона, като внимавате да добавите и водещо и последващо пространство. След това използвате формулата на тази страница в получения текст.
