Vispārēja formula
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Kopsavilkums
Lai aizliegtu ievadi, kas satur vienu no daudzajām lietām, varat izmantot pielāgotu datu validācijas kārtulu, kuras pamatā ir funkcija MEKLĒŠANA.
Parādītajā piemērā datu validācija, kas piemērota B5: B11, ir:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Paskaidrojums
Datu validācijas kārtulas tiek aktivizētas, kad lietotājs pievieno vai maina šūnas vērtību.
Šī formula izmanto funkciju MEKLĒŠANA, lai pārbaudītu lietotāja ievadi katrai vērtībai nosauktajā diapazonā "saraksts". Meklēšanas loģika ir "satur" - kad tiek atrasta vērtība no "saraksta", MEKLĒŠANA atgriež vērtības pozīciju kā skaitli. Ja tas nav atrasts, SEARCH atgriež kļūdu.
Funkcija ISNUMBER pēc tam pārvērš skaitļus par TRUE un kļūdas par FALSE, un dubultnegatīvais operators TRUE FALSE vērtības maina uz 1s un nullēm. Tā kā nosauktajā diapazonā "saraksts" ir 5 vērtības, mēs iegūstam 5 rezultātus šādā masīvā:
(0; 0; 0; 0; 0)
Pēc tam SUMPRODUCT apkopo masīva vienumus, un rezultāts tiek pārbaudīts pret nulli. Kamēr visi vienumi ir nulle, SUMPRODUCT atgriež nulli un validācija izdodas. Ja SUMPRODUCT atgriež citu skaitli (ti, kad tiek atrasts vienums sarakstā), formula atgriež FALSE un validācija neizdodas.
Piezīme. Šūnu atsauces datu validācijas formulās ir saistītas ar augšējo kreiso šūnu diapazonā, kas izvēlēts, kad ir definēts validācijas noteikums, šajā gadījumā B5.
