Obecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
souhrn
Chcete-li zvýraznit buňky, které obsahují jeden z mnoha textových řetězců, můžete použít vzorec založený na funkcích ISNUMBER a SEARCH spolu s funkcí SUMPRODUCT. V zobrazeném příkladu je podmíněné formátování aplikované na B4: B11 založeno na tomto vzorci:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Vysvětlení
Při práci zevnitř ven tato část vzorce prohledává každou buňku v B4: B11 všechny hodnoty v pojmenovaném rozsahu „things“:
--ISNUMBER(SEARCH(things,B4)
Funkce SEARCH vrací pozici hodnoty, pokud je nalezena, a chybu #HODNOTA, pokud není nalezena. U B4 se výsledky vrátí v poli, jako je tento:
(8;#VALUE!;#VALUE!)
Funkce ISNUMBER změní všechny výsledky na TRUE nebo FALSE:
(TRUE;FALSE;FALSE)
Dvojitý zápor před ISNUMBER vynutí hodnotu PRAVDA / NEPRAVDA na 1/0:
(1;0;0)
Funkce SUMPRODUCT poté sčítá výsledky, které se testují proti nule:
=SUMPRODUCT((1;0;0))>0
Jakýkoli nenulový výsledek znamená, že byla nalezena alespoň jedna hodnota, takže vzorec vrací TRUE a spustí pravidlo.
Prázdné věci ignorujte
Chcete-li ignorovat prázdné buňky v pojmenovaném rozsahu „things“, můžete vyzkoušet upravený vzorec, jako je tento:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Toto funguje, dokud testované textové hodnoty neobsahují řetězec „FALSE“. Pokud ano, můžete rozšířit funkci IF tak, aby zahrnovala hodnotu, pokud je známo, že se v textu nevyskytuje nepravda (tj. „Zzzz“, „####“ atd.)
Možnost rozlišovat velká a malá písmena
SEARCH nerozlišuje velká a malá písmena. Chcete-li zkontrolovat také případ, nahraďte SEARCH vyhledáním takto:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Předcházení falešným shodám
Jedním z problémů tohoto přístupu je, že můžete vidět falešné shody způsobené podřetězci, 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 provádí shodu „obsahuje“.
Chcete-li provést částečnou opravu, můžete do hledaných slov (např. „Dr“ nebo „dr“) přidat mezeru, aby nedošlo k 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í vedle interpunkce. To lze částečně vyřešit přidáním mezery také kolem původního textu. Chcete-li přidat mezeru na začátek a konec obou současně, můžete zkusit následující vzorec:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
To však neopraví problémy způsobené interpunkcí.
Pokud potřebujete úplnější řešení, jednou z možností je normalizovat text nejprve ve sloupci pomocníka a zároveň přidat úvodní a koncový prostor. Poté můžete hledat celá slova obklopená mezerami.
