谷歌表格双下拉列表带All选项的过滤器实现方法咨询

核心适配逻辑 #

下拉列表选中「All」时，该维度的过滤条件直接返回真，不对结果做限制，仅当选中具体值时才执行匹配校验，两个下拉的条件逻辑独立互不干扰

修改后完整公式 #

=SORT(FILTER('List of Classes, Divided by Classes'!A:P,
IF(B1,('List of Classes, Divided by Classes'!$B:$B=B1),)+
IF(C1,('List of Classes, Divided by Classes'!$C:$C=1),)+
IF(D1,('List of Classes, Divided by Classes'!$D:$D=D1),)+
IF(E1,('List of Classes, Divided by Classes'!$E:$E=E1),)+
IF(F1,('List of Classes, Divided by Classes'!$F:$F=F1),)+
IF(G1,('List of Classes, Divided by Classes'!$G:$G=G1),),
IF(I1="All",TRUE,'List of Classes, Divided by Classes'!P:P=I1),
IF(K1="All",TRUE,'List of Classes, Divided by Classes'!M:M=K1)),1,TRUE)

改动说明 #

• 先给I1、K1两个下拉列表的可选值中新增「All」选项
• 原公式中P列固定匹配I1的逻辑，替换为动态判断：I1为All时跳过该维度过滤，否则执行匹配
• 原公式中M列固定匹配K1的逻辑，做同上处理
• 若需要给B1-G1对应的筛选条件也增加All选项，只需将对应判断逻辑调整为 IF(OR(XX1="",XX1="All"),TRUE, 原匹配逻辑) 即可适配

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