4. Найдите дубликаты в Excel с помощью формулы
к счастью, существует несколько способов их найти и удалить. Мы рекомендуем сделать резервную копию листа Excel перед удалением дубликатов. Давайте посмотрим, как подсчитывать, находить и удалять повторяющиеся значения в Excel.1. Используйте кнопку «Удалить дубликаты»
Самый быстрый способ найти и удалить дубликаты в Excel — использовать кнопку Excel «Удалить дубликаты». Этот метод позволяет искать дубликаты на основе данных в одном
Contents
- 1 1. Используйте кнопку «Удалить дубликаты»
- 2 2. Найдите повторяющиеся данные с помощью условного форматирования
- 3 3. Удаление дубликатов с помощью условного фильтра
- 4 4. Найдите дубликаты в Excel с помощью формулы
- 5 5. Подсчитайте количество дубликатов по формуле
- 6 6. Удаление повторяющихся значений с помощью формулы
- 7 7. Удалите повторяющиеся данные с помощью расширенных фильтров
- 8 8. Удаление дубликатов Excel с помощью Power Query
- 9 9. Использование сводной таблицы
1. Используйте кнопку «Удалить дубликаты»
ри обнаружении дубликатов.Нажмите любую ячейку с данными в электронной таблице. Это не обязательно должна быть ячейка, столбец или строка, в которых вы хотите удалить дубликаты.
Выберите вкладку «Данные» и нажмите кнопку «Удалить дубликаты» на панели инструментов.
Отметьте столбцы, в которых вы хотите удалить дубликаты. По умолчанию все столбцы отмечены флажком. Кроме того, если в ваших столбцах нет заголовков, снимите флажок «Мои данные имеют заголовки», чтобы включить первую строку. Нажмите «ОК» после того, как вы выбрали нужные столбцы.
Excel выдает сообщение о том, сколько строк было удалено и сколько строк осталось. Этот метод удаляет всю строку, а не только значение. Нажмите «ОК», чтобы принять результаты и вернуться к таблице.
Если вам не нравятся результаты, нажмите Ctrl+ Z, чтобы вернуть удаленные значения на ваш лист.
2. Найдите повторяющиеся данные с помощью условного форматирования
Если вы предпочитаете просматривать дубликаты в Excel, прежде чем что-либо удалять, попробуйте использовать условное форматирование . Это находит дубликаты в Excel, но не удаляет их.
Выберите столбцы или весь лист, где вы хотите найти повторяющиеся значения.
Откройте вкладку «Главная» и нажмите кнопку «Условное форматирование».
Выберите «Выделить правила ячеек» и выберите «Повторяющиеся значения».
Выберите цвет, которым вы хотите отображать повторяющиеся значения. Вы также можете переключить «Тип правила» с «Повторяющиеся значения» на «Уникальные значения», если вы предпочитаете, чтобы уникальные значения были выделены. Когда закончите, нажмите «Готово».
2. Найдите повторяющиеся данные с помощью условного форматированияПросмотрите выделенные ячейки и удалите ненужные дубликаты. В моем примере я хочу удалить только строки, в которых каждое значение дублируется.
3. Удаление дубликатов с помощью условного фильтра
Если вы хотите упростить удаление дубликатов в Excel после использования условного форматирования, используйте фильтр Excel на основе цвета ячейки, чтобы отображать только дубликаты или уникальные значения. Используя этот вариант, вы удалите вручную всю строку или только повторяющиеся ячейки.
Настройте условное форматирование на основе предыдущего раздела.
Откройте вкладку «Главная», нажмите кнопку «Сортировка и фильтр» и выберите «Фильтр».
В заголовке каждого столбца появятся стрелки раскрывающегося списка. Нажмите стрелку и выберите «Фильтровать по цвету». Выберите цвет повторяющихся ячеек, чтобы просмотреть только их. Или выберите «Без заполнения», чтобы выбрать только уникальные значения.
Обратите внимание, что при выборе варианта «Без заливки» дубликаты (включая исходное значение) не отображаются. Если вы по-прежнему хотите видеть все значения, выберите «Фильтровать по цвету». При этом вверху списка помещаются либо цветные ячейки, либо ячейки без заливки.
В моем примере я сортирую по цвету повторяющиеся ячейки. Это позволяет вам просмотреть дубликаты и удалить любые строки или значения, которые вы не хотите сохранять. Если вы решите отображать только уникальные значения, вы можете скопировать или переместить все видимые данные на новый лист. При этом останутся только дубликаты.
Завершив редактирование данных, еще раз нажмите стрелку раскрывающегося списка и выберите «Очистить фильтр».
Если вы хотите удалить стрелки раскрывающегося списка, выберите «Сортировать и фильтровать» на панели инструментов, а затем выберите «Фильтр». При этом все фильтры будут очищены.
Совет. вы можете Делайте гораздо больше с фильтрами Excel . Например, вы можете удалить пустые ячейки
3. Удаление дубликатов с помощью условного фильтра
бликаты в Excel с помощью формулыФормулы Excel позволяют выполнить практически любую задачу в электронной таблице, включая поиск дубликатов. Существует несколько различных формул, которые вы можете использовать в зависимости от того, что вы хотите увидеть. Все они основаны на значении Функции СЧЕТ . Эти формулы не удаляют дубликаты. Вы должны сделать это вручную, как только они будут найдены. Независимо от того, какую формулу вы используете, процесс один и тот же:
Создайте новый столбец на том же листе. Я назвал свой «Дубликаты».
Выберите первую пустую ячейку в новом столбце. Введите желаемую формулу (см. различные формулы под шагами) в строку функций/формул или в саму ячейку.
Если вы хотите распространить формулу на другие ячейки столбца, нажмите и удерживайте маленький квадрат в правом нижнем углу ячейки, содержащей формулу. Потяните вниз, чтобы заполнить столько ячеек, сколько хотите.
Теперь, когда вы знаете, как вставить формулу, лучше всего использовать следующие варианты:
=COUNTIF(Range, Criteria) >1– диапазон – это ваш столбец, а критерий — самая верхняя ячейка. Это хорошо работает для поиска дубликатов в одном столбце. Например, чтобы увидеть дубликаты только на основе столбца D, вы должны использовать=COUNTIF(D:D, D2) >1. Результат «True» означает дубликат, а «False» — уникальный.
- Если вы не хотите, чтобы ссылка на ячейку менялась при перетаскивании, используйте вместо этого абсолютные ссылки на ячейки. Например, я бы использовал
=COUNTIF($D$2:$D$105, $D2) >1, чтобы проверить весь столбец D. - Если вы хотите показать что-то отличное от «Истина» или «Ложь», заключите СЧЁТЕСЛИ внутри функции ЕСЛИ:
=IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", " "). При этом будет отображаться «Дублировать» для дубликатов и пустая ячейка для уникальных значений. Вы можете легко использовать любые слова, например «Дубликат» и «Уникальный». - Если вы хотите сравнить несколько столбцов одновременно, вам нужно будет использовать абсолютные ссылки на ячейки и оператор IF, например:
=IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique")Эта формула будет отображать «Дубликат», только если значения в D, E и F одинаковы. Если какой-либо из трех подряд уникален, вместо него будет отображаться «Уникальный». - Если вы хотите отображать толькоповторяющиеся значения, а не первое вхождение значения, попробуйте вместо этого следующее:
=IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique")При этом отображается только «Дублировать» для второго или больше случаев.
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
Мощность запроса может удалять повторяющиеся значения в Excel, как показано ниже. Этот метод удаляет всю повторяющуюся строку.
Откройте вкладку «Данные» и выберите «Из таблицы/диапазона».
Все ваши данные должны быть выбраны автоматически. Если нет, введите ве
5. Подсчитайте количество дубликатов по формуле
ите, нажмите «ОК».
Откроется редактор Power Query. Выберите столбцы и щелкните правой кнопкой мыши заголовок выбранного столбца. В меню выберите «Удалить дубликаты». Это удалит все дубликаты в редакторе Power Query. В этом инструменте исходное вхождение остается в списке.
Если вы хотите удалить дубликаты из всей таблицы, нажмите кнопку «Таблица» в верхнем левом углу и выберите «Сохранить дубликаты», чтобы отобразить только повторяющиеся записи и удалить остальны
6. Удаление повторяющихся значений с помощью формулы
-content/uploads/2024/05/how-to-find-and-remove-duplicates-in-excel-keep-duplicates.jpg" alt="Сохраняйте дубликаты в Excel Power Query">Отображаются все дубликаты, включая исходные записи. Чтобы удалить лишние данные и оставить только исходные записи, снова нажмите «Таблица» и выберите «Удалить дубликаты».
Нажмите «Закрыть и загрузить» вверху, чтобы открыть таблицу в новой вкладке той же книги. Будут загружены только результаты вашего запроса. При этом ничего не удаляется из исходного листа.
9. Использование сводной таблицы
Используйте сводные таблицы, чтобы отображать только уникальные значения в ваших данных, удаляя таким образом повторяющиеся записи. На самом деле это не удаляет строки или значения из исходных данных; он просто показывает вам уникальные значения.
Откройте вкладку «Вставка» и выберите «Сводная таблица». Выберите «Из таблицы/диапазона».
Выберите таблицу или диапазон, в котором вы хотите скрыть повторяющиеся значения. По умолчанию должен быть выбран весь набор значений. Если нет, нажмите Ctrl+ A, чтобы выбрать все. Или используйте мышь, чтобы выбрать собственный диапазон.
Выберите, следует ли разместить сводную таблицу на том же листе или на новом листе. Если это существующий лист, введите или выберите ячейку, которую вы хотите использовать, в поле «Местоположение». Нажмите «ОК».
На боковой панели сводной таблицы отметьте или перетащите столбцы, из которых вы хотите извлечь уникальные значения, в раздел «Строки».
Вам потребуется отформатировать сводную таблицу, чтобы она отображалась в табличной форме. Для этого перейдите на вкладку «Дизайн» и выполните следующие действия:
Выберите «Макет отчета ->Показать в табличной форме».
Выберите «Промежуточные итоги ->Не показывать промежуточные итоги».
7. Удалите повторяющиеся данные с помощью расширенных фильтровсводной таблице">Выберите «Макет отчета ->Повторить все метки элементов».
Выберите «Общие итоги ->Выкл. для строк и столбцов».
Вы получите сводную таблицу с уникальными значениями в табличной форме.
При таком большом количестве способов найти и удалить дубликаты в Excel нет смысла делать это снова вручную. Выберите свой любимый метод и используйте его. Экономьте время, решая повторяющиеся проблемы, попробуйте эти Советы и рекомендации по Microsoft Excel , чтобы сэкономить еще больше времени. Также узнайте, как продолжить очистите свои данные в Excel .
