Início / coding / Perguntas / 78533198
Accepted
Michi
Asked: 2024-05-26 01:23:07 +0800 CST

Calcule SUMPRODUCT com vários critérios de coluna e critérios de uma linha

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

Quero Cell J7calcular SUMPRODUCTcom base em vários critérios de coluna e critérios de uma linha:

  1. Os critérios para a linha que você pode encontrar emCell I2
  2. Os critérios para as colunas você pode encontrar em Range J2:J3e Range K2:K3.

No exemplo acima o resultado é:

J7 = 500x7% + 90x5% = 39.5

Até agora consegui extrair SUMcom base nos critérios usando esta fórmula:

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

Porém, não tenho ideia de como alterar essa fórmula para que ela calcule SUMPRODUCTe inclua todos os critérios tanto para coluna quanto para linha.

excel

3 respostas

  1. Portanto, tente implementar o seguinte para obter os resultados desejados. Eu não tinha certeza se o ratioíndice da coluna deveria ser codificado aqui:

    insira a descrição da imagem aqui

    • Usando LAMBDA()a função auxiliar chamadaBYROW()

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

    • Ou sem usar LAMBDA()funções auxiliares:

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

    Caso de uso um: -> Quando ColCrit1está vazio

    insira a descrição da imagem aqui

    Use o caso dois: -> Quando ColCrit2está vazio

    insira a descrição da imagem aqui

    Idéia semelhante extraída da última resposta da postagem do OP: Usando uma lista (intervalo) de critérios de coluna (não células únicas) na fórmula FILTER

    Mais uma maneira de usar Custom LAMBDA()+ 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

    Tentar:

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

    Para contabilizar nenhuma entrada ColCrit1ou nenhuma entrada em ColCrit2, você pode modificá-lo:

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

    insira a descrição da imagem aqui

    • 0

  3. Uma versão usando SUMPRODUCT, COUNTIFe FILTER, e com

    • Critérios em múltiplas células, funcionalidade usual disponível para COUNTIFcritérios
    • Incluir todos os espaços em branco em marcas ou produtos
    • Incluir todos os anos se o critério estiver em branco

    Calcule o resultado com:

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

    Crie os seguintes intervalos e funções nomeados (estes estarão disponíveis em todas as planilhas da pasta de trabalho):

    Nome Refere-se a
    _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))) )))

    Fórmula com resultado

    • 0

relate perguntas