Como dividir um valor total do contrato em valores mensais iguais, a partir da data de início do contrato, no Excel?

Question

Eu preciso criar uma fórmula para preencher colunas mensais com receita mensal com base no valor total do contrato dividido de maneira uniforme entre a duração do contrato, a partir da data em que o contrato será assinado. As informações que tenho atualmente no meu relatório são a data de início, o valor total do contrato e a duração (em meses). Eu sei que eu poderia facilmente calcular o valor mensal e, em seguida, inserir manualmente esse valor em cada mês, mas eu tenho um relatório com centenas de entradas, então preciso de uma fórmula que eu possa aplicar a cada entrada.

O formato que estou pensando (mas estou aberto a outras sugestões!) é como abaixo ...

Oqueeuesperoquepareçacomafórmulamágicaseriaessa(eudigiteiessamanualmente)...

Eu deveria então poder criar uma tabela dinâmica para resumir a previsão de receita mensal.

Eu não preciso da fórmula para calcular meses parciais, por ex. se o contrato começar no meio do mês, é bom que isso conte como um mês inteiro de receita. Se houver uma maneira mais fácil de dividir isso em colunas mensais, isso não é um problema - são apenas as informações resumidas que serão apresentadas no meu relatório.

Por favor, deixe-me saber se você tem alguma idéia sobre como eu posso fazer isso e deixe-me saber se você precisar de algum esclarecimento.

Obrigado!

    

Answer 1

Na célula D2 , copie / cole esta fórmula: _{^{(Você pode ter que alterar vírgulas para ponto e vírgula dependendo das configurações regionais.)}}

=IF(AND(D$1>=DATE(YEAR($A2),MONTH($A2),1),D$1<=DATE(YEAR($A2),MONTH($A2)+$B2,1)),$C2/$B2,"")

Quebrando, a lógica é esta:

IF
   (the date in row 1 is >= the first day of the contract start date month)
   AND
   (the date in row 1 is <= the first day of the month of the contract start date plus the duration in months)
THEN
   calculate the total value divided by the number of months
ELSE
   return blank (change "" to 0 or "-" or whatever fits your need)

O ponto chave disso é que os valores na primeira linha - os títulos das colunas - são datas reais e não texto. A forma como a fórmula é escrita, essas datas precisam ser o primeiro de cada mês. Isso não é difícil se você digitar os dois primeiros ou três e depois arrastar o resto; O Excel será incrementado automaticamente. Se isso é um problema, me avise e podemos encontrar outro meio.