Tratamento de várias condições em uma fórmula IF. Este artigo compara os três métodos diferentes.
Quando você precisa fazer um cálculo condicional, a função IF é a resposta. Caso contrário. Na figura a seguir, um IF simples calcula um bônus se suas vendas forem de $ 20.000 ou mais.
Mas o que acontece quando duas condições precisam ser atendidas? A maioria das pessoas aninha uma instrução IF dentro de outra, assim:
Mas isso sai do controle se você tiver muitas condições que precisam ser atendidas. A função AND encurtará e simplificará a fórmula. = AND (teste, teste, teste, teste) só será verdadeiro se todos os testes lógicos forem verdadeiros. O exemplo a seguir mostra uma fórmula mais curta com os mesmos resultados.
Se você gosta de AND, pode encontrar um uso para OR e NOT. = OR (teste, teste, teste, teste) será verdadeiro se qualquer um dos testes lógicos for verdadeiro. NÃO inverterá uma resposta. =NOT(True)é falso. =NOT(False)é verdade. Se você alguma vez tiver que fazer algo sofisticado como um NAND, você NÃO pode fazer (E (Teste, Teste, Teste, Teste)).
Cuidado
Embora o Excel 2013 tenha apresentado o XOR como um exclusivo ou, ele não funciona da maneira que os contadores esperariam. =XOR(True,False,True,True)é Verdadeiro por razões muito complicadas de explicar aqui. XOR realmente conta se você tem um número ímpar de valores True. Ímpar. Muito estranho.
Dica de bônus
Usando a lógica booleana
Sempre cubro o IF em meus seminários. E sempre pergunto como as pessoas resolveriam o problema das duas condições. Os resultados geralmente são os mesmos; 70-80% das pessoas usam IF aninhado e 20-30% usam AND. Apenas uma vez, na Virgínia, uma mulher da Price Waterhouse ofereceu esta fórmula:
Funciona. Ele dá a mesma resposta que as outras fórmulas. Calcule o bônus .02 * B4. Mas então multiplique esse bônus por testes lógicos entre parênteses. Quando você força o Excel a multiplicar um número por True ou False, o True torna-se 1 e o False torna-se 0. Qualquer número vezes 1 é ele mesmo. Qualquer número vezes 0 é 0. Multiplicar o bônus pelas condições garante que apenas as linhas que atendam às duas condições sejam pagas.
É legal. Funciona. Mas parece confuso quando você o vê pela primeira vez. Minha piada no seminário sempre é: “Se você vai deixar seu emprego no mês que vem e odeia seus colegas de trabalho, comece a usar esta fórmula”.
Assistir vídeo
- A função IF mais simples é
=IF(Logical Test,Formula if True, Formula if False) - Mas o que fazer se tiver que testar duas condições?
- Muitas pessoas farão
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Isso se torna difícil se 3, 5, 17 condições!
- Em vez disso, use
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Se você gosta de AND, considere OR, NOT para outras situações
- NAND pode ser feito com NOT (AND ())
- NOR pode ser feito com NOT (OU ())
- Tenha cuidado ao usar XOR, pois os resultados não são o que você esperava
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2025 - Condições múltiplas em IF!
Farei o podcast deste livro inteiro, o “i” no canto superior direito o levará à lista de reprodução de todos aqueles podcasts!
Tudo bem, vamos começar com o caso mais simples de IF do mundo, o vice-presidente de vendas fictício vem até nós e diz: "Ei, este mês qualquer pessoa que tenha mais de $ 20.000 em vendas receberá um bônus de 2%." Tudo bem, então a função IF tem três partes: Um teste lógico que vai dizer “É B4> $ 20000?” Vírgula, então o que fazer se for VERDADEIRO? Se for TRUE, .02 * B4, vírgula, o que fazer se for FALSE? Bem, se você não ganhou $ 20000, nenhum bônus para você é zero, certo. Ctrl + Enter para copiar isso, e você verá que temos o bônus apenas em linhas que estavam acima de $ 20000, esta perto, mas ainda sem bônus, certo. Agora, nunca houve um plano de bônus tão simples, certo, sempre há várias regras, então aqui, temos que verificar se a receita é> $ 20000 E se o lucro bruto percentual> 50%.
Tudo bem, e se você sabe como resolver isso, pense em como você vai resolver isso, tudo bem, e vou prever que muitos de vocês vão dizer "Bem, vamos começar com um Declaração IF e, mais tarde, outra declaração IF como esta, os IFs aninhados. ” E alguns de vocês estão dizendo "Bem, vamos fazer uma instrução IF e, imediatamente, aqui dentro dos parênteses, vamos para outra função chamada AND." E então, há uma maneira de fazer isso sem nenhum IFs. Tudo bem, então vou dar uma olhada nisso, então aqui está a primeira, a forma mais comum, o IF aninhado, OK, é assim que a maioria das pessoas faria isso.
= SE, faça o primeiro teste, veja se a receita em B4> $ 20.000, se for então faça outro SE, veja se o lucro bruto percentual> 0,05? Se for VERDADEIRO, então ambas as condições são VERDADEIRAS, podemos fazer 0,02 * B4, caso contrário, nenhum bônus. OK, mas ainda não terminamos, feche os parênteses internos, vírgula e, se o primeiro teste não for verdadeiro, sem bônus, Ctrl + Enter para copiar. Você vê que apenas as linhas que estão acima de $ 20.000 e acima do percentual de lucro bruto de 50% recebem o bônus. Tudo bem agora, no passado, costumava haver esse limite horrível, onde você não podia aninhar mais de 7 instruções IF. Aquele foi um dia doloroso, principalmente se você estivesse apenas adicionando condições lentamente ao longo dos meses e finalmente tivesse um que tinha 7 e precisava adicionar um 8º. Tudo bem, bem, hoje você pode ir para 32, eu não acho que você deveria ir para 32,mas se você precisa desesperadamente de ir de 7 para 8, então isso é uma coisa boa, tudo bem. Portanto, esta é a abordagem da instrução IF aninhada. Quando faço meus seminários ao vivo, cerca de metade da sala está fazendo isso, mas há um jeito muito, muito melhor de fazer.
= IF e então imediatamente entra em uma função chamada AND, então dentro do AND colocamos todos os testes: É a receita> 20.000, vírgula, é um lucro bruto percentual> 0,5. Se houver mais testes, continue colocando vírgulas com os testes adicionais, e feche o final, tudo no final tem que ser TRUE para que o final seja TRUE. Então se chegarmos a este ponto se o final for VERDADEIRO, .02 * receita, caso contrário, 0, é uma fórmula mais curta, é mais fácil de entrar, você obtém os mesmos resultados, a vida é ótima.
Tudo bem, de todos os seminários que fiz nos últimos 15 anos, apenas uma vez alguém entrou e me atingiu com essa fórmula maluca. Ela disse: “Olha, vamos apenas fazer = 0,02 * a receita, assim.” Tudo bem, calcule o bônus, e eu fico tipo “Uau, espere, isso vai sair caro, você vai dar o bônus para todos”. ela fica tipo “Espere, eu não terminei, vezes e depois entre parênteses vamos colocar cada condição, então receita> 20.000 vezes, entre parênteses, porcentagem do lucro bruto> 0,5”. Tudo bem, e aqui está o que acontece é que calculamos o bônus, e então eles avaliam como VERDADEIRO ou FALSO. E quando forçamos o Excel a multiplicar um VERDADEIRO ou FALSO por um número, o VERDADEIRO se torna 1, qualquer coisa * 1 é ele mesmo, o FALSO se torna 0! Tudo bem, então o que temos aqui é 2% * a receita * 1 * 0, qualquer coisa * 0 = 0, certo,então isso limpa o bônus. Esta é a lógica booleana, TRUE * TRUE, 1 * 1 = 1, se ambos forem FALSE ou todos eles forem FALSE, isso será avaliado como 0 e obteremos exatamente o mesmo resultado. Eu acho que você deveria mudar para este? Não, é confuso, a menos que você esteja deixando seu emprego na próxima semana e odeie seus colegas de trabalho, sinta-se à vontade para mudar para isso, tudo bem.
Se você gosta da função AND, existem outras funções ou verificações para ver se alguma das condições é TRUE, então isso ou isso ou isso retornará TRUE. NOT vai reverter TRUE para FALSE e FALSE para TRUE, o que é útil quando você está tentando fazer os conceitos booleanos de NAND ou NOR. NAND significa not-and, é TRUE quando pelo menos uma condição é FALSE, certo. Então, se nenhum deles for VERDADEIRO, isso é ótimo, se alguns deles forem VERDADEIROS, isso é ótimo, mas assim que todos eles forem VERDADEIROS, então não pagamos. NOR significa not-or, significa que nenhuma das condições é TRUE - se isso acontecer, ou isso acontecer, ou se isso acontecer, nenhum bônus para você. E então o XOR, agora cuidado com este, ele foi introduzido no Excel 2013 e não faz o que nós, contadores, achamos que deveria fazer.Exclusivo-ou significa que apenas um dos testes é VERDADEIRO e funciona quando há duas condições. Mas para engenheiros elétricos, eles estão fazendo isso em pares, então não dá os resultados que você pode imaginar.
Tudo bem, então aqui está o Teste 1, Teste 2, Teste 3, Teste 4, três deles são VERDADEIROS e XOR diz "É exatamente um destes VERDADEIRO?" E quando fazemos este XOR, ele diz "Sim, exatamente um daqueles que é VERDADEIRO." e isso porque a função do Excel está duplicando a operação de um chip muito comum que é usado em engenharia elétrica, eu sei, é chocante, certo? Você acha que o Excel é apenas para contadores, mas os engenheiros também usam o Excel e, aparentemente, adicionaram XOR para eles e não para contadores. Então a forma como isso é avaliado, quando eles olham para os dois primeiros, “um desses 2 é VERDADEIRO? Sim!" Tudo bem, então obtemos aquele VERDADEIRO, e então eles pegam a resposta do XOR disso e a comparam com o VERDADEIRO, “um destes 2 é VERDADEIRO? Não, 2 deles são VERDADEIROS, então isso se torna um FALSO! ” Então eles pegam essa resposta e fazem um XOR com a última,então eles estão fazendo isso em pares, certo? “É um destes 2 VERDADEIRO? Sim!" Certo, é assim que entendemos. Acontece que o que está realmente fazendo, a coisa da engenharia elétrica é contar se um número ímpar de entradas for VERDADEIRO. Não é necessariamente útil para contadores, que esperam fazer o que significa XOR em inglês.
Certo, muitas dicas ótimas neste livro, coisas úteis e até mesmo essa discussão horrível sobre NAND e XOR e coisas assim. Compre o livro, você terá todas essas dicas na palma da sua mão. Recapitulação de hoje: Função IF mais simples, = teste lógico IF, o que fazer se for VERDADEIRO, o que fazer se for FALSO, mas se você tiver 2 condições, muitas pessoas aninham as declarações IF, mas imagine se você tivesse 3 , 5 ou 17 condições para aninhar. E vai resolver isso, encurtar um pouco, então se você gosta de AND, tem OR ou NÃO, você pode fazer NAND, você pode fazer NOR, mas tome cuidado ao usar aquele novo Excel 2013 XOR, os resultados podem não ser o que você Espero.
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: Podcast2025.xlsx
