25.06.2012
Заборона значень, що повторюються, в Excel 2010
Шановні працівники «Б & К»! Допоможіть вирішити таку проблему. Є база даних у форматі 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 або на форумі редакції.