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

2. Щелкаем левой кнопкой мышки на заголовке колонки «A» (выделяем весь столбец).

3. Вызываем в меню «Данные». Лента примет вид, как на рис. 2.

4. В группе «Работа с данными» щелкаем на иконке «Проверка данных». Откроется окно «Проверка вводимых значений», как на рис. 3.

5. На вкладке «Параметры» щелкаем на значке выпадающего списка «Тип данных:» и выбираем вариант «Другой».

6. В поле «Формула:» вводим: «=СЧЁТЕСЛИ(A:A;A1)=1» (рис. 3).

7. Переходим на закладку «Сообщение об ошибке». В поля «Заголовок:» и «Сообщение:» вводим текст сообщения об ошибке, как показано на рис. 4. Этот текст будет появляться каждый раз при попытке ввести повторяющееся значение в столбец «A» (колонка «Таб №» в базе данных).

8. В окне «Проверка вводимых значений» нажимаем «ОК». Готово.

Теперь пару слов по поводу формулы. Рассмотрим ее работу применительно к ячейке «A1». Формула берет содержимое из «A1» и считает, сколько раз встречается это значение в пределах колонки «A». Если количество повторений превысит «1», сработает механизм проверки вводимых значений, Excel сообщит об ошибке и заблокирует ввод данных.

В данном случае принципиальны два момента. Первый — это способ адресации блока для проверки значений. Мы для этого использовали адрес колонки (выражение «A:A»). Это сделано для того, чтобы получить возможность записывать в базу новые значения вплоть до конца рабочего листа. При этом проверка дубликатов будет работать правильно.

Второй момент состоит в том, что в качестве критерия нужно указывать первую ячейку базы данных, т. е. «A1». На первый взгляд, в таком виде формула выполнит лишнюю проверку, так как в ячейке «A1» находится текст заголовка «Таб №» (рис. 1). И никаких совпадений этого текста с другими ячейками в колонке «A» не должно быть по определению. В принципе, так оно и есть. Но если в формуле в качестве критерия указать, например, «A2», то проверка повторяющихся значений будет происходить только в пределах заполненной области базы данных. А это нам не подходит.

2. В любую ячейку колонки «A» вводим повторяющееся значение табельного номера. Например, в «A3» печатаем текст «00001».

3. Нажимаем «Enter» или щелкаем на любой ячейке рабочего листа. Появится окно с предупреждением об ошибке, как на рис. 5. В данном случае мы можем отменить ввод значения или исправить его и нажать кнопку «Повторить».

Тот же эффект мы получим при попытке ввести одинаковый табельный номер в любом месте колонки «A».

При текущих настройках (окно на рис. 4) возможность ввести дубликаты полностью заблокирована, так как параметр «Вид:» установлен в положение «Останов». Это можно изменить. Например, если выбрать из списка вариант «Предупреждение» или «Сообщение» (рис. 4), то при записи повторяющихся значений система выдаст сообщение об ошибке. Но в окне на рис. 5 появятся дополнительные кнопки, чтобы разблокировать ввод данных. Таким образом, Excel предупредит о возможной ошибке, но при желании значение все же можно будет записать в базу данных. И последний момент.

Для решения такой задачи понадобится задействовать программу на языке VBA. Но это — материал для одной из следующих статей.