Generička formula
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Sažetak
Da biste onemogućili unos koji sadrži jednu od mnogih stvari, možete upotrijebiti prilagođeno pravilo za provjeru podataka zasnovano na funkciji SEARCH.
U prikazanom primjeru, provjera valjanosti podataka primijenjena na B5: B11 je:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Obrazloženje
Pravila za provjeru valjanosti podataka pokreću se kada korisnik doda ili promijeni vrijednost ćelije.
Ova formula koristi funkciju SEARCH za testiranje korisničkog unosa za svaku vrijednost u imenovanom rasponu "popis". Logika pretraživanja je "sadrži" - kada se pronađe vrijednost sa "popisa", SEARCH vraća položaj vrijednosti kao broj. Ako nije pronađeno, SEARCH vraća pogrešku.
Funkcija ISNUMBER tada pretvara brojeve u TRUE, a pogreške u FALSE, a dvostruki negativni operator mijenja TRUE FALSE vrijednosti u 1s i nule. Budući da imenovani raspon "popis" sadrži 5 vrijednosti, vratit ćemo 5 rezultata u niz poput ovog:
(0; 0; 0; 0; 0)
SUMPRODUCT zatim sažima stavke u polju i rezultat se testira na nuli. Sve dok su sve stavke nula, SUMPRODUCT vraća nulu i provjera valjanosti uspijeva. Ako SUMPRODUCT vrati drugi broj (tj. Kada se pronađe stavka na "popisu"), formula vraća FALSE i provjera valjanosti ne uspijeva.
Napomena: Reference ćelija u formulama za provjeru valjanosti podataka su u odnosu na gornju lijevu ćeliju u rasponu odabranom kada je definirano pravilo provjere valjanosti, u ovom slučaju B5.
