Найти, выделить, отфильтровать, подсчитать и удалить дубликаты в Excel

Если нужно найти дублирующиеся ячейки только в одном столбце или списке, можно использовать COUNTIF функцию для быстрого поиска и подсчета дублирующихся данных.
11. Выберите пустую ячейку рядом со столбцом, в котором будете искать дубликаты.

2. Введите формулу =COUNTIF($C$3:$C$12, C3)>1 и нажмите клавишу Enter.

3. Перетащите маркер автозаполнения формулы, чтобы скопировать формулу в другие ячейки.

Примечания: В формуле =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 — это столбец или список, в котором вы будете искать дублирующиеся значения. Поскольку столбец остается неизменным при копировании формулы, обычно используется абсолютная ссылка с «$».
(2) C3 — это первая ячейка указанного столбца. Это относительная ссылка, так как она должна автоматически изменяться при копировании формулы в другие ячейки.
(3) Эта формула возвращает Да или Нет. Да означает, что соответствующее значение является дубликатом, а Нет указывает, что соответствующее значение уникально в столбце.
(4) Иногда значения Да или Нет могут быть неочевидны. Можно объединить исходную формулу с функцией IF, чтобы возвращать Дубликаты напрямую.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates","")

В некоторых случаях требуется сравнить два столбца и найти дублирующиеся значения. Например, у вас есть два списка имен, и вы хотите найти дубликаты во втором списке по сравнению с первым. Для этого можно использовать функции VLOOKUP и IFERROR.

1. Выберите пустую ячейку рядом со вторым списком имен.

2. Введите формулу =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),"") и нажмите клавишу Enter.

3. Перетащите маркер автозаполнения формулы, чтобы скопировать формулу в другие ячейки по необходимости.

Примечания: В приведенной выше формуле,
(1) D3 — это первая ячейка во втором списке имен. Ссылка должна изменяться автоматически при копировании формулы, поэтому она относительная.
(2) $B$3:$B$18 — это первый список имен. Это абсолютная ссылка, так как диапазон должен оставаться неизменным при копировании формулы.
(3) Если имя дублируется с именами из первого списка, формула вернет имя; иначе — пустую строку.
(4) Также можно использовать формулу =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates","") чтобы найти дублирующиеся имена во втором списке по сравнению с первым. Эта формула вернет "Duplicates" если соответствующее имя является дубликатом.

(5) Если нужно найти дубликаты в двух столбцах на разных листах, просто добавьте имя листа перед ссылкой на сравниваемый столбец. В нашем примере замените $B$3:$B$18 на Sheet1!$B$3:$B$18 в формуле.

Формулы, приведённые выше, не учитывают регистр при поиске дубликатов, то есть "apple" считается дубликатом "APPLE". Для поиска дубликатов с учетом регистра в одном столбце можно использовать формулу массива.

1. Выберите пустую ячейку рядом со столбцом, в котором будете искать дубликаты.

2. Введите формулу массива =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicate") и нажмите Ctrl + Shift + Enter.

3. Перетащите маркер автозаполнения формулы, чтобы скопировать формулу массива в другие ячейки.

Примечания: В приведенной выше формуле массива
(1) $C$3:$C$12 — это столбец, в котором нужно найти дублирующиеся значения. Ссылка абсолютная, так как диапазон остается неизменным при копировании формулы массива.
(2) C4 — это первая ячейка в столбце. Ссылка относительная, так как она должна изменяться при копировании формулы массива.
(3) Если соответствующая ячейка является дубликатом, формула массива вернет "Duplicate", иначе — пустую строку.

Можно воспользоваться функцией Использовать условное форматирование, чтобы быстро выделить дублирующиеся ячейки в столбце или диапазоне.

1. Выберите столбец, в котором хотите выделить дублирующиеся ячейки.

2. Перейдите на Главная > Использовать условное форматирование > Правила выделения ячеек > Дублирующиеся значения. Смотрите скриншот ниже:

3. В появившемся диалоговом окне Дублирующиеся значения выберите Дубликат из первого выпадающего списка, выберите вариант выделения из второго списка и нажмите кнопку OK.

Примечание: Если предустановленные варианты выделения не подходят, выберите Пользовательский формат во втором выпадающем списке, затем укажите цвет выделения, шрифт и границы ячеек в появившемся окне Формат ячеек.

Теперь все дублирующиеся ячейки будут выделены в выбранном столбце, как показано на скриншоте ниже.

Примечания:
(1) После выделения дублирующихся ячеек их можно легко отфильтровать. (Щелкните, чтобы узнать как)
(2) После выделения дублирующихся ячеек их также можно быстро удалить массово. (Щелкните, чтобы узнать как)

Некоторые пользователи предпочитают выделять строки на основе дублирующихся ячеек в определенном столбце. В этом случае можно создать пользовательское правило условного форматирования.

1. Выберите диапазон (без строки заголовка), в котором будете выделять строки на основе дублирующихся ячеек.

2. Перейдите на Главная > Использовать условное форматирование > Новое правило.

3. В диалоговом окне Новое правило форматирования,
(1) Щелкните, чтобы выбрать Использовать формулу для определения форматируемых ячеек опцию;
(2) Введите формулу =COUNTIF($C$3:$C$12,$C3)>1 в поле Форматировать значения, для которых эта формула возвращает истину ;
Советы: В формуле $C$3:$C$12 — это столбец с дублирующимися ячейками, а $C3 — первая ячейка в столбце.
(3) Нажмите кнопку Формат .

4. В диалоговом окне Формат ячеек укажите цвет заливки, шрифт, границы ячеек по необходимости и последовательно нажмите OK для сохранения настроек.

Теперь в выбранном диапазоне строки выделены на основе дублирующихся ячеек в указанном столбце. Смотрите скриншот:

Чтобы выделить дублирующиеся строки в определенном диапазоне, также можно воспользоваться функцией Использовать условное форматирование.

1. Выберите диапазон без строки заголовка.

2. Перейдите на Главная > Использовать условное форматирование > Новое правило.

3. В диалоговом окне Новое правило форматирования:
(1) Щелкните, чтобы выбрать Использовать формулу для определения форматируемых ячеек опцию;
(2) В поле Форматировать значения, для которых эта формула возвращает истину введите формулу =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Нажмите кнопку Формат .

Примечания: В формуле =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 — это первый столбец в диапазоне, а $B3 — первая ячейка в этом столбце;
(2) $C$3:$C$12 — второй столбец в диапазоне, а $C3 — первая ячейка в столбце;
(3) $D$3:$D$12 — третий столбец в диапазоне, а $D3 — первая ячейка в столбце;
(4) Если в вашем диапазоне больше столбцов, добавьте их ссылки и первые ячейки последовательно в формулу.

4. В диалоговом окне Формат ячеек укажите цвет выделения, шрифт, границы ячеек и т.д. по необходимости, затем последовательно нажмите OK для сохранения настроек.

Теперь дублирующиеся строки определены и выделены в выбранном диапазоне. Смотрите скриншот:

Вы могли заметить, что все дубликаты определяются или выделяются вышеуказанными методами. Иногда требуется увидеть только те элементы, которые дублируются, кроме первого вхождения. Это также можно сделать с помощью функции Использовать условное форматирование и другой формулы.

1. Выберите столбец с заголовком.
Советы: Если нужно выделить строки на основе дубликатов в одном столбце, кроме первого вхождения, выберите диапазон без строки заголовка.

2. Перейдите на Главная > Использовать условное форматирование > Новое правило.

3. В диалоговом окне Новое правило форматирования:
(1) Щелкните, чтобы выделить Использовать формулу для определения форматируемых ячеек опцию;
(2) В поле Форматировать значения, для которых эта формула возвращает истину введите формулу =COUNTIF($C$3:$C3, C3)>1;
Советы: Чтобы выделить строки на основе дубликатов в одном столбце, введите формулу =COUNTIF($C$3:$C3, $C3)>1.
(3) Нажмите кнопку Формат .

4. В появившемся диалоговом окне Формат ячеек укажите цвет выделения, шрифт, границы ячеек и т.д. по необходимости, затем нажмите OK для сохранения настроек.

Теперь вы увидите, что дублирующиеся ячейки, кроме первого вхождения (или строки на основе дубликатов в указанном столбце), выделены. Смотрите скриншот:

Когда мы выделяем дублирующиеся ячейки или строки с помощью функции Использовать условное форматирование, все дубликаты выделяются одним цветом. Однако, если разные серии дубликатов выделить разными цветами, различия будут заметнее. Для этого можно воспользоваться VBA в Excel.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В этом окне выберите Вставка > Модуль и вставьте приведённый ниже код в новое окно модуля.

VBA: Выделить дублирующиеся ячейки разными цветами:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Нажмите клавишу F5 или щелкните значок Выполнить для запуска этого макроса.

4. В появившемся диалоговом окне Kutools для Excel выберите столбец, в котором хотите выделить дублирующиеся ячейки разными цветами, и нажмите OK.

Теперь каждая серия дублирующихся значений будет выделена своим цветом.

Первый инструмент — это функция Выбрать дубликаты и уникальные ячейки, предоставляемая Kutools для Excel. Она позволяет быстро находить уникальные или дублирующиеся ячейки.

1. Выберите столбец или диапазон, в котором хотите найти и выделить дублирующиеся ячейки.

2. Перейдите в Kutools > Выбрать > Выбрать дубликаты и уникальные ячейки.

3. В диалоговом окне Выбрать дубликаты и уникальные ячейки установите нужные параметры и нажмите Ok для завершения операции.

Примечания: В диалоговом окне Выбрать дубликаты и уникальные ячейки
(1) Если нужно выбрать или выделить все дубликаты, кроме первого вхождения, установите флажок Дубликаты (исключая первое совпадение) . В противном случае установите флажок Дубликаты (включая первое совпадение) .
(2) Чтобы выделить дубликаты, отметьте Цвет фона заливки и укажите нужный цвет выделения.
(3) Чтобы выбрать или выделить строки на основе дубликатов в выбранном столбце, отметьте Выбрать всю строку .
(4) Чтобы выбрать или выделить дублирующиеся значения с учетом регистра, отметьте Учет регистра .

Kutools для Excel также предлагает удобный инструмент — Сравнить ячейки, который помогает легко находить и выделять дублирующиеся ячейки в двух столбцах.

1. Перейдите в Kutools > Сравнить ячейки, чтобы открыть диалоговое окно Выбрать одинаковые и разные ячейки.

2. В диалоговом окне Выбрать одинаковые и разные ячейки укажите два столбца в полях Найти значения в и По сравнению с, отметьте опцию То же значение и другие параметры по необходимости.

Примечания:
(1) Чтобы найти дублирующиеся строки, отметьте По строке ; чтобы найти дублирующиеся ячейки, отметьте По ячейке в разделе Метод ;
(2) Отметьте Цвет фона заливки и укажите цвет, если нужно выделить дублирующиеся строки или ячейки;
(3) Отметьте Выбрать всю строку если нужно выбрать или выделить всю строку на основе дубликатов;
(4) Отметьте Учет регистра если хотите найти или выделить дубликаты с учетом регистра.

3. Последовательно нажмите Ok для завершения настроек.

Теперь вы увидите, что дубликаты в столбце, который вы указали в поле Найти значения в, определены и выделены.

Этот способ поможет определить и выделить дублирующиеся ячейки с помощью правила условного форматирования, а затем легко отфильтровать их по цвету выделения в Excel.

1. Примените условное форматирование для поиска и выделения дубликатов в указанном столбце. (Щелкните, чтобы узнать как)

2. Щелкните по заголовку указанного столбца и выберите Данные > Фильтр.

3. Затем щелкните значок фильтра в заголовке столбца и выберите Фильтр по цвету, затем выберите нужный цвет условного форматирования в выпадающем списке. Смотрите скриншот:

Теперь будут отфильтрованы только строки с дублирующимися ячейками. Смотрите скриншот:

В качестве альтернативы можно определить дубликаты с помощью формулы во вспомогательном столбце, а затем легко отфильтровать их по этому столбцу в Excel.

1. Добавьте вспомогательный столбец рядом с исходными данными и укажите в заголовке столбца Дубликат.

2. Выберите первую пустую ячейку под заголовком столбца, введите формулу =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate","") и перетащите маркер автозаполнения, чтобы скопировать формулу в другие ячейки.

Примечание: В приведенной выше формуле $C$3:$C$12 — это столбец с дублирующимися данными, а C3 — первая ячейка (кроме заголовка) в столбце.

3. Щелкните по заголовку столбца — Дубликаты, и выберите Данные > Фильтр.

4. Затем щелкните значок фильтра в заголовке столбца, отметьте только Дубликат, и нажмите OK . Смотрите скриншот:

Теперь будут отфильтрованы только строки с дублирующимися значениями. Смотрите скриншот:

Обычно можно использовать формулу =COUNTIF(диапазон, критерий) для подсчета общего количества определенного значения в указанном диапазоне. Например, чтобы узнать, сколько раз «Apple» встречается в списке A2:A10, используйте формулу =COUNTIF(A2:A10, "Apple").

Однако формула =COUNTIF(диапазон, критерий) считает только указанное дублирующееся значение. Как быть, если нужно подсчитать дубликаты по двум или нескольким условиям? А если нужно учитывать регистр? Следующие методы помогут решить эти задачи.

Можно использовать формулу массива для подсчета дублирующихся значений с учетом регистра и условий в Excel. Например, чтобы узнать, сколько раз значение «Apple» встречается в списке B2:B21 с учетом регистра, выполните следующие действия:

1. Выберите пустую ячейку.

2. Введите формулу =SUM(--EXACT(B2:B20,E2)).

3. Нажмите Ctrl + Shift + Enter, чтобы получить результат.

Примечания: В формуле массива
(1) B2:B20 — это столбец, в котором нужно подсчитать дубликаты. (2) E2 — это ячейка с указанным значением, количество вхождений которого нужно подсчитать. Можно заменить ссылку на значение в кавычках, например "Apple".

Иногда требуется подсчитать дубликаты по двум или более условиям. Для этого можно использовать функцию COUNTIFS .
Например, есть таблица продаж фруктов, как показано на скриншоте ниже. Нужно подсчитать, сколько раз продавалось яблоко05.07.2020 и сумма продаж превышает300. Для этого используйте формулу:

1. Выберите пустую ячейку.

2. Введите формулу =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Нажмите клавишу Enter, чтобы получить результат.

Примечания: В приведенной выше формуле
(1) B3:B20 — это (первый) столбец с датой, а G4 — критерий по дате;
(2) C3:C20 — (второй) столбец с фруктами, а G3 — критерий по фрукту;
(3) D3:D20 — (третий) столбец с суммой, а ">300" — критерий по сумме.
(4) Если в вашей таблице больше столбцов и условий, добавьте их ссылки и критерии.

Чтобы подсчитать все дубликаты в столбце, кроме первого вхождения, выполните следующие действия:

1. Выберите пустую ячейку рядом со столбцом.

2. Введите формулу =IF(COUNTIF($B$3:B3,B3)>1,"YES","")и перетащите маркер автозаполнения вниз, чтобы скопировать формулу в другие ячейки.

Примечания: В приведенной выше формуле
(1) $B$3:B3 — это диапазон, в котором подсчитываются дубликаты. В $B$3:B3 ячейка B3 будет автоматически изменяться при копировании формулы.
(2) B3 — первая ячейка в указанном столбце.
(3) Эта формула возвращает YES или пустую строку. YES означает, что соответствующее значение — дубликат, пустая строка — уникальное значение.

Теперь все дубликаты в указанном столбце определены. Можно подсчитать результаты формулы, чтобы получить общее количество дубликатов.

3. Выберите пустую ячейку.

4. Введите формулу =COUNTIF(C3:C16,"YES")и нажмите Enter .

Примечания: В приведенной выше формуле
(1) C3:C16 — это диапазон, в котором применялась формула для определения дубликатов на предыдущем шаге.
(2) YES — это значение, возвращаемое предыдущей формулой.

Теперь мы получили общее количество дублирующихся значений в указанном столбце. Первое вхождение не учитывается.

Чтобы подсчитать количество всех дубликатов, включая первое вхождение, используйте формулу массива.

1. Выберите пустую ячейку.

2. Введите формулу =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. Нажмите Ctrl + Shift + Enter, чтобы получить результат.

Примечание: В приведенной выше формуле B3:B16 — это указанный столбец, в котором нужно подсчитать дубликаты, включая первое вхождение.

Чтобы упростить работу и не запоминать длинные формулы, воспользуйтесь функцией Выбрать дубликаты и уникальные ячейки, предоставляемой Kutools для Excel, чтобы быстро подсчитать количество дублирующихся значений в указанном списке или столбце с учетом или без учета первого вхождения.

1. Выберите столбец, в котором нужно подсчитать количество дублирующихся значений, и перейдите в Kutools > Выбрать > Выбрать дубликаты и уникальные ячейки.

2. В диалоговом окне Выбрать дубликаты и уникальные ячейки отметьте опцию Дубликаты (исключая первое совпадение) или Дубликаты (включая первое совпадение) по необходимости и нажмите Ok.

3. Теперь все дублирующиеся значения, включая или исключая первое вхождение, выбраны, и одновременно появляется диалоговое окно с количеством выбранных ячеек. Смотрите скриншот выше.

Допустим, вы хотите сравнить два списка имен и подсчитать количество дубликатов между ними. Как быстро решить эту задачу? Это также можно сделать с помощью формулы в Excel.

1. Выберите пустую ячейку.

2. Введите формулу =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Нажмите клавишу Enter .

Примечания: В приведенной выше формуле
(1) B3:B12 — это первый столбец имен, в котором нужно подсчитать дубликаты.
(2) D3:D18 — это второй столбец имен, по которому производится сравнение.

В качестве альтернативы можно воспользоваться сторонним дополнением Kutools для Excel, чтобы быстро подсчитать общее количество дублирующихся ячеек между двумя столбцами.

1. Перейдите в Kutools > Выбрать > Выбрать одинаковые и разные ячейки.

2. В диалоговом окне Выбрать одинаковые и разные ячейки
(1) Укажите два столбца в полях Найти значения в и По сравнению с отдельно.
(2) Отметьте По ячейке .
(3) Отметьте То же значение .

4. Нажмите кнопку Ok.

Теперь все дублирующиеся ячейки в первом столбце выбраны, и одновременно появляется диалоговое окно с количеством выбранных дубликатов. Смотрите скриншот:

Примечание: Эта функция подсчитывает общее количество дублирующихся значений в столбце, который вы указали в поле Найти значения в диалогового окна Выбрать одинаковые и разные ячейки. Если нужно подсчитать общее количество дубликатов во втором столбце, используйте функцию Выбрать одинаковые и разные ячейки снова, указав второй столбец в поле Найти значения в.

Можно быстро подсчитать каждое дублирующееся значение только один раз с помощью формулы:

1. Выберите пустую ячейку.

2. Введите формулу =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")) .

3. Нажмите клавишу Enter, чтобы получить результат.

Примечание: В приведенной выше формуле C3:C19 — это указанный столбец, в котором нужно подсчитать каждое дублирующееся значение только один раз.

При подсчете списка можно подсчитать каждое дублирующееся значение только один раз с учетом регистра, используя формулу массива в Excel.

1. Выберите пустую ячейку.

2. Введите формулу массива =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))),0),0)).

3. Нажмите Ctrl + Shift + Enter, чтобы получить результат.

Примечание: В приведенной выше формуле массива C3:C19 — это указанный столбец, в котором нужно подсчитать каждую серию дубликатов только один раз с учетом регистра.

Если у вас установлен Kutools для Excel, вы также можете воспользоваться функцией Извлечение уникальных ячеек в диапазоне (включая первое дублирование), чтобы быстро подсчитать каждую серию дублирующихся значений только один раз в Excel.

1. Выберите пустую ячейку.

2. Перейдите в Kutools > Помощник формул > Подсчет > Извлечение уникальных ячеек в диапазоне (включая первое дублирование).

3. В диалоговом окне Помощник формул укажите столбец, в котором нужно подсчитать дубликаты только один раз, в поле Диапазон и нажмите Ok.

Результат подсчета сразу появится в выбранной ячейке.

Можно воспользоваться функцией Промежуточные итоги для подсчета каждой серии дублирующихся значений в столбце в Excel.

1. Выберите столбец, в котором нужно подсчитать каждую серию дублирующихся значений, и нажмите Данные > Сортировка по возрастанию или Сортировка по убыванию.

2. В появившемся диалоговом окне Предупреждение о сортировке выберите опцию Развернуть выделение и нажмите кнопку Сортировка.

Теперь вы увидите, что выделение отсортировано по дублирующимся значениям указанного столбца.

3. Оставьте выделение и нажмите Данные > Промежуточные итоги.

4. В диалоговом окне Промежуточные итоги
(1) Выберите указанный столбец в списке При каждом изменении в ;
(2) Выберите Количество в списке Использовать функцию ;
(3) Отметьте только указанный столбец в списке Добавить промежуточные итоги к ;
(4) Нажмите кнопку OK .

Теперь каждая серия дублирующихся значений подсчитана, а результат подсчета добавлен под каждой серией дубликатов, смотрите скриншот выше.

Также можно создать сводную таблицу для быстрого массового подсчета каждой серии дублирующихся значений в Excel.

1. Выберите диапазон, содержащий указанный столбец, и перейдите в Вставка > Сводная таблица.

2. В диалоговом окне Создать сводную таблицу укажите место для новой сводной таблицы и нажмите OK.

3. В области Поля сводной таблицы перетащите указанный столбец в разделы Строки и Значения . Теперь вы увидите, что каждая серия дублирующихся значений в указанном столбце подсчитана массово. Смотрите скриншот:

Если у вас уже установлен Kutools для Excel, воспользуйтесь удобной функцией Расширенное объединение строк для быстрого массового подсчета каждой серии дублирующихся значений в указанном столбце.

Примечание: Функция Расширенное объединение строк изменяет выбранный диапазон и удаляет строки на основе дублирующихся значений в указанном ключевом столбце. Чтобы сохранить данные, рекомендуется сделать резервную копию или скопировать данные в другое место перед выполнением следующих операций.

1. Добавьте пустой столбец справа от исходного диапазона данных и назовите новый столбец Количество.

2. Выделите исходный диапазон данных и новый столбец вместе, затем перейдите в Kutools > Объединить и разделить > Расширенное объединение строк.

3. В диалоговом окне Расширенное объединение строк
(1) Щелкните, чтобы выбрать указанный столбец, по которому нужно подсчитать каждую серию дублирующихся значений, и нажмите Ключевой столбец.
(2) Щелкните, чтобы выбрать новый столбец (Количество), затем нажмите Вычислить > Количество.
(3) При необходимости укажите типы комбинации или вычисления для других столбцов.
(4) Нажмите кнопку Ok .

Теперь каждая серия дублирующихся значений в указанном столбце подсчитана массово. Смотрите скриншот:

Допустим, в столбце есть список фруктов, некоторые из которых встречаются несколько раз. Теперь нужно отметить каждый дублирующийся фрукт в порядке появления. Как это сделать? В этом разделе представлена формула для решения задачи в Excel.

1. Добавьте пустой столбец справа от исходных данных.

2. Введите формулу =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") в первую ячейку добавленного столбца.

3. Перетащите маркер автозаполнения этой формулы, чтобы скопировать формулу в другие ячейки.

Примечания: В приведенной выше формуле
(1) $C$3:$C$14 — это указанный столбец, в котором нужно подсчитать дублирующиеся значения по порядку.
(2) C3 — первая ячейка в указанном столбце.
(3) Если соответствующее значение — дубликат, формула вернет порядковый номер1,2,3… по порядку появления; если значение уникально, формула вернет пустую строку.

Можно воспользоваться функцией Удалить дубликаты для удаления всех дублирующихся значений, кроме первого вхождения, напрямую.

1. Выберите столбец, в котором хотите удалить все дублирующиеся значения, кроме первого вхождения.

2. Перейдите в Данные > Удалить дубликаты.

3. В диалоговом окне Предупреждение об удалении дубликатов выберите опцию Продолжить с текущим выделением и нажмите кнопку Удалить дубликаты.

Совет: Чтобы удалить строки на основе дублирующихся значений в выделении, выберите опцию Развернуть выделение .

4. В диалоговом окне Удалить дубликаты отметьте только указанный столбец и нажмите OK.

Совет: Если на предыдущем шаге выбрана опция Развернуть выделение, здесь будут перечислены все столбцы. Тем не менее, отметьте только нужный столбец.

5. Затем появится диалоговое окно с количеством удалённых дубликатов. Нажмите OK для закрытия.

Также можно воспользоваться функцией Расширенный фильтр для удаления всех дублирующихся значений из указанного столбца.

1. Перейдите в Данные > Дополнительно.

2. В диалоговом окне Расширенный фильтр
(1) Отметьте Копировать в другое место ;
(2) В поле Диапазон списка выберите указанный столбец, из которого нужно удалить дублирующиеся значения;
(3) В поле Копировать в укажите диапазон для вставки столбца;
(4) Отметьте Только уникальные записи .
(5) Нажмите кнопку OK .

Теперь указанный столбец скопирован в указанный диапазон без дублирующихся значений, кроме первого вхождения. Смотрите скриншот:

Также можно воспользоваться VBA для быстрого удаления дублирующихся значений, кроме первого вхождения, из столбца в Excel.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Application.

2. Перейдите в Вставка > Модуль и вставьте приведённый ниже код VBA в новое окно модуля.

VBA: Удалить дублирующиеся значения, кроме первого вхождения

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Нажмите клавишу F5 или кнопку Выполнить для запуска макроса.

4. В появившемся диалоговом окне укажите диапазон, из которого нужно удалить дублирующиеся значения, и нажмите OK.

Все дублирующиеся значения, кроме первого вхождения, будут немедленно удалены из указанного диапазона.

Примечание: Этот код VBA учитывает регистр.

Можно выделить все дублирующиеся значения, включая первое вхождение, в столбце или списке с помощью правила условного форматирования, затем отфильтровать их по цвету выделения и удалить массово.

1. Примените условное форматирование для выделения дублирующихся значений. (Щелкните, чтобы узнать как)

2. Выберите столбец, из которого нужно удалить дублирующиеся значения (включая первое вхождение), и перейдите в Данные > Фильтр.

3. Щелкните значок фильтра в заголовке указанного столбца. В выпадающем меню выберите Фильтр по цвету, затем укажите цвет выделения из подменю.

Теперь все дублирующиеся значения отфильтрованы.

4. Выделите все отфильтрованные ячейки, щелкните правой кнопкой мыши и выберите Удалить строку в контекстном меню. В появившемся диалоговом окне подтверждения нажмите OK для продолжения.

5. Теперь все дублирующиеся значения удалены массово. Оставьте выделенный список и снова выберите Фильтр > Данные для отмены фильтра.

Теперь все дублирующиеся ячейки, включая первое вхождение, удалены массово, и остались только уникальные значения.

Примечание: Этот способ удаляет строки на основе дублирующихся значений в указанном столбце.

Также можно воспользоваться формулой для определения дублирующихся значений, включая первое вхождение, во вспомогательном столбце, затем отфильтровать их по результатам формулы и удалить массово.

1. Добавьте вспомогательный столбец рядом с указанным столбцом, введите формулу =COUNTIF($B$3:$B$11,B3) в первую ячейку вспомогательного столбца и перетащите маркер автозаполнения вниз, чтобы скопировать формулу в другие ячейки. Смотрите скриншот:

Примечание: В приведенной выше формуле $B$3:$B$11 — это указанный столбец, из которого нужно удалить дублирующиеся значения, а B3 — первая ячейка в указанном столбце.

2. Выделите вспомогательный столбец и перейдите в Данные > Фильтр.

3. Щелкните значок фильтра в заголовке вспомогательного столбца, затем в выпадающем меню отметьте все значения, кроме 1, и нажмите OK . Смотрите скриншот:

4. Теперь все дублирующиеся значения отфильтрованы. Выделите отфильтрованные ячейки во вспомогательном столбце, щелкните правой кнопкой мыши и выберите Удалить строку в контекстном меню.

5. В появившемся диалоговом окне подтверждения нажмите OK для продолжения.

6. Теперь все дублирующиеся значения и их строки удалены массово. Затем снова выберите Данные > Фильтр для отмены фильтра.

Теперь все дублирующиеся значения, включая первое вхождение, удалены массово.

Если у вас установлен Kutools для Excel, воспользуйтесь функцией Выбрать дубликаты и уникальные ячейки для быстрого выделения и удаления дублирующихся значений, включая или исключая первое вхождение, в Excel.

1. Выберите столбец, из которого нужно удалить дубликаты.

2. Перейдите в Kutools > Выбрать > Выбрать дубликаты и уникальные ячейки.

3. В диалоговом окне Выбрать дубликаты и уникальные ячейки отметьте опцию Дубликаты (включая первое совпадение) и нажмите Ok.

Примечания:
(1) Чтобы выделить и удалить дубликаты, исключая первое вхождение, отметьте Дубликаты (исключая первое совпадение) .
(2) Чтобы выделить и удалить строки на основе дублирующихся значений в указанном столбце, отметьте Выбрать всю строку .
(3) Чтобы выделить и удалить дубликаты с учетом регистра, отметьте Учет регистра .
(4) Чтобы выделить, подсветить и удалить дублирующиеся ячейки или строки, отметьте Цвет фона заливки или Цвет шрифта заливки и укажите нужные цвета заливки или шрифта.

4. Затем появится диалоговое окно с количеством выбранных ячеек, нажмите OK для закрытия.

5. Щелкните правой кнопкой мыши по выбранным ячейкам и выберите Удалить в контекстном меню.

6. В появившемся диалоговом окне Удалить отметьте опцию Сдвинуть ячейки вверх и нажмите OK.

Теперь все дублирующиеся значения, включая первое вхождение, удалены массово.

В большинстве случаев мы определяем дублирующиеся значения в столбце, а затем удаляем целые строки по этим дубликатам. По сути, эта операция аналогична удалению дубликатов из одного столбца, поэтому можно использовать аналогичные решения для удаления строк на основе дубликатов в указанном столбце.

Первый способ — воспользоваться встроенной функцией Удалить дубликаты для удаления строк по дубликатам в указанном столбце. Просто выберите диапазон для удаления строк, перейдите в Данные > Удалить дубликаты, отметьте только нужный столбец в диалоговом окне Удалить дубликаты и нажмите OK для завершения операции.

Также можно воспользоваться функциями Использовать условное форматирование и Фильтр для удаления строк на основе дублирующихся значений в указанном столбце. Сначала выделите строки на основе дублирующихся значений с помощью правила условного форматирования (щелкните, чтобы узнать как). Затем отфильтруйте диапазон по цвету. После этого удалите все отфильтрованные строки. В конце очистите или отмените фильтр, и останутся только строки с уникальными значениями в указанном столбце.

В качестве альтернативы можно добавить вспомогательный столбец и использовать формулу =COUNTIF($C$3:$C$21,C3) для определения дубликатов в указанном столбце. Затем отфильтруйте значения больше1 во вспомогательном столбце и удалите все отфильтрованные строки. После очистки фильтра останутся только строки с уникальными значениями в указанном столбце.

Стороннее дополнение Kutools для Excel также предлагает очень удобную функцию Выбрать дубликаты и уникальные ячейки для быстрого выбора строк на основе дублирующихся значений в указанном столбце, после чего вы можете легко удалить выбранные строки через контекстное меню.

Функция Расширенное объединение строк в Kutools для Excel также позволяет быстро удалять строки на основе дублирующихся значений в указанном ключевом столбце.

Можно добавить вспомогательный столбец и воспользоваться формулой для определения дублирующихся значений между двумя столбцами, а затем отфильтровать и удалить дубликаты.

1. Добавьте пустой столбец рядом с указанным столбцом, из которого нужно удалить дублирующиеся значения.

2. В первой ячейке вспомогательного столбца (без учета заголовка) введите формулу =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique","Duplicate") и перетащите маркер автозаполнения вниз, чтобы скопировать формулу в другие ячейки.

Примечания: В приведенной выше формуле
(1) C2 — первая ячейка в указанном столбце, из которого нужно удалить дубликаты;
(2) $A$2:$A$13 — это другой столбец для сравнения.
(3) Эта формула возвращает Дубликат если соответствующее значение дублируется с данными из другого столбца, и возвращает Уникальное если отличается от значений в другом столбце.

3. Выделите вспомогательный столбец и перейдите в Данные > Фильтр.

4. Щелкните значок фильтра в заголовке вспомогательного столбца, затем в выпадающем меню отметьте только Дубликат, и нажмите OK .

5. Теперь все дублирующиеся значения отфильтрованы. Выделите отфильтрованные ячейки, щелкните правой кнопкой мыши и выберите Удалить строку в контекстном меню. Затем нажмите OK в появившемся диалоговом окне подтверждения.

6. Теперь все дублирующиеся значения удалены из указанного столбца. Затем снова выберите Данные > Фильтр для отмены фильтра.

Теперь в указанном столбце остались только уникальные значения. Вспомогательный столбец можно удалить по необходимости.

Примечание: Этот способ удаляет целые строки на основе дублирующихся значений в указанном столбце.

Если у вас установлен Kutools для Excel, воспользуйтесь функцией Выбрать одинаковые и разные ячейки для быстрого выделения дублирующихся значений между двумя столбцами и их удаления.

1. Перейдите в Kutools > Выбрать > Выбрать одинаковые и разные ячейки для активации функции.

2. В диалоговом окне Выбрать одинаковые и разные ячейки укажите оба столбца в полях Найти значения в и По сравнению с, отметьте опции По ячейке и То же значение, затем нажмите Ok. Смотрите скриншот:

3. Теперь все дублирующиеся значения между двумя столбцами выбраны в первом столбце (указанном в поле Найти значения в). Затем нажмите OK в появившемся диалоговом окне.

4. Можно нажать клавишу Delete для удаления этих дубликатов напрямую или щелкнуть правой кнопкой мыши и выбрать Удалить в контекстном меню.