Uso das funções PROCV e SE para localizar valores em múltiplas tabelas em planilha do Excel

A planilha abaixo é composta de três tabelas. No intervalo A1:E10 temos a tabela de comissões com os nomes dos representantes, tempo de casa, valor de vendas, taxa de comissão calculada com base na quantidade de anos de trabalho e no total de vendas. A coluna E mostra o valor a ser pago de comissão.

planilha-calculo-comissoes-procv-e-se.png

O intervalo G1:H10, contém a relação entre valor vendido e a taxa de comissão a ser utilizada no cálculo para os representantes com menos de três anos de casa. No intervalo J1:K10, são apresentadas as colunas com a relação de vendas e comissões para quem tem mais de três anos de casa.

Para simplificar a fórmula, você pode nomear as tabelas de taxa de comissão por tempo de trabalho (G1:H10 e J1:K10) para Tabela1 e Tabela2 respectivamente. Faça o seguinte, selecione o intervalo da primeira tabela G1:H10 e entre com o nome Tabela1 na Caixa de Nome na Barra de Fórmulas.

caixa-de-nomes-excel.png

E faça o mesmo para o intervalo J1:K10, nomeando-o Tabela2.

O próximo passo é criar a fórmula que obtém a taxa de comissão de uma das tabelas de acordo com o tempo de casa relacionado na coluna B de nossa planilha. Veja a fórmula a seguir:

=PROCV(C2;SE(B2<3;Tabela1;Tabela2);2)

A fórmula acima, será inserida inicialmente na célula D2 e depois copiada para as demais células do intervalo até a célula D10. Primeiro, utilizamos a função SE para verificar se o valor da célula B2, que contém o tempo de trabalho do representante Benson, é menor que 3. O Excel irá retornar o intervalo da Tabela1 se a verificação for verdadeira, ou seja, se o Benson realmente tiver menos de 3 anos de casa, e retornará o intervalo da Tabela2 caso contrário.

O resultado da verificação descrita acima, será passado para a função PROCV, como intervalo a ser pesquisado. A função PROCV recebe três parâmetros: a célula C2 que define o valor a ser pesquisado, o intervalo de dados a ser pesquisado (Tabela1 ou Tabela2), e a coluna do referido intervalo que será retornada como resultado (2).

Depois de concluir a fórmula e pressionar Enter, verifique se o valor apresentado é 7,00%, e se estiver correto, copie a fórmula para as demais células, até a linha 10.

Vale observar que ao criar os nomes de intervalos Tabela1 e Tabela2 o Excel irá trabalhar com a referência absoluta para estes intervalos. Caso utilize as referências (G1:H10 e J1:K10) ao invés de nomes para os intervalos, você terá que converter as referências das tabelas de comissão para absolutas ( $G$1:$H$10 e $J$1:$K$10 ).





Eryx Guimarães

Eryx Guimarães desenvolve e mantém soluções para empreendimentos na web.

Entre em Contato

Contato