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

2. Введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, и нажмите Enter .

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

Ноты: В формуле = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12, C3)> 1,
(1) 3 канадских доллара: 12 канадских долларов - это столбец или список, в котором вы найдете повторяющиеся значения. Поскольку столбец статичен, когда мы копируем формулу в другие ячейки, обычно это абсолютная ссылка со знаком «$».
(2) C3 - первая ячейка указанного столбца. Это относительная ссылка, потому что нам нужно, чтобы она изменялась автоматически, когда мы копируем формулу в другие ячейки.
(3) Эта формула вернет ИСТИНА or ФАЛАЗА. ИСТИНА означает, что соответствующее значение дублируется, а НЕПРАВДА указывает, что соответствующее значение уникально в столбце.
(4) Иногда ИСТИНА или ЛОЖЬ не могут быть поняты интуитивно. Мы можем объединить исходную формулу и функцию ЕСЛИ, чтобы вернуть Дубликаты непосредственно.
= ЕСЛИ (СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, "Дубликаты", "")

В некоторых случаях вам нужно сравнить два столбца и найти повторяющиеся значения. Например, у вас есть два списка имен, и вы хотите найти повторяющиеся имена во втором списке по сравнению с первым. вы можете применить функции ВПР и ЕСЛИОШИБКА, чтобы сделать это легко.

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

2. Введите формулу = ЕСЛИОШИБКА (ВПР (D3; $ B $ 3: $ B $ 18,1,0); ""), и нажмите Enter .

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

Ноты: В приведенной выше формуле
(1) D3 - первая ячейка во втором списке имен. Ссылка должна изменяться автоматически, когда мы копируем формулу в другие ячейки, в результате она относительна.
(2) 3 млрд долларов: 18 млрд долларов это первый список имен. Это абсолютная ссылка, потому что диапазон должен оставаться статическим, когда мы копируем формулу в другие ячейки.
(3) Когда имя дублируется с именами в первом списке, формула вернет имя; в противном случае возвращается пустое значение.
(4) Вы также можете применить формулу = ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: $ B $ 18; D3)> 0, "Дубликаты", "") найти повторяющиеся имена во втором списке по сравнению с первым списком. Эта формула вернет «Дубликаты» если соответствующее имя повторяется.

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

Формулы, представленные выше, не соответствуют регистру при поиске дубликатов, говорят, что они считают "яблоко" дубликатом с "ЯБЛОКО". Здесь вы можете применить формулу массива, чтобы найти повторяющиеся значения в одном столбце с совпадающими регистрами.

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

2. Введите формулу массива = ЕСЛИ (СУММ ((- ТОЧНО ($ C $ 3: $ C $ 12, C3))) <= 1, "", "Дублировать"), и нажмите Ctrl + Shift + Enter ключи.

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

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

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

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

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

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

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

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

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

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

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

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

3. В диалоговом окне "Новое правило форматирования"
(1) Щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант;
(2) Введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12, $ C3)> 1 в Формат значений, где эта формула истинна коробка;
Советы: В формуле 3 канадских доллара: 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) 3 млрд долларов: 12 млрд долларов - это первый столбец в диапазоне, а $ B3 это первая ячейка в этом столбце;
(2) 3 канадских доллара: 12 канадских долларов - второй столбец в диапазоне, а $ C3 это первая ячейка в столбце;
(3) $ D $ 3: $ D $ 12 - третий столбец диапазона, а $ D3 это первая ячейка в столбце;
(4) Если в вашем диапазоне больше столбцов, вы можете последовательно добавлять ссылки на столбцы и их первые ячейки в формулу.

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

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

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

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

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

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

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

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

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

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

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

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 ключ или щелкните Run значок для запуска этого VBA.

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

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

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

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

2. Нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки.

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

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

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

1. Нажмите Кутулс > Сравнить ячейки , чтобы открыть диалоговое окно Выбрать одинаковые и разные ячейки.

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

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

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

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

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

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

2. Щелкните, чтобы выбрать заголовок указанного столбца, и щелкните Данные > ФИЛЬТР.

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

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

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

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

2. Выберите первую пустую ячейку под заголовком столбца, введите формулу = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, "Дублировать", ""), и перетащите маркер автозаполнения этой ячейки, чтобы скопировать эту формулу в другие ячейки.

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

3. Щелкните, чтобы выбрать заголовок столбца - Дубликаты, и щелкните Данные > ФИЛЬТР.

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

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

В общем, мы можем применить = СЧЁТЕСЛИ (диапазон; критерии) для подсчета общего количества определенного значения, появляющегося в указанном диапазоне. Говорит считать, сколько раз «Яблоко» появляется в списке A2: A10, Мы можем применить формулу = СЧЁТЕСЛИ (A2: A10; «Яблоко») для подсчета количества этого повторяющегося значения.

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

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

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

2. Введите формулу = СУММ (- ТОЧНО (B2: B20; E2)).

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

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

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

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. Введите формулу = ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: B3; B3)> 1; "ДА"; ""), а затем перетащите маркер автозаполнения вниз, чтобы скопировать эту формулу в другие ячейки.

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

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

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

4. Введите формулу = СЧЁТЕСЛИ (C3: C16; «ДА»), и нажмите Enter .

Заметки: В формуле выше
(1) C3: C16 это диапазон, который мы применили по формуле для выявления дубликатов на последнем этапе.
(2) ДА - значение, возвращаемое последней формулой.

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

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

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

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

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

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

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

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

2. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» установите флажок Дубликаты (кроме 1-го) or Все дубликаты (включая 1-й) вариант, как вам нужно, и щелкните Ok .

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

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

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

2. Введите формулу = СУММПРОИЗВ (- (ISNUMBER (MATCH (B3: B12, D3: D18,0)))).

3, нажмите Enter .

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

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

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки.

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

4, Нажмите Ok .

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

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

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

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

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

3, нажмите Enter ключ для получения результата подсчета.

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

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

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

2. Введите формулу массива = СУММ (ЕСЛИОШИБКА (1 / ЕСЛИ (C3: C19 <> "", ЧАСТОТА (ЕСЛИ (ТОЧНО (C3: C19; ТРАНСПОЗИРОВАТЬ (C3: C19))), ПОИСКПОЗ (СТРОКА (C3: C19), СТРОКА (C3: C19) ), ""), МАТЧ (СТРОКА (C3: C19), СТРОКА (C3: C19))), 0), 0)).

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

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

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

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

2. Нажмите Кутулс > Формула Помощник > Статистический > Подсчет ячеек с уникальными значениями (включая первое повторяющееся значение).

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

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

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

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

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

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

3. Не снимая выделения, нажмите Данные > Сумма.

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

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

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

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

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

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

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

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

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

2. Выберите вместе исходный диапазон данных и новый столбец и нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

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

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

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

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

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

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

Заметки: В формуле выше
(1) 3 канадских доллара: 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, нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложения.

2. Нажмите Вставить > Модули, а затем вставьте ниже код VBA в новое окно модуля.

ниже 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 WorksAdume Dim String Далее xRg = Application.InputBox ("Выберите диапазон:", "Kutools for Excel", "",,,,, 8) Если xRg не имеет значения, то выйдите из поднабора xURg = Intersect (xRg.Worksheet.UsedRange, xRg) Установить xWs = xURg.Worksheet Установить xDc = CreateObject ("scripting.dictionary") xURgAddress = xURg.Address xBol = Application.ScreenUpdating Application.ScreenUpdating = False For xFNum = 1 To xURg.Count Установить xFRg = xURg.Item (xFNumror) Если (xFNumror) (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) УстановитьxURg = xWs.Range (xStr) Else xStr = xStr & "," & xDc_keys (xI) Установить 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 ключ или щелкните Run кнопку для запуска этого VBA.

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

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

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

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

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

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

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

Затем все повторяющиеся значения отфильтровываются.

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

5. Затем все повторяющиеся значения удаляются сразу. Не снимая выделения с отфильтрованного списка, нажмите ФИЛЬТР > Данные еще раз, чтобы отменить фильтр.

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

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

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

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

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

2. Выберите вспомогательный столбец и щелкните Данные > ФИЛЬТР.

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

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

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

6. Теперь все повторяющиеся значения и их строки удаляются массово. Продолжайте нажимать Данные > ФИЛЬТР еще раз, чтобы отменить фильтр.

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

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

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

2. Нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки.

3. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» установите флажок Все дубликаты (включая 1-й) и нажмите Ok .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Выберите вспомогательный столбец и щелкните Данные > ФИЛЬТР.

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

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

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

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

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

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

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

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

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

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