Как удалить дубликаты в Microsoft Excel — инструкция

Excel

Как определить дубликаты в таблице

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

  1. Выберите область с данными и на вкладке «Главная» вызовите меню «Условное форматирование».Вызовите меню условного форматирования, чтобы найти дубликаты в Microsoft Excel
  2. Наведите указатель мыши на список правил «Правила выделения ячеек» и выберите вариант из списка «Повторяющиеся значения».Выбор правила условного форматирования для поиска дубликатов в Microsoft Excel
  3. Вы можете изменить цвет выделения, но цвет по умолчанию. Просто подтвердите действие.Проверяет правило условного форматирования для поиска дубликатов в Microsoft Excel
  4. Обратите внимание, что ячейки с дубликатами выделяются, а уникальные значения остаются невыделенными. Теперь вы точно знаете, какие данные дублируются и можно ли их удалить.Результат использования условного форматирования для поиска дубликатов в Microsoft Excel

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

Читайте также: Как разделить ячейку в excel по диагонали и разбить столбец на два

Удаление дубликатов в таблице Excel

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

  1. Выберите необходимые ячейки, перейдите на вкладку «Данные» и разверните меню «Работа с данными».Перейдите в меню управления данными, чтобы удалить дубликаты в Microsoft Excel
  2. Нажмите «Удалить дубликаты».Вызывает возможность удаления дубликатов в Microsoft Excel
  3. Вы выбрали только один столбец, поэтому в появившемся окне просто нажмите «ОК».Подтверждение действия для дубликатов в Microsoft Excel
  4. Отобразится уведомление с информацией о найденных и удаленных повторяющихся значениях. Затем он покажет, сколько уникальных значений осталось.Информация после удаления дубликатов в Microsoft Excel
  5. Изюминка условного форматирования исчезла, то есть повторы удалены, и остались только уникальные строки.Просмотр результатов дедупликации в Microsoft Excel

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

  1. Вы видите два столбца с днями недели с надписью «Рабочий» или «Нерабочий день». Некоторые значения полностью повторяются, например «Понедельник — рабочий», но есть и «Понедельник — нерабочий». Поэтому удаление дубликатов будет работать только в первом случае.Просмотр двух столбцов перед удалением дубликатов в Microsoft Excel
  2. Выберите два столбца и нажмите кнопку «Удалить дубликаты».Выбор двух столбцов для удаления дубликатов в Microsoft Excel
  3. Подтвердите свой выбор в новом окне.Подтверждение выбора двух столбцов для удаления дубликатов в Microsoft Excel
  4. Просмотрите информацию об удаленных и оставшихся значениях.Информация о проверке двух столбцов для удаления дубликатов в Microsoft Excel
  5. Теперь обратите внимание на то, какие линии были удалены.Результат удаления дубликатов в Microsoft Excel в две колонки

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

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последние версии Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество встроенной функции поиска и удаления дубликатов — функции «Удалить дубликаты.

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

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

  1. Сначала выберите область, где вы хотите удалить дубликаты. Чтобы выделить всю таблицу, нажмите Ctrl+A.
  2. Далее переходим на вкладку «ДАННЫЕ» —> группа «Работа с данными» и нажимаем кнопку «Удалить дубликаты».

Удалить дубликаты, скопировав уникальные записи в другое место

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

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

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

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

Применение расширенного фильтра

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

Для использования фильтра необходимо:

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

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

Выделение повторов

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

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

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

Создание сводной таблицы

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

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

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

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

Формулы для поиска дубликатов в 1 столбце

  • Дубликаты, кроме первого экземпляра:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2, $A2)>1, «Дубликат»; «»)

  • Дубликаты из 1 экземпляра:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10, $A2)>1, «Дубликат», «Уникальный»)

Где ячейка A2 — первая, а A10 — последняя ячейка в диапазоне для поиска дубликатов.

Формулы для поиска дубликатов строк

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

=ЕСЛИ(СЧЁТЕСЧЁТ($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1,»Повторяющаяся строка»,»Уникальный»)

  • Дублировать строки с 1 вхождением. В этом случае, если в результатах поиска будет две и более одинаковых строки, все они будут помечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЧЁТ($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, «Дубликат строки», «Уникальный»)

Где A, B и C — столбцы, которые нужно проверить на наличие дубликатов.

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

Оцените статью
Блог о Microsoft Word