Ir ao conteúdo
  • Cadastre-se
Entre para seguir isso  
Dotto

[Resolvido] Excel - Função SE utilizando datas

Recommended Posts

Boa noite a todos!

Gostaria de pedir a ajuda dos usuários do fórum para resolver meu problema:

Preciso criar uma tabela no excel para cálculo de IR do período de 1994 até hoje. Para tanto, preciso que o usuário alimente a planilha com o mês, o ano e o valor do pagamento para que automaticamente se aplique os valores corretos de alíquota e dedução.

Fiz algumas pesquisas e entendi como se cria uma função SE, como se criar funções dentro de funções, etc, mas, para minha necessidade específica, a fórmula ficaria gigantesca, pois teria que criar condições pra testar:

1) os 12 meses

2) os quase 20 anos

3) o valor.

Pra piorar a situação, em cada período as faixas de salário são diferentes.

Há uma maneira mais fácil de atingir meu objetivo?

Agradeço desde já!

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom dia Dotto

Há uma maneira mais fácil de atingir meu objetivo?

Sim, em vez de usar a função SE, use a função PROCV.

Clique no link abaixo para baixar uma exemplo de cálculo de Imposto de Renda e INSS que fiz para um usuário de outro fórum.

Avantagem do uso da função PROCV é que quando ocorrer alteração de valores, não precisa alterar a fórmula, apenas atualizar a tabela de valores.

http://www.sendspace.com/file/ae34i4

Se quiser um ajuda mais especifica, poste um exemplo da tua planilha num site gratuito como o sendspace e cole o link aqui o fórum.

Dê retorno.

Att.

  • Curtir 1

Compartilhar este post


Link para o post
Compartilhar em outros sites

Muito obrigado pelas respostas!

Estou sem o arquivo aqui comigo, posso enviá-lo apenas terça-feira.

Patropi,

de toda forma, adiantando, minha planilha é semelhante a essa que você enviou. Já criei a fórmula para cálculo do IR que, assim como a sua, leva em consideração o desconto de previdência, dependentes, etc. Ela também considera que nem todo o valor recebido é tributável (vale refeição) nem aplicável o desconto de previdência (algumas gratificações específicas)

No entanto, para minha necessidade, a planilha precisará de várias "TABELAS DO I. RENDA", uma para cada período nesses últimos 20 anos e isso que está gerando toda a dificuldade. O usuário terá que colocar o mês e ano do pagamento, pois, em cada período, os valores de alíquota e dedução são diferentes.

Há ainda dois agravantes:

1) As faixas de salário em cada "TABELA DO I. RENDA" são diferentes.

EX: Se em sua tabela, o valor de transição de isento para a alíquota mínima é R$ 1710,79, em outros períodos esse valor é diferente.

2) As "TABELAS DO I. RENDA" não se inserem em uma janela de tempo regular.

EX: Uma tabela pode valer, por exemplo, de abril/1994 até dezembro/1994, a seguinte de janeiro/1995 até outubro/1995, a posterior de novembro/1995 até setembro/1995 e assim por diante (não que esses intervalos estejam corretos, mas a ideia é essa)

Compartilhar este post


Link para o post
Compartilhar em outros sites

Dotto

Pela sua explanação, eu acho mais fácil então, fazer uma Aba da planilha para cada Tabela (ano ou período, conforme a vigência da tabela), relacionando todos os funcionários do período.

Se for necessário você poderá fazer numa outra Aba o total a recolher por funcionário.

Att.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Patropi,

antes de mais nada, obrigado pela paciência em me ajudar a resolver o problema.

Na correria do dia a dia, acabei esquecendo de pegar a planilha para postar aqui.

Dando prosseguimento à discussão, fiz como você sugeriu, uma aba para cada período. No entanto, havia esquecido de considerar que a cada ano também são alteradas as faixas de salário e porcentagens de contribuição ao INSS, o que acaba trazendo o problema que evitamos de volta. É inviável fazer uma aba para cada caso, pois serão 10 anos de regras de IR multiplicados por mais 10 anos de regras previdenciárias, ou seja, 100 abas.

Para não ficar muito teórico, deixe-me explicar para que exatamente servirá a planilha: Ela será usada para calcular o pagamento proporcional de funcionários, devido a desligamento dos mesmos, de acordo com os dias trabalhados no mês.

Assim sendo, o ideal seria algo simples, com apenas uma aba contendo a planilha. O usuário a alimentará com valores como:

1) Salário Bruto

2) Gratificações não tributáveis

3) Gratificações que não contam para efeitos previdenciários

4) Dias trabalhados

5) Número de dependentes

E obterá respostas como:

1) Salário Bruto proporcional

2) Desconto previdenciário

3) Desconto IR

4) Salário líquido proporcional

Estou pesquisando como resolver a situação e acredito já ter entendido como devo criar a fórmula. Vi como testar se células estão vazias, como testar intervalos de datas, etc. Mas com tantas variáveis para se testar, ficará extremamente extensa e confusa a fórmula. Exemplificando, seria assim:

SE(teste de data;SE(teste de faixa salarial;x;SE(teste de faixa salarial;y;SE(teste de faixa salarial;z;SE(teste de faixa salarial;a;B))));SE(teste de data;SE(teste de faixa salarial;x;SE(teste de faixa salarial;y;SE(teste de faixa salarial;z;SE(teste de faixa salarial;a;B))))))

Partindo do princípio que não tenha cometido nenhum erro na fórmula acima, ainda assim fica muito confuso, difícil de atualizar (já que acada ano as regras de IR e previdência mudam) e, o pior, a fórmula acima só leva em consideração 2 anos. A fórmula final será, pelo menos, 5 vezes maior. É provável que o excel sequer aceite algo dessa extensão.

Não sei se dá pra comparar, mas já brinquei um pouco com programação básica em C++ e Pyton e acredito que esteja fazendo algo como condições "if/else", quando o ideal acho que seria algo como "switch case".

Alguma sugestão? :)

Desculpe pelo post extenso, mas queria colocar toda a problemática em perspectiva.

PS: Esqueci de mencionar, o exemplo de planilha que você disponibilizou não me serviria, pois a sua trata apenas do ano vigente e, nesse caso, pode-se atualizar a tabela de descontos de IR e Previdência sempre que necessário, a cada ano. A minha será utilizada para colocar em dia 10 anos de pagamentos pendentes.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom dia Dotto

Como a planilha é grande, realmente o melhor caminho é o VBA.

Como eu ainda sou iniciante em VBA, deixo para os colegas que são feras em VBA te ajudarem.

Minha praia são as fórmulas.

Att.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Obrigado de qualquer forma!

Fico aqui no aguardo por alguma sugestão. :)

Compartilhar este post


Link para o post
Compartilhar em outros sites

Patropi,

desculpe voltar a consultar você, mas estava olhando a planilha que você disponibilizou aqui e percebi que você utilizou uma fórmula mais enxuta que a minha, o que já me ajudaria bastante, se conseguir entendê-la.

Fórmula para cálculo de IR (célula E3):

=IF((C3-D3-B3*$K$8)<=0;0;(C3-D3-B3*$K$8)*VLOOKUP((C3-D3-B3*$K$8);$H$3:$K$7;3)-VLOOKUP((C3-D3-B3*$K$8);$H$3:$K$7;4))

Tenho duas perguntas:

1) Por que você usa o símbolo "$" para designar células?

Ex: Logo no início "$K$8" ao invés de simplesmente "K8"?

2) Nessa função "VLOOKUP" vi que você está calculando um valor "C3-D3-B3*$K$8", depois determina as coordenadas de início e fim da tabela de I. RENDA e depois dá um número. Me confirme se é isso que você está fazendo:

"Você está calculando o valor "C3-D3-B3*$K$8", procurando na tabela designada onde verticalmente se encaixa esse valor obtido para ter de retorno o que consta na mesma linha na terceira ou quarta colunas (aliquota e dedução)?

Se for isso mesmo, eu não poderia criar um "IF" no local onde se designa a tabela na função "VLOOKUP", de acordo com o mês e ano que o usuário alimentar na planilha, para procurar a tabela do ano vigente que interessa? Ou, melhor ainda, criar uma tabela de datas para utilizar com outra função VLOOKUP, que me retorne as coordenadas das tabelas de IR?

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom dia Dotto

1) Por que você usa o símbolo "$" para designar células?

Ex: Logo no início "$K$8" ao invés de simplesmente "K8"?

O $ serve para bloquear a célula, assim quando arrastamos a fórmula para outras linhas a célula que contém cifrão permanece fixa, ou seja, não atualiza.

Se eu colocar o cifrão só antes do K, ele vai fixar só a coluna, se coloco só antes do número, vai fixar apenas a linha, e se colocar nos dois como eu fiz vai fixar a coluna e a linha.

2) Nessa função "VLOOKUP" vi que você está calculando um valor "C3-D3-B3*$K$8", depois determina as coordenadas de início e fim da tabela de I. RENDA e depois dá um número. Me confirme se é isso que você está fazendo:

"Você está calculando o valor "C3-D3-B3*$K$8", procurando na tabela designada onde verticalmente se encaixa esse valor obtido para ter de retorno o que consta na mesma linha na terceira ou quarta colunas (aliquota e dedução)?

A fórmula faz o seguinte --> Valor do salário(C3) menos o valor descontado de INSS(D3) vezes o valor por dependente (K8), assim obtemos o valor do salário tributável e depois sobre esse valor é que vai ser calculado o IRRF e a parcela a deduzir.

Sem ver um exemplo da tua planilha fica difícil sugerir alguma coisa.

Att.

Compartilhar este post


Link para o post
Compartilhar em outros sites
A fórmula faz o seguinte --> Valor do salário(C3) menos o valor descontado de INSS(D3) vezes o valor por dependente (K8), assim obtemos o valor do salário tributável e depois sobre esse valor é que vai ser calculado o IRRF e a parcela a deduzir.

Isso eu tinha entendido. O que queria enteder era como funciona a função VLOOKUP.

De qualquer forma, acho que já entendi o que ela faz.

O que estava querendo saber é se essa função VLOOKUP pode ser usada para retornar uma coordenada de outra célula. Se puder, posso utilizá-la para testar a data e retornar para mim as coordenadas da tabela de IR que interessa ao período.

Algo assim:

abril-11 dezembro-11 $A$21:$C$25

janeiro-12 dezembro-12 $A$13:$C$17

janeiro-13 junho-13 $A$5:$C$9

Estou conseguindo obter as coordenadas, mas quando jogo o resultado em outra fórmula VLOOKUP, obtenho um erro "#VALOR!". Estou tentando inserir como texto, mas não dá certo.

Estou no trabalho e aqui é bloqueado o acesso a sites de hospedagem, mas já enviei a planilha pro meu e-mail e, quando chegar em casa, envio a planilha pra você.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Só dando um feedback, consegui resolver o problema criando uma tabela para calcular cada o IR e INSS em cada período e uma célula com uma fórmula para consultar essa tabela.

Agora vou abrir outro tópico pra tratar sobre geração de relátórios.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Caso o autor do tópico necessite, o mesmo será reaberto, para isso deverá entrar em contato com a moderação solicitando o desbloqueio.

Compartilhar este post


Link para o post
Compartilhar em outros sites
Visitante
Este tópico está impedido de receber novos posts.
Entre para seguir isso  





Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas publicações sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

×