Excel - Fórmula utilizando a função PROCV com referência relativa

Veremos neste artigo como efetuar, facilmente, o cálculo do imposto de renda utilizando a função PROCV para pesquisar as faixas e deduções.

Em nosso último artigo Microsoft Excel - Fórmula utilizando a função PROCV com referência absoluta, estudamos a função PROCV com referências absolutas, quando a função apenas retornará um valor se o elemento procurado for encontrado com exatidão.

Exploraremos agora a forma RELATIVA, onde a função PROCV  passa a trabalhar por aproximação, partindo do pressuposto de que a lista pesquisada está devidamente ordenada.

Criaremos para este artigo um cenário muito próximo ao do artigo anterior. Se você desejar, faça o download do arquivo modelo do artigo anterior e simplesmente altere conforme orientaremos à seguir.

Iremos aprimorar o exercício colocando o cálculo do desconto de imposto de renda do funcionário, consultando a tabela de imposto de renda da receita federal, com base no salário.

A tabela de imposto de renda vigente é a que está abaixo. Devemos criá-la na planilha TABELAS:

Temos abaixo a imagem da planilha trabalhada no artigo anterior.

Devemos alterá-la, ou criar uma nova, para que tenha as três informações destacadas na imagem abaixo, contendo a alíquota de imposto, a parcela a deduzir e o cálculo efetivo do importo de renda.

Precisaremos então:

1. Descobrir, com base no salário, qual a alíquota de imposto de renda
2. Descobrir, com base no salário, qual a parcela de dedução do imposto
3. Calcular, o imposto de renda retido na fonte
4. Fazer com que o imposto retido na fonte seja descontado no LÍQUIDO A RECEBER

Teoria do PROCV com referência RELATIVA

A função PROCV, com referência relativa tem o seguinte funcionamento: Considerando a matriz onde serão pesquisadas as informações, a pesquisa ocorre apenas na PRIMEIRA COLUNA, e os valores das demais colunas podem ser utilizados como resposta. 

Exemplifiquemos: Estamos tentando encontrar a alíquota do imposto de renda para um salário hipotético de R$ 3.000,00. Para isto, se estivéssemos consultando a tabela abaixo manualmente, poderíamos facilmente encontrar a alíquota de 15%, isso porque o salário que temos como referência está acima de R$ 2.679,29 (piso da faixa de 15%) e abaixo de R$ 3.572,43 (piso da faixa imediatamente superior).

Quando aplicamos o raciocínio acima na função PROCV, ela terá exatamente esse comportamento. A função ficará assim planejada:


=PROCV    (ELEMENTO_PROCURADO; 
                     MATRIZ_DE_PESQUISA;
                     COLUNA;
                     TIPO_DE_PESQUISA)
      
No nosso exemplo, a função será construída conforme abaixo:

• ELEMENTO_PROCURADO será o SALÁRIO do funcionário, no caso, a célula I5.
• MATRIZ_DE_PESQUISA será a tabela de IMPOSTO DE RENDA presente na planilha TABELAS
• Estamos buscando o a alíquota. Alíquota é a segunda COLUNA dentro da tabela de IMPOSTO DE RENDA, posição 2 nas colunas.
• TIPO_DE_PESQUISA será informado dizendo que a lista está devidamente ordenada. Se informarmos FALSO neste argumento o Excel irá buscar com referência absoluta, ou seja, "somente retornará algum valor se o cargo for encontrado com exatidão, rigorosamente na mesma grafia". Se informarmos VERDADEIRO, ou se simplesmente omitirmos o argumento, a função PROCV procurará o valor conforme o raciocínio trabalhado no exemplo.

Assim, coloquemos para a célula I6 (alíquota), a seguinte fórmula: "=PROCV(I5;TABELAS!E3:G7;2)". Note que temos apenas três argumentos: ELEMENTO_PROCURADO, MATRIZ_DE_PESQUISA e COLUNA. O quarto foi omitido, definindo o comportamento relativo. Dessa forma buscamos o salário na primeira coluna da tabela e, após identificada a faixa, estamos buscando a coluna de número 2 da matriz.

A célula de DEDUÇÃO também receberá uma formula semelhante, conforme vemos na ilustração abaixo, alterando-se apenas o número da coluna, passando de 2 para 3, buscando a terceira coluna, parcela de dedução. A fórmula de I7 então fica assim: "=PROCV(I5;TABELAS!E3:G7;3)"

O imposto de renda, então, já pode ser calculado aplicando-se a alíquota sobre o valor do salário e abatendo a parcela de dedução: A fórmula de I8 fica assim: "=I5*I6-I7"

Para fazer o download do modelo concluído, CLIQUE AQUI

.

Estamos aqui, à disposição, aceitando sugestões para novos artigos.

Aproveitem e conheçam o site da nossa empresa: www.excelline.net.

Contem conosco!

Julho/2014

GILBERTO GOMES
ExcelLine - Office World