Как удалить повторяющиеся значения в Excel.
Распространенная проблема, возникающая при увеличении размера базы данных, заключается в том, что в ней появляется много дубликатов. И даже если в вашем огромном файле всего несколько одинаковых записей, эти несколько повторений могут вызвать массу проблем. Например, не рекомендуется отправлять несколько копий одного и того же документа одному и тому же лицу или несколько раз включать одни и те же данные в отчет.
Поэтому перед использованием базы имеет смысл проверить ее на наличие повторяющихся записей, чтобы потом не тратить время на исправление ошибок.
- Как удалить повторяющиеся строки вручную
- Удалить дубликаты в смарт-таблице
- Удалите дубликаты, скопировав уникальные записи в другое место
- Формулы для удаления дубликатов
- Формулы для поиска дубликатов в столбце
- Удалить повторяющиеся строки с помощью формул
- Универсальный инструмент для поиска и удаления дубликатов в Excel
В нескольких наших недавних статьях мы обсуждали различные способы обнаружения дубликатов в Excel и выделения неуникальных ячеек или строк. Однако могут быть случаи, когда вы в конечном итоге захотите устранить дубликаты в своих таблицах. И это только тема данного руководства.
Удаление повторяющихся строк вручную
Если вы используете последнюю версию Microsoft Excel с 2007 по 2019 год, у вас есть небольшое преимущество. Эти версии включают встроенную функцию для поиска и удаления повторяющихся значений.
Этот инструмент позволяет находить и удалять абсолютные совпадения (ячейки или целые строки), а также частичные совпадения (с одинаковыми значениями в столбце или диапазоне).
Важно! Поскольку инструмент «Удалить дубликаты» безвозвратно удаляет идентичные записи, рекомендуется сделать копию исходных данных, прежде чем что-либо удалять.
Для этого выполните следующие действия.
- Для начала выберите область, в которой хотите работать. Чтобы выделить всю таблицу, нажмите Ctrl + A,
- После указания диапазона перейдите на вкладку «Данные» > и нажмите кнопку «Удалить дубликаты» .
- Откроется диалоговое окно. Выберите столбцы для проверки на наличие дубликатов и нажмите «ОК».
- Чтобы удалить повторяющиеся строки с одинаковыми данными во всех столбцах, оставьте флажки рядом со всеми столбцами снятыми, как на снимке экрана ниже.
- Чтобы удалить частичные совпадения на основе одного или нескольких ключевых столбцов, выберите только их. Если в вашей таблице много столбцов, самый быстрый способ — нажать кнопку «Очистить выделение». А затем отметьте те, которые вы хотите проверить.
- Если в вашей таблице нет заголовков, снимите флажок Мои данные в правом верхнем углу диалогового окна, которое обычно включено по умолчанию.
- Если в диалоговом окне указать все столбцы, строка удаляется только в том случае, если во всех из них есть повторяющиеся значения. Но в некоторых ситуациях вам не нужно учитывать данные в некоторых столбцах. Поэтому снимите с них галочки. Например, если каждая строка содержит уникальный идентификационный код, программа никогда не найдет ни одного дубликата. Поэтому флажок рядом с колонкой с такими кодами следует убрать.
Законченный! Все повторяющиеся строки в нашей выборке удаляются, и отображается сообщение, указывающее, сколько повторяющихся записей было удалено и сколько уникальных.
Важная заметка. Повторяющиеся значения определяются тем, что появляется в ячейке, а не тем, что в ней написано на самом деле. Представьте, что A1 и A2 содержат одну и ту же дату. Один из них имеет формат 15.05.2020, а другой — формат 15.05.2020.При поиске повторяющихся значений Excel считает, что они не совпадают. Точно так же значения, отформатированные по-разному, считаются разными, поэтому 1 209,32 доллара — это совсем не то же самое, что 1 209,32.
Поэтому для поиска и удаления дубликатов в таблице или диапазоне данных рекомендуется применять один и тот же формат ко всему столбцу.
Примечание. Функция «Удалить дубликаты» удаляет второе и все последующие совпадения, оставляя все уникальные и первые вхождения идентичных записей.
Удаление дубликатов в «Умной таблице».
Я думаю, вы знаете, что если мы преобразуем ряд ячеек в таблицу, в нашем распоряжении будет много интересных дополнительных возможностей для работы с этими данными. Именно по этой причине такую таблицу Excel называют «Умной».
Выделите нужный нам диапазон, затем выберите «Форматировать как таблицу» на вкладке «Главная». Затем вам будет предложено указать желаемый вариант дизайна. Когда вы закончите, вкладка «Дизайн» откроется автоматически».
Выберите нужную кнопку на ленте, как показано на скриншоте. Затем мы отмечаем столбцы, в которых будем искать повторения. Ну а дальше произойдет то же самое, что описано в предыдущем пункте.
Однако, в отличие от ранее упомянутого инструмента удаления, операцию можно отменить, если что-то пошло не так.
Избавьтесь от повторов, скопировав уникальные записи в другое место.
Другой способ удалить дубликаты — выбрать все уникальные записи и скопировать их в другую таблицу или книгу. Подробные шаги приведены ниже.
- Выберите область или всю таблицу, которую вы хотите обработать (1).
- Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр — Дополнительно» (3-4).
- В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
- Выберите переключатель для копирования в другое место (6).
- Убедитесь, что в списке областей указана правильная область. Это должна быть область из шага 1.
- В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи (на самом деле достаточно указать его левую верхнюю ячейку).
- Выберите только уникальные записи (8).
- Наконец, нажмите OK, и уникальные значения будут скопированы в новое место:
Комментарий. Расширенный фильтр позволяет копировать отфильтрованные данные только в другое место на активном листе. Например, выберите место внизу под вашими исходными данными.
Думаю, вы понимаете, что можно обойтись и без копирования. Просто выберите опцию «Список фильтров на месте», и повторяющиеся записи будут временно скрыты фильтром. Они не будут удалены, но и не будут вам мешать.
Как убрать дубликаты строк с помощью формул.
Другой способ удалить неуникальные данные — идентифицировать их с помощью формулы, затем отфильтровать, а затем удалить лишние после этого.
Преимуществом такого подхода является его универсальность: он позволяет:
- найти и удалить дубликаты в одном столбце,
- найти повторяющиеся строки на основе значений в нескольких столбцах данных,
- сохранить первые вхождения повторяющихся записей.
Минус в том, что приходится запоминать несколько формул.
В зависимости от вашей задачи используйте одну из следующих формул для обнаружения дубликатов.
Формулы для поиска повторяющихся значений в одном столбце
Добавляем еще один столбец, где пишем формулу.
Повторы названий продуктов, за исключением первого появления:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B2, $B2)>1, "Дубликат"; "")
Как видите, это считается нормальным, когда значение встречается впервые (например, в ячейке B4). А вот его второе появление (в Б7) уже считается повторением.
Отмечаем все повторения вместе с первым вхождением:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17, $B2)>1, "Дубликат", "Уникальный")
Где A2 — первая, а A10 — последняя ячейка в совпадающем диапазоне.
Ну а теперь, чтобы удалить лишнее, поставьте фильтр в столбец H и оставьте только «Дублировать». После этого линии, оставшиеся на экране, просто стираются.
Вот небольшое пошаговое руководство.
- Выберите любую ячейку и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «Данные».
- Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке нужного столбца.
- И, наконец, удалите дубликаты. Для этого выделите отфильтрованные строки, перетащив указатель мыши на их номера, щелкните правой кнопкой мыши и выберите в контекстном меню пункт «Удалить строку». Причина, по которой вам нужно сделать это вместо того, чтобы просто нажимать кнопку «Удалить» на клавиатуре, заключается в том, что это действие удалит целые строки, а не только содержимое ячейки.
Формулы для поиска повторяющихся строк.
Если нам нужно найти и удалить повторяющиеся строки (или их части), действуем так же, как и для отдельных ячеек. Мы просто немного изменим формулу.
С помощью формулы выделяем неуникальные строки, кроме первого вхождения:
=ЕСЛИ(СЧЁТЕСЧЁТ($B$2:$B2, $B2,$A$2:A2,$A2,$C$2:C2,$C2)>1, "Дубликат"; "")
В итоге видим 2 повтора.
Теперь самый простой вариант — поставить фильтр на столбец H и слово «Дублировать». После этого просто удаляем сразу все отфильтрованные строки.
Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:
=ЕСЛИ(СЧЁТЕСЧЁТ($B$2:$B$17, $B2,$A$2:$A$17,$A2,$C$2:$C$17,$C2)>1, "Дубликат"; "")
Затем снова установите фильтр и продолжайте так же, как описано выше.
Duplicate Remover - универсальный инструмент для поиска и удаления дубликатов в Excel.
В отличие от встроенного средства удаления дубликатов Excel, о котором мы говорили выше, средство удаления дубликатов Ablebit не ограничивается простым удалением повторяющихся записей. Подобно швейцарскому армейскому ножу, этот многофункциональный инструмент сочетает в себе все наиболее важные варианты использования и позволяет вам определять, выбирать, выделять, удалять, копировать и перемещать уникальные или повторяющиеся значения, с первым вхождением или без него, все повторяющиеся или перекрывающиеся строки в одном и том же таблицы или путем сравнения двух таблиц.
Безупречно работает на всех операционных системах и всех версиях Microsoft Excel 2019 - 2003.
Как избавиться от дубликатов в Excel в 2 клика мышки.
Предполагая, что в Excel установлен Ultimate Suite, выполните следующие простые действия, чтобы удалить повторяющиеся строки или ячейки:
- Выберите ячейку в электронной таблице, с которой вы хотите работать, и нажмите «Быстрая дедупликация» на вкладке «Данные Ablebits.
- Откроется диалоговое окно, и все столбцы будут выбраны по умолчанию. Выберите нужные, а также укажите нужное действие в выпадающем списке в правом нижнем углу.
Поскольку моя цель — просто выделить повторяющиеся данные, я выбрал Colorize».
Помимо выбора цвета вам доступны и другие операции:
- Удалить дубликаты
- Выберите дубликаты
- Перечислите их в столбце статуса
- Копировать дубликаты на новый лист
- Перейти на новый лист
- Нажимаем кнопку ОК и оцениваем результат:
Как видно на скриншоте выше, были найдены строки с повторяющимися значениями в первых 3 столбцах (первые вхождения здесь по умолчанию не считаются дубликатами).
Совет. Если вы хотите определить повторяющиеся строки на основе значений в ключевом столбце, оставьте выбранными только этот столбец (столбцы) и снимите флажки со всех других нерелевантных столбцов.
А если вы хотите выполнить другое действие, например удалить повторяющиеся строки или скопировать повторяющиеся значения в другое место, выберите соответствующий вариант из раскрывающегося списка.
Больше возможностей для поиска дубликатов при помощи Duplicate Remover.
Если вам нужны дополнительные параметры, например удаление повторяющихся строк, включая первые вхождения, или поиск уникальных значений, используйте мастер удаления дубликатов, который предоставляет эти и другие функции. Давайте рассмотрим пример того, как найти повторяющиеся значения с первым вхождением или без него.
удаление дубликатов в Excel — обычная операция. Но в каждом конкретном случае может быть ряд функций. В то время как Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.
- Выберите любую ячейку в таблице, из которой вы хотите удалить дубликаты, перейдите на вкладку Ablebits Data и нажмите кнопку Duplicate Remover.
- Вам предлагается 4 варианта проверки дубликатов на листе Excel:
- Дублирует без первого появления повторяющихся записей.
- Дублируется с 1 вхождением.
- Уникальные списки.
- Уникальные значения и первые повторяющиеся вхождения.
- В этом примере мы выберем второй вариант, то есть дубликаты + 1 экземпляр:
- Все ваши данные будут автоматически выделены.
- Теперь выберите столбцы, в которых вы хотите проверить наличие дубликатов. Как и в предыдущем примере, выбираем первые три столбца:
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с Quick Duplicate Finder, мастер удаления дубликатов может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Чтобы увидеть результат более четко, установите флажок «Заливка цветом» и нажмите «Готово.
Мастеру дедупликации требуется совсем немного времени, чтобы проанализировать таблицу и показать вам результат:
Как видите, результат аналогичен тому, что мы наблюдали выше. Но здесь мы выделили дубликаты, включая первое появление этих повторяющихся записей. Если вы выберете опцию удаления, эти 4 записи будут удалены из вашей таблицы.
Плагин также создает резервную копию таблицы, чтобы вы случайно не потеряли нужные данные: вдруг вы хотели сохранить первые вхождения данных, но случайно выбрали не тот элемент.
