Как показать только повторяющиеся строки в столбце Excel?
В различных сценариях анализа данных вы можете столкнуться с ситуацией, когда столбец содержит как уникальные, так и повторяющиеся значения, но вы хотите просматривать или обрабатывать только повторяющиеся строки. Ручной поиск и скрытие каждого уникального значения по одному может быть неэффективным и подверженным ошибкам, особенно при работе с большими наборами данных. Пример показан на скриншоте ниже, где после фильтрации остаются видимыми только повторяющиеся записи.
Чтобы эффективно решить эту задачу, Excel предлагает несколько подходов: от встроенных функций до продвинутых надстроек и решений для автоматизации. Эти методы значительно снижают ручную нагрузку, помогают избежать ошибок и применимы независимо от размера набора данных. Ниже описаны различные практические решения, позволяющие выбрать наиболее подходящий вариант в зависимости от ваших потребностей и уровня владения Excel.
Показать только дубликаты с помощью условного форматирования и функции фильтрации
Показать только дубликаты с помощью Kutools для Excel 
Показать только дубликаты с помощью формул и функции фильтрации
Показать только дубликаты с помощью условного форматирования и функции фильтрации
Этот метод использует условное форматирование Excel для визуального выделения повторяющихся значений в выбранном столбце. После выделения можно использовать функцию фильтрации, чтобы отобразить только эти дубликаты, оставив уникальные данные вне поля зрения. Этот подход особенно подходит для пользователей, которые предпочитают интуитивно понятное решение с использованием мыши и хотят избежать написания формул или кода.
Преимущества этого метода включают простоту и прямую визуальную обратную связь. Однако он лучше всего подходит для наборов данных среднего размера. Для частых или крупномасштабных операций или задач автоматизации см. другие решения ниже.
1. Выберите значения, которые нужно проверить на наличие дубликатов, затем нажмите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения. В диалоговом окне Повторяющиеся значения диалог, выберите Повторяющиеся из левого выпадающего списка, выберите желаемый формат выделения из правого выпадающего списка и нажмите OK. Смотрите скриншот:
Дубликаты будут выделены выбранным цветом.
2. Нажмите заголовок столбца (например, "Имя"), затем перейдите к Данные > Фильтр чтобы активировать функцию фильтрации.
3. Нажмите Фильтр значок в заголовке столбца. Из меню выберите Фильтр по цвету, затем выберите цвет, который вы установили для дубликатов на предыдущем шаге. Смотрите скриншот:
4. Нажмите OK. В результате останутся видимыми только строки, содержащие повторяющиеся значения, а уникальные значения будут скрыты из отфильтрованного списка.
Этот метод рекомендуется для быстрого анализа и разового просмотра; помните, если вы измените данные после применения условного форматирования, повторно примените фильтр, чтобы соответствующим образом обновить результаты.
Показать только дубликаты с помощью Kutools для Excel
Для пользователей, которые регулярно нуждаются в управлении или анализе повторяющихся и уникальных строк, Kutools для Excel предоставляет удобный и простой в использовании инструмент под названием Выбрать дубликаты и уникальные ячейки. Эта функция позволяет быстро находить и выбирать уникальные строки, чтобы вы могли скрыть их всего за несколько кликов, оставляя видимыми только повторяющиеся значения. Kutools для Excel особенно ценен для больших и сложных наборов данных, где ручные операции могут быть громоздкими или трудоемкими.
После бесплатной установки Kutools для Excel выполните следующие шаги:
1. Выберите диапазон данных, затем нажмите Kutools > Выбрать > Выбрать дубликаты и уникальные ячейки.
2. В диалоговом окне Выбрать дубликаты и уникальные ячейки проверьте Только уникальные значения и Выбрать всю строку. Нажмите OK. Появится диалоговое окно сводки, показывающее количество выбранных строк.
3. Нажмите OK чтобы закрыть диалоговое окно сводки. Теперь выбраны уникальные строки. Щелкните правой кнопкой мыши любой заголовок строки из выбранной группы, затем выберите Скрыть из контекстного меню.
С помощью этих шагов все уникальные строки будут скрыты, оставив только ваши повторяющиеся строки для анализа или дальнейших действий.
Этот подход особенно выгоден для повторяющихся рабочих процессов, сессий проверки данных или при совместной работе с большими таблицами данных, поскольку он ускоряет работу и снижает вероятность случайного пропуска данных.
Показать только дубликаты с помощью формул и функции фильтрации
Если вы предпочитаете метод, основанный полностью на собственных возможностях Excel, и хотите точный, гибкий контроль, использование формул в сочетании с функцией фильтрации может быть очень эффективным. Установив формулу для маркировки повторяющихся строк, вы сможете фильтровать только те строки для просмотра и дальнейшей работы.
Этот подход идеально подходит для пользователей, которые комфортно работают с формулами и хотят возможность настраивать или расширять логику, например, выявлять дубликаты в нескольких столбцах или применять условные проверки дубликатов. С другой стороны, установка формул для больших или динамических наборов данных может потребовать внимания при копировании формул и поддержании согласованности ссылок.
1. В пустой соседней ячейке вашего набора данных (например, в ячейке B2, если ваши данные начинаются в A2) введите следующую формулу, чтобы отметить повторяющиеся записи:
=COUNTIFS($A$2:$A$12,$A2)>1Перетащите маркер заполнения вниз через целевой диапазон. Результат TRUE в ячейке формулы указывает на дубликат, тогда как FALSE отмечает уникальное значение. Смотрите скриншот:
2. Нажмите заголовок вспомогательного столбца (B1, прямо над вашими формулами), затем нажмите Данные > Фильтр чтобы добавить элементы управления фильтром.
3. Используйте раскрывающийся фильтр во вспомогательном столбце, чтобы выбрать только TRUE, затем нажмите OK. Только повторяющиеся значения будут отображаться в основном столбце данных.
Примечание: Настройте A2:A12 в формуле по мере необходимости, исходя из вашего фактического диапазона данных. Для динамических наборов данных убедитесь, что вы охватываете весь потенциальный диапазон, или используйте таблицы Excel для более легкого управления столбцами.
При корректировке или расширении вашего набора данных повторно примените формулу и фильтр, чтобы поддерживать точные результаты. Если вы столкнетесь с несоответствиями, дважды проверьте диапазоны ссылок на ячейки, чтобы избежать частичного покрытия.
Показать только дубликаты с помощью кода VBA (Автоматическая фильтрация и отображение только дубликатов)
Для продвинутых пользователей Excel автоматизация процесса обнаружения и отображения повторяющихся строк может сэкономить значительное время, особенно при работе с большими или часто обновляемыми наборами данных. Используя макрос VBA, вы можете мгновенно фильтровать и просматривать только повторяющиеся строки в выбранном столбце — без ручного форматирования цветом или создания формул. Этот подход подходит, если вам нужна однократная или повторяемая процедура, которую можно легко применить к различным диапазонам данных.
Преимущества этого метода включают скорость, последовательность и пригодность для очень больших наборов данных. Однако вам нужно будет включить макросы и иметь хотя бы среднее знакомство с редактором Excel VBA. Если вы новичок в макросах, рассмотрите возможность сохранения работы заранее, так как операция макроса может не быть отменена обычной функцией «Отменить».
1. Перейдите в Разработчик > Visual Basic, чтобы открыть окно Microsoft Visual Basic for Applications. Нажмите Вставка > Модуль и вставьте следующий код в модуль:
Sub FilterDuplicatesOnly()
Dim rng As Range
Dim ws As Worksheet
Dim duplicateCol As Range
Dim lastRow As Long
Dim colNum As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set rng = Application.Selection
If rng Is Nothing Then
MsgBox "Please select a range first.", vbExclamation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, rng.Cells(1, 1).Column).End(xlUp).Row
colNum = rng.Column
' Add a temporary helper column next to the selected column
ws.Cells(1, colNum + 1).Value = "IsDuplicate"
ws.Range(ws.Cells(2, colNum + 1), ws.Cells(lastRow, colNum + 1)).Formula = "=COUNTIF(" & rng.Address & "," & ws.Cells(2, colNum).Address(False, False) & ")>1"
' Apply filter to show only duplicates
ws.Range(ws.Cells(1, colNum), ws.Cells(lastRow, colNum + 1)).AutoFilter Field:=2, Criteria1:="TRUE"
End Sub2. После ввода кода вернитесь в Excel и выберите столбец данных, где вы хотите фильтровать дубликаты (например, выберите все ячейки от A2 до A12, если ваши данные находятся в столбце A). Затем в редакторе VBA нажмите кнопку 
Выполнить чтобы выполнить макрос. Макрос вставит временный вспомогательный столбец, использует его для определения дубликатов, а затем автоматически отфильтрует, чтобы показать только строки с повторяющимися значениями.
Временный вспомогательный столбец необходим для этого типа автоматизации VBA. При желании вы можете удалить этот столбец после просмотра ваших повторяющихся данных.
Практический совет: Если вы столкнетесь с ошибками, убедитесь, что вы выбрали диапазон данных перед запуском макроса. Вы можете легко повторно запустить макрос при изменении данных, что делает его особенно полезным для периодической проверки дубликатов.
Этот макрос позволяет вам быстро сосредоточиться на ваших повторяющихся данных без повторяющихся шагов ручного форматирования, создания формул или настройки фильтров.
Показать только повторяющиеся значения
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек