Генеричка формула
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Резиме
Да бисте онемогућили унос који садржи једну од многих ствари, можете користити прилагођено правило за проверу података засновано на функцији СЕАРЦХ.
У приказаном примеру, валидација података примењена на Б5: Б11 је:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Објашњење
Правила за проверу ваљаности података покрећу се када корисник дода или промени вредност ћелије.
Ова формула користи функцију СЕАРЦХ за тестирање корисничког уноса за сваку вредност у именованом опсегу „листа“. Логика претраживања је „садржи“ - када се пронађе вредност са „листе“, СЕАРЦХ враћа положај вредности као број. Ако није пронађено, СЕАРЦХ враћа грешку.
Функција ИСНУМБЕР затим претвара бројеве у ТРУЕ, а грешке у ФАЛСЕ, а двоструки негативни оператор мења вредности ТРУЕ ФАЛСЕ у 1с и нуле. С обзиром да именовани опсег „листа“ садржи 5 вредности, вратићемо 5 резултата у низ попут овог:
(0; 0; 0; 0; 0)
СУМПРОДУЦТ затим сумира ставке у низу и резултат се тестира на нулу. Све док су све ставке нула, СУМПРОДУЦТ враћа нулу и валидација успева. Ако СУМПРОДУЦТ врати други број (тј. Када је пронађена ставка на „листи“), формула враћа ФАЛСЕ и валидација не успева.
Напомена: референце на ћелије у формулама за проверу ваљаности података односе се на горњу леву ћелију у опсегу изабраном када је дефинисано правило провере, у овом случају Б5.
