Üldine valem
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Kokkuvõte
Lahtri testimiseks, et näha, kas see sisaldab ühte paljudest stringidest, saate kasutada valemit, mis põhineb funktsioonidel OTSING, ISNUMBER ja SUMPRODUCT. Kopeeritud C5 valem on järgmine:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kus asjad on nimega vahemik E5: E9.
Selgitus
Me soovime, et testida iga raku B5: B11 et näha, kas see sisaldab mis tahes stringid nimega vahemik asjad (E5: E9). Valem, mida me C5-s kasutame, on kopeeritud:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
See valem põhineb valemil (mida on siin selgitatud), mis kontrollib lahtrit ühe alamstringi suhtes. Kui lahter sisaldab alamstringi, tagastab valem TÕENE. Kui ei, tagastab valem FALSE:
ISNUMBER(SEARCH(things,B5))
Sellisel juhul anname aga otsingule stringide loendi. Kuna asjades on 5 stringi , tagastab SEARCH sellise massiivi 5 tulemust:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kui SEARCH leiab stringi, tagastab see stringi positsiooni. Kui SEARCH ei leia stringi, tagastab see väärtuse #VALUE! viga. Kuna B5-s ilmub esimese sõnana "kollane", näeme tähte 1. Kuna teisi stringe ei leita, on ülejäänud 4 üksust vead.
See massiiv tagastatakse otse funktsioonile ISNUMBER. Seejärel tagastab ISNUMBER massiivi TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Kui meil on massiivis kasvõi üks TRUE, siis teame, et lahter sisaldab vähemalt ühte otsitavat stringi. Lihtsaim viis TRUE leidmiseks on kõigi väärtuste liitmine. Saame seda teha SUMPRODUCTiga, kuid kõigepealt peame sundima TRUE / FALSE väärtused 1-le ja 0-le topeltnegatiiviga (-) järgmiselt:
--ISNUMBER(SEARCH(things,B5))
See annab uue massiivi, mis sisaldab ainult 1-sid ja 0-sid:
(1;0;0;0;0)
tarnitakse otse SUMPRODUCTile:
=SUMPRODUCT((1;0;0;0;0))
Vaid ühe massiivi töötlemiseks lisab SUMPRODUCT massiivi üksused ja tagastab tulemuse. Mis tahes nullist erinev tulemus tähendab, et meil on "tabamus", nii et lisame> 0, et sundida TÕENE või VÄÄR lõpptulemus:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Kõvakodeeritud loendiga
Stringide loendi jaoks pole vaja kasutada vahemikku. Võite kasutada ka massiivi konstanti. Näiteks "punase", "sinise" ja "rohelise" kontrollimiseks võite kasutada järgmist valemit:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Vale vastete vältimine
Selle lähenemisviisi üks probleem on see, et pikemate sõnade sees esinevatest alamstringidest võite saada valesid vasteid. Näiteks kui proovite sobitada "dr", võite leida ka "Andrea", "juua", "kuiv" jne, kuna nende sõnade sees on "dr". See juhtub seetõttu, et SEARCH teeb automaatselt vaste "sisaldab".
Kiire häkkimise huvides võite otsingusõnade (nt "dr" või "dr") ümber tühiku lisada, et vältida "dr" teise sõna tabamist. Kuid see ei õnnestu, kui "dr" ilmub lahtris esimesena või viimasena või ilmub kirjavahemärkidega.
Kui vajate täpsemat lahendust, on üks võimalus teksti normaliseerida kõigepealt abistaja veerus, lisades kindlasti ka ees- ja tagaosa. Seejärel kasutate saadud tekstil selle lehe valemit.
