Michi's questions

在 中，我想根据 中的过滤器Range E5:G13过滤数据，并首先按 对其进行排序。因此，我在 中实现了此公式：Column A:CCell F1Column B
Cell E5

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
b)

一切运行良好。

现在 - 作为第二步 - 我想根据 对过滤后的列表进行升序排序date。
但是，此排序仅适用于 中的值Column B。
在示例中，这意味着 的排序airline应保持原样，并且在 中的排序应按升序airline列出date。预期结果Range E5:G13显示它应该是什么样子。
在 SQL 中，这个问题可以通过 函数解决PARTITION BY。

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
c;SORT(b;1;1);
c)

我需要如何更改cExcel 公式中的部分才能使其起作用？

我Cell E8已经实现了下面的公式来Columns A:C根据中的标准进行过滤Cell E2。

=LET(
a,FILTER(A2:C18,A2:A18<=E2),
b,CHOOSECOLS(a,2),
c,CHOOSECOLS(a,3),
d,HSTACK(b,c),
d)

一切运行良好。

然而，问题是

1. 没有prod.列出唯一且
   
2. 并不是val.每个独特的prod.
   

第一个问题很容易用函数解决UNIQUE，但我不知道如何用求和来解决第二个问题。
我知道在最新的 Excel 版本中有一个GROUPBY可以解决这个问题的函数。
但是，我的 Excel 中还没有这个函数。我使用的是 Office365（版本 2410）。

您知道还有其他方法可以使它发挥作用吗？

在 中我实现了以下公式。它根据、 和 中的条件Cell G8过滤 中的列表：Column A:ECell G2Range H2:H4Range I2:I4

=LET(
a,COUNTIF(H2:H4,B1:B20)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C1:C20)+AND(I2:I4=""),
c,FILTER(A1:E20,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

一切运行良好。

现在，我想知道是否有可能以某种方式摆脱COUNTIF公式部分的行限制，使其看起来像这样：

=LET(
a,COUNTIF(H2:H4,B:B)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C:C)+AND(I2:I4=""),
c,FILTER(A:E,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

但是，此公式返回#VALUE!。

我需要如何修改公式才能使其发挥作用？

注意：我喜欢在公式中通过添加变量来轻松添加其他过滤器COUNTIF。因此，解决方案也应保持这种灵活性。

在上表中我有两个不同的列表：

列表 A =Range A1:C12
列表 B = Range E1:G4 （信息：此列表中的产品始终是唯一的）

Range E8:E10在用此公式过滤后的结果中List A：

=UNIQUE(FILTER(B:B;A:A=E7))

这一切都有效。

现在，Range F8:G10我想向列表 B 中过滤后的产品添加其他信息。
到目前为止，我可以使用以下公式对第一行执行此操作：

=DROP(FILTER(E1:G4;E1:E4=E8);;1)

我需要如何更改公式才能使其适用于所有行？

在上表中我有两个不同的列表：

列表AColumn A:C
列表BColumn E:G

在Range I3:J6和中我使用以下公式Range K3:L4根据标准过滤这两个列表：Cell I1

Cell I3 =CHOOSECOLS(FILTER(A1:C12,A1:A12=I1),2,3) 
Cell K3 =CHOOSECOLS(FILTER(E1:G12,E1:E12=I1),2,3)

一切运行良好。

现在我的目标是 中的列表Range I9:J11。
该列表唯一地显示 中的筛选列表 A 中的所有产品，Range I3:J6并从 中的筛选列表 B 中减去每个产品的相应值Range K3:L4。

我知道我可以通过将UNIQUE函数和函数应用于和SUMIF中的现有结果来做到这一点。Range I3:J6Range K3:L4

但是，我更希望有一个不需要Range I3:J6和 的解决方案Range K3:L4。相反，应该显示
中的列表而不显示“辅助范围”。Range I9:J11

您知道如何实现这个目标吗？

笔记：

1. List B永远不会包含不存在的值List A。无论是品牌还是产品。
   

2. List B每个品牌也可能包含多次相同的产品。它并不总是唯一的。例如，可能会出现Product 02每次出现 x 次的情况Brand C。

在中Range G3:G6我想根据在中输入的行条件转置值Cell G1。
（注意：列出的产品Column A始终是唯一的。）

在此示例中，Row B2:E2forProduct A被转置并显示在 中Range G3:G6。

到目前为止我已经能够开发出这个公式：

=LET(
a,TRANSPOSE(B2:E2),
IFERROR(INDEX(FILTER(a,a<>0),SEQUENCE(4,,1)),""))

此公式有效。
但是，我不知道如何使Range B2:E2此公式中的 灵活，以便它根据 中的输入而变化Cell G1。

您是否知道如何修改它以使其发挥作用？

我Range A7:B11已经使用以下公式创建了一个数组：

=HSTACK(A1:A5;C1:C5)

现在，我想将公式应用于这个数组并仅过滤掉最后的列表如下所示的FILTER值：=Project B

0 |    A      |  B  |   C  |
--|-----------|-----|------|--
  |           |     |      |
7 | Project B | 200 |      |
8 | Project B | 600 |      | 
9 |           |     |      |

我尝试了这样的事情：

=FILTER(A7#;A7#="Project B")

它返回#VALUE!

我认为问题在于我需要以某种方式告诉公式Filter，过滤器应该应用于数组的第一列。

你知道如何改变公式才能让它发挥作用吗？

在中Range A7:B11我想Range A1:A5根据中的值按降序排列值Range C1:C5。因此，我尝试使用以下公式：

=SORT(A1:C5;;-1)

但是，当我这样做时，结果如下所示：

我需要如何修改SORT公式才能

1. 根据中的值以正确的降序获取列表Range C1:C5？
   
2. Column B忽略其中的空Range A1:C5并且不显示 0 值？

我Cell B6:B9想

1. 按Column A降序排列，
   
2. Cell B4始终显示从输入的行开始输入的行数Cell B2。

到目前为止我已经能够开发出这个公式：

=CHOOSEROWS(SORT(A1:A10,,-1),SEQUENCE(C4,,C2))

这个公式几乎给了我结果。

然而，一旦到达起点下方不超过 4 行的位置（如上例所示），公式就会返回错误#VALUE!。

这个想法是，在这种情况下，公式会显示剩余的 3 行，而只剪掉第四行。

我需要如何修改它才能使其工作？

在 中Column B我想提取中字符串中出现的第一个数字Column A。
在 中Column C我想提取中字符串中出现的最后一个数字Column A。

我尝试应用该问题的解决方案，但它们对我的情况不起作用。

我需要哪个公式才能得到Column B和的预期结果Column C？

我Column B想

1. 按降序排列值Column A，并
2. 那么只显示按降序排列的第 5,6,7 位的值。
   

到目前为止，我已经能够使用这个公式解决第一步：

=TAKE(SORT(A1:A10;;-1);5)

但是，我现在不知道如何实现第二步。应该是这样的

=TAKE(SORT(A1:A10;;-1);5-7)

您知道我需要哪个公式才能仅显示排名 5,6,7的值吗？

注意：5、6、7 只是本题的一个例子。
如果有公式可以灵活定义这个范围就更好了。

我Column L想显示每种产品的第一个边距，即每行中的第二个非空单元格。

到目前为止我已经能够开发出这个公式：

=XLOOKUP(TRUE,ISNUMBER(C2:J2),C2:J2)

它正确地给出了第一笔销售额，即每行中第一个非空单元格。
我需要如何修改它才能显示每行中的第二个非空单元格？

在 中，Range M6:M14我想根据 中的值获取相应的数据Range K6:14。
但是，我希望具有以下灵活性：

1. lookup_array根据输入使用Cell M3和
   
2. return_array根据 中的输入使用。Cell M5例如，如果我要从 切换到 ，则2024-M应该列出Product中的相应产品。Column A
   

参考这个问题的答案我正在寻找这样的内容：

=LET(
     _A, K6:K14,
     _B, >> flexible based on input in Cell M3 <<,
     _RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))),
     _RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))),
     XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))

注意：
中的值lookup_array不是唯一的。在此示例中130，400和70。因此，如果中的值的频率Range K6:14与中的值的频率不匹配，return_array则它们应该按降序Range M6:M14与中的值匹配。 （上面的示例公式已经以这种方式处理）。Range K6:14

我需要什么公式才能使Range M6:M14工作成果灵活lookup_array而有成效return_array？

正如您在屏幕截图中看到的，我设置了parameterwith 数据类型date并选择了日期2024-05-27。
然而，当我在 SQL 中查询这个参数时，我得到了一个完全不同的值 ( 1992-01-01)。

您知道为什么会出现这种情况以及如何解决吗？

      |   A   |B |C|   D  |E|    F   |   G  |   H  |   I  |J|    K    |     L    |    M     |   
------|-------|--|-|------|-|--------|------|------|------|-|---------|----------|----------|--
    1 |       |  | |ratio | | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
    2 |Brand A|P1| |  7%  | |   yes  |  500 |   70 |   60 | |   2024  |  Brand A |    P1    |         
    3 |Brand A|P2| |  8%  | |   yes  |  100 |   47 |  300 | |         |  Brand D |    P4    |            
    4 |Brand A|P2| | 10%  | |   yes  |  800 |   21 |  200 | |         |          |          |          
    5 |Brand B|P1| |  5%  | |   yes  |   90 |   56 |  150 | |         |          |          |                     
    6 |Brand C|P4| |  3%  | |   no   |   45 |  700 |  790 | |         |          |          |                  
    7 |Brand C|P2| |  8%  | |   no   |  600 |  150 |   40 | |  Result |    191.5 |          |          
    8 |Brand D|P1| | 12%  | |   yes  |  900 |   90 |  980 | |         |          |          |                    
    9 |Brand D|P1| | 20%  | |   yes  |  125 |  854 |  726 | |         |          |          |          
    10|Brand D|P3| | 19%  | |   yes  |   70 |  860 |  614 | |         |          |          |          
    11|Brand D|P4| |  2%  | |   yes  |  842 |  250 |   85 | |         |          |          |          
    12|Brand E|P4| |  6%  | |   no   |  300 |  324 |  450 | |         |          |          |   
       

我想SUMPRODUCT通过将 中的值Column D与 中的相应值相乘来计算Column G:I。
该SUMPRODUCT公式还应考虑RowinCell K2和ColumninRange L2:L4和 的过滤器Range M2:M4。

在上面的例子中，结果是：

L7 = 7%x70 + 12%x90 + 20%x854 + 2%x250 = 191.5

参考这个问题的答案，我尝试应用这个解决方案：

=LET(
    a, IF(
        COUNTA($L$2:$L$3) = 0,
        N(ISNUMBER(ROW($A$2:$A$12))),
        COUNTIF($L$2:$L$3, $A$2:$A$12)
    ),
    b, IF(
        COUNTA($M$2:$M$3) = 0,
        N(ISNUMBER(ROW($B$2:$B$12))),
        COUNTIF($M$2:$M$3, $B$2:$B$12)
    ),
    c, CHOOSECOLS($A$2:$I$12, 3, XMATCH($K$2, $A$1:$I$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

但是，它会返回0结果。据我所知，这是由空引起的Column C。
我假设在这个公式中Column D必须将其声明为固定列才能用于SUMPRODUCT.

您知道如何更改公式以使其发挥作用吗？

   |   A   | B|    C    |D|     E    |     F    |      
---|-------|--|---------|-|----------|----------|-
   |       |  | Revenue | | ColCrit1 | ColCrit2 |
2  |Brand A|P1|    500  | |  Brand A |    P1    |         
3  |Brand A|P2|    100  | |  Brand B |    P3    |            
4  |Brand A|P2|    800  | |  Brand D |          |
5  |Brand B|P1|     90  | |          |          |
6  |Brand C|P4|     45  | |          |          |
7  |Brand C|P2|    600  | |   Result |    500   |
8  |Brand D|P1|    900  | |          |     90   |
9  |Brand D|P1|    125  | |          |    900   |
10 |Brand D|P3|     70  | |          |    125   |
11 |Brand D|P3|    842  | |          |     70   |
12 |Brand E|P4|    300  | |          |    842   |

   

我想根据多列条件过滤列表。
用户可以在Range E2:E4和中输入列标准Range F2:F4。

到目前为止，我只能在单个单元格条目上创建过滤公式：

F7 = FILTER(C2:C12,IF(E2="",1,(A2:A12=E2))*IF(F2="",1,(B2:B12=F2)))

您知道是否有一个公式可以将标准应用为范围？
像这样的东西：

F7 = FILTER(C2:C12,IF(E2:E4="",1,(A2:A12=E2:E4))*IF(F2:F4="",1,(B2:B12=F2:F4)))

   |   A   | B|    C  |   D    |   E  |   F  |   G  |H|    I    |     J    |    K     |  L
---|-------|--|-------|--------|------|------|------|-|---------|----------|----------|-----
   |       |  | ratio | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
2  |Brand A|P1|   7%  |   yes  |  500 |   70 |   60 | |   2023  |  Brand A |    P1    |         
3  |Brand A|P2|   8%  |   yes  |  100 |   47 |  300 | |         |  Brand B |          |            
4  |Brand A|P2|  10%  |   yes  |  800 |   21 |  200 | |         |          |          |          
5  |Brand B|P1|   5%  |   yes  |   90 |   56 |  150 | |         |          |          |                     
6  |Brand C|P4|   3%  |   no   |   45 |  700 |  790 | |         |          |          |                  
7  |Brand C|P2|   8%  |   no   |  600 |  150 |   40 | |  Result |    39.5  |          |          
8  |Brand D|P1|  12%  |   yes  |  900 |   90 |  980 | |         |          |          |                    
9  |Brand D|P1|  20%  |   yes  |  125 |  854 |  726 | |         |          |          |          
10 |Brand D|P3|  19%  |   yes  |   70 |  860 |  614 | |         |          |          |          
11 |Brand D|P3|   2%  |   yes  |  842 |  250 |   85 | |         |          |          |          
12 |Brand E|P4|   6%  |   no   |  300 |  324 |  450 | |         |          |          |   

   

我Cell J7想SUMPRODUCT根据多列条件和一行条件来计算：

1. 您可以在其中找到该行的条件Cell I2
   
2. Range J2:J3您可以在和中找到列的条件Range K2:K3。
   

在上面的例子中，结果是：

J7 = 500x7% + 90x5% = 39.5

到目前为止，我已经能够SUM使用以下公式根据标准提取：

=SUM(IF(COUNTIF(I2;C1:G1)*COUNTIF(J2:J3;A2:A12)*COUNTIF(K2:K3;B2:B12);C2:G12))

但是，我不知道如何更改此公式，以便它计算SUMPRODUCT并包含列和行的所有条件？

我希望 Excel 根据 中输入的日期计算一年前的数据Cell A2。
到目前为止，当我使用这个公式时，这没有任何问题：

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))

然而，一旦我有闰年并且开始日期Cell A2是 2 月 29 日，一年前的计算就会在 3 月 1 日结束，正如您在 中看到的Column A。

我需要如何更改公式才能获得如图所示的预期结果Column B？