Fórmulas complexas no Excel (Sumário)
- Introdução às fórmulas complexas no Excel
- Escolha Função servir como função de pesquisa
- VLOOKUP com ESCOLHA
- SE Com Função AND & OR
Introdução às fórmulas complexas no Excel
O Excel vem com muitas fórmulas úteis, temos uma categoria financeira, lógica, texto, data e hora, pesquisa e referências e matemática e trigonometria ”. Cada fórmula individual é boa o suficiente para usuários intermediários, onde eles enfrentam situações diretas, mas à medida que você avança para o próximo nível, sua habilidade precisa elevar; portanto, a aplicação de fórmulas mais complexas no Excel desempenha um papel vital na resolução de situações complexas. Tendo isso em mente, este artigo fornecerá algumas das fórmulas complexas importantes e necessárias. Leia.
Escolha Função servir como função de pesquisa
Muitos de vocês não usaram a função ESCOLHER, mas isso é muito útil para servir também como função de pesquisa.
Você pode fazer o download deste modelo de Excel de fórmulas complexas aqui - Modelo de Excel de fórmulas complexas Por exemplo, veja os dados abaixo.
Temos dados de clientes mensalmente sob esta tabela, temos cada tabela de clientes. Nesta tabela, na célula B13, criei a lista suspensa do mês, para que, quando essa lista suspensa for alterada, obtenhamos o total do mês selecionado.
Crie uma tabela como a abaixo.
Aplique VLOOKUP à célula D13.
Agora abra a função CHOOSE na célula B14.
Selecione o número INDEX como uma célula VLOOKUP e faça uma referência absoluta.
Agora selecione para cada argumento os números de vendas de cada mês.
Feche o suporte e pressione a tecla Enter.
Agora copie e cole a fórmula nas células abaixo também.
Agora a opção ESCOLHER mostrará os números do mês selecionado.
VLOOKUP com função ESCOLHER
Vimos a tradição VLOOKUP, mas também podemos combiná-la com a função ESCOLHER. Por exemplo, veja os dados abaixo.
Nas células A1 a B11, temos uma tabela de vendas e na célula D2 eu criei a lista suspensa de meses e na célula E2 precisamos aplicar o VLOOKUP e obter o valor de vendas do mês selecionado. Mas o problema de aplicar o VLOOKUP é uma estrutura de dados, em que o valor da pesquisa fica à direita da coluna de resultados, portanto, o VLOOKUP não pode buscar os dados da direita para a esquerda; nesse caso, podemos usar a função CHOOSE.
Para selecionar a matriz da tabela, precisamos abrir a função CHOOSE aqui.
No argumento Número INDEX em flor, os colchetes inserem 1 e 2. Agora selecione o primeiro intervalo como coluna Mês.
Selecione a coluna Vendas como o segundo intervalo.
Mencione o número do índice da coluna como 2 e a pesquisa de intervalo como 0.
Portanto, obtivemos o resultado do mês de abril da tabela. Portanto, sempre que você não tiver uma estrutura de dados precisa, podemos selecionar a função ESCOLHER.
SE Com Função AND & OR
As funções lógicas são cheias de lógica para calcular as coisas. Veja os dados de exemplo abaixo.
Na tabela acima, precisamos calcular o bônus com base nas condições abaixo.
- Se o Serviço tiver mais de 1, 5 anos e se o departamento for Vendas ou Suporte, o Bônus será 1000.
- Se qualquer outra coisa, o bônus é 3000.
Para calcular o bônus, precisamos aplicar IF, AND e & OR juntos. Abra a condição IF na célula D2.
Precisamos testar várias condições, abrir a função AND.
Agora, a próxima lógica é Vendas ou Suporte, portanto, para essa condição OR aberta.
Teste se o título é Vendas ou Suporte.
Portanto, se todas as condições fornecidas forem VERDADEIRAS, o bônus deverá ser 10000.
Se os testes lógicos fornecidos forem FALSE, o bônus será 3000.
Feche o suporte e pressione a tecla Enter, devemos obter o resultado.
Copie e cole a fórmula nas células abaixo.
Então, de acordo com a condição, obtivemos o resultado. Se o serviço for> 1, 5 anos e o título for Vendas ou Suporte, obteremos 10000 ou 3000.
Fórmula complexa com caracteres curinga
O VLOOKUP exige que um valor exato de pesquisa seja correspondido para buscar os dados. Esse é o slogan tradicional, mas ainda podemos buscar os dados usando o valor de pesquisa de partículas. Por exemplo, se o valor da pesquisa for "VIVO" e na tabela principal se for "VIVO Mobile", ainda poderemos corresponder usando caracteres curinga. Veremos um dos exemplos agora, abaixo estão os dados do exemplo.
Temos tabela de pesquisa na coluna A. Na coluna C, temos valores de pesquisa, esses valores de pesquisa não são exatamente iguais aos valores da tabela de pesquisa. Então, veremos como aplicar o VLOOKUP usando curingas.
Primeiro, abra a função VLOOKUP na célula D1.
O primeiro argumento é o valor da pesquisa . Um dos problemas com o valor da pesquisa aqui é que não temos uma correspondência exata, portanto, precisamos anexar esse valor da pesquisa com um asterisco antes e depois do valor da pesquisa.
Aqui, aplicamos dois caracteres de asterisco “*” e C2 e ”*”. Aqui, o asterisco indica que qualquer coisa entre o caractere curinga deve ser correspondida e retornar o resultado relacionado.
Mesmo que tivéssemos apenas o caractere de asterisco "Infosys" correspondendo ao valor na tabela de pesquisa e retornado o resultado exato como "Infosys Ltd".
Da mesma forma, na célula D6, obtivemos o valor do erro como #VALUE! porque não há palavra "Mintra" na tabela de pesquisa.
Artigos recomendados
Este é um guia para fórmulas complexas no Excel. Aqui discutimos algumas das fórmulas complexas importantes e necessárias com exemplos e modelo do Excel. Você também pode consultar nossos outros artigos sugeridos para saber mais -
- Fórmula de PESQUISA no Excel (dicas e truques)
- Tutoriais sobre a função NORMSINV do Excel
- Caixa de nome e seus usos no Excel
- Concatenar seqüências de caracteres no Excel