主页 / coding / 问题 / 78535726
Accepted
Michi
Asked: 2024-05-26 23:34:05 +0800 CST

使用多列条件和一行条件计算 SUMPRODUCT（数据范围内有空列）

  • 772
      |   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.

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

excel

2 个回答

  1. 改变：

    c, CHOOSECOLS($A$2:$I$12, 3, XMATCH($K$2, $A$1:$I$1)),

    到：

    c, CHOOSECOLS($A$2:$I$12, 4, XMATCH($K$2, $A$1:$I$1)),

    或者：

    c, CHOOSECOLS($A$2:$I$12, XMATCH("ratio", $A$1:$I$1), XMATCH($K$2, $A$1:$I$1)),

    在此输入图像描述

    • 2
  2. Best Answer

    这对我来说有效，而不是对列位置进行硬编码XMATCH()以使其动态化：

    在此输入图像描述

    • 使用BYROW()：

    =LET(
     α, A2:I12,
     δ, COUNTIF(L2:L4,TAKE(α,,1)),
     ε, COUNTIF(M2:M4,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     SUM(BYROW(CHOOSECOLS(FILTER(α, φ),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
 LAMBDA(a, PRODUCT(a)))))

    • 或者不使用LAMBDA()助手：

    =LET(
     α, A2:I12,
     δ, COUNTIF(L2:L4,TAKE(α,,1)),
     ε, COUNTIF(M2:M4,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     Σ, CHOOSECOLS(FILTER(α, φ),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
     SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))

    LAMBDA()顺便说一句，您也可以使用这个自定义公式：

    =LET(
     λ,LAMBDA(α,δ,ABS(AND(ISBLANK(δ))-MMULT(--(α=TOROW(δ)),{1;1;1}))),
        SUM(FILTER(G2:I12,G1:I1=K2)*D2:D12*λ(A2:A12,L2:L4)*λ(B2:B12,M2:M4)))

    但是，如果列始终固定，您可以删除XMATCH()该ratio列的 并对其进行硬编码。column_index 4

    根据OP的评论：

    这个公式有效。它唯一一次给出 #CALC 是当我删除所有 列条件时。 如果所有列条件为空，是否还有方法计算SUMPRODUCT？

    最终更新的公式：

    =LET(
     α, A2:I12,
     δ, COUNTIF(L2:L4,TAKE(α,,1)),
     ε, COUNTIF(M2:M4,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     Σ, CHOOSECOLS(FILTER(α, IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ)),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
     SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))

    或者，

    =LET(
     α, A2:I12,
     δ, COUNTIF(L2:L4,TAKE(α,,1)),
     ε, COUNTIF(M2:M4,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     SUM(BYROW(CHOOSECOLS(FILTER(α, IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ)),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
     LAMBDA(a, PRODUCT(a)))))
    • 0

相关问题