Início / coding / Perguntas / 79226115
Accepted
Michi
Asked: 2024-11-26 17:44:36 +0800 CST

SUMIF em duas matrizes da função FILTER

  • 772
0 UM B C E E F G E EU Eu E eu
1 Marca A Produto 01 600 Marca A Produto 01 370 Marca C
2 Marca A Produto 02 100 Marca C Produto 02 650
3 Marca A Produto 02 300 Marca C Produto 01 390 Produto 02 200 Produto 02 650
4 Marca B Produto 01 400 Produto 02 800 Produto 01 390
5 Marca B Produto 03 500 Produto 04 900
6 Marca C Produto 02 200 Produto 01 700
7 Marca C Produto 02 800
8 Marca C Produto 04 900
9 Marca C Produto 01 700 Produto 02 350
10 Marca D Produto 03 250 Produto 04 900
11 Marca D Produto 03 460 Produto 01 310
12 Marca D Produto 04 690

Na tabela acima tenho duas listas diferentes:

Lista A Column A:C
Lista BColumn E:G

E Range I3:J6eu Range K3:L4filtro essas duas listas com base nos critérios Cell I1usando as seguintes fórmulas:

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

Tudo isso funciona bem.

Agora meu alvo é a lista em Range I9:J11.
A lista exibe exclusivamente todos os produtos da lista filtrada A em Range I3:J6e subtrai os valores correspondentes por produto da lista filtrada B em Range K3:L4.

Eu sei que poderia fazer isso aplicando a UNIQUEfunção e a SUMIFfunção nos resultados existentes em Range I3:J6e Range K3:L4.

No entanto, eu preferiria ter uma solução na qual eu não precisasse de Range I3:J6e Range K3:L4.
Em vez disso, a lista em Range I9:J11deveria ser exibida sem os "helper ranges".

Você tem alguma ideia de como conseguir isso?

Observação:

  1. List Bnunca conterá valores que não existam em List A. Nem para marca nem para produto.

  2. List Btambém pode conter várias vezes o mesmo produto por marca. Nem sempre é único. Por exemplo, pode acontecer que Product 02apareça x vezes por Brand C.

excel

4 respostas

  1. Best Answer

    GROUPBY()pode ser uma maneira mais simples. Tente-

    =LET(x,VSTACK(HSTACK(E1:F3,G1:G3*(-1)),A1:C12),GROUPBY(CHOOSECOLS(x,2),CHOOSECOLS(x,3),SUM,0,0,,CHOOSECOLS(x,1)=I1))

    Você também pode tentar-

    =LET(p,UNIQUE(VSTACK(FILTER(B:B,A:A=I1),FILTER(F:F,E:E=I1))),
HSTACK(p,MAP(p,LAMBDA(x,SUMIFS(C:C,B:B,x,A:A,I1)-SUMIFS(G:G,F:F,x,E:E,I1)))))

    insira a descrição da imagem aqui

    • 3

  2. Esta fórmula resolve a tarefa:

    =LET(
  r,VSTACK(
    FILTER(B1:C12,A1:A12=I1,{"",0}),
    HSTACK(FILTER(F1:F3,E1:E3=I1,""),-FILTER(G1:G3,E1:E3=I1,0))
  ),
  _c1,TAKE(r,,1),_c2,TAKE(r,,-1),u,UNIQUE(_c1),
  HSTACK(u,MAP(u,LAMBDA(v,SUM(_c2*(_c1=v)))))
)

    insira a descrição da imagem aqui

    A ideia é negar valores do segundo intervalo e então unir ambos os intervalos e a soma pelo produto.

    A fórmula pode ser simplificada usando a nova função GROUPBY, que ainda não está disponível para mim.

    • 2

  3. Mais uma vez, só um pouco de confusão:

    insira a descrição da imagem aqui

    Fórmula em I3:

    =LET(x,VSTACK,GROUPBY(x(B.:.B,F.:.F),x(C.:.C,-G.:.G),SUM,,0,,x(A.:.A,E.:.E)=I1))
    • 2

  4. A fórmula em E6

    =LET(filt1,FILTER(A1:C12,A1:A12=I1),
filt2,FILTER(E1:G3,E1:E3=I1),
sumx1,UNIQUE(CHOOSECOLS(filt1,2)),
sumpr1,BYROW(sumx1,LAMBDA(a,SUMPRODUCT(IF(CHOOSECOLS(filt1,2)=a,CHOOSECOLS(filt1,3),0)))),
sumpr2,BYROW(sumx1,LAMBDA(a,SUMPRODUCT(IF(CHOOSECOLS(filt2,2)=a,CHOOSECOLS(filt2,3),0)))),
HSTACK(sumx1,sumpr1-sumpr2))

    A primeira parte é baseada no seu teste. Na segunda, SUMPRODUCTsoma os valores para os Productnomes exclusivos.

    insira a descrição da imagem aqui

    • 0

relate perguntas