Fórmulas Excel avançadas

Lista das 10 principais fórmulas e funções avançadas do Excel

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

# 1 - Fórmula VLOOKUP no Excel

Esta função avançada do Excel é uma das fórmulas mais usadas no Excel. É principalmente devido à simplicidade desta fórmula e sua aplicação na procura de um determinado valor de outras tabelas que tem uma variável comum entre essas tabelas. Suponha que você tenha duas tabelas com detalhes sobre o salário e o nome do funcionário de uma empresa, sendo o ID do funcionário a coluna principal. Você deseja obter o salário da Tabela B na Tabela A.

Você pode usar PROCV conforme abaixo.

Isso resultará na tabela abaixo quando aplicamos esta fórmula avançada do Excel em outras células da coluna Salário do funcionário.

Arraste a fórmula para o restante das células.

Existem três delimitações principais de VLOOKUP:

1. Você não pode ter uma coluna primária à direita da coluna para a qual deseja preencher o valor de outra tabela. Nesse caso, a coluna Salário do funcionário não pode ser anterior à ID do funcionário.
2. No caso de valores duplicados na coluna primária da Tabela B, o primeiro valor será preenchido na célula.
3. Se você inserir uma nova coluna no banco de dados (por exemplo, inserir uma nova coluna antes do Salário do funcionário na Tabela B), a saída da fórmula pode ser diferente com base em uma posição que você mencionou na fórmula (No caso acima, a saída estaria em branco)

# 2 - Fórmula INDEX no Excel

Esta fórmula avançada do Excel é usada para obter o valor de uma célula em uma determinada tabela, especificando o número de linhas, coluna ou ambos. Ex. Para obter o nome de um funcionário na 5ª observação, Abaixo estão os dados.

Podemos usar a fórmula avançada do Excel conforme abaixo:

A mesma fórmula INDEX pode ser usada para obter o valor ao longo da linha. Ao usar o número da linha e da coluna, a sintaxe seria semelhante a esta:

A fórmula acima retornaria “Rajesh Ved”.

Observação: se você inserir outra linha nos dados da 5ª linha, a fórmula retornará “Chandan Kale”. Conseqüentemente, a saída dependeria de quaisquer mudanças que ocorressem na tabela de dados ao longo do tempo.

# 3 - Fórmula de CORRESPONDÊNCIA no Excel

Esta fórmula avançada do Excel retorna o número da linha ou coluna quando há uma correspondência de certa string ou número no intervalo fornecido. No exemplo abaixo, estamos tentando encontrar a posição de “Rajesh Ved” na coluna Nome do funcionário.

A Fórmula seria a seguinte:

A função MATCH retornaria 5 como o valor.

O terceiro argumento é usado para a correspondência exata. Você também pode usar +1 e -1 com base em seus requisitos.

Observação: é possível combinar INDEX e MATCH para superar a limitação de VLOOKUP.

# 4 - SE E Fórmula no Excel

Existem muitos casos em que é necessário criar sinalizadores com base em algumas restrições. Todos nós estamos familiarizados com a sintaxe básica do IF. Usamos esta função avançada do Excel IF para criar um novo campo com base em alguma restrição em um campo já existente. Mas e se precisarmos usar várias colunas em consideração ao criar um sinalizador. Ex. No caso abaixo, queremos sinalizar todos os funcionários cujo salário é maior que 50 mil, mas o ID do funcionário é maior que 3.

Usaríamos IF AND em tais casos. Veja abaixo a imagem do mesmo.

Ele retornaria o resultado como 0.

Podemos ter muitas condições ou restrições para criar um sinalizador com base em várias colunas usando AND.

# 5 - SE OU Fórmula no Excel

Da mesma forma, também podemos usar a função OR no Excel em vez de AND se apenas precisarmos satisfazer uma das condições de muitos.

Nos casos acima, se qualquer uma das condições for satisfeita, teremos a célula preenchida como 1 else 0. Podemos também substituir 1 ou 0 por algumas substrings com aspas duplas (“”).

# 6 - Fórmula SUMIF no Excel

Em algumas análises, pode ser necessário filtrar algumas observações ao aplicar a função de soma ou contagem. Em tais casos, esta função SUMIF avançada do Excel está em nosso resgate. Ele filtra todas as observações com base em certas condições fornecidas nesta fórmula avançada do Excel e as soma. Ex. E se quisermos saber a soma dos salários apenas dos funcionários com ID de funcionário maior que 3.

Aplicando a Fórmula SUMIFS:

A fórmula retorna os resultados como 322000.

Também podemos contar o número de funcionários na organização com ID de funcionário maior que 3 quando usamos CONT.SE em vez de SOMASE.

# 7 - Fórmula CONCATENADA no Excel

Esta função avançada do Excel é uma das fórmulas que podem ser usadas com várias variantes. Esta fórmula avançada do Excel nos ajuda a juntar várias sequências de texto em uma única sequência de texto. Ex, se quisermos mostrar o ID do funcionário e o nome do funcionário em uma única coluna.

Podemos usar esta fórmula CONCATENADA apenas para fazer isso.

A fórmula acima resultará em “1Aman Gupta”.

Podemos ter mais uma variante colocando um único hífen entre ID e NOME. Ex. CONCATENAR (B3, ”-“, C3) resultará em “1-Aman Gupta”. Também podemos usar isso em VLOOKUP quando LOOKUP no valor do Excel é uma mistura de mais de uma variável.

# 8 - Fórmula ESQUERDA, MEIO e DIREITA no Excel

Podemos usar esta fórmula avançada do Excel se quisermos extrair uma determinada substring de uma determinada string. As fórmulas mencionadas podem ser usadas com base em nossos requisitos. Ex. Se quisermos extrair os primeiros 5 caracteres do nome do funcionário, podemos usar a fórmula LEFT no Excel com o nome da coluna e o segundo parâmetro como 5.

O resultado é fornecido abaixo:

A aplicação da fórmula RIGHT no Excel também é a mesma, só que estaríamos olhando para o caractere à direita da string. No entanto, no caso de uma função MID no Excel, devemos fornecer a posição inicial da string de texto necessária e o comprimento da string.

# 9 - Fórmula OFFSET no Excel

Essa função avançada do Excel com uma combinação de outras funções, como SUM ou AVERAGE, pode ser útil para dar um toque dinâmico aos cálculos. É melhor usado nos casos em que estamos inserindo linhas contínuas em um banco de dados existente. OFFSET Excel nos dá um intervalo onde precisamos mencionar a célula de referência, o número de linhas e colunas. Ex. Se quisermos calcular a média dos primeiros 5 funcionários da empresa em que temos o salário dos funcionários classificados por ID de funcionário, podemos fazer o seguinte. O cálculo abaixo sempre nos dará um salário.

• Isso nos dará a soma dos salários dos primeiros 5 funcionários.

# 10 - Fórmula TRIM no Excel

Esta fórmula avançada do Excel é usada para limpar os espaços sem importância do texto. Ex. Se quisermos remover espaços no início de algum nome, podemos usá-lo usando a função TRIM no excel como abaixo:

A saída resultante seria “Chandan Kale” sem nenhum espaço antes de Chandan.