Как найти и удалить дубликаты в Excel
Дубликаты значений в Excel могут быть раздражающими, но, к счастью, есть несколько методов для их поиска и удаления. Мы рекомендуем сделать резервную копию вашей таблицы Excel перед удалением дубликатов. Давайте рассмотрим, как подсчитать, найти и удалить дубликаты значений в Excel.
Содержание
- Используйте кнопку “Удалить дубликаты”
- Найдите дублирующиеся данные с помощью условного форматирования
- Удалите дубликаты с помощью условного фильтра
- Найдите дубликаты в Excel с помощью формулы
- Подсчитайте количество дубликатов с помощью формулы
- Удалите дублирующиеся значения с помощью формулы
- Удалите дублирующиеся данные с помощью расширенных фильтров
- Удалите дубликаты Excel с помощью Power Query
- Использование сводной таблицы
1. Используйте кнопку “Удалить дубликаты”
Самый быстрый способ найти и удалить дубликаты в Excel — использовать кнопку “Удалить дубликаты”. Этот метод позволяет вам искать дубликаты на основе данных в одном или нескольких столбцах. Он удаляет целые строки, когда дубликаты найдены.
Нажмите на любую ячейку с данными в вашей таблице. Это не обязательно должна быть ячейка, столбец или строка, в которых вы хотите удалить дубликаты.
Выберите вкладку “Данные” и нажмите кнопку “Удалить дубликаты” на панели инструментов.
Отметьте столбцы, в которых вы хотите удалить дубликаты. Все столбцы отмечены по умолчанию. Также, если ваши столбцы не имеют заголовков, снимите отметку с поля “У моих данных есть заголовки”, чтобы ваша первая строка была включена. Нажмите “ОК” после того, как вы выбрали нужные столбцы.
Excel сообщает вам, сколько строк было удалено и сколько строк осталось. Этот метод удаляет всю строку, а не только значение. Нажмите “ОК”, чтобы принять результаты и вернуться к вашей таблице.
Если вам не нравятся результаты, нажмите Ctrl + Z, чтобы вернуть удаленные значения на ваш лист.
2. Найдите дублирующиеся данные с помощью условного форматирования
Если вы предпочитаете просмотреть дубликаты в Excel перед удалением чего-либо, попробуйте использовать условное форматирование. Это находит дубликаты в Excel, но не удаляет их.
Выберите столбцы или весь лист, где вы хотите искать дублирующиеся значения.
Откройте вкладку “Главная” и нажмите кнопку “Условное форматирование”.
Выберите “Правила выделения ячеек” и выберите “Дублирующиеся значения”.
Выберите цвет, в котором вы хотите, чтобы дублирующиеся значения отображались. Вы также можете переключить “Тип правила” с “Дублирующиеся значения” на “Уникальные значения”, если вы хотите, чтобы уникальные значения были выделены. Нажмите “Готово”, когда закончите.
Просмотрите выделенные ячейки и удалите дубликаты, которые вы не хотите. В моем примере я хочу удалить только строки, где каждое значение дублируется.
3. Удалите дубликаты с помощью условного фильтра
Если вы хотите упростить удаление дубликатов в Excel после использования условного форматирования, используйте фильтр Excel на основе цвета ячейки, чтобы отображать только дубликаты или уникальные значения. С помощью этого варианта вы сможете вручную удалить целые строки или только дублирующиеся ячейки.
Настройте условное форматирование на основе предыдущего раздела.
Откройте вкладку “Главная”, выберите кнопку “Сортировка и фильтр“ и выберите “Фильтр“.
Стрелки выпадающего списка появятся в заголовке каждого столбца. Нажмите на стрелку и выберите “Фильтровать по цвету”. Выберите цвет дублирующихся ячеек, чтобы просмотреть только их. Или выберите “Без заливки”, чтобы выбрать только уникальные значения.
Обратите внимание, что дубликаты (включая оригинальное значение) не отображаются, когда вы выбираете “Без заливки”. Если вы все еще хотите видеть все значения, выберите “Фильтровать по цвету” вместо этого. Это поместит либо цветные ячейки, либо ячейки без заливки в верхнюю часть списка.
В моем примере я сортирую по цвету дублирующихся ячеек. Это позволяет вам просмотреть дубликаты и удалить любые строки или значения, которые вы не хотите сохранить. Если вы решите показать только уникальные значения, вы можете скопировать или переместить все видимые данные на новый лист. Это оставит только дубликаты.
Когда вы закончите редактирование данных, снова нажмите стрелку выпадающего списка и выберите “Очистить фильтр“.
Если вы хотите удалить стрелки выпадающего списка, выберите “Сортировка и фильтр“ на панели инструментов и выберите “Фильтр“. Это очистит все фильтры.
Совет: вы можете сделать гораздо больше с фильтрами Excel. Например, вы можете удалить пустые ячейки или удалить лишние пробелы из ячейки.
4. Найдите дубликаты в Excel с помощью формулы
Формулы Excel могут выполнять почти любую задачу на таблице, включая поиск дубликатов. Есть несколько различных формул, которые вы можете использовать в зависимости от того, что вы хотите увидеть. Все они основаны на функциях COUNT. Эти формулы не удаляют дубликаты. Вы должны сделать это вручную, как только они будут найдены. Независимо от того, какую формулу вы используете, процесс одинаков:
Создайте новый столбец на том же листе. Я назвал свой “Дубликаты”.
Выберите первую пустую ячейку в вашем новом столбце. Введите желаемую формулу (см. различные формулы ниже шагов) в строке функций/формул или в самой ячейке.
Если вы хотите расширить формулу на другие ячейки в столбце, нажмите и удерживайте маленький квадрат в правом нижнем углу ячейки, содержащей формулу. Потяните вниз, чтобы заполнить столько ячеек, сколько вам нужно.
Теперь, когда вы знаете, как вставить формулу, вот лучшие варианты для использования:
=COUNTIF(Диапазон, Критерий) >1– Диапазон — это ваш столбец, а критерий — это верхняя ячейка. Это хорошо работает для поиска дубликатов в одном столбце. Например, чтобы увидеть дубликаты на основе только столбца D, вы бы использовали=COUNTIF(D:D, D2) >1. Результат “True” означает дубликат, в то время как “False” означает уникальный.
- Если вы не хотите, чтобы ссылка на ячейку изменялась при перетаскивании, используйте абсолютные ссылки на ячейки. Например, я бы использовал
=COUNTIF($D$2:$D$105, $D2) >1, чтобы проверить весь столбец D. - Если вы хотите показать что-то другое, кроме True или False, заключите COUNTIF внутри функции IF:
=IF(COUNTIF($D$2:$D$105, $D2) > 1, "Дубликат", " "). Это покажет “Дубликат” для дубликатов и пустую ячейку для уникальных значений. Вы можете легко использовать любые слова, которые хотите, такие как “Дубликат” и “Уникальный”. - Если вы хотите сравнить несколько столбцов одновременно, вам нужно будет использовать абсолютные ссылки на ячейки и оператор IF, например:
=IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Дубликат", "Уникальный"). Эта формула покажет “Дубликат” только если значения в D, E и F одинаковы. Если любое из трех уникально в строке, будет отображаться “Уникальный”. - Если вы хотите показать только дублирующиеся значения, а не первое вхождение значения, попробуйте это:
=IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Дубликат", "Уникальный"). Это покажет “Дубликат” только для второго или более вхождений.
5. Подсчитайте количество дубликатов с помощью формулы
Вы можете использовать вышеуказанную формулу без текста > 1, чтобы подсчитать количество дубликатов в столбце. Две формулы станут =COUNTIF($D$2:$D$105, $D2) и =COUNTIF(D:D, D2). Введите новый столбец, чтобы он отображал, сколько раз каждый элемент появляется в данных.
Значение “1” означает, что это уникальное значение. Все остальное равно дубликатам.
6. Удалите дублирующиеся значения с помощью формулы
После того, как вы нашли дублирующиеся значения (Раздел 4) или количество дубликатов (Раздел 5), используйте метод фильтра для удаления дубликатов и сохранения уникальных значений.
Нажмите на любую ячейку в столбце с вашими дублирующимися значениями или количеством. Перейдите в “Главная -> Сортировка и фильтр -> Фильтр“, чтобы включить выпадающее окно фильтра в заголовке столбца.
Нажмите стрелку выпадающего списка в вашем столбце дубликатов.
Отметьте значение(я), которые вы хотите сохранить, снимите отметку с того, что вы не хотите, и нажмите “Применить”. Скрыть все дубликаты, выбрав только “Уникальные”, “Ложные” или любое другое ваше обозначение для уникальных значений для метода дублирования, и “1” для подсчета дубликатов. Просмотрите только дубликаты, сняв отметку с уникальных идентификаторов.
Выберите видимые строки, нажав Ctrl + C. Если по какой-либо причине это выберет все строки, включая скрытые строки, используйте сочетание клавиш Alt + ; вместо этого.
Скопируйте уникальные значения или дубликаты (в зависимости от того, что вы выбрали для отображения) на другой лист. Удалите эти значения из оригинального листа.
Перейдите в “Сортировка и фильтр -> Фильтр“. Нажатие “Фильтр“ удалит фильтр и покажет все оставшиеся дубликаты или уникальные значения.
Отсюда пройдите через дубликаты и удалите ячейки или строки, которые вы больше не хотите. Помните, что оригинальное вхождение указано вместе с дублирующимися значениями.
7. Удалите дублирующиеся данные с помощью расширенных фильтров
До сих пор мы использовали только базовые фильтры, но вы также можете найти и удалить дубликаты в Excel с помощью расширенных фильтров.
Выберите столбец, который вы хотите отфильтровать. Откройте вкладку “Данные” и нажмите “Расширенный”.
В окне “Расширенный фильтр“ выберите “Фильтровать список на месте”. Это скрывает дубликаты в том же наборе данных. Позже вы можете вручную скопировать и вставить уникальные значения в другое место на том же листе или на отдельном листе.
Если вы еще не выбрали свои столбцы, выберите их. Они автоматически появятся в поле “Диапазон списка”. Оставьте поле “Диапазон критериев” пустым.
Отметьте поле рядом с “Только уникальные записи” и нажмите “ОК”.
Это покажет уникальные значения в ваших данных. Используйте сочетание клавиш Alt + ;, чтобы выбрать видимые строки, только если вы хотите выполнить какие-либо действия с ними, такие как копирование в другое место.
С другой стороны, выберите “Скопировать в другое место” в окне Расширенного фильтра, если вы хотите, чтобы Excel автоматически скопировал уникальные значения в другое место на том же листе.
Сначала выберите “Диапазон списка”. Вы можете оставить “Диапазон критериев” пустым.
Нажмите один раз на поле “Скопировать в” и выберите строки на вашем листе, куда вы хотите скопировать уникальные данные.
Убедитесь, что поле рядом с “Только уникальные записи” отмечено.
Этот метод скрывает всю дублирующуюся строку, а не только значения. Он также скрывает оригинальное вхождение дублирующегося значения, а не только дублирующие версии. Вам нужно будет просмотреть дубликаты, чтобы извлечь оригинальные значения.
8. Удалите дубликаты Excel с помощью Power Query
Power Query может удалить дублирующиеся значения в Excel, как показано ниже. Этот метод удаляет всю дублирующуюся строку.
Откройте вкладку “Данные” и выберите “Из таблицы/диапазона”.
Все ваши данные должны автоматически быть выбраны. Если нет, введите диапазон всей таблицы в поле “Создать таблицу”. Нажмите “ОК”, когда закончите.
Откроется редактор “Power Query”. Выберите столбцы и щелкните правой кнопкой мыши на заголовке выбранного столбца. Выберите “Удалить дубликаты” из меню. Это удаляет все дубликаты в редакторе Power Query. В этом инструменте оригинальное вхождение остается в списке.
Если вы хотите удалить дубликаты из всей таблицы, нажмите кнопку “Таблица” в верхнем левом углу и выберите “Сохранить дубликаты”, чтобы показать только дублирующиеся записи и удалить остальные.
Это показывает все дубликаты, включая оригинальные записи. Чтобы удалить лишние и оставить только оригинальные записи, снова нажмите “Таблица” и выберите “Удалить дубликаты”.
Нажмите “Закрыть и загрузить” вверху, чтобы открыть таблицу на новом листе того же рабочего файла. Загружаются только результаты вашего запроса. Это не удаляет ничего из оригинального листа.
9. Использование сводной таблицы
Используйте сводные таблицы, чтобы отображать только уникальные значения в ваших данных, тем самым удаляя дублирующиеся записи. Это на самом деле не удаляет никакие строки или значения из ваших оригинальных данных; это просто показывает вам уникальные значения.
Откройте вкладку “Вставка” и выберите “Сводная таблица”. Выберите “Из таблицы/диапазона”.
Выберите таблицу или диапазон, где вы хотите скрыть дублирующиеся значения. Ваш весь набор значений должен быть выбран по умолчанию. Если нет, нажмите Ctrl + A, чтобы выбрать все. Или используйте мышь, чтобы выбрать пользовательский диапазон.
Выберите, должна ли сводная таблица быть размещена в том же листе или в новом листе. Если это существующий лист, введите или выберите ячейку, которую вы хотите использовать в поле “Место расположения”. Нажмите “ОК”.
В боковой панели сводной таблицы отметьте или перетащите столбцы, из которых вы хотите извлечь уникальные значения, в раздел “Строки”.
Вам нужно будет отформатировать сводную таблицу, чтобы показать ее в табличной форме. Для этого перейдите на вкладку “Дизайн” и выполните следующие шаги:
Выберите “Макет отчета -> Показать в табличной форме”.
Выберите “Промежуточные итоги -> Не показывать промежуточные итоги”.
Выберите “Макет отчета -> Повторить все метки элементов”.
Выберите “Итоги -> Выключить для строк и столбцов”.
Вы получите сводную таблицу с уникальными значениями в табличной форме.
С таким количеством способов найти и удалить дубликаты в Excel нет причин делать это снова вручную. Выберите свой любимый метод и действуйте. Пока вы экономите время на проблемах с дубликатами, попробуйте эти советы и приемы Microsoft Excel, чтобы сэкономить еще больше времени. Также узнайте, как дополнительно очистить ваши данные в Excel.
Кредит изображения: Pixabay. Все скриншоты от Кристал Краудер.
