如何利用Excel的SCAN函数补全LET返回数组的空白单元格？

问题背景 #

通过LET函数生成的数组存在第一列空白行的情况，示例如下：

Header 1    Header 2
Value 1      3
             4
Value 2      5
             6

需要将第一列的空白行填充为上一行的有效值，最终目标效果：

Header 1    Header 2
Value 1      3
Value 1      4
Value 2      5
Value 2      6

最初尝试的局限 #

原本尝试通过BYROW与LAMBDA组合实现，但无法在当前行空白时引用上一行的有效值，旧公式如下：

=LET(dataset,HSTACK(Sheet1!M1:M1000,Sheet1!P3:AH1003),f,FILTER(dataset,ISNUMBER(INDEX(dataset,,18))),HSTACK(INDEX(f,,1),INDEX(f,,2),INDEX(f,,4),INDEX(f,,18)))

可行解决方案：引入SCAN函数 #

通过使用SCAN函数追踪并复用前一行的有效值，修改后的公式完全满足需求：

=LET(f,LET(dataset,HSTACK(Sheet1!M1:M1000,Sheet1!P3:AH1003),f,FILTER(dataset,ISNUMBER(INDEX(dataset,,18))),HSTACK(INDEX(f,,1),INDEX(f,,2),INDEX(f,,4),INDEX(f,,18))),r,SCAN(INDEX(f,1,1),INDEX(f,,1),LAMBDA(a,b,IF(b<>"",b,a))),HSTACK(r,DROP(f,,1)))

公式逻辑说明 #

1. 内层LET完成原始数据的整合、筛选和指定列提取，得到初始数组f
2. SCAN函数以数组第一列的首个值为初始追踪值，遍历第一列所有元素：
   • 若当前元素非空，则保留当前值；若为空，则沿用前一个有效值
3. 最后通过HSTACK将填充完成的第一列与原数组剩余列合并，得到最终结果

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