Как посчитать количество повторений

Question

У меня есть таблица Excel, которая выглядит примерно так:

+------------+-----------+
| First Name | Last Name |
+------------+-----------+
| John       | Paul      |
| Joe        | Jones     |
| John       | Paul      |
+------------+-----------+

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

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

Answer 1

Вот несколько методов, которые вы можете попробовать.

Использование формулы массива

Предполагая, что имена указаны в столбце A, а фамилии - в столбце B (с A1 и B1, содержащими заголовки "Имя" и "Фамилия"), вставьте следующую формулу в C2.

=SUM(($A$2:$A$11=A3)*($B$2:$B$11=B3))

После ввода формулы нажмите Ctrl + Shift + Enter, чтобы ввести ее как формулу массива. Скопируйте его в пустые ячейки под ним. Формула покажет вам, сколько раз комбинация имен появляется в ваших данных. Затем вы можете просто использовать условное форматирование, чтобы более наглядно увидеть, какие люди перечислены в вашей таблице хотя бы дважды.

---

Использование COUNTIF & Другой столбец

Предполагая, что имена указаны в столбце A, а фамилии - в столбце B (где A1 и B1 содержат метки заголовков "Имя" и "Фамилия"), вставьте следующую формулу в C2 и скопируйте ее в ячейки под ней.

=A2&B2

Вы получите что-то вроде "JohnPaul" или "JoeJones". Затем вы можете просто скрыть этот столбец позже.

В ячейку D2 введите FF. формула. Скопируйте его в другие ячейки под ним.

=COUNTIF($C$2:$C$11,C2)

Вы увидите тот же результат, что и в формуле массива выше.

---

Использование функции COUNTIFS и условного форматирования

Я считаю, что эта функция доступна только в Excel 2007 и выше.

Скажем, имена в A и фамилии в B.

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

=COUNTIFS($A:$A,$A1,$B:$B,$B1)>=2

Установите предпочитаемый формат, затем нажмите OK.
В окне диспетчера правил условного форматирования установите значение Применить к =$A:$B

Answer 2

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

Допустим, имена указаны в столбце B, а фамилии - в столбце C. Также предположим, что первая строка данных - это строка 2. Предположим также, что мы поместим счетчики дубликатов в столбец А.

Далее в CELL A2 ставим следующую формулу:

=IF(AND(B2=B1,C2=C1),A1+1,1)

Теперь скопируйте A2 и вставьте его в столбец A, чтобы эта формула была рядом с каждым именем в вашем списке.

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

Удачи!

Answer 3

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

=COUNTA(A1:A20)-SUM(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)=1,1,0))

Введите это как формулу массива, нажав Ctrl+Shift+Enter. Это даст вам счет в одной ячейке - дополнительные столбцы не нужны.

Вы также можете вернуть номера строк каждой дублирующейся записи, но вам понадобится небольшая помощь от пользовательской функции VBA (UDF). Чтобы создать UDF в VBA, нажмите Alt+F11 и вставьте модуль. Вставьте следующий код в модуль.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr

out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))

CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

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

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,ROW(B1:B20),0),", ")

Введите эту формулу как формулу массива, нажав Ctrl+Shift+Enter.

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

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,A1:A20&" "&B1:B20,0),", ")

Также введите это как формулу массива.