Folha de referência de fórmulas do Excel: guia avançado

Então você passou pelo básico, abordou as coisas intermediárias e agora está pronto para lutar com as grandes armas - as fórmulas avançadas do Excel! O Excel tem muitas funções e fórmulas avançadas para cálculos sofisticados, por isso é útil ter um guia que identifique as que você precisa.

Esta folha de dicas de fórmulas do Excel abrange fórmulas avançadas de previsão, análise estatística, funções de manipulação de dados, tratamento de erros e muito mais.

Esta referência irá equipá-lo com o conhecimento de como usar essas funções avançadas. Cada fórmula é acompanhada por explicações claras, sintaxe e exemplos práticos para ajudar os usuários intermediários do Excel a se tornarem usuários avançados.

Faça o download e imprima a folha de dicas e mantenha-a à mão.

Ok, vamos começar.

Excel-fórmulas-cheatsheet-advanced-1 Baixar

Em primeiro lugar, vamos entrar em fórmulas de matriz.

Índice

• Fórmulas de Matriz
  • 1. Função ÚNICA
  • 2. Função CLASSIFICAR
  • 3. Função FILTRO
• Randomizando funções do Excel
  • 1. Função ALEATÓRIO
  • 2. Função RANDARRAY
• Fórmulas avançadas de previsão no Microsoft Excel
  • 1. Função FORECAST.ETS
  • 2. Função TENDÊNCIA
• Fórmulas Estatísticas Avançadas
  • 1. Função PERCENTIL
  • 2. Função QUARTIL
• Análise avançada de dados e fórmulas de manipulação
  • 1. Função TRANSPOR
  • 2. Função de FREQUÊNCIA
  • 3. Função GETPIVOTDATA
• Tratamento Avançado de Erros
• Fórmulas de Pesquisa Avançada
  • 1. Função XLOOKUP
  • 2. Função XMATCH
• Pensamentos finais

Fórmulas de Matriz

Nossa folha de dicas para iniciantes mostra como classificar e filtrar seus dados manualmente. Usuários avançados fazem isso programaticamente com fórmulas de matriz.

As fórmulas de matriz permitem que você execute cálculos em várias células simultaneamente. Estas são três funções principais:

1. EXCLUSIVO

2. ORGANIZAR

3. FILTRO

Algumas dessas funções estão disponíveis apenas nas versões mais recentes do Microsoft Excel.

1. Função ÚNICA

A função UNIQUE aceita um intervalo e retorna uma lista de valores exclusivos.

Suponha que você tenha dados de vendas para itens de vestuário. Para localizar os itens exclusivos na coluna A, use esta fórmula:

=ÚNICO(A2:A6)

2. Função CLASSIFICAR

A função SORT classifica o conteúdo de um intervalo. A sintaxe é:

SORT(array, [sort_index], [sort_order], [by_col])

• array : o intervalo de valores a classificar.

• sort_index : a coluna a classificar (1 por padrão)

• sort_order : 1 para crescente (padrão) ou 2 para decrescente).

• by_col : TRUE para classificar por coluna (padrão) ou FALSE para classificar por linha.

Os últimos três argumentos são opcionais e os padrões geralmente são os que você deseja.

Para classificar os dados de amostra pela primeira coluna, use esta fórmula:

=SORTE(A2:C6)

3. Função FILTRO

A função FILTER permite filtrar um intervalo em uma condição específica. Esta é a sintaxe:

=FILTRO(array, include, [if_vazio])

• array : o intervalo a ser filtrado.

• include : a condição que determina quais valores filtrar.

• if_empty : especifica o que retornar se nenhum valor atender aos critérios de filtragem (o padrão é “”).

Suponha que você queira filtrar as linhas nos dados de amostra para mostrar apenas onde o valor de vendas é maior que US$ 15. Use esta fórmula:

=FILTRO(A2:C6, C2:C6>15)

Randomizando funções do Excel

Nosso cheat intermediário mostra como usar a função RAND que produz um número aleatório entre 0 e 1.

Os usuários avançados do Excel sabem como usar as funções de randomização para gerar dados de amostra rapidamente.

1. Função ALEATÓRIO

A função ALEATÓRIO é mais flexível que RAND porque você pode especificar os números inferiores e superiores como algo diferente de 0 e 1.

Para gerar dados com números entre 1 e 100, insira esta fórmula na célula A1:

= ALEATÓRIO ENTRE(1, 100)

Em seguida, copie a célula para quantas linhas e colunas desejar. Leva segundos para produzir uma grade de números aleatórios:

2. Função RANDARRAY

Você pode estar pensando que seria bom evitar a cópia manual da função RANDBETWEEN. Para ficar superavançado, você pode usar a nova função RANDARRAY na versão mais recente do Microsoft Excel.

A sintaxe é:

RANDARRAY([linhas], [colunas], [min], [max], [número inteiro])

• linhas : número de linhas

• colunas : número de colunas

• min : menor número

• máx : número mais alto

• número inteiro : o padrão é TRUE, caso contrário, usa números decimais.

Para gerar números aleatórios entre 1 e 100 em seis linhas e nove colunas, use esta fórmula:

=RANDARRAY(6, 9, 1, 100, VERDADEIRO)

Fórmulas avançadas de previsão no Microsoft Excel

As funções de previsão do Excel são usadas para prever valores futuros com base nas tendências de dados existentes. Essas funções ajudam a identificar padrões e projetar tendências com base em seus dados.

1. Função FORECAST.ETS

A antiga função PREVISÃO foi substituída por um conjunto de funções mais recentes no Excel 2016.

Você escolhe a função com base no modelo de previsão específico que deseja. Por exemplo, a função FORECAST.ETS usa o algoritmo Exponential Smoothing .

A sintaxe é:

FORECAST.ETS(target_date, valores, linha do tempo)

• target_date : a data para a qual você deseja um valor calculado.

• valores : os dados históricos.

• linha do tempo : um intervalo de datas

Suponha que você tenha datas de 1º a 5 de janeiro na coluna A e valores de vendas na coluna B. Esta fórmula preverá o próximo valor de vendas:

=PREVISÃO.ETS(“6 de janeiro de 2023”, B2:B6, A2:A6)

2. Função TENDÊNCIA

A função TREND projeta um conjunto de valores com base no método dos mínimos quadrados. Ele retorna uma matriz. A sintaxe é:

TREND(conhecido_y, [conhecido_x], [novo_x], [const])

• known_y : intervalo de valores y

• known_x : intervalo de valores x

• new_x : intervalo de valores calculados

Freqüentemente, o known_y são os pontos de dados, enquanto o known_x são as datas.

Usando os mesmos dados do exemplo anterior, você pode inserir a fórmula abaixo na célula C2. Um conjunto de valores será gerado.

=TENDÊNCIA(B2:B6, A2:A6)

Fórmulas Estatísticas Avançadas

As funções estatísticas avançadas incluem o cálculo de percentis e quartis. Algumas funções matemáticas estão disponíveis para compatibilidade com versões anteriores, mas é recomendável usar as versões mais atualizadas.

1. Função PERCENTIL

Essa função calcula a porcentagem de pontos de dados que ficam abaixo de um valor específico. A sintaxe é:

PERCENTILE.INC(matriz, k)

• matriz : o intervalo de células

• k : o percentil de 0 a 1

Suponha que você queira calcular o percentil 70 dos dados na coluna B. Use esta fórmula:

=PERCENTIL.INC(B2:B6, 0,7)

2. Função QUARTIL

Essa função é uma variação da função percentil, mas usa quartos para dividir os dados. Esta é a sintaxe:

QUARTILE.INC(matriz, quarto)

• array : o intervalo de dados

• quart : 1 para o 25º percentil, 2 para o 50º, 3 para o 75º e 4 para o máximo.

A fórmula abaixo calculará o primeiro quartil de dados na coluna A.

=QUARTIL.INC(A2:A5, 1)

Análise avançada de dados e fórmulas de manipulação

Várias funções avançadas permitem alternar o formato dos dados, analisar distribuições de frequência e extrair dados de tabelas dinâmicas.

1. TRANSPOR

2. FREQUÊNCIA

3. GETPIVOTDATA

1. Função TRANSPOR

Às vezes, você deseja mover os dados em suas linhas para colunas e vice-versa. Você pode fazer isso manualmente ou usar a função TRANSPOSE.

Suponha que você tenha os itens “T-Shirt”, “Hoodie” e “Jeans” nas células A2, A3 e A4. Você deseja transformá-los em títulos de coluna. Esta função retorna os valores em uma única linha:

=TRANSPOSE(A2:A4)

2. Função de FREQUÊNCIA

Esta função calcula a distribuição de frequência de valores dentro de um conjunto de dados. Esta é a sintaxe:

FREQUENCY(data_array, bins_array)

• data_array : intervalo de valores.

• bins_array : os intervalos a serem usados.

Suponha que você tenha dados de vendas na coluna B e queira analisar a distribuição de frequência dos valores com base em quantos valores são:

• abaixo de 20.

• de 20 a 80.

• acima de 80.

Isso representa três compartimentos e pode ser calculado com esta fórmula:

=FREQUÊNCIA(A2:A6, {20,80})

Para saber mais sobre distribuições de frequência no Excel, confira este vídeo:

3. Função GETPIVOTDATA

Esta função permite extrair informações resumidas de tabelas dinâmicas. Esta é a sintaxe:

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field : o campo de dados ou valor que você deseja recuperar da tabela dinâmica.

• pivot_table : uma referência à tabela dinâmica.

• field1, item1, etc. : os pares campo/item a serem filtrados.

Suponha que você tenha uma tabela dinâmica com base na cor dos itens vendidos. Para extrair as vendas de itens vermelhos, use esta fórmula:

=GETPIVOTDATA(“Vendas”, A1, “Cor”, “Vermelho”)

Tratamento Avançado de Erros

Mesmo as fórmulas mais básicas do Excel podem produzir erros. Usuários intermediários devem saber como usar ISERROR para lidar com erros. Usuários avançados também devem estar familiarizados com a função ERROR.TYPE para identificação de erros.

A função ERROR.TYPE ajuda a identificar o tipo específico de erro em uma célula ou fórmula.

Ele retorna um valor numérico correspondente a diferentes tipos de erro, como #N/A, #VALUE!, #REF! e muito mais.

Suponha que você tenha um erro na célula A1 e queira identificar seu tipo de erro. A fórmula a seguir retornará o número que corresponde ao erro específico:

=ERRO.TIPO(A1)

Você pode combinar isso com várias funções para responder de forma diferente, dependendo do tipo de erro. Estes são os erros mais comuns e seus valores:

1. #NULO! (nenhuma célula comum encontrada em um intervalo)

2. #DIV/0! ( divisão por zero ou uma célula vazia)

3. #VALOR! (tipo de dado ou argumento inadequado em uma fórmula)

4. #REF! (uma célula referenciada foi excluída ou há uma referência circular )

5. #NOME? (O Excel não reconhece a função ou intervalo)

6. #NUM! (valor numérico inválido)

7. #N/A (valor não encontrado)

Suponha que você queira lidar com três tipos de erros específicos. Use esta fórmula para exibir uma mensagem de erro específica com base no tipo:

=SE(ERRO.TIPO(C2), SE(ERRO.TIPO(C2)=2, “Erro de divisão por zero”, SE(ERRO.TIPO(C2)=3, “Erro de valor inválido”, SE(ERRO.TIPO(C2) )=7, “Erro de valor não encontrado”, “Outro erro”))), C2)

Fórmulas de Pesquisa Avançada

Nossas folhas de dicas para iniciantes e intermediários cobriram uma seleção de funções de pesquisa . Aqui estão algumas opções avançadas:

1. XLOOKUP

2. XMATCH

1. Função XLOOKUP

Esta função de pesquisa permite pesquisar um valor em um intervalo e retornar um valor correspondente de outra coluna ou intervalo.

Ele oferece mais versatilidade do que funções de pesquisa mais simples, como VLOOKUP . Esta é a sintaxe:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : o valor que você deseja pesquisar.

• lookup_array : o intervalo para a pesquisa.

• return_array : o intervalo que mostrará o valor correspondente.

• match_mode : correspondência exata (0), próxima menor (1), próxima maior (-1) ou correspondência curinga (2).

• search_mode : -1 para cima para baixo, 1 para baixo para cima ou 2 para pesquisa binária.

• if_not_found : define o valor a ser retornado se nenhuma correspondência for encontrada.

Suponha que você queira pesquisar em um intervalo de dados a primeira ocorrência de um item de roupa e retornar os valores das vendas. Esta fórmula procurará o texto “Moletom” e retornará o valor na célula adjacente, se encontrado:

=XLOOKUP(“Moletom com capuz”, A2:A6, B2:B6)

2. Função XMATCH

Esta função permite encontrar a posição de um valor especificado dentro de um intervalo ou matriz. Esta é a sintaxe:

XMATCH(lookup_value, lookup_array, [match_type], [search_mode])

• lookup_value : o valor que você deseja encontrar.

• lookup_array : O intervalo que você deseja pesquisar.

• match_type : correspondência exata (0), próximo menor (-1), próximo maior (1).

• search_mode : pesquisa binária (1) ou pesquisa linear (2).

Suponha que você deseja localizar a primeira ocorrência de um item amarelo em um intervalo na coluna B. Use esta fórmula:

=XMATCH(“Amarelo”, B2:B6, 0)

Pensamentos finais

Esta folha de dicas cobriu uma ampla gama de funções, desde análise estatística, fórmulas de pesquisa, técnicas de manipulação de dados e estratégias de tratamento de erros.

Os exemplos e explicações fornecidos ajudam a desmistificar essas fórmulas avançadas, tornando-as acessíveis mesmo para aqueles com experiência limitada.

À medida que você começa a incorporá-los em suas tarefas do Excel, você está no caminho certo para aumentar suas habilidades no Excel para um nível avançado.

Mas lembre-se, esta folha de dicas é apenas a ponta do iceberg. As coisas realmente incríveis acontecem quando você é criativo, mistura e combina essas fórmulas e as adapta para resolver seus desafios únicos. O Excel é como uma tela, e essas fórmulas são sua paleta — então vá em frente, pinte sua obra-prima!