Excel表格Section列自动填充公式的优化方案咨询

需求说明 #

• Heading列输入内容后，Section列自动填充从表格首行到当前行之间最后一个非空的Heading值
• 第一个Heading值之前的Section单元格保持空白

原公式问题 #

你当前使用的公式依赖INDIRECT、ADDRESS这类易失性函数，每次工作表有变动都会触发全局重算，数据量较大时效率极低。

高效替代方案 #

方案1：Excel 365/2021及以上版本（推荐）

使用SCAN动态数组函数，一次性生成整列结果，无需下拉，计算效率最高：

=IFERROR(SCAN("", [Heading], LAMBDA(a, b, IF(b<>"", b, a))), "")

• 原理：SCAN遍历Heading列，遇到非空值就更新结果，空值则继承上一个非空Heading的值，完全匹配需求，且为非易失性函数。

方案2：全版本兼容方案

简化LOOKUP写法，规避易失性函数，同样实现需求：

=IF(ROW()=ROW([Heading]), "", LOOKUP(2, 1/([Heading]$1:[@Heading]<>"") , [Heading]$1:[@Heading]))

• 原理：
  1. [Heading]$1:[@Heading]直接引用从Heading列首行到当前行的区域，无需拼接地址
  2. 1/([Heading]$1:[@Heading]<>"")将非空单元格转为1，空单元格转为错误值，LOOKUP(2,...)会自动匹配最后一个非错误值对应的Heading内容
  3. 开头的判断确保第一个Heading行之前的单元格保持空白（若数据从第2行开始，可将ROW([Heading])改为2）

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