Guru do Excel e Power BI

Boa noite, colegas.

Estou precisando da ajuda de vcs de novo... Agora com fórmulas.

São fórmulas de 5 colunas (apontadas pelas setas amarelas) mas que caminham no mesmo sentido.

Na Coluna 1 - Saídas Tipo 01 ($)
Preciso que o resultado apresente o valor gasto por cada participante com as Despesas do Tipo 01.
Os valores estão dispostos na coluna [Valor da Parcela] da Tabela "TB_ControleFinanceiro" e as Saídas / Despesas / Aquisições do tipo 01 estão classificadas na Tabela "TB_TipoSaida"
Sei que uma fórmula do tipo SomaSe talvez atendesse a demanda mas queria saber se é possível "automatizar a fórmula" caso fosse acrescentado mais alguma classificação nas Despesas do Tipo 01 da Tabela "TB_TipoSaida".

Na Coluna 2 - Saídas - Tipo 02 ($)
A mesma demanda da coluna anterior.
As Saídas / Despesas / Aquisições do Tipo 02 também estão classificadas na Tabela "TB_TipoSaida".

Na Coluna 3 - Distribuição Proporcional Mensal (%) Despesas Tipo 01
Preciso calcular o fator de ajuste conforme o Capital Investido de cada participante.
O Fator de Ajuste é dado pela relação entre o Capital Investido de cada Participante e o Capital Total.
Os Valores do Capital Aplicado de cada Participante estão relacionados por Meses na Tabela "TB_CapitalSocialAplicado" e a proporção do Capital Mensal de cada participante se encontra na Tabela "TB_PontoEquilibrioMensal".
Considerando como exemplo um grupo de 4 participantes e utilizando o mês de Fevereiro/2020 da Tabela "TB_CapitalSocialAplicado" foram aplicados R$ 100.000,00 distribuídos da seguinte maneira:

Participante 01: R$ 25.000,00
Participante 01: R$ 0,00
Participante 01: R$ 40.000,00
Participante 04: R$ 35.000,00

A Distribuição Proporcional do Capital Aplicado de cada Participante considerando o range "Rng_Mes_Financeiro" = Fevereiro e "Rng_Ano_Financeiro" = 2020 verificado na Tabela "TB_ProporcionalMensal" traria como resultado para essa coluna:
Participante 01: Distribuição Proporcional Mensal = 25,00%
Participante 02: Distribuição Proporcional Mensal = 0,00%
Participante 03: Distribuição Proporcional Mensal = 40,00%
Participante 04: Distribuição Proporcional Mensal = 35,00%

Na Coluna 4 - Ajuste Financeiro Mensal ($) Saídas Tipo 01 e Tipo 02
Supondo que as despesas do Tipo 01 e Tipo 02 para o Mês de Fevereiro de 2020 somaram R$ 12.000,00 distribuídas da seguinte forma:
Despesas Tipo 01: R$ 4.000,00
Despesas Tipo 02: R$ 8.000,00
E foram pagas pelos participantes da seguinte forma:
Participante 01: Despesas Tipo 01: R$ 1.000,00 Despesas Tipo 02: R$ 2.500,00
Participante 02: Despesas Tipo 01: R$ 1.200,00 Despesas Tipo 02: R$ 0,00
Participante 03: Despesas Tipo 01: R$ 0,00 Despesas Tipo 02: R$ 1.500,00
Participante 04: Despesas Tipo 01: R$ 1.800,00 Despesas Tipo 02: R$ 4.000,00
Considerando que as Despesas do Tipo 01 são divididas proporcionalmente ao Fator de Ajuste Mensal (Coluna 3) e considerando que as Despesas do Tipo 02 são divididas igualmente entre o número de participantes, a fórmula deveria ser dada por:
(Valor Pago por cada participante) - (Fator de ajuste mensal do participante) x Soma(Despesas Tipo 01) - Soma(Despesas Tipo 01) / Nº de participantes do grupo.
Tomando como exemplo o Participante 01:
(1.000,00 + 2.500,00) - (25,00% x 4.000,00) - (8.000,00/4)
= 3.500,00 - 1.000,00 - 2.000,00
= 500,00 (positivo)
Participante 02:
(1.200,00 + 0,00) - (0,00% x 4.000,00) - (8.000,00/4)
= 1.200,00 - 0,00 - 2.000,00
= - 800,00 (negativo)
Participante 03:
(0,00 + 1.500,00) - (40,00% x 4.000,00) - (8.000,00/4)
= 1.500,00 - 1.600,00 - 2.000,00
= -2.100,00 (positivo)
Participante 04:
(1.800,00 + 4.000,00) - (35,00% x 4.000,00) - (8.000,00/4)
= 5.800,00 - 1.400,00 - 2.000,00
= 2.400,00 (positivo)

Na Coluna 5 - Ajuste Financeiro Anual ($)
O mesmo Cálculo da coluna anterior porém para o período todo... no caso o ano de 2020 ("Rng_Ano_Financeiro")

Soube como descrever a fórmulas (acredito que esteja certo no meu raciocínio) mas não soube como transcrevê-las para a planilha.
Não soube como trazer os valores do Capital Aplicado (Tabela "TB_CapitalSocialAplicado") ou a Distribuição Proporcional do Capital Aplicado (Tabela "TB_ProporcionalMensal") de cada Participante para a construção das fórmulas.

Os feras aqui em fórmulas e cálculos poderiam me ajudar com isso?
Segue planilha anexa para testes.

Obrigado e boa noite.

Boa tarde, pessoal.

Alguém poderia me ajudar com essas fórmulas?

Bom dia.

Alguém poderia me ajudar com essa demanda?

Boa tarde...

Alguém saberia pelo menos como eu extraíria o valor do Capital Associado (Tabela "TB_CapitalSocial Aplicado") que cada Participante manteve de acordo com o mês e ano selecionados no Range "Rng_Mes_Financeiro" e "Rng_Ano_Financeiro"?

E como eu somaria a contribuição de cada participante conforme o tipo de despesa da tabela "TB_TipoSaida"? Sei que um SomaSes colocando cada tipo de "Saída - Tipo 01" e cada tipo de "Saída - Tipo 02 " resolveria essa parte... mas quero saber se tem como eu "automatizar" a fórmula a medida que for introduzindo tipos de Saída na Tabela "TB_TipoSaida".

Essas duas questões já me dariam uma grande direção na construção das fórmulas que necessito?

Seria um ProcX para isso? Ou alguma outra maneira?


muito obrigado a quem puder ajudar.

Bom dia...

Alguém poderia me ajudar com isso por favor?

Veja se ajuda.

Boa noite, babdallas

Já foram de graaannnde ajuda suas fórmulas.

Fiz umas simulações aqui e vi que alguns ajustes são necessários.

Veja por exemplo quando selecionamos para o Range "Rng_Mes_Financeiro" o mês de Fevereiro.
Fiz uma simulação em que foram gastos R$ 5.000,00 com Despesas do Tipo 01 e R$ 10.000,00 com Despesas do Tipo 02

- A Primeira Coluna [Saídas Tipo 01 ($)] traz os resultados corretos
Fórmula OK

- A Segunda Coluna [Saídas Tipo 02 ($)] traz os resultados corretos
Fórmula OK

- A Terceira Coluna [Crédito / Aporte
] traz os resultados corretos
Houve necessidade de incluir essa coluna e penso que pode ajudar na elaboração das fórmulas das colunas seguintes
Fórmula OK

- A Quarta Coluna [Distribuição Proporcional Mensal (%) Despesas Tipo 01] traz os resultados corretos
Fórmula OK

- A Quinta Coluna não trouxe o resultado esperado
Tomando como exemplo o Participante 1 e o Mês de Fevereiro:
Para as Saídas - Tipo 01 a divisão é feita pela distribuição Proporcional baseada no Capital Associado de cada Participante
Nesse caso o Participante 01 é responsável por 25% das Saídas - Tipo 01 (Divididas pelo fator de Ajuste Mensal dessas Despesas)
Para as Saídas - Tipo 02 a divisão é feita igualmente entre a quantidade de Participantes (nesse caso 4 e portanto 25% para cada um).

Então o resultado esperado seria 25% x 5.000 + 25% x 10.000 = 1.250 + 2.500 = 3.750
O Participante 01 pagou 1.600 em despesas e fez um aporte de 1.000, portanto o resultado dele para essa coluna seria:
2.600 - 3.750 = -1.150

No caso do Participante 2:
0% x 5.000 + 25% x 10.000 = 2.500
O Participante 02 pagou 8.700 em despesas e portanto o resultado dele para essa coluna seria:
8.700 - 2.500 = 6.200

No caso do Participante 3:
30% x 5.000 + 25% x 10.000 = 1.500 + 2500 = 4.000
O Participante 03 pagou 2.500 em despesas , portanto o resultado dele para essa coluna seria:
2.500- 4.000 = -1.500

No caso do Participante 4:
45 % x 5.000 + 25% x 10.000 = 2.250 + 2.500 = 4.750
O Participante 04 pagou 2.200 em despesas e fez um Crédito / Aporte de 3.000 e portanto o resultado dele para essa coluna seria:
2.200 + 3.000 - 4.750 = 450

- A Coluna 5 deve fazer o mesmo cálculo da Coluna 4, porém considerando o ano todo (Range "Rng_Ano_Financeiro")
No caso do Partipante 01:
Despesas - Tipo 01: 10% x 1.540 (Mês Janeiro) + 25% x 1.600 (Mês Fevereiro) + 12,5% x 700 (Mês Março) = 154 + 400 + 87,50 = 641,50
Despesas - Tipo 02: 25% x 5.860 (Mês Janeiro) + 25% x 10.000 (Mês Fevereiro) + 25% x 8.100(Mês Março) = 1.465 + 2.500 + 2025 = 5.990
Total de Despesas devidas pelo Participante 01 (Tipo 01 e Tipo 02) = 6.631,50
Ele pagou nesse período 300 (Tipo 01 em Janeiro) + 600 (Tipo 02 em Janeiro) + 1.600 (Tipo 01 em Fevereiro) + 0 (Tipo 02 em Fevereiro) + 500(Tipo 01 em Março) + 700 (Tipo 02 em Março) = 900 + 1.600 + 1.200 = 3.700
Total de Crédito / Aporte realizado pelo Participante 01 em 2020 = 1.000 (em Fevereiro) + 1.000 (em Março) = 2.000
Portanto, o resultado esperado para essa coluna seria:
3.700 (despesas pagas) + 2.000 (créditos / aportes) - 641,50 (Despesas Tipo 01 Proporcionais ao Capital Associado do Participante) - 5.990 ((Despesas Tipo 02 divididas igualmente entre a quantidade de Participantes) = -931,50

Da mesma forma para os demais participantes.

Simplifiquei a planilha eliminando algumas colunas e algumas linhas com valores para ficar mais fácil a realização dos testes.
Mantive as setas amarelas apenas nas colunas que precisam ter as fórmulas revisadas
Segue planilha anexa.

E desde já muito obrigado a todos que puderem ajudar.

Veja agora se melhorou.

Boa noite, babdallas.

Muito obrigado pela grande ajuda.
Era quase isso... mas baseado na sua fórmula e fazendo mais alguns testes consegui enxergar o que faltava e cheguei ao resultado desejado para a Coluna 5.
Repare que a Soma dos valores da coluna 5 é igual a 0 (zero).
O que significa que se alguém no grupo tem um crédito (um excedente) de 100 reais alguém do grupo está devendo 100 reais para ele.
Vou remover a seta amarela apontando para a Coluna 5 pois chegamos ao que necessitava para essa coluna.

Envio novamente a planilha com a fórmula da Coluna 5 alterada.

Falta agora apenas buscar um resultado semelhante para a COLUNA 6.
Essa eu não consegui pois não entendo de fórmulas matriciais ainda.
Mas o resultado desejado para essa coluna tem o mesmo princípio.
Ela tem que buscar o resultado da soma dos valores da coluna = 0 (zero) para o Range. Se alguém tem crédito de X, significa que outro alguém está devedor -X.

Mais uma vez obrigado.

Veja agora, por favor. A fórmula é matricial, portanto precisa de CSE (Ctrl+Shift+Enter).

Funcionou, babdallas.

Muito grato pela sua ajuda.