Excel公式：使用 COUNTIF 计算范围内的唯一值

220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>

Excel 公式：使用 COUNTIF 计算范围内的唯一值

通用公式

=SUMPRODUCT(1/COUNTIF(data,data))

概括

要计算单元格区域中唯一值的数量，您可以使用基于COUNTIF和SUMPRODUCT函数的公式。在示例显示中，F6 中的公式为：

=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))

解释

从内到外，COUNTIF 配置为 B5:B14 范围内的值，使用所有这些相同的值作为标准：

COUNTIF(B5:B14,B5:B14)

因为我们为标准提供了 10 个值，所以我们返回一个包含 10 个结果的数组，如下所示：

{3;3;3;2;2;3;3;3;2;2}

每个数字代表一个计数——“Jim”出现 3 次，“Sue”出现 2 次，以此类推。

这个数组被配置为一个以 1 为分子的除数。除法后，我们得到另一个数组：

{0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5}

范围内仅出现一次的任何值都将显示为 1，但多次出现的值将显示为对应于倍数的小数值。（即数据中出现 4 次的值将生成 4 个值 = 0.25）。

最后，SUMPRODUCT 函数将数组中的所有值相加并返回结果。

处理空白或空单元格的一种方法是调整公式如下：

=SUMPRODUCT(1/COUNTIF(data,data&“”))

通过将空字符串( “”) 连接到数据，当数据中有空白单元格时，我们可以防止 COUNTIF 创建的数组中出现零。这很重要，因为除数中的零会导致公式引发 #DIV/0 错误。它之所以有效，是因为使用空字符串（“”）作为标准将计算空单元格。

但是，尽管此版本的公式在使用空白单元格时不会引发 #DIV/0 错误，但它会在计数中包含空白单元格。如果要从计数中排除空白单元格，请使用：

=SUMPRODUCT((data<>“”)/COUNTIF(data,data&“”))

这具有通过使相关计数的分子为零来抵消空白单元格的计数的效果。

这是一个很酷且优雅的公式，但它的计算速度比使用 FREQUENCY 计算唯一值的公式要慢得多。对于较大的数据集，您可能希望切换到基于 FREQUENCY 函数的公式。这是数值公式和文本值公式。

在Excel 365中，UNIQUE 函数提供了一种更好、更优雅的方式来列出唯一值和计算唯一值。

220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>

发表评论 取消回复