Obecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
souhrn
Chcete-li otestovat, zda buňka obsahuje jeden z mnoha řetězců, můžete použít vzorec založený na funkcích SEARCH, ISNUMBER a SUMPRODUCT. Zkopírovaný vzorec v C5 je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kde věci jsou pojmenovaný rozsah E5: E9.
Vysvětlení
Chceme otestovat každou buňku v B5: B11, abychom zjistili, zda obsahuje některý z řetězců v pojmenovaných věcech rozsahu (E5: E9). Zkopírovaný vzorec, který používáme v C5, je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Tento vzorec je založen na vzorci (vysvětleném zde), který kontroluje buňku na jediný podřetězec. Pokud buňka obsahuje podřetězec, vrátí vzorec hodnotu TRUE. Pokud ne, vrátí vzorec FALSE:
ISNUMBER(SEARCH(things,B5))
V tomto případě však dáme SEARCH seznam řetězců. Protože ve věcech je 5 řetězců , vrátí SEARCH 5 výsledků v takovém poli:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Když SEARCH najde řetězec, vrátí jeho pozici. Pokud SEARCH nenajde řetězec, vrátí #HODNOTA! chyba. Protože „žlutá“ se objevuje jako první slovo v B5, vidíme 1. Protože ostatní řetězce nejsou nalezeny, další 4 položky jsou chyby.
Toto pole se vrací přímo do funkce ISNUMBER. ISNUMBER pak vrátí pole hodnot PRAVDA / NEPRAVDA:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Pokud máme v poli co i jen TRUE, víme, že buňka obsahuje alespoň jeden z řetězců, které hledáme. Nejjednodušší způsob, jak zkontrolovat TRUE, je sečíst všechny hodnoty dohromady. Můžeme to udělat pomocí SUMPRODUCT, ale nejprve musíme vynutit hodnoty TRUE / FALSE na 1 s a 0 s dvojitým záporem (-), jako je tento:
--ISNUMBER(SEARCH(things,B5))
Tím se získá nové pole obsahující pouze 1 s a 0 s:
(1;0;0;0;0)
doručeno přímo do SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
S pouze jedním polem ke zpracování přidá SUMPRODUCT položky do pole a vrátí výsledek. Jakýkoli nenulový výsledek znamená, že máme „hit“, takže přidáme> 0, abychom vynutili konečný výsledek PRAVDA nebo NEPRAVDA:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
S pevně zakódovaným seznamem
Není nutné používat rozsah pro seznam řetězců, které chcete vyhledat. Můžete také použít maticovou konstantu. Chcete-li například zkontrolovat „červenou“, „modrou“ a „zelenou“, můžete použít následující vzorec:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Předcházení falešným shodám
Jedním z problémů tohoto přístupu je, že můžete získat falešné shody z podřetězců, které se objevují uvnitř delších slov. Pokud se například pokusíte najít výraz „dr“, můžete také najít „Andrea“, „drink“, „suchý“ atd., Protože uvnitř těchto slov se objevuje „dr“. K tomu dochází, protože SEARCH automaticky provede shodu „obsahuje“.
Chcete-li rychle zaseknout, můžete přidat hledaná slova (např. „Dr“ nebo „dr“), abyste zabránili zachycení slova „dr“ v jiném slově. To se ale nezdaří, pokud se „dr“ objeví jako první nebo poslední v buňce nebo se objeví s interpunkcí.
Pokud potřebujete přesnější řešení, jednou z možností je normalizovat text nejprve ve sloupci pomocníka a současně přidat úvodní a koncový prostor. Potom použijete vzorec na této stránce ve výsledném textu.
