Інформація по темі "" | Публікації по тегу
21.01.13
8634 3
Друкувати
Обране

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

 

Важно! Система проверки правильности значений работает только при заполнении таблицы с клавиатуры. Вставку данных через буфер обмена она не контролирует.

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

 

Успешной работы! Жду ваших писем, предложений и замечаний на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции.

env_icon Наш сайт корисний для вас?

Отримуйте першими актуальні новини! env_icon

Підписатися на найактуальнішу розсилку для бухгалтера бюджетної установи

Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це



Коментарі
(3)
вава
20.01.2019
Спасибо!
Ібрагім
01.09.2020
Дякую велике, дуже допомогли.transparent_pxl.png
Ібрагім
01.09.2020
Дякую велике, дуже допомоли)
Залишити коментар:
Ваше ім`я
Коментарі
Шановні колеги!
Довгі роки ми наполегливо працювали, щоб вчасно підставляти плече підтримки всім українським бухгалтерам бюджетного сектору. Ми завжди розуміли, що їм нізвідки чекати допомоги й опори, тому розвивали новинний і консультативний портал власними силами та засобами. За що ви повсякчас віддячуєте нам своєю довірою та прихильністю. Ми дуже цінуємо це!

Та настають складні часи в усьому світі – й дедалі важче стає підтримувати гідний рівень роботи найбільшого в Україні порталу за свій кошт, частково покриваючи витрати завдяки рекламі. Цього вкрай замало!

Buhgalter.com.ua – це не лише стрічка новин із тематичними розділами та корисною інформацією. По той бік монітора щоденно стоять на варті роботи порталу 14 фахівців та чимале технічне забезпечення для безперешкодного функціонування сайту.

Ми сформували максимально комфортну ціну, яка б не «била по кишені», – 30 грн на місяць або 1 грн на день. Передплатники видань «Бюджетна бухгалтерія» та «Оплата праці», як і раніше, продовжують користуватися порталом безкоштовно та без реклами.

Розуміємо, для вас це стало несподіванкою, але в сьогоднішніх умовах ми не можемо діяти інакше. Цілком посильна сума, що дорівнює чашці кави у кав’ярні, підтримає життя вашого улюбленого ресурсу. За це ми віддячимо вам завжди цікавою й важливою інформацією та новими ідеями. Далі буде тільки краще!

Залишайтеся з нами – і давайте підтримувати один одного!

Ваші колеги – команда buhgalter.com.ua
30
грн/
місяц
Це приблизно усього 1 грн/день
Ви можете скасувати в будь-який момент
ic-acc-pay
Для того, чтоб распечатать текст необходимо авторизоваться или зарегистрироваться
Закрити
icon-block
Заважає реклама?
Ви маєте можливість її відключити всього за 30 грн