开启筛选后如何获取Excel列中最后一个可见值？

问题分析 #

你当前使用的=INDEX(M12:M;COUNTA(M12:M))无法适配筛选状态，因为COUNTA会统计所有非空单元格（包括被筛选隐藏的），导致返回整个列表的最后值而非可见区域的最后值。

解决方案 #

根据你使用的Excel版本，选择对应的公式：

方案1：Excel 365/2021及以上（支持动态数组）

使用TOCOL和TAKE组合，自动忽略筛选隐藏行并提取最后一个可见值：

=TAKE(TOCOL(M12:M,1),-1)

• TOCOL(M12:M,1)：将M12开始的非空单元格转为单列，自动排除筛选隐藏的行
• TAKE(..., -1)：提取该单列的最后一个值

方案2：兼容旧版Excel（2019及更早）

使用SUBTOTAL结合INDEX的数组公式（输入后按Ctrl+Shift+Enter确认）：

=INDEX(M12:M,SUMPRODUCT(SUBTOTAL(3,OFFSET(M12:M,ROW(M12:M)-ROW(M12),0,1))))

• SUBTOTAL(3, OFFSET(...))：对每个单元格单独判断是否可见（3对应COUNTA，仅统计可见单元格）
• SUMPRODUCT：求和得到可见非空单元格的总数
• INDEX：根据总数提取对应位置的单元格值

或者使用LOOKUP的简化版公式（同样支持旧版Excel）：

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(M12,ROW(M12:M)-ROW(M12),0)),M12:M)

• 1/SUBTOTAL(...)：生成可见单元格为1、隐藏单元格为错误值的数组
• LOOKUP(2, ...)：忽略错误值，找到最后一个符合条件的可见单元格值

验证 #

以你给出的例子测试：筛选后M12（Name1）、M13（Name2）可见，M14（Name3）隐藏，上述公式均会返回Name2，满足需求。

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