Excel基于年月双参数的去重计数公式故障求助

问题描述 #

需按年份和月份双参数进行去重计数，统计对应年月的报表运行次数。单参数场景下公式可用，但双参数时失效，已确认列引用正确。

• Excel版本：2305 Build 16
• 当前使用的数组公式：

  {=SUM(IF(bpt!$E$2:$E$15008='Cover Page'!$B10,(1/COUNTIFS(bpt!$C$2:$C$15008,bpt!$C$2:$C$15008,bpt!$E$2:$E$15008,bpt!$E$2:$E$15008)),0))}
  

相关数据说明 #

• 报表数据表格：Excel Spreadsheet
• 模拟数据样例：Mockup Data

解决方法 #

方法1：修正原数组公式 #

原公式的问题在于COUNTIFS未将目标年月作为条件限制，导致计算的是全量数据的重复次数。调整后公式如下（旧版Excel需按Ctrl+Shift+Enter以数组形式输入）：

{=SUM(IF(bpt!$E$2:$E$15008='Cover Page'!$B10,1/COUNTIFS(bpt!$C$2:$C$15008,bpt!$C$2:$C$15008,bpt!$E$2:$E$15008,'Cover Page'!$B10),0))}

方法2：使用新版Excel函数（无需数组输入） #

若你的Excel支持UNIQUE和FILTER函数，可使用更简洁的公式：

=COUNTA(UNIQUE(FILTER(bpt!$C$2:$C$15008,bpt!$E$2:$E$15008='Cover Page'!$B10)))

原理说明 #

• 方法1：通过IF筛选出目标年月的数据，再用COUNTIFS统计该年月下每个报表的重复次数，取倒数后求和，即可得到去重后的计数。
• 方法2：先用FILTER提取目标年月的报表数据，再用UNIQUE去重，最后用COUNTA统计去重后的数量。

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