如何在动态行数据中统计列内重复值的重复次数？

需求说明 #

给定从Web API导入的动态列数据（示例：[A,B,C,A,D,F,A,B]），需统计所有重复值的额外出现次数总和（示例中A重复2次、B重复1次，合计3），且数据刷新时自动适配行数变化。

解决方案 #

1. Excel 365/2021（动态数组版本，推荐）

使用动态数组函数自动适配行范围，无需手动调整：

=SUM(COUNTIF(A:A,UNIQUE(FILTER(A:A,A:A<>"")))-1)

或者用LET函数封装，逻辑更清晰：

=LET(
    data, FILTER(A:A, A:A<>""),
    unique_vals, UNIQUE(data),
    SUM(COUNTIF(data, unique_vals)-1)
)

逻辑解释：

• FILTER(A:A,A:A<>"")：自动提取A列所有非空数据，数据刷新时同步更新范围。
• UNIQUE(data)：获取数据中的唯一值列表。
• COUNTIF(data, unique_vals)：统计每个唯一值的出现次数。
• 对每个次数减1（减去首次出现的1次）后求和，得到总重复次数。

2. 旧版Excel（无动态数组，需按数组公式输入）

使用INDEX+COUNTA动态定位范围，结合FREQUENCY统计次数：

=SUM(IF(FREQUENCY(MATCH(A1:INDEX(A:A,COUNTA(A:A)),A1:INDEX(A:A,COUNTA(A:A)),0),MATCH(A1:INDEX(A:A,COUNTA(A:A)),A1:INDEX(A:A,COUNTA(A:A)),0))>1,FREQUENCY(MATCH(A1:INDEX(A:A,COUNTA(A:A)),A1:INDEX(A:A,COUNTA(A:A)),0),MATCH(A1:INDEX(A:A,COUNTA(A:A)),A1:INDEX(A:A,COUNTA(A:A)),0))-1,0))

注意：输入后需按Ctrl+Shift+Enter作为数组公式确认。
逻辑解释：

• INDEX(A:A,COUNTA(A:A))：动态定位A列最后一个非空单元格，生成自适应范围。
• MATCH获取每个值的首次出现位置，FREQUENCY统计每个值的出现次数。
• 筛选出次数大于1的项，计算(次数-1)后求和得到总重复次数。

示例验证 #

代入示例数据[A,B,C,A,D,F,A,B]：

• 唯一值为A,B,C,D,F，对应出现次数为3,2,1,1,1。
• 每个次数减1后为2,1,0,0,0，求和结果为3，与需求一致。

内容的提问来源于stack exchange，提问作者Siersonek