Guru do Excel e Power BI

Preciso da ajuda dos experts. Recebi esta planilha para efetuar um orçamento, mas ela contém alguns problemas. Ela alimenta outras planilhas, exportando as linhas totalizadoras de cada tabela uma outra planilha consolidadora, para montagem de um orçamento plurianual, de abril/16 em diante. A data inicial é definida na célula Q8. No caso usei abril pq é o início do ano financeiro na minha empresa.

A sequência de preenchimento é a seguinte:

1- Na tabela da esquerda (colunas de B até I) escolhemos a NATUREZA da despesa, o valor (coluna E) , a frequência com que ocorre a despesa (MENSAL, BIMESTRAL, ANUAL, etc) e o mês à partir do qual ela se inicia.

2- As escolhas se refletem automaticamente nas tabelas da direita, separadas por NATUREZA para facilitar a análise e posterior importação para uma outra planilha. Foi utilizada a fórmula SOMASES com os critérios de acordo com os dados inputados na tabela da esquerda

3- Cada linha distribui a despesa de acordo com o período informado.Aí já começa o problema, pois cada linha tem uma lógica diferente, em função da frequencia da despesa

3- Os índices anuais de reajuste na parte superior da tabela são pré definidos pela empresa e deverão servir de base para os reajsutes devidos nas datas informadas nas tabelas superiores. Para simplificar, estamos apenas usando o IPC-Fipe e a data base de repactuação do contrato..

4- A tabela deveria somar mês a mês as despesas, porém reajustando-as de acordo com o IPC-Fipe no mês seguinte ao mês de repactuação do contrato.

PROBLEMAS:

1- Como vocês podem verificar, da forma como foi montada a tabela e a linha que soma as despesas do mês, a planilha NÃO está reajustando corretamente os valores. Ela deveria reajustar no corpo da tabela primeiro, mantendo a lógica dos períodos quando as despsas ocorressem, e na linha cinza no final de cada tabela, deveríamos ter somente a soma dos valores já atualizados. Porém ela está reajustando na linha de totais, o que causa errro, pois os valores somados não vem atualizados. VEja e teste alguns valores...

2- Tentei de várias maneiras fazer este reajuste no corpo da tabela, mas não tive sucesso.
Quando os períodos são mensais, até que funciona com um SE e um DESLOC para mudar o índice ano a ano.
Porém, quando as despesas são intermitentes, esta fórmula não funciona. HELP!!!

Segue anexa a planilha. Por favor, HELP!

Esqueci de colar o link do tópico. Segue

viewtopic.php?f=9&t=3272

abs e aguardo ajuda!

Avancei um pouco na sua planilha na questão do indexador, mas travei na periodicidade.
Quem sabe alguém mais tem alguma sugestão.

DJunqueira, achei excelente a solução para a linha mensal. A idéia de criar a tabela TsubCont e a idéia de criar a tabela dos índices de reajuste em sequencia para o PROCV localizar eles foi top!

A fórmula funcionou perfeitamente para o período MENSAL, mas realmente a dificuldade está em alternar a soma de acordo com a periodicidade. Segue abaixo uma idéia que tive:

Talvez usar uma ou mais funções SEs aninhadas por fora da fórmula, ou dentro da fórmula quando chegar no critério FREQUENCIA, e aí comparando a coluna G ( FREQUENCIA escolhida para a despesa na tabela TSubCont) com a coluna P, e , se forem iguais, usar o DESLOC para deslocar a referencia do mês (linha Q) em X períodos conforme a coluna P.

De certa forma seria a mesma lógica que usaram na planilha original, mas adaptando para sua fórmula...

Bom, acho quem tese funciona, mas estou tentando há horas mas não saiu..

Vamos ver se alguem mais ajuda. Por ora valeu!

Ok, solução final encontrada.

DJunqueira, chegou quase lá. Cara você é fera mesmo. Eu não conhecia as fórmulas MOD e tb não iria ter a idéia do ÉPAR na linha BIMESTRAL, ficou show. A solução tá quase perfeita, com exceção de um detalhe. O reajuste do valor deve ocorrer no mês seguinte ao mês da data base e não no próprio mês.

No caso da frequência MENSAL, a fórmula está fazendo corretamente em qualquer situação. Já para os outros casos não, ele está reajustando no próprio mês e não no seguinte. Tentei alterar os parâmetros da sua fórmula mas não tive sucesso. Ia fazer um "SE" antes mas a fórmula iria duplicar de tamanho. Enfim, a lógica do reajuste deve ser a mesma do MENSAL.

Se você conseguir, aí fechamos com chave de ouro. Por ora muito obrigado!

Cara, desculpe a minha dúvida anterior. Não tinha visto o seu "truque" das datas de reajuste começarem no dia 5 e não do dia 1 do mês. Aí entendi que o PROCV procura o valor aproximado, devido ao parâmetro 1 no final (PROCV(Q$8;$L$8:$N$14;3;1) e não acha na tabela de reajustes, não reajustando no mês da data base e sim no seguinte. Cara acho que você matou a pau!

Legal jpomaga, nem sempre as pessoas entendem a apresentação de uma solução mais sofisticada tão bem quanto vc, realmente foi um prazer te ajudar.