Запрет ввода повторяющихся значений

Простая задача: есть диапазон ячеек (допустим А1:А10), куда пользователь вводит данные с клавиатуры. Необходимо обеспечить уникальность всех введенных значений, т.е. запретить пользователю вводить значение, если оно уже присутствует в диапазоне, т.е. было введено ранее.

Выделяем диапазон ячеек и жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В старых версиях - Excel 2003 и ранее - открываем меню Данные - Проверка (Data - Validation). На вкладке Параметры (Settings) из выпадающего списка Тип данных (Allow) выбираем вариант Другой (Custom) и вводим следующую формулу в строку Формула (Formula):

=СЧЁТЕСЛИ($A$1:$A$10;A1)<=1

или в английской версии =COUNTIF($A$1:$A$10;A1)<=1

forbid-duplicates1.png

Смысл этой формулы прост - она подсчитывает количество ячеек в диапазоне A1:A10 равных содержимому ячейки А1. Ввод будет разрешен только в те ячейки, где полученное число меньше или равно 1. Причем диапазон задан жестко (абсолютными ссылками со знаками $), а ссылка на текущую ячейку А1 - относительно. Таким образом подобная проверка будет производиться для каждой выделенной ячейки. Для полноты картины можно в этом окне перейти еще на вкладку Сообщение об ошибке (Error Alert) и ввести текст, который будет появляться при попытке ввести дубликаты:

forbid-duplicates2.png

Вот и все - жмем ОК и наслаждаемся реакцией окружающих :)

Плюс этого способа - в простоте реализации, а минус - в том, что подобную защиту легко отключить в том же диалоговом окне или скопировав и вставив в наш диапазон ячейки с дубликатами. Против лома нет приема. Для предотвращения таких террористических действий пользователя придется включать уже серьезную защиту листа с паролем и писать специальный макрос перехвата копирования. 

Но от случайного ввода дубликатов подобный способ вполне предохранит.

Ссылки по теме


Кирилл
23.10.2012 00:26:31
Вопрос: как быть если, мы постоянно добавляем строчки в диапазон, то есть ввели нового и диапазон стал А1:А11, а в формуле остался А1:А10?
23.10.2012 00:28:30
Пропишите в формуле диапазон с запасом - А1:А100, например.
12.03.2013 14:46:09
разобрался) ..скажите может что то можно придумать насчет того что бы вставлять числа скопированные с других ячеек (не заходя в них - ctrl+C/ctrl+V) и оно реагировало на повторение??
12.03.2013 20:46:41
От порчи ваших ячеек копированием и вставкой защититься можно только специальным макросом, который будет перехватывать вставку, блокировать контекстное меню и CTRL+V.
05.04.2013 10:20:33
Николай, спасибо! Ваш сайт ОЧЕНЬ помогает! Скажите, а можно ли в одном столбце задать два условия для проверки?
У меня уже стоит проверка по кол-ву знаков при вводе и надо в том же столбце исключить повторы.
11.04.2013 08:22:41
Да, можно соединить два условия в одно с помощью логической функции И. Проверяем повторы и чтобы текст был не больше 20 знаков, например:
=И(СЧЁТЕСЛИ($A$1:$A$10;A1)<=1;ДЛСТР(A1)<20)
18.04.2013 10:28:50
спасибо!!
01.08.2013 12:08:48
Николай, добрый день. Мы ведем реестр платежей, нужно, чтобы Эксель сообщал или выделял повторно предоставленные к оплате счета (Совпадает Поставщик и совпадает номер счета). Функция СЧЁТЕСЛИ хороша, но мне еще нужно добавить второе условие "совпадение текста" в графе наименование поставщика. Как это сделать? Подскажите пожалуйста :)
01.08.2013 22:27:45
Склейте номер счета и название поставщика в отдельном столбце с помощью функции СЦЕПИТЬ и делайте проверку по этому столбцу (его можно скрыть потом для маскировки).
02.08.2013 03:26:46
Получилось склеить :), а вот какой функцией сделать проверку склеенного?
02.08.2013 06:50:11
Все той же СЧЕТЕСЛИ - как в примере
19.12.2014 16:01:56
Мне кажется методологически не верно НЕ ДАВАТЬ пользователю вводить дублированные данные. Не однократно сталкивался с такой постановкой задачи и каждый раз оказывалось что эта проблема должны решаться по другому. НЕ ЗАПРЕТОМ ВВОДА.
Например задача. Необходимо создать список сотрудников и ввести их табельные номера. Понятно что табельные номера должны быть уникальны.
Мы создаем программку не дающую возможность вбить дублированный ТабНум и сажаем девочку за эту работу, Через некоторое время выясняется, что работа встала, потому, что девочка подошла к очередной фамилии, завела ее, а табельный номер, ей программы вбить не дает. она тупо смотрит в список и говорит, ну вот же, здесь написано, Иванов, номер 7534, а Ваша программа не дает его занести. И оказывается, что этот номер действительно правильный для Иванова, но ошибка была совершена ранее 50-ю десятью строчками, при вводе Сидорова, и вместо его номера 7543 он ввела 7534. Приходится искать а где же это раньше она совершила ошибку? В результате работа по вводу (срочная между прочим) останавливается и начинаются судорожные поиски. Поэтому общий принцип такой: Если вы посадили человека (часто малоопытного) за вбивание данных - разрешите ему вбивать все. Ошибки найдете позднее и его данные "причешете". Вывод: Если Вы профессионально занимаетесь созданием больших списков - время дороже контроля ошибок.