Início / coding / Perguntas / 78535726
Accepted
Michi
Asked: 2024-05-26 23:34:05 +0800 CST

Calcule SUMPRODUCT com vários critérios de coluna e critérios de uma linha (com colunas vazias no intervalo de dados)

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

Quero calcular SUMPRODUCTmultiplicando os valores de Column Dpelos valores correspondentes em Column G:I.
A SUMPRODUCTfórmula também deve considerar os filtros para Rowin Cell K2e Columnin Range L2:L4e Range M2:M4.

No exemplo acima o resultado é:

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

Com referência à resposta nesta pergunta , tentei aplicar esta solução:

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

No entanto, ele retorna 0como resultado. Pelo que posso dizer, isso é causado pelo vazio Column C.
Presumo que de alguma forma nesta fórmula ela Column Ddeve ser declarada como coluna fixa a ser usada para o arquivo SUMPRODUCT.

Você tem alguma ideia de como alterar a fórmula para que funcione?

excel

2 respostas

  1. Mudar:

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

    Para:

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

    Ou:

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

    insira a descrição da imagem aqui

    • 2
  2. Best Answer

    Isso funciona do meu lado, em vez de codificar a posição da coluna XMATCH()para torná-la dinâmica:

    insira a descrição da imagem aqui

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

    • Ou sem usar LAMBDA()ajudante:

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

    Aliás, você LAMBDA()também pode usar esta fórmula personalizada:

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

    No entanto, você pode remover o XMATCH()for da ratiocoluna e torná-lo codificado column_index 4se a coluna for sempre fixa.

    De acordo com os comentários do OP:

    A fórmula funciona. A única vez que dá um #CALC é quando removo TODOS os critérios da coluna. Existe também uma maneira de calcular o SUMPRODUCT se TODOS os critérios da coluna estiverem vazios?

    Fórmulas finais atualizadas:

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

    Ou,

    =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

relate perguntas