Função SE – fórmulas aninhadas e evitando armadilhas

Função SE – fórmulas aninhadas e evitando armadilhas

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. As informações foram úteis? Dê sua opinião no final desta página. Aqui está o artigo em inglês para facilitar a referência.

A função SE permite fazer uma comparação lógica entre um valor e o que você espera, testando uma condição e retornando um resultado se for Verdadeiro ou Falso.

  • =SE(Algo for Verdadeiro, então faça algo, caso contrário, faça outra coisa)

Portanto, uma instrução se pode ter dois resultados. O primeiro resultado é se a comparação for verdadeira, a segunda se sua comparação for falsa.

As instruções SE são incrivelmente robustas e compõem a base de muitos modelos de planilha, mas elas também são a causa de muitos problemas de planilha. De forma ideal, uma instrução SE deve se aplicar a condições mínimas, tais como Masculino/Feminino, Sim/Não/Talvez, só para citar alguns, mas, às vezes, é preciso avaliar cenários mais complexos que exigem aninhar* mais de 3 funções SE juntas.

* “Aninhar” se refere à prática de unir várias funções em uma única fórmula.

Use a função SE, uma das funções lógicas, para retornar um valor se uma condição for verdadeira e um outro valor se for falsa.

Sintaxe

SE(teste_lógico, valor_se_verdadeiro, [valor_se_falso])

Por exemplo:

  • =SE(A2>B2,"Acima do orçamento","OK")

  • =SE(A2=B2;B4-A4;"")

Nome do argumento

Descrição

teste_lógico   

(obrigatório)

A condição que você deseja testar.

valor_se_verdadeiro   

(obrigatório)

O valor que você deseja retornar se o resultado do teste_lógico for VERDADEIRO.

valor_se_falso   

(opcional)

O valor que você deseja retornar se o resultado do teste_lógico for FALSO.

Comentários

O Excel permite aninhar até 64 funções SE diferentes, mas não é nada aconselhável fazê-lo. Por quê?

  • Várias instruções SE exigem uma grande quantidade de raciocínio para serem criadas corretamente e para certificar-se de que sua lógica pode calcular corretamente cada condição, do início ao fim. Se você não aninhar sua fórmula de forma 100% precisa, então ela poderá funcionar 75% das vezes, mas retornar resultados inesperados 25% das vezes. Infelizmente, são grandes as chances de cair nos 25%.

  • Várias instruções SE podem se tornar extremamente difíceis de serem mantidas, especialmente quando você retoma a declaração algum tempo depois e tenta descobrir o que você, ou pior, outra pessoa, estava tentando fazer.

Se você se deparar com uma instrução SE que parece continuar crescendo sem parar, é hora de pegar o mouse e repensar sua estratégia.

Vejamos como criar de forma apropriada uma instrução SE aninhada complexa usando vários SEs e como reconhecer quando é hora de usar outra ferramenta no seu arsenal do Excel.

Exemplos

O exemplo a seguir mostra uma instrução SE aninhada relativamente padrão para a conversão da pontuação dos testes dos alunos em resultados equivalente utilizando letras.

Instrução SE aninhada complexa – A fórmula em E2 é =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"))))))))))))
  • =SE(D2>89;"A";SE(D2>79;"B";SE(D2>69;"C";SE(D2>59;"D";"F"))))

    Essa instrução SE aninhada complexa segue uma lógica simples:

  1. Se a Pontuação do teste (na célula D2) for maior que 89, o aluno receberá um A

  2. Se a Pontuação do teste for maior que 79, o aluno receberá um B

  3. Se a Pontuação do teste for maior que 69, o aluno receberá um C

  4. Se a Pontuação do teste for maior que 59, o aluno receberá um D

  5. Nos outros casos, o aluno receberá um F

Esse exemplo específico é relativamente seguro, pois não é provável que a correlação entre as pontuações de teste e as notas de letras mude, portanto, não exigirá muita manutenção. Mas aqui está um pensamento: e se você precisar segmentar as notas entre um +, A e A-(e assim por diante)? Agora sua declaração de quatro condições se precisa ser regravada para ter 12 condições! Esta é a aparência de sua fórmula agora:

  • =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"))))))))))))

Essa fórmula continua precisa e funcionará conforme o esperado, mas ela levará muito tempo para ser escrita e ainda mais tempo para ser testada a fim de verificar se ela faz o que você deseja. Outro problema evidente é que você teve que inserir as pontuações e as letras equivalentes manualmente. Quais são as chances de que haja um erro de digitação acidental? Agora, imagine tentar fazer isso 64 vezes com condições mais complexas! Claro, é possível, mas você quer mesmo ter esse tipo de trabalho e ficar sujeito a possíveis erros que serão muito difíceis de identificar?

Dica: Todas as funções do Excel exigem parênteses de abertura e fechamento (). O Excel tentará ajudar você a descobrir o que vai em cada lugar, colorindo diferentes partes da sua fórmula quando você a estiver editando. Por exemplo, se você fosse editar a fórmula acima, conforme movesse o cursor por cada um dos parênteses finais ")", o parêntese de abertura correspondente ficaria da mesma cor. Isso pode ser especialmente útil em fórmulas complexas aninhadas quando você está tentando descobrir se há parênteses correspondentes o suficiente.

Exemplos adicionais

A seguir está um exemplo muito comum de cálculo da Comissão de vendas com base nos níveis de conquista de Receita.

A fórmula na célula D9 é SE(C9>15000;20%;SE(C9>12500;17,5%;SE(C9>10000;15%;SE(C9>7500;12,5%;SE(C9>5000;10%;0)))))
  • =SE(C9>15000;20%;SE(C9>12500;17,5%;SE(C9>10000;15%;SE(C9>7500;12,5%;SE(C9>5000;10%;0)))))

Essa fórmula diz que SE(C9 for maior que 15.000 deve-se retornar 20%, SE(C9 for maior do que 12.500 deve-se retornar 17,5% e assim por diante...

Embora seja bastante semelhante ao exemplo anterior das Notas, essa fórmula é um ótimo exemplo de como pode ser difícil manter grandes instruções SE. O que você precisaria fazer se a sua organização decidisse adicionar novos níveis de remuneração e, possivelmente, até mesmo, alterar a moeda existente ou os valores das porcentagens? Você teria muito trabalho a fazer!

Dica: Você pode inserir quebras de linha na barra de fórmulas para facilitar a leitura de fórmulas longas. Basta pressionar ALT+ENTER antes do texto que você deseja passar para uma nova linha.

Este é um exemplo do cenário de comissão com a lógica fora de ordem:

A fórmula em D9 está fora de ordem, uma vez que =SE(C9>5000;10%;SE(C9>7500;12,5%;SE(C9>10000;15%;SE(C9>12500;17,5%;SE(C9>15000;20%;0)))))

Você pode ver o que há de errado? Compare a ordem das comparações de receita com o exemplo anterior. Qual é a maneira como esta está indo? É isso mesmo, vai de baixo para cima ($5000 a $15000), não ao contrário. Mas por que isso deve ser tão importante? É muito importante porque a fórmula não pode passar a primeira avaliação para qualquer valor acima de $5000. Digamos que você tenha $12500 em receita, a instrução se retornará 10% porque é maior do que $5000, e ele será interrompido. Isso pode ser incrivelmente problemático porque, em muitos casos, esses tipos de erros ficam innotados até terem um impacto negativo. Portanto, saiba que há algumas armadilhas sérias com instruções IF aninhadas complexas, o que você pode fazer? Na maioria dos casos, você pode usar a função PROCV em vez de criar uma fórmula complexa com a função se. Usando a função PROCV, você primeiro precisa criar uma tabela de referência:

A fórmula na célula D2 é =PROCV(C2;C5:D17;2;VERDADEIRO)
  • =PROCV(C2;C5:D17;2;VERDADEIRO)

Essa fórmula diz para procurar o valor em C2 no intervalo C5: C17. Se o valor for encontrado, retorne o valor correspondente da mesma linha na coluna D.

A fórmula na célula C9 é =PROCV(B9;B2:C6;2;VERDADEIRO)
  • =PROCV(B9;B2:C6;2;VERDADEIRO)

Da mesma forma, esta fórmula procura o valor da célula B9 no intervalo B2:B22. Se o valor for encontrado, então o valor correspondente será retornado na mesma linha na coluna C.

Observação: Essas duas funções PROCVs usam o argumento VERDADEIRO no final das fórmulas, o que significa que queremos que elas localizem uma correspondência aproximada. Em outras palavras, elas farão a correspondência dos valores exatos na tabela de pesquisa, bem como os valores que estiverem entre eles. Nesse caso, as tabelas de pesquisa precisam ser classificadas em ordem Crescente, do menor para o maior.

A função PROCV é abordada com mais detalhes aqui, mas ela é, certamente, muito mais simples do que uma instrução SE aninhada complexa de 12 níveis! Há também outros benefícios menos óbvios:

  • As tabelas de referência da PROCV ficam à mostra e podem ser facilmente vistas.

  • Os valores da tabela podem ser facilmente atualizados e você não precisa tocar na fórmula se suas condições mudarem.

  • Se você não quiser que as pessoas vejam ou interfiram na sua tabela de referência, basta colocá-la em outra planilha.

Você sabia?

Agora há uma função de IFS que pode substituir várias instruções if aninhadas por uma única função. Então, em vez de nosso exemplo de notas iniciais, que tem 4 funções se aninhadas:

  • =SE(D2>89;"A";SE(D2>79;"B";SE(D2>69;"C";SE(D2>59;"D";"F"))))

É possível simplificar com uma única função SES:

  • =SES(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";VERDADEIRO;"F")

A função SES é ótima porque você não precisa se preocupar com todas essas instruções SE e parênteses.

Observação: Este recurso estará disponível apenas se você tiver uma assinatura do Office 365. Se for assinante do Office 365, verifique se tem a versão mais recente do Office.

Experimente o Office 365 ou a versão mais recente do Excel

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Tópicos Relacionados

vídeo: funções
de função de IFS avançadas (Office 365, Excel 2016 e posterior)
a função cont.se contará valores com base em um único critério
a função cont. ses contará valores com base em vários critérios
a função SOMASE somará valores com base em um único critério
a função SOMASES somará valores com base em vários critérios
e funções
ou

Função PROCV visão geral de fórmulas no Excel
como evitar fórmulas
quebradasdetectar erros em fórmulas
funções
lógicas Funções do Excel (ordem alfabética)
funções do Excel (por categoria)

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×