Consylion

IA e Produtividade· Automação

Fórmulas do Excel: as 15 que mais resolvem, com exemplo

"Eu copio a fórmula da internet, ela funciona, e quando dá erro eu não sei o que ela estava perguntando." Se isso soa familiar, o problema não é falta de memória. Ninguém precisa decorar centenas de funções: precisa saber qual pergunta cada uma responde. Neste guia, 15 fórmulas do Excel fazem a maior parte do trabalho, todas sobre a mesma tabela de vendas. Para medir o tempo que a planilha poupa, veja o guia de produtividade; aqui o assunto é acertar a fórmula.

Fórmulas do Excel: o que são e como a fórmula "pergunta"

Uma fórmula do Excel é uma conta escrita numa célula, e toda fórmula começa com o sinal de igual, segundo a Microsoft. Pode ser simples, como =C2*D2, ou usar uma função, como =SOMA(E2:E11). A distinção que eu uso é esta: fórmula é a conta inteira; função é uma conta pronta, com nome e argumentos (os valores que você entrega a ela entre parênteses).

O instinto diz que o caminho é decorar nomes de função. O problema é outro: quem decora o nome não sabe a pergunta que ela responde e não reconhece o erro quando ele aparece. Toda célula com fórmula é uma pergunta feita à planilha. "Quanto a Ana vendeu de camiseta?" já contém a função: é uma soma com duas condições.

A lista abaixo não é um ranking de uso: são as fórmulas que, pelo meu critério, mais resolvem tarefas de quem trabalha com números.

Consylion Insights · a carta das 7h10

Uma ideia por dia para aplicar no negócio e na vida. E 5 guias grátis para começar.

Ao se inscrever, você recebe a carta diária, o acesso aos 5 guias e o contato da Consylion por e-mail e WhatsApp. Para sair, basta um clique. Política de privacidade.

Da pergunta à fórmula, em quatro passos

Passo a passo

  1. Escreva a pergunta em palavras.
  2. Ache a função pelo verbo da pergunta.
  3. Deixe os valores de apoio em células.
  4. Confira o resultado por um segundo caminho.

Escreva a pergunta em palavras. "Quanto cada vendedor vendeu?" é uma pergunta; "SOMASE" não é.

Ache a função pelo verbo. Somar, contar, decidir, buscar e arredondar apontam para famílias diferentes. Somar com condição é SOMASE; contar com condição é CONT.SE; decidir é SE; buscar em outra tabela é PROCV.

Deixe os valores de apoio em células. Taxa, meta e tabela de preços ficam em células, nunca digitados dentro da fórmula. A Microsoft recomenda algo parecido na página do SOMA. A regra é minha: fórmula que não é reescrita não ganha erro novo a cada mudança de taxa.

Confira por um segundo caminho. O total por vendedor e o total por produto têm de dar o mesmo número. Se não dão, uma das fórmulas está errada.

As fórmulas do Excel que mais resolvem, numa tabela de vendas em R$

Imagine uma loja de roupas fictícia, com dez vendas e números redondos. As colunas são Vendedor (A), Produto (B), Quantidade (C), Preço unitário (D) e Total (E, que é =C2*D2). A tabela de preços fica em G2:H4, a taxa de comissão em H6 e a meta de venda em H7.

Exemplo com números

Vendas de uma loja de roupas fictícia

Linha Vendedor Produto Qtd Preço Total
2 Ana Camiseta 4 R$ 50 R$ 200
3 Bruno Calça 2 R$ 120 R$ 240
4 Ana Tênis 1 R$ 300 R$ 300
5 Carla Camiseta 6 R$ 50 R$ 300
6 Bruno Tênis 2 R$ 300 R$ 600
7 Carla Calça 3 R$ 120 R$ 360
8 Ana Calça 5 R$ 120 R$ 600
9 Bruno Camiseta 10 R$ 50 R$ 500
10 Carla Tênis 1 R$ 300 R$ 300
11 Ana Camiseta 2 R$ 50 R$ 100
12 Total 36 R$ 3.500

Números de exemplo fictício. Troque pelos seus.

Com essa tabela, cada pergunta tem uma fórmula e um resultado [cálculo]. As fórmulas usam ponto e vírgula, como o Excel em português do Brasil costuma pedir.

Modelo pronto para copiar

Qual fórmula para qual pergunta

Pergunta Função Fórmula Resultado
Quanto vendemos no total? SOMA =SOMA(E2:E11) R$ 3.500
Qual o valor médio por venda? MÉDIA =MÉDIA(E2:E11) R$ 350
Qual a maior venda? MÁXIMO =MÁXIMO(E2:E11) R$ 600
Qual a menor venda? MÍNIMO =MÍNIMO(E2:E11) R$ 100
Quantas vendas a Ana fez? CONT.SE =CONT.SE(A2:A11;"Ana") 4
Quanto a Ana vendeu? SOMASE =SOMASE(A2:A11;"Ana";E2:E11) R$ 1.200
Quanto a Ana vendeu de camiseta? SOMASES =SOMASES(E2:E11;A2:A11;"Ana";B2:B11;"Camiseta") R$ 300
A venda da linha 2 bateu a meta? SE =SE(E2>=$H$7;"Meta batida";"Abaixo da meta") Abaixo da meta
A linha 6 é do Bruno e vale pelo menos R$ 500? E (dentro do SE) =SE(E(A6="Bruno";E6>=500);"Sim";"Não") Sim
Qual o preço da camiseta na tabela de preços? PROCV =PROCV(B2;$G$2:$H$4;2;FALSO) R$ 50
E se o produto não está na tabela? SEERRO =SEERRO(PROCV("Boné";$G$2:$H$4;2;FALSO);"produto não cadastrado") produto não cadastrado
Quanto vendemos por peça, com 2 casas? ARRED =ARRED(E12/C12;2) R$ 97,22
Qual é a data de hoje? HOJE =HOJE() a data do dia
Como tirar os espaços sobrando de um nome? ARRUMAR =ARRUMAR(" Ana ") Ana
Como juntar vendedor e produto? CONCAT =CONCAT(A2;" - ";B2) Ana - Camiseta

Nas contas de apoio: a média é 3.500 dividido por 10 vendas; o valor por peça é 3.500 dividido por 36, que dá 97,2222 e, em 2 casas, R$ 97,22. No ARRED, E12 e C12 são os totais da linha 12 (R$ 3.500 e 36 peças).

Como cada grupo funciona

Resumir números: SOMA, MÉDIA, MÁXIMO e MÍNIMO

O SOMA soma valores individuais, referências de célula e intervalos, ou uma mistura dos três. Ele ignora o texto do intervalo; já =A1+B1+C1 dá erro se uma dessas células tiver texto, segundo a Microsoft. A MÉDIA ignora texto e células vazias, mas conta as células com zero. O MÁXIMO e o MÍNIMO devolvem 0 se os argumentos não tiverem números.

Para a média de uma parte da tabela, existe o MÉDIASE. A da Ana é =MÉDIASE(A2:A11;"Ana";E2:E11), R$ 300 (1.200 dividido por 4 vendas) [cálculo].

Contar e somar com condição: CONT.SE, SOMASE e SOMASES

O CONT.SE conta as células que atendem a um critério e aceita um só. O SOMASE soma os valores de um intervalo cujas células correspondentes atendem ao critério. Texto e critérios com símbolo, como ">=400", vão entre aspas duplas.

A ordem dos argumentos pega muita gente. No SOMASE, o intervalo a somar vem por último. No SOMASES, que aceita vários critérios, ele vem primeiro, e a Microsoft aceita até 127 pares de intervalo e critério.

Os três cortes fecham no mesmo total [cálculo]:

  • Por vendedor (SOMASE): Ana R$ 1.200 + Bruno R$ 1.340 + Carla R$ 960 = R$ 3.500.
  • Por produto (SOMASE): Camiseta R$ 1.100 + Calça R$ 1.200 + Tênis R$ 1.200 = R$ 3.500.
  • O total da coluna (SOMA): R$ 3.500.

Se um dos cortes desse outro número, o erro estaria na fórmula ou no dado. Critérios, curingas e datas estão no guia do SOMASE.

Decidir: a função SE, com E e OU

O SE devolve um valor quando a condição é verdadeira e outro quando é falsa: SE(teste; valor_se_verdadeiro; valor_se_falso). Texto na resposta vai entre aspas, e a Microsoft avisa que a célula pode mostrar 0 se faltar a resposta de um dos casos.

Com a meta de R$ 400 em H7, copie o SE da tabela pela coluna. Três vendas batem a meta (R$ 600, R$ 600 e R$ 500), somando R$ 1.700 [cálculo]; o =SOMASE(E2:E11;">=400") confirma o mesmo total.

Para duas condições, a função E exige que todas sejam verdadeiras e a função OU aceita que apenas uma seja, e as duas entram no lugar do teste do SE. "Bruno e venda de pelo menos R$ 500" pega 2 vendas (R$ 600 e R$ 500). Trocando o E por OU, "Bruno ou venda de pelo menos R$ 500" pega 4 vendas: as três do Bruno e a calça de R$ 600 da Ana [cálculo].

Buscar em outra tabela: PROCV e SEERRO

O PROCV procura um valor na primeira coluna de uma tabela e devolve o que está numa coluna à direita: PROCV(valor; tabela; coluna; FALSO). O último argumento decide o tipo de busca. A Microsoft diz que a correspondência aproximada é o padrão quando ele fica em branco, e que ela exige a primeira coluna em ordem. Meu critério é um só: PROCV sempre com FALSO, que pede o valor exato. Para o uso completo e o erro #N/D, veja o guia do PROCV.

O SEERRO devolve o valor que você escolher quando a fórmula dá erro, e o resultado normal quando não dá. O cuidado é o que ele esconde: ele trata #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? e #NOME!. A Microsoft orienta a não usar SEERRO para mascarar o #NOME?, porque esse erro indica que a fórmula precisa ser corrigida. Meu uso é restrito: SEERRO em volta de um PROCV, para o item que não existe na tabela, e mais nada.

Acabamento: ARRED, HOJE, ARRUMAR e CONCAT

O ARRED arredonda para o número de casas que você pede. Com 2 casas, 97,2222 vira 97,22. O HOJE não tem argumentos e devolve a data atual, então o resultado muda todo dia. O ARRUMAR tira os espaços sobrando do texto, menos o espaço único entre palavras.

O CONCAT junta textos, mas não coloca separador: o hífen entre vendedor e produto é um argumento seu. A Microsoft informa que ele está disponível no Office 2019 ou numa assinatura do Microsoft 365, e que o CONCATENAR, que ele substitui, continua existindo por compatibilidade. Em versão mais antiga, use o CONCATENAR.

Referência relativa e absoluta: o cifrão trava a célula

Ao copiar uma fórmula, o Excel move as referências junto. A Microsoft descreve quatro formas:

Modelo pronto para copiar

Referências ao copiar uma fórmula

Forma Nome Ao copiar
A1 relativa muda linha e coluna: de =A1 na B2 para =A2 na B3
$A$1 absoluta fica igual: =$A$1 nas duas
$A1 mista, coluna travada a coluna fica; a linha muda
A$1 mista, linha travada a linha fica; a coluna muda

Veja o efeito na comissão de 5%. Ao lado de cada vendedor, em K2:K4, ficam os totais (Ana R$ 1.200, Bruno R$ 1.340, Carla R$ 960), e a taxa está em H6. A fórmula =K2*$H$6 na linha 2 e copiada para baixo vira =K3*$H$6 e =K4*$H$6. Resultado: Ana R$ 60, Bruno R$ 67 e Carla R$ 48, somando R$ 175, que é 5% de R$ 3.500 [cálculo].

Sem o cifrão, =K2*H6 copiada para a linha 3 vira =K3*H7, e H7 é a meta de venda. O Excel multiplica 1.340 por 400 e devolve R$ 536.000 de "comissão" do Bruno [cálculo]. Nenhum aviso aparece: a conta é válida, a pergunta é que ficou errada.

O erro mais comum: a fórmula não funciona

O Excel quase nunca erra. A referência andou, o número está guardado como texto ou o separador está trocado. O quadro reúne o que a Microsoft documenta:

Modelo pronto para copiar

Sintoma, causa e conserto

Sintoma Causa Conserto
#NOME? Nome da função digitado errado, texto sem aspas, nome não definido ou falta de dois-pontos no intervalo Corrija a fórmula; não esconda o erro com SEERRO
#VALOR! Algo errado na fórmula ou nas células usadas: espaços ou texto onde deveria haver número, data guardada como texto Confira as células referenciadas
#REF! A fórmula aponta para uma célula excluída ou coberta por uma colagem; no PROCV, o número da coluna é maior que a tabela Desfaça logo após excluir (Ctrl+Z); use intervalo em vez de células soltas; ajuste o número da coluna
#DIV/0! Divisão por zero ou por célula vazia Garanta que o divisor não é zero, por exemplo com =SE(A3;A2/A3;0)
#N/D O PROCV, o PROCX ou o CORRESP não achou o valor; ou o valor procurado é número e a tabela guarda texto Confira se o valor existe nos dados; só depois use SEERRO
A fórmula aparece como texto Célula formatada como texto ou modo Mostrar Fórmulas ligado Formato Geral (Ctrl+1), depois F2 e Enter; confira o Mostrar Fórmulas
A fórmula não recalcula Cálculo manual ligado Arquivo, Opções, Fórmulas, Cálculo automático
Fórmula quebra com vários argumentos Separador errado: a Microsoft diz que ele varia conforme a localidade e a configuração Troque entre ponto e vírgula e vírgula
#### Coluna estreita demais, que não é erro de fórmula Aumente a largura da coluna

Os exemplos deste guia usam ponto e vírgula, uma escolha do guia para o Excel em português do Brasil; se o seu Excel pedir vírgula, troque, porque a fórmula quebra com o delimitador errado.

Quem trabalha na nuvem encontra o Google Planilhas em guia próprio. E se a tabela pede dezenas de SOMASE, a tabela dinâmica resume os mesmos cortes sem uma fórmula por corte.

Exercício de 15 minutos

Mão na massa · 30 minutos

Três perguntas suas, traduzidas em fórmulas

  • Minutos 0 a 3: abra uma planilha sua com uma coluna de valores e escreva, em palavras, três perguntas sobre ela.
  • Minutos 3 a 10: ache o verbo de cada pergunta, escolha a função na tabela e escreva as fórmulas. Taxas e metas ficam em células com cifrão.
  • Minutos 10 a 15: confira cada resultado por outro caminho, com a conta à mão ou o total por outro corte.
  • Pronto quando: as três respostas batem com a conta feita à mão e, se houver cortes por vendedor e produto, os dois somam o mesmo total.

Hoje, escreva três perguntas sobre uma coluna sua, traduza e confira. Quem aprende a pergunta não depende da fórmula copiada. Para medir o ganho, volte ao guia de produtividade.

Fórmula é pergunta escrita em código; escreva a pergunta antes da fórmula.

Para levar
Fórmula é pergunta escrita em código; escreva a pergunta antes da fórmula.

Padrão Consylion de aconselhamento

Como usar este conselho

De onde vem
A documentação oficial da Microsoft para cada função e para os erros de fórmula, mais o critério da Consylion de escrever a pergunta antes da fórmula, deixar os valores de apoio em células e conferir o resultado por um segundo caminho. As 25 fontes estão listadas no fim do artigo.
Quando se aplica

Quem usa o Excel no trabalho ou no próprio negócio, conhece o SOMA e copia fórmulas da internet sem saber por que funcionam.

Limites
  • O exemplo é fictício, com números redondos. Os resultados são contas feitas em Python a partir da tabela; confira as fórmulas no seu Excel antes de confiar nelas.
  • O nome das funções e o separador de argumentos dependem do idioma e da configuração regional. O texto usa os nomes em português e o ponto e vírgula; se o seu Excel pedir vírgula, troque.
  • CONCAT e PROCX não existem em todas as versões do Excel: confira a ajuda da Microsoft antes de usar em um arquivo compartilhado.
  • Fórmula não corrige dado errado: confira a origem dos números. Para cálculo fiscal, trabalhista ou contábil, procure um contador.
Na prática
Excel e uma planilha sua, em 15 minutos.
Como medir o resultado

Em uma amostra de 5 contas que você faz à mão, compare o resultado da fórmula com a conta conferida por outro caminho. A meta é diferença zero nas cinco.

Quando revisar
  • A Microsoft mudar o nome, a sintaxe ou a disponibilidade de uma função citada.
  • A busca por fórmulas do Excel mudar de formato ou ganhar uma ferramenta dominante.

Última revisão em .

Ver todos os manuais de ia e produtividade na loja →

Próximo passo

Produtividade: o que é e como medir a sua, com exemplo

Produtividade no trabalho: o que é, como medir em entregas por hora, um exemplo de duas semanas com a conta feita e um diário de tempo para copiar.

Ler o próximo →

Perguntas frequentes

Como usar o SE com duas condições?
Coloque a função E ou a função OU dentro do primeiro argumento do SE. Com E, a resposta é verdadeira só se todas as condições forem verdadeiras; com OU, basta uma. Por exemplo, =SE(E(A6="Bruno";E6>=500);"Sim";"Não") só devolve Sim quando a venda é do Bruno e vale pelo menos R$ 500. A Microsoft permite até 255 condições em cada uma.
Qual a diferença entre PROCV e PROCX?
O PROCX é o sucessor do PROCV: tem um argumento próprio para o texto que aparece quando o item não é achado, em vez de depender do SEERRO. A Microsoft informa que ele não está disponível no Excel 2016 e no Excel 2019. Se o arquivo vai circular entre versões, o PROCV com FALSO é a escolha segura.
Edson Oliveira

Quem escreveu

Edson Oliveira

Fundador da Consylion e criador do Protocolo Consylion™

Li mais de 2.000 livros de negócios e reuni nos manuais da Consylion os métodos que uma pequena empresa consegue aplicar. Aqui, escrevo o passo a passo para você usar no seu negócio, com exemplos e a fonte de cada número.

Como produzimos este conteúdo

A pauta parte das dúvidas reais de quem toca uma pequena empresa e dos métodos dos manuais Consylion. O texto é escrito com apoio de inteligência artificial e segue o padrão editorial da Consylion: cada número, regra e citação vem de uma fonte confiável (órgãos públicos, normas, leis e pesquisas com metodologia publicada), aberta e conferida, e está listada no fim do artigo; o que não pode ser verificado não entra. Os exemplos com números são ilustrativos e estão identificados como tal. Quando surgem dados melhores ou as regras mudam, o artigo é revisado. Última revisão em . Leia a política editorial completa.

Compartilhe com quem precisa

WhatsAppLinkedIn

Continue lendo

Próximos artigos para você

Fontes citadas

  1. 01 Microsoft Support, Visão geral de fórmulas no Excel
  2. 02 Microsoft Support, Função SOMA
  3. 03 Microsoft Support, Função MÉDIA
  4. 04 Microsoft Support, Função MÁXIMO
  5. 05 Microsoft Support, Função MÍNIMO
  6. 06 Microsoft Support, Função CONT.SE
  7. 07 Microsoft Support, Função SOMASE
  8. 08 Microsoft Support, Função SOMASES
  9. 09 Microsoft Support, Função MÉDIASE
  10. 10 Microsoft Support, Função SE
  11. 11 Microsoft Support, Função E
  12. 12 Microsoft Support, Função OU
  13. 13 Microsoft Support, Função SEERRO
  14. 14 Microsoft Support, Função PROCV
  15. 15 Microsoft Support, Função PROCX
  16. 16 Microsoft Support, Função ARRED
  17. 17 Microsoft Support, Função HOJE
  18. 18 Microsoft Support, Função ARRUMAR
  19. 19 Microsoft Support, Função CONCAT
  20. 20 Microsoft Support, Como corrigir um erro #NOME?
  21. 21 Microsoft Support, Como corrigir um erro #VALOR!
  22. 22 Microsoft Support, Como corrigir um erro #REF!
  23. 23 Microsoft Support, Como corrigir um erro #DIV/0!
  24. 24 Microsoft Support, Como corrigir um erro #N/D
  25. 25 Microsoft Support, Como evitar fórmulas quebradas