如何编写Excel数组公式，基于SubID与Category多条件返回对应值？

假设你的数据位于单元格区域 A2:C5（表头在A1:C1），以下是针对需求编写的数组公式：

兼容Excel 2019及以后版本（含365/2021） #

=IF(AND(E2=1111,COUNTIFS(A:A,E2,C:C,"Charge")>0),5,IF(AND(E2=2222,COUNTIFS(A:A,E2,C:C,"Charge")=0),MAXIFS(B:B,A:A,E2),""))

• 说明：E2为存放目标SubID的单元格，可根据实际位置调整
• 输入方式：Excel 365/2021直接回车即可；旧版需按Ctrl+Shift+Enter触发数组计算

兼容更早Excel版本（如2016及以前） #

=IF(AND(E2=1111,SUMPRODUCT((A:A=E2)*(C:C="Charge"))>0),5,IF(AND(E2=2222,SUMPRODUCT((A:A=E2)*(C:C="Charge"))=0),MAX(IF(A:A=E2,B:B)),""))

• 说明：用SUMPRODUCT替代COUNTIFS、数组MAX替代MAXIFS，适配无新函数的旧版本
• 输入方式：必须按Ctrl+Shift+Enter触发数组计算，公式会自动被大括号包裹（请勿手动添加）

公式逻辑拆解 #

• 第一分支：判断目标SubID是否为1111，且该SubID对应的记录中存在Charge类别，满足则返回5
• 第二分支：判断目标SubID是否为2222，且该SubID对应的记录中不存在Charge类别，满足则返回该SubID对应的最大Day值
• 末尾空值""为其他SubID的默认返回值，可根据需求修改为特定内容

优化建议 #

建议将公式中的整列引用（如A:A）替换为具体数据区域（如A2:A5），减少计算量，提升公式运行效率。

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