Fórmula IF Excel - Declarações If Then

Baixar exemplo de pasta de trabalho

Baixe a apostila de exemplo

Este tutorial demonstra como usar o Função Excel IF no Excel para criar declarações If Then.

Visão geral da função IF

A função IF verifica se uma condição é atendida. Se TRUE faça uma coisa, se FALSE faça outra.

Para usar a função de planilha do IF Excel, selecione uma célula e digite:

(Observe como as entradas da fórmula aparecem)

Sintaxe da função IF e entradas:

1	= IF (teste_lógico, valor_se_verdadeiro, valor_se_falso)

teste lógico - Expressão lógica. Exemplo: A1> 4.

valor_se_verdadeiro - Valor ou cálculo a ser executado se a expressão lógica for TRUE.

valor_se_falso - Valor ou cálculo a ser executado se a expressão lógica for FALSE.

IF é uma função “condicional”. Isso significa que você define um teste lógico e ele retornará um valor se o teste for avaliado como verdadeiro e um valor diferente se for falso

Como usar a função IF

Aqui está um exemplo muito básico para que você possa ver o que quero dizer. Tente digitar o seguinte no Excel:

1	= IF (2 + 2 = 4, "É verdade", "É falso!")

Visto que 2 + 2 na verdade é igual a 4, o Excel retornará "É verdade!". Se usássemos isso:

1	= IF (2 + 2 = 5, "É verdade", "É falso!")

Agora o Excel retornará "É falso!", Porque 2 + 2 não é igual a 5.

Veja como você pode usar IF em uma planilha.

1	= IF (C4-D4> 0, C4-D4,0)

Você administra uma barra de esportes e define limites de guias individuais para clientes diferentes. Você configurou esta planilha para verificar se cada cliente ultrapassou seu limite; nesse caso, você os interromperá até que paguem sua conta.

Você verifica se C4-D4 (o valor da guia atual menos o limite) é maior que 0. Este é o seu teste lógico. Se isso for verdade, IF retorna “Sim” - você deve cortá-los. Se for falso, IF retorna “Não” - você os deixa continuar bebendo.

O que IF pode retornar

Acima, retornamos uma string de texto, “Sim” ou “Não”. Mas você também pode retornar números ou mesmo outras fórmulas.

Digamos que alguns de seus clientes estejam usando grandes abas. Para desencorajar isso, você vai começar a cobrar juros dos clientes que ultrapassarem o limite.

Você pode usar IF para isso:

1	= IF (C4> D4, C4 * 0,03,0)

Se a tabulação for maior que o limite, retorne a tabulação multiplicada por 0,03, o que retorna 3% da tabulação. Caso contrário, retorne 0: eles não estão na guia, então você não cobrará juros.

Usando IF com AND

Você pode combinar IF com a função AND do Excel <>. Você usa isso no teste lógico, o que permite especificar duas ou mais condições para testar. O Excel só retornará TRUE se TODOS os testes forem verdadeiros.

Então, você implementou sua taxa de juros. Mas alguns de seus clientes regulares estão reclamando. Eles sempre pagaram suas contas no passado, por que você está reprimindo-os agora? Você chega a uma solução: você não cobra juros de certos clientes confiáveis.

Você cria uma nova coluna em sua planilha para identificar clientes confiáveis ​​e atualiza sua declaração IF com uma função AND:

1	= SE (E (C4> D4, F4 = "Não"), C4 * 0,03,0)

Vejamos a parte AND separadamente:

1	AND (C4> D4, F4 = "Não")

Observe as duas condições:

• C4> D4: verificar se eles ultrapassaram o limite de guias, como antes
• F4 = ”Não”: este é o novo bit, verificando se eles não são um cliente confiável

Portanto, agora só retornamos a taxa de juros se o cliente ultrapassou a guia e temos “Não” na coluna do cliente confiável. Seus clientes regulares estão felizes novamente.

Leia mais na página principal do Excel AND Function <>.

Usando IF com OR

OR é outra das funções lógicas do Excel. Como AND, permite definir mais de uma condição. Mas, ao contrário de AND, ele retornará TRUE se QUALQUER um dos testes que você definir for verdadeiro.

Talvez o fato de os clientes ultrapassarem a conta não seja o único motivo pelo qual você os interrompeu. Talvez você dê a algumas pessoas uma proibição temporária por outras razões, talvez jogar no local.

Então, você adiciona uma nova coluna para identificar clientes banidos e atualiza o seu “Corte?” coluna com um teste OR:

1	= SE (OU (C4> D4, E4 = "Sim"), "Sim", "Não")

Olhando apenas para a parte OR:

1	OU (C4> D4, E4 = "Sim")

Existem duas condições:

• C4> D4: verificar se eles ultrapassaram o limite de guias
• F4 = ”Sim”: a nova parte, verificando se eles estão atualmente banidos

Isso será avaliado como verdadeiro se eles ultrapassarem a guia ou se houver um "Sim" na coluna E. Como você pode ver, Harry está cortado agora, embora não tenha ultrapassado o limite de guias.

Leia mais na página principal da Função OR do Excel <>.

Usando IF com XOR

XOR é outra função lógica, que retorna o “Ou Exclusivo”. Isso é um pouco menos intuitivo do que os anteriores que discutimos.

Em casos simples, você define duas condições e o XOR retornará:

• TRUE se algum dos argumentos for verdadeiro (igual a um OR normal)
• FALSE se ambos os argumentos forem verdadeiros
• FALSE se ambos os argumentos forem falsos

Um exemplo pode tornar isso mais claro. Imagine que você queira começar a dar bônus mensais à sua equipe:

• Se eles venderem mais de $ 800 em comida ou mais de $ 800 em bebidas, você lhes dará meio bônus
• Se eles venderem mais de $ 800 em ambos, você lhes dará um bônus completo
• Se venderem menos de $ 800 em ambos, não recebem nenhum bônus.

Você já sabe como descobrir se eles recebem o bônus total. Você simplesmente usaria IF com AND, conforme descrito anteriormente.

1	= SE (E (C4> 800, D4> 800), "Sim", "Não")

Mas como você descobriria quem ganha a metade do bônus? É aí que entra o XOR:

1	= IF (XOR (C4> = 800, D4> = 800), "Sim", "Não")

Como você pode ver, as vendas de bebidas de Woody ultrapassaram US $ 800, mas não as vendas de alimentos. Então ele ganha a metade do bônus. O inverso é verdadeiro para o Coach. Diane e Carla venderam mais de $ 800 por ambos, então elas não recebem meio bônus (ambos os argumentos são VERDADEIROS), e Rebecca ganhou abaixo do limite para ambos (ambos os argumentos FALSO), então a fórmula retorna novamente “Não”.

Leia mais na página principal da Função Excel XOR <>.

Usando IF com NOT

NOT é mais uma das funções lógicas do Excel, que é muito comumente usada com IF.

NÃO inverte o resultado de um teste lógico. Em outras palavras, ele verifica se uma condição não foi atendida.

Você pode usá-lo com IF assim:

1	= SE (E (C3> = 1985, NÃO (D3 = "Steven Spielberg")), "Assistir", "Não assistir")

Aqui temos uma tabela com dados de alguns filmes dos anos 1980. Queremos identificar os filmes lançados em ou após 1985, que não foram dirigidos por Steven Spielberg.

Como NOT está aninhado em uma função AND, o Excel avaliará isso primeiro. Em seguida, ele usará o resultado como parte do AND.

Leia mais na página principal da Função NOT do Excel <>.

Declarações IF aninhadas

Você também pode retornar uma instrução IF dentro de sua instrução IF. Isso permite que você faça cálculos mais complexos.

Vamos voltar para a nossa mesa de clientes. Imagine que você queira classificar os clientes com base no nível de dívida com você:

• $ 0: nenhum
• Até $ 500: Baixo
• $ 500 a $ 1000: médio
• Acima de $ 1000: alta

Você pode fazer isso “aninhando” as declarações IF:

1	= SE (C4 = 0, "Nenhum", SE (C4 <= 500, "Baixo", SE (C4 <= 1000, "Médio", SE (C4> 1000, "Alto"))))

É mais fácil entender se você colocar as instruções IF em linhas separadas (ALT + ENTER no Windows, CTRL + COMMAND + ENTER no Macs):

12345

=IF (C4 = 0, "Nenhum",IF (C4 <= 500, "Baixo",IF (C4 <= 1000, "Médio",IF (C4> 1000, "Alto", "Desconhecido"))))

SE C4 for 0, retornamos “Nenhum”. Caso contrário, passamos para a próxima instrução IF. SE C4 for igual ou inferior a 500, retornamos “Baixo”. Caso contrário, passamos para a próxima instrução IF … e assim por diante.

Simplificando declarações complexas de IF com colunas auxiliares

Se você tiver várias instruções IF aninhadas e também estiver usando funções lógicas, suas fórmulas podem se tornar muito difíceis de ler, testar e atualizar.

É especialmente importante ter isso em mente se outras pessoas usarão a planilha. O que faz sentido na sua cabeça pode não ser tão óbvio para os outros.

As colunas auxiliares são uma ótima maneira de contornar esse problema.

Você é um analista do departamento financeiro de uma grande empresa. Você foi solicitado a criar uma planilha que verifica se cada funcionário é elegível para a pensão da empresa.

Aqui estão os critérios:

Portanto, se você tem menos de 55 anos, precisa ter 30 anos de serviço sob sua responsabilidade para se qualificar. Se você tem entre 55 e 59 anos, precisa de 15 anos de serviço. E assim por diante, até os 65 anos, quando você é elegível, não importa há quanto tempo você trabalhou lá.

Você pode usar uma única instrução IF complexa para resolver este problema:

1	= SE (OR (F4> = 65, E (F4> = 62, G4> = 5), E (F4> = 60, G4> = 10), E (F4> = 55, G4> = 15), G4 > 30), "Elegível", "Não elegível")

Uau! É meio difícil entender isso, não é?

Uma abordagem melhor pode ser usar colunas auxiliares. Temos cinco testes lógicos aqui, correspondendo a cada linha na tabela de critérios. Isso é mais fácil de ver se adicionarmos quebras de linha à fórmula, como discutimos anteriormente:

12345678

= SE (OU(F4> = 65,AND (F4> = 62, G4> = 5),AND (F4> = 60, G4> = 10),AND (F4> = 55, G4> = 15),G4> 30), "Elegível", "Não elegível")

Portanto, podemos dividir esses cinco testes em colunas separadas e, em seguida, simplesmente verificar se algum deles é verdadeiro:

Cada coluna da tabela de E a I contém cada um de nossos critérios separadamente. Então, em J4, temos a seguinte fórmula:

1	= SE (CONTAR.SE (E4: I4, VERDADEIRO), "Elegível", "Não Elegível")

Aqui temos uma instrução IF e o teste lógico usa CONT.SE <> para contar o número de células em E4: I4 que contêm VERDADEIRO.

Se CONT.SE não encontrar um valor VERDADEIRO, ele retornará 0, que IF interpreta como FALSO, então IF retorna “Não elegível”.

Se COUNTIF encontrar algum valor TRUE, ele retornará o número deles. IF interpreta qualquer número diferente de 0 como TRUE, então retorna “Elegível”.

Dividir os testes lógicos desta forma torna a fórmula mais fácil de ler e, se algo estiver errado com ela, é muito mais fácil detectar onde está o erro.

Usando agrupamento para ocultar colunas auxiliares

As colunas auxiliares tornam a fórmula mais fácil de gerenciar, mas depois de colocá-las no lugar e saber que estão funcionando corretamente, muitas vezes elas apenas ocupam espaço na planilha, sem adicionar qualquer informação útil.

Você pode ocultar as colunas, mas isso pode causar problemas porque as colunas ocultas são difíceis de detectar, a menos que você observe atentamente os cabeçalhos das colunas.

A melhor opção é agrupar.

Selecione as colunas que deseja agrupar, no nosso caso E: I. Em seguida, pressione ALT + SHIFT + SETA PARA A DIREITA no Windows ou COMMAND + SHIFT + K no Mac. Você também pode ir para a guia “Dados” na faixa de opções e selecionar “Grupo” na seção “Esboço”.

Você verá o grupo exibido acima dos cabeçalhos das colunas, assim:

Em seguida, basta pressionar o botão “-“ para ocultar as colunas:

A Função IFS

As instruções IF aninhadas são muito úteis quando você precisa realizar comparações lógicas mais complexas e precisa fazê-lo em uma célula. No entanto, eles podem ficar complicados à medida que ficam mais longos e podem ser difíceis de ler e atualizar na tela.

Do Excel 2022 e Excel 365, a Microsoft introduziu outra função, IFS, para ajudar a tornar isso um pouco mais fácil de gerenciar. O exemplo de IF aninhado acima pode ser alcançado com IFS como este:

1234567

= IFS (C4 = 0, "Nenhum",C4 <= 500, "Baixo",C4 <= 1000, "Médio",C4> 1000, "Alto",VERDADEIRO, "Desconhecido",)

Você pode ler tudo sobre isso na página principal do Excel IFS Function <>.

Usando IF com Formatação Condicional

O recurso de formatação condicional do Excel permite que você formate uma célula de diferentes maneiras, dependendo de seu conteúdo. Como o IF retorna valores diferentes com base em nosso teste lógico, podemos usar a formatação condicional para tornar mais fácil ver esses valores diferentes.

Então, vamos voltar para a nossa tabela de bônus da equipe anterior.

Estamos retornando "Sim" ou "Não", dependendo de qual bônus queremos dar. Isso nos diz o que precisamos saber, mas a informação não salta para nós. Vamos tentar consertar isso.

Veja como você faria isso:

• Selecione o intervalo de células que contém suas instruções IF. Em nosso caso, é E4: F8.
• Clique em “Formatação condicional” na seção “Estilos” da guia “Página inicial” na faixa de opções.
• Clique em “Highlight Cells Rules” e depois em “Equal to”.
• Digite “Sim” (ou qualquer valor de retorno de que você precisa) na primeira caixa e, a seguir, escolha a formatação desejada na segunda caixa. (Vou escolher verde para isso).
• Repita para todos os valores de retorno (também definirei os valores “Não” para vermelho)

Aqui está o resultado:

Usando IF em fórmulas de matriz

Uma matriz é um intervalo de valores e, no Excel, as matrizes são representadas como valores separados por vírgulas entre colchetes, como:

1	{1,2,3,4,5}

A beleza das matrizes é que elas permitem que você execute um cálculo em cada valor no intervalo e, em seguida, retorne o resultado. Por exemplo, a função SUMPRODUCT pega duas matrizes, multiplica-as e soma os resultados.

Portanto, esta fórmula:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Retorna 32. Por quê? Vamos trabalhar nisso:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Podemos trazer uma instrução IF para essa imagem, de modo que cada uma dessas multiplicações só aconteça se um teste lógico retornar verdadeiro.

Por exemplo, pegue estes dados:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "no"> 1 = SUMPRODUTO (SE ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))

Observação: no Excel 2022 e anteriores, você deve pressionar CTRL + SHIFT + ENTER para transformar isso em uma fórmula de matriz.

Acabaríamos com algo assim:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "no"> 1 $ C $ 2: $ C $ 10 = $ G2

Em inglês, se o nome na coluna C for igual ao que está em G2 ("Olivia"), multiplique os valores nas colunas D e E para essa linha. Caso contrário, não os multiplique. Em seguida, some todos os resultados.

Você pode aprender mais sobre esta fórmula na página principal da Fórmula SUMPRODUCT IF <>.

SE no Planilhas Google

A função IF funciona exatamente da mesma forma no Planilhas Google e no Excel:

Notas Adicionais

Use a função IF para testar se uma condição é TRUE. Se a condição for VERDADEIRA, faça uma coisa. Se for FALSO, faça outro. A condição deve ser uma expressão lógica (ex: a1> 5), uma referência a uma célula contendo VERDADEIRO ou FALSO ou uma matriz contendo todos os valores lógicos.

A função IF só pode testar uma condição de cada vez. No entanto, você pode “aninhar” outras funções lógicas dentro da condição IF para testar várias condições de uma vez:

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (OR (a1> 0, a2> 0), VERDADEIRO, FALSO)
= if (XOR (a1> 0, a2> 0), VERDADEIRO, FALSO)
OU Funções testam se um ou mais condições sejam atendidas.
Funções AND testam se tudo condições sejam atendidas.
Teste de funções XOR se um e somente um condições sejam atendidas.

Você também pode "aninhar" uma função IF dentro de uma função IF:

1	= if (a1 <0, if (a2 <0, "Ambos", "apenas 1"), "apenas um")

Agora, alguns exemplos concretos de como a função IF funciona na prática:

1. Inicie um novo livro de trabalho.

2. Na célula A1, insira o valor 10 (e pressione Enter)

3. Em seguida, na Célula B1, insira a seguinte fórmula:

1	= SE (A1> 5, "MAIOR QUE 5", "MENOS QUE 5")

4. A tela deve ser semelhante a esta agora:

5. Se você inseriu a fórmula corretamente, verá a mensagem “Maior que 5” na célula B1.

6. A fórmula que você inseriu na Célula B1 realiza o teste “A1> 5”, ou seja, verifica se o valor na Célula A1 é maior que 5. Atualmente o valor na Célula A1 é 10 - então a condição é VERDADEIRA e a mensagem “BIGGER THAN 5” aparece

7. Se agora alterarmos o valor na célula A1 para 2:

Então, a mensagem na célula B2 agora é “MENOS DE 5”, pois a condição é FALSA.

8. Você pode continuar alterando o valor na célula A1 e a mensagem na célula B2 será ajustada de acordo.

9. Claro que existem situações em que a condição pode dar resultados prejudiciais:

• O que acontece se inserirmos o valor 5 na célula A1?

• E se deixarmos a célula A1 em branco?

• E se colocarmos algum texto na célula A1, por exemplo, a frase DOG

Mais sobre a função Excel IF

Veremos agora a função IF com mais detalhes. Ele pode ser usado para analisar grandes quantidades de dados com muita facilidade.

Imagine que você é um Gerente de Vendas de Área e tem uma Equipe de Vendas. Você pode registrar o total de vendas que cada pessoa faz em uma planilha do Excel simples:

Suponha que o critério para um bônus fosse que as vendas feitas por essa pessoa excedessem £ 40.000. Você poderia apenas “examinar” os dados e determinar que apenas Anton, Newton e Monique atingiram a meta.

Isso é muito fácil quando você tem apenas um punhado de nomes. No entanto, se você tiver vários, há margem para erro. Felizmente, usando a função IF do Excel, isso pode ser feito de forma muito mais rápida e segura.

Configure uma nova pasta de trabalho e digite os dados conforme acima. Então, na célula D4, digite a seguinte fórmula: -

1	= SE (C4> 40000, "BÔNUS A PAGAR", "SEM BÔNUS")

para que você tenha:

Observe como o Excel mostra a estrutura da fórmula IF - que é um aide memoire útil.

Depois de digitar a fórmula, pressione ENTER e você a verá avaliar para a primeira linha:

A fórmula foi avaliada para Martin - como ele ganhou menos de £ 40.000, ele não tem direito a nenhum bônus.

E então arrastamos as fórmulas para baixo clicando no canto inferior direito e arrastando para baixo podemos determinar se ou cada pessoa tem direito a um bônus:

E vemos que o Excel determinou qual dos vendedores tem direito a um bônus.

Voltar para a lista de todas as funções no Excel

Declarações VBA IF

Você também pode usar as instruções If no VBA. Clique no link para saber mais, mas aqui está um exemplo simples:

1234567

Sub Test_IF ()Se Intervalo ("a1"). Valor <0, entãoIntervalo ("b1"). Valor = "Negativo"Fim seFim se

Este código testará se um valor de célula é negativo. Nesse caso, ele escreverá “negativo” na próxima célula.