Como usar a função OFFSET do Excel -

Resumo

A função OFFSET do Excel retorna uma referência a um intervalo construído com cinco entradas: (1) um ponto inicial, (2) um deslocamento de linha, (3) um deslocamento de coluna, (4) uma altura em linhas, (5) uma largura em colunas. OFFSET é útil em fórmulas que requerem uma faixa dinâmica.

Objetivo

Crie um deslocamento de referência a partir de um determinado ponto de partida

Valor de retorno

Uma referência de célula.

Sintaxe

= OFFSET (referência, linhas, colunas, (altura), (largura))

Argumentos

  • referência - O ponto de partida, fornecido como uma referência ou intervalo de célula.
  • linhas - o número de linhas a serem deslocadas abaixo da referência inicial.
  • cols - O número de colunas a serem deslocadas à direita da referência inicial.
  • height - (opcional) A altura em linhas da referência retornada.
  • largura - (opcional) a largura em colunas da referência retornada.

Versão

Excel 2003

Notas de uso

A função OFFSET do Excel retorna um intervalo dinâmico construído com cinco entradas: (1) um ponto inicial, (2) um deslocamento de linha, (3) um deslocamento de coluna, (4) uma altura em linhas, (5) uma largura em colunas.

O ponto de partida (o argumento de referência ) pode ser uma célula ou um intervalo de células. Os argumentos rows e cols são o número de células a serem "compensadas" do ponto inicial. Os argumentos de altura e largura são opcionais e determinam o tamanho do intervalo que é criado. Quando a altura e a largura são omitidas, o padrão é a altura e largura de referência .

Por exemplo, para fazer referência a C5 começando em A1, a referência é A1, rows é 4 e cols é 2:

=OFFSET(A1,4,2) // returns reference to C5

Para fazer referência a C1: C5 de A1, a referência é A1, rows é 0, cols é 2, height é 5 e largura é 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Observação: a largura pode ser omitida, pois o padrão é 1.

É comum ver OFFSET envolvido em outra função que espera um intervalo. Por exemplo, para SUM C1: C5, começando em A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

O principal objetivo do OFFSET é permitir que as fórmulas se ajustem dinamicamente aos dados disponíveis ou à entrada do usuário. A função OFFSET pode ser usada para construir um intervalo nomeado dinâmico para gráficos ou tabelas dinâmicas, para garantir que os dados de origem estejam sempre atualizados.

Observação: a documentação do Excel afirma que a altura e a largura não podem ser negativas, mas os valores negativos parecem ter funcionado bem desde o início dos anos 1990. A função OFFSET no Planilhas Google não permite um valor negativo para os argumentos de altura ou largura.

Exemplos

Os exemplos abaixo mostram como OFFSET pode ser configurado para retornar diferentes tipos de intervalos. Essas telas foram feitas com o Excel 365, portanto, OFFSET retorna uma matriz dinâmica quando o resultado é mais de uma célula. Em versões anteriores do Excel, você pode usar a tecla F9 para verificar os resultados retornados de OFFSET.

Exemplo 1

Na tela abaixo, usamos OFFSET para retornar o terceiro valor (março) na segunda coluna (oeste). A fórmula em H4 é:

=OFFSET(B3,3,2) // returns D6

Exemplo # 2

Na tela abaixo, usamos OFFSET para retornar o último valor (junho) na terceira coluna (Norte). A fórmula em H4 é:

=OFFSET(B3,6,3) // returns E9

Exemplo # 3

Abaixo, usamos OFFSET para retornar todos os valores na terceira coluna (Norte). A fórmula em H4 é:

=OFFSET(B3,1,3,6) // returns E4:E9

Exemplo # 4

Abaixo, usamos OFFSET para retornar todos os valores de maio (quinta linha). A fórmula em H4 é:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Exemplo # 5

Abaixo, usamos OFFSET para retornar os valores de abril, maio e junho para a região Oeste. A fórmula em H4 é:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Exemplo # 6

Abaixo, usamos OFFSET para retornar os valores de abril, maio e junho para oeste e norte. A fórmula em H4 é:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Notas

  • OFFSET retorna apenas uma referência, nenhuma célula é movida.
  • Ambas as linhas e cols podem ser fornecidas como números negativos para inverter a sua direcção de afastamento da normais - negativos cols deslocado para a esquerda, e negativos linhas desvio acima.
  • OFFSET é uma "função volátil" - será recalculada a cada alteração da planilha. As funções voláteis podem fazer com que pastas de trabalho maiores e mais complexas funcionem lentamente.
  • OFFSET exibirá o #REF! valor de erro se o deslocamento estiver fora da borda da planilha.
  • Quando a altura ou largura é omitida, a altura e a largura da referência são usadas.
  • OFFSET pode ser usado com qualquer outra função que espera receber uma referência.
  • A documentação do Excel diz que a altura e a largura não podem ser negativas, mas os valores negativos funcionam.

Vídeos relacionados

Como criar um intervalo de nome dinâmico com OFFSET Neste vídeo, veremos como criar um intervalo de nome dinâmico usando a função OFFSET, a maneira mais comum de criar um intervalo de nome dinâmico com uma fórmula.

Artigos interessantes...