Excel多条件数据筛选：空列条件自动忽略的公式优化

需求说明 #

需要对区域E1:J14进行筛选，规则如下：

• 行条件：单元格L2、M2的值（行条件永不为空）
• 列条件：区域O2:O4、P2:P4、Q2:Q4中的值，仅应用非空的列条件，空条件自动忽略

当前使用的公式仅能适配部分场景，无法覆盖所有空条件组合：

=LET(a;COUNTIF(O2:O4;A3:A14);b;COUNTIF(P2:P4;C3:C14);c;COUNTIF(Q2:Q4;J3:J14);FILTER(FILTER(E3:H14;(E1:H1=L2)*(E2:H2=M2);""));IFS(SUM(a)=0;b;SUM(b)=0;SUM(c)=0;1;a*b*c);""))

不同空条件组合的预期结果：

• 示例1：仅ColCrit1为空 → 60,300,980,450
• 示例2：仅ColCrit2为空 → 60,300,200,980
• 示例3：ColCrit2和ColCrit3为空 → 60,300,200,790,40,980,726,614,85
• 示例4：ColCrit1和ColCrit3为空 → 60,300,150,980,726,614,450

修改后的适配公式 #

通过动态判断每个列条件区域是否为空，自动忽略空条件，公式如下：

=LET(
    行筛选结果, FILTER(E3:H14, (E1:H1=L2)*(E2:H2=M2), ""),
    品牌条件, IF(COUNTA(O2:O4)=0, TRUE, COUNTIF(O2:O4, A3:A14)>0),
    产品条件, IF(COUNTA(P2:P4)=0, TRUE, COUNTIF(P2:P4, C3:C14)>0),
    类型条件, IF(COUNTA(Q2:Q4)=0, TRUE, COUNTIF(Q2:Q4, J3:J14)>0),
    最终筛选条件, 品牌条件*产品条件*类型条件,
    FILTER(行筛选结果, 最终筛选条件, "")
)

公式逻辑说明 #

1. 行筛选：先根据L2（年份）和M2（半年度）筛选出E3:H14中符合行条件的数据。
2. 列条件动态判断：
   • 对每个列条件区域（品牌、产品、类型），用COUNTA检查是否有非空值。如果为空，直接返回TRUE（跳过该条件）；否则判断当前行对应值是否在条件列表中。
3. 组合筛选条件：将三个列条件相乘，只有所有非空条件都满足时，结果才为TRUE。
4. 最终结果输出：用组合后的条件筛选行筛选结果，得到符合要求的数据。

该公式可适配所有空条件组合场景，自动忽略任意为空的列条件。

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