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

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

Уважаемые сотрудники «Б & K»! Помогите решить такую проблему. Есть база данных в формате MS Excel. Известно, что в определенной колонке этой базы должны находиться уникальные значения. Нужно сделать так, чтобы при ее заполнении Excel автоматически проверял эту колонку. И в случае, когда введенное значение уже есть в базе данных, — выдавал сообщение об ошибке. Работаю с программой Excel 2010. Спасибо.

Кирилл Воеводин, главный бухгалтер, г. Харьков.

Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

В Excel есть разные способы решения этой проблемы. Самый простой — воспользоваться встроенной системой контроля правильности значений. Предлагаю применить ее к таблице, фрагмент которой показан на рис. 1. В этой базе есть перечень сотрудников, их табельные номера и сумма выручки. И сейчас мы сделаем так, чтобы при пополнении списка Excel проверял значения в колонке «Таб №» и, если в ней будут одинаковые значения, — предупредил нас об этом. Делаем так.

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 предупредит о возможной ошибке, но при желании значение все же можно будет записать в базу данных. И последний момент.