Função OFFSET do Excel (sumário)
- OFFSET no Excel
- Fórmula OFFSET no Excel
- Como usar a função OFFSET no Excel?
OFFSET no Excel
OFFSET A função no Excel ajuda o usuário a retornar uma célula ou intervalo de células que é um número especificado. de linhas e colunas de uma célula. Podemos especificar o não. de linhas e não. de colunas a serem retornadas.
Abaixo está a fórmula OFFSET no Excel:
A função OFFSET no Excel consiste nos seguintes argumentos:
- Referência: é o argumento a partir do qual queremos basear o deslocamento. Pode ser uma referência de célula ou um intervalo de células adjacentes, se não Deslocamento retorna o # VALOR! Valor do erro.
- Linhas: é o não. de linhas para compensar. Se usarmos um número positivo, ele compensa as linhas abaixo e, se um número negativo for usado, compensa as linhas acima.
- Colunas: É para não. de colunas para compensar. Portanto, é o mesmo conceito de linhas, se usarmos um número positivo, ele compensa as colunas à direita e, se um número negativo for usado, compensa as colunas à esquerda.
- Altura: este argumento é opcional. Deve ser um número positivo. É um número que representa o número de linhas na referência retornada.
- Largura: este também é um argumento opcional. Deve ser um número positivo. É um número que representa o número de colunas na referência retornada.
Notas:
- A função OFFSET no Excel retorna o # REF! valor de erro, se linhas e colunas deslocam a referência sobre a borda da planilha.
- Na Função OFFSET, é suposto ter a mesma altura ou largura que uma referência, se a altura ou a largura forem omitidas.
- OFFSET retorna uma referência, na verdade não move nenhuma célula ou intervalo de células ou altera a seleção. Pode ser usado com qualquer função que espera um argumento de referência.
Como usar a função OFFSET no Excel?
Função OFFSET no Excel é muito simples e fácil de usar. Vamos entender o funcionamento da Função OFFSET no Excel através de um exemplo de Fórmula OFFSET.
Você pode baixar este modelo do Excel da função OFFSET aqui - Modelo do Excel da função OFFSETExemplo 1
A fórmula Offset retorna uma referência de célula com base em um ponto de partida, linhas e colunas que especificamos. Podemos ver no exemplo abaixo:
= DESLOCAMENTO (A1, 3, 1)
A fórmula informa ao Excel para considerar a célula A1 como ponto de partida (referência) e, em seguida, mova 3 linhas para baixo (linhas) e 1 coluna para a esquerda (argumento de colunas). Depois disso, a fórmula OFFSET retorna o valor na célula B4.
A foto à esquerda mostra que a rota da função e a captura de tela à direita demonstram como podemos usar a fórmula OFFSET em dados em tempo real. A diferença entre as duas fórmulas é que a segunda (à direita) inclui uma referência de célula (D1) no argumento da linha. Mas como a célula D1 contém o número 1, e exatamente o mesmo número aparece no argumento de linhas da primeira fórmula, ambos retornariam um resultado idêntico - o valor em B2.
Exemplo # 2 Uso da Função OFFSET para Soma
Como sabemos que a função OFFSET também pode ser usada com a outra função do Excel, portanto, neste exemplo, veremos com a função SUM.
Na foto acima, suponha que tenhamos que descobrir a soma das marcas para que também possamos usar a função OFFSET.
A fórmula está abaixo:
Portanto, na fórmula acima, D5 é a primeira célula em que os dados começam, que é uma referência como célula inicial. Depois disso, o valor das linhas e da coluna é 0, para que possamos colocá-lo como 0, 0. Depois, há 4, o que significa 4 linhas abaixo da referência para a qual o Excel precisa calcular a soma e, finalmente, é 1, significa que há 1 coluna como largura.
Podemos consultar a foto acima para correlacionar o exemplo explicado.
E abaixo está o resultado:
Só temos que pressionar Enter depois de inserir uma fórmula na célula necessária e, em seguida, obter o resultado.
Pontos a serem lembrados sobre a função OFFSET no Excel
- A função OFFSET no Excel apenas retorna uma referência, não move nenhuma célula ou intervalo de células na real.
- Se a fórmula OFFSET retornar um intervalo de células, as linhas e colunas sempre se referirão à célula superior esquerda no intervalo retornado.
- A referência deve incluir uma célula ou intervalo de células, caso contrário, a fórmula retornará o erro #VALUE.
- Se as linhas e colunas especificadas acima da borda da planilha, a fórmula Offset no Excel retornará a # REF! erro.
- A função OFFSET pode ser usada em qualquer outra função do Excel que aceite uma referência de célula ou intervalo em seus argumentos.
Por exemplo, se estivermos usando a fórmula = OFFSET (A1, 3, 1, 1, 3) por si só, ela fornecerá um #VALUE! Erro, pois um intervalo de retorno (1 linha, 3 colunas) não cabe em uma célula. No entanto, se agruparmos na função SUM como abaixo:
= SOMA (DESLOCAMENTO (A1, 3, 1, 1, 3))
A fórmula Offset retorna a soma dos valores em uma linha por um intervalo de 3 colunas 3 linhas abaixo e 1 coluna à direita da célula A1, ou seja, o total de valores nas células B4: D4.
Como podemos ver, o total de 36 + 63 + 82 do Mar destacado é igual a 181, que ocorreu como resultado no G2 após a aplicação da fórmula de soma e deslocamento. (destacado em amarelo)
Uso da função OFFSET no Excel
Como vimos que o que a função OFFSET realmente faz com a ajuda do exemplo acima, podemos ter perguntas que: Por que incomodar a fórmula OFFSET que é um pouco complexa, por que não usar simplesmente uma referência direta como sum ou B4: D4?
A função OFFSET no Excel é muito boa para abaixo:
- Obtendo o intervalo da célula inicial:
Às vezes, não sabemos o endereço exato ou real do intervalo, apenas sabemos de onde ele começa a partir de uma determinada célula. Nesse caso, podemos usar a função OFFSET, que é fácil de executar.
- Criando faixa dinâmica:
As referências como o exemplo acima B1: C4 são estáticas, significa que sempre se referem a um intervalo determinado ou fixo. Mas, em alguns casos, as tarefas são mais fáceis de executar com intervalos dinâmicos. Isso ajuda especialmente quando trabalhamos com a alteração de dados. Por exemplo, poderíamos ter uma planilha em que uma nova linha ou coluna é inserida ou adicionada todos os dias / semana / mês; nesse caso, a função OFFSET ajudará.
Limitações e alternativas da função OFFSET no Excel
Como toda e qualquer fórmula no Excel tem suas próprias limitações e alternativas, vimos como e quando usar a Função OFFSET no Excel.
Abaixo estão as limitações críticas da função OFFSET no Excel:
- Função com fome de recursos:
Isso significa que sempre que houver uma alteração nos dados de origem, a fórmula será recalculada para todo o conjunto, o que mantém o Excel ocupado por mais tempo. Se houver uma pequena planilha, isso não afetará muito, mas se houver várias planilhas, o Excel poderá levar um tempo para recalcular.
- Dificuldade na revisão:
Como sabemos, as referências retornadas pela função Offset são dinâmicas ou alteradas e podem conter uma fórmula grande, que pode ser difícil de corrigir ou editar conforme o requisito.
Alternativas da função OFFSET no Excel:
- Função INDEX:
A função INDEX no Excel também pode ser usada para criar um intervalo dinâmico de referências. Não é exatamente o mesmo que OFFSET. Mas, como OFFSET, a função INDEX não é tão volátil, portanto, não desacelera o Excel, que é uma limitação para OFFSET.
- Função INDIRETA:
Também podemos usar a função INDIRETO para criar um intervalo dinâmico de referências de várias fontes, como valores de célula , texto e os intervalos nomeados.
Artigos recomendados
Este foi um guia para compensar no Excel. Aqui discutimos a fórmula OFFSET no Excel e 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 -
- MS Excel: Função LOOKUP
- Melhor exemplo da função XIRR do Excel
- Guia surpreendente sobre a função NÃO no Excel
- IPMT no Excel com exemplos