Somando dinamicamente os valores da tabela - Microsoft Excel

Question

Estou criando uma planilha que modela a venda de algumas casas unifamiliares.

Eu construí um cronograma de vendas que diz que X das casas serão vendidas por mês, e eu estou procurando multiplicar esse número X pelos valores apropriados (os valores não são os mesmos, e variam de US $ 300.000 a US $ 450.000 )

Digamos que eu venda 4 no primeiro mês, 4 no segundo mês e 4 no último mês, e os valores de venda são os seguintes:

1 - 300k

2 - 300k

3 - 300k

4 - 350k

5 - 350k

6 - 350k

7 - 400k

8 - 400k

9 - 400k

10 - 450k

11 - 450k

12 - 450k

Posso criar uma fórmula que analise o número de casas vendidas no primeiro mês (4) e, em seguida, some os valores 300k + 300k + 300k + 350k (representando as 4 primeiras residências da lista)

O segundo mês deve ser relativamente similar. 4 casas serão vendidas nesse mês, mas agora eu gostaria de somar 5 em casa - valores 8 (350k + 350k + 350k + 400k)

E para o 3º mês de 4 casas vendidas: 400k + 400k + 400k + 450k.

Existe uma função que pode adicionar dinamicamente diferentes valores de tabela com base em algum tipo de critério?

Obrigado antecipadamente!

    

Answer 1

Esta fórmula fará o que você precisa.

=SUMPRODUCT($B$1:$B$12,--(ISNUMBER(MATCH($A$1:$A$12,ROW(INDIRECT("1:"&SUM($E$2:E2))),0))))-SUM($F$1:F1)

Esta é uma fórmula de tipo de matriz e, portanto, as referências precisam ser limitadas apenas ao conjunto de dados.

ROW(INDIRECT("1:"&SUM($E$2:E2)) cria uma matriz de números. Em F2 , a matriz é {1,2,3,4} , em seguida, F3 , torna-se {1,2,3,4,5,6,7,8} e assim por diante.

A correspondência tenta encontrar os valores em $A$1:$A$12 , um de cada vez, ou retorna um número ou um erro.

Quando o erro SUMPRODUCT ignora o valor correspondente em $B$1:$B$12 , caso contrário, soma o valor.

Como em F3 somaremos todos os valores de 1 a 8, precisamos remover o valor já somado de F2 . Assim, o SUM($F$1:F1) .