Fórmula OFFSET do Excel (sumário)
- O que é a Fórmula OFFSET no Excel?
- Como usar a fórmula OFFSET no Excel?
O que é a Fórmula OFFSET no Excel?
OFFSET Formula no Excel fornece uma célula ou um intervalo de células retangulares dinâmicas como uma saída que é um subconjunto do intervalo total fornecido com um número especificado de linhas, colunas, altura e largura.
Sintaxe da fórmula OFFSET no Excel
Explicação da função OFFSET no Excel:
- referência: O ponto de partida para OFFSET ou podemos dizer um intervalo de células que pode ser considerado a base para OFFSET.
- linhas : o número de linhas para DESLOCAR abaixo ou acima da referência base.
- cols: o número de colunas para OFFSET à direita ou esquerda da referência base.
- height: Número de linhas na referência retornada.
- width: Número de colunas na referência retornada.
A altura e a largura são os parâmetros opcionais para esta função e podem ser usados para determinar o tamanho da referência criada.
Como usar a fórmula OFFSET no Excel?
Agora aprenderemos como escrever uma fórmula de deslocamento para obter o valor da pesquisa. Vamos entender a fórmula de deslocamento com alguns exemplos no Excel.
Você pode fazer o download deste modelo OFFSET Formula Excel aqui - Modelo OFFSET Formula ExcelFórmula OFFSET do Excel - Exemplo # 1
Suponha que tenhamos os dados abaixo.
- Na célula E2, insira a seguinte fórmula:
= DESLOCAMENTO (A1, 11, 1)
- Depois de concluir a fórmula, pressione Enter e veja a saída.
O que essa fórmula fez é: ela tomou referência da célula A1 (Ano) e desceu 11 linhas após A1 (ou seja, linha nº 12) e depois ganhou o valor que é coordenada da 11ª linha após A1 e primeira coluna ( coluna A = 0 coluna B = 1. Nesse caso, como sabemos, podemos ir tanto para a direita quanto para a esquerda). Que é $ 141, 27 milhões.
Fórmula OFFSET do Excel - Exemplo # 2
Agora veremos outro exemplo de célula de referência para um ano específico. Coloque a seguinte fórmula na célula E3:
= DESLOCAMENTO (A1, 11, 0)
- Depois de concluir a fórmula, pressione Enter e veja a saída.
Se você viu a fórmula, é a mesma da anterior, a única alteração está na referência da coluna. A referência da coluna 0 (Zero) significa que o valor será capturado da coluna A pela 11ª linha após A1 (que é 2010).
Fórmula OFFSET do Excel - Exemplo # 3
Neste exemplo, calcularemos a soma das últimas N linhas usando a função OFFSET do Excel. A fórmula geral para resumir os últimos N números é:
= SOMA (DESLOCAMENTO (A1, CONTAGEM (A: A), 0, -N))
Para resumir as últimas N observações de seus dados, você pode usar a função SUM juntamente com as funções OFFSET e COUNT juntas. Neste exemplo, somaremos os últimos 5 pontos de dados. Observe que quando dizemos N, é uma generalização de qualquer valor inteiro. Assim, você pode calcular os últimos 3, últimos 4, últimos 5, etc., dependendo do seu conjunto de dados e de seus requisitos.
Vamos fazê-lo para o nosso conjunto de dados:
- Insira a seguinte fórmula na célula E2 do arquivo do Excel.
= SUM (OFFSET (B1, CONTAGEM (B: B), 0, -5))
- Clique em Enter para ver a saída.
Como já vimos, a função OFFSET do Excel gera intervalos dinâmicos retangulares quando recebe uma referência inicial.
- Nesse caso, a referência inicial é fornecida em termos de B1 (como um primeiro argumento para a função OFFSET).
- Usamos a função COUNT porque precisamos da soma das observações do último valor. A função COUNT ajuda a OFFSET a determinar quantas observações (linhas) existem na coluna B. Conta todos os valores numéricos na coluna B (19 é a contagem).
- Com contagem como 19, a fórmula OFFSET se torna:
= DESLOCAMENTO (B1, 19, 0, -5)
- Essa fórmula inicia o deslocamento em B1 com 19 linhas e a mesma coluna (0). O parâmetro de altura -5 ajuda essa função a estender o intervalo na direção inversa em 5 linhas (consulte o sinal negativo associado a 5).
- O intervalo dinâmico das últimas cinco observações é fornecido como uma entrada para a função soma. O que resulta em somar os da coluna B.
Fórmula OFFSET do Excel - Exemplo # 4
Neste exemplo, calcularemos as vendas medianas dos últimos 3 anos. A função OFFSET pode ser usada como argumento para calcular o mesmo.
- Coloque a seguinte fórmula na célula E2:
= MÉDIA (DESLOCAMENTO (B1, CONTAGEM (B: B), 0, -3))
- Pressione Enter para ver a saída.
Esta fórmula funciona nas linhas semelhantes à anterior.
- OFFSET Cria um intervalo dinâmico das últimas três observações da coluna B com a ajuda da função COUNT (= OFFSET (A1, 19, 0, -3)).
- Isso pode ser fornecido como uma saída para a função MEDIAN. O que classifica as observações em ordem crescente ou decrescente e seleciona o valor mais médio, como você vê na saída (US $ 212, 07).
Fórmula OFFSET do Excel - Exemplo # 5
Aqui, calcularemos o ano mediano usando as funções MEDIAN e OFFSET.
- Use a seguinte fórmula para o mesmo na célula E3:
= MÉDIA (DESLOCAMENTO (A1, CONTAGEM (A: A), 0, -3))
- Clique em Enter para ver a saída.
A mesma coisa aconteceu aqui. A fórmula adotou um intervalo dinâmico criado pela função OFFSET com a ajuda de COUNT. E o usou como argumento para uma função MEDIAN que classificou os valores do ano em ordem crescente ou decrescente e, em seguida, forneceu saída como 2017.
O uso da fórmula OFFSET está na criação dos intervalos dinâmicos. É sempre melhor trabalhar em faixas dinâmicas em vez de estáticas. Como você pode estar tendo dados que são adicionados de vez em quando. Por exemplo, mestre de funcionários. O Cadastro de funcionários sempre aumentará de vez em quando, pois deve haver novos funcionários entrando. Toda vez que usar a mesma fórmula não faz sentido. Em vez disso, crie um intervalo dinâmico usando a função OFFSET e veja a mágica. Certamente você economizará um tempo.
Coisas para lembrar
- A função OFFSET retorna apenas uma referência a um intervalo específico de células no Excel. Ele não move as células de suas posições originais.
- Por padrão, essa função funciona na parte superior (em termos de linhas) e no lado direito (em termos de colunas). No entanto, ele pode ser fornecido com argumentos negativos para reverter a funcionalidade padrão. O argumento de linha negativa-like ajuda a compensar os dados de baixo para cima e o argumento col negativo ajuda a compensar os dados para a esquerda.
- A fórmula OFFSET pode diminuir a funcionalidade do Excel, pois é uma fórmula recalculada toda vez que há uma alteração na planilha.
- O erro #REF será gerado se o intervalo estiver fora do intervalo da pasta de trabalho.
- Qualquer outra função que espera uma referência pode usar a função OFFSET nela.
Artigos recomendados
Este é um guia para a fórmula OFFSET no Excel. Aqui discutimos como usar a função OFFSET no Excel, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -
- Como quebrar texto no Excel?
- Guia simples de classificação no Excel
- Função LOOKUP no Excel
- Como usar a função FIND no Excel?