As 10 funções mais importantes do excel
1. Função SE
A função IF retorna um valor se a condição especificada for TRUE, caso contrário, algum outro valor. Simplificando, a função SE pode primeiro testar uma condição e retornar um valor com base no resultado dessa condição.
Sintaxe
SE(teste_lógico, valor_se_verdadeiro, valor_se_falso)
Argumentos
- teste_lógico: a condição que você deseja avaliar.
- value_if_true: O valor que você deseja obter se esta condição for TRUE.
- value_if_false: O valor que você deseja obter se esta condição for FALSE.
Comentários
- O número máximo de condições aninhadas que você pode executar é 64.
- Você pode usar operadores de comparação para avaliar uma condição.
Exemplo
No exemplo abaixo, usamos um operador de comparação para avaliar diferentes condições.
- Usamos texto específico para obter o resultado se a condição foi atendida ou não.
- Você também pode usar TRUE e FALSE para obter o resultado.
- Se você pular a especificação de um valor para obter o resultado se a condição for TRUE, ele retornará zero.
- E se você pular a especificação de um valor para obter o resultado se a condição for FALSE, ele retornará zero.
No exemplo abaixo, usamos a função IF para criar uma fórmula de aninhamento.
Especificamos uma condição e se essa condição for falsa usamos outro IF para avaliar outra condição e realizar uma tarefa e se essa condição for FALSE usamos outro IF.
Dessa forma, usamos IF cinco vezes para criar uma fórmula de aninhamento. Você pode usar a mesma coisa 64 vezes para uma fórmula de aninhamento.
2. Função SE ERRO
A função IFERROR retorna um valor específico se ocorrer um erro. Em palavras simples, ele pode testar o valor e se esse valor for um erro, ele retornará o valor que você especificou.
Sintaxe
IFERROR(valor, valor_se_erro)
Argumentos
- valor: o valor que você deseja testar quanto a erros.
- value_if_error: O valor que você deseja recuperar quando ocorrer um erro.
Comentários
- A função SEERRO diz respeito à ocorrência de um erro, não ao tipo de erro.
- Se você ignorar o valor ou value_if_error retornará 0 no resultado.
- Pode testar #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? e #NULO!.
- Se você avaliar um array, ele retornará um array de resultados para cada elemento especificado.
Exemplo
No exemplo abaixo, usamos a função IFERROR para substituir #DIV/0! com texto significativo.
IFERROR é compatível apenas com versões 2007 e anteriores. Para resolver esse problema, você pode usar ISERROR.
Função 3.TRUNC
A função TRUNC retorna um número inteiro após truncar o número original. Em termos simples, remove casas decimais de um número com uma precisão específica e depois retorna a parte inteira do resultado.
Sintaxe
TRUNC(número, [número_dígitos])
Argumentos
- número: o número que você deseja truncar.
- [num_digits]: um número para especificar a precisão para truncar um número.
Comentários
- Se você ignorar a especificação de múltiplos, um erro será retornado.
- Ele arredonda do zero.
- Se você tiver dois múltiplos na mesma distância, retornará o múltiplo maior do número que você está arredondando.
Exemplo
No exemplo abaixo, usamos TRUNC para truncar os dados e remover a hora das datas.
4. Função SOMASE
A função SUMIF retorna a soma dos números que atendem à condição especificada . Em termos simples, apenas considera e calcula a soma dos valores que satisfazem a condição.
Sintaxe
SOMASE(intervalo, critérios, [intervalo_soma])
Argumentos
- intervalo: um intervalo de células a partir do qual você deseja verificar os critérios.
- critério: um critério que pode ser um número, um texto, uma expressão, uma referência de célula ou uma função.
- [sum_range]: um intervalo de células contendo os valores que você deseja somar.
Comentários
- Se sum_range for omitido, as células do intervalo serão somadas.
- Certifique-se de usar aspas duplas para especificar critérios de texto ou que incluam símbolos matemáticos, que devem ser colocados entre aspas duplas.
- O tamanho do intervalo de critérios e o intervalo de soma devem ser do mesmo tamanho.
Exemplo
No exemplo abaixo, especificamos A1:A9 como intervalo de critérios e B1:B9 como intervalo de soma e depois especificamos os critérios em A12 que tem o valor C.
Você também pode inserir critérios diretamente na função. No exemplo abaixo, usamos um curinga asterisco para especificar um critério que possui um alfabeto “S”.
E, se você ignorar o intervalo de soma, você obterá a soma do intervalo de critérios. Mas isso só será possível se a faixa de critérios possuir valores numéricos.
5. Função ÍNDICE
A função INDEX retorna um valor de uma lista de valores com base em seu número de índice. Em termos simples, INDEX retorna um valor de uma lista de valores e você precisa especificar a posição desse valor.
Sintaxe
INDEX possui duas sintaxes diferentes. No primeiro você pode usar uma forma de array de um índice para simplesmente obter um valor de uma lista usando sua posição.
ÍNDICE(matriz, núm_linha, [núm_coluna])
No segundo , você pode usar um formulário de patrocínio que é menos usado na vida real, mas pode usá-lo se tiver mais de uma linha para promover.
ÍNDICE(referência, núm_linha, [núm_coluna], [núm_área])
Argumentos
- array: um intervalo de células ou uma constante de array.
- referência: um intervalo de células ou vários intervalos.
- row_number: número da linha da qual você deseja obter o valor.
- [col_number]: O número da coluna da qual você deseja obter o valor.
- [area_number]: Se você estiver se referindo a vários intervalos de células (usando sintaxe de referência), especifique um número para se referir a um intervalo entre todos eles.
Comentários
- Quando os argumentos núm_linha e núm_coluna são especificados, ele retornará o valor na célula na interseção dos dois.
- Se você especificar row_num ou column_num como 0 (zero), ele retornará a matriz de valores para toda a coluna ou linha, respectivamente.
- Quando núm_linha e núm_coluna estiverem fora do intervalo, ele retornará #REF! erro.
- Se area_number for maior que os intervalos de números especificados, ele retornará #REF!.
Exemplo 1 – Usando ARRAY para obter um valor de uma lista
No exemplo abaixo, utilizamos a função INDEX para obter a quantidade do mês de junho. Na lista, Jun está na 6ª posição (6ª linha), por isso especifiquei 6 em row_number. INDEX retornou o valor 1904 no resultado.
E se você estiver se referindo a um intervalo com mais de uma coluna, deverá especificar o número da coluna.
Exemplo 2 – Usando REFERENCE para obter o valor de múltiplas listas
No exemplo abaixo, em vez de selecionar todo o intervalo de uma vez, selecionei em três intervalos diferentes. No último argumento especificamos 2 em area_number que definirá o intervalo a ser usado entre esses três intervalos diferentes.
Agora, na segunda linha, nos referimos à 5ª linha e à 1ª coluna. INDEX retornou o valor 172 que está na 5ª linha da 2ª linha.
6. Função VLOOKUP
A função VLOOKUP procura um valor na primeira coluna de uma tabela e retorna o valor na mesma linha do valor correspondente usando o número do índice. Em termos simples, realiza uma pesquisa vertical.
Sintaxe
PROCV(valor_procurado,matriz_tabela,núm_índice_coluna,pesquisa_intervalo)
Argumentos
- lookup_value: um valor que você deseja procurar em uma coluna. Você pode fazer referência a uma célula que contém o valor de pesquisa ou inserir esse valor diretamente na função.
- table_array: um intervalo de células, um intervalo nomeado a partir do qual você deseja encontrar o valor.
- col_index_num: um número representa o número da coluna da qual você deseja recuperar o valor.
- range_lookup: Use false ou 0 para uma correspondência exata e true ou 1 para uma correspondência adequada. O padrão é verdadeiro.
Comentários
- Se PROCV não encontrar o valor que você está procurando, ele retornará #N/A.
- VLOOKUP só pode fornecer o valor que está à direita do valor de pesquisa. Se quiser olhar do lado direito, você pode usar INDEX e MATCH para isso.
- Se você usar a correspondência exata, ela corresponderá apenas ao primeiro valor da coluna.
- Você também pode usar curingas com VLOOKUP .
- Você pode usar TRUE ou 1 se quiser uma correspondência adequada e FALSE ou 0 para uma correspondência exata.
- Se você usar uma correspondência adequada (True): retornará o próximo menor valor na lista se não houver correspondência exata.
- Se o valor que você procura for menor que o menor valor da lista, VLOOKUP retornará #N/A.
- Se houver um valor exato que você está procurando, ele lhe dará esse valor exato.
- Certifique-se de ter classificado a lista em ordem crescente.
Exemplo
1. Usando VLOOKUP para categorias
No exemplo abaixo, temos uma lista de alunos com as notas que receberam, e na coluna de comentários queremos uma nota com base nas notas deles.
Na lista de marcas acima, gostaríamos de adicionar observações de acordo com a categoria abaixo.
Neste temos duas opções de uso.
A PRIMEIRA é criar uma fórmula de aninhamento com SE, o que leva um pouco de tempo, e a SEGUNDA opção é criar uma fórmula com PROCV com uma correspondência adequada. E a fórmula será:
=PROCV(B2,$E$2:$G$5,3,VERDADEIRO)
Como funciona
Eu uso a coluna “MIN MARKS” para corresponder ao valor de pesquisa e recuperar o valor da coluna “Observações”.
Já mencionei que quando você usa TRUE e não há valor de pesquisa de correspondência exata, ele retornará o próximo menor valor do valor de pesquisa. Por exemplo, quando procuramos o valor 77 na tabela de categorias, 65 é o menor valor depois de 77.
É por isso que pontuamos “Bom” nas observações.
2. Tratamento de erros na função VLOOKUP
Um dos problemas mais comuns que surgem ao usar VLOOKUP é que você receberá #N/A sempre que nenhuma correspondência for encontrada. Mas a solução para este problema é simples e fácil. Deixe-me mostrar um exemplo simples.
No exemplo abaixo temos uma lista de nomes e suas idades e na célula E6 utilizamos a função VLOOKUP para procurar um nome na lista. Cada vez que digito um nome que não está na lista, recebo #N/A.
Mas o que quero aqui é exibir uma mensagem significativa em vez do erro. A fórmula será: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),,”Não encontrado”)
Como funciona : IFNA pode testar um valor para #N/A e se houver um erro você pode especificar um valor no lugar do erro.
7. Função IFNA
A função IFNA retorna um valor específico se ocorrer um erro #N/A. Ao contrário de IFERROR, ele avalia apenas o erro #N/A e retorna o valor que você especificou.
Sintaxe
IFNA(valor, valor_se_na)
Argumentos
- valor: o valor que você deseja testar para erro #N/A.
- value_if_na: O valor que você deseja retornar se ocorrer um erro.
Comentários
- Se você não especificar nenhum argumento, IFNA irá tratá-lo como uma string vazia (“”).
- Se um valor for um array, ele retornará o resultado como um array.
- Irá ignorar todos os outros erros #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? e #NULO!.
Exemplo
Na função VLOOKUP, #N/A ocorre quando o valor de pesquisa não está no intervalo de pesquisa e para isso especificamos uma mensagem significativa usando IFNA.
Nota: O IFNA foi introduzido no Excel 2013, portanto não está disponível nas versões anteriores.
8. Função RAND
A função RAND retorna um número aleatório entre 0 e 1. Em termos simples, você pode gerar um número aleatório entre 0 e 1 (ela atualiza seu valor toda vez que você faz uma alteração na planilha).
Sintaxe
ALEATÓRIO()
Argumentos
- Não há argumentos para especificar nas funções RAND
Comentários
- Se você colocar zero em um múltiplo, retornará zero no resultado.
- Se você ignorar a especificação de múltiplos, um erro será retornado.
- Ele arredonda do zero.
- Se você tiver dois múltiplos na mesma distância, retornará o múltiplo maior do número que você está arredondando.
Exemplo
Além de ter números entre 0 e 1, você também pode usar RAND para números aleatórios entre dois números específicos. No exemplo abaixo, usei-o para criar uma fórmula que gera um número aleatório entre 50 e 100.
Quando você insere esta fórmula em uma célula, ela retorna um número entre 100 e 50 multiplicando os valores retornados pelo RAND pela equação que usamos. Para entender esta fórmula, precisamos dividi-la em três partes:
- Primeiro, quando ele detecta o número mais baixo do número mais alto, você obtém a diferença entre os dois.
- Em seguida, multiplica essa diferença pelo número aleatório retornado após a subtração.
- E terceiro, adicione esse número ao menor número restante na terceira parte da equação.
Relacionado: Como gerar letras aleatórias rapidamente no Excel
9. Função SOMA
A função SUM retorna a soma dos valores fornecidos . Em termos simples, com a função SUM você pode calcular a soma de uma lista de valores (você pode inserir um valor diretamente na função ou referir-se a um intervalo de células.
Sintaxe
SOMA(número1,[número2],…)
Argumentos
- number1 : número, intervalo de células contendo números ou célula única contendo um número.
- [número2] : número, intervalo de células contendo números ou célula única contendo um número.
Comentários
- Ele ignora valores de texto.
Exemplo
No exemplo abaixo, você pode inserir números diretamente na função usando vírgulas entre eles.
Você também pode simplesmente consultar um intervalo para calcular a soma dos números e se houver algum texto, valor lógico ou célula vazia, ele irá ignorá-los.
Se houver um valor de erro em uma célula à qual você está se referindo, ele retornará #N/A no resultado.
Se você tiver valores numéricos formatados como texto, eles serão ignorados. Recomenda-se convertê-los em números antes de usar SUM.
10. Função OU
A função OR retorna um valor booleano (TRUE ou FALSE) após testar as condições que você especifica. Em termos simples, você pode testar múltiplas condições com a função AND e ela retornará TRUE se alguma dessas condições (ou todas) for TRUE e retornará FALSE somente se todas essas condições forem FALSE.
Sintaxe
OU(lógica1, [lógica2],…)
Argumentos
- lógico1: Condição que você deseja verificar.
- [lógico2]: Condições adicionais que você deseja verificar.
Comentários
- Os valores serão ignorados se a célula ou tabela de referência contiver uma célula ou texto vazio.
- O resultado das condições deve ser um valor lógico (TRUE ou FALSE).
- Ele retornará um erro se nenhum valor lógico for retornado.
Exemplo
No exemplo abaixo, criamos uma condição usando a função SE que se um aluno obtiver 60 pontos acima em qualquer uma das duas disciplinas, a fórmula retornará VERDADEIRO.
Agora, no exemplo abaixo, usamos um número para obter valores lógicos em uma fórmula. Você também pode executar a condição acima na ordem inversa.
Você pode usar TRUE e FALSE em vez de números. A função OR trata esses valores lógicos como números.
Mais tutoriais
Funções Estatísticas / Funções de Data / String – Funções de Texto / Funções Financeiras