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

Проверено и проверено Сайантони Дасом

Microsoft Excel — широко используемое приложение, особенно в бизнес-анализе, но оно может быть немного запутанным, когда дело доходит до удаления и устранения дубликатов данных. Удаление дубликатов в Excel — распространенная задача для людей, работающих с огромными наборами данных. Когда вы объединяете разные таблицы или когда много людей имеют доступ к одному и тому же документу, в вашей электронной таблице могут оказаться повторяющиеся записи. Таким образом, данные становятся избыточными. Чем больше набор данных, тем выше вероятность обнаружения дубликатов записей. Это может быть проблематично, если они не идентифицированы и не обработаны правильно.

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Найти и удалить дубликаты

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтр по уникальным значениям

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

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

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

Сортировка и фильтрация

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

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

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

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

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

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

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

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

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

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

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

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

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

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

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

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

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

=А2&В2&С2

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

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

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

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

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

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

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

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

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

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

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

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

статья>

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

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

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

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

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

3. После нажатия на опцию «Повторяющиеся значения» появится всплывающее окно.

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

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

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

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

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

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

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

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

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

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

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

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

Вариант января не отмечен

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

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

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

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

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

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

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

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

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

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

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

Заключение

В этой статье мы изучили различные подходы к удалению дубликатов записей. Если вы заинтересованы в изучении основ Excel или хотите развить более продвинутые навыки работы с Microsoft Excel, Simplilearn предлагает вам курс Business Analytics For Strategic Decision Making. Это идеальный курс, который поможет вам начать путь к карьере бизнес-аналитика.

Пожалуйста, не стесняйтесь задавать любые вопросы в разделе комментариев к статье «Как удалить дубликаты в Excel?: Пошаговое руководство». Наши эксперты ответят вам по этому вопросу в кратчайшие сроки.