Splošna formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Povzetek
Če želite celico preizkusiti, da vidite, ali vsebuje enega od številnih nizov, lahko uporabite formulo, ki temelji na funkcijah SEARCH, ISNUMBER in SUMPRODUCT. Kopirana formula v C5 je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kjer je stvar imenovani obseg E5: E9.
Pojasnilo
Želimo preizkusiti vsako celico B5: B11, da vidim, če vsebuje katero od nizov v imenovanih dosega stvari (E5: E9). Formula, ki jo uporabljamo v C5, kopirana navzdol, je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ta formula temelji na formuli (tukaj pojasnjena), ki preverja celico za posamezen podniz. Če celica vsebuje podniz, formula vrne TRUE. V nasprotnem primeru formula vrne FALSE:
ISNUMBER(SEARCH(things,B5))
V tem primeru pa ISKANJU damo seznam nizov. Ker je v stvareh 5 nizov , SEARCH vrne 5 rezultatov v matriki, kot je ta:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Ko SEARCH najde niz, vrne položaj tega niza. Če SEARCH ne najde niza, vrne #VALUE! napaka. Ker je "rumena" prikazana kot prva beseda v B5, vidimo 1. Ker drugih nizov ni mogoče najti, so ostali 4 elementi napake.
Ta matrika se vrne neposredno v funkcijo ISNUMBER. Nato ISNUMBER vrne polje TRUE / FALSE vrednosti:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Če imamo v polju celo eno TRUE, vemo, da celica vsebuje vsaj enega niza, ki ga iščemo. Najlažji način za preverjanje TRUE je seštevanje vseh vrednosti. To lahko storimo s SUMPRODUCT, vendar moramo najprej prisiliti vrednosti TRUE / FALSE na 1s in 0s z dvojnim negativom (-), kot je ta:
--ISNUMBER(SEARCH(things,B5))
Tako dobimo novo matriko, ki vsebuje samo 1 in 0:
(1;0;0;0;0)
dostavljeno neposredno SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Z enim samim nizom za obdelavo SUMPRODUCT doda elemente v matriki in vrne rezultat. Vsak rezultat, ki ni nič, pomeni, da imamo "zadetek", zato dodamo> 0, da vsilimo končni rezultat TRUE ali FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
S težko kodiranim seznamom
Za seznam nizov, ki jih je treba iskati, ni treba uporabiti obsega. Uporabite lahko tudi konstanto matrike. Na primer, če želite preveriti, ali so "rdeča", "modra" in "zelena", lahko uporabite naslednjo formulo:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Preprečevanje lažnih ujemanj
Težava tega pristopa je, da lahko dobite napačna ujemanja iz podnizov, ki se pojavijo v daljših besedah. Če na primer poskušate ujemati "dr", boste morda našli tudi "Andrea", "pijačo", "suho" itd., Ker se v teh besedah pojavlja "dr". To se zgodi, ker SEARCH samodejno izvede ujemanje "vsebuje".
Za hiter kramp lahko dodate prostor okoli iskalnih besed (npr. "Dr" ali "dr"), da ne boste ujeli besede "dr" z drugo besedo. Vendar to ne bo uspelo, če se bo "dr" pojavil prvi ali zadnji v celici ali če bo prikazan z ločili.
Če potrebujete natančnejšo rešitev, je ena možnost besedilo najprej normalizirati v pomožnem stolpcu, pri čemer pazite, da dodate tudi vodilni in zadnji prostor. Nato uporabite formulo na tej strani v nastalem besedilu.
