Excel 2010多参数数组查找：如何区分空单元格与真实0值？

一、改进SUMIFS实现动态空值判断 #

针对原公式中固定单元格引用失效的问题，可通过数组公式动态定位符合条件的单元格，再判断其是否为空，避免误将空值识别为0：

=IF(COUNTIFS(DATA!$A:$A,'All Cities'!$B$2,DATA!$D:$D,'All Cities'!$D$6,DATA!$E:$E,'All Cities'!$L$8)=0,"",IF(ISEMPTY(INDEX(DATA!H:H,SMALL(IF((DATA!$A:$A='All Cities'!$B$2)*(DATA!$D:$D='All Cities'!$D$6)*(DATA!$E:$E='All Cities'!$L$8),ROW(DATA!H:H)),1))),"",SUMIFS(DATA!H:H,DATA!$A:$A,'All Cities'!$B$2,DATA!$D:$D,'All Cities'!$D$6,DATA!$E:$E,'All Cities'!$L$8)))

公式说明： #

1. 先用COUNTIFS判断是否存在匹配记录，无匹配时直接返回空
2. 通过SMALL+IF数组组合，动态定位第一条符合条件的行号，再用INDEX指向对应H列单元格
3. 用ISEMPTY判断该单元格是否为空，为空则返回空，否则返回SUMIFS的求和结果
4. 注意：Excel 2010中需按Ctrl+Shift+Enter确认输入数组公式，公式会自动被大括号{}包裹（手动输入大括号无效）

二、用INDEX+MATCH实现多参数直接查找（支持返回空值） #

如果需求是单条记录查找而非汇总，INDEX+MATCH组合可直接返回单元格原值（包括空值），避免SUMIFS将空值转为0的问题：

=IFERROR(INDEX(DATA!H:H,MATCH(1,(DATA!$A:$A='All Cities'!$B$2)*(DATA!$D:$D='All Cities'!$D$6)*(DATA!$E:$E='All Cities'!$L$8),0)),"")

公式说明： #

1. 用(条件1)*(条件2)*(条件3)生成匹配数组，符合所有条件的位置返回1，其余为0
2. MATCH查找数组中第一个1的位置，INDEX返回对应H列的单元格值
3. IFERROR用于处理无匹配记录的情况，返回空值而非#N/A
4. 注意：同样需按Ctrl+Shift+Enter作为数组公式输入

方案选择建议 #

• 若需对多城市同项目汇总求和，使用第一部分的改进SUMIFS公式
• 若仅需查找单条记录的原始值（包括空值），使用第二部分的INDEX+MATCH组合

内容的提问来源于stack exchange，提问作者Taric.png