soma de vlookup usando a fórmula de matriz

Question

Eu tenho essa tabela de pagamentos:

account | amount
--------+-------
  101   | 3
  101   | 5
  102   | 7
  103   | 9

Eu nomeei o intervalo desta tabela "pagamentos". Sua primeira coluna eu chamei de "contas". Sua segunda coluna eu chamei de "quantidades".

Eu também tenho outra tabela que atribui contas a grupos:

account | group
--------+-------
  101   | 1
  102   | 1
  103   | 2

Eu nomeei o intervalo desta tabela "grupos".

Agora quero somar todos os pagamentos de contas do grupo 1 usando apenas uma fórmula, sem usar colunas adicionais. Estou tentando fazer isso usando essa fórmula de matriz:

sum(if(vlookup(accounts,groups,2,false)=1,amounts,0))

Eu também tentei essa fórmula de matriz:

sumif(if(vlookup(accounts,groups,2,false)=1,amounts,0))

Essas fórmulas não funcionam. Eu acho que sei porque - parece que vlookup function não retorna array quando usado em uma fórmula de matriz de célula.

Então, como posso calcular isso usando uma fórmula?

    

Answer 1

Parece-me que você pode conseguir o que procura usando a seguinte fórmula:

=SUMPRODUCT(B1:B4*(LOOKUP(A1:A4,C1:C3,D1:D3)=1))

O =1 refere-se ao número do grupo do qual você está procurando as contas. Note que esta é uma fórmula regular, não uma fórmula de matriz.

Eu usei referências de célula regulares em oposição a regiões nomeadas, porque acho que isso facilita relacionar a fórmula à planilha XLS.

A captura de tela a seguir mostra onde estão os diferentes valores e inclui uma fórmula um pouco mais genérica que você pode usar para fazer o mesmo cálculo para diferentes grupos, arrastando o canto inferior direito da célula F1 .

Sevocêquisercontinuarusandonomes,teráqueintroduzirnomesdiferentesparaascolunascomcontas.afórmulaficariaassim:

=SUMPRODUCT(Payments*(LOOKUP(Accounts1,Accounts2,Groups)=1))

Porquestõesdeintegridade,confira Como usar a função LOOKUP no Excel para saber as condições sob as quais você pode use LOOKUP .

    

Answer 2

Sim, você está correto, VLOOKUP não retornará uma matriz, então você precisa de outra abordagem. Suponha que a primeira coluna de grupos seja chamada de accounts2 e a segunda coluna seja numbers , em seguida, experimente essa fórmula de matriz,

=SUM(IF(ISNUMBER(MATCH(accounts,IF(numbers=1,accounts2),0)),amounts))

confirme com CTRL + SHIFT + ENTER