Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

1.     Удаление повторяющихся значений в Excel (2007+)

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

удалить-дубликаты excel

Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

удалить-дубликаты excel

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2.     Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

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

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

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

3.     Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

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

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

4.     Использование сводных таблиц для определения повторяющихся значений

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

сводная таблица excel

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

сводная таблица excel


40 комментариев

  1. Pingback: Как сравнить два столбца в Excel — методы сравнения данных Excel « Blog — organiser

  2. Отличная статья. Спасибо. Всегда испытывал много трудностей с Экселем. Многие задачи занимали по много времени. Советы описанные в статье помогут сократить трудозатраты, что очень классно.

  3. Мне нужно отформатировать повторяющиеся значения но не по полным значениям а по первым 10 элементам например есть CZE21055A2 Зырянов и есть CZE21055A2 Иванов так вот мне надо чтобы форматирование произошло по первым 10 элементам и выделило цветом повторяющиеся
    CZE21055A2 Зырянов
    CZE21055A2 Иванов в итоге должно показаться что это повторяющиеся значения цветом

  4. помогите плиз как выделить одинаковые гиперссылки? Гиперссылки могут называться по разному, но и ссылки разные. задача найти одинаковые ссылки в гиперссылках.

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

    Файл:  1100.xlsx

  6. Здравствуйте!Подскажите пожалуйста,как после выделения дубликатов цветом,убрать эту функцию?Т.е я проверила,и нужно вернуть исходный вид

  7. Доброе время суток!Подскажите, как с помощью формулы можно удалить повторы, если значений названий столбцов аж 30. А строк больше 30 000…
    с помощью сводной таблицы, как я понимаю, нереально все 30 000 проставить 1, чтоб потом проверить.
    Помогите найти рещение….Как отфильтровать эту базу данных на повторы?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *