Všeobecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Zhrnutie
Na zvýraznenie buniek, ktoré obsahujú jeden z mnohých textových reťazcov, môžete použiť vzorec založený na funkciách ISNUMBER a SEARCH spolu s funkciou SUMPRODUCT. V zobrazenom príklade je podmienené formátovanie použité na B4: B11 založené na tomto vzorci:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Vysvetlenie
Pri práci zvnútra von táto časť vzorca vyhľadáva v každej bunke v B4: B11 všetky hodnoty v pomenovanom rozsahu „things“:
--ISNUMBER(SEARCH(things,B4)
Funkcia SEARCH vráti pozíciu hodnoty, ak sa nájde, a chybu #HODNOTA, ak sa nenájde. Pre B4 sa výsledky vrátia v takomto poli:
(8;#VALUE!;#VALUE!)
Funkcia ISNUMBER zmení všetky výsledky na TRUE alebo FALSE:
(TRUE;FALSE;FALSE)
Dvojitý zápor pred ISNUMBER vynúti hodnotu TRUE / FALSE na 1/0:
(1;0;0)
Funkcia SUMPRODUCT potom sčíta výsledky, ktoré sa testujú proti nule:
=SUMPRODUCT((1;0;0))>0
Akýkoľvek nenulový výsledok znamená, že bola nájdená aspoň jedna hodnota, takže vzorec vráti hodnotu TRUE, čo spustí pravidlo.
Prázdne veci ignorujte
Ak chcete ignorovať prázdne bunky v pomenovanom rozsahu „things“, môžete vyskúšať upravený vzorec takto:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Funguje to, pokiaľ testované textové hodnoty neobsahujú reťazec „FALSE“. Ak áno, môžete rozšíriť funkciu IF tak, aby obsahovala hodnotu, ak je v texte známe, že je nepravdivá (napr. „Zzzz“, „####“ atď.)
Možnosť rozlišovať veľké a malé písmená
SEARCH nerozlišuje veľké a malé písmená. Ak chcete skontrolovať aj prípad, nahraďte výraz SEARCH výrazom FIND takto:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Predchádzanie nesprávnym zhodám
Jedným z problémov tohto prístupu je, že môžete vidieť nesprávnu zhodu spôsobenú podreťazcami, ktoré sa objavujú vo vnútri dlhších slov. Ak sa napríklad pokúsite nájsť zhodu s výrazom „dr“, môžete tiež nájsť „Andrea“, „piť“, „suché“ atď., Pretože v týchto slovách sa vyskytuje výraz „dr“. Stáva sa to preto, lebo SEARCH automaticky vykoná zhodu „obsahuje“.
Ak chcete vykonať čiastočnú opravu, môžete okolo hľadaných slov (napr. „Dr“ alebo „dr“) pridať medzeru, aby ste zabránili chybe výrazu „dr“ v inom slove. To sa však nepodarí, ak sa výraz „dr“ zobrazí ako prvý alebo posledný v bunke alebo sa objaví vedľa interpunkcie. To možno čiastočne vyriešiť pridaním medzery aj okolo pôvodného textu. Ak chcete pridať medzeru na začiatok a na koniec oboch súčasne, môžete vyskúšať nasledujúci vzorec:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
To však nevyrieši problémy spôsobené interpunkciou.
Ak potrebujete úplnejšie riešenie, jednou z možností je normalizácia textu najskôr v stĺpci pomocníka, pričom treba pridať aj úvodný a koncový priestor. Potom môžete vyhľadávať celé slová obklopené medzerami.
