17 Fórmulas Avançadas do Excel - Um Deve Saber Para Todos os Profissionais

Se você costuma flexionar as mãos e perder horas para fazer as coisas manualmente no Excel, está perdendo o quão poderoso o Excel pode ser se você souber usá-lo da maneira certa. O Microsoft Excel é conhecido por suas fórmulas que podem funcionar com eficiência em um grande número de dados na célula, sem muito envolvimento manual necessário no processo.

Nós compilamos 17 fórmulas avançadas do Excel que têm uma incrível capacidade de transformar longas tarefas mundanas e manuais em alguns segundos de trabalho. São fórmulas que todo profissional deve ter na ponta dos dedos para economizar seu precioso tempo, ou para impressionar seu chefe em um momento crítico.

Percorra a lista e diga-nos o seu favorito!

Os alunos recebem um aumento salarial médio de 58%, sendo o mais alto até 400%.

1. CORRESPONDÊNCIA DE ÍNDICE

Fórmula = ÍNDICE(C3:E9,CORRESP(B13, C3:C9,0),CORRESP(B14,C3:E3,0))

Uma excelente alternativa para a fórmula VLOOKUP ou HLOOKUP no Excel que tem algumas desvantagens para executar tarefas de pesquisa. O INDEX MATCH é uma fórmula de combinação de 2 funções separadas:

ÍNDICE: Esta fórmula retorna o valor de uma célula em uma tabela com base na coluna e no número da linha.

CORRESP: Esta fórmula retorna a posição de uma célula em uma linha ou coluna.

Um exemplo da combinação das fórmulas INDEX e MATCH é: Ao pesquisar e retornar a altura de uma pessoa com base em seu nome, você pode usar esta fórmula para alterar ambas as variáveis ​​(neste caso, nome e altura) usando a fórmula INDEX MATCH .

Fonte

Vamos decompô-lo passo a passo:

Como combinar INDEX e MATCH

• Digite ÍNDICE
• Selecione a área que deseja INDEXAR
• Bloqueie a área com F4
• Encontre a linha da qual você deseja procurar os dados
• Digite MATCH com a série de informações e bloqueie a área com F4
• Digite 0 para a correspondência exata, feche os colchetes
• Pressione Enter

Ideias interessantes de projetos de ciência de dados

Agora você tem um conjunto totalmente dinâmico e funcional de colunas e linhas na planilha, onde todas as vasculhadas pelas células e linhas para os dados corretos são feitas automaticamente.

2. MÉDIA

Para executar a fórmula da média para encontrar a média de qualquer intervalo de números, aqui estão as etapas que você precisa seguir:

Insira os valores, células ou escala de células que você está calculando no formato.

A fórmula deve começar com =MÉDIA(número1, número 2, etc.)

Se você deseja realizar a média de um intervalo de células na planilha, aqui estão as etapas que você precisa seguir:

• Insira os valores, células ou escala de células que você está calculando no formato, assim como você fez acima
• A fórmula deve ser como =MÉDIA(Valor inicial: Valor final).

Se você está querendo saber como inserir os valores para a região das células, você pode selecionar a área na sua planilha com o mouse e bloqueá-la com F4. Isso fará com que o Excel faça o resto do trabalho para você sem fazer nenhuma soma e divisão da figura com o número de itens no grupo.

3. SOMA

Esta fórmula no Excel é denotada como SUMIF(intervalo, critérios, [intervalo de soma]). Isso resultaria na soma dos valores dentro do intervalo desejado de células que atenderiam aos requisitos definidos por você. Por exemplo, =SOMA(C3: C12, “>70.000) retornaria a soma dos valores entre as células de C3 e C12 apenas das células que possuem o valor maior que 70.000.

No caso de finanças, salário ou até cálculos de custos, você precisa do valor dos leads que estão associados a funcionários específicos determinados pela condição definida por você. Fazer isso manualmente é muito demorado e retarda as coisas.

A fórmula para a condição acima pode ser =SOMA (intervalo, critérios, [intervalo_soma]), onde Intervalo pode ser definido como o Intervalo da planilha da qual você precisa escolher os valores.

[sum_range] é definido como o intervalo adicional ou opcional que você adicionará além do primeiro intervalo inserido.

Aqui está um exemplo:

Fonte

4. DESLOCAMENTO COMBINADO COM SOMA

Sozinha, a função OFFSET pode não ser útil, mas quando combinada com outros serviços, você pode obter uma fórmula complexa com resultados mais rápidos se quiser criar uma função dinâmica que possa somar qualquer número variável de células, a fórmula SUM regular, que é estático precisa ser combinado com a função OFFSET.

Então, para descobrir a soma dos valores nas células variáveis, a fórmula deve ser:

=SOMA(B4:OFFSET(B4,0,E2-1))

Fonte

Aqui a referência final da função SUM é substituída pela função OFFSET. A fórmula SUM começando em B4 termina com uma variável e é uma fórmula OFFSET começando em B4, continuando pelo valor em E2 (“3”) menos um. Isso ajuda o método a se mover por duas células e somar três anos de dados. Na referência acima, você pode ver que a célula F7 contém a soma das células B4: D4 = 15.

5. SE E

Essa fórmula é útil em situações em que você precisa criar certas condições para vasculhar uma pilha de dados. A instrução IF ajuda a usar a função IF avançada do Excel para criar um novo campo com base nessas condições em uma pista já existente.

Por exemplo, se você quiser marcar os funcionários com salários acima de 50K e ID do funcionário maior que 3, a fórmula será:

SE(E(E4>3,F4>50000)1,0)

Fonte

Como não há casos reais nos dados acima, a fórmula retornaria o valor 0.

6. CONCATENAR

Se você tem muitas strings de texto em sua folha de dados e gostaria de fazer os dados aparecerem em uma linha, esta fórmula é sua escolha. Por exemplo, se você deseja representar o ID do funcionário e o nome do funcionário em uma única coluna, o método deve ser:

=CONCATENAR(B3, C3)

Fonte

7. PMT

Essa função o ajudará a descobrir os pagamentos futuros devidos para um empréstimo, considerando uma taxa de juros específica, valor principal e duração do empréstimo. Aqui está o que você precisa para começar:

• O prazo do empréstimo
• O principal inicial (dinheiro) do empréstimo
• O valor futuro
• O tipo de empréstimo

Agora, se você quiser encontrar o pagamento mensal de um valor principal, a fórmula para o mesmo será:

= PMT (taxa, por, PV, [fv], [tipo])

Vamos entender isso com um exemplo:

Fonte

=PMT(C2/12.B2.A2)

E a melhor parte é que você pode arrastar o canto inferior direito das células PMT pelos campos para calcular automaticamente o valor do pagamento mensal para todos os campos!

Tipos populares de empregos em ciência de dados

8. APARAR

Esta é uma das fórmulas mais usadas no excel que limpa qualquer espaço indesejado nos campos. Por exemplo: Se você precisar remover os espaços no início de algum nome, você pode fazer isso usando a função TRIM:

=TRIM(C6)

Fonte

Isso retornaria você com o valor de Chandan Kale sem espaços extras anexados à frente ou ao final.

9. LEN

Esta é uma função que informa o número de caracteres em uma sequência de texto. Uma das maneiras mais interessantes de usar isso é, por exemplo, se você quiser destacar os doadores que doaram mais de US$ 1.000 contando o número de dígitos na coluna de doação, a fórmula será:

=LEN(B2)

Fonte

E se você quiser destacar todas as células na coluna Doação, precisará:

• Selecione a guia Início no menu
• Clique em Formatação Condicional (na barra de ferramentas)
• Selecione Usar uma Fórmula para determinar quais células formatar

Fonte

Aqui, se você fizer a condição “> 3”, qualquer coisa acima de $ 1.000 receberá a formatação exclusiva, que você pode escolher clicando na opção Formatar.

10. ESCOLHA

Esta é uma ótima função para análise de cenários em modelagem financeira. Ele permite que você escolha entre um número específico de opções e retorne a “escolha” que você selecionou. Por exemplo, se você tiver três valores diferentes para o crescimento da receita no próximo ano com base em suposições, usando a função ESCOLHER, você poderá retornar o valor de acordo com sua necessidade.

A fórmula é:

=ESCOLHER(C7,D3,D4,D5)

Fonte

11. CELULAR, ESQUERDA, MÉDIO e DIREITO

Todas as funções acima podem ser combinadas de várias maneiras para obter algumas fórmulas avançadas.

• A função CELL é usada para retornar diferentes tipos de informações sobre o conteúdo da célula
• O serviço LEFT é usado para substituir o texto do início da célula.
• A função MID pode retornar texto de qualquer um dos pontos iniciais da célula.
• A função DIREITA retorna apenas o texto do final da célula.

Fonte

12. XNPV e XIRR

Para todos os analistas que se deparam com banco de investimento, pesquisa de ações ou qualquer outra área de finanças corporativas que exija desconto de fluxos de caixa, essas fórmulas certamente economizarão muito tempo e resmungos!

Perguntas e respostas da entrevista sobre ciência de dados

Por exemplo, se você deseja calcular o Valor Líquido Presente (VPL) para qualquer investimento usando uma taxa especificada de desconto e fluxos de caixa ocorrendo em intervalos irregulares, use a função a seguir.

=XNPV(taxa de desconto, fluxos de caixa, datas)

Fonte

Por outro lado, a função XIRR informa a taxa interna de retorno (onde saída = entrada) para uma série de fluxos de caixa que ocorrem em intervalos irregulares, como:

13. CONTAGEM ( )

Os analistas são frequentemente encontrados na ponta dos pés lutando para encontrar o número de células com valores (números, erros, texto, valores lógicos) e aquelas que estão vazias. A função COUNTA( ) pode ajudá-lo a descobrir o nome de células não vazias em um intervalo selecionado. Por exemplo, a fórmula para contar valores para uma planilha de dados com colunas A1-A10 é:

=CONTA(A1: A10)

Fonte

14. VF

Esta fórmula é útil se você deseja investir dinheiro em algo e saber o seu valor. Os requisitos da fórmula FV são:

• A taxa de juros do empréstimo
• Número de pagamentos
• O Pagamento para cada período
• Saldo inicial atual
• Tipo de empréstimo

Por exemplo, se você quiser comparar vários CDs virgens e tiver uma herança de $ 20.000 para investir em um CD. As taxas de juros são representadas no formato decimal; pagamentos são zero. A fórmula do cenário será:

=FV(A2/12,B2,C2,D2)

Fonte

Os resultados serão:

Fonte

15. RAND ENTRE

Esta função ajuda a selecionar aleatoriamente um número dentro de um intervalo predefinido de números. Depois de colocar os números mais baixos e mais altos na fórmula, o Excel pode escolher os dados certos dos campos aos quais os nomes no intervalo estão anexados e escolher aleatoriamente. O método para o cenário é:

=RANDBETWEEN(ponto inicial, ponto final)

Fonte

16. PEQUENO

A função SMALL no Excel retorna valores numéricos com base na posição do valor em uma lista classificada por importância. Esta função ajuda a recuperar o “nésimo menor valor” de uma matriz ou intervalo de células, como o menor valor, o 2º valor mais baixo, o 3º valor mais baixo, etc.

A sintaxe da fórmula é

= PEQUENO (vezes, intervalo)

Por exemplo,

Fonte

Como a função SMALL é automática, você precisa fornecer um intervalo e um inteiro para 'nth' para especificar o valor classificado. Os nomes oficiais para esses argumentos são 'array' e 'k'.

17. QUARTIL

Esta função retorna o quartil (cada um dos quatro grupos iguais) em um determinado conjunto de dados e pode retornar o valor mínimo, primeiro quartil, valor máximo do segundo quartil. Esta função traz a quantidade quartil dos campos em um array. A função retorna um valor numérico de acordo com o percentil solicitado.

Sintaxe: =QUARTILE (array, quart)

Fonte

CONCLUSÃO:

O Microsoft Excel é inevitável quando se trata do local de trabalho do século 21, mas o truque é que não precisa ser tão assustador. Torne-o seu amigo e veja sua produtividade disparar!