Общая формула
=COUNT(FIND(xlist,A1))=0
Резюме
Чтобы использовать проверку данных для ограничения знаков препинания, вы можете использовать именованный диапазон и формулу, основанную на функциях НАЙТИ и СЧЁТ. В показанном примере проверка данных, примененная к C5: C10:
=COUNT(FIND(xlist,B5))=0
где xlist - именованный диапазон D5: D11.
Объяснение
Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки. Когда настраиваемая формула возвращает значение ИСТИНА, проверка проходит и вводные данные принимаются. Когда формула возвращает FALSE, проверка не выполняется, и ввод отклоняется с появлением всплывающего сообщения.
В этом случае мы ранее определили именованный диапазон «xlist» как D5: D11. В этом диапазоне содержатся недопустимые символы.
Формула, которую мы используем для проверки данных:
=COUNT(FIND(xlist,B5))=0
Работа изнутри. Функция НАЙТИ настроена с xlist для "поиска текста" и ячейкой B5 в качестве текста для поиска. Поскольку мы даем FIND массив с несколькими значениями, FIND возвращает массив результатов, по одному для каждого символа в именованном диапазоне «xlist». Для ячейки B5 результат НАЙТИ выглядит следующим образом:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Каждая ошибка #VALUE означает, что один символ не найден. Если мы попытаемся ввести, скажем, "demolition @", который включает ограниченный символ, FIND вернет:
(#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Обратите внимание, что второе значение в массиве теперь 11.
Затем функция COUNT возвращает количество всех чисел в массиве. Когда массив не содержит чисел (т. Е. Запрещенных символов), COUNT возвращает ноль, выражение возвращает TRUE, и проверка данных завершается успешно. Однако, если массив не содержит чисел (т.е. найден хотя бы один ограниченный символ), COUNT возвращает число, выражение возвращает FALSE, и проверка данных не выполняется.
Символы, которые появляются в названном диапазоне xlist, могут быть настроены в соответствии с требованиями.
Примечание. Ссылки на ячейки в формулах проверки данных относятся к левой верхней ячейке в диапазоне, выбранном при определении правила проверки, в данном случае B5.
