10 fórmulas avançadas do Excel que você deve conhecer

Todo analista financeiro The Analyst Trifecta® Guide O guia definitivo sobre como ser um analista financeiro de classe mundial. Você quer ser um analista financeiro de classe mundial? Você está procurando seguir as melhores práticas do setor e se destacar na multidão? Nosso processo, denominado The Analyst Trifecta®, consiste em análises, apresentações e habilidades pessoais e passa mais tempo no Excel do que gostariam de admitir. Com base em anos e anos de experiência, compilamos as fórmulas de Excel mais importantes e avançadas que todo analista financeiro de nível mundial deve conhecer.

1. ÍNDICE MATCH

Fórmula: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Esta é uma alternativa avançada às fórmulas VLOOKUP ou HLOOKUP (que têm várias desvantagens e limitações). ÍNDICE MATCH Fórmula de correspondência de índice A combinação das funções INDEX e MATCH é uma fórmula de pesquisa mais poderosa do que VLOOKUP. Aprenda a usar INDEX MATCH neste tutorial do Excel. Index retorna o valor de uma célula em uma tabela com base na coluna e no número da linha e Match retorna a posição de uma célula em uma linha ou coluna. Aprenda como fazê-los neste guia é uma combinação poderosa de fórmulas do Excel que levarão sua análise e modelagem financeira ao próximo nível.

INDEX retorna o valor de uma célula em uma tabela com base no número da coluna e da linha.

MATCH retorna a posição de uma célula em uma linha ou coluna.

Aqui está um exemplo das fórmulas INDEX e MATCH combinadas. Neste exemplo, procuramos e retornamos a altura de uma pessoa com base em seu nome. Visto que o nome e a altura são variáveis ​​na fórmula, podemos alterar os dois!

Fórmulas avançadas do Excel - Índice de correspondênciaIndex Match Formula Combinar as funções INDEX e MATCH é uma fórmula de pesquisa mais poderosa do que VLOOKUP. Aprenda a usar INDEX MATCH neste tutorial do Excel. Index retorna o valor de uma célula em uma tabela com base na coluna e no número da linha e Match retorna a posição de uma célula em uma linha ou coluna. Aprenda como fazer isso neste guia

Para obter uma explicação passo a passo ou como usar esta fórmula, consulte nosso guia gratuito sobre como usar INDEX MATCH na fórmula de correspondência de índice do Excel. Combinar as funções INDEX e MATCH é uma fórmula de pesquisa mais poderosa do que VLOOKUP. Aprenda a usar INDEX MATCH neste tutorial do Excel. Index retorna o valor de uma célula em uma tabela com base na coluna e no número da linha e Match retorna a posição de uma célula em uma linha ou coluna. Aprenda como lidar com eles neste guia.

2. SE combinado com AND / OR

Fórmula: = SE (AND (C2> = C4, C2 <= C5), C6, C7)

Qualquer pessoa que passou muito tempo realizando vários tipos de modelos financeiros Tipos de modelos financeiros Os tipos mais comuns de modelos financeiros incluem: modelo de 3 demonstrativos, modelo DCF, modelo de M&A, modelo LBO, modelo de orçamento. Descubra os 10 principais tipos sabe que fórmulas IF aninhadas podem ser um pesadelo. Combinar IF com AND ou a função OR pode ser uma ótima maneira de manter as fórmulas mais fáceis de auditar e mais fáceis de serem entendidas por outros usuários. No exemplo abaixo, você verá como usamos as funções individuais em combinação para criar uma fórmula mais avançada.

Para obter uma análise detalhada de como executar essa função no Excel, consulte nosso guia gratuito sobre como usar IF com declaração E / OU IF entre dois números Baixe este modelo gratuito para uma declaração IF entre dois números no Excel. Neste tutorial, mostramos passo a passo como calcular IF com a instrução AND. Aprenda a construir uma instrução IF que irá testar se uma célula contém um valor entre dois números e, em seguida, produzir o resultado que você deseja se essa condição for atendida. Fórmulas avançadas do Excel - SE EDeclaração IF entre dois números Baixe este modelo gratuito para uma declaração IF entre dois números no Excel. Neste tutorial, mostramos passo a passo como calcular IF com a instrução AND. Aprenda a construir uma instrução IF que irá testar se uma célula contém um valor entre dois números e então produzir o resultado que você deseja se essa condição for atendida

3. OFFSET combinado com SUM ou AVERAGE

Fórmula: = SUM (B4: OFFSET (B4,0, E2-1))

A função OFFSET Função OFFSET A função OFFSET está categorizada nas funções de Consulta e Referência do Excel. OFFSET retornará um intervalo de células. Ou seja, ele retornará um número especificado de linhas e colunas de um intervalo inicial que foi especificado. por si só não é particularmente avançado, mas quando combinamos com outras funções como SOMA ou MÉDIA, podemos criar uma fórmula bastante sofisticada. Suponha que você queira criar uma função dinâmica que pode somar um número variável de células. Com a fórmula SUM normal, você está limitado a um cálculo estático, mas ao adicionar OFFSET você pode fazer com que a referência da célula se mova.

Como funciona: Para fazer esta fórmula funcionar, substituímos a célula de referência final da função SUM pela função OFFSET. Isso torna a fórmula dinâmica e a célula referenciada como E2 é onde você pode dizer ao Excel quantas células consecutivas você deseja adicionar. Agora temos algumas fórmulas avançadas do Excel!

Abaixo está uma captura de tela dessa fórmula um pouco mais sofisticada em ação.

fórmula de soma de compensação

Como você pode ver, a fórmula SUM começa na célula B4, mas termina com uma variável, que é a fórmula OFFSET começando em B4 e continuando pelo valor em E2 (“3”), menos um. Isso move o final da fórmula de soma para 2 células, somando 3 anos de dados (incluindo o ponto de partida). Como você pode ver na célula F7, a soma das células B4: D4 é 15, que é o que a fórmula de deslocamento e soma nos dá.

Aprenda como construir esta fórmula passo a passo em nosso curso avançado de Excel.

4. ESCOLHER

Fórmula: = ESCOLHER (escolha, opção 1, opção 2, opção 3)

A função CHOOSE Função CHOOSE A função CHOOSE é categorizada nas funções de Consulta e Referência do Excel. Ele retornará um valor de uma matriz correspondente ao número de índice fornecido. A função retornará a enésima entrada em uma determinada lista. Como analista financeiro, a função CHOOSE é útil na seleção de um determinado conjunto de dados. Por exemplo, somos ótimos para análise de cenário 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, imagine que você tem três premissas diferentes para o crescimento da receita no próximo ano: 5%, 12% e 18%. Usando a fórmula CHOOSE você pode retornar 12% se disser ao Excel que deseja a escolha # 2.

Leia mais sobre a análise de cenário no Excel. Análise de cenário A análise de cenário é uma técnica usada para analisar decisões por meio da especulação de vários resultados possíveis em investimentos financeiros. Em modelagem financeira, isso.

ESCOLHER a função

Para ver um vídeo de demonstração, confira nosso Curso Avançado de Fórmulas Excel.

5. XNPV e XIRR

Fórmula: = XNPV (taxa de desconto, fluxos de caixa, datas)

Se você é um analista que trabalha em banco de investimento Plano de carreira de banco de investimento Guia de carreira de banco de investimento - planeje sua carreira de IB. Saiba mais sobre salários de banco de investimento, como ser contratado e o que fazer após uma carreira no IB. A divisão de banco de investimento (IBD) ajuda governos, empresas e instituições a levantar capital e concluir fusões e aquisições (M&A). , pesquisa de patrimônio, planejamento e análise financeiros (FP&A FP&A Analyst Torne-se um FP&A Analyst em uma corporação. Descrevemos o salário, as habilidades, a personalidade e o treinamento de que você precisa para cargos de FP&A e uma carreira financeira de sucesso. Analistas de FP&A, gerentes e diretores são responsável por fornecer aos executivos a análise e as informações de que precisam), ou qualquer outra área de finanças corporativas que exija o desconto de fluxos de caixa,então essas fórmulas são um salva-vidas!

Simplificando, o XNPV e o XIRR permitem que você aplique datas específicas a cada fluxo de caixa individual que está sendo descontado. O problema com as fórmulas básicas de VPL e TIR do Excel é que elas assumem que os períodos de tempo entre o fluxo de caixa são iguais. Rotineiramente, como analista, você enfrentará situações em que os fluxos de caixa não são cronometrados de maneira uniforme, e esta fórmula é como você conserta isso.

Fórmula de financiamento avançado XNPV no Excel

Para uma análise mais detalhada, consulte nosso guia de fórmulas grátis IRR vs XIRR XIRR vs IRR Por que usar XIRR vs IRR. O XIRR atribui datas específicas a cada fluxo de caixa individual, tornando-o mais preciso do que a TIR ao construir um modelo financeiro no Excel. bem como nosso guia XNPV.

6. SUMIF e CONT.SE

Fórmula: = CONT.SE (D5: D12, ”> = 21 ″)

Essas duas fórmulas avançadas são ótimos usos para funções condicionais. SUMIF adiciona todas as células que atendem a certos critérios e CONT.SE conta todas as células que atendem a determinados critérios. Por exemplo, imagine que você deseja contar todas as células maiores ou iguais a 21 (a idade legal para beber nos EUA) para descobrir quantas garrafas de champanhe você precisa para um evento de cliente. Você pode usar COUNTIF como uma solução avançada, conforme mostrado na imagem abaixo.

Fórmula CONT.SE

Em nosso curso avançado de Excel, dividimos essas fórmulas com ainda mais detalhes.

7. PMT e IPMT

Fórmula: = PMT (taxa de juros, número de períodos, valor presente)

Se você trabalha em banco comercial Banco comercial Perfil de carreira Um plano de carreira no banco comercial é fornecer aos clientes produtos de crédito, como empréstimos a prazo, linhas de crédito rotativo, consórcios, serviços de gestão de caixa e outros produtos de renda fixa. Como analista de crédito ou gerente de contas, você fornece consultoria financeira, imobiliário, FP&A FP&A O planejamento e análise financeira (FP&A) é uma função importante em uma corporação. Profissionais de FP&A apoiam a tomada de decisões executivas para o CEO, CFO e Conselho de Administração com planejamento, análise e modelagem. Aprenda o que o trabalho de um analista, gerente ou diretor de FP&A envolve - salário, requisitos, educação, habilidades ou qualquer posição de analista financeiro que lida com cronogramas de dívidas. Você vai querer entender essas duas fórmulas detalhadas.

A fórmula PMT fornece o valor de pagamentos iguais ao longo da vida de um empréstimo. Você pode usá-lo em conjunto com o IPMT (que informa os pagamentos de juros para o mesmo tipo de empréstimo) e, em seguida, separar os pagamentos de principal e juros.

Aqui está um exemplo de como usar a função PMT para obter o pagamento mensal da hipoteca de uma hipoteca de $ 1 milhão a 5% por 30 anos.

8. LEN e TRIM

Fórmulas: = LEN (texto) e = TRIM (texto)

São fórmulas um pouco menos comuns, mas certamente muito sofisticadas. Esses aplicativos são excelentes para analistas financeiros. The Analyst Trifecta® Guide O guia definitivo sobre como ser um analista financeiro de classe mundial. Você quer ser um analista financeiro de classe mundial? Você está procurando seguir as melhores práticas do setor e se destacar na multidão? Nosso processo, denominado The Analyst Trifecta®, consiste em análises, apresentações e habilidades pessoais que precisam organizar e manipular grandes quantidades de dados. Infelizmente, os dados que obtemos nem sempre estão perfeitamente organizados e às vezes pode haver problemas como espaços extras no início ou no final das células

No exemplo abaixo, você pode ver como a fórmula TRIM limpa os dados do Excel.

Excel Avançado - TRIM

9. CONCATENAR

Fórmula: = A1 & ”mais texto”

Concatenar não é realmente uma função em si - é apenas uma forma inovadora de juntar informações de diferentes células e tornar as planilhas mais dinâmicas. Esta é uma ferramenta muito poderosa para analistas financeiros que executam modelagem financeira (consulte nosso guia de modelagem financeira grátis Guia de modelagem financeira grátis Este guia de modelagem financeira cobre dicas do Excel e melhores práticas sobre suposições, motivadores, previsões, vinculação das três declarações, análise de DCF, mais para Saber mais).

No exemplo abaixo, você pode ver como o texto “New York” mais “,“ se junta a “NY” para criar “New York, NY”. Isso permite que você crie cabeçalhos e rótulos dinâmicos em planilhas. Agora, em vez de atualizar a célula B8 diretamente, você pode atualizar as células B2 e D2 independentemente. Com um grande conjunto de dados, essa é uma habilidade valiosa de se ter à sua disposição.

Exemplo de fórmula concatenada do Excel

10. Funções CELL, LEFT, MID e RIGHT

Essas funções avançadas do Excel podem ser combinadas para criar algumas fórmulas muito avançadas e complexas para usar. A função CELL pode retornar uma variedade de informações sobre o conteúdo de uma célula (como seu nome, localização, linha, coluna e muito mais). A função LEFT pode retornar texto do início de uma célula (da esquerda para a direita), MID retorna o texto de qualquer ponto inicial da célula (da esquerda para a direita) e RIGHT retorna o texto do final da célula (da direita para a esquerda).

Abaixo está uma ilustração dessas três fórmulas em ação.

Para ver como eles podem ser combinados de uma maneira poderosa com a função CELL, nós dividimos para você passo a passo em nossa classe de fórmulas avançadas do Excel.

Fórmulas CELL, LEFT, MID e RIGHT

Mais treinamento de fórmulas do Excel

Esperamos que essas 10 fórmulas avançadas do Excel tenham sido úteis para você. Eles devem percorrer um longo caminho para melhorar sua análise financeira e habilidades de modelagem financeira Habilidades de modelagem financeira Aprenda as 10 habilidades de modelagem financeira mais importantes e o que é necessário para ser bom em modelagem financeira no Excel. Habilidades mais importantes: contabilidade.

Abaixo estão mais recursos financeiros para ajudá-lo a se tornar um usuário avançado do Excel:

  • Folha de referências das fórmulas do Excel Folha de referências das fórmulas do Excel A folha de referências das fórmulas do Excel do Finance lhe dará todas as fórmulas mais importantes para realizar análises financeiras e modelagem em planilhas do Excel. Se você deseja se tornar um mestre em análise financeira do Excel e um especialista na construção de modelos financeiros, então você veio ao lugar certo.
  • Atalhos de teclado do Excel Atalhos do Excel PC Mac Atalhos do Excel - Lista dos atalhos mais importantes e comuns do MS Excel para usuários de PC e Mac, finanças, contabilidade. Os atalhos do teclado aceleram suas habilidades de modelagem e economizam tempo. Aprenda a editar, formatar, navegar, faixa de opções, colar especial, manipulação de dados, edição de fórmulas e células e outros atalhos
  • Curso intensivo de Excel grátis
  • Curso avançado de Excel
  • Excel para modelagem financeira

Recomendado

Arrendamento de capital vs arrendamento operacional
O que é um Dividendo?
Qual é a taxa anual efetiva?