代码之家 › 专栏 › 技术社区 › Michi

为行和列筛选具有多个条件的数据区域,如果为空则忽略这些条件

excel-formula excel

Michi · 技术社区 · 11 月前

  |   A   |B| C|D|  E  |  F  |  G  |  H  |I|  J  |K|    L   |    M   |N|   O    |   P    |   Q    |
--|-------|-|--|-|-----|-----|-----|-----|-|-----|-|--------|--------|-|--------|--------|--------|-
1 |       | |  | |2024 |2024 |2025 |2025 | |     | |RowCrit1|RowCrit2| |ColCrit1|ColCrit2|ColCrit3|
2 |       | |  | | HY1 | HY2 | HY1 | HY2 | |     | |    2025|     HY1| | Brand A|      P1|   Type1|
3 |       | |  | |     |     |     |     | |     | |        |        | | Brand C|      P3|   Type4|
4 |Brand A| |P1| | 500 |  70 |  60 |  80 | |Type1| |        |        | | Brand D|        |        |    
5 |Brand A| |P1| | 100 |  47 | 300 | 100 | |Type4| |        |        | |        |        |        |
6 |Brand A| |P2| | 800 |  21 | 200 | 360 | |Type4| |        |        | |Results |        |        |
7 |Brand B| |P1| |  90 |  56 | 150 | 578 | |Type2| |        |        | |    60  |        |        |
8 |Brand C| |P4| |  45 | 700 | 790 | 800 | |Type2| |        |        | |   300  |        |        |
9 |Brand C| |P2| | 600 | 150 |  40 |  10 | |Type2| |        |        | |   980  |        |        |
10|Brand D| |P1| | 900 |  90 | 980 | 453 | |Type1| |        |        | |        |        |        |
11|Brand D| |P1| | 125 | 854 | 726 | 850 | |Type2| |        |        | |        |        |        |
12|Brand D| |P3| |  70 | 860 | 614 | 140 | |Type3| |        |        | |        |        |        |
13|Brand D| |P4| | 842 | 250 |  85 | 215 | |Type2| |        |        | |        |        |        |
14|Brand E| |P3| | 300 | 324 | 450 | 430 | |Type4| |        |        | |        |        |        |

我想过滤范围 E1:J14 基于中的行标准 Cell L2 和 M2 和中的列标准 Range O2:O4 , P2:P4 和 Q2:Q4 .

在上面的例子中,我能够在中获得正确的结果 Range O7:O10 使用此公式:

=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
等等

(旁注:行条件永远不会为空)

我目前使用的公式只适用于某些情况。
我必须如何修改它才能使其适用于所有人?

0 回复 | 直到 11 月前

rachel 11 月前

我真的希望你这次能得到它。尽我所能让它尽可能无痛。。。。。

=LET(
    a, COUNTIF(O2:O4, A4:A14) + AND(O2:O4 = ""),
    b, COUNTIF(P2:P4, C4:C14) + AND(P2:P4 = ""),
    c, COUNTIF(Q2:Q4, J4:J14) + AND(Q2:Q4 = ""),
    FILTER(FILTER(E4:H14, (E1:H1 = L2) * (E2:H2 = M2), ""), a * b * c, "")
)

简要解释以下公式:

=COUNTIF(O2:O4, A4:A14) + AND(O2:O4 = "")

对于列A中的每个值,如果列A列在ColCrit1中或ColCrit1为空,则返回1,否则返回0。

ColCrit1为空: