Este fórum está sendo desativado

[jpomaga]

Pessoal, preciso de um help urgente. A planilha anexa é uma planilha de serviços, cujo total de cada tipo de serviço (serviço 1, serviço 2, etc) precisa ser alocado ao longo dos meses do ano na planilha do lado direito, que se inicia na célula K10, de acordo com a sua data de início(campo "A PARTIR DO MÊS") e com sua frequência ou periodicidade ( campo "FREQUÊNCIA"), que pode ser MENSAL, BIMESTRAL, SEMESTRAL, ANUAL, PONTUAL (ocorre uma única vez), etc).

Tentei utilizando as fórmulas SOMARPRODUTO e SOMASES, cheguei até numa solução parcial (ver aba TESTE FORMULA), mas a fórmula ficou muito grande.
Na aba TESTE_FÓRMULA (2), tente consolidar numa linha a fórmula, que ficou maior ainda.
Queria uma solução mais "light", e que fizesse a distribuição dos valores de forma correta. Ou seja:

Preciso que a fórmula que se inicia na célula K10 "distribua" o valor da coluna F ("Preço Total"), no mês de início conforme informado na coluna H ""A partir do mês"), e repita este valor conforme a FREQUENCIA informada na coluna G.

Detalhe importante: A fórmula deve também checar o mês vigente (range K8:AI8) e , se for mês do reajuste, procurar o valor do reajuste na tabela "tab_reajuste" na aba Listas e multiplicar o índice pelo valor atual vigente. (Esta parte já está OK na fórmula, é aprte do procv).

Segue arquivo. Espero uma ajuda !

[DJunqueira]

Fórmula enxugada e funcionando.

[jpomaga]

DJ

Você é o cara mesmo. Ficou muito boa a fórmula enxuta. Ela funciona perfeitamente quando optamos por consolidar por frequência, até usei esta opção hoje mais cedo com fórmula gigante anterior..
No entanto, para viabilizar minha análise, precisava que a fórmula alocasse somente o valor de cada linha nos meses correspondentes à frequência e ao mês de início. Na aba TESTE FORMULA (2), cada linha da tabela com os meses deverá ser independente, não devendo ser uma consolidação das ocorrências da mesma frequência.
OU seja, a fórmula deve alocar somente o valor da linha no(s) mês(s) correspondentes, de acordo com a frequência, além de checar o mês de início da ocorrência do valor e o mês do reajuste. Ver exemplo com valores na aba TESTE FORMULA (2),na parte superior
Não sei se fui claro. Segue novamente a planilha com destaque e comentário na aba TESTE FORMULA (2).

[DJunqueira]

Ok, simplificou mais ainda.

[jpomaga]

DJunqueira

Você me surpreende cada vez mais, pela simplicidade e facilidade com que responde aos mais variados desafios aqui do fórum. Parabéns mais uma vez. Ficou ótima a solução!

Acredito que esta solução vai ajudar muita gente que trabalha com medição de contratos e deseja simular o fluxo ao longo dos meses. Parabéns mais uma vez!

Queria aproveitar e te convidar para participar do grupo do whatsapp do Guru do Excel. Já falei com a turma e eles , inclusive o Laennder adm do grupo, esperam ansiosos pela sua entrada! (rs). Caso esteja a fim, me contate pelo e-mail: jpomaga@outlook.com que lhe passo o link para acesso ao grupo.

Abs JP.

[jpomaga]

Só pra fechar com chave de ouro, segue a fórmula, agora com a condição de teste para a frequência SEMANAL. Testei e deu certo. Também consertei a referência fixa da célula G6 estava $G$6 e na verdade é para travar só a coluna, $G6.

(fórmula da célula J10, primeira célula superior esquerda da tabela de medição. É só arrastar pra direita e para baixo)
=SE($G6="PONTUAL";tab_QQP2[@[Pr Total]:[Pr Total]]*(tab_QQP2[@[A PARTIR DO MÊS]:[A PARTIR DO MÊS]]=J$5)*(1+PROCV(J$5;tab_reajuste;3;1));SOMARPRODUTO(--(MOD(MÊS(tab_QQP2[@[A PARTIR DO MÊS]:[A PARTIR DO MÊS]]);ÍNDICE(lista_FREQUENCIA;CORRESP($G6;Listas!$A$5:$A$13;0);2))=MOD(MÊS(J$5);ÍNDICE(lista_FREQUENCIA;CORRESP($G6;Listas!$A$5:$A$13;0);2))))*SE($G6="SEMANAL";tab_QQP2[@[Pr Total]:[Pr Total]]*(1+PROCV(J$5;tab_reajuste;3;1))*4;tab_QQP2[@[Pr Total]:[Pr Total]]*(1+PROCV(J$5;tab_reajuste;3;1))))

Segue abaixo a plan novamente com a fórmula acima já testada.

[jpomaga]

Segue versão final, com slicers para filtro da tabela e com gráfico indicando o perfil de medição. Agradeço de novo DJunqueira. Valeu!

[DJunqueira]

Muito bom!

[jpomaga]

VAleu. Utilizando vi uma oportunidade de melhorar um pouco mais. Faltava um detalhe na fórmula: ela tinha que testar o mês vigente x o mês à parti do qual deveria começar a medição. Inclui mais este "SE" na fórmula, que ficou assim (referente à célula K17 da planilha que segue agora em anexo:

=SEERRO(SE(tab_QQP2[@[A PARTIR DO MÊS]:[A PARTIR DO MÊS]]<=K$16;SE($I17="PONTUAL";tab_QQP2[@[Pr Total]:[Pr Total]]*(tab_QQP2[@[A PARTIR DO MÊS]:[A PARTIR DO MÊS]]=K$16)*(1+PROCV(K$16;tab_reajuste;3;1));SOMARPRODUTO(--(MOD(MÊS(tab_QQP2[@[A PARTIR DO MÊS]:[A PARTIR DO MÊS]]);ÍNDICE(lista_FREQUENCIA;CORRESP($I17;Listas!$A$5:$A$13;0);2))=MOD(MÊS(K$16);ÍNDICE(lista_FREQUENCIA;CORRESP($I17;Listas!$A$5:$A$13;0);2)))*SE($I17="SEMANAL";tab_QQP2[@[Pr Total]:[Pr Total]]*(1+PROCV(K$16;tab_reajuste;3;1))*4;tab_QQP2[@[Pr Total]:[Pr Total]]*(1+PROCV(K$16;tab_reajuste;3;1)))));0);0)

Se você achar uma forma de reduzir ainda mais a fórmula, seria ótimo. Ainda acho que dá pra fazer uma jogada com a questão das frequencias.... bom, o ótimo é inimigo do bom, não é mesmo? Segue anexa a última versão.