Pular para o conteúdo

Dicas

Dicas de Excel para o Dia a Dia: 6 Fórmulas que Poupam Tempo

Por Publicado em 7 min de leitura
Neste artigo
  1. Em resumo
  2. Dica 1: troque o SE dentro de SE pela função SES
  3. Dica 2: trave a tabela de regras antes de arrastar
  4. Dica 3: a ordem dos testes importa, e a fronteira também
  5. Dica 4: deixe sempre uma saída para o que nenhum teste pegou
  6. Dica 5: quando a faixa cresce, use uma tabela e o PROCV
  7. Dica 6: use o SEERRO para o erro que você espera
  8. Como usar as seis juntas
  9. Para ver a SES sendo montada

Dica boa de Excel é a que você usa na segunda-feira de manhã, não a que impressiona no vídeo. Reunimos seis, todas de fórmula, para quem já abre planilha no trabalho e quer perder menos tempo com ela. A base é uma aula da Hashtag Treinamentos sobre como trocar o SE dentro de SE, e cada dica foi refeita e testada no Excel em português, versão 365. As dicas 1 e 2 vêm da aula. As de 3 a 6 são nossas: apoiam-se na documentação da Microsoft, citada nas fontes com a data da conferência, e em testes que fizemos.

Se o que você procura são atalhos de teclado, a Hashtag tem uma aula só disso, a “15 atalhos do Excel para economizar horas de trabalho”, no YouTube. Aqui o assunto é fórmula.

Em resumo

  1. Troque SE dentro de SE pela SES quando tiver mais de duas faixas.
  2. Trave a tabela de regras com $ antes de arrastar a fórmula.
  3. Cuide da ordem dos testes: a SES para no primeiro que der verdadeiro.
  4. Deixe sempre uma saída para o valor que nenhum teste pegou.
  5. Faixa que cresce vira tabela e PROCV, em vez de uma fórmula enorme.
  6. Use SEERRO só para o erro que você espera.

Dica 1: troque o SE dentro de SE pela função SES

O exemplo da aula é um registro de estoque. Cada produto tem uma quantidade, e a empresa classifica o estoque em quatro níveis: até 5 mil unidades é o nível 1, o mais perigoso, e a partir dele o produto precisa de reposição; de 5 mil a 30 mil é o nível 2; de 30 mil a 40 mil é o nível 3; acima de 40 mil é o nível 4, o confortável.

Com a função SE, cada faixa nova abre outro SE dentro do “valor se falso” do anterior. Com quatro níveis, são três SE empilhados:

=SE(B2<=5000;1;SE(B2<=30000;2;SE(B2<=40000;3;4)))

Funciona, mas o próprio Fred diz na aula que a fórmula fica grande e confusa. A solução que ele mostra é a função SES, que recebe vários testes na mesma fórmula, cada um seguido do resultado para o caso de ele ser verdadeiro:

C2=SES(B2<=5000;1;B2<=30000;2;B2<=40000;3;B2>40000;4)
LinhaABC
1ProdutoEstoqueNível
2Parafuso3.8001
3Porca12.5002
4Arruela36.0003
5Prego52.0004
A SES testa na ordem: o parafuso, com 3.800 unidades, cai no primeiro teste e vira nível 1. Arrastando a fórmula, o resultado dos quatro produtos é 1, 2, 3 e 4.

As duas fórmulas dão o mesmo resultado nos quatro produtos: testamos lado a lado, linha por linha. A diferença está na leitura. Na SES você enxerga os quatro testes em fila, e para mudar o limite do nível 3 mexe em um número só, sem contar parênteses.

Um aviso sobre versões: a página da Microsoft sobre a SES lista a função a partir do Excel 2019 (e no 2021, no 2024 e no Microsoft 365). Se a sua versão não tiver a SES, o caminho continua sendo o SE dentro de SE.

Dica 2: trave a tabela de regras antes de arrastar

Escrever os limites dentro da fórmula, como fizemos acima, é rápido, mas tem um custo: quando a regra mudar, você precisa editar a fórmula. O melhor é pôr os limites numa tabelinha ao lado e apontar a fórmula para ela. Na aula, é o que o Fred faz, e é aqui que mora uma armadilha.

Ao arrastar uma fórmula para baixo, o Excel desloca as referências junto. Para a quantidade em estoque isso é o que você quer: a linha de baixo deve olhar o estoque de baixo. Para a tabela de regras, não: ela precisa ficar parada. Por isso você trava essas referências com o cifrão, como em $E$2, e a aula usa a tecla F4 para colocar os cifrões em cada uma delas. A Microsoft descreve a diferença na página de visão geral de fórmulas: uma referência absoluta, como $A$1, não se ajusta quando você copia ou preenche a fórmula, e a relativa, como A1, se ajusta.

Veja o que acontece com e sem a trava, usando a mesma tabela de regras na coluna E:

C3=SES(B3<=$E$2;1;B3<=$E$3;2;B3<=$E$4;3;VERDADEIRO;4)
LinhaABCDEF
1ProdutoEstoqueCom $LimiteSem $
2Parafuso3.80015.0001
3Porca12.500230.0001
4Arruela36.000340.0001
5Prego52.00044
Na coluna C os limites estão travados (E2, E3, E4) e o resultado é o certo. Na coluna F, sem o cifrão, a fórmula da porca olha E3, E4 e E5: a porca, com 12.500, sai como nível 1.

Sem a trava, a segunda linha passou a comparar o estoque com os limites das linhas seguintes. A porca (12.500 unidades) e a arruela (36.000) saíram como nível 1, e a fórmula não acusa erro nenhum. É o tipo de erro que só aparece quando alguém desconfia de um número. Fórmula arrastada, tabela fixa: confira os cifrões.

Dica 3: a ordem dos testes importa, e a fronteira também

A SES devolve o resultado do primeiro teste verdadeiro, e a página da Microsoft avisa que as condições precisam ser inseridas na ordem correta. Troque a ordem e o resultado muda, sem erro. Testamos a mesma regra de estoque com a ordem invertida, começando pelo teste do nível 3 (B2<=40000):

Estoque Ordem certa Ordem trocada
3.800 1 3
12.500 2 3
36.000 3 3
52.000 4 4

Na ordem trocada, qualquer valor até 40.000 caiu no primeiro teste e virou nível 3. Por isso nossa recomendação é seguir uma direção só: do menor limite para o maior, ou do maior para o menor, nunca misturado.

Faltou um detalhe: o que acontece em cima da linha? Pela regra da aula, o nível 1 vai “até 5 mil”, e o nível 2 vai “de 5 mil a 30 mil”. Quem tem exatamente 5.000 está em qual? A fórmula usa o sinal de menor ou igual, então 5.000 fica no nível 1, e 5.001 já é nível 2. Testamos os valores de fronteira com a fórmula da dica 1:

Estoque 4.000 5.000 5.001 30.000 30.001 40.000 40.001
Nível 1 1 2 2 3 3 4

Sempre confira os valores de fronteira depois de montar uma regra por faixas. Se a regra do seu trabalho diz “a partir de 5.000”, o sinal certo é outro, e só o teste mostra.

Dica 4: deixe sempre uma saída para o que nenhum teste pegou

E se o estoque for maior que o último limite e você não escreveu um teste para isso? A página da Microsoft é direta: se nenhuma condição verdadeira for encontrada, a SES retorna o erro #N/D. Testamos com 60.000 numa SES que parava no teste de 40.000: o resultado foi #N/D.

A correção que a própria página sugere é usar VERDADEIRO como último teste, que sempre é verdadeiro e funciona como o “caso contrário”:

=SES(B2<=5000;1;B2<=30000;2;B2<=40000;3;VERDADEIRO;4)

Testada com 60.000, essa versão devolve 4, igual à fórmula da aula, que usa B2>40000 como último teste. As duas escolhas se comportam igual quando alguém digita texto no lugar do número: nos nossos testes, a célula com “abc” virou nível 4 nas duas versões, sem erro nenhum, e a célula vazia virou nível 1. Nenhuma das duas avisa que o dado está errado, então confira a coluna de estoque antes de confiar no nível.

Dica 5: quando a faixa cresce, use uma tabela e o PROCV

A SES é ótima para três ou quatro faixas. Com dez, a fórmula volta a ser um paredão. Para regras que crescem em ordem, como desconto por quantidade, a alternativa é uma tabela de faixas e o PROCV em correspondência aproximada.

Um exemplo com números fictícios: quem compra de 1 a 9 unidades não tem desconto, a partir de 10 tem 5%, a partir de 50, 10% e a partir de 100, 15%.

C4=PROCV(B4;$E$2:$F$5;2;VERDADEIRO)
LinhaABCDEF
1PedidoQuantidadeDescontoA partir deDesconto
2Pedido A80%10%
3Pedido B105%105%
4Pedido C6410%5010%
5Pedido D12015%10015%
Com VERDADEIRO no último argumento, o PROCV procura o maior valor da coluna E que não passa da quantidade. 64 unidades caem na faixa de 50, e o desconto é 10%.

Quando a regra mudar, você muda a tabela, e a fórmula continua igual. Comparamos o PROCV com uma SES equivalente (a de quatro testes, de B>=100 até B>=10, com VERDADEIRO no fim) nas quantidades 1, 9, 10, 49, 50, 99, 100 e 500, e os resultados bateram em todas.

A página do PROCV traz dois cuidados, e nós testamos os dois:

  • A primeira coluna precisa estar em ordem crescente. A Microsoft diz que, fora de ordem, o valor retornado pode ser inesperado. Embaralhamos as linhas da nossa tabela de faixas, e 64 unidades passaram a render 5%, em vez de 10%.
  • Abaixo da menor faixa, o resultado é #N/D. A página avisa que, com correspondência aproximada, um valor menor que o menor da primeira coluna dá esse erro. Na nossa tabela, a quantidade 0 deu #N/D.

Dica 6: use o SEERRO para o erro que você espera

Busca com PROCV em correspondência exata devolve #N/D quando o código não existe. Isso é normal, mas não é bonito num relatório. A função SEERRO resolve: se o primeiro argumento der erro, ela devolve o texto que você escolher.

B3=SEERRO(PROCV(A3;$D$2:$E$4;2;FALSO);"Código não encontrado")
LinhaABCDE
1CódigoPreçoTabelaPreço
2P-2103,50P-10418,90
3P-999Código não encontradoP-2103,50
4P-305129,00
O código P-999 não existe na tabela: sem o SEERRO a célula mostraria #N/D, e com ele mostra o texto combinado.

Tem um porém, e ele importa. A página da Microsoft lista os erros que o SEERRO avalia, e são todos os do Excel: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? e #NULO! (a página em português escreve #NOME! no último, mas no Excel ele aparece como #NULO!). Ou seja, ele esconde todos, e não só o “não encontrado”. Testamos duas situações. Na primeira, escrevemos o nome da função errado dentro do SEERRO (PROCVV, com um V a mais): a célula mostrou “Código não encontrado”, e o erro de digitação passou invisível. Na segunda, pedimos a coluna 3 numa tabela de duas colunas, o que sozinho dá #REF!: de novo, o texto de “não encontrado”.

A dica, então, é montar a fórmula sem o SEERRO, conferir que ela acerta, e só então envolver. Assim o que ele esconde é o erro que você previu, e não um engano seu.

Como usar as seis juntas

Uma rotina simples, em quatro passos, para quando aparecer uma regra por faixas na sua planilha:

  1. Escreva a regra num papel ou numa tabelinha, com os limites e o que cada faixa devolve.
  2. Escolha a ferramenta pelo tamanho: até quatro ou cinco faixas, a SES; mais do que isso, ou regra que muda toda hora, tabela e PROCV.
  3. Trave o que não pode andar e arraste.
  4. Teste os valores de fronteira e um valor fora de todas as faixas. Só depois, se preciso, envolva com SEERRO.

Para ver a SES sendo montada

A aula do Fred monta a SES do zero com o mesmo exemplo de estoque e mostra o momento em que ele trava as referências com o F4. O vídeo está logo abaixo deste texto: vale assistir uma vez de ponta a ponta e depois refazer na sua planilha.

Para passar de dicas soltas a uma base completa, a Hashtag Treinamentos reúne as aulas gratuitas por nível no melhor curso de Excel dela. As aulas gratuitas não emitem certificado. Quem quer o caminho inteiro, do básico ao avançado, com suporte a dúvidas e certificado de carga horária, tem o Excel Impressionador, que avaliamos como o melhor curso de Excel pago.

Assista à aula

Miniatura do vídeo: PARE de usar função SE agrupado no Excel! Use essas no lugar

Perguntas frequentes

Quais são as melhores dicas de Excel para quem está começando?

Comece pelas de fórmula que evitam retrabalho: trave com o cifrão o que não pode andar quando você arrasta, troque SE dentro de SE pela função SES quando houver mais de duas faixas e teste sempre os valores de fronteira, como 5.000 e 5.001, antes de confiar no resultado.

Em quais versões do Excel existe a função SES?

A página de suporte da Microsoft lista a SES a partir do Excel 2019, além do Excel 2021, do 2024 e do Microsoft 365. Se a sua versão não tiver a SES, o caminho é o SE dentro de SE ou uma tabela de faixas com PROCV.

Qual a diferença entre a função SE e a função SES?

O SE faz um teste e tem dois caminhos. Para mais de dois caminhos você coloca um SE dentro do outro. A SES recebe vários pares de teste e resultado na mesma fórmula e devolve o resultado do primeiro teste verdadeiro, sem aninhar nada.

Como fazer o Excel não mostrar #N/D numa busca?

Envolva a busca com SEERRO e escreva o texto que deve aparecer quando nada for encontrado, como =SEERRO(PROCV(A2;D2:E4;2;FALSO);"Código não encontrado"). Use só quando o erro for esperado, porque o SEERRO também esconde erros de digitação na fórmula.

Continue lendo