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