Поиск, выделение, фильтрация, подсчёт и удаление дубликатов в Excel

Чтобы найти дублирующиеся ячейки только в одном столбце или списке, используйте функцию СЧЁТЕСЛИ — она позволяет быстро и без усилий находить и подсчитывать повторяющиеся данные.
1. Выберите пустую ячейку рядом со столбцом, в котором вы хотите найти дубликаты.

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(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»,«»), чтобы найти и удалить дублирующиеся имена во втором списке по сравнению с первым. Эта формула вернёт значение «Дубликаты», если соответствующее имя является дубликатом.

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

Приведённые выше формулы при поиске дубликатов не учитывают регистр: например, «яблоко» будет считаться дубликатом «ЯБЛОКО». Чтобы найти дублирующиеся значения в одном столбце с учётом регистра, используйте формулу массива.

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

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

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

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

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

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

2. Нажмите Главная > Условное форматирование > Правила выделения ячеек > Дублирующиеся значения. См. снимок экрана ниже:

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

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

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

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

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

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

2. Нажмите Главная > Условное форматирование > Создать правило.

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

4. В диалоговом окне «Установить формат ячейки» задайте нужный цвет заливки, шрифт и границы ячеек, затем последовательно нажмите кнопки ОК, чтобы сохранить настройки.

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

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

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. В диалоговом окне «Установить формат ячейки» укажите цвет выделения, шрифт, границы ячеек и т. д. по необходимости, затем последовательно нажмите кнопки ОК, чтобы сохранить настройки.

На данный момент Дублирующая строка определены и выделены в Выберите диапазон. См. снимок экрана:

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

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

2. Щелкните Главная>Использовать условное форматирование>Создать правило.

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

4. В появившемся диалоговом окне «Установить формат ячейки» укажите цвет выделения, шрифт, границы ячеек и т. д. по необходимости, затем нажмите кнопку ОК, чтобы сохранить настройки.

Then you will see the duplicate cells except the first instances in the selected column (or rows based on duplicates in the specified column) выделены. См. снимок экрана:

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

1. Нажмите клавиши Alt+F11, чтобы открыть окно Microsoft Visual Basic для приложений.

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 или щелкните значок Выполнить, чтобы запустить этот макрос VBA.

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

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

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

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

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

3. В диалоговом окне «Выбрать дубликаты/уникальные ячейки» установите нужные параметры и нажмите кнопку ОК, чтобы завершить операцию.

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

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

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

2. В диалоговом окне «Выбрать одинаковые/разные ячейки» укажите два столбца в полях Найти значения в и Сравнивать с, установите флажок Одинаковые значения и при необходимости отметьте другие параметры.

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

3. Последовательно нажимайте кнопки ОК, чтобы завершить настройку.

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

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

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

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

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

После этого будут отображаться только строки с повторяющимися ячейками. См. снимок экрана:

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

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

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

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

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

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

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

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

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

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

1. Выделите пустую ячейку.

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

3. Нажмите сочетание клавиш Ctrl+Shift+Enter, чтобы получить результат подсчёта.

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

Иногда нужно подсчитать дубликаты по двум или более критериям. В таких случаях на помощь приходит функция СЧЁТЕСЛИМН (COUNTIFS).
Например, у вас есть таблица продаж фруктов, как на снимке экрана ниже. Необходимо определить, сколько раз встречается «яблоко», проданное 7/5/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 — это диапазон, в котором выполняется подсчёт дубликатов. При копировании формулы в другие ячейки значение B3 в выражении $B$3:B3 будет автоматически обновляться.
(2) B3 — первая ячейка в указанном столбце.
(3) Эта формула возвращает «ДА», если значение является дубликатом, или оставляет ячейку пустой, если значение уникально.

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

3. Выделите пустую ячейку.

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

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

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

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

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. В диалоговом окне «Выбрать дубликаты/уникальные ячейки» установите флажок напротив параметра Дубликаты (исключая первое совпадение) или Дубликаты (включая первое совпадение) — в зависимости от ваших задач — и нажмите кнопку ОК.

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. Нажмите кнопку ОК.

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

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

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

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. В диалоговом окне Помощник формул укажите в поле Диапазон столбец, в котором нужно подсчитать дубликаты, и нажмите кнопку ОК.

Затем результат подсчёта сразу же появится в выделенной ячейке.

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

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

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

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

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

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

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

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

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

2. В диалоговом окне «Создание сводной таблицы» укажите место для размещения новой сводной таблицы и нажмите кнопку ОК.

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

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

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

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

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

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

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

Допустим, в столбце содержится список фруктов. В этом списке некоторые фрукты встречаются многократно. Теперь вам нужно пометить каждый повторяющийся фрукт в порядке его появления. Как это можно сделать? В данном разделе представлена формула, позволяющая легко решить эту задачу в 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. В диалоговом окне «Удалить дубликаты» отметьте только указанный столбец и нажмите кнопку ОК.

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

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

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

1. Нажмите Данные > Расширенный.

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

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

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

1. Нажмите клавиши Alt+F11, чтобы открыть окно Microsoft Visual Basic для приложений.

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 или кнопку Выполнить, чтобы запустить этот код VBA.

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

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

Примечание. Этот код VBA является С учетом регистра.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. В появившемся диалоговом окне подтверждения нажмите кнопку ОК, чтобы продолжить.

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

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

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

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

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

3. В диалоговом окне «Выбрать дубликаты/уникальные ячейки» установите флажок напротив параметра Дубликаты (включая первое совпадение) и нажмите кнопку ОК.

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

4. Затем появится диалоговое окно с информацией о количестве выбранных ячеек. Нажмите кнопку ОК, чтобы закрыть его.

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

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

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

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

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

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

Кроме того, вы можете добавить вспомогательный столбец и применить формулу =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) эта формула вернёт значение Duplicate, если соответствующее значение дублируется со значениями в другом столбце, и вернёт значение Unique, если оно отличается от значений в другом столбце.

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

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

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

6. После этого все дублирующиеся значения будут удалены из указанного столбца. Чтобы отменить фильтрацию, снова нажмите Данные > Фильтр.

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

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

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

1. Нажмите Kutools > Выделить > Выбрать одинаковые/разные ячейки, чтобы активировать эту функцию.

2. В диалоговом окне «Выбрать одинаковые/разные ячейки» укажите оба столбца в полях Найти значения в и Сравнить с соответственно, отметьте параметры Одна ячейка и Одинаковые значения, затем нажмите кнопку Ok. См. снимок экрана:

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

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