Fórmulas avançadas do Excel (sumário)

  • Introdução às fórmulas avançadas no Excel
  • Exemplos de fórmula avançada no Excel

Introdução às fórmulas avançadas no Excel

Quando você atingir o nível intermediário no Excel, precisará trabalhar duro para avançar para o nível avançado. Para avançar para o nível avançado, você deve estar ciente de algumas das fórmulas avançadas usadas com freqüência. Neste artigo, abordarei as 10 principais fórmulas avançadas que todos os alunos do Excel devem conhecer. Siga este artigo para aprender e explorar.

Exemplos de fórmula avançada no Excel

Vamos entender como usar as fórmulas avançadas no Excel com alguns exemplos.

Você pode baixar este modelo avançado de fórmulas Excel aqui - modelo avançado de fórmulas Excel

Exemplo # 1 - Função VLOOKUP parcial

Você deve ter encontrado uma situação em que o VLOOKUP gera um erro, mesmo se houver uma pequena falha de correspondência no valor da pesquisa. Por exemplo, se você estiver procurando pelo salário do nome Abhishek Sinha e se tiver apenas Abhishek, o VLOOKUP não poderá buscar os dados.

No entanto, usando o caractere asterisco em cada extremidade do valor da pesquisa, podemos buscar os dados para o valor da pesquisa parcial.

Na tabela 1, tenho nome e salário completos, mas na tabela 2 tenho apenas nomes parciais e preciso encontrar o salário de cada funcionário.

Etapa 1: Abra a fórmula VLOOKUP na célula E3. Antes de selecionar o valor da pesquisa, coloque um asterisco (*) em ambos os lados do valor da pesquisa.

Etapa 2: como de costume, você pode concluir a fórmula do VLOOKUP agora e teremos os resultados.

Depois de usar a fórmula acima, a saída é mostrada abaixo.

A mesma fórmula é usada em outras células.

NOTA: Uma limitação aqui é Parcial VLOOKUP retorna o mesmo valor se houver Abhishek Sinha e Abhishek Naidu. Porque aqui o valor parcial comum é Abhishek.

Exemplo # 2 - COUNTIFS com símbolos do operador

Você deve ter usado a função COUNTIF & IFS para contar as coisas da lista. Também podemos contar com base nos símbolos do operador, como maior que (>) menor que (<) e sinal de igual (=).

Agora dê uma olhada nos dados abaixo, por exemplo. Se você deseja contar o número total de faturas da região SUL após a data de 10 de janeiro de 2018, como conta?

Exemplo # 3 - Condição SE com Condições AND & OR

As funções lógicas fazem parte de nossas atividades diárias. Você precisa dominá-los para avançar para o próximo nível. Se você estiver calculando o bônus com base em várias condições, precisará aninhar a condição AND ou OR com a condição SE para concluir o trabalho.

Suponha que você precise calcular o valor do bônus para cada departamento com base no departamento e nos anos de serviço em que você precisa dessas funções. Com base nos critérios abaixo, precisamos calcular o bônus.

Se o serviço for superior a 4 anos e o departamento for um bônus de Vendas ou Suporte, é 50000 ou então, o bônus é 25000.

Aplique a fórmula abaixo para obter o valor do bônus.

Depois de usar a fórmula acima, a saída mostrada abaixo.

Mesma fórmula aplicada na célula E3 a E9.

Exemplo # 4 - Função TEXT para torná-lo dinâmico

Digamos que você esteja mantendo uma tabela de vendas diária e precise atualizá-la todos os dias. No início da tabela, você tem um cabeçalho que diz "Dados de vendas consolidados de DD-MM-AAAA a DD-MM-AAAA". Como e quando a tabela é atualizada, é necessário alterar a data do cabeçalho. Não é uma tarefa frustrante fazer a mesma coisa repetidamente? Podemos dinamizar esse cabeçalho usando as funções TETX, MIN e Max, juntamente com concatenar o símbolo comercial do e comercial (&).

Exemplo # 5 - INDEX + MATCH + MAX para encontrar o vendedor mais alto

Você tem uma lista de vendedores e as vendas que eles fizeram com relação ao nome. Como você sabe quem é o melhor ou o mais alto vendedor da lista? Obviamente, temos várias outras técnicas para determinar o resultado, mas essa função pode devolver o maior vendedor do lote.

Aplique a função abaixo para obter o nome mais alto do vendedor.

Depois de usar a fórmula acima, a saída mostrada abaixo.

Exemplo # 6 - Obtenha uma contagem de valores exclusivos da lista

Se você possui muitos valores duplicados e precisa informar quantos valores exclusivos existem, como você o informa? Você pode perceber removendo o valor duplicado, mas essa não é a maneira dinâmica de determinar a contagem de valores exclusiva.

Usando esta função de matriz, podemos distinguir os valores únicos do lote.

Aplique a fórmula abaixo para obter uma lista de valores exclusivos.

Nota: Esta é uma fórmula de matriz. Você precisa fechá-la pressionando as teclas Shift + Ctrl e pressione a tecla Enter.

Exemplo # 7 - Use intervalo nomeado para tornar o menu suspenso dinâmico

Se você costuma trabalhar com uma lista suspensa e atualizá-la, precisa voltar ao intervalo da lista de fontes para excluir ou adicionar valores. Depois disso, você precisa voltar para as células suspensas e atualizar novamente o intervalo da lista suspensa.

No entanto, se você puder criar um intervalo nomeado para sua lista suspensa, poderá torná-la dinâmica e atualizada.

Crie um intervalo de nomes conforme mostrado na imagem abaixo.

Agora vá para a célula suspensa e abra a caixa de diálogo suspensa.

Na fonte, pressione a tecla F3; ele exibirá todos os nomes definidos; selecione o nome da sua lista suspensa.

Ok, uma lista suspensa está pronta e atualizará os valores automaticamente quando houver uma alteração no intervalo da lista suspensa.

Exemplo # 8 - Livrar-se de valores de erro usando a função IFERROR

Tenho certeza de que você encontrou valores de erro ao trabalhar com os cálculos do VLOOKUP, Division. Lidar com esses valores de erro é uma tarefa tediosa. Mas podemos nos livrar desses valores de erro usando a função IFERROR na fórmula.

Depois de usar a fórmula acima, a saída mostrada abaixo.

Mesma fórmula usada em outras células.

Exemplo # 9 - Use a função PMT para criar seu próprio gráfico EMI

Atualmente, a opção EMI não é uma coisa estranha para todos nós. No Excel, podemos estimar nosso próprio gráfico EMI usando a função PMT. Siga as etapas abaixo para criar seu próprio gráfico.

Aplique a fórmula abaixo na célula B4 para obter o valor EMI.

Exemplo # 10 - INDEX + MATCH como uma alternativa à função VLOOKUP

Espero que você esteja ciente da limitação da função VLOOKUP. Uma das principais limitações é que o VLOOKUP pode buscar os dados da esquerda para a direita e não da direita para a esquerda. Nem sempre os dados estão bem organizados e prontos para uso. Muitas vezes, a coluna de valor que estamos procurando está no lado esquerdo do valor da pesquisa, portanto, o VLOOKUP falha em ajudar nesses casos.

Uma combinação da função INDEX + MATCH serve como uma alternativa à função VLOOKUP.

Depois de usar a fórmula acima, a saída é mostrada abaixo.

A mesma fórmula é usada em outras células.

Com base no ID do produto, precisamos extrair valores de vendas. Na tabela principal, o ID do produto está no lado direito da coluna de vendas. Portanto, o VLOOKUP não pode buscar os dados. Use a fórmula abaixo para buscar os dados.

Artigos recomendados

Este é um guia para fórmulas avançadas no Excel. Aqui discutimos como usar as fórmulas avançadas no Excel, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Como usar a função INDEX do Excel?
  2. Excel avançado | Função de banco de dados
  3. Exemplos na fórmula TRIM no Excel
  4. Guia da fórmula OFFSET do Excel

Categoria: