Как удалить повторяющиеся значения в Excel?
В этом руководстве объясняется, как удалить повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавления от повторяющихся строк, поиска точных дубликатов и поиска частичных совпадений.
Хотя Microsoft Excel в первую очередь является инструментом расчета, его электронные таблицы часто используются в качестве баз данных для отслеживания запасов, составления отчетов о продажах или ведения списков рассылки.
Распространенная проблема, возникающая при увеличении размера базы данных, заключается в том, что в ней появляется много дубликатов. И даже если в вашем огромном файле всего несколько одинаковых записей, эти несколько повторений могут вызвать массу проблем. Например, отправка нескольких копий одного и того же документа одному и тому же человеку или несколько раз наличие одних и тех же данных в отчете — не лучшая идея.
Поэтому перед использованием базы данных имеет смысл проверить наличие повторяющихся записей, чтобы потом не тратить время на исправление ошибок.
- Как вручную удалить повторяющиеся строки
- Устранение дубликатов в смарт-таблице
- Устранение дубликатов путем копирования уникальных записей в другое место
- Формулы для удаления дубликатов
- Формулы для поиска дубликатов в столбце
- Удалить повторяющиеся строки с помощью формул
- Универсальный инструмент для поиска и удаления дубликатов в Excel
В нескольких наших недавних статьях мы обсуждали различные способы обнаружения дубликатов в Excel и выделения неуникальных ячеек или строк (см ссылки в конце статьи). Однако могут быть случаи, когда вы захотите в конечном итоге удалить дубликаты в своих таблицах. И это только тема данного руководства.
Удаление повторяющихся строк вручную
Если вы используете последнюю версию Microsoft Excel с 2007 по 2019 год, у вас есть небольшое преимущество. Эти версии содержат встроенную функцию для поиска и удаления повторяющихся значений.
Этот инструмент позволяет находить и удалять абсолютные совпадения (целые ячейки или строки), а также частичные совпадения (имеющие одинаковые значения в столбце или диапазоне).
Важно! Поскольку инструмент «Удалить дубликаты» безвозвратно удаляет идентичные записи, рекомендуется создать копию исходных данных, прежде чем что-либо удалять.
Для этого выполните следующие действия.
- Для начала выберите диапазон, над которым хотите работать. Чтобы выделить всю таблицу, нажмите Ctrl+A,
- Указав диапазон, перейдите на вкладку «Данные >» и нажмите кнопку «Удалить дубликаты» .
- Откроется диалоговое окно. Выберите столбцы для проверки на наличие дубликатов и нажмите OK».
- Чтобы удалить повторяющиеся строки с одинаковыми данными во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на снимке экрана ниже.
- Чтобы удалить частичные совпадения на основе одного или нескольких ключевых столбцов, выберите только их. Если в вашей таблице много столбцов, самый быстрый способ — нажать кнопку «Отменить выбор». А затем проверьте те, которые вы хотите проверить.
- Если в вашей таблице нет заголовков, снимите флажок Мои данные в правом верхнем углу диалогового окна, который обычно включен по умолчанию.
- Если в диалоге указать все столбцы, строка будет удалена только в том случае, если во всех из них есть повторяющиеся значения. Но в некоторых ситуациях вам не нужно учитывать данные в определенных столбцах. Поэтому снимите с них галочки. Например, если каждая строка содержит уникальный идентификационный код, программа никогда не найдет ни одного дубликата. Поэтому галочку напротив столбца с такими кодами нужно снять.
Сделанный! Все повторяющиеся строки в нашем диапазоне удаляются, и отображается сообщение, указывающее, сколько повторяющихся записей было удалено, а сколько уникальных.
Важная заметка. Повторяющиеся значения определяются тем, что отображается в ячейке, а не тем, что в ней на самом деле набрано. Предположим, что A1 и A2 содержат одну и ту же дату. Один из них имеет формат 15.05.2020, а другой — 15 мая 2020 года. При поиске повторяющихся значений Excel считает, что они не совпадают. Точно так же значения, имеющие другой формат, считаются разными, поэтому 1209,32 доллара — это совсем не то же самое, что 1209,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 средства удаления дубликатов, о котором мы говорили ранее, средство удаления дубликатов Ablebits не ограничивается удалением повторяющихся записей. Подобно швейцарскому армейскому ножу, этот многофункциональный инструмент сочетает в себе все основные варианты использования и позволяет определять, выбирать, выделять, удалять, копировать и перемещать уникальные или повторяющиеся значения, с первым вхождением или без него, все повторяющиеся или перекрывающиеся строки в одной таблице или сравнение двух таблиц.
Он отлично работает на всех операционных системах и всех версиях 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-е вхождения.
- В данном примере мы выберем второй вариант, т.е. Дубликаты + 1-е появление:
- Все ваши данные будут выделены автоматически.
- Теперь выберите столбцы, которые вы хотите проверить на наличие дубликатов. Как и в предыдущем примере, мы выбираем первые 3 столбца:
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с Quick Duplicate Finder, мастер Duplicate Remover может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Чтобы увидеть результат более четко, установите флажок «Заливка цветом» и нажмите «Готово.
Мастеру дедупликации требуется очень мало времени, чтобы проанализировать вашу таблицу и показать вам результат:
Как видите, результат аналогичен тому, что мы наблюдали ранее. Но здесь мы выделили дубликаты, включая первое появление этих повторяющихся записей. Если вы выберете опцию удаления, эти 4 записи будут удалены из вашей таблицы.
Плагин также создает резервную копию рабочего листа, чтобы вы случайно не потеряли нужные данные: вы вдруг захотели сохранить первые вхождения данных, но случайно выбрали не тот элемент.
Мы рассмотрели несколько способов удаления дубликатов из ваших электронных таблиц с формулами и без них. Я надеюсь, что хотя бы одно из решений, упомянутых в этом обзоре, сработает для вас.
Все мощные инструменты очистки от дубликатов, описанные выше, включены в надстройку Ultimate Suite for Excel. Если вы хотите попробовать их, я рекомендую вам загрузить полнофункциональную пробную версию и сообщить нам свои отзывы в комментариях.
Что ж, как вы только что убедились, есть несколько способов найти повторяющиеся значения в Excel, а затем удалить их. И каждый из них имеет свои сильные стороны и ограничения.
Еще на ту же тему:
Подсчет уникальных значений в Excel. В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул и как это делать в сводной таблице. Мы также увидим несколько примеров подсчета текста и уникальных чисел… Как подсчитать количество пустых и непустых ячеек в Excel: Если ваша задача состоит в том, чтобы заставить Excel подсчитывать пустые ячейки на листе, прочитайте эту статью, чтобы найти 3 способы сделать это. Узнайте, как искать и выбирать между ними, используя стандартные…
Как сравнить два столбца на наличие совпадений и различий — чтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Как выделить повторяющиеся значения в Excel? — В этом уроке вы узнаете, как отображать дубликаты в Excel. Мы увидим несколько методов затенения повторяющихся ячеек, целых строк или последовательных вхождений с использованием условного форматирования. Ранее мы изучали различные…
Как посчитать количество повторяющихся значений в Excel? Зачем считать дубликаты? Мы можем получить ответы на многие интересные вопросы. Например, сколько клиентов совершили покупки, сколько менеджеров участвовало в продаже, сколько раз они работали с определенным поставщиком и т д. Да…
Как найти повторяющиеся значения в таблице Excel — В этой статье мы рассмотрим разные подходы к одной из самых распространенных и, на мой взгляд, важных задач в Excel: как найти дубликаты в ячейках и столбцах таблицы…
Функция СЧЁТЕСЛИМН в Excel с различными условиями — объясняем на примерах. — В этом руководстве объясняется, как использовать функцию СЧЁТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных: числа, даты, текст,…
СЧЁТЕСЛИ в Excel: примеры одиночных и множественных условий. В этой статье мы сосредоточимся на функции Excel COUNTIF, которая предназначена для подсчета ячеек с определенным условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем…
Как проверить правильность ввода данных в Excel? — Подтвердите правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода по существующему списку допустимых значений. В случае правильной записи в отдельной графе поставить