Excel：如何在 .xls 文件中模拟 FILTER 函数的多行筛选效果

文章背景： 最近在处理 Excel 表格时，我遇到了一个看似离谱的问题：同样一个数组公式 =FILTER(A2:A50, B2:B50="C")，在 .xls 文件中只能返回一个结果，而在 .xlsm 文件中却能正确返回所有匹配的值！

这让我开始思考：为什么会有这种差别？背后又藏着哪些 Excel 的“老古董”逻辑？

🔍 问题重现：FILTER 函数在不同格式中的不同表现

我们都知道 FILTER 是 Office 365 / Excel 2021 后引入的动态数组函数，可以一次性返回多个结果，非常适合数据筛选。但奇怪的是：

• 在 .xlsm 文件中（支持宏，现代格式），使用公式 =FILTER(A2:A50, B2:B50="C")，能够返回所有符合条件的结果。
• 而在 .xls 文件中（老旧格式 Excel 97-2003），同样的公式被自动加上了 {} 括号，结果只返回第一个匹配项！

原本的动态数组公式，竟然被降级为了传统的数组公式，这不是“削弱”是什么？

🧠 原因解析：老旧格式的“兼容性劫难”

打开兼容性检查器（如图下所示），你会看到类似这样的提示：

也就是说，.xls 文件不支持新的动态数组机制，所以 Excel 自动将其转换为传统的数组公式，而传统数组公式的行为是只返回单个结果（或要求用 Ctrl+Shift+Enter 输入，配合多行公式下拉才能实现多结果返回）。

因此，.xls 文件天然就无法正常支持动态数组函数。

那么，在 .xls 文件中想要实现类似 FILTER 的多行筛选功能，怎么办？

可以使用经典的数组公式组合来“模拟”这个过程：

=IF(INDEX(A:A, SMALL(IF(B:B="C", ROW(B:B), 4^8), ROW(A1)))=0, "", INDEX(A:A, SMALL(IF(B:B="C", ROW(B:B), 4^8), ROW(A1))))

这个公式的思路是：

1. 用 IF(B:B="C", ROW(B:B), 4^8) 构造符合条件的行号数组；
2. 用 SMALL(..., ROW(A1)) 提取第 N 小的匹配行号；
3. 用 INDEX(A:A, ...) 拿到对应的 A 列内容；
4. 再结合 IF(...=0, "", ...) 来避免零值的出现。

将此公式输入单元格后向下拖动，即可逐行返回所有匹配项，实现动态筛选的效果。

因此，如果你经常使用动态数组公式，强烈建议：

避免使用 .xls 格式，改用 .xlsx 或 .xlsm。