Excel公式需求：按交易ID统计含0金额拒付行的交易数量

需求拆解 #

• 按唯一Transaction ID分组统计
• 需满足：分组内至少存在1行明细同时符合「状态码为"D"」且「Payment Amount为0」
• 最终统计符合条件的唯一交易ID总数，用于计算拒付交易率

高效公式方案（适配不同Excel版本） #

方案1：Excel 365/2021 动态数组公式（推荐，性能最优） #

利用动态数组函数组合直接完成统计，无需辅助列：

=ROWS(FILTER(UNIQUE(A:A),COUNTIFS(A:A,UNIQUE(A:A),B:B,"D",C:C,0)>=1))

• 分步解释：
  1. UNIQUE(A:A)提取所有唯一的Transaction ID
  2. COUNTIFS(...)统计每个唯一ID下满足「状态码D+金额0」的行数
  3. FILTER筛选出统计结果≥1的ID（即该交易存在符合条件的明细）
  4. ROWS统计筛选后的唯一ID数量

性能优化：替换整列引用为精确数据范围（如A2:A3000001），减少计算量：

=ROWS(FILTER(UNIQUE(A2:A3000001),COUNTIFS(A2:A3000001,UNIQUE(A2:A3000001),B2:B3000001,"D",C2:C3000001,0)>=1))

方案2：旧版Excel（无动态数组）—— 辅助列+数据透视表（大数量首选） #

300万行数据用数组公式易卡顿，推荐分步操作：

1. 添加辅助列（如D列），在D2输入公式后向下填充：

   =IF(AND(B2="D",C2=0),A2,"")
   

   作用：仅标记出满足条件的交易ID，不满足的留空
2. 统计唯一ID数量：
   • 复制D列到新列（如E列），删除空值后用COUNT(UNIQUE(E:E))计数；
   • 或直接插入数据透视表，将E字段拖至「行」区域，行标签的数量即为符合条件的交易数

方案3：旧版Excel数组公式（慎用，大数量易卡顿） #

若不想用辅助列，可使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=SUM(IF(FREQUENCY(IF(AND(B:B="D",C:C=0),A:A,""),A:A)>0,1,0))

• 解释：FREQUENCY对满足条件的交易ID统计出现频次，频次>0的即为符合要求的唯一交易，最终用SUM计数

大数量数据性能建议 #

• 始终使用精确数据范围替代整列引用，减少无效计算
• 优先选择Excel 365动态数组公式，性能远优于旧版数组公式
• 数据量超大规模时，建议导入Power Query进行分组统计，再返回Excel，性能提升显著

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