Как удалить дубликаты в Excel
Иногда ваша электронная таблица Excel ускользает от вас, и вы можете обнаружить, что она нуждается в хорошей очистке. Особенно распространенная проблема – это дублирование информации в нескольких ячейках или строках. Итак, вот несколько простых способов найти и удалить повторяющиеся данные в Excel, чтобы исправить это.
Как найти повторяющиеся значения в Excel
Перед удалением повторяющихся значений рекомендуется находить и выделять повторяющиеся значения в Excel. При удалении значений они навсегда удаляются из электронной таблицы Excel, поэтому их выделение в первую очередь дает вам возможность просмотреть дубликаты и убедиться, что они вам не нужны.
В нашем примере мы будем использовать следующую таблицу:
Наш вымышленный пользователь отслеживает все перьевые ручки, которые она нарисовала. Электронная таблица содержит информацию о ручке и цвете чернил в ручке. Но она подозревает, что несколько раз случайно вошла в свои ручки.
Самый простой способ определить повторяющиеся значения – использовать условное форматирование. Чтобы выделить повторяющиеся значения с помощью условного форматирования, выполните следующие действия:
- Выберите ячейки, которые вы хотите проверить на наличие дубликатов.
- На вкладке « Главная » нажмите « Условное форматирование» в группе « Стили ».
- Выберите Правила для выделения ячеек> Повторяющиеся значения .
- Появится всплывающее окно, в котором можно выбрать формат стиля для повторяющихся ячеек.
- По завершении нажмите ОК .
Этот метод выделяет дубликаты в каждом столбце или строке. Как вы можете видеть ниже, любая компания, модель или цвет чернил, перечисленные более одного раза, будут выделены.
В этом примере нам нужно посмотреть на столбец Модель, чтобы найти повторяющиеся ручки. У нас есть две ручки Student, 41 и Preppy. У 41-х разные чернила, поэтому, скорее всего, это разные ручки. Но ручки Student и Preppy могут быть дубликатами.
Как видите, этот метод не идеален.
Он находит каждую повторяющуюся ячейку, но в этом случае нас интересует только поиск повторяющихся строк. У вас может быть много разных ручек от одной компании с одинаковыми чернилами. Но у вас меньше шансов иметь более одного пера одной компании, модели и цвета чернил.
Как выделить повторяющиеся строки в Excel с помощью настраиваемой формулы
Excel позволяет создавать произвольное условное форматирование с помощью формул. Мы можем использовать этот метод для выявления повторяющихся строк. Выбор этого параметра аналогичен тому, как мы выбирали повторяющиеся значения выше, за исключением того, что вместо выбора правил выделения ячеек выберите новое правило .
Это вызывает всплывающее меню. Установите для стиля « Классический» , затем откройте следующее раскрывающееся меню и выберите « Использовать формулу», чтобы определить, какие ячейки нужно форматировать .
Формула, которую мы хотим ввести:
COUNTIFS($A$2:$A$14,$A2,$B$2:$B$14,$B2,$C$2:$C$14,$C2)>1Давайте подробнее рассмотрим эту формулу.
Формулы условного формирования должны возвращать истинный или ложный ответ. Если ответ верный, применяется форматирование. Это уравнение верно, когда оно насчитывает более одной строки с одинаковой информацией.
Чтобы проверить строки на наличие дубликатов, формула сначала выбирает столбец ($ A $ 2: $ A $ 14). Мы используем абсолютное расположение, потому что хотим, чтобы все использовали один и тот же диапазон ячеек при оценке строк на наличие дубликатов.
Следующая переменная – это цель, которую мы проверяем на наличие повторяющегося значения ($ A2). На этот раз для столбца используется абсолютное расположение, но не для строки. Это позволяет нашей формуле постепенно проверять каждую строку.
Мы повторяем это для каждого столбца в строке.
После ввода формулы не забудьте выбрать стиль форматирования. По умолчанию стиль отсутствует. Итак, даже если вы все сделаете правильно, если вы забудете этот шаг, вы не увидите результатов.
После этого наша таблица выглядела так:
Этот метод выделяет только повторяющиеся строки целиком. После того как вы найдете все дубликаты в своей таблице Excel, вы можете решить, нужно ли их удалить.
Как удалить дубликаты в Excel
В Excel легко удалить дубликаты. На вкладке Данные в группе Инструменты Data, вы найдете возможность удаления дубликатов. Этот инструмент очень удобен, поскольку позволяет вам выбрать, какие столбцы вы хотите проверить.
Чтобы использовать этот инструмент, выберите свои данные и нажмите « Удалить дубликаты» .
Когда вы это сделаете, появится всплывающее окно, в котором вы сможете выбрать, какие столбцы проверять на наличие дубликатов. Если вы выберите все столбцы, будут удалены только повторяющиеся строки.
Однако вам не обязательно выбирать все столбцы. Например, если бы я хотел узнать, какие чернила в настоящее время находятся в перьях, вы могли бы выбрать « Цвет чернил» и удалить повторяющиеся цвета. Это оставит первое перо, использующее новый цвет, и удалит все последующие записи.
Использование инструмента Excel «Удалить дубликаты» автоматически удаляет повторяющиеся записи.
Если вы хотите только временно удалить повторяющиеся записи, но не удалять их, вы можете вместо этого отфильтровать свои данные.
Как отфильтровать дубликаты в Excel
Фильтры позволяют управлять отображаемой частью данных без фактического удаления каких-либо значений. Вы можете найти параметры фильтра на вкладке « Данные » в группе « Сортировка и фильтр ».
Есть много разных способов фильтрации данных в Excel. В приведенном ниже примере показано только, как отфильтровать повторяющиеся значения:
- Выберите данные, которые вы хотите фильтровать.
- Нажмите « Дополнительно» в группе « Сортировка и фильтр ».
- Во всплывающем окне включите параметр, чтобы отображать только уникальные записи .
- Щелкните ОК .
Фильтрация этих данных заставляет Excel скрывать любые повторяющиеся строки. Итак, в нашем примере дублированные ручки Student и Preppy были скрыты.
Но повторяющиеся данные не исчезли, они просто скрыты от просмотра. Вот почему наша таблица по-прежнему отформатирована в соответствии с созданным нами ранее правилом, несмотря на то, что не появляются повторяющиеся перья.
Фактически, если вы посмотрите на номера строк, вы увидите, где спрятаны две строки. Строки перескакивают с семи на девять и снова переходят между 12 и 14. Данные не исчезли, они просто скрылись из виду.
Обзор способов удаления дубликатов
Первый шаг в очистке ваших данных – выявление любых дубликатов. Удаление дубликатов в Excel приводит к безвозвратному удалению данных. Выделение их дает вам возможность сначала оценить дубликаты. А если вас интересуют повторяющиеся строки, вам нужно использовать формулы условного форматирования, чтобы найти их.
И, наконец, если вы не хотите удалять повторяющиеся данные, попробуйте вместо этого использовать фильтр. Это скрывает их от просмотра, фактически не удаляя дублирующиеся данные из Excel.