Všeobecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Zhrnutie
Ak chcete otestovať, či bunka obsahuje jeden z mnohých reťazcov, môžete použiť vzorec založený na funkciách SEARCH, ISNUMBER a SUMPRODUCT. Vzorec v C5, skopírovaný nadol, je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kde veci sú pomenované rozsahy E5: E9.
Vysvetlenie
Chceme otestovať každú bunku v B5: B11, aby sme zistili, či obsahuje niektorý z reťazcov v pomenovaných veciach rozsahu (E5: E9). Vzorec, ktorý používame v C5, skopírovaný nadol, je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Tento vzorec je založený na vzorci (vysvetlenom tu), ktorý kontroluje bunku na jediný podreťazec. Ak bunka obsahuje podreťazec, vráti vzorec hodnotu TRUE. Ak nie, vzorec vráti FALSE:
ISNUMBER(SEARCH(things,B5))
V tomto prípade však dáme SEARCH zoznam reťazcov. Pretože vo veciach je 5 reťazcov , funkcia SEARCH vráti 5 výsledkov v takomto poli:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Keď SEARCH nájde reťazec, vráti jeho pozíciu. Ak SEARCH nenájde reťazec, vráti #HODNOTU! chyba. Pretože „žltá“ sa javí ako prvé slovo v B5, vidíme 1. Pretože ostatné reťazce sa nenašli, ďalšie 4 položky sú chyby.
Toto pole sa vracia priamo do funkcie ISNUMBER. ISNUMBER potom vráti pole s hodnotami TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Ak máme v poli čo i len jednu hodnotu TRUE, vieme, že bunka obsahuje aspoň jeden z reťazcov, ktoré hľadáme. Najjednoduchší spôsob, ako skontrolovať hodnotu TRUE, je spočítať všetky hodnoty. Môžeme to urobiť pomocou SUMPRODUCT, ale najskôr musíme vynútiť hodnoty TRUE / FALSE na 1 s a 0 s dvojitým záporom (-), ako je tento:
--ISNUMBER(SEARCH(things,B5))
Týmto sa získa nové pole obsahujúce iba 1 s a 0 s:
(1;0;0;0;0)
dodané priamo do SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
S iba jedným poľom na spracovanie SUMPRODUCT pridá položky do poľa a vráti výsledok. Akýkoľvek nenulový výsledok znamená, že máme „zásah“, takže pridáme> 0, aby sme vynútili konečný výsledok TRUE alebo FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
S pevne zakódovaným zoznamom
Nie je potrebné používať rozsah pre zoznam reťazcov, ktoré hľadáte. Môžete tiež použiť konštantu poľa. Napríklad na kontrolu „červenej“, „modrej“ a „zelenej“ môžete použiť nasledujúci vzorec:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Predchádzanie nesprávnym zhodám
Jedným z problémov tohto prístupu je, že môžete získať falošné zhody z podreťazcov, 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 rýchlo zaseknúť, môžete okolo hľadaných slov (napr. „Dr“ alebo „dr“) pridať medzeru, aby ste zabránili chyteniu slova „dr“ v inom slove. To sa však nepodarí, ak sa výraz „dr“ objaví v bunke ako prvý alebo posledný alebo sa objaví s interpunkciou.
Ak potrebujete presnejš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ú medzeru. Potom použijete vzorec na tejto stránke vo výslednom texte.
