Há muito tempo, trabalhei para o vice-presidente de vendas de uma empresa. Eu estava sempre modelando algum novo programa de bônus ou plano de comissão. Fiquei bastante acostumado a comissionar planos em todos os tipos de condições. O mostrado nesta dica é bem manso.
A abordagem normal é começar a construir uma fórmula IF aninhada. Você sempre começa na extremidade superior ou inferior da faixa. “Se as vendas forem superiores a $ 500.000, o desconto é de 20%; de outra forma,… ." O terceiro argumento da função IF é uma função IF totalmente nova que testa o segundo nível: "Se as vendas forem superiores a $ 250K, o desconto é de 15%; caso contrário, …"
Essas fórmulas ficam cada vez mais longas à medida que há mais níveis. A parte mais difícil de tal fórmula é lembrar quantos parênteses de fechamento colocar no final da fórmula.
Se você estiver usando o Excel 2003, sua fórmula já está chegando ao limite. Com essa versão, você não pode aninhar mais de 7 funções IF. Foi um dia feio quando os poderes que mudaram o plano de comissão e você precisava de uma maneira de adicionar uma oitava função IF. Hoje, você pode aninhar 64 funções IF. Você nunca deve aninhar tantos, mas é bom saber que não há problema em aninhar 8 ou 9.
Em vez de usar a função IF aninhada, tente usar a função VLOOKUP. Quando o quarto argumento de VLOOKUP muda de False para True, a função não está mais procurando uma correspondência exata. Bem, primeiro VLOOKUP tenta encontrar uma correspondência exata. Mas se uma correspondência exata não for encontrada, o Excel se acomoda na linha um pouco menos do que o que você está procurando.
Considere a tabela abaixo. Na célula C13, o Excel estará procurando uma correspondência de $ 28.355 na tabela. Quando não consegue encontrar 28355, o Excel retornará o desconto associado ao valor que é apenas menor - neste caso, o desconto de 1% para o nível de $ 10K.
Ao converter as regras para a tabela em E13: F18, você precisa começar do nível mais baixo e prosseguir para o nível mais alto. Embora não tenha sido declarado nas regras, se alguém estiver abaixo de $ 10.000 em vendas, o desconto será de 0%. Você precisa adicioná-lo como a primeira linha da tabela.
Cuidado
Quando você está usando a versão “True” de VLOOKUP, sua tabela deve ser classificada em ordem crescente. Muitas pessoas acreditam que todas as tabelas de pesquisa devem ser classificadas. Mas uma tabela precisa ser classificada apenas para uma correspondência aproximada.
E se o seu gerente quiser uma fórmula totalmente independente e não quiser ver a tabela de bônus à direita? Depois de construir a fórmula, você pode incorporar a tabela diretamente na fórmula. Coloque a fórmula no modo Editar clicando duas vezes na célula ou selecionando a célula e pressionando F2. Use o cursor para selecionar o segundo argumento inteiro: $ E $ 13: $ F $ 18.
Pressione a tecla F9. O Excel incorpora a tabela de pesquisa como uma constante de matriz. Na constante da matriz, um ponto e vírgula indica uma nova linha e uma vírgula indica uma nova coluna. Consulte Noções básicas sobre constantes de matriz.
Pressione Enter. Copie a fórmula para as outras células.
Agora você pode excluir a tabela. A fórmula final é mostrada abaixo.
Agradeço a Mike Girvin por me ensinar sobre os parênteses correspondentes. A técnica de VLOOKUP foi sugerida por Danny Mac, Boriana Petrova, Andreas Thehos e @mvmcos.
