主页 / coding / 问题 / 78533198
Accepted
Michi
Asked: 2024-05-26 01:23:07 +0800 CST

使用多列条件和一行条件计算 SUMPRODUCT

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

3 个回答

  1. 因此，尝试实现以下内容以获得所需的输出，我不确定列ratio索引是否应该在此处进行硬编码：

    在此输入图像描述

    • 使用LAMBDA()名为的辅助函数BYROW()

    =LET(
     α, A2:G12,
     δ, COUNTIF(J2:J3,TAKE(α,,1)),
     ε, COUNTIF(K2:K3,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     SUM(BYROW(CHOOSECOLS(FILTER(α, φ),3,XMATCH(I2,A1:G1)),LAMBDA(a, PRODUCT(a)))))

    • 或者不使用LAMBDA()辅助函数：

    =LET(
     α, A2:G12,
     δ, COUNTIF(J2:J3,TAKE(α,,1)),
     ε, COUNTIF(K2:K3,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     Σ, CHOOSECOLS(FILTER(α, φ),3,XMATCH(I2,A1:G1)),
     SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))

    用例一： --> 当ColCrit1为空时

    在此输入图像描述

    用例二： --> 当ColCrit2为空时

    在此输入图像描述

    类似的想法来自OP帖子的最后一个答案：Using a list (range) of columns criteria (not single cells) in FILTER Formula

    LAMBDA()使用 Custom + 的另一种方法MMULT()：

    =LET(
     λ,LAMBDA(α,δ,ABS(AND(ISBLANK(δ))-MMULT(--(α=TOROW(δ)),{1;1}))),
   SUM(FILTER(E2:G12,E1:G1=I2)*C2:C12*λ(A2:A12,J2:J3)*λ(B2:B12,K2:K3)))
    • 1
  2. Best Answer

    尝试：

    =LET(
    a, COUNTIF($J$2:$J$3, $A$2:$A$12),
    b, COUNTIF($K$2, $B$2:$B$12),
    c, CHOOSECOLS($A$2:$G$12, 3, XMATCH($I$2, $A$1:$G$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

    要考虑 中没有条目ColCrit1，或者 中没有条目ColCrit2，您可以修改它：

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

    在此输入图像描述

    • 0

  3. SUMPRODUCT使用,COUNTIF和FILTER, 和 with 的版本

    • COUNTIF多个单元格中的标准可用于标准的常用功能
    • 包括品牌或产品中的所有空白
    • 如果条件为空，则包括所有年份

    计算结果：

    =SUMPRODUCT(
_filter_by_year,
_ratios,
_include(_brands, _col_crit_1),
_include(_products, _col_crit_2))

    创建以下命名范围和函数（这些将在工作簿的所有工作表中可用）：

    姓名 指的是
    _data =SO_74776702!$A$1:$G$12
    _brands =INDEX(DROP(_data,1),,1)
    _col_crit_1 =SO_74776702!$J$2:$J$5
    _col_crit_2 =SO_74776702!$K$2:$K$5
    _row_crit =SO_74776702!$I$2:$I$5
    _years =TAKE(DROP(_data,,4),1)
    _products =INDEX(DROP(_data,1),,2)
    _ratios =DROP(TAKE(DROP(_data,,2),,1),1)
    _filter_by_year =LET(_yd, DROP(_data,1, 4), _filtered, IF(_all_blanks(_row_crit), _yd, FILTER(_yd, fx_countifs_or(_years,_row_crit))), _sum_cols(_filtered) )
    _all_blanks =LAMBDA(range, COUNTBLANK(range) = ROWS(range))
    _include_all =LAMBDA(range, IFERROR(range * 0, 0) + 1)
    _include =LAMBDA(_range,_criteria, IF(_all_blanks(_range), _include_all(_range), fx_countifs_or(_range,_criteria)) )
    _sum_cols =LAMBDA(_cols,BYROW(_cols,LAMBDA(_row,SUM(_row))))
    fx_countifs_or =LAMBDA(_range,_criteria, MAP(_range, LAMBDA(_row, N(OR(COUNTIF(_row, _criteria))) )))

    公式与结果

    • 0

相关问题