Общая формула
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Резюме
Чтобы запретить ввод, который содержит одну из многих вещей, вы можете использовать настраиваемое правило проверки данных на основе функции ПОИСК.
В показанном примере проверка данных, примененная к B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Объяснение
Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки.
Эта формула использует функцию ПОИСК, чтобы проверить вводимые пользователем значения для каждого значения в именованном диапазоне «список». Логика поиска: «содержит» - когда найдено значение из «списка», ПОИСК возвращает позицию значения в виде числа. Если не найден, ПОИСК возвращает ошибку.
Затем функция ЕЧИСЛО преобразует числа в ИСТИНА, а ошибки в ЛОЖЬ, а оператор двойного отрицания изменяет значения ИСТИНА ЛОЖЬ на единицы и нули. Поскольку именованный диапазон "list" содержит 5 значений, мы возвращаем 5 результатов в виде такого массива:
(0; 0; 0; 0; 0)
SUMPRODUCT затем суммирует элементы в массиве, и результат сравнивается с нулем. Пока все элементы равны нулю, СУММПРОИЗВ возвращает ноль и проверка завершается успешно. Если СУММПРОИЗВ возвращает другое число (т.е. когда элемент в «списке» найден), формула возвращает ЛОЖЬ, и проверка не выполняется.
Примечание. Ссылки на ячейки в формулах проверки данных относятся к левой верхней ячейке в диапазоне, выбранном при определении правила проверки, в данном случае B5.
