如何用纯Excel公式动态实现：获取上方最近非空单元格值（含+1逻辑）

原VBA逻辑拆解 #

你之前用VBA生成的公式逻辑是：

• 若当前单元格的上一行（A列）内容为#，则当前单元格返回1
• 否则，找到当前单元格上方最近的非空单元格，取其值加1
  但VBA生成的公式硬编码了行号，表格新增行后无法自动适配，以下是纯Excel公式的动态实现方案。

通用Excel公式（兼容所有版本） #

在A列需要应用逻辑的第一个单元格（比如A2）输入以下公式，然后下拉填充即可：

=IF(A1="#",1,LOOKUP(2,1/(A$1:A1<>""),A$1:A1)+1)

公式逻辑说明

• LOOKUP(2,1/(A$1:A1<>""),A$1:A1)：这部分是核心，用来定位上方最近的非空单元格值。A$1:A1<>""生成一个布尔数组，1除以这个数组会得到1（对应非空单元格）或错误值（对应空单元格）；LOOKUP会自动忽略错误值，找到最后一个1对应的单元格值，也就是当前单元格上方最近的非空值。
• 外层IF判断上一行是否为#，满足条件则返回1，否则取最近非空值加1。

Excel 365/2021简化版公式 #

如果你使用的是Excel 365或2021版本，可以用更直观的XLOOKUP函数：

=IF(A1="#",1,XLOOKUP(TRUE,A$1:A1<>"",A$1:A1,,0,-1)+1)

公式逻辑说明

• XLOOKUP(TRUE,A$1:A1<>"",A$1:A1,,0,-1)：通过-1参数指定从后往前查找，找到第一个非空单元格并返回其值，逻辑更直白。
• 同样通过外层IF处理#的特殊场景。

关键优势 #

这两个公式都是动态自适应的，当表格新增行时，下拉填充后公式会自动扩展查找范围，完全不需要重新运行VBA，完美解决原方案无法适配表格扩展的问题。

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