Agora que temos os dados no lugar organizados, veremos como calcular as deduções automaticamente. A tabela de Deduções (Tabela 2) possui o seguinte formato: na primeira coluna colocamos as classes de deduções (Saúde, Instrução, PGBL, INSS), na segunda são os valores calculados a partir dos Totais e Classes da Movimentação Mensal, na terceira colocaremos os valores máximos para cada dedução. O formato final é assim:

Tabela 2 - Deduções: Os nomes das classes utilizados nesta tabela devem ser os mesmos utilizados na tabela de Movimentação Mensal pois são esses nomes que servirão para conectar uma coisa com a outra.

Agora criaremos a fórmula para a coluna Executado da Tabela 2. Nós precisamos que essa célula contenha a soma dos valores da Tabela 1 onde a classe seja igual à da coluna Classes na Tabela 2 e ao mesmo tempo não ultrapasse o valor máximo na coluna Disponível.

Para a primeira parte do problema utilizaremos a função somase() que faz soma segundo uma condição. Uma versão mais radical dela, a somases(), o faz com múltiplas condições. A fórmula básica seria:

Fórmula 2 - soma condicional

SOMASE(ir_classes; A20; ir_movimentação_total)

Nesta fórmula ir_classes indica onde a condição deve ser localizada, A20 indica o valor da condição que, neste caso, coincide com a coluna Classe na Tabela 2 e ir_movimentação_total o intervalo de onde os valores são somados. Essa fórmula funciona muito bem graças aos intervalos nomeados pois conforme você adiciona mais dados na Tabela 1 eles são processados diretamente na Tabela 2 sem problemas.

Mas esta fórmula possui um problema, ela só funciona corretamente para Saúde e INSS, onde a dedução é de 100%. Para Instrução a dedução máxima é um valor fixo que é dado no ano e para o PGBL é 15% da renda total apenas. A solução para isso é adicionar um se() para verificar se o valor está ultrapassando o limite. A versão modificada ficará assim:

Fórmula 3 - soma condicional com teto

=SE(
    SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
    C20;
    SOMASE(ir_classes; A20; ir_movimentação_total)
    )

Ela primeiro verifica se o valor ultrapassa o teto (coluna "Disponível" da Tabela 2), se sim, ela força o valor teto, se não, ela usa o total. Simples assim.

A coluna "Disponível" também usa somase(), mas com algumas exceções. As classes Saúde e INSS usam a formula normal do somase() que exemplifiquei anteriormente, a classe Instrução usa um valor fixo que devem ser inserido manualmente e a do PGBL usa a somase(), mas toma como referência o somatório da renda - que por acaso é uma classe também, mas não de dedução - e dela extrai os 12%. A formula fica assim:

Fórmula 4 - teto do PGBL

=-SOMASE(ir_classes; "renda"; ir_movimentação_total) * 0,12

Notem o sinal de menos no início da fórmula. Acontece aqui o seguinte, ela está sendo calculada a partir de uma classe que tem valor positivo (a "Renda") e por isso o sinal dela tem de ser invertido para ficar compatível com as demais.

Agora basta adicionarmos o somatório de cada coluna (Executado e Disponível) no fim utilizando a função soma(). Precisaremos destes totais mais tarde.