Splošna formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Povzetek
Če želite preizkusiti celico, da vidite, ali vsebuje vse elemente na seznamu, lahko to storite s formulo, ki uporablja funkcijo ISKANJE, s pomočjo funkcij ISNUMBER, SUMPRODUCT in COUNTA.
Kontekst
Recimo, da imate seznam besedilnih nizov v obsegu B5: B8 in želite ugotoviti, ali te celice vsebujejo vse besede v drugem obsegu, E5: E7.
Lahko sestavite formulo, ki uporablja ugnezdene stavke IF za preverjanje vsakega predmeta, vendar se to ne bo dobro prilagodilo, če boste morali iskati veliko stvari. Vsakič, ko dodate besedo za iskanje, boste morali dodati še en ugnezdeni IF in prilagoditi oklepaje.
Rešitev
Rešitev je ustvariti formulo, ki šteje vsa ujemanja hkrati. Ko imamo to, preprosto primerjamo to število s številom predmetov, ki jih iščemo. Če se ujemajo, vemo, da celica vsebuje vse elemente.
V prikazanem primeru je formula, ki jo uporabljamo:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Pojasnilo
Ključ je ta delček:
ISNUMBER(SEARCH(things,B5)
To temelji na drugi formuli (tukaj je podrobno razloženo), ki preprosto preveri celico za posamezen podniz. Če celica vsebuje podniz, formula vrne TRUE. V nasprotnem primeru formula vrne FALSE.
Če pa isti formuli damo seznam stvari (v tem primeru uporabljamo imenovani obseg, imenovan "stvari", E5: E7), nam bo vrnil seznam TRUE / FALSE vrednosti, po eno za vsak element v . Rezultat je matrika, ki je videti takole:
(TRUE; TRUE; TRUE)
Kjer vsaka TRUE predstavlja najdeni element, vsaka FALSE pa element, ki ga ni mogoče najti.
Vrednosti TRUE / FALSE lahko prisilimo na 1s in 0s z dvojnim negativom (- imenovan tudi dvojni unar):
--ISNUMBER(SEARCH(things,B5))
ki da matriko, kot je ta:
(1; 1; 1)
Nato to matriko obdelamo s SUMPRODUCT, kar nam bo dalo skupno vsoto. Če je ta vsota enaka številu elementov v imenovanem obsegu "stvari", vemo, da smo našli vse stvari in lahko vrnemo TRUE. Način, kako to naredimo, je, da dve številki neposredno primerjamo. Z uporabo COUNTA dobimo število praznih celic v "stvareh":
=COUNTA(things)
S težko kodiranim seznamom
Ni zahteve, da za seznam stvari uporabite obseg. Če iščete le majhno število stvari, lahko uporabite seznam v obliki matrike, ki se imenuje konstanta matrike. Če na primer iščete samo rdečo, modro in zeleno barvo, lahko uporabite ("rdeča", "modra", "zelena") takole:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
