如何优先不使用lambda函数将Excel静态表格转为动态可溢出表格

适用版本 #

Excel 365 / Excel 2021 及以上支持动态数组的版本

方案1：XLOOKUP 实现（推荐，逻辑更简洁） #

直接在D3单元格输入以下公式：
=B3:INDEX(B:B, XLOOKUP(TRUE, ISBLANK(B3:B1000), ROW(B3:B1000)) - 1)

逻辑说明 #

• ISBLANK(B3:B1000) 遍历指定区间，返回布尔数组，空白单元格对应值为TRUE
• XLOOKUP 定位第一个出现TRUE的位置，返回对应行号
• 行号减1得到第一个空白单元格上方的最后一行数据行号，用INDEX定位后和起始单元格B3组成连续区间，自动向下溢出

方案2：MATCH 实现（兼容性更好，不需要XLOOKUP支持） #

直接在D3单元格输入以下公式：
=B3:INDEX(B:B, MATCH(TRUE, ISBLANK(B3:B1000), 0) + ROW(B3) - 2)

逻辑说明 #

• MATCH(TRUE, ISBLANK(B3:B1000), 0) 返回B3:B1000区间内第一个空白单元格的相对位置
• 结合起始行号ROW(B3)换算得到对应绝对行号，减2得到最后一个有效数据行的绝对行号
• 同样和B3组成连续区间自动溢出

注意事项 #

• 公式中的B3:B1000可根据实际业务的最大数据量调整区间上限，只要覆盖你可能用到的最大行数即可
• 输入公式后无需按组合键，直接回车就会自动生成溢出数组，后续所有引用这个区间的位置都可以直接用D3#调用
• 两个方案均未使用LAMBDA类函数，完全符合要求

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