如何通过非数组公式组合按‘Complete’状态筛选数据？

当然有可行的方案！不用数组公式（无需按Ctrl+Shift+Enter）和VBA，咱们可以用两种不同的非数组公式组合来实现需求，适配不同的Excel版本：

方法1：适配Excel 2010及以后版本（兼容旧版） #

如果你的Excel版本不支持动态数组，推荐用INDEX+AGGREGATE的组合公式，完全属于普通公式范畴：

假设你的数据范围是$B$4:$E$13，状态列是$E$4:$E$13，想要从G4单元格开始输出筛选结果：

1. 在G4单元格输入以下公式：

=IFERROR(INDEX($B$4:$B$13,AGGREGATE(15,6,(ROW($E$4:$E$13)-ROW($E$3))/($E$4:$E$13="Complete"),ROW(1:1))),"")

2. 向右拖动公式到J4（对应原数据的4列），再向下拖动整个区域，直到单元格显示空值为止。

公式原理拆解 #

• AGGREGATE(15,6,...)：15代表调用「SMALL（取最小值）」逻辑，6代表忽略所有错误值；
• (ROW($E$4:$E$13)-ROW($E$3))：把原数据的行号转换成相对于数据区域的偏移量（比如E4对应偏移量1，E5对应2）；
• /($E$4:$E$13="Complete")：当E列单元格等于Complete时，这个计算式结果为偏移量本身；否则会生成#DIV/0!错误，被AGGREGATE的6参数忽略；
• ROW(1:1)：随着公式向下拖动，会自动变成ROW(2:2)、ROW(3:3)，依次取出第1、2、3...个符合条件的偏移量；
• INDEX根据偏移量提取对应列的数据，IFERROR用来处理没有更多符合条件数据时的空值显示。

方法2：适配Excel 365/2021及以后版本（更简洁） #

如果你的Excel支持动态数组功能，直接用FILTER函数就搞定了，这是官方内置的非数组公式，无需手动填充：

=FILTER($B$4:$E$13,$E$4:$E$13="Complete","无符合条件数据")

输入公式后，Excel会自动溢出所有符合条件的行数据，不需要拖动填充，非常省心。

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