Bottom line: Aprenda como calcular comissões no Excel para um plano básico em camadas e tabela de taxas usando as funções IF, VLOOKUP ou XLOOKUP.

nível de Habilidade: Intermediário

Vídeo Tutorial

Cálculo de comissões no Excel pode ser uma tarefa complicada. Isso é especialmente verdadeiro se você tentou usar várias instruções IF para calcular comissões para cada nível em uma tabela de taxas.

este artigo explicará como usar a função VLOOKUP para tornar esse processo muito mais fácil. O segredo é definir o último argumento no vlookup para TRUE, para encontrar a correspondência mais próxima.

confira meu artigo sobre como usar o VLOOKUP para encontrar a correspondência mais próxima para uma explicação detalhada sobre como definir o último argumento como verdadeiro.

atualização: O post foi atualizado para incluir instruções para calcular comissões com a nova função XLOOKUP. Aqui está um artigo & vídeo sobre XLOOKUP.

Baixar arquivo

baixe o arquivo para acompanhar.

cálculos de comissões-IF Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

calculando comissões com VLOOKUP

com um plano de comissão simples, você normalmente tem uma tabela de taxas que lista a taxa de pagamento em cada nível de vendas. À medida que o representante de vendas faz mais vendas, sua taxa de pagamento normalmente aumentará.

a taxa de pagamento pode ser um valor em dólar fixo, porcentagem da receita, porcentagem da cota, etc. O trabalho do VLOOKUP é encontrar o valor de vendas do representante na tabela de Taxas e retornar a taxa de pagamento correspondente.

para este exemplo, nosso plano de comissões se parece com o seguinte:

• Rep vende us $0-us$50.000, eles ganham 5%
• Rep vende $51,000-de r$100.000, eles ganham 7%
• Rep vende $100,001-us$150.000, eles ganham 10%
• Rep vende mais de us $150,001, eles ganham 15%

A repetição vai fazer com que uma certa quantidade de vendas durante o mês. Ele / ela vai ganhar uma certa taxa de pagamento, dependendo do nível de vendas alcançado.

podemos usar uma fórmula VLOOKUP para calcular a taxa de pagamento para um determinado valor de vendas (valor de pesquisa). Para que isso funcione, precisamos definir o último argumento no vlookup como TRUE.

com o último argumento definido como TRUE, vlookup encontrará a correspondência mais próxima do valor de pesquisa menor ou igual ao valor de pesquisa. Isso basicamente nos permite encontrar um valor entre intervalos de dois números (níveis).

é por isso que o último argumento no VLOOKUP é chamado range_lookup. Quando esse argumento é verdadeiro, VLOOKUP está procurando entre intervalos de valores na coluna tier minimum para encontrar uma correspondência exata ou o valor menor que o valor de pesquisa.

como configurar a tabela de taxas

ao colocar esta tabela de taxas no Excel, você só precisa listar o nível mínimo para o intervalo de pesquisa. Novamente, o VLOOKUP procurará uma” correspondência mais próxima ” que seja menor ou igual ao valor de pesquisa. Se encontrar um valor maior que o valor de pesquisa, ele retornará a linha anterior.

neste exemplo para uma tabela de taxa de comissões, a primeira linha no intervalo de pesquisa precisa ser zero. Isso ocorre porque o representante de vendas poderia ter vendas de US $ 0 e o valor de pesquisa seria zero. Se o valor de pesquisa (valor de vendas) fosse um número negativo, o vlookup retornaria um erro.

é importante saber isso e configurar sua tabela de taxas para todos os valores de pesquisa possíveis.

se o valor das vendas for maior que a última linha no intervalo de pesquisa, o vlookup retornará a última linha. Por exemplo, se o representante obtivesse vendas de US $175.000, o vlookup retornaria 15%.

calcular comissões para retornar um valor em Dólar

o pagamento também pode ser devolvido como um valor em dólar, em vez de uma porcentagem. Com esta configuração, o pagamento será uma taxa fixa.

isso significa que o pagamento será o mesmo, independentemente do valor das vendas dentro do nível. No exemplo abaixo, se o pagamento for $1.000 se o valor das vendas for $55.000 ou $95.000.

o pagamento não está em uma escala deslizante. É uma taxa fixa para cada nível.

se você estiver procurando por um cálculo de escala deslizante, consulte meu artigo sobre cálculo de comissão com uma estrutura de taxa em camadas usando SUMPRODUCT.

cálculos de Comissão com XLOOKUP

também podemos usar a nova função XLOOKUP para este cálculo. Ele funciona muito semelhante ao VLOOKUP ao encontrar a correspondência mais próxima.

> Vantagens com XLOOKUP

Existem duas principais diferenças e vantagens com XLOOKUP:

1. com XLOOKUP, especificamos a matriz de pesquisa e a matriz de retorno como intervalos separados. Isso é B4:B7 E D4: D7 na imagem acima. Esta é uma vantagem sobre o VLOOKUP porque podemos inserir ou excluir linhas entre as colunas B E D, e a fórmula ainda funcionará.
   quando adicionamos / excluímos colunas dentro da matriz de tabela do VLOOKUP, temos que alterar manualmente o número do índice da coluna ou usar uma fórmula para calculá-lo dinamicamente.
2. XLOOKUP não requer que os dados sejam classificados ao usar correspondência exata ou próximo item menor/maior para o modo de correspondência. O XLOOKUP realmente procurará o próximo item menor/maior e retornará o resultado dessa linha/coluna.
   VLOOKUP requer que os dados sejam classificados quando o último argumento for verdadeiro. Normalmente, você deseja classificar os dados para estes tipos de tabelas de qualquer maneira, mas é bom saber que você pode simplesmente colocar novos níveis de taxa na parte inferior da tabela e XLOOKUP vai trabalhar.

desvantagem do XLOOKUP

a principal desvantagem do XLOOKUP é a compatibilidade. Você e todos os usuários do seu arquivo devem estar em uma versão do Microsoft / Office 365 que tenha XLOOKUP. Não é compatível com versões anteriores ou está disponível em versões mais antigas do Excel.

portanto, VLOOKUP funciona muito bem neste cenário e é muito mais fácil de escrever do que uma fórmula if aninhada.

evite aninhado se fórmulas

uma abordagem comum para calcular comissões está usando instruções IF. Com uma tabela de taxas como essa, você teria que escrever várias instruções IF. Você basicamente tem que escrever uma instrução IF para cada nível (linha) na tabela.

você então tem que juntar todas as declarações IF em uma fórmula longa e feia. Estes são chamados aninhados if declarações.

eu sempre tento evitar IFs aninhados quando possível porque eles são difíceis de ler e entender, e eles podem ser mais lentos para o Excel para calcular. Se você tiver milhares de fórmulas aninhadas em sua pasta de trabalho, seu tempo de cálculo poderá desacelerar.

a fórmula IF é muito mais difícil de manter. Requer modificação quando as linhas são adicionadas / excluídas da tabela. VLOOKUP e XLOOKUP não requerem este tipo de manutenção.

se você usar IF, certifique-se de tornar cada referência de célula à tabela de Taxas uma referência absoluta (F4 no teclado). Caso contrário, você obterá resultados incorretos ao copiar a fórmula para baixo.

usar o VLOOKUP é muito mais fácil e limpo do que usar fórmulas if aninhadas. Como você pode ver com este exemplo, o vlookup permite que você use uma fórmula para calcular a taxa de pagamento da Comissão para qualquer valor de vendas.

Recursos Adicionais

• Como Utilizar o PROCV para Encontrar a Correspondência mais próxima, Último Argumento é Igual a TRUE
• Como Calcular Cumulativa Camadas Comissões com SUMPRODUCT
• PROCV Explicado em Termos Simples no Starbucks
• PROCV & MATCH – UM Duo Dinâmico
• O Novo XLOOKUP Função para o Excel + Vídeo Tutorial
• Função SE Explicou: Como Escrever uma Instrução IF Fórmula no Excel

Como Usar PROCV com a Correspondência mais próxima?

o VLOOKUP com a técnica de correspondência mais próxima também pode ser usado para cálculos de colchetes fiscais, correspondência de preços, etc. O que você usa essa técnica para calcular?