Як уникнути появи дублікатів в стовпці на аркуші Excel

Сьогодні я розповім Вам про те, як уникнути появи дублікатів в стовпці даних на аркуші Excel. Цей прийом працює в Microsoft Excel 2013, 2010 2007 і попередніх версіях.

Ми вже торкалися цієї теми в одній зі статей. Тому, можливо, Ви вже знаєте, як в Excel зробити так, щоб введені повторно дані автоматично виділялися кольором в процесі введення.

З цієї статті Ви дізнаєтесь, як запобігти появі дублікатів в одному або декількох стовпчиках на аркуші Excel. Як зробити так, щоб в першому стовпчику таблиці були тільки унікальні дані, що з’являються лише одного разу, наприклад, номери рахунків-фактур, номенклатурні записи або дати.

Як запобігти появі дублікатів – 5 простих кроків

В Excel є інструмент, про який часто незаслужено забувають – «Перевірка даних». З його допомогою можна уникнути помилок, що виникають при введенні. Пізніше ми обов’язково присвятимо окрему статтю цій корисній інструменту. А поки, для розігріву, покажемо його роботу на простому прикладі

Припустимо, у нас є таблиця даних з інформацією про клієнтів, що складається з стовпців з іменами (Name), телефонними номерами (Phone) і адресами електронної пошти (e-mail). Нам потрібно, щоб адреси електронної пошти не повторювалися. Наступні кроки допоможуть уникнути повторної відправки листа на один і той же адресу.

  1. Переглядаємо таблицю і, якщо необхідно, видаляємо всі повторювані записи. Для цього спочатку виділимо дублікати кольором, а потім, перевіривши всі значення, видалимо їх вручну.
  2. Виділяємо весь стовпець, в якому хочемо уникнути появи дублікатів. Для цього клацаємо мишею по першій клітинці з даними і, утримуючи клавішу Shift утримуючи, клацаємо по останній комірці. Якщо цей стовпець крайній в таблиці, як в нашому випадку, то можемо використовувати комбінацію клавіш Ctrl + Shift + End. Найголовніше спочатку виділіть саме перший осередок з даними.

зауваження: Якщо дані оформлені, як звичайний діапазон, а не як повноцінна таблиця Excel, то необхідно виділити всі комірки стовпчика, в тому числі порожні. У нашому прикладі це буде діапазон D2: D1048576.

  1. Відкрийте вкладку дані (Data) і клацніть по іконці Перевірка даних (Data Validation), щоб викликати діалогове вікно Перевірка вводятьсязначень (Data Validation).
  2. на вкладці параметри (Settings) у випадаючому списку Тип даних (Allow) виберіть інший (Custom) і в полі Формула (Formula) введіть такий вислів:

    = СЧЁТЕСЛІ ($ D: $ D; D2) = 1
    = COUNTIF ($ D: $ D, D2) = 1

    тут $ D: $ D – це адреси першої і останньої клітинки в стовпці. Зверніть увагу, що ми використовували знак долара, щоб записати абсолютне посилання. D2 – це адреса першої виділеної комірки стовпчика, це не абсолютне посилання.

    Ця формула підраховує кількість повторень значення осередки D2 у діапазоні D1: D1048576. Якщо це значення зустрічається в заданому діапазоні тільки одного разу, тоді все в порядку. Якщо значення зустрічається кілька разів, то Excel покаже повідомлення, текст якого ми запишемо на вкладці Повідомлення про помилку (Error Alert).

Підказка: Ми можемо шукати повторювані значення, записані не тільки в поточному, а й в іншому стовпці. Цей стовпець може перебувати на іншому аркуші або навіть в іншій робочій книзі. Таким чином, вводячи електронні адреси в стовпець, ми можемо порівнювати їх з адресами, які занесені в чорний список і з якими вирішено припинити співпрацю. Я розповім докладніше про таке застосування інструменту «Перевірка даних»В одній з майбутніх статей.

  1. відкриваємо вкладку Повідомлення про помилку (Error Alert) і заповнюємо поля Заголовок (Title) і Повідомлення (Error message). Саме це повідомлення буде показано в Excel при спробі ввести повторюється значення в стовпець. Постарайтеся доступно пояснювати в своєму повідомленні деталі помилки, щоб Вам і Вашим колегам було зрозуміло в чому причина. Інакше після тривалого часу, наприклад, через місяць, Ви можете забути, що означає дане сообщеніе.Напрімер, так:Заголовок: Повторюваний email.

    Повідомлення: Введений Вами адресу email вже використовується в даному стовпці. Допускається вводити тільки унікальні адреси email.

  2. натисніть ОК, щоб закрити діалогове вікно Перевірка вводятьсязначень (Data validation).

Тепер при спробі ввести в стовпець e-mail адресу, яку в ньому вже існує, буде показано створене нами повідомлення про помилку. Це спрацює, як при створенні запису e-mail для нового клієнта, так і при спробі змінити e-mail існуючого клієнта:

Якщо наша боротьба з дублікатами допускає виключення

На кроці 4 в випадаючому списку вид (Style) вибираємо попередження (Warning) або Повідомлення (Information). Поведінка повідомлення про помилку зміниться наступним чином:

попередження: У діалоговому вікні буде запропоновано набір кнопок Так (Yes) / немає (No) / Скасувати (Cancel). якщо натиснути Так (Yes), то введене значення буде додано в клітинку. Щоб повернутися до редагування осередку, натисніть немає (No) або Скасувати (Cancel). За замовчуванням активна кнопка немає (No).

Повідомлення: У діалоговому вікні буде запропоновано натиснути кнопку ОК або Скасувати (Cancel). За замовчуванням активна кнопка ОК – введене повторюється значення залишиться в осередку. Якщо хочете змінити дані в осередку, натисніть Скасувати (Cancel), щоб повернутися до редагування.

зауваження: Хочу ще раз звернути Вашу увагу на ту обставину, що повідомлення про появу повторюваного значення буде показано тільки при спробі ввести це значення в клітинку. інструмент «Перевірка даних»В Excel не виявить дублікати серед уже введених записів, навіть якщо цих дублікатів сотні!

Ссылка на основную публикацию