Excel公式：计算一个范围内的唯一数值

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

Excel 公式：计算范围内的唯一数值

通用公式

=SUM(—(FREQUENCY(data,data)>0))

概括

要计算某个范围内的唯一数值，您可以使用基于FREQUENCY和SUM函数的公式。在所示示例中，员工编号出现在 B5:B14 范围内。F5中的公式为：

=SUM(—(FREQUENCY(B5:B14,B5:B14)>0))

它返回 4，因为列表中有 4 个唯一的员工 ID。

解释

注意：在 Excel 365 之前，Excel 没有专门的函数来计算唯一值。这个公式显示了一种计算唯一值的方法，只要它们是数字的。如果您有文本值或文本和数字的混合，则需要使用更复杂的公式。

Excel FREQUENCY 函数返回频率分布，它是一个汇总表，显示数值的频率，按“bins”组织。我们在这里使用它作为计算唯一数值的一种迂回方式。

从内到外，我们为数据数组和 bins 数组提供相同的一组数字给 FREQUENCY：

FREQUENCY(B5:B14,B5:B14)

FREQUENCY 返回一个数组，其中包含范围内每个数值的计数：

{4;0;0;0;2;0;1;3;0;0;0}

结果有点神秘，但意思是905出现4次，773出现2次，801出现1次，963出现3次。

FREQUENCY 有一个特殊的功能，它会自动为数据数组中已经出现的任何数字返回零，这就是为什么一旦遇到数字，值就为零的原因。

接下来，测试这些值中的每一个是否大于零：

{4;0;0;0;2;0;1;3;0;0;0}>0

结果是这样的数组：

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}

每个 TRUE 代表列表中的唯一编号。默认情况下，SUM 忽略逻辑值，因此我们将 TRUE 和 FALSE 值强制为 1 和 0，并使用双负(–)，产生：

=SUM({1;0;0;0;1;0;1;1;0;0;0})

最后，SUM 将这些值相加并返回总数，在本例中为 4。

注意：您也可以使用 SUMPRODUCT 对数组中的项目求和。

另一种计算唯一数值的方法是使用COUNTIF 而不是 FREQUENCY。这是一个更简单的公式，但请注意，在较大的数据集上使用 COUNTIF 来计算唯一值可能会导致性能问题。FREQUENCY 公式虽然更复杂，但计算速度更快。

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

发表评论 取消回复