Excel溢出公式跨列引用遭误判循环引用的技术咨询

这确实是Excel溢出数组（Spill Arrays）依赖跟踪机制的硬限制，根源在于Excel将溢出区域视为单一整体对象，而非独立的单元格集合。

你在制作涡轮蒸汽消耗量动态表格时，希望用Spill Arrays实现动态列（无需手动复制公式），其中部分次级涡轮列的入口温度需要引用前一列初级涡轮的出口温度。但Excel无法识别这种逐元素的单向依赖，反而触发虚假的循环引用错误。

简化复现步骤 #

• 在新工作表的A1输入1，B1输入2
• 在A2输入公式：=BYCOL($1:$1, LAMBDA(x, 2*x))，此时A2会横向溢出到B2，得到2和4
• 将B1修改为=A2，Excel立即触发循环引用错误

预期的单向依赖链是A1→A2→B1→B2，但Excel的依赖跟踪逻辑是：

1. BYCOL($1:$1, ...)生成的整个溢出区域（A2:B2）被标记为依赖于整个第1行（A1:B1）
2. 当B1引用A2时，就形成了「第1行 → 溢出区域 → B1（属于第1行）」的循环链，触发错误

可行的替代方案 #

要实现级联涡轮的动态计算，需要绕过Spill Arrays的整体依赖限制：

• 逐列独立计算：用INDEX配合SEQUENCE生成单值而非整体溢出，比如在A2输入=2*INDEX($1:$1, COLUMN(A:A))，然后横向拖拽公式，或者用HSTACK逐个拼接列计算结果，确保每列的依赖仅指向左侧单个单元格
• 命名范围隔离：为每列的出口温度定义单独的命名范围，次级列的入口温度直接引用前一列的命名范围，避免整行/整区域的依赖
• 自动扩展表格：放弃Spill Arrays，使用传统拖拽公式，配合Excel的「表格（Table）」功能，添加新列时自动复制公式到新列

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