统计不同值的7种方法

标签：Excel技巧

很多时候，我们需要统计列表中的不同值的个数，在Excel中有多种方法实现。

首先，我们来解释什么是不同值和唯一值。不同值意味着值是不同的，例如列表{A, B, B, C}中的不同值是{A, B, C}，不同值个数是3。而唯一值意味着值仅出现一次，例如列表{A, B, B, C}中的唯一值是{A, C}，唯一值个数是2。

方法1：使用COUNTIFS函数

COUNTIFS函数允许基于一个或多个判断条件来统计值。如下图1所示的列表，统计列表中的不同值个数，使用公式：

=SUM(1/COUNTIFS(B5:B13,B5:B13))

图1

COUNTIFS函数用于查看列表中每个值出现了多少次。当将计数取倒数时，会得到一个分数值，列表中每个不同的值加起来就是1。然后，SUM函数将所有这些分数相加，总数就是列表中不同项目的数量。

方法2：使用UNIQUE函数

如下图2所示，很简单的公式：

=COUNTA(UNIQUE(B5:B13))

图2

UNIQUE函数返回列表中所有不同的值，COUNTA函数统计这些值的个数。

方法3：使用高级筛选

在单元格中输入公式：

=SUBTOTAL(103,B5:B13)

如下图3所示。

图3

公式中，103指示仅统计可见单元格。

然后，选择单元格区域B4:B13，单击功能区“数据”选项卡“排序和筛选”组中的“高级”命令。在“高级筛选”对话框中，勾选“选择不重复的记录”复选框，如下图4所示。

图4

单击“确定”按钮，结果如下图5所示。

图5

方法4：使用数据透视表

选择数据区域，单击功能区“插入”选项卡“表格”组中的“数据透视表”，在“来自表格或区域的数据透视表”对话框中，选取“现有工作表”单选按钮，选取在工作表中放置透视表的单元格位置，如下图6所示。

图6

在数据透视表字段中，选取要获取不同值计数的字段到行，如下图7所示。

图7

在工作表中，选择数据透视表数据，可以在底部状态栏中看到计数值为4，即为不同值个数，如下图8所示。

图8

方法5：使用数据透视表数据模型

选择数据区域，单击功能区“插入”选项卡“表格”组中的“数据透视表”，在“来自表格或区域的数据透视表”对话框中，选取“现有工作表”单选按钮，选取在工作表中放置透视表的单元格位置，勾选“将此数据添加到数据模型”复选框，如下图9所示。

图9

在“数据透视表字段”窗口，将“数据”字段拖至“值”窗口，然后单击其右侧下拉箭头，在快捷菜单中单击“值字段设置”，如下图10所示。

图10

在“值字段设置”对话框中，选取“计算类型”列表中的“非重复计数”，如下图11所示。

图11

单击“确定”，结果如下图12所示。

图12

方法6：删除重复值

选择数据列表，单击功能区“数据”选项卡“数据工具”组中的“删除重复值”命令，如下图13所示。

图13

在“删除重复值”对话框中，单击“全选”，如下图14所示。

图14

单击“确定”，Excel会给出一个消息框，显示保留的不同值数，如下图15所示。

图15

方法7：使用VBA

按Alt+F11组合键，打开VBE，插入一个标准模块，输入下面的代码：

Function CountDistinctValues(rng As Range) As Integer
 Application.Volatile
 Dim var As Variant
 Dim distinctValues As New Collection
 On Error Resume Next
 For Each var In rng
   If Not (IsEmpty(var)) Then
     distinctValues.Add var, CStr(var)
   End If
 Next var
 CountDistinctValues = distinctValues.Count
End Function

在工作表中，输入公式：

=CountDistinctValues(B5:B13)

结果如下图16所示。

图16