Função Excel OFFSET (Exemplo + Vídeo)
Quando usar a função Excel OFFSET
A função OFFSET do Excel pode ser usada quando você deseja obter uma referência que desloca o número especificado de linhas e colunas do ponto inicial.
O que retorna
Ele retorna a referência para a qual a função OFFSET aponta.
Sintaxe
= OFFSET (referência, linhas, colunas, [altura], [largura])
Argumentos de entrada
- referência - A referência da qual você deseja compensar. Pode ser uma referência de célula ou um intervalo de células adjacentes.
- filas - o número de linhas a serem compensadas. Se você usar um número positivo, ele se desloca para as linhas abaixo, e se um número negativo é usado, ele se desloca para as linhas acima.
- cols - o número de colunas a serem compensadas. Se você usar um número positivo, ele será compensado para as colunas à direita, e se um número negativo for usado, ele será compensado para as colunas à esquerda.
- [altura] - este é um número que representa o número de linhas na referência retornada.
- [largura] - este é um número que representa o número de colunas na referência retornada.
Compreendendo os princípios básicos da função OFFSET do Excel
A função OFFSET do Excel é possivelmente uma das funções mais confusas do Excel.
Vejamos um exemplo simples de um jogo de xadrez. Existe uma peça no xadrez chamada Torre (também conhecida como torre, marquês ou reitor).
[Imagem cortesia: Wikipedia maravilhosa]
Agora, como todas as outras peças de xadrez, uma Torre tem um caminho fixo no qual pode se mover no tabuleiro. Pode ir direto (para a direita / esquerda ou para cima / baixo no tabuleiro), mas não pode ir na diagonal.
Por exemplo, suponha que temos um tabuleiro de xadrez no Excel (como mostrado abaixo), em uma determinada caixa (D5 neste caso), a Torre só pode ir nas quatro direções destacadas.
Agora, se eu pedir que você leve a torre da posição atual para aquela destacada em amarelo, o que você dirá à torre?
Você vai pedir para dar dois passos para baixo e dois passos para a direita … não é?
E isso é uma aproximação de como a função OFFSET funciona.
Agora vamos ver o que isso significa no Excel. Eu quero começar com a célula D5 (que é onde a Torre está) e então ir duas linhas para baixo e duas colunas para a direita e buscar o valor da célula lá.
A fórmula seria:
= OFFSET (de onde começar, quantas linhas abaixo, quantas colunas à direita)
Como você pode ver, a fórmula no exemplo acima na célula J1 é = OFFSET (D5,2,2).
Começou em D5 e depois desceu duas linhas e duas colunas à direita e atingiu a célula F7. Em seguida, ele retornou o valor na célula F7.
No exemplo acima, vimos a função OFFSET com 3 argumentos. Mas existem mais dois argumentos opcionais que podem ser usados.
Vejamos um exemplo simples aqui:
Suponha que você deseja usar a referência à célula A1 (em amarelo) e deseja referir-se a todo o intervalo destacado em azul (C2: E4) em uma fórmula.
Como você faria isso usando um teclado? Você deve primeiro ir para a célula C2 e, em seguida, selecionar todas as células em C2: E4.
Agora vamos ver como fazer isso usando a fórmula OFFSET:
= OFFSET (A1,1,2,3,3)
Se você usar esta fórmula em uma célula, ela retornará um #VALUE! erro, mas se você entrar no modo de edição, selecionar a fórmula e pressionar F9, verá que ela retorna todos os valores destacados em azul.
Agora vamos ver como essa fórmula funciona:
= OFFSET (A1,1,2,3,3)
- O primeiro argumento é a célula onde deve começar.
- O segundo argumento é 1, que informa ao Excel para retornar uma referência que foi DESLOCADA em 1 linha.
- O terceiro argumento é 2, que informa ao Excel para retornar uma referência que foi OFFSET por 2 colunas.
- O quarto argumento é 3. Isso especifica que a referência deve cobrir 3 linhas. Isso é chamado de argumento de altura.
- O quinto argumento é 3. Isso especifica que a referência deve abranger 3 colunas. Isso é chamado de argumento de largura.
Agora que você tem a referência a um intervalo de células (C2: E4), pode usá-la em outra função (como SUM, COUNT, MAX, AVERAGE).
Esperamos que você tenha um bom conhecimento básico do uso da função OFFSET do Excel. Agora vamos dar uma olhada em alguns exemplos práticos do uso da função OFFSET.
Função Excel OFFSET - Exemplos
Aqui estão dois exemplos de uso da função Excel OFFSET.
Exemplo 1 - Encontrando a última célula preenchida na coluna
Suponha que você tenha alguns dados em uma coluna, conforme mostrado abaixo:
Para encontrar a última célula da coluna, use a seguinte fórmula:
= OFFSET (A1, COUNT (A: A) -1,0)
Esta fórmula assume que não há valores além dos mostrados e que essas células não têm uma célula em branco. Ele funciona contando o número total de células que estão preenchidas e desloca a célula A1 de acordo.
Por exemplo, neste caso, existem 8 valores, então COUNT (A: A) retorna 8. Compensamos a célula A1 em 7 para obter o último valor.
Exemplo 2 - Criação de um menu suspenso dinâmico
Você pode estender o conceito mostrado no Exemplo 1 para criar intervalos nomeados dinâmicos. Isso pode ser útil se você estiver usando os intervalos nomeados em fórmulas ou na criação de menus suspensos e provavelmente adicionar / excluir dados da referência que faz o intervalo nomeado.
Aqui está um exemplo:
Observe que a lista suspensa se ajusta automaticamente quando o ano é adicionado ou removido.
Isso acontece porque a fórmula usada para criar o menu suspenso é dinâmica e identifica qualquer adição ou exclusão e ajusta o intervalo de acordo.
Aqui está como fazer isso:
- Selecione a célula onde deseja inserir o menu suspenso.
- Vá para Dados -> Ferramentas de dados -> Validação de dados.
- Na caixa de diálogo Validação de dados, na guia Configurações, selecione Lista no menu suspenso.
- Na fonte, insira a seguinte fórmula: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Clique OK.
Vamos ver como essa fórmula funciona:
- Os três primeiros argumentos da função OFFSET são A1, 0 e 0. Isso significa essencialmente que ela retornaria a mesma célula de referência (que é A1).
- O quarto argumento é para altura e aqui a função COUNT retorna o número total de itens na lista. Ele assume que não há espaços em branco na lista.
- O quinto argumento é 1, o que indica que a largura da coluna deve ser um.
Notas Adicionais:
- OFFSET é uma função volátil (use com cuidado).
- Ele recalcula sempre que a pasta de trabalho do Excel é aberta ou sempre que um cálculo é acionado na planilha. Isso pode aumentar o tempo de processamento e tornar sua pasta de trabalho mais lenta.
- Se o valor da altura ou largura for omitido, ele será considerado o da referência.
- Se filas e cols são números negativos, então a direção do deslocamento é invertida.
Alternativas de função OFFSET do Excel
Devido a algumas das limitações da função OFFSET do Excel, você pode querer considerar alternativas a ela:
- Função INDEX: a função INDEX também pode ser usada para retornar uma referência de célula. Clique aqui para ver um exemplo de como criar um intervalo nomeado dinâmico usando a função INDEX.
- Tabela do Excel: Se você usar referências estruturadas na Tabela do Excel, não precisa se preocupar com a adição de novos dados e com a necessidade de ajustar as fórmulas.
Função Excel OFFSET - Vídeo Tutorial
- Função VLOOKUP do Excel.
- Função HLOOKUP do Excel.
- Função INDEX do Excel.
- Função INDIRETA do Excel.
- Função Excel MATCH.
