拆解列空值自动填充ArrayFormula公式及寻求更优实现方案

一、原公式拆解 #

首先你给出的公式存在语法笔误，符合描述逻辑的完整正确版本为：
ArrayFormula(IF(ROW(A:A)<=MATCH(2,1/(A:A<>""),1),LOOKUP(ROW(A:A),ROW(A:A)/IF(A:A<>"",TRUE,FALSE),A:A),""))
（公式里的分号是部分地区的电子表格参数分隔符，和逗号作用一致）
各个模块的运行逻辑如下：

• 边界判断模块：ROW(A:A)<=MATCH(2,1/(A:A<>""),1)
  • A:A<>""返回布尔值数组，A列非空单元格为TRUE（对应数值1），空单元格为FALSE（对应数值0）
  • 1/(A:A<>"")会把非空单元格转为数值1，空单元格转为错误值
  • MATCH(2,1/(A:A<>""),1)用模糊匹配规则，查找小于2的最大值的最后出现位置，也就是A列最后一个非空值的行号，作用是让A列最后一个非空值之后的所有行返回空，避免把最后一个值一直填充到表格末尾
• 填充逻辑模块：LOOKUP(ROW(A:A),ROW(A:A)/IF(A:A<>"",TRUE,FALSE),A:A)
  • IF(A:A<>"",TRUE,FALSE)同样返回非空为TRUE的布尔数组
  • ROW(A:A)/IF(...)会把非空单元格转为对应的行号，空单元格转为错误值
  • LOOKUP默认忽略错误值，会自动匹配小于当前行号的最大有效行号，提取对应A列的值，实现非空值向下填充空白的效果
• 最外层IF就是根据边界判断结果，行号在最后一个非空行之前就返回填充值，否则返回空。

二、更简洁的实现方案 #

你猜测的没错，现在主流电子表格都有原生的更优雅的实现方式：

Google Sheets 方案 #

直接用SCAN遍历函数，逻辑非常直白：
=ARRAYFORMULA(IF(ROW(A:A)<=MATCH(2,1/(A:A<>""),1),SCAN(,A:A,LAMBDA(a,b,IF(b<>"",b,a))),""))
如果不需要限制最后一个非空值之后返回空，还能进一步简化为：
=SCAN(,A:A,LAMBDA(a,b,IF(b<>"",b,a)))
SCAN会逐行遍历A列，遇到非空值就更新当前存储的值，遇到空值就保留上一次存储的非空值，完全匹配你的需求。

Excel 方案 #

• Excel 365/2021可以直接用上面的SCAN函数，语法完全一致
• 低版本Excel可以用简化LOOKUP写法，下拉填充即可：
  文本数据用=LOOKUP("座",A$1:A1)，数字数据用=LOOKUP(9E+307,A$1:A1)

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