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

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

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

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)

Para que uma instrução IF possa ter dois resultados. O primeiro resultado é se sua comparação for verdadeiro, a segunda se sua comparação for falso.

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

Enquanto o Excel permitirá que você aninhar até 64 funções se diferente, não é recomendável fazer isso. Por quê?

  • Várias instruções se exigem uma grande quantidade de pensamento criem corretamente e certifique-se de que sua lógica pode calcular corretamente por meio de cada condição totalmente até o final. Se você não aninhe sua fórmula 100% com precisão, em seguida, ele pode trabalhar 75% do tempo, mas retornar resultados inesperados 25% do tempo. Infelizmente, as chances de você captura a 25% são fina.

  • Várias instruções se podem se tornar muito difícil de manter, especialmente quando você voltar algum tempo posteriormente e tenta descobrir o que você ou pior alguém, 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

Neste exemplo particular é relativamente seguro porque não é provável que a correlação entre resultados de teste e pontuações de letra serão alterados, para que ele não requer muita manutenção. Mas aqui está um pensamento – o que acontece se você precisar segmento as notas entre A +, A e A - (e assim por diante)? Agora seu demonstrativo de se quatro condição precisa ser reescrito para ter 12 condições! Aqui está como sua fórmula ficaria 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"))))))))))))

É preciso ainda funcionalidade e funciona conforme esperado, mas leva muito tempo para escrever e mais tempo para testar para certificar-se de que ela faz o que você deseja. Outro problema evidente é que você já tinha inserir pontuações e pontuações de letra equivalente à mão. Quais são as chances de que você terá um erro de digitação acidentalmente? Agora, imagine tentar fazer isso 64 vezes com condições mais complexas! Claro, é possível, mas você deseja realmente assunto por conta própria para esse tipo de esforço e erros provável que será realmente difícil especiais?

Dica : Cada função no Excel requer um parênteses de abertura e fechamento (). Excel tentará ajudar você descobrir o que vai onde por colorir diferentes partes da fórmula quando você estiver editando-lo. Por exemplo, se você tivesse editar a fórmula acima, como você mover o cursor após cada um dos parênteses final ")", seu parêntese de abertura correspondente ficará a mesma cor. Isso pode ser especialmente útil em fórmulas aninhadas complexas quando você está tentando descobrir se você tiver bastante parênteses correspondentes.

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...

Enquanto é bastante semelhante à cedo notas exemplo, esta fórmula é um ótimo exemplo de como é difícil pode ser manter instruções IF grandes – o que você precisa fazer se a sua organização decidiu adicionar novos níveis de remuneração e possivelmente até mesmo alterar o valores de porcentagem ou moeda existente? Você teria muito trabalho nas suas mãos!

Dica : Você pode inserir quebras de linha na barra de fórmulas para facilitar a leitura de fórmulas longas. Basta pressione ALT + ENTER antes do texto que você deseja quebrar 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 está errado? Compare a ordem das comparações de receita ao exemplo anterior. Como este será um? Isso está correto, ele vai de baixo para cima (r $5.000 a US $15.000), não o contrário. Mas por que deve ser um grande negócio? É muito importante porque a fórmula não pode passar a primeira avaliação para qualquer valor mais de US $5.000. Digamos que você tem $12,500 em receita – a instrução se retornará 10%, pois é maior que r $5.000 e ele irá parar lá. Isso pode ser muito problemático porque em muitas situações esses tipos de erros vá percebidos até que tinham um impacto negativo. Para que saber que há sejam algumas armadilhas graves com instruções IF aninhadas complexas, 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. Usar PROCV, primeiro é necessário 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 na 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, essa fórmula procura o valor na célula B9 no intervalo B2:B22. Se o valor for encontrado, retorne o valor correspondente na mesma linha na coluna C.

Observação : Ambas essas VLOOKUPs usam o argumento TRUE no final das fórmulas, significando que queremos para procurar uma correspondência de approxiate. Em outras palavras, ele corresponderão os valores exatos na tabela de pesquisa, bem como todos os valores que estejam entre elas. Nesse caso, as tabelas de pesquisa precisam ser classificada em ordem crescente, do menor para o maior.

PROCV é coberta de muito mais detalhes aqui, mas isso sabe é muito mais simples do que uma instrução aninhada se nível de 12, complexa! 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?

Há agora uma função de IFS que pode substituir vários aninhada instruções IF com uma única função. Portanto, em vez de nosso exemplo de notas inicial, que possui 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: Avançada funções se
função IFS (Office 365, o Excel 2016 e posterior)
função CONT.Se o contará valores com base em um único critério
função CONT o contará valores com base em vários critérios
a função SOMASE será somar valores com base em um único critério
função SOMASES o será somar valores com base em vários critérios
função e
função ou
Função PROCV
Visão geral de fórmulas no Excel
como evitar fórmulas quebradas
Usar verificação de erros para detectar erros em fórmulas
funções lógicas
Funções do Excel (ordem alfabéticos)
funções do Excel (por categoria)

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Expanda suas habilidades
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.

×