Как отобразить в столбце Excel только дублирующиеся строки?
В различных сценариях анализа данных вы можете столкнуться с ситуацией, когда столбец содержит как уникальные, так и дублирующиеся значения, но вам нужно видеть или обрабатывать только дублирующиеся строки. Поиск и ручное скрытие каждого уникального значения по отдельности может оказаться неэффективным и чреватым ошибками, особенно при работе с большими наборами данных. На рисунке ниже приведён пример: после фильтрации отображаются только дублирующиеся записи.
Для эффективного решения этой задачи Excel предлагает несколько подходов — от встроенных функций до расширенных надстроек и решений автоматизации. Эти методы существенно сокращают объём ручной работы, минимизируют риск ошибок и одинаково хорошо работают с наборами данных любого размера. Ниже приведены практические решения, которые помогут вам выбрать оптимальный вариант с учётом ваших задач и уровня владения Excel.
Отображение только дубликатов с помощью Использовать условное форматирование и функции фильтрации
Отображение только дубликатов с помощью Kutools для Excel 
Отображение только дубликатов с помощью формул и функции фильтрации
Отображение только дубликатов с помощью кода VBA (автофильтр и отображение только дубликатов)
Отображение только дубликатов с помощью Использовать условное форматирование и функции фильтрации
Этот метод использует условное форматирование Excel для визуального выделения дублирующихся значений в выбранном столбце. После выделения можно применить фильтрацию, чтобы отобразить только дубликаты, скрыв уникальные данные. Такой подход идеально подходит пользователям, которые предпочитают интуитивное управление с помощью мыши и хотят избежать написания формул или кода.
Преимущества этого метода — простота и мгновенная визуальная обратная связь. Однако он идеально подходит лишь для наборов данных умеренного размера. Если вам нужны частые или масштабные операции, а также автоматизация, ознакомьтесь с другими решениями ниже.
1. Выделите значения, которые нужно проверить на дубликаты, затем нажмите Главная > Использовать условное форматирование > Правила выделения ячеек > Дублирующиеся значения. В диалоговом окне Дублирующиеся значения выберите Повторяющиеся значения в левом раскрывающемся списке, укажите нужный формат выделения в правом раскрывающемся списке и нажмите ОК. См. снимок экрана:
Дубликаты будут выделены выбранным вами цветом.
2. Щёлкните заголовок столбца (например, «Имя»), затем перейдите в Данные > Фильтр, чтобы активировать функцию фильтрации.
3. Нажмите значок Фильтр в заголовке столбца. В меню выберите Фильтровать по цвету, затем укажите цвет, который был задан для дубликатов на предыдущем шаге. См. снимок экрана:
4. Нажмите ОК. После этого отобразятся только строки с дублирующимися значениями, а уникальные значения исчезнут из отфильтрованного списка.
Этот метод рекомендуется для быстрого анализа и разовых проверок; помните, что если вы измените данные после применения Использовать условное форматирование, необходимо повторно применить фильтр, чтобы обновить результаты.
Отображение только дубликатов с помощью Kutools для Excel
Если вам регулярно приходится управлять или анализировать дублирующиеся и уникальные строки, Kutools для Excel предлагает удобную и простую в использовании утилиту под названием Выбрать дубликаты/уникальные ячейки. С её помощью вы сможете мгновенно находить и выделять уникальные строки, чтобы скрыть их всего за несколько кликов — и оставить видимыми только дублирующиеся значения. Kutools для Excel особенно незаменима при работе с большими и сложными наборами данных, где ручные операции становятся громоздкими и трудоёмкими.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выделите диапазон данных, затем нажмите Kutools > Выделить > Выбрать дубликаты/уникальные ячейки.
2. В диалоговом окне Выбрать дубликаты/уникальные ячейки установите флажки Уникальные значения и Выбрать всю строку. Нажмите ОК. Появится диалоговое окно со сводкой, в котором отображается количество выделенных строк.
3. Нажмите ОК, чтобы закрыть диалоговое окно со сводкой. Теперь выделены уникальные строки. Щёлкните правой кнопкой мыши любой заголовок строки из выделенной группы и выберите Скрыть в контекстном меню.
После выполнения этих действий все уникальные строки будут скрыты, и для анализа или дальнейших операций останутся только ваши дублирующиеся строки.
Этот подход особенно выгоден при выполнении повторяющихся рабочих процессов, сессий анализа данных или совместной работе с большими таблицами — он ускоряет задачи и снижает риск случайного пропуска данных.
Отображение только дубликатов с помощью формул и функции фильтрации
Если вы отдаете предпочтение методу, основанному исключительно на встроенных возможностях Excel, и стремитесь к точному и гибкому контролю, сочетание формул с функцией фильтрации окажется чрезвычайно эффективным. Создав формулу для пометки дублирующихся строк, вы сможете легко отфильтровать только их — для удобного просмотра и дальнейшей обработки.
Этот подход идеально подходит пользователям, уверенно работающим с формулами и желающим настраивать или расширять логику — например, выполнять поиск дубликатов сразу по нескольким столбцам или применять условные проверки на повторяющиеся значения. С другой стороны, создание формул для больших или динамических наборов данных может потребовать особого внимания при их копировании и поддержании согласованности ссылок.
1. В пустой ячейке рядом с вашим набором данных (например, в ячейке B2, если данные начинаются с A2) введите следующую формулу, чтобы пометить дублирующиеся записи:
=COUNTIFS($A$2:$A$12,$A2)>1Перетащите маркер заполнения вниз по целевому диапазону. Значение ИСТИНА в ячейке с формулой указывает на дубликат, тогда как ЛОЖЬ помечает уникальное значение. См. снимок экрана:
2. Click the header of your helper column (B1, directly above your formulas), затем нажмите Данные > Фильтр, чтобы добавить элементы управления фильтрацией.
3. Используйте раскрывающийся список фильтра во вспомогательном столбце, чтобы выбрать только ИСТИНА, затем нажмите ОК. В основном столбце данных будут отображаться только дублирующиеся значения.
Примечание: При необходимости скорректируйте диапазон A2:A12 в формуле в соответствии с вашим фактическим диапазоном данных. Для динамических наборов данных убедитесь, что охватывается весь потенциальный диапазон, либо используйте таблицы Excel — это упростит управление столбцами.
При изменении или расширении набора данных повторно примените формулу и фильтр, чтобы результаты оставались точными. В случае несоответствий дважды проверьте диапазоны ссылок на ячейки, чтобы избежать частичного охвата.
Отображение только дубликатов с помощью кода VBA (автофильтр и отображение только дубликатов)
Для опытных пользователей Excel автоматизация обнаружения и отображения дублирующихся строк может значительно сэкономить время, особенно при работе с большими или часто обновляемыми наборами данных. С помощью макроса VBA вы можете мгновенно отфильтровать и показать только дублирующиеся строки в выбранном столбце — без ручного цветового форматирования или создания формул. Такой подход идеален, если вам нужно решение «в один клик» или многократно повторяемый способ, который легко применять к разным диапазонам.
Преимущества этого метода — высокая скорость, надёжная последовательность и отличная масштабируемость для работы с очень большими наборами данных. Однако вам понадобится включить макросы и иметь хотя бы базовые навыки работы в редакторе VBA Excel. Если вы только начинаете осваивать макросы, обязательно сохраните файл заранее: операции, выполняемые макросами, как правило, невозможно отменить стандартной командой «Отменить».
1. Перейдите в меню Разработчик → Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Нажмите Вставка → Модуль и вставьте следующий код в модуль:
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек