Fórmulas e funções do Excel: aprenda com EXEMPLOS básicos

Fórmulas e funções são os blocos de construção do trabalho com dados numéricos no Excel. Este artigo apresenta fórmulas e funções.

Dados de tutoriais

Para este tutorial, trabalharemos com os seguintes conjuntos de dados.

Orçamento de materiais domésticos

S / N ITEM QTY PREÇO SUBTOTAL É acessível?
1 Mangas 9 600
2 Laranjas 3 1200
3 Tomates 1 2500
4 Óleo de cozinha 5 6500
5 Água tônica 13 3900

Cronograma do projeto de construção de casas

S / N ITEM DATA DE INÍCIO DATA FINAL DURAÇÃO (DIAS)
1 Pesquisar terreno 04/02/2015 07/02/2015
2 Deitar Foundation 10/02/2015 15/02/2015
3 Cobertura 27/02/2015 03/03/2015
4 Pintura 09/03/2015 21/03/2015

O que são fórmulas no Excel?

FÓRMULAS NO EXCEL é uma expressão que opera em valores em um intervalo de endereços de células e operadores. Por exemplo, =A1+A2+A3, que encontra a soma do intervalo de valores da célula A1 à célula A3. Um exemplo de fórmula composta de valores discretos como =6*3.

=A2 * D2 / 2

AQUI,

  • "=" informa ao Excel que esta é uma fórmula e deve avaliá-la.
  • "A2" * D2" faz referência aos endereços das células A2 e D2 e ​​depois multiplica os valores encontrados nesses endereços das células.
  • "/" é o operador aritmético de divisão
  • "2" é um valor discreto

Exercício prático de fórmulas

Trabalharemos com os dados amostrais do orçamento doméstico para calcular o subtotal.

  • Crie uma nova pasta de trabalho no Excel
  • Insira os dados mostrados no orçamento de materiais domésticos acima.
  • Sua planilha deve ter a seguinte aparência.

Exercício Prático de Fórmulas

Vamos agora escrever a fórmula que calcula o subtotal

Defina o foco para a célula E4

Insira a seguinte fórmula.

=C4*D4

AQUI,

  • "C4*D4" usa a multiplicação do operador aritmético (*) para multiplicar o valor do endereço da célula C4 e D4.

Pressione a tecla Enter

Você obterá o seguinte resultado

Exercício Prático de Fórmulas

A imagem animada a seguir mostra como selecionar automaticamente o endereço da célula e aplicar a mesma fórmula a outras linhas.

Exercício Prático de Fórmulas

Erros a evitar ao trabalhar com fórmulas no Excel

  1. Lembre-se das regras de Brackets de Divisão, Multiplicação, Adição e Subtração (BODMAS). Isso significa que as expressões entre colchetes são avaliadas primeiro. Para operadores aritméticos, a divisão é avaliada primeiro, seguida pela multiplicação, depois a adição e a subtração são as últimas a serem avaliadas. Usando esta regra, podemos reescrever a fórmula acima como =(A2 * D2) / 2. Isso garantirá que A2 e D2 sejam primeiro avaliados e depois divididos por dois.
  2. As fórmulas de planilha do Excel geralmente funcionam com dados numéricos; você pode aproveitar a validação de dados para especificar o tipo de dados que devem ser aceitos por uma célula, ou seja, apenas números.
  3. Para garantir que está trabalhando com os endereços de células corretos referenciados nas fórmulas, você pode pressionar F2 no teclado. Isso destacará os endereços das células usados ​​​​na fórmula e você poderá verificar para garantir que sejam os endereços das células desejados.
  4. Ao trabalhar com muitas linhas, você pode usar números de série para todas as linhas e ter uma contagem de registros na parte inferior da planilha. Você deve comparar a contagem do número de série com o total do registro para garantir que suas fórmulas incluam todas as linhas.

Confira
As 10 principais fórmulas de planilha do Excel

O que é função no Excel?

FUNÇÃO NO EXCEL é uma fórmula predefinida usada para valores específicos em uma ordem específica. A função é usada para tarefas rápidas, como encontrar a soma, contagem, média, valor máximo e valores mínimos para um intervalo de células. Por exemplo, a célula A3 abaixo contém a função SUM que calcula a soma do intervalo A1:A2.

  • SOMA para soma de um intervalo de números
  • MÉDIA para calcular a média de um determinado intervalo de números
  • CONTAGEM para contar o número de itens em um determinado intervalo

A importância das funções

Funções aumentam a produtividade do usuário ao trabalhar com Excel. Digamos que você queira obter o total geral do orçamento de suprimentos domésticos acima. Para simplificar, você pode usar uma fórmula para obter o total geral. Usando uma fórmula, você teria que referenciar as células E4 a E8, uma por uma. Você teria que usar a seguinte fórmula.

= E4 + E5 + E6 + E7 + E8

Com uma função, você escreveria a fórmula acima como

=SUM (E4:E8)

Como você pode ver na função acima usada para obter a soma de um intervalo de células, é muito mais eficiente usar uma função para obter a soma do que usar a fórmula que terá que fazer referência a muitas células.

Funções comuns

Vejamos algumas das funções mais comumente usadas nas fórmulas do MS Excel. Começaremos com funções estatísticas.

S / N FUNÇÃO CATEGORIA DESCRIÇÃO USO
01 SOMA Matemática e Trig Adiciona todos os valores em um intervalo de células = SUM (E4: E8)
02 MIN Estatístico Encontra o valor mínimo em um intervalo de células =MIN(E4:E8)
03 MAX Estatístico Encontra o valor máximo em um intervalo de células =MÁX(E4:E8)
04 MÉDIA Estatístico Calcula o valor médio em um intervalo de células = MÉDIA (E4: E8)
05 CONTAGEM Estatístico Conta o número de células em um intervalo de células = COUNT (E4: E8)
06 LEN Texto Retorna o número de caracteres em uma string de texto = LEN (B7)
07 SUMIF Matemática e Trig Adiciona todos os valores em um intervalo de células que atendem a critérios especificados.
=SOMASE(intervalo,critérios,[intervalo_soma])
=SUMIF(D4:D8,”>=1000″,C4:C8)
08 AVERAGEIF Estatístico Calcula o valor médio em um intervalo de células que atendem aos critérios especificados.
= AVERAGEIF (intervalo, critério, [intervalo_ médio])
=MÉDIASE(F4:F8,”Sim”,E4:E8)
09 DIAS Data e Hora Retorna o número de dias entre duas datas =DIAS(D4,C4)
10 Estamos Data e Hora Retorna a data e hora atuais do sistema = AGORA ()

Funções Numéricas

Como o nome sugere, essas funções operam com dados numéricos. A tabela a seguir mostra algumas das funções numéricas comuns.

S / N FUNÇÃO CATEGORIA DESCRIÇÃO USO
1 ÉNÚMERO Dados Pessoais Retorna True se o valor fornecido for numérico e False se não for numérico = ISNUMBER (A3)
2 RAND Matemática e Trig Gera um número aleatório entre 0 e 1 = RAND ()
3 ROUND Matemática e Trig Arredonda um valor decimal para o número especificado de casas decimais = ROUND (3.14455,2)
4 MEDIAN Estatístico Retorna o número no meio do conjunto de números fornecidos = MEDIAN (3,4,5,2,5)
5 PI Matemática e Trig Retorna o valor da função matemática PI(π) =PI()
6 PODER Matemática e Trig Retorna o resultado de um número elevado a uma potência.
POTÊNCIA(número, potência)
=PODER(2,4)
7 MOD Matemática e Trig Retorna o resto quando você divide dois números =MOD(10,3)
8 ROMAN Matemática e Trig Converte um número em algarismos romanos =ROMANO(1984)

Funções de string

Essas funções básicas do Excel são usadas para manipular dados de texto. A tabela a seguir mostra algumas das funções de string comuns.

S / N FUNÇÃO CATEGORIA DESCRIÇÃO USO COMO
1 ESQUERDA Texto Retorna um número de caracteres especificados desde o início (lado esquerdo) de uma string =ESQUERDA(“GURU99”,4) Deixou 4 caracteres de “GURU99”
2 DIREITO Texto Retorna um número de caracteres especificados do final (lado direito) de uma string =DIREITA(“GURU99”,2) À direita 2 caracteres de “GURU99”
3 MID Texto Recupera um número de caracteres do meio de uma string de uma posição inicial e comprimento especificados.
=MID (texto, num_inicial, num_chars)
=MÉDIO(“GURU99”,2,3) Recuperando caracteres 2 a 5
4 ISTEXTO Dados Pessoais Retorna True se o parâmetro fornecido for Texto =ÉTEXTO(valor) valor - o valor a ser verificado.
5 ENCONTRAR Texto Retorna a posição inicial de uma sequência de texto dentro de outra sequência de texto. Esta função diferencia maiúsculas de minúsculas.
= ENCONTRAR (encontrar_texto, dentro do_texto, [núm_inicial])
= ENCONTRAR(“oo”,”Telhado”,1) Encontre oo em “Telhados”, o resultado é 2
6 SUBSTITUIR Texto Substitui parte de uma string por outra string especificada.
= SUBSTITUIR (texto_antigo, num_inicial, num_chars, novo_texto)
=SUBSTITUIR(“Telhado”,2,2,”xx”) Substitua “oo” por “xx”

Funções de data e hora

Essas funções são usadas para manipular valores de data. A tabela a seguir mostra algumas das funções de data comuns

S / N FUNÇÃO CATEGORIA DESCRIÇÃO USO
1 INFORMAÇÃO Data e Hora Retorna o número que representa a data no código Excel = DATA (2015,2,4)
2 DIAS Data e Hora Encontre o número de dias entre duas datas =DIAS(D6,C6)
3 MÊS Data e Hora Retorna o mês a partir de um valor de data =MÊS(“4/2/2015”)
4 MINUTO Data e Hora Retorna os minutos de um valor de tempo =MINUTO(“12:31”)
5 ANO Data e Hora Retorna o ano a partir de um valor de data =ANO(“04/02/2015”)

Função VLOOKUP

O Função VLOOKUP é usado para realizar uma pesquisa vertical na coluna mais à esquerda e retornar um valor na mesma linha de uma coluna que você especificar. Vamos explicar isso em linguagem leiga. O orçamento de suprimentos domésticos possui uma coluna de número de série que identifica exclusivamente cada item do orçamento. Suponha que você tenha o número de série do item e queira saber a descrição do item, você pode usar a função VLOOKUP. Aqui está como a função VLOOKUP funcionaria.

Função VLOOKUP

=VLOOKUP (C12, A4:B8, 2, FALSE)

AQUI,

  • "=VLOOKUP" chama a função de pesquisa vertical
  • "C12" especifica o valor a ser procurado na coluna mais à esquerda
  • "A4:B8" especifica a matriz da tabela com os dados
  • "2" especifica o número da coluna com o valor da linha a ser retornado pela função VLOOKUP
  • "FALSE," informa à função VLOOKUP que estamos procurando uma correspondência exata do valor de pesquisa fornecido

A imagem animada abaixo mostra isso em ação

Função VLOOKUP

Baixe o código Excel acima

Resumo

O Excel permite manipular os dados usando fórmulas e/ou funções. As funções são geralmente mais produtivas em comparação com a escrita de fórmulas. As funções também são mais precisas em comparação com as fórmulas porque a margem de erro é mínima.

Aqui está uma lista de fórmulas e funções importantes do Excel

  • Função SOMA = =SUM(E4:E8)
  • Função MÍN = =MIN(E4:E8)
  • Função MÁX = =MAX(E4:E8)
  • Função MÉDIA = =AVERAGE(E4:E8)
  • Função CONTAGEM = =COUNT(E4:E8)
  • Função DIAS = =DAYS(D4,C4)
  • Função PROCV = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Função DATA = =DATE(2020,2,4)