Üldine valem
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Kokkuvõte
Lahtri testimiseks ühest paljudest stringidest, jättes teised välja, võite kasutada valemit, mis põhineb funktsioonidel SEARCH, ISNUMBER ja SUMPRODUCT. Näidatud näites on valem C5-s järgmine:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kus "kaasata" on nimega vahemik E5: E9 ja "välista" on nimega vahemik G5: G6.
Selgitus
Põhimõtteliselt kasutab see valem lahtri sees mitme stringi otsimiseks funktsiooni SEARCH. Vasakpoolse SUMPRODUCTi seest otsib SEARCH kõiki stringe nimetatud vahemikus "include".
Paremal SUMPRODUCT otsib SEARCH kõiki stringe nimetatud vahemikus "välista".
Valemi mõlemas osas tagastab SEARCH stringide leidmisel numbrilised positsioonid ja vead, kui neid ei leita. Funktsioon ISNUMBER teisendab numbrid TÕELISEKS ja vead VÄÄRIKS ning topeltnegatiiv teisendab VÄÄRTÕED 1 ja 0.
Sel hetkel näeb tulemus välja järgmine:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Siis:
=(1>0)*(0=0) =TRUE*TRUE =1
Märkus. See valem tagastab kas 1 või null, mida käsitletakse valemites, tingimusvormingus või andmete valideerimisel nagu TÕENE ja VALE.
