☰

如何计算重复出现的次数

tag-icon • tag-icon microsoft-excel worksheet-function
如何计算重复出现的次数

我有一个 Excel 电子表格，它看起来或多或少是这样的：

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

我需要做的是检测重复条目的数量，并显示名字和姓氏至少出现两次的行数。

所以基本上我在寻找列表中有多少人至少出现在列表中两次。

答案1

以下是一些您可以尝试的方法。

使用数组公式

假设名字在列A姓氏在列乙（和A1和B1包含标题标签“First Name”和“Last Name”），将以下公式插入到C2。

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

输入公式后，按Ctrl+ Shift+Enter将其作为数组公式输入。将其复制到其下方的空白单元格中。公式将显示名称组合在数据中出现的次数。然后，您只需使用条件格式更清楚地看到哪些人在您的表中至少列出了两次。

使用 COUNTIF 和另一列

假设名字在列A姓氏在列乙（和A1和B1包含标题标签“First Name”和“Last Name”），将以下公式插入到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

设置您的首选格式，然后单击“确定”。
在“条件格式规则管理器”窗口中，设置适用于到=$A:$B

在此处输入图片描述

答案2

如果我要解决这个问题，我会首先按姓氏对行进行排序，然后按名字对行进行排序，以便所有重复项都会彼此相邻。

假设名字在 B 列，姓氏在 C 列。我们还假设第一个数据行是第 2 行。我们还假设我们将重复项的运行计数放在 A 列中。

接下来，在单元格 A2 中输入以下公式：

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

现在，复制 A2 并将其粘贴到 A 列，以便该公式位于列表中的每个名称旁边。

如果您正确完成了所有这些操作，A 列现在将包含重复项的计数。如果您想更轻松地检测它们，请在 A 列上设置条件格式，以便值 > 1 会突出显示。

祝你好运！

答案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) 的帮助。要在 VBA 中创建 UDF，请按 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),", ")

也将其作为数组公式输入。

屏幕截图

相关内容