![如何计算某一列中出现次数超过 1 次的值的个数](https://linux22.com/image/1380539/%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%E6%9F%90%E4%B8%80%E5%88%97%E4%B8%AD%E5%87%BA%E7%8E%B0%E6%AC%A1%E6%95%B0%E8%B6%85%E8%BF%87%201%20%E6%AC%A1%E7%9A%84%E5%80%BC%E7%9A%84%E4%B8%AA%E6%95%B0.png)
在单元格 B5 中我有一个公式:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
如果 A 列包含如下所示的数据,则公式将返回正确答案 3,因为有三个值出现不止一次(14、16 和 17)。这正是我想要知道的;有三个值出现不止一次。我不需要知道这些值是什么,也不需要知道它们在哪里(尽管我对最后一部分确实有一点巧妙的技巧)。
Column A:
12
13
14
14
14
15
16
16
17
17
但是,如果 A2:A10 范围内的任何地方是空白单元格,则重复值的数量将减少 1(在上面的例子中,公式将返回 2,而它应该是 3)在下面的示例中,空白单元格由“B”表示。
Column A:
12
13
14
B
14
15
16
16
17
17
在此示例中,14、16 和 17 仍然各自出现了不止一次;因此公式应该返回 3,但它返回的是 2。
我相当确定这是由于第一部分=SUMPRODUCT((A2:A10<>"")
计算本质上非空单元格所致。当然,如果恰巧有两个(或更多)空白单元格,那么它会重新增加,但这也不正确,因为它仍然忽略了非空白重复项(如果这有任何意义的话)。
答案1
注意:您的问题是指9单元格范围,但你正在显示10示例中的值,所以不太正确......尽管我理解这个问题。
公式的第一部分是可以的,因为这是计算不同非空值数量的标准方法......但是当你减去第二个时COUNTIF
你还需要排除空白,所以你需要删除第二个中的&“”部分COUNTIF
,即这个版本
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....但是这个版本更好
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
这两个公式都适用于 A2:A10 中的文本或数字数据(或两者的混合),但仅适用于数字值(根据您的示例),您也可以使用FREQUENCY
如下函数
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)