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

Обновлено: 03.07.2024

Для людей, которые работают с большими наборами данных в Excel или с базами данных, одной из наиболее распространенных задач проверки ошибок является удаление повторяющихся значений. Некоторые люди называют этот процесс дедупликацией. Основная причина, по которой это необходимо в наборах данных, заключается в том, что каждый раз, когда вы создаете поле первичного ключа, вы можете иметь только уникальные значения в этом столбце. Таким образом, любое повторяющееся значение является ошибкой.

Повторяющиеся значения могут появляться в вашем наборе данных по нескольким причинам. Таким образом, всегда разумно проверять наличие этой ошибки в любых данных, которые вы получаете от другого лица. Всякий раз, когда вы хотите проверить наличие повторяющихся записей, обязательно отметьте, какие столбцы должны иметь только уникальные значения. Затем используйте один из пяти методов, описанных ниже, для выявления и удаления дубликатов. Обратите внимание на одну важную вещь:

Каждый метод удаления дубликатов Excel немного отличается, и у каждого есть свои плюсы и минусы.

Поэтому всегда учитывайте контекст ситуации, прежде чем выбирать свой подход.

Функция удаления повторяющихся данных

Вам нужно быстро удалить записи или целые строки данных из таблицы.
Вы можете автоматически удалять дубликаты, не видя, какие записи повторяются
У вас очень большой набор, который сложно визуально очистить.

Excel 2010 имеет мощную функцию работы с данными, которая позволяет просто выбирать набор данных и удалять повторяющиеся строки. Подход с использованием функции данных, вероятно, является самым простым и быстрым способом удаления повторяющихся значений в Excel.

Ключевым недостатком использования функции данных является то, что она не позволяет провести какой-либо анализ перед удалением дубликатов. Это может быть рискованно, если вы хотите визуально подтвердить, что ваши данные ошибочны, прежде чем избавиться от них. Поскольку функция автоматически удаляет дубликаты, когда вы нажимаете кнопку ввода, вы не сможете быстро проверить свои данные, пока они не исчезнут.

Чтобы использовать эту функцию, просто выберите нужный набор данных. Затем перейдите к заголовку «Данные» на ленте Excel и нажмите кнопку «Удалить дубликаты».

В выбранном наборе данных функция спросит, какие столбцы должны быть уникальными. Если вы выберете только один столбец, Excel удалит любую строку, в которой есть повторяющиеся значения в этом столбце, независимо от значений в любом другом столбце.

Если вы выберете несколько столбцов для проверки, Excel удалит только те строки, которые имеют одинаковые значения для всех выбранных столбцов. В этой ситуации, если предположить, что два значения в поле Уникальный идентификатор дублируются, пока одно из других выбранных полей отличается, Excel не удалит эту строку. Проверка всех столбцов является лучшим подходом, если вы хотите удалить только те строки, которые являются точной копией другой строки. Затем вы можете использовать один из других методов, описанных ниже, для исследования любых дополнительных дубликатов в поле «Уникальный идентификатор».

После завершения процесса функция данных сообщает, сколько дубликатов было выявлено и удалено.

Условное форматирование

У вас есть набор данных, который достаточно мал для визуальной очистки.
Вы хотите визуально определить, проанализировать и подтвердить дубликаты перед их удалением.
Ваши данные достаточно сложны, поэтому выделение дубликатов поможет их идентифицировать.

Одной из наиболее полезных функций условного форматирования в Excel является возможность выделения повторяющихся значений. Чтобы использовать эту функцию, просто выделите нужные данные. В нашем примере набора данных, поскольку нас интересуют только повторяющиеся значения в поле «Уникальный идентификатор», мы выделим этот конкретный столбец. Затем на вкладке "Главная" ленты Excel нажмите:

Условное форматирование
Правила выделения ячеек
Повторяющиеся значения

Если вы используете настройку по умолчанию, ваши ячейки будут выделены красным цветом.Поскольку условное форматирование не удаляет дубликаты автоматически, вам придется удалять их вручную. Из-за этого ограничения метод условного форматирования в первую очередь удобен для небольших наборов данных, когда вы можете достаточно просмотреть весь набор данных, чтобы внести изменения.

Сводная таблица

Вы создаете отдельный список уникальных значений.
Вы хотите сравнить количество уникальных значений с исходным набором данных, чтобы увидеть, существуют ли дубликаты.
Вы хотите выполнить сведение данных, используя только уникальные значения.

Подход со сводной таблицей лучше всего подходит, если вы хотите быстро создать уникальный список значений для одного столбца. После того как вы это сделаете, вы сможете легко увидеть, существуют ли дубликаты, сравнив количество уникальных списков с количеством исходного столбца данных.

Вариант сводной таблицы также удобен, если у вас есть ситуация, когда повторяющиеся значения не обязательно являются неправильными, вам просто нужно выполнить сведение всех существующих повторяющихся значений. Например, вы можете работать с базой данных, в которой повторяющиеся записи фактически представляют собой отдельные транзакции, и их необходимо суммировать, а не очищать.

Важно помнить, что сводная таблица фактически создает совершенно новый набор данных. Поэтому, если вам действительно нужно внести изменения в исходный набор данных, имеет смысл использовать один из других методов.

Чтобы вставить сводную таблицу, выберите нужный набор данных. Затем перейдите на вкладку «Вставка» на ленте Excel и нажмите «Сводная таблица».

Кроме того, вы можете использовать сочетание клавиш: ALT + D + P + F

После того как вы создали свою сводную таблицу, просто возьмите имя поля столбца, из которого вы хотите удалить дубликаты, и перетащите его в поле "Метки строк". После этого появится уникальный список значений из этого столбца.

Если вам нужно свести данные на основе этого нового списка уникальных значений, просто используйте формулу СУММЕСЛИ, чтобы просуммировать конкретные значения из повторяющихся записей.

Сортировка данных

У вас есть набор данных, который достаточно мал для визуальной очистки.
Вы хотите визуально определить, проанализировать и подтвердить дубликаты перед их удалением.
Ваши данные достаточно просты, чтобы вы могли легко различать значения

Сортировка таблицы данных — один из самых быстрых способов найти дубликаты в Excel. Предполагая, что ваш набор данных небольшой и имеет простые значения, простая сортировка должна позволить вам увидеть, существуют ли какие-либо дубликаты.

Однако, если в вашем столбце есть сложные значения, например десятизначный буквенно-цифровой код, вам будет очень сложно увидеть повторяющиеся значения визуально. В этом случае следует использовать условное форматирование.

Чтобы отсортировать данные, перейдите на вкладку "Данные" на ленте Excel и нажмите любую из кнопок сортировки.

Кроме того, чтобы использовать функцию быстрого доступа, вы можете просто выбрать нужный столбец и ввести:

ALT + A + S + D (для сортировки по убыванию)
ALT + A + S + A (для сортировки по возрастанию)

Расширенный фильтр

Вы хотите видеть только уникальные записи в своем наборе данных
Вы не хотите удалять какие-либо записи и предпочитаете просто скрыть их.

Расширенный фильтр работает, скрывая записи, которые совпадают с любыми другими записями в вашем наборе данных. Обратите внимание, что уникальность в данном случае определяется записью, целой строкой данных, а не отдельным значением. Поэтому расширенный фильтр будет отфильтровывать строку, только если вся строка дублируется.

Из-за своего определения уникальности использование функции расширенного фильтра имеет ключевой недостаток: она не обязательно будет отфильтровывать повторяющиеся значения в столбце первичного ключа. Если у вас есть таблица, в которой две записи имеют одинаковый уникальный идентификатор, пока одно из других полей отличается, ни одна запись не будет отфильтрована. Еще один недостаток использования этого метода заключается в том, что при большом наборе данных может быть трудно увидеть, были ли какие-либо записи отфильтрованы.

Чтобы использовать функцию расширенного фильтра, выберите свой набор данных и перейдите к заголовку «Данные» на ленте Excel.В разделе "Сортировка и фильтр" нажмите кнопку "Дополнительно" рядом с большой кнопкой фильтра.

Появится всплывающее окно с опцией в самом низу, позволяющей вам отфильтровать, чтобы вы видели только уникальные записи. Установите флажок и нажмите OK.

Предположим, у нас есть некоторые данные для списка сотрудников, которые будут постоянно обновляться, и мы хотим убедиться, что любой пользователь, добавляющий в список, не добавляет повторяющуюся запись. В этом посте мы узнаем, как использовать проверку данных, чтобы пользователь не мог ввести значение, если оно уже было введено в таблицу.

В нашем примере у нас есть простой набор данных, который содержит поле для идентификатора сотрудника, имени и адреса, и мы предотвратим ввод дубликатов идентификаторов сотрудников. Наши данные также были преобразованы в таблицу, и эта таблица была названа EmployeeData. Если вы хотите узнать, как создать таблицу в Excel, прочитайте это Введение в таблицы Excel .

Добавьте настраиваемые критерии проверки. Это позволит нам написать формулу для проверки правильности данных или нет. В этом случае мы введем формулу, чтобы подсчитать, сколько раз значение появляется в списке, и ограничим ее до 1.

Выберите полный столбец данных, который мы собираемся ограничить уникальными значениями.
Перейдите на вкладку "Данные".
Нажмите кнопку "Проверка данных".
Выберите «Проверка данных» в раскрывающемся меню.
Выберите вкладку "Настройки".
В раскрывающемся списке Разрешить выберите "Пользовательский".
В области Формула введите эту формулу.

Мы также можем добавить предупреждающее сообщение для пользователей, которое будет отображаться, когда курсор активной ячейки находится на ячейке с этой проверкой данных.

Перейдите на вкладку "Входящее сообщение".
Введите заголовок входящего сообщения.
Введите описательное сообщение, которое будет предупреждать пользователя о проверке данных на наличие уникальных значений.
Когда активная ячейка находится в диапазоне с этой проверкой данных уникального значения, будет отображаться входное сообщение.

Мы также можем добавить сообщение об ошибке, которое будет отображаться, если пользователь попытается ввести повторяющееся значение.

Перейдите на вкладку "Оповещение об ошибке".
Выберите стиль всплывающего сообщения: «Стоп», «Предупреждение» или «Информация».
Введите заголовок всплывающего сообщения.
Введите описательное сообщение, которое предупредит пользователя об ошибке.
Нажмите кнопку ОК.
Теперь, если вы попытаетесь ввести повторяющееся значение, появится всплывающее окно с предупреждением об ошибке ввода повторяющегося значения.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Мы рассмотрели похожую тему в одной из наших предыдущих статей. Поэтому вы должны знать, как автоматически выделять дубликаты в Excel после ввода текста.

Эта статья поможет вам предотвратить появление дубликатов в одном или нескольких столбцах на листе Excel. Таким образом, вы можете иметь только уникальные данные в 1-м столбце вашей таблицы, будь то номера счетов, единицы хранения или даты, каждая из которых упоминается только один раз.

Как остановить дублирование — 5 простых шагов

В Excel есть средство проверки данных — незаслуженно забытый инструмент. С его помощью вы сможете избежать ошибок, возникающих в ваших записях. Мы обязательно посвятим несколько будущих статей этой полезной функции. А теперь в качестве разминки вы увидите простой пример использования этой опции. :)

Предположим, у вас есть рабочий лист "Клиенты", содержащий такие столбцы, как "Имена", "Номера телефонов" и "Электронная почта", которые вы используете для рассылки информационных бюллетеней. Таким образом, все адреса электронной почты должны быть уникальными. Следуйте приведенным ниже инструкциям, чтобы избежать отправки одного и того же сообщения одному клиенту дважды.

При необходимости найдите и удалите все дубликаты из таблицы. Вы можете сначала выделить дубликаты и удалить их вручную после просмотра значений. Или вы можете удалить все дубликаты с помощью надстройки Duplicate Remover.
Выберите весь столбец, в котором нужно избежать дублирования. Нажмите на первую ячейку с данными, удерживая кнопку Shift на клавиатуре, а затем выберите последнюю ячейку. Или просто используйте комбинацию Ctrl+Shift+End. Важно сначала выбрать 1-ю ячейку данных.

Примечание. Если ваши данные находятся в простом диапазоне Excel, а не в полноценной таблице Excel, вам нужно выбрать все ячейки в столбце, даже пустые, от D2 до D1048576

Здесь $D:$D — адреса первой и последней ячеек в вашем столбце. Пожалуйста, обратите внимание на знаки доллара, которые используются для обозначения абсолютной ссылки. D2 — это адрес первой выбранной ячейки, а не абсолютная ссылка.

С помощью этой формулы Excel подсчитывает количество вхождений значения D2 в диапазоне D1:D1048576. Если это упоминается только один раз, то все в порядке. Если одно и то же значение появляется несколько раз, Excel покажет предупреждающее сообщение с текстом, который вы указали на вкладке «Предупреждение об ошибке».

Совет. Вы можете сравнить свой столбец с другим столбцом, чтобы найти дубликаты. Второй столбец может находиться на другом листе или в книге событий. Например, вы можете сравнить текущий столбец с тем, который содержит адреса электронной почты клиентов из черного списка,
с которыми вы больше не будете работать. :) Я дам более подробную информацию об этой опции проверки данных в одном из моих будущих сообщений.

Если в вашем правиле "Дубликаты не допускаются" могут быть исключения :)

На четвертом шаге выберите Предупреждение или Информация в списке меню Стиль. Поведение предупреждающего сообщения изменится соответствующим образом:

Внимание! Кнопки в диалоговом окне поменяются как Да / Нет / Отмена. Если вы нажмете Да, введенное вами значение будет добавлено. Нажмите Нет или Отмена, чтобы вернуться к редактированию ячейки. Нет — кнопка по умолчанию.

Информация: Кнопки в предупреждающем сообщении будут "ОК" и "Отмена". Если вы нажмете Ok (по умолчанию), будет добавлен дубликат. Отмена вернет вас в режим редактирования.

Примечание: Еще раз хочу обратить ваше внимание на то, что оповещение о повторяющейся записи будет появляться только при попытке ввести значение в ячейку. Excel не будет находить существующие дубликаты при настройке средства проверки данных. Этого не произойдет, даже если в вашей колонке более 150 дубликатов. :).

Иногда при редактировании листа Excel может потребоваться обеспечить уникальность данных в столбце, а именно предотвратить дублирование значений в столбце. Это требует, чтобы Excel автоматически предупреждал вас каждый раз, когда вы вводите повторяющиеся значения в столбце. После этого мы поделимся с вами двумя быстрыми способами его получить.

Способ 1: предотвратить с помощью «проверки данных»

Сначала выделите все ячейки в определенном столбце, например "Столбец А".
Затем перейдите на вкладку "Данные" и нажмите кнопку "Проверка данных" в группе "Инструменты данных".
Далее во всплывающем диалоговом окне на вкладке "Настройки" выберите вариант "Пользовательский" в раскрывающемся списке "Разрешить".
После этого введите следующую формулу в поле "Формула".

Позже перейдите на вкладку "Предупреждение об ошибке" в текущем диалоговом окне.
Впоследствии вы можете настроить сообщение об ошибке в соответствии со своими потребностями, например, указав «Повторяющиеся значения» в качестве заголовка ошибки и «Значение было введено в тот же столбец!» как сообщение об ошибке.
Наконец нажмите "ОК".
Теперь вы можете попробовать.

Во-первых, введите значение в ячейку A1.
Затем введите то же значение в ячейку A2.
Вы сразу же получите сообщение об ошибке, как показано на следующем снимке экрана.

Способ 2. Предотвращение с помощью Excel VBA

Для начала запустите редактор Excel VBA со ссылкой на «Как запустить код VBA в вашем Excel».
Затем поместите следующий код в проект листа, например "Лист 1 (Лист 1)"

С тех пор каждый раз, когда вы вводите значение в любые ячейки, независимо от столбцов, макрос будет автоматически проверять другие значения в том же столбце. Если он найдет одинаковые значения, вы получите предупреждение, как показано на следующем рисунке.

Сравнение

Преимущества	Недостатки
Метод 1	Простота в эксплуатации	Может отслеживать и работать только с данными в определенном столбце
Метод 2	Может отслеживать данные во всех столбцах и работать с ними	Увеличивает риск атаки вирусов или вредоносных программ

Вернуть ценные данные Excel

По общему признанию, Excel подвержен сбоям. И каждый раз, когда он выходит из строя, открытый в данный момент файл Excel может быть поврежден как ветер. Следовательно, чтобы избежать потери ценных данных Excel, вы должны не только регулярно создавать их резервные копии, но и иметь замечательный инструмент восстановления xls, такой как DataNumen Excel Repair. Это пригодится в случае серьезного повреждения данных Excel.

Читайте также: