Bendroji formulė
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Santrauka
Norėdami neleisti įvesties, kurioje yra vienas iš daugelio dalykų, galite naudoti pasirinktinę duomenų tikrinimo taisyklę, pagrįstą funkcija PAIEŠKA.
Pateiktame pavyzdyje duomenų patvirtinimas, taikomas B5: B11, yra:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Paaiškinimas
Duomenų tikrinimo taisyklės suaktyvinamos, kai vartotojas prideda arba pakeičia langelio vertę.
Ši formulė naudoja funkciją PAIEŠKA, kad būtų galima patikrinti vartotojo įvestį kiekvienai vertei, nurodytam diapazone „sąrašas“. Paieškos logika yra „yra“ - kai randama reikšmė iš „sąrašo“, PAIEŠKA grąžina vertės padėtį kaip skaičių. Jei nerandama, SEARCH pateikia klaidą.
Tada funkcija ISNUMBER konvertuoja skaičius į TRUE ir klaidas į FALSE, o dvigubai neigiamas operatorius pakeičia TRUE FALSE reikšmes į 1s ir nulius. Kadangi pavadintame diapazone „sąrašas“ yra 5 vertės, mes gauname 5 tokio masyvo rezultatus:
(0; 0; 0; 0; 0)
Tada SUMPRODUCT apibendrina masyvo elementus ir rezultatas patikrinamas lyginant su nuliu. Kol visi elementai yra lygūs nuliui, SUMPRODUCT grąžina nulį ir patvirtinimas pavyksta. Jei SUMPRODUCT pateikia kitą skaičių (ty kai randamas elementas „sąraše“), formulė pateikia FALSE ir patvirtinti nepavyksta.
Pastaba: langelių nuorodos duomenų tikrinimo formulėse yra santykinės su viršutine kairiąja ląstele diapazone, pasirinktu, kai apibrėžta patvirtinimo taisyklė, šiuo atveju B5.
