使用INDIRECT函数时Excel返回#VALUE!错误的求助

错误原因 #

你的公式返回#VALUE!，核心问题是INDIRECT生成多工作表数组引用时，COUNTIFS无法直接识别这种非连续的区域数组，函数要求每个条件区域的维度完全匹配，当前写法导致参数格式不兼容。

解决方法 #

方法一：适配全版本的嵌套写法 #

替换原公式为：

=SUMPRODUCT(SUM(COUNTIFS(INDIRECT("'"&{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}&"'!$A$11:$A$38"),$B2,INDIRECT("'"&{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}&"'!$B$11:$AF$38"),"V")))

• 新增的SUM会先单独计算每个月份工作表的"V"次数，再通过SUMPRODUCT汇总全年数据，解决多工作表引用的维度不兼容问题

方法二：Excel 2019/365专属简洁写法 #

利用TEXTJOIN合并工作表名称，生成连续的多区域引用：

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&TEXTJOIN("','",,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"})&"'!$A$11:$A$38"),$B2,INDIRECT("'"&TEXTJOIN("','",,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"})&"'!$B$11:$AF$38"),"V"))

• TEXTJOIN把12个工作表名合并为'JAN','FEB',...,'DEC'格式，让INDIRECT生成符合COUNTIFS要求的连续多工作表区域

额外建议 #

• 核对所有月份工作表的名称，确保和公式里的数组完全一致（区分大小写）
• 如果存在重名员工，建议改用统计工作表的ID列（A2:A23）匹配，将公式中的$B2替换为$A2，同时调整月份工作表的匹配列（A11:A38改为ID列），避免统计错误

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