Как удалить строки на основе дубликатов в одном столбце Excel?
В Excel удаление дублирующих строк — это простая задача с помощью функции Удалить дубликаты на вкладке Данные. Однако процесс удаления целых строк на основе дублирующихся значений только в одном столбце может быть не столь очевидным. В этой статье представлены несколько простых методов для эффективного решения этой задачи, обеспечивая точность и чистоту ваших данных. Удаление строк по дубликатам в указанном столбце может быть крайне важно при подготовке данных для отчётности, анализа или когда необходимо оставить только уникальные записи по определённому критерию. Ниже приведены методы — от встроенных возможностей Excel до решений с использованием формул, VBA и инструментов Kutools для Excel, — что позволяет выбрать наиболее подходящий способ в зависимости от ваших задач и рабочих процессов. Каждый метод имеет свои преимущества, обусловленные сложностью задачи, объёмом данных и уровнем владения инструментами Excel.
- Удалить строки на основе дубликатов в одном столбце с помощью функции Удалить дубликаты
- Удалить строки на основе дубликатов в одном столбце с помощью Kutools для Excel
- Используйте код VBA для Удалить строки с помощью Дублирующиеся значения в Укажите столбец
- Используйте формулы и вспомогательные столбцы для выявления и Удалить дубликаты в столбце
- Удалить строки на основе дубликатов в одном столбце с объединением/суммированием/усреднением/подсчётом значений из удалённых строк
Удалить строки на основе дубликатов в одном столбце с помощью функции Удалить дубликаты
Этот подход основан на использовании функции Excel Удалить дубликаты, которая без усилий удаляет всю строку, содержащую повторяющиеся значения в указанном столбце. Это одно из самых быстрых решений и идеально подходит, когда ваши данные уже структурированы, а вы хотите оставить только первое вхождение каждого повторяющегося значения.
- Выделите диапазон, в котором Вы хотите выполнить Удалить строки на основе дубликатов в одном столбце, и нажмите Данные>Удалить дубликаты.
- В появившемся диалоговом окне Удалить дубликаты установите флажок только напротив столбца, содержащего дублирующиеся значения, который будет использоваться в качестве основы для удаления всей строки, и нажмите кнопку OK.
Примечание: Внимательно проверьте выбранные столбцы. Оценке на наличие дубликатов подлежат только отмеченные столбцы. Если случайно отметить дополнительные столбцы, некоторые предполагаемые дубликаты могут остаться нетронутыми. - Появится следующее диалоговое окно с количеством найденных и удалённых дублирующихся значений. Нажмите OK, чтобы завершить и закрыть диалоговое окно. Это сообщение информирует о количестве удалённых дублирующихся строк и оставшихся уникальных строках для вашей справки.
Примечание: Внимательно проверьте выбранные столбцы. Оценке на наличие дубликатов подлежат только отмеченные столбцы. Если случайно отметить дополнительные столбцы, некоторые предполагаемые дубликаты могут остаться нетронутыми.
Преимущества: Идеально подходит для простых задач, не требует надстроек или формул и сохраняет первое вхождение каждого дубликата.
Недостатки: Удаляет все столбцы из строк с дубликатами и не позволяет предварительно «проверить» помеченные дубликаты перед удалением.
Удалить строки на основе дубликатов в одном столбце с помощью Kutools для Excel
С помощью утилиты Kutools для Excel Выбрать дубликаты/уникальные ячейки вы также можете удалять строки на основе всех дублирующихся значений в столбце, оставляя только строки с уникальными значениями. Кроме того, эта утилита позволяет выбрать: удалить строки с дубликатами, оставив только первое вхождение, или полностью удалить все дублирующиеся строки, включая их первые вхождения. Этот метод особенно подходит пользователям, которым нужно более гибкое и быстрое управление дубликатами по сравнению со встроенными средствами Excel.
- Выберите столбец с дублирующимися значениями, который будет использоваться в качестве основы для удаления строк, и нажмите Kutools > Выделить > Выбрать дубликаты/уникальные ячейки.
Совет: Рекомендуется выделять только тот столбец, по которому нужно проверять дубликаты, чтобы случайно не удалить данные из других столбцов. - В открывшемся диалоговом окне Выбрать дубликаты/уникальные ячейкиустановите флажок напротив опции Дубликаты (кроме первого)или опции Все дубликаты (включая первый)в разделе Правило. Затем установите флажок напротив опции Выбрать всю строкуи нажмите кнопку Ok.
Пояснение параметров:
•Дубликаты (кроме первого): Сохраняется первое вхождение. Все остальные дублирующиеся строки отмечаются для удаления.
•Все дубликаты (включая первый): Отмечаются все строки, в которых столбец содержит дублирующиеся значения, включая первое вхождение. - Появится диалоговое окно, как показано ниже, с количеством строк, выделенных на основе дублирующихся значений в выбранном столбце. Нажмите кнопку OK, чтобы закрыть его.
- Нажмите Главная > Удалить > Удалить строки листа, чтобы удалить выделенные строки с дублирующимися значениями. Таким образом вы удалите все строки на основе дублирующихся значений в указанном столбце.
Совет: Перед удалением рекомендуется сохранить резервную копию файла. Удалённые строки нельзя восстановить с помощью команды «Отменить» после выполнения других операций или сохранения и закрытия файла.
Совет: Рекомендуется выделять только тот столбец, по которому нужно проверять дубликаты, чтобы случайно не удалить данные из других столбцов.
Совет: Перед удалением рекомендуется сохранить резервную копию файла. Удалённые строки нельзя восстановить с помощью команды «Отменить» после выполнения других операций или сохранения и закрытия файла.Исходные данные:
Результаты после удаления дубликатов:
Если выбрана опция:Дубликаты (кроме первого)
Если выбрана опция:Все дубликаты (включая первый)
Преимущества: Гибкое выделение и предварительный просмотр, возможность удалить все или только нужные дубликаты — полный контроль над процессом у вас в руках.
Недостатки: Требуется установка.
Удалить строки на основе дубликатов в столбце с использованием кода VBA
Этот метод использует макрос VBA для автоматического перебора диапазона Выбрать данные и удаления Вся строка, если обнаружены Дублирующиеся значения в Укажите столбец. Такое решение особенно ценно при работе с большими наборами данных или при выполнении повторяющихся задач очистки и обеспечивает более высокий уровень автоматизации по сравнению с ручными методами. Перед запуском кода VBA всегда создавайте резервную копию книги, так как изменения, внесённые макросами, нельзя легко отменить.
- Нажмите Разработчик > Visual Basic, чтобы открыть окно редактора Microsoft Visual Basic для приложений. Если вкладка Разработчик не отображается, её можно включить через Файл > Параметры > Настройка ленты.
- В редакторе VBA нажмите Вставка>Модуль, чтобы создать новый модуль кода, затем вставьте следующий код в окно модуля:
Sub DeleteRowsWithDuplicateInColumn()
Dim rng As Range
Dim i As Long, j As Long
Dim lastRow As Long
Dim colNum As Long
Dim ws As Worksheet
Dim dict As Object
On Error Resume Next
Set ws = ActiveSheet
Set rng = Application.InputBox("Select your data range (including column headers)", "KutoolsforExcel", ws.UsedRange.Address, Type:=8)
If rng Is Nothing Then Exit Sub
colNum = Application.InputBox("Enter the column number to check for duplicates (e.g.,2 for column B):", "KutoolsforExcel", 1, Type:=1)
Set dict = CreateObject("Scripting.Dictionary")
lastRow = rng.Rows.Count
For i = lastRow To 2 Step -1
If dict.Exists(rng.Cells(i, colNum).Value) Then
rng.Rows(i).EntireRow.Delete
Else
dict.Add rng.Cells(i, colNum).Value, 1
End If
Next i
End Sub3. После ввода кода нажмите кнопку 
, чтобы выполнить его. Когда появится запрос, выберите ваш диапазон данных (включая заголовки), а затем укажите номер столбца, в котором нужно проверять дубликаты (например, введите)2 для столбца B).
Макрос автоматически пройдёт по данным снизу вверх и удалит все строки, содержащие повторяющиеся значения в указанном столбце, оставив только первое вхождение каждого уникального значения.
Определение и Удалить строки с дубликатами с помощью формул Excel и вспомогательных столбцов
Если вы предпочитаете решение на основе формул, вы можете использовать вспомогательные столбцы для пометки Дублирующиеся значения, а затем вручную удалить помеченные строки. Этот метод идеален, когда вы хотите визуально проверить, какие строки являются дубликатами, перед их удалением, обеспечивая прозрачность процесса, хотя некоторые шаги выполняются вручную.
1. Добавьте новый вспомогательный столбец в таблицу данных (например, в столбец D) и введите следующую формулу в пустую ячейку рядом с данными, например в D2:
=COUNTIF($A$2:$A2,A2)>12. Скопируйте или протяните эту формулу вниз по вспомогательному столбцу для всех соответствующих строк. Результат ИСТИНА помечает дубликат, тогда как ЛОЖЬ указывает на первое вхождение.
3. Примените функцию Фильтр к диапазону данных, включая вспомогательный столбец. Затем отфильтруйте вспомогательный столбец по значениям ИСТИНА — это дублирующиеся строки, кроме первого вхождения.
4. Выделите отфильтрованные строки и воспользуйтесь командой Главная > Удалить > Удалить строки листа, чтобы удалить их из таблицы. При необходимости удалите или скройте вспомогательный столбец после завершения.
5. После удаления дублирующих строк нажмите Фильтр, чтобы отменить фильтрацию и отобразить скрытые данные.
Пометить все Дублирующиеся значения, включая их первое вхождение:Используйте эту формулу во вспомогательном столбце:
=COUNTIF($A$2:$A$24,A2)>1После заполнения вниз все записи, в которых значение встречается более одного раза в ограниченном диапазоне, вернут результат ИСТИНА, независимо от позиции. Фильтрация и удаление, как описано выше, приведут к удалению всех строк с дублирующимися значениями, оставив только уникальные элементы.
Недостатки:Требует выполнения ручных действий для удаления, менее эффективен для очень больших наборов данных.
Удалить строки на основе дубликатов в одном столбце с объединением/суммированием/усреднением/подсчётом значений из удалённых строк
Если вы хотите удалить всю строку на основе дублирующихся значений в указанном столбце, но при этом сохранить данные из этих дубликатов, воспользуйтесь утилитой Kutools для Excel Расширенное объединение строк. Этот инструмент позволяет эффективно удалять строки с повторяющимися значениями в выбранном столбце и одновременно объединять, суммировать, усреднять или подсчитывать данные из удаляемых строк в других столбцах — сохраняя целостность и ценность вашего набора данных. Такой подход особенно полезен, когда ваш бизнес-процесс требует агрегации связанных данных после удаления дубликатов, например при консолидации записей о продажах или журналов транзакций.
- Выделите диапазон, в котором Вы хотите выполнить Удалить строки на основе дубликатов в одном столбце, и нажмите Kutools>Объединить и разделить>Расширенное объединение строк.
- В открывшемся диалоговом окне Расширенное объединение строк в качестве примера мы удалим строки с дублирующимися значениями в столбце «Fruit». Кроме того, мы объединим информацию о цене из столбца «Price» и просуммируем общее количество для каждого типа фруктов в столбце «Amount». Выполните следующие шаги, чтобы завершить операцию:
- В разделе Данные столбца диалогового окна найдите и выберите столбец «Fruit», содержащий дублирующиеся значения, на основе которого вы хотите выполнить удаление строк, а затем выберите Первичный ключ в раскрывающемся списке Операция.
- Выберите столбец «Price», в котором будут объединены все значения, соответствующие дубликатам из указанного столбца. Затем укажите разделитель в раскрывающемся списке Операция в разделе Объединить.
- Выберите столбец «Amount», к значениям которого будут применены вычисления (сумма, среднее, количество и т.д.) на основе дубликатов в указанном столбце. Затем в группе Вычислитьвыберите опцию Суммаиз раскрывающегося списка Операция.
- Нажмите кнопку OK. Все строки теперь удалены на основе дубликатов в указанном столбце, а данные из других столбцов автоматически объединены или рассчитаны.
Демонстрация: Удалить строки на основе дубликатов в одном столбце в Excel
См. также:
Подсчёт Дублирующиеся значения в столбце в Excel
Удаление Дублирующая строка с сохранением только максимальных значений в Excel
Скрытие строк при наличии дубликатов в Excel
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек