Поиск и удаление дубликатов — служба поддержки Майкрософт

Excel

Введите и отформатируйте данные

Введите и отформатируйте данные

Поиск и удаление дубликатов

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Дополнительно… Меньше

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

1. Выберите ячейки, которые вы хотите проверить на наличие дубликатов.

   Примечание.  Excel не может выделить дубликаты в области значений отчета сводной таблицы.

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

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

   ​​​​​

Удалить повторяющиеся значения

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

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

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

2. Щелкните Данные > Удалить дубликаты , а затем в разделе Столбцы установите или снимите флажки для столбцов, из которых вы хотите удалить дубликаты.

   Например, на этом листе столбец января содержит информацию о ценах, которую я хочу сохранить.

   Итак, я снял флажок Январь в поле Удалить дубликаты .

3. Нажмите OK .

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

Нужна дополнительная помощь?

Пошаговое руководство по удалению дубликатов в Excel [обновлено]

Мы покажем, как удалять дубликаты в Excel с помощью набора данных Sports. Этот набор данных содержит информацию об олимпийских медалистах за 2012 год. 

Поиск и удаление дубликатов

Дублирование данных иногда полезно, но часто это просто затрудняет понимание данных. Поиск, выделение и просмотр дубликатов перед удалением лучше, чем удаление всех дубликатов сразу.

1. Выберите диапазон ячеек, содержащих повторяющиеся значения, которые необходимо удалить. Примечание: Лучший способ удалить дубликаты — это удалить все контуры или промежуточные итоги из ваших данных.
2. Выбрав Данные > Удалить дубликаты , а затем отметив или сняв флажки со столбцов, которые вы хотите очистить, вы можете удалить повторяющиеся записи.
3. Затем нажмите OK .

Как удалить повторяющиеся значения в Excel

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

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

• Шаг 2: Затем найдите параметр « Удалить дубликаты » и выберите его. Вкладка «ДАННЫЕ» → раздел «Инструменты данных» → «Удалить дубликаты»

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

Если ваши данные состоят из заголовков столбцов, выберите параметр «Мои данные имеют заголовки», а затем нажмите «ОК».

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

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

• Шаг 5. Как вы заметили, повторяющиеся записи удаляются.

Давайте двигаться вперед и понять, как удалить дубликаты в Excel с помощью параметра «Расширенный фильтр».

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

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

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

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

Фильтр уникальных значений

Выполните следующие действия для фильтрации уникальных значений:

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

Вкладка «Данные» → раздел «Сортировка и фильтр» → «Дополнительно».

Опция сортировки и фильтрации

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

• Если вам необходимо отфильтровать диапазон ячеек/таблиц на месте, щелкните Фильтровать список на месте.
• Если вам нужно скопировать результаты фильтра в другое место, примите следующие меры:
• Нажмите «Копировать в другое место», чтобы скопировать значения.
• В поле «Копировать в» введите ссылку на ячейку, в которую должны быть скопированы результирующие значения.
• Вы можете временно щелкнуть параметр «Свернуть диалоговое окно» ( ), чтобы скрыть всплывающее окно. Затем, как только вы выбрали ячейку на листе, нажмите кнопку «Развернуть» ().
• Установите флажок «Только уникальные записи» и нажмите «ОК».

Всплывающее окно расширенного фильтра

Использование опции расширенного фильтра

Параметр «Расширенный фильтр» в Excel помогает фильтровать повторяющиеся значения и копировать уникальные значения в другое место. Посмотрите следующие шаги, чтобы узнать, как работает параметр «Расширенный фильтр».

• Сначала щелкните ячейку или диапазон в наборе данных, из которого вы хотите удалить дубликаты. Если вы нажмете на одну ячейку, Excel автоматически определит диапазон, когда вы нажмете «Расширенный фильтр».

• Найдите параметр «Расширенный фильтр».

Вкладка «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Дополнительно» и нажмите на нее.

•  Появится диалоговое окно. Он состоит из списка опций для расширенной фильтрации.

• Выберите параметр «Копировать в другое место», чтобы скопировать уникальные значения в другое место.
• Проверьте диапазон ваших записей в поле «Список диапазонов» и убедитесь, что это именно тот диапазон, который вы указали.
• В поле «Копировать в:» введите диапазон, в который должны быть скопированы результирующие уникальные значения.
• Установите флажок «Только уникальные записи». Этот шаг является самым важным.
• Нажмите OK.

•  Уникальные значения будут скопированы в ячейку G1.

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

Читайте также: Лучшее руководство по расширенным функциям Excel

Как использовать формулы для удаления дубликатов в Excel?

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

Этот метод включает объединение столбцов с помощью формулы Excel и определение количества. Затем мы отфильтруем повторяющиеся значения (те, у которых количество больше 1).

• Давайте объединим столбцы A, B и C с помощью оператора конкатенации «&». Итак, формула Excel будет:

=А2&В2&С2

Эта формула вводится в ячейку D2, а затем копируется во все строки.

• Теперь нам понадобится еще один столбец с именем «Количество», чтобы найти дубликаты в столбце D. Следовательно, мы используем функцию СЧЁТЕСЛИ в ячейке E2. Формула будет:

=СЧЁТЕСЛИ($D$2:D2,D2)

Эта формула помогает подсчитать количество вхождений каждого значения в столбце D.

Если значение Count равно «1», то оно появилось только один раз и является уникальным. Если значение равно «2» или больше, то оно считается повторяющимся значением.

• Теперь добавьте фильтр в столбец «Число», выбрав параметр «Фильтр».

Вы найдете его на вкладке «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Фильтр»

Нажмите на фильтр в верхней части столбца E. Выберите «1», чтобы сохранить только уникальные значения и удалить дубликаты.

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

Давайте продолжим, разобравшись с последним подходом к удалению дубликатов: с помощью Power Query.

Проблемы с удалением дубликатов из выделенных или промежуточных данных

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

Условное форматирование уникальных или повторяющихся значений

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

1.Начните с выбора ячеек, которые вы хотите проверить на повторяющиеся значения.

2. Затем вам нужно будет найти параметр «Повторяющиеся значения» в условном форматировании на вкладке «Главная».

Вкладка «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».

3. После того, как вы нажмете на опцию Duplicate Values, появится всплывающее окно.

4. Теперь в поле рядом с параметром «значения с» выберите форматирование повторяющихся значений в раскрывающемся меню. Затем нажмите ОК.

Всплывающее окно повторяющихся значений

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

Теперь выполните следующие действия, чтобы удалить повторяющиеся значения:

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

2. Затем найдите параметр «Удалить дубликаты» и выберите его.

Вкладка «Данные» → раздел «Инструменты данных» → «Удалить дубликаты».

3. В столбцах установите или снимите флажки для столбцов, из которых вы хотите удалить дубликаты. Затем нажмите ОК.

Опция удаления дубликатов

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

Образец рабочего листа

Итак, снимите флажок «Январь» в поле «Удалить дубликаты».

Январь Опция не отмечена

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

Как использовать инструмент Power Query для удаления дубликатов в Excel?

Power Query в Excel позволяет импортировать данные из различных источников, очищать и преобразовывать данные. Этот инструмент упрощает удаление дубликатов в Excel.

• Выберите ячейку или диапазон, перейдите на вкладку «Данные» → раздел «Получить и преобразовать данные» → «Из таблицы/диапазона» и нажмите на нее.

• При щелчке вы увидите диалоговое окно для создания таблицы запросов мощности. Убедитесь, что диапазон значений указан правильно. Нажмите «ОК».

• Появится следующее окно редактора Power Query.

Теперь у вас есть два варианта. Вы можете удалить дубликаты на основе:

• Один или несколько столбцов
• Весь стол

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

Чтобы удалить повторяющиеся записи на основе всей таблицы, нажмите кнопку в верхнем левом углу предварительного просмотра данных. А затем выберите опцию «Удалить дубликаты».

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

При выборе опции «Закрыть и загрузить» данные будут загружены в вашу электронную таблицу.