Заборона значень, що повторюються, в Excel 2010

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