Запрет повторяющихся значений в Excel 2010
1. Открываем Excel 2010, загружаем документ (рис. 1).
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», то проверка повторяющихся значений будет происходить только в пределах заполненной области базы данных. А это нам не подходит.
С формулой мы разобрались. Пора посмотреть, как это выглядит на практике. Делаем так.
1. Открываем базу данных, как на рис. 1.
2. В любую ячейку колонки «A» вводим повторяющееся значение табельного номера. Например, в «A3» печатаем текст «00001».
3. Нажимаем «Enter» или щелкаем на любой ячейке рабочего листа. Появится окно с предупреждением об ошибке, как на рис. 5. В данном случае мы можем отменить ввод значения или исправить его и нажать кнопку «Повторить».
Тот же эффект мы получим при попытке ввести одинаковый табельный номер в любом месте колонки «A».
При текущих настройках (окно на рис. 4) возможность ввести дубликаты полностью заблокирована, так как параметр «Вид:» установлен в положение «Останов». Это можно изменить. Например, если выбрать из списка вариант «Предупреждение» или «Сообщение» (рис. 4), то при записи повторяющихся значений система выдаст сообщение об ошибке. Но в окне на рис. 5 появятся дополнительные кнопки, чтобы разблокировать ввод данных. Таким образом, Excel предупредит о возможной ошибке, но при желании значение все же можно будет записать в базу данных. И последний момент.
Важно! Система проверки правильности значений работает только при заполнении таблицы с клавиатуры. Вставку данных через буфер обмена она не контролирует.
Для решения такой задачи понадобится задействовать программу на языке VBA. Но это — материал для одной из следующих статей.
Успешной работы! Жду ваших писем, предложений и замечаний на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции.