如何用Excel公式获取不连续单元格组的最后非空值？

Excel本身没有内置的JOINRANGES()函数或直接的{,}聚合语法来合并不连续区域为数组，但可以通过现有函数组合实现类似效果，再套用经典的LOOKUP技巧来获取最后非空值，以下是兼容Excel 2007的解决方案：

核心原理回顾 #

针对连续区域的最后非空值，经典公式依赖LOOKUP的特性：当查找值大于所有匹配项时，会返回最后一个满足条件的值。公式逻辑为：

=LOOKUP(2,1/(区域<>""),区域)

我们的目标是把不连续区域转换为可被LOOKUP识别的一维数组，再套用此逻辑。

场景1：同一行的不连续单个单元格 #

例如要获取A1、D1、F1、X1中的最后非空值，可使用CHOOSE函数将单个单元格按顺序组合为一维数组：

=LOOKUP(2,1/(CHOOSE({1,2,3,4},A1,D1,F1,X1)<>""),CHOOSE({1,2,3,4},A1,D1,F1,X1))

• CHOOSE({1,2,3,4},A1,D1,F1,X1)：生成包含指定单元格值的横向数组
• 后续逻辑与连续区域的LOOKUP公式完全一致

如需调整单元格数量，只需修改{1,2,3,4}中的数字个数，并对应增减单元格引用即可。

场景2：同一列的不连续区域 #

例如要获取A1:A4和A10:A15中的最后非空值，可使用数组常量纵向合并区域，再结合INDEX遍历数组：

=LOOKUP(2,1/(INDEX({A1:A4;A10:A15},ROW(INDIRECT("1:"&ROWS(A1:A4)+ROWS(A10:A15))))<>""),INDEX({A1:A4;A10:A15},ROW(INDIRECT("1:"&ROWS(A1:A4)+ROWS(A10:A15)))))

关键部分解释： #

• {A1:A4;A10:A15}：用分号;纵向合并两个列区域，生成包含所有单元格的虚拟数组
• ROW(INDIRECT("1:"&ROWS(A1:A4)+ROWS(A10:A15)))：生成从1到合并后总行数的连续数字序列，用于遍历虚拟数组
• INDEX(..., 序列)：提取虚拟数组中每个位置的值，转换为LOOKUP可处理的一维数组

注意：此公式为数组公式，Excel 2007中需按Ctrl+Shift+Enter完成输入（输入后公式会自动被大括号{}包裹）。

如果是三个或更多列区域，只需扩展数组常量（如{A1:A4;A10:A15;A20:A25}），并更新ROWS求和部分即可。

场景3：同一行的不连续区域 #

例如要获取A1:D1和F1:I1中的最后非空值，只需将数组常量的分隔符改为逗号,,并使用COLUMN生成遍历序列：

=LOOKUP(2,1/(INDEX({A1:D1,F1:I1},COLUMN(INDIRECT("1:"&COLUMNS(A1:D1)+COLUMNS(F1:I1))))<>""),INDEX({A1:D1,F1:I1},COLUMN(INDIRECT("1:"&COLUMNS(A1:D1)+COLUMNS(F1:I1)))))

• 逗号,用于横向合并行区域
• COLUMNS函数计算合并后的总列数，配合COLUMN生成遍历序列
• 同样需按Ctrl+Shift+Enter作为数组公式输入

注意事项 #

1. 所有合并的区域必须是同方向的（要么都是纵向列，要么都是横向行），不能混合行列
2. Excel 2007及更早版本中，数组常量最多支持64个区域合并，若超过需拆分逻辑
3. 若区域包含错误值（如#N/A），需在公式中增加ISERROR判断，避免公式报错

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