Yleinen kaava
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Yhteenveto
Voit estää syötteen, joka sisältää yhden monista asioista, käyttämällä mukautettua tietojen tarkistussääntöä, joka perustuu HAKU-toimintoon.
Esitetyssä esimerkissä tietoihin B5: B11 sovellettu tietojen validointi on:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Selitys
Tietojen tarkistussäännöt laukaistaan, kun käyttäjä lisää tai muuttaa solun arvoa.
Tämä kaava käyttää SEARCH-funktiota käyttäjän syötteen testaamiseen jokaiselle nimettyjen alueiden "luettelo" -arvolle. Hakulogiikka on "sisältää" - kun arvo luettelosta löytyy, SEARCH palauttaa arvon sijainnin numerona. Jos sitä ei löydy, SEARCH palauttaa virheen.
ISNUMBER-funktio muuntaa sitten numerot TOSiksi ja virheet EPÄTOSI, ja kaksoisnegatiivinen operaattori muuttaa TOSI EPÄTOSI -arvoiksi 1 ja nollan. Koska nimetty alue "lista" sisältää 5 arvoa, saamme 5 tulosta tällaisessa taulukossa:
(0; 0; 0; 0; 0)
SUMPRODUCT tiivistää sitten taulukon kohteet ja tulosta testataan nollan suhteen. Niin kauan kuin kaikki kohteet ovat nollia, SUMPRODUCT palauttaa nollan ja vahvistus onnistuu. Jos SUMPRODUCT palauttaa toisen numeron (eli kun kohde "luettelosta" löytyy), kaava palauttaa EPÄTOSI ja vahvistus epäonnistuu.
Huomautus: Soluviittaukset datan validointikaavoissa ovat suhteessa vasempaan yläkulmaan valitulla alueella, kun validointisääntö on määritetty, tässä tapauksessa B5.
