Excel跨多年份工作表求和遇缺失工作表报引用错误的解决方法

问题说明 #

• 业务场景：工作表按年份区分命名，需要跨表匹配指定车型，对对应数值列求和
• 故障原因：原有公式中INDIRECT函数引用不存在的工作表时会直接返回#REF!错误，只要某车型缺少任意年份对应的工作表，整个公式就会计算失败
• 原有故障公式：

=SUMPRODUCT(SUMIF(INDIRECT("'" & C24 & Year & "*'!B:B"),C4,INDIRECT("'" & C24 & Year & "*'!Z:Z")))

注意：INDIRECT本身不支持通配符匹配工作表名，原公式中拼接的*属于无效写法，必须保证年份列表Year中的值和工作表名内的年份段完全对应。

可用调整方案 #

方案1：全版本兼容（支持Excel 2007及以上所有版本） #

用IFERROR捕获不存在工作表产生的引用错误，将错误值转换为0后再参与求和，输入公式后按Ctrl+Shift+Enter三键结束数组计算（365版本直接回车即可）：

=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&C24&TRANSPOSE(Year)&"'!B:B"),C4,INDIRECT("'"&C24&TRANSPOSE(Year)&"'!Z:Z")),0))

如果同一年份对应多个带后缀的工作表（例如XX车型2023上半年、XX车型2023下半年），需要先通过宏表函数枚举所有工作表名做匹配，操作步骤如下：

1. 按Ctrl+F3打开名称管理器，新建名称，命名为ShtList，引用位置填写=GET.WORKBOOK(1)&T(NOW())，保存即可
2. 使用以下数组公式（三键回车确认），自动过滤出包含指定车型前缀、年份在目标列表内的所有工作表参与计算，自动跳过不存在/不匹配的工作表：

=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&MID(ShtList,FIND("]",ShtList)+1,99)&"'!B:B"),C4,INDIRECT("'"&MID(ShtList,FIND("]",ShtList)+1,99)&"'!Z:Z"))*ISNUMBER(SEARCH(C24&Year,MID(ShtList,FIND("]",ShtList)+1,99))),0))

方案2：Excel 365/2021及以上版本简化写法 #

利用TOCOL函数直接过滤错误值，不需要额外处理数组方向，计算效率更高，直接回车即可生效：

=SUM(TOCOL(IFERROR(SUMIF(INDIRECT("'"&C24&Year&"'!B:B"),C4,INDIRECT("'"&C24&Year&"'!Z:Z")),NA()),2))

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