Google Sheets中ArrayFormula相对引用失效 单元格值重复如何解决

问题根源 #

ArrayFormula 触发整列返回同一个值的核心原因是公式内参与逐行计算的引用被固定为单个单元格，数组运算阶段没有按行匹配对应位置的数据源，直接用首个引用值填充了所有输出行。

典型错误写法 #

• 引用单单元格作为运算源：例如需要B列同步A列内容时，写为=ARRAYFORMULA(IF(A:A<>"",A2,""))，公式中A2是固定单元格，所有输出行都会直接读取A2的值。
• 逐行运算参数被绝对引用锁死：例如做VLOOKUP匹配时，查找值写为$A$2，锁定了首行单元格作为唯一查找值，所有行返回同一条匹配结果。
• 嵌套不支持数组运算的函数：部分旧版函数不支持逐行数组迭代，运算后仅返回单个结果，被ArrayFormula拉伸填充到整列。

修正方案 #

• 基础同列/逐行映射场景：直接引用整列作为运算参数，不要锁死单个单元格，例如A列非空时同步A列内容到B列的正确公式：

  =ARRAYFORMULA(IF(A:A<>"",A:A,""))
  

• 带逐行匹配/计算场景：所有需要按行取值的参数都使用同高度的范围引用，不要写死单单元格，例如逐行VLOOKUP匹配的正确公式：

  =ARRAYFORMULA(IF(A:A<>"",VLOOKUP(A:A, 数据源!$A:$B,2,FALSE),""))
  

• 复杂自定义逻辑场景：如果用到不支持原生数组运算的逻辑，改用MAP函数做逐行迭代，避免返回单值：

  =MAP(A:A,LAMBDA(单格值,IF(单格值="","",自定义运算逻辑)))
  

排查步骤 #

1. 选中写入ArrayFormula的首个单元格，检查公式内所有参与逐行计算的引用，替换掉固定单单元格（如A2、C3这类带固定行号的非范围引用）
2. 检查引用前的$绝对引用标记，确保逐行取值的参数没有同时锁定行号和列号
3. 测试公式在单单元格运行的结果，如果单单元格就返回固定值，说明公式内的引用或函数逻辑本身没有做逐行适配

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