如何简化Excel FILTER函数公式，同时排除指定区域的精确与部分匹配项

简化后的公式（适用于Excel 365/2021及以上版本） #

=FILTER(B3:B600,BYROW(B3:B600,LAMBDA(x,NOT(OR(ISNUMBER(SEARCH(Q3:Q100,x)),ISNUMBER(MATCH(x,R3:R100,0)))))*(B3:B600<>""))

公式说明 #

• BYROW(B3:B600,LAMBDA(x,...))：逐行遍历B3:B600中的每个单元格，将当前单元格值命名为x
• ISNUMBER(SEARCH(Q3:Q100,x))：检查x是否包含Q3:Q100中的任意字符串（部分匹配，不区分大小写；若需区分大小写，替换为FIND）
• ISNUMBER(MATCH(x,R3:R100,0))：检查x是否与R3:R100中的任意值精确匹配
• OR(...)：只要满足上述两个排除条件中的任意一个，就标记为需要排除的条目
• NOT(...)：取反逻辑，只保留不符合任何排除条件的有效条目
• *(B3:B600<>"")：额外过滤掉B列中的空单元格

旧版Excel兼容公式（无LAMBDA支持） #

如果使用不支持动态数组和LAMBDA的旧版Excel，可改用SUMPRODUCT实现：

=FILTER(B3:B600,(SUMPRODUCT(--ISNUMBER(SEARCH(Q3:Q100,B3:B600)))+SUMPRODUCT(--ISNUMBER(MATCH(B3:B600,R3:R100,0))))=0)*(B3:B600<>"")

公式逻辑：通过SUMPRODUCT统计每个单元格的部分匹配、精确匹配次数，两者相加为0时，说明该单元格不属于任何排除范围，予以保留。

核心优势 #

这个优化方案既整合了部分匹配与精确匹配的排除逻辑，又严格区分了两种匹配规则——只有明确命中Q列部分匹配或R列精确匹配的条目才会被排除，彻底避免了统一筛选条件可能导致的有效条目误删问题，同时公式结构更直观易读。

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