Üldine valem
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Kokkuvõte
Ühe paljudest asjadest koosneva sisendi keelamiseks võite kasutada kohandatud andmete valideerimise reeglit, mis põhineb funktsioonil OTSI.
Näidatud näites on andmete B5: B11 andmete valideerimine järgmine:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Selgitus
Andmete valideerimise reeglid käivitatakse, kui kasutaja lisab või muudab lahtri väärtust.
See valem kasutab funktsiooni SEARCH, et testida kasutaja sisestatud väärtusi nimega vahemikus "loend". Otsinguloogika on "sisaldab" - kui leitakse väärtus loendist, tagastab SEARCH väärtuse positsiooni numbrina. Kui seda ei leitud, tagastab SEARCH vea.
Funktsioon ISNUMBER teisendab seejärel numbrid TÕENE ja vead VÄÄRAKS ning topeltnegatiivne operaator muudab TÕENE VÄÄR väärtuseks 1 ja null. Kuna nimega vahemik "loend" sisaldab 5 väärtust, saame sellise massiivi abil tagasi 5 tulemust:
(0; 0; 0; 0; 0)
Seejärel võtab SUMPRODUCT massiivi üksused kokku ja tulemust kontrollitakse nulliga. Kuni kõik üksused on null, tagastab SUMPRODUCT nulli ja valideerimine õnnestub. Kui SUMPRODUCT tagastab muu numbri (st kui üksus loendist on leitud), tagastab valem vale ja valideerimine nurjub.
Märkus: lahtriviited andmete valideerimise valemites on valideerimisreegli määratlemisel valitud vahemikus vasaku ülemise lahtri (antud juhul B5) suhtes.
