Excel图表调用动态范围名称出现公式报错问题

报错原因 #

1. 核心诱因：INDIRECT、OFFSET都属于Excel的易失性函数，图表引擎对这类函数返回的动态引用兼容性极差，哪怕公式在普通单元格内可以正常运行，也会被图表识别为非法引用。
2. 次要诱因：调用命名范围时未指定作用域，或者命名范围被设置为工作表局部级，跨工作表调用时无法被正确识别。

修复步骤 #

• 第一步：替换易失性函数，改用INDEX+MATCH的非易失性组合实现完全相同的动态引用效果，修改后的命名范围公式如下：

Summary_values = 
INDEX('Summary'!$1:$1048576,2,MATCH("CumTotal",'Summary'!$1:$1,0)):
INDEX('Summary'!$1:$1048576,COUNTA('Summary'!$A:$A)-1,MATCH("CumTotal",'Summary'!$1:$1,0))

如果你的Summary表A列存在空值，会导致COUNTA统计行数不准，可以把第二个INDEX的行参数替换为如下逻辑，自动取A列最后一个非空行的行号：
MAX(IF('Summary'!$A:$A<>"",ROW('Summary'!$A:$A),0))

• 第二步：调整命名范围的作用域为工作簿级，避免跨表调用时作用域匹配错误。
• 第三步：图表调用命名范围时必须补全作用域前缀，例如你的工作簿名称为运营统计.xlsx，则图表系列值需要填写为=运营统计.xlsx!Summary_values，不要仅填写Summary_values，否则图表会默认查找当前工作表下的局部命名范围，找不到就触发报错。

验证方法 #

修改完成后先在任意空白单元格输入=SUM(Summary_values)，确认可以正常返回计算结果，再绑定到图表即可。如果依然报错，可以先手动填入CumTotal列的固定范围到图表，排除图表本身的格式或其他数据源错误。

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