Общая формула
=COUNTIF(list,A1)=0
Резюме
Примечание. В Excel есть встроенные правила проверки данных для раскрывающихся списков. На этой странице объясняется, как создать собственное правило проверки, если вы хотите * запретить * пользователю вводить значение в список.
Чтобы разрешить только значения, которых нет в списке, вы можете использовать проверку данных с помощью настраиваемой формулы на основе функции СЧЁТЕСЛИ. В показанном примере проверка данных, примененная к B5: B9:
=COUNTIF(list,B5)=0
где «список» - именованный диапазон D5: D7.
Объяснение
Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки.
В этом случае функция СЧЁТЕСЛИ является частью выражения, которое возвращает ИСТИНА, если значение не существует в определенном списке. Функция СЧЁТЕСЛИ просто подсчитывает вхождения значения в списке. Пока счетчик равен нулю, запись будет проходить проверку. Если счетчик не равен нулю (т.е. пользователь ввел значение из списка), проверка не удастся.
Примечание. Ссылки на ячейки в формулах проверки данных относятся к левой верхней ячейке в диапазоне, выбранном при определении правила проверки, в данном случае B5.
