Обща формула
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Обобщение
За да забраните въвеждането, което съдържа едно от много неща, можете да използвате персонализирано правило за проверка на данни, основаващо се на функцията SEARCH.
В показания пример валидирането на данните, приложено към B5: B11, е:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Обяснение
Правилата за проверка на данните се задействат, когато потребителят добави или промени стойността на клетката.
Тази формула използва функцията SEARCH, за да тества потребителски вход за всяка стойност в посочения диапазон "списък". Логиката за търсене е "съдържа" - когато се намери стойност от "списък", SEARCH връща позицията на стойността като число. Ако не бъде намерен, SEARCH връща грешка.
След това функцията ISNUMBER преобразува числата в TRUE и грешките в FALSE, а двойният отрицателен оператор променя стойностите на TRUE FALSE на 1s и нули. Тъй като посоченият диапазон "списък" съдържа 5 стойности, получаваме 5 резултата в масив като този:
(0; 0; 0; 0; 0)
След това SUMPRODUCT обобщава елементите в масива и резултатът се тества срещу нула. Докато всички елементи са нула, SUMPRODUCT връща нула и проверката е успешна. Ако SUMPRODUCT връща друг номер (т.е. когато е намерен елемент в "списък"), формулата връща FALSE и проверката е неуспешна.
Забележка: Препратките към клетки във формулите за валидиране на данни са спрямо горната лява клетка в избрания диапазон, когато е дефинирано правилото за валидиране, в този случай B5.
