Folha de referência de fórmulas do Excel: guia intermediário

Então você conseguiu dominar o básico do Excel – parabéns! Agora, você decidiu dar um up nas coisas e mergulhar nas coisas mais avançadas, certo? Bem, você está com sorte porque reunimos uma folha de dicas do Excel intermediário útil só para você!

Esta folha de dicas intermediária do Microsoft Excel abrange uma ampla gama de tópicos que aumentarão suas habilidades em análise numérica, processamento de texto e lógica complexa de data e hora.

Tendo o Excel Intermediate Cheat Sheet como uma referência de área de trabalho, você pode se aprofundar nas fórmulas e funções mais poderosas do Microsoft Excel.

Agora, certifique-se de ler, mas também imprima e salve a folha de dicas abaixo!

Vamos começar!

Excel-formulas-cheat-sheet-intermediate-1 Baixar

Índice

• Fórmulas matemáticas intermediárias
  • Lógica Condicional
• Fórmulas Estatísticas Intermediárias
  • 1. MINA e MAXA
  • 2. CONT.A e CONT.SE
• Fórmulas do Excel para Análise Financeira
  • 1. Função do PRODUTO
  • 2. Funções QUOCIENTE e LOG
  • Funções para Cenários de Investimento
• Fórmulas intermediárias de data e hora do Excel
  • 1. Funções intermediárias do mês
  • 2. Funções da Semana Intermediária
  • 3. Funções de tempo intermediárias
• Funções do Excel para Álgebra Condicional e Lógica
  • 1. Função SE
  • 2. Função E
  • 3. OU Função
  • 4. NÃO Função
• Células de referência
  • 1. Função INDIRETA
  • 2. Função ÍNDICE
  • 3. Função OFFSET
• Fórmulas de Texto Intermediário
  • 1. Função TEXTJOIN
  • 2. Função SUBSTITUIR
  • 3. Função SUBSTITUTA
• Fórmulas de pesquisa intermediárias
• Fórmulas para lidar com erros do Microsoft Excel
  • 1. Função SEERRO
  • 2. Função IFNA
• Atalhos de fórmulas e funções
• Formatação de Célula Intermediária
• Pensamentos finais

Fórmulas matemáticas intermediárias

Nossa folha de dicas para iniciantes cobriu as funções matemáticas mais comuns para iniciantes, como a função SUM. Usuários intermediários devem se familiarizar com estas funções mais complexas:

• ABS() retorna o valor absoluto de um número.

• SQRT() retorna a raiz quadrada de um número

• RAND() fornece um número aleatório entre 0 e 1

Lógica Condicional

Os usuários intermediários também devem saber como realizar cálculos matemáticos com base em várias condições usando estas funções:

• SUMIFS(intervalo_soma, intervalo_critérios1, critérios1, [….])

• COUNTIFS(intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2], …)

• AVERAGEIFS(intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2], …)

As funções avaliam os critérios fornecidos e produzem um valor lógico de VERDADEIRO ou FALSO.

Digamos que você tenha os seguintes dados de vendas nas colunas A, B e C:

Para calcular a soma das vendas de camisetas vermelhas, use a fórmula SOMAS:

=SOMAS(C2:C6, A2:A6, “Camiseta”, B2:B6, “Vermelho”)

Para contar o número de linhas com camisetas vermelhas, use a fórmula COUNTIFS:

=CONT.IFS(C2:C6, A2:A6, “Camiseta”, B2:B6, “Vermelho”)

Para calcular a média de vendas de camisetas vermelhas, use a fórmula AVERAGEIFS:

=MÉDIAIFS(C2:C6, A2:A6, “Camiseta”, B2:B6, “Vermelho”)

Fórmulas Estatísticas Intermediárias

Além de funções básicas como MIN e MAX, o Excel possui uma gama mais ampla de fórmulas estatísticas para usuários intermediários. Aqui estão alguns dos mais úteis:

• MINA

• MAXA

• CONT.A

• CONT.SE

1. MINA e MAXA

As funções MIN e MAX mais comumente usadas ignoram valores de texto.

As funções alternativas MINA e MAXA consideram texto e números ao procurar os valores mais altos ou mais baixos. Os valores de texto são avaliados como se fossem zero.

As diferenças são mostradas na imagem abaixo. A primeira linha avalia um valor mínimo de 10, enquanto a segunda linha avalia como 0 devido à presença de um valor de texto.

2. CONT.A e CONT.SE

A função COUNTA é usada para contar o número de células não vazias em um intervalo.

A função CONT.SE é menos específica, pois é usada para contar o número de células dentro de um intervalo que atende a uma condição ou critério específico.

Você também pode usar essas funções para contar o número de valores distintos em uma coluna. Este vídeo mostra como fazer isso.

Fórmulas do Excel para Análise Financeira

Existem várias fórmulas que você desejará usar ao realizar análises financeiras, como previsão de investimentos:

• PRODUTO(número1, [número2…])

• QUOCIENTE(numerador, denominador)

• LOG(número, [base])

1. Função do PRODUTO

Suponha que você queira calcular o valor futuro de um investimento inicial de $ 1.000 (célula B1) a uma taxa de juros anual de 4% (célula B2) após cinco anos (célula B3).

Use a função PRODUTO para calcular o valor futuro com esta fórmula:

=B1 * PRODUTO(1 + B2)^B3

Esta fórmula retornará o valor futuro do seu investimento.

2. Funções QUOCIENTE e LOG

O próximo passo é calcular quantos anos levará para um investimento dobrar de valor à taxa de juros dada.

Use a função QUOCIENTE em combinação com a função LOG para calcular isso da seguinte forma:

=QUOCIENTE(LOG(2) /LOG(1 + B2), 1)

Essa fórmula retornará 17 com os dados da amostra, indicando que levará 17 anos para o investimento dobrar de valor a uma taxa de juros anual de 4%.

Esta imagem mostra as fórmulas em uma planilha do Excel :

Funções para Cenários de Investimento

Estas são algumas funções que são adequadas para cenários financeiros específicos. Um deles pode ser exatamente o que você precisa:

• NPV – A função NPV (Net Present Value) calculará o valor presente líquido de um investimento com base em uma série de fluxos de caixa futuros com base em uma taxa de desconto.

• ACCRINT – A função ACCRINT calcula os juros acumulados de um título que paga juros periódicos. Isso é útil para determinar os juros ganhos em um título desde a última data de pagamento até uma determinada data de liquidação.

• INTRATE – A função INTRATE calcula a taxa de juros para um título totalmente investido.

• PMT – A função PMT calcula o pagamento total de um título de dívida.

• IRR – A função IRR fornece a taxa interna de um retorno.

• YIELD – A função YIELD fornece o rendimento de um título com base na taxa de juros, valor de face e vencimento.

Fórmulas intermediárias de data e hora do Excel

As funções básicas de data e hora no Excel incluem as funções AGORA e HOJE para a data atual. Os usuários intermediários também devem saber como extrair componentes de uma determinada data usando:

• DIA(data)

• Data do mês)

• ANO(data)

A fórmula =MONTH(“23 de abril de 2023″) retornará um resultado de 4 para o 4º mês. Da mesma forma, as funções DIA e ANO retornarão 23 e 2023, respectivamente.

1. Funções intermediárias do mês

Os usuários intermediários às vezes lidam com a adição ou subtração de meses a uma data e encontram o final de um mês.

• EDATE(data_início, número_dos_meses)

• EOMONTH(start_date, number_of_months)

Por exemplo, a fórmula =EDATE(“23 de abril de 2023”, 2) calculará a data dois meses depois.

A fórmula =EOMONTH(23 de abril de 2023”, 2) calcula o final do mês dois meses depois.

O resultado é “30 de junho de 2023”, que leva em conta que junho tem apenas trinta dias. Se você especificar 3 meses, o resultado será “31 de julho de 2023”.

2. Funções da Semana Intermediária

Você pode usar uma combinação das funções SUM e WEEKDAY para contar o número de dias úteis inteiros dentro de um intervalo de datas.

A função WEEKDAY retorna o dia da semana para uma determinada data. O segundo parâmetro determina o sistema de numeração com o padrão sendo de domingo a sábado.

Se você tiver uma série de datas em um intervalo de células de B1:B40, use esta fórmula para calcular os dias úteis:

=SOMA(–(DIA DA SEMANA(B1:B40,2)>5))

3. Funções de tempo intermediárias

A função YEARFRAC calcula a fração de um ano entre duas datas.

A função TIMEVALUE é usada para converter uma hora representada como texto em um número decimal que representa a proporção de um dia de 24 horas.

O resultado é um número de série do Excel em que 1 representa um dia completo de 24 horas, 0,5 representa 12 horas, 0,25 representa 6 horas e assim por diante.

Suponha que você tenha um horário no formato de texto “17:45” na célula A1 e deseja convertê-lo em um número decimal . Esta é a fórmula:

=VALOR DE TEMPO(A1)

O valor 0,74 representa a proporção do dia de 24 horas que passou no horário de quinze minutos antes das seis horas.

Use para para alterar a célula de hora de um formato de hora para um formato de texto. Você pode formatar isso como uma porcentagem para ficar mais claro.

Funções do Excel para Álgebra Condicional e Lógica

As funções condicionais e lógicas são essenciais para a tomada de decisões no Excel. As seguintes funções podem ser combinadas com as fórmulas mais básicas do Excel para uma lógica poderosa:

1. SE(condição, valor_se_verdadeiro, valor_se_falso):

2. E(condição1, condição2, …)

3. OU(condição1, condição2, …)

4. NÃO(condição1, condição2, …)

A função SE avalia uma condição e retorna valores diferentes dependendo se a condição é verdadeira ou falsa.

As funções AND, OR e NOT verificam quais condições são verdadeiras e permitem que você tome decisões de acordo.

Aqui estão alguns exemplos baseados nos dados de vendas que usamos anteriormente.

1. Função SE

Suponha que você queira exibir o texto “Low Sales” se as vendas dos itens forem menores que 15. Se as vendas forem maiores, você deseja exibir “High Sales” em uma nova coluna.

Use esta fórmula e copie-a para as outras linhas:

=SE(C2<15, “Vendas Baixas”, “Vendas Altas”)

2. Função E

Suponha que você queira exibir VERDADEIRO ou FALSO para camisetas com pelo menos 15 vendas. Use esta fórmula:

=E(A3=”Camiseta”, C3>=15)

3. OU Função

Suponha que você queira testar várias condições e retornar TRUE se qualquer uma das condições for atendida e FALSE caso contrário, então você usaria a função OR.

Por exemplo, se tivermos pontuações de um teste nas células A1 e B1 e quisermos saber se alguma pontuação está acima de 80, poderíamos usar: =OR( A1>80, B1>80) . Isso retornará TRUE se qualquer pontuação (ou ambas) for superior a 80 e FALSE se ambas forem 80 ou menos.

4. NÃO Função

Suponha que você queira verificar se o item não é um moletom. Use esta fórmula:

=NÃO(A2="Moletom")

Células de referência

Um iniciante se familiarizará rapidamente com o uso de referências absolutas e relativas , como $A$1 ou B2. Os usuários intermediários devem se familiarizar com o uso de referências indiretas, índices e compensações:

1. INDIRETO(ref_text)

2. INDEX(intervalo, núm_linha, núm_coluna)

3. OFFSET(reference_cell, linhas, colunas):

1. Função INDIRETA

A função INDIRETO retorna o valor da referência de célula especificada inserida como texto. Para referenciar a célula B3, use esta fórmula:

=INDIRETO(“B3”)

A vantagem sobre o uso de referências de célula simples é que esta função fornece uma referência dinâmica. Isso significa que o Excel atualiza automaticamente a referência quando a estrutura da planilha muda, por exemplo, uma linha é excluída.

2. Função ÍNDICE

A função INDEX é usada para fazer referência a células dentro de um intervalo especificado com base em números de linha e coluna.

Para referenciar a célula B3 em uma tabela de seis linhas, use esta fórmula:

=ÍNDICE(B1:B6, 3)

3. Função OFFSET

A função DESLOC retorna uma célula ou intervalo que está a um número especificado de linhas e colunas distante de uma célula de referência.

Se você deseja exibir o valor da célula que é duas células e uma célula abaixo de A1, use esta fórmula:

=DESVIO(A1, 2, 1)

Aqui estão as fórmulas em ação:

Fórmulas de Texto Intermediário

Iniciantes devem conhecer funções de texto como LEFT, que extrai um ou mais caracteres do lado esquerdo de uma string. Um usuário avançado deve estar familiarizado com funções como estas:

1. TEXTJOIN(delimitador, ignore_empty, text1, [text2, …])

2. REPLACE(old_text, start_num, num_chars, new_text)

3. SUBSTITUTE(texto, texto_antigo, texto_novo, [instance_num])

1. Função TEXTJOIN

A função TEXTJOIN concatena células com o delimitador que você fornece. Você também pode especificar onde ignorar as células em branco.

Para produzir uma lista delimitada por vírgulas de alguns itens na coluna A, use esta fórmula:

=TEXTJOIN(“, “, VERDADEIRO, A2:A4)

2. Função SUBSTITUIR

A função substituir permite especificar a posição inicial e o comprimento da string que você deseja trocar no destino.

Para substituir os dois primeiros caracteres de uma string por “XX”, use esta fórmula:

= TEXTOIN (A2, 1, 2, “XX”)

3. Função SUBSTITUTA

A função substituta substitui as ocorrências especificadas de uma cadeia de texto dentro de outra cadeia de texto por uma nova cadeia de texto.

Para substituir as ocorrências da palavra “Jeans” por “Leggings”, use esta fórmula:

=SUBSTITUIR(A3, “Jeans”, “Leggings”)

Aqui estão as fórmulas em ação:

Fórmulas de pesquisa intermediárias

Funções de pesquisa comuns, como VLOOKUP e MATCH, são abordadas na folha de dicas do iniciante.

Os usuários intermediários devem se familiarizar com a função de pesquisa CHOOSE:

• ESCOLHA(núm_índice, valor1, [valor2, …])

A função CHOOSE retorna um valor de uma lista de valores com base em um número de índice especificado.

Suponha que você tenha uma coluna de tamanhos de camisetas rotuladas como 1, 2 ou 3. Você deseja exibir uma categoria de pequeno, médio e grande, respectivamente, na mesma linha. Use esta fórmula:

=ESCOLHA(A1, “Pequeno”, “Médio”, “Grande”)

Fórmulas para lidar com erros do Microsoft Excel

A folha de dicas para iniciantes listou as mensagens de erro típicas que você verá ao trabalhar com o Excel. Os usuários intermediários devem ser capazes de usar fórmulas para lidar com os erros normalmente.

1. SEERRO(valor, if_error)

2. IFNA(valor, valor_se_na)

1. Função SEERRO

Erros de divisão por zero são comuns, mas a exibição padrão do Excel de #DIV/0! não ficará claro para todos os usuários.

Use a função SEERRO para substituir o erro padrão por “Não é possível dividir por zero” com esta fórmula:

=SEERRO(A1/B1, “Não é possível dividir por zero”)

Esta imagem mostra o erro bruto na terceira linha e a função IFERROR lidando com a quarta linha.

2. Função IFNA

A função IFNA no Excel é usada para capturar e manipular erros #N/A.

Outra seção desta folha de dicas mostrava a função CHOOSE. Esta função retornará um erro N/A se o número do índice não for um número inteiro.

Suponha que o tamanho de uma camiseta tenha sido inserido como 1,5, com o usuário pensando erroneamente que isso mostrará uma categoria entre pequeno e médio. Em vez disso, ele mostrará um erro #N/A .

Para fornecer um erro mais útil, use esta fórmula:

=IFNA(ESCOLHA(A2, “Baixo”, “Médio”, “Alto”), “Índice inválido”)

Atalhos de fórmulas e funções

Existem vários atalhos de teclado que podem fazer você trabalhar de forma mais eficiente com sua planilha.

• F2 : Edite a célula ativa e posicione o ponto de inserção no final do conteúdo da célula.

• F9 : Calcula e exibe o resultado da parte selecionada de uma fórmula.

• Ctrl + Shift + Enter : insira uma fórmula de matriz.

• Shift + F3 : abre a caixa de diálogo Inserir Função.

• Esc : cancela a entrada de uma fórmula e reverte para o conteúdo original da célula.

Alguns desses atalhos do Excel podem não estar disponíveis em todos os idiomas ou layouts de teclado.

Formatação de Célula Intermediária

Na análise financeira, a formatação adequada das células que contêm números, datas e moedas é crucial. Para formatar células, siga estas etapas:

1. Selecione as células que deseja formatar.

2. Clique com o botão direito do mouse nas células selecionadas e escolha “Formatar células”.

3. Selecione a categoria apropriada (por exemplo, Número, Moeda, Data) e aplique o formato desejado.

Alguns tipos de formatação comuns a serem considerados em finanças incluem:

• Moeda : $ 1.234,56

• Porcentagem : 12,34%

• Contabilidade : ($ 1.234,00)

• Data : 10 de maio de 2023

Pensamentos finais

À medida que você se sentir mais confortável com as várias fórmulas e funções destacadas nesta folha de dicas , você se sentirá mais bem equipado para lidar com tarefas e projetos cada vez mais complexos.

A prática é a chave para dominar qualquer habilidade, portanto, não tenha medo de experimentar essas fórmulas em seu trabalho diário. Eles trarão sua experiência do nível iniciante ao nível intermediário.

Esta é apenas uma amostra do que as funções intermediárias do Excel podem fazer para simplificar seu trabalho, agilizar suas análises e ajudá-lo a descobrir insights de seus dados.

A verdadeira mágica acontece quando você começa a combinar as funções em nossa folha de dicas e as adapta para atender às suas necessidades exclusivas. Então, não pare por aqui. Continue explorando, continue experimentando e você descobrirá que o Excel não é apenas uma ferramenta, é um divisor de águas!

Sente que quer levar as coisas para o próximo nível? Confira nossa folha de dicas de fórmulas avançadas do Excel.