Fórmulas aninhadas e lógica condicional no Excel

Se você trabalha com planilhas diariamente, mais cedo ou mais tarde vai se deparar com elas. Fórmulas aninhadas e lógica condicional no ExcelEles são a base de muitos modelos poderosos: desde um simples sistema de aprovação/reprovação até sistemas complexos de comissões, descontos ou classificação de dados.

Domine as funções SE, E, OU e NÃO, juntamente com variações mais modernas, como... Alternativas do tipo SE.CONJUNTO e PROCVIsso faz toda a diferença entre uma planilha que "funciona mais ou menos" e um arquivo estável, fácil de manter e livre de surpresas de última hora. Vamos analisar isso com calma, mas de forma direta e com exemplos claros.

O que é lógica condicional no Excel e por que ela é tão importante?

A lógica condicional no Excel baseia-se na formulação de uma condição. Pergunta de verdadeiro/falso e retorna um resultado diferente em cada caso. O cerne de tudo isso é a função SE, que responde à ideia: "se A acontecer, faça B; se não, faça C".

A função A função SE verifica uma condição lógica. (o teste lógico) e, dependendo se a condição for atendida ou não, retorna um valor diferente: um quando o resultado é VERDADEIRO e outro quando é FALSO. É válido tanto para números quanto para texto, datas ou outros resultados de fórmulas.

Em termos de sintaxe, a função clássica é escrita assim: =SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)Os testes lógicos podem ter operadores de comparação como =, <>, >, <, >= ou <= e podem usar referências de células, constantes ou até mesmo outras funções.

O argumento valor_se_verdadeiro Isto é o que é retornado quando a condição é atendida: pode ser texto, um número, outra fórmula ou uma referência. O argumento valor_se_falso Este é o valor retornado quando a condição não é atendida; se você deixar em branco, o Excel exibirá FALSO por padrão, o que geralmente causa confusão se você não planejou isso.

Primeiros exemplos simples com a função SE

Um uso típico da função SE é classificar dados em dois grupos. Por exemplo, você pode querer distinguir se uma pessoa é adulto ou menor dependendo da idade:

Em uma célula, você poderia escrever algo como isto: =SE(C3>=18; "Adulto"; "Menor de idade")Aqui, o teste lógico é C3>=18; se for VERDADEIRO, o resultado é "Adulto", e se for FALSO, é "Menor de idade".

A beleza dessa estrutura reside no fato de que ela funciona da mesma forma com valores de textoContinuando com o mesmo exemplo, você poderia perguntar se uma pessoa tem direito a um desconto com base na etiqueta que você atribuiu a ela em outra coluna: =SE(D3="Maior de 18 anos"; "Sim"; "Não").

Em essência, a lógica por trás de todos esses exemplos é sempre a mesma: o Excel avalia o teste, verifica se é verdadeiro ou falso e, em cada caso, retorna um resultado diferente dependendo do que você definiu.

Combine a operação SE com as funções lógicas E, OU e NÃO.

Quando a situação exige a avaliação de mais de uma condição, as coisas ficam interessantes. O Excel oferece funções lógicas. E, Ou e Nãoque são frequentemente combinadas com SI para construir testes mais complexos.

A função Y Retorna VERDADEIRO somente se todas as condições são verdadeirasEm combinação com SI, a estrutura típica seria: =SE(E(condição1; condição2); valor_se_verdadeiro; valor_se_falso)É muito útil quando você precisa que vários requisitos sejam atendidos ao mesmo tempo, como uma faixa etária ou vários critérios de validação.

A função O retorna VERDADEIRO se pelo menos uma das condições é atendidaEm uma fórmula do SI, seria integrada desta forma: =SE(OU(condição1; condição2); valor_se_verdadeiro; valor_se_falso)É ideal para casos em que existem diferentes situações que dão direito ao mesmo resultado, como por exemplo, vários motivos aceitáveis ​​para solicitar um benefício.

A função NÃO Inverte o resultado lógico: se a condição for verdadeira, retorna FALSO, e se for falsa, retorna VERDADEIRO. Combinado com a função SE, teria esta forma: =SE(NÃO(condição); valor_se_verdadeiro; valor_se_falso)Algo útil quando o que você procura é exatamente o oposto de um teste.

As funções AND e OR aceitam até 255 condições individuaisNo entanto, na prática, não é aconselhável ir tão longe, pois as fórmulas tornam-se muito difíceis de ler, testar e manter. A função NÃO, por outro lado, permite apenas uma condição.

Exemplos práticos com SIM, E, OU e NÃO

Imagine que você tem números em sua planilha e deseja validar intervalos. Você poderia configurar algo assim: =SE(E(A2>0; B2<100); VERDADEIRO; FALSO)Esta fórmula confirma se A2 é maior que 0 e se B2 é menor que 100. Se ambas as condições forem verdadeiras, retorna VERDADEIRO; se alguma delas falhar, retorna FALSO.

Também funciona perfeitamente com texto. Um exemplo seria: =SE(E(A3="Vermelho", B3="Verde", VERDADEIRO, FALSO)Aqui, Y exige que A3 contenha "Vermelho" e B3 "Verde". Se apenas uma das duas condições for atendida, o resultado será FALSO, pois ambas as condições não são satisfeitas simultaneamente.

O uso de O altera o comportamento. Por exemplo: =SE(OU(A4>0; B4<50); VERDADEIRO; FALSO) A função retornará VERDADEIRO se A4 for maior que 0 ou se B4 for menor que 50; basta que qualquer uma das duas condições seja verdadeira para que a função OU seja considerada verdadeira.

Outro caso comum: =SE(OU(A5="Vermelho", B5="Verde", VERDADEIRO, FALSO)Aqui, a fórmula retornará VERDADEIRO se pelo menos uma das duas células contiver o texto esperado. Para casos em que você deseja verificar várias opções válidas, o operador OU é ideal.

Com "NÃO", você pode inverter o critério. Por exemplo =SE(NÃO(A6>50); VERDADEIRO; FALSO) Retornará VERDADEIRO se A6 não for maior que 50. Ou =SE(NÃO(A7="Vermelho"); VERDADEIRO; FALSO) Será marcado como VERDADEIRO quando o conteúdo de A7 for diferente de “Vermelho”.

Trabalhando com datas usando lógica condicional

A lógica condicional no Excel não se limita a números ou texto; com o As datas também podem ser comparadas. Muito útil para datas de vencimento, prazos ou acompanhamentos.

Um exemplo simples: =SE(A2>B2; VERDADEIRO; FALSO) A fórmula compara duas datas; se a data na célula A2 for posterior à data na célula B2, ela retorna VERDADEIRO. Isso permite determinar rapidamente se um evento ocorreu após uma data de referência.

Se você quiser validar se uma data está dentro de um intervalo, pode usar Y: =SE(E(A3>B2; A3Esta fórmula verifica se A3 é posterior a B2 e anterior a C2. Se ambas as condições forem atendidas, considera-se que está dentro do intervalo.

A combinação com O abre mais possibilidades. Por exemplo: =SE(OU(A4>B2; A4O critério aqui é que A4 seja mais recente que B2 ou esteja dentro de um período de 60 dias após B2, usando a soma dos dias imediatamente após a data.

E com a opção NÃO, os casos excluídos não podem ser identificados, como por exemplo... =SE(NÃO(A5>B2); VERDADEIRO; FALSO), que marca VERDADEIRO quando A5 não for posterior a B2, ou seja, quando for anterior ou igual à data de referência.

Use os operadores AND, OR e NOT na formatação condicional.

A lógica condicional não é usada apenas em fórmulas comuns, ela também é fundamental para... formatação condicional baseada em fórmulasNesse contexto, muitas vezes você nem precisa envolver AND, OR ou NOT dentro de um YES.

Na guia Página Inicial do Excel, dentro de Formatação condicional > Nova regraVocê pode escolher “Usar uma fórmula para determinar quais células formatar” e escrever diretamente uma fórmula lógica, por exemplo. =A2>B2Se a condição for verdadeira, o Excel aplicará a formatação escolhida.

Continuando com exemplos de datas, você poderia usar =E(A3>B2; A3) como uma fórmula de formatação condicional para destacar as células que contêm uma data entre B2 e C2. Enquanto essa condição for atendida, a célula será formatada com a formatação selecionada.

Outra possibilidade: =O(A4>B2; A4<B2+60) Para destacar datas que atendam a pelo menos um desses critérios. O uso de O facilita a detecção de vários tipos de casos em uma única regra de formatação condicional.

Por fim, você poderia sugerir =NÃO(A5>B2) Para destacar valores que não ultrapassem uma determinada data, sem a necessidade de envolvê-los em uma função SE. A formatação condicional interpreta diretamente o resultado verdadeiro/falso da fórmula.

O que é uma fórmula SE aninhada e quando usá-la?

Quando uma única condição não é suficiente e você precisa mais de dois resultados possíveisEntram em ação as fórmulas SE aninhadas: basicamente, inserir uma função SE dentro de outra como parte do argumento valor_se_falso ou valor_se_verdadeiro.

A ideia é que o Excel avalia o primeiro testeSe a condição for atendida, retorna o resultado esperado; caso contrário, em vez de fornecer um valor final, avalia uma segunda função IF contendo outro teste, e assim por diante. Dessa forma, é possível encadear vários níveis de tomada de decisão.

Um exemplo bastante conhecido é a conversão de notas numéricas em notas por letras. Uma primeira versão simples poderia ser algo como isto: =SE(D2>89; «A»; SE(D2>79; «B»; SE(D2>69; «C»; SE(D2>59; «D»; «F»)))).

A lógica por trás dessa fórmula é passo a passo: se a nota for maior que 89, é atribuído um A; caso contrário, verifica-se se é maior que 79 para atribuir um B; se isso também não for verdade, tenta-se 69 para um C; depois 59 para um D e, se nenhuma dessas opções for verdadeira, retorna-se um F. É um exemplo clássico de... SE aninhado em cadeia.

O esquema torna-se mais complexo se você quiser refiná-lo ainda mais, por exemplo, separando A+, A e A- ou adicionando mais nuances. A fórmula poderia evoluir para algo como isto: =SE(B2>97; «A+»; SE(B2>93; «A»; SE(B2>89; «A-«; SE(B2>87; «B+»; SE(B2>83; «B»; SE(B2>79; «B-«; SE(B2>77; «C+»; SE(B2>73; «C»; SE(B2>69; «C-«; SE(B2>57; «D+»; SE(B2>53; «D»; SE(B2>49; «D-«; «F»))))))))))).

Embora funcionalmente correta, essa fórmula... É um processo longo, tedioso de escrever e difícil de comprovar.Além disso, qualquer alteração nos limites ou rótulos exige que você edite manualmente toda a string, aumentando consideravelmente a probabilidade de introduzir um erro difícil de detectar.

Limitações e problemas das fórmulas SE aninhadas

O Excel permite aninhamento de até 64 funções SE dentro da mesma fórmulaMas isso não significa que seja uma boa ideia se aproximar desse limite. Depois de alguns níveis, a fórmula começa a ficar incontrolável.

Entre as principais desvantagens estão os complexidade da lógica e o risco de erros silenciososUma cadeia de SI (Sistemas de Informação) pode parecer funcionar bem na maioria dos casos, mas falhar em uma pequena porcentagem de situações que você só detecta depois que o problema já ocorreu.

Outra dificuldade é a Manutenção de médio e longo prazoSe você voltar a essa planilha meses depois, ou se outra pessoa a herdar, entender exatamente o que aquela fórmula cheia de parênteses pretendia fazer é uma verdadeira dor de cabeça, especialmente se ninguém documentou a lógica original.

Se você se deparar com uma fórmula SE que continua crescendo e adicionando cada vez mais condições, provavelmente é hora de... propor uma estratégia diferenteUtilize uma função mais adequada ou reestruture o modelo de dados.

Um exemplo típico de uma fórmula SE aninhada que se torna difícil de manter é o cálculo de comissões por níveis. Por exemplo: =SE(C9>15000; 20%; SE(C9>12500; 17,5%; SE(C9>10000; 15%; SE(C9>7500; 12,5%; SE(C9>5000; 10%; 0)))))onde você aumenta a comissão de acordo com seu nível de renda.

Importância da ordem em condições aninhadas

Em fórmulas SE aninhadas que trabalham com intervalos, o A ordem das comparações é crucial.Se você definir uma condição muito ampla em primeiro lugar, os demais testes nunca serão avaliados para determinados valores, resultando em resultados errôneos.

Continuando com o exemplo da comissão, se você escrever as comparações em ordem crescente (primeiro >5000, depois >7500, etc.) em vez de ordem decrescente, você descobrirá que valores altos acionam o primeira condição válida e interromper a avaliaçãoPor exemplo, com uma renda de 12.500, uma fórmula mal ordenada poderia retornar 10% simplesmente porque é maior que 5.000 e não verifica as faixas de renda mais altas.

Esse tipo de falha pode ser especialmente perigoso porque Não apresenta nenhum erro visível.A fórmula retorna uma porcentagem, o que parece correto, mas o cálculo está incorreto. Em contextos de folha de pagamento, bônus ou precificação, essas discrepâncias podem ter consequências significativas.

Portanto, ao usar instruções IF aninhadas para intervalos ou extensões, é aconselhável definir claramente a ordem em que você deseja que as condições sejam avaliadas e verificar alguns casos extremos para garantir que a lógica se comporte conforme o esperado.

Neste ponto, faz sentido considerar alternativas mais robustas, como o uso de tabelas de referência com PROCV ou recorrer a funções mais modernas que simplifiquem a estrutura da fórmula.

Use a função PROCV como alternativa a longas instruções SE aninhadas.

Em muitos cenários onde você usa longas sequências de instruções IF, você poderia resolver isso de forma mais elegante com um tabela de referência e função de pesquisa, como a função PROCV. A ideia é transferir a complexidade da fórmula para uma pequena tabela que reúne todos os casos.

Para isso, primeiro você cria uma tabela com duas colunas: na primeira, você coloca os intervalos ou valores de referência ordenados (por exemplo, limites de notas ou faixas de renda) e na segunda... resultado associado a cada segmento (a nota, a porcentagem da comissão, etc.).

Depois de ter essa tabela, você pode usar uma fórmula como esta: =PROCV(C2; C5:D17; 2; VERDADEIRO)onde C2 é o valor a ser pesquisado, C5:D17 é o intervalo que inclui a tabela, 2 indica que você deseja o resultado da segunda coluna e TRUE ativa uma pesquisa de intervalo aproximado.

Outro exemplo semelhante seria =PROCV(B9; B2:C6; 2; VERDADEIRO)Esta função procura o valor na célula B9 da primeira coluna do intervalo B2:C6 e retorna o valor correspondente da segunda coluna. Dessa forma, você substitui um valor na célula B9 pela célula B9. Função IF aninhada em vários níveis por meio de uma única função de busca..

As vantagens são claras: A tabela de referência está visível e é editável.Você pode alterar limites, porcentagens ou rótulos sem mexer na fórmula e, se não quiser que os usuários a vejam, pode sempre colocá-la em outra planilha dentro da mesma pasta de trabalho.

SETIFY: a evolução natural de IFs aninhados

Nas versões modernas do Excel (Office 2019, 2021 e Microsoft 365), você tem a função SIM DEFINIDO, projetado precisamente para aliviar a necessidade de encadear instruções IF uma após a outra e tornar o código muito mais legível.

IFS.SET permite definir até 127 pares “teste lógico – resultado” Na mesma fórmula. Em vez de ter um único valor "se-falso", você lista os casos um após o outro e o Excel retorna o resultado correspondente. primeira condição que é atendida.

Tomando como exemplo as notas, você poderia transformar a fórmula clássica de vários "ses" em algo como... =SE(D2>89; "A"; D2>79; "B"; D2>69; "C"; D2>59; "D"; VERDADEIRO; "F")O último par (VERDADEIRO; «F») funciona como uma cláusula geral para qualquer caso não abrangido pelas condições acima.

A grande vantagem é que O emaranhado de parênteses desaparece. típico de instruções IF aninhadas, e a fórmula fica muito mais clara de ler e manter, especialmente quando você tem várias seções ou cenários diferentes.

Em contextos mais avançados, você pode até combinar IF.SET com funções lógicas como AND ou OR em testes, de forma semelhante a como faria em um IF clássico, mas mantendo uma estrutura mais organizada.

No entanto, como não existe um argumento genérico `value_if_false`, você precisa... Defina explicitamente o caso oposto., geralmente com um par final VERDADEIRO – resultado padrão, para que a fórmula sempre retorne algo que faça sentido.

Exemplos adicionais de uso avançado de IF e lógica condicional.

Além dos exemplos típicos de notas e comissões, a função SE e suas variantes podem ser combinadas com outras funções do Excel para resolver cenários bastante variados no dia a dia.

Por exemplo, você pode usar SE para Classificar os pontos de dados em dois grupos.Aplicações aprovadas e reprovadas, clientes ativos e inativos, produtos acima ou abaixo da meta de vendas, etc. A estrutura lógica é a mesma; apenas a condição e o texto ou valor retornado mudam.

Você também pode verificar valores em formato de textoPor exemplo, para validar se um livro pertence a um autor específico. A fórmula seria semelhante a: =SE(B2="Stephen King", "Sim", "Não")Isso também se aplica a listas de estados, códigos ou qualquer outro texto que você precise verificar.

Existem casos em que isso faz sentido. Aninhar IF para realizar várias verificações sucessivasUm exemplo típico é o rastreamento de remessas: se o pacote for marcado como enviado, você retorna uma mensagem; caso contrário, verifica se está pronto; se não estiver, indica "pendente" ou um status semelhante.

Além disso, a SI pode ser integrada a outras funções, tais como: OU, E, COMBINAR, CONCATENAR e muitos outros. Um exemplo um pouco mais complexo seria acompanhar o preço de uma ação e exibir um texto indicando se ela sobe ou desce, concatenando a variação percentual, algo como "Subiu 5%" ou "Caiu 3%".

Em combinação com funções de tratamento de erros, como SE.ERRO ou SE.NDVocê pode lidar com situações em que a avaliação é inválida, retornando mensagens personalizadas em vez de códigos de erro pouco amigáveis.

Erros comuns ao trabalhar com IF e lógica condicional

Um dos problemas mais frequentes ao usar a função SE é encontrar dificuldades com a fórmula. retorna um 0 aparentemente sem significadoIsso geralmente ocorre porque você não definiu explicitamente os argumentos `value_if_true` e `value_if_false`, ou porque um ramo retorna vazio enquanto outro é interpretado como um número.

Outro erro clássico é a mensagem #NOME?, o que quase sempre indica que há algo escrito incorretamente na fórmula: o nome da função, texto sem aspas, um separador incorreto ou uma referência a uma função que não existe nessa versão do Excel.

Para evitar isso, é aconselhável Verifique se os textos estão sempre entre aspas.Verifique se o teste lógico possui a sintaxe correta (operadores, parênteses, etc.) e se os nomes das funções correspondem exatamente à versão em sua linguagem.

Ao trabalhar com fórmulas longas ou aninhadas, é altamente recomendável usar o assistente de avaliação de fórmulas Na aba Fórmulas, você encontra o assistente, que permite percorrer a avaliação passo a passo e ver o resultado de cada parte, o que é essencial quando a lógica se torna complexa.

Por fim, mantenha recursos como SE.ERRO ou SE.ND Para encapsular suas fórmulas principais e capturar erros de tempo de execução de forma elegante, exibindo um texto compreensível em vez de um código de erro que possa confundir o usuário.

Dominar todas essas funções de lógica condicional e compreender suas limitações permite criar planilhas muito mais limpas, confiáveis ​​e fáceis de manter, reduzindo o número de problemas ocultos e economizando tempo sempre que precisar atualizar ou expandir seus modelos.