如何避免Google Sheets中#REF!数组结果覆盖数据错误？

核心结论 #

Google官方人员Spencer Faris的说法是正确的：

遗憾的是……ARRAYFORMULA()公式永远无法覆盖数据，尝试覆盖就会失效，且无脚本无法查看‘最后编辑记录’等内容

ARRAYFORMULA的设计逻辑是输出一个连续的数组范围，当该范围内的单元格存在手动输入的内容时，公式的数组输出会因“尝试覆盖已有数据”触发#REF!错误。同时，纯公式确实无法区分单元格内容是公式生成还是手动编辑的，也无法追踪编辑记录。

无需脚本的替代方案 #

要实现“公式生成初始值，允许手动编辑且不触发错误”的需求，最可靠的纯公式方案是拆分公式列与手动编辑列，通过辅助列实现逻辑：

方案步骤 #

1. 保留A列作为输入源，新增辅助列（比如C列）放置原ARRAYFORMULA逻辑，仅生成初始值：

   ARRAYFORMULA(IF(A1:A<>"", "Favorite Color "&A1:A, ""))
   

2. 将B列设为手动编辑专用列，用户直接在B列修改内容；
3. 新增结果列（比如D列），优先显示手动编辑内容，无手动内容时显示公式生成值：

   ARRAYFORMULA(IF(B1:B<>"", B1:B, C1:C))
   

这样B列的手动编辑不会干扰C列的公式运行，D列整合后的结果完全符合需求，且不会触发任何错误。

为什么你之前的尝试失败？ #

• 方法1：IF(B1:B="",...)的逻辑看似会保留已有内容，但ARRAYFORMULA仍会尝试对整个B列输出数组，只要B列存在手动编辑的单元格，数组输出就会因范围冲突触发错误，而非仅修改空单元格。
• 方法2：迭代计算是用于处理循环引用的设置，和数组输出覆盖已有数据的冲突无关，因此无效。
• 方法3：SUBSTITUTE(B1:B,B1:B,B1:B)本质是返回B列原有内容，但ARRAYFORMULA输出数组时仍会尝试覆盖整个B列范围，只要有手动单元格就会触发错误。

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