Você tem uma fórmula do Excel que aninha várias funções IF? Quando você chega ao ponto com muitas instruções IF aninhadas, você precisa ver se tudo se tornaria mais simples com uma função VLOOKUP simples. Já vi fórmulas de 1000 caracteres serem simplificadas para uma fórmula VLOOKUP de 30 caracteres. É fácil manter quando você precisa adicionar novos valores posteriormente.
Há muito tempo, trabalhei para o vice-presidente de vendas do trabalho. 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 que está abaixo é 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%; de outra forma,… "
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.
Mini Dica Bônus
Combinando os parênteses
O Excel percorre uma variedade de cores para cada novo nível de parênteses. Enquanto o Excel reutiliza as cores, ele usa preto apenas para o parêntese de abertura e para o parêntese de fechamento correspondente. Ao terminar a fórmula abaixo, continue digitando os parênteses de fechamento até digitar um parêntese preto.
Voltar para a dica de fórmula aninhada
Se você estiver usando o Excel 2003, sua fórmula já está chegando ao limite. Naquela época, você não podia 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 fazer isso, mas é bom saber que não há problema em aninhar 8 ou 9.
Em vez de usar a função IF aninhada, tente usar o uso incomum para 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. Nesse 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 no caso de fazer 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.
Usando o cursor, selecione todo o segundo argumento: $ E $ 13: $ F $ 18.
Pressione a tecla F9. O Excel irá incorporar 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.
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.
Assistir vídeo
- Com um programa de comissão, bônus ou desconto em camadas, você geralmente precisa aninhar suas funções IF
- O limite do Excel 2003 era de 7 instruções IF aninhadas.
- Agora você pode aninhar 32, mas não acho que você deva aninhar 32
- Quando você usaria a versão de correspondência aproximada de VLOOKUP? Esta é a hora.
- Traduzir o programa de desconto em uma tabela de pesquisa
- PROCV não encontrará a resposta na maioria dos casos.
- Colocando, True no final dirá a VLOOKUP para encontrar o valor apenas menos.
- Esta é a única vez que a tabela VLOOKUP deve ser classificada.
- Não quer a tabela VLOOKUP para o lado? Incorpore na fórmula.
- F2 para editar a fórmula. Selecione a tabela de pesquisa. Pressione F9. Entrar.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2030 - IF aninhado substituído por VLOOKUP!
Estarei enviando o podcast deste livro inteiro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Tudo bem, isso é muito comum com um programa de comissão, ou um programa de desconto, ou um programa de bônus, onde temos os níveis, diferentes níveis, certo? Se você estiver acima de $ 10000, terá 1% de desconto, $ 50000 5% de desconto. Você tem que ter cuidado ao construir essa instrução IF aninhada, você começa na extremidade superior se estiver procurando maior que, ou na extremidade inferior se estiver procurando menos que. Assim, B10> 50000, 20%, caso contrário, outro IF, B10> 250000, 25% e assim por diante. Esta é apenas uma fórmula longa e complicada e, se sua tabela for maior, no Excel 2003, você não pode aninhar mais de 7 instruções IF, estamos quase perto do limite aqui. Você pode ir para 32 agora, eu não acho que você deveria ir para 32, e mesmo se você estiver gritando “Ei, espere,e a nova função que acabou de sair no Excel 2016, a versão de fevereiro de 2016, com a função IFS? ” Sim, claro, há menos parênteses aqui e 87 caracteres em vez de 97 caracteres, ainda é uma bagunça, vamos nos livrar disso e fazer isso com um PROCV!
Tudo bem, aqui estão as etapas, você segue essas regras e as vira de cabeça para baixo. A primeira coisa que temos a dizer é, se você teve $ 0 em vendas, você obtém um desconto de 0%, se você teve $ 10.000 em vendas, você obtém um desconto de 1%, se você tem $ 50.000 em vendas, você obtém um desconto de 5% . $ 100.000, desconto de 10%, $ 250000, desconto de 15% e, finalmente, qualquer coisa> $ 500.000 recebe o desconto de 20%, certo. Agora, muitas vezes, toda vez que estou falando sobre VLOOKUP, digo que cada VLOOKUP que você criar vai terminar em FALSO, e as pessoas vão dizer "Bem, espere um segundo, para que serve a versão VERDADEIRA?" É exatamente para este caso em que estamos procurando por um intervalo, então estamos procurando este valor, um VLOOKUP normal, é claro, 2, FALSE não encontrará 550000, retornará o # N / A!Portanto, neste caso muito especial, vamos alterar esse FALSE para um TRUE, e isso dirá ao Excel "Vá procurar o 550000, se você não conseguir encontrá-lo, forneça o valor que é apenas menor." Então isso nos dá os 20%, é isso que faz, certo? E esta é a única vez que sua tabela VLOOKUP tem que ser classificada, todas as outras vezes com, FALSE, pode ser como você quiser. E sim, você poderia deixar o TRUE desativado, mas eu sempre coloco lá apenas para me lembrar que este é um daqueles VLOOKUPs estranhos e incrivelmente perigosos, e eu não quero copiar essa fórmula para outro lugar. Tudo bem, então vamos copiar e colar fórmulas especiais, certo.tudo bem? E esta é a única vez que sua tabela VLOOKUP tem que ser classificada, todas as outras vezes com, FALSE, pode ser como você quiser. E sim, você poderia deixar o TRUE desativado, mas eu sempre coloco lá apenas para me lembrar que este é um daqueles VLOOKUPs estranhos e incrivelmente perigosos, e eu não quero copiar essa fórmula para outro lugar. Tudo bem, então vamos copiar e colar fórmulas especiais, certo.tudo bem? E esta é a única vez que sua tabela VLOOKUP tem que ser classificada, todas as outras vezes com, FALSE, pode ser como você quiser. E sim, você poderia deixar o TRUE desativado, mas eu sempre coloco lá apenas para me lembrar que este é um daqueles VLOOKUPs estranhos e incrivelmente perigosos, e eu não quero copiar essa fórmula para outro lugar. Tudo bem, então vamos copiar e colar fórmulas especiais, certo.
Próxima reclamação: o seu gerente não quer ver a tabela de pesquisa, ele quer "Apenas se livre dessa tabela de pesquisa". Tudo bem, podemos fazer isso incorporando a tabela de pesquisa, veja só, ótimo truque que aprendi com Mike Girvin da ExcelIsFun. F2 para colocar a fórmula no modo Editar e, a seguir, selecionar com muito cuidado apenas o intervalo da tabela de pesquisa. Pressione F9, e ele pega esta tabela e a coloca na nomenclatura de array, e então eu apenas pressiono Enter assim. Copie isso, Colar Fórmulas Especiais, e então estou livre para me livrar da tabela de pesquisa, que continua a trabalhar na linha. Isso é um grande aborrecimento se você tiver que voltar e editar isso, você realmente tem que olhar para ele com muita clareza. A vírgula significa que vamos para a próxima coluna, o ponto-e-vírgula significa que vamos para a próxima linha, certo,mas você poderia, em teoria, voltar lá e mudar essa fórmula se um dos números da cadeia mudar.
Tudo bem, então usar uma VLOOKUP em vez de uma instrução IF aninhada é a dica nº 32 em nosso caminho para a 40, “40 melhores dicas de Excel de todos os tempos”, você pode ter este livro inteiro. Clique naquele “i” no canto superior direito, $ 10 para um e-book, $ 25 para o livro impresso, certo. Então, hoje estamos tentando resolver um bônus de comissão escalonado ou programa de desconto. IFs aninhados são muito comuns, cuidado, 7 é tudo que você pode ter no Excel 2003, hoje você pode ter 32, mas nunca deveria ter 32. Então, quando usar a versão MATCH aproximada de VLOOKUP, é isso. Pegue esse programa de desconto, vire-o de cabeça para baixo, transforme-o em uma tabela de pesquisa começando com o menor número e indo para o maior número. VLOOKUP, é claro, não encontrará a resposta, mas alterando VLOOKUP para TRUE no final, ele dirá para encontrar o valor apenas menos,esta é a única vez que a tabela deve ser classificada. Se você não quiser ver aquela tabela lá fora, você pode incorporá-la na fórmula usando o truque de Mike Girvin, F2 para editar a fórmula, selecione a tabela de pesquisa, pressione F9 e Enter.
Certo ei, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2030.xlsx
