Fóruns
Microsoft Excel - 2...
Datas & Horas
Cálculo do valor me...
 
Notifications
Clear all

Cálculo do valor mensal do budget

 
Datas & Horas
8 Posts
1 Usuários
0 Reactions
1,630 Visualizações
RSS
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Pessoal

Peço ajuda no problema a seguir (vide planilha postada para maior clareza):

Dado o Valor total orçado de um contrato, a data de início do contrato e data de término do contrato, a planilha deverá alocar os valores mensais previstos nos meses entre as datas de início e término do contrato. Para os meses que não correspondem ao intervalo contratual, o valor no campo deverá ser zero.

Problema: As fórmulas do intervalo F8:AC8 deverão considerar o valor fracionado no mês caso a data de início ou fim do contrato não caia no fim do mês.
Ex: Data fim do contrato é 15/03/2012. Neste mês, considerar 15 dias de execução, multiplicado pelo valor diário previsto (valor do contrato dividido pelo prazo)

O que já fiz..: Consegui na fórmula testar se o ANO e o MÊS vigente pertencem ao intervalo do contrato. Resta calcular o valor correto previsto p/ o mês, quando cair em fração de mês...
Não consigo fazer com que as fórmulas do intervalo F8:AC8 considerem a fração de mês para cálculo do valor que irá executar no mês. Outra coisa: A soma dos valores mensais tem que ser igual ao valor estimado em B8. Aguardo um help dos feras deste forum..

Abs

JPOMAGA.

 
Postado : 28/06/2011 10:18 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

JPOMAGA, na célula F11 coloca essa fórmula:

=SE(OU(ANO(F10)&MÊS(F10)*1<ANO($C$11)&MÊS($C$11)*1;ANO(F10)&MÊS(F10)*1>ANO($D$11)&MÊS($D$11)*1);0;SE(MÊS($C$11)&ANO($C$11)=MÊS(F10)&ANO(F10);$B$11/$E$11*(F9-DIA($C$11)+1);SE(MÊS($D$11)&ANO($D$11)=MÊS(F10)&ANO(F10);$B$11/$E$11*(DIA($D$11)-1);$B$11/$E$11*F9)))

Depois é só arrastar para o lado....

Qualquer coisa da o grito...
Abraço

 
Postado : 28/06/2011 11:07 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Bernardo

Agradeço pela pronta resposta. Deu certo em parte, mas apareceram valores adicionais >> 4.189,19 4.054,05 e 4.189,19 nos meses de out/12, nov/12 e dez/12 respectivamente. Você pode checar o que houve?

Segue novamente a planilha com a sua fórmula já inserida no intervalo F8:AC8.

Idéia: Talvez usando ÍNDICE, DESLOC e CORRESP a fórmula não ficaria menor?

 
Postado : 29/06/2011 9:29 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

JPOMANGA, realmente...

era somente um detalhe de formatação (infelizmente.... que a fez ficar maior ainda...)

mas de pronta entrega fica com essas sugestões (vide anexo)... até encurta-la... mas acredito que não seria necessário essas fórmulas...

Qualquer coisa da o grito...

 
Postado : 30/06/2011 12:10 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Bernardo

Show de bola, já funcionou. Só que tem detalhe. A planilha vai crescer (e muito) para baixo. Neste caso deu erro, pq a variável que vc criou para a as datas início e fim de contrato (_IN e _FI) estão com ferencia fixa, e ela tem que ser livre nas linhas, para quando eu arrrastar para baixo (para a linha 12 por ex) ela mudar também junto com os novos valores das células B12, C12, D12 e E12.

_IN .... =DATA(ANO(Plan1!$C$11);MÊS(Plan1!$C$11);1)
_FI.....=DATA(ANO(Plan1!$D$11);MÊS(Plan1!$D$11);1)

Será que dá para ajustar ou uso a fórmula maior da linha 13, que é maior mas está coma referência dinâmica, bastando só destravar a referência fixa das linhas...

O q achas ?

 
Postado : 30/06/2011 1:42 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

JPOMAGA, substitua as fórmulas dinamicas por essas:

_IN: =DATA(ANO(Plan1!$C11);MÊS(Plan1!$C11);1)
_FI: =DATA(ANO(Plan1!$D11);MÊS(Plan1!$D11);1)

Obs. Antes de substituir, deixe qualquer célula da linha 11 selecionada.

Qualquer coisa da o grito...

 
Postado : 30/06/2011 1:50 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Enquanto vc gentilmente me respondia meu reply, teste a versão "maior" que vc fez, destravando as linhas. Acho que ficou blz, dá um olhada..

Vou testar a sua dica alterando as variáveis. Se funcionar é melhor, porque a planilha fica mais leve. Meu medo é que quando eu for transportar para a planilha final onde vou usar, que é bem maior, dê problema com a variável. Acho que ela acompanha a plan, não?

 
Postado : 30/06/2011 2:13 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Bernardo

Acho que deu certo agora, com a versão light usando as variáveis conforme vc orientou. Muito obrigado!

Segue versão final para verificação e uso de quem precisar. Tenho certeza que quem mexe com orçamento de custeio e investimento vai usar e muito!

Abs :D

 
Postado : 30/06/2011 2:26 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico