Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função Excel OFFSET no Excel para criar um deslocamento de referência de uma célula inicial.
Visão geral da função OFFSET
A função OFFSET começa com uma referência de célula definida e retorna uma referência de célula com um número específico de linhas e colunas deslocadas da referência original. As referências podem ser uma célula ou um intervalo de células. O deslocamento também permite redimensionar a referência para um determinado número de linhas / colunas.
(Observe como as entradas da fórmula aparecem)
Sintaxe e entradas da função IFERROR:
| 1 | = OFFSET (referência, linhas, colunas, altura, largura) |
referência - A referência de célula inicial da qual você deseja compensar.
filas - O número de linhas a serem compensadas.
cols - O número de colunas a serem compensadas.
altura - OPCIONAL: Ajuste o número de linhas na referência.
largura - OPCIONAL: Ajuste o número de colunas na referência.
Qual é a função OFFSET?
A função OFFSET é uma das funções de planilha mais poderosas, pois pode ser bastante versátil no que cria. Ele dá ao usuário a capacidade de definir uma célula ou intervalo em uma variedade de posições e tamanhos.
CUIDADO: A função OFFSET é uma das funções voláteis. Na maioria das vezes, quando você está trabalhando em sua planilha, o computador só recalculará uma fórmula se as entradas tiverem seus valores alterados. Uma função volátil, no entanto, recalcula cada vez que você faz uma alteração em qualquer célula. Deve-se ter cuidado para garantir que você não cause um grande tempo de recálculo devido ao uso excessivo da função volátil ou por ter muitas células dependentes do resultado de uma função volátil.
Exemplos básicos de linha
Em cada uso da função OFFSET, você precisa fornecer um ponto de partida ou âncora. Vejamos esta tabela para ajudar a entender isso:
Usaremos "Bob" na célula B3 como nosso ponto de ancoragem. Se quiséssemos pegar o valor logo abaixo (Charlie), diríamos que queremos mudar a linha em 1. Nossa fórmula seria semelhante a
| 1 | = OFFSET (B3, 1) |
Se quiséssemos mudar para cima, isso seria uma mudança negativa. Você pode pensar nisso como o número da linha está diminuindo, então precisamos subtrair. Assim, para obter o valor acima (Adam), escreveríamos
| 1 | = OFFSET (B2, -1) |
Exemplos de coluna básica
Continuando a ideia do exemplo anterior, adicionaremos outra coluna à nossa tabela.
Se quiséssemos pegar o professor para Bob, poderíamos usar a fórmula
| 1 | = OFFSET (B2, 0, 1) |
Neste caso, dissemos que queremos deslocar zero linhas (também conhecido como permanecer na mesma linha), mas queremos deslocar 1 coluna. Para colunas, um número positivo significa deslocamento para a direita e números negativos significam deslocamento para a esquerda.
OFFSET e MATCH
Suponha que você tenha várias colunas de dados e queira dar ao usuário a capacidade de escolher de qual coluna buscar os resultados. Você pode usar a função INDEX ou pode usar OFFSET. Uma vez que MATCH retornará a posição relativa de um valor, precisaremos ter certeza de que o ponto de ancoragem está à esquerda do nosso primeiro valor possível. Considere o seguinte layout:
Em B2, escreveremos esta fórmula:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
O MATCH vai parecer “fevereiro” no intervalo C1: F1 e encontrá-lo no 2WL célula. O OFFSET irá então deslocar 1 coluna para a direita de B2 e agarrar o valor desejado de 9. Observe que OFFSET não tem problemas em usar a mesma célula que contém a fórmula como o ponto de ancoragem.
NOTA: Esta técnica pode ser usada como um substituto para VLOOKUP ou HLOOKUP quando você deseja retornar um valor da esquerda / acima do intervalo de pesquisa. Isso ocorre porque OFFSET pode fazer deslocamentos negativos.
OFFSET para obter um alcance
Você pode usar os 4º e 5º argumentos na função OFFSET para retornar um intervalo em vez de apenas uma única célula. Suponha que você queira somar 3 colunas nesta tabela.
| 1 | = MÉDIA (OFFSET (A1, CORRESPONDÊNCIA (F2, A2: A5,0), 1,1,3)) |
Em F2, selecionamos o nome de um aluno para o qual desejamos obter suas pontuações médias nos testes. Para fazer isso, vamos usar a fórmula
| 1 | = AVERAGE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
O MATCH irá pesquisar nosso nome na coluna A e retornar a posição relativa, que é 3 em nosso exemplo. Vamos ver como isso será avaliado. Primeiro, o OFFSET vai baixa 3 linhas de A1 e 1 coluna para o direito de A1. Isso nos coloca na célula B3.
| 1 | = MÉDIA (OFFSET (A1, 3, 1, 1, 3)) |
Em seguida, vamos redimensionar o intervalo. O novo intervalo terá B3 como célula superior esquerda. Terá 1 linha de altura e 3 colunas de altura, dando-nos o intervalo B4: D4.
| 1 | = MÉDIA (OFFSET (A1,3, 1, 1, 3)) |
Observe que, embora você possa legitimamente colocar valores negativos nos argumentos de deslocamento, só pode usar valores não negativos nos argumentos de dimensionamento.
No final, nossa função AVERAGE vê:
| 1 | = MÉDIA (B4: D4) |
Assim, obtemos nossa solução de 86,67
OFFSET com SUM dinâmico
Como OFFSET é usado para encontrar uma referência, em vez de apontar para a célula diretamente, é mais útil quando você está lidando com dados que têm linhas adicionadas ou excluídas. Considere a seguinte tabela com um Total na parte inferior
| 1 | = SOMA (B2: B4) |
Se tivéssemos usado uma fórmula SUM básica aqui de “= SUM (B2: B4)” e, em seguida, inserido uma nova linha para adicionar um registro para Bill, teríamos a resposta errada
Em vez disso, vamos pensar em como resolver isso do ponto de vista do Total. Nós realmente queremos pegar tudo, desde a célula B2 até a célula um pouco acima do nosso total. A maneira como podemos escrever isso em uma fórmula é fazer um deslocamento de linha de -1. Assim, usamos isso como a fórmula para nosso total na célula B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Esta fórmula faz o que acabamos de descrever: começa em B2 e vai para 1 célula acima de nossa célula total. Você pode ver como, depois de adicionar os dados de Bill, nosso total é atualizado corretamente.
OFFSET para obter os últimos N itens
Digamos que você esteja registrando vendas mensais, mas deseja poder ver os últimos 3 meses. Em vez de ter que atualizar manualmente suas fórmulas para continuar ajustando conforme novos dados são adicionados, você pode usar a função OFFSET com COUNT.
Já mostramos como você pode usar OFFSET para obter um intervalo de células. Para determinar quantas células precisamos mudar, usaremos COUNT para descobrir quantas números estão na coluna B. Vejamos nossa tabela de exemplo.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Se começarmos em B1 e deslocarmos 4 linhas (a contagem de números na coluna B), terminaremos no final do nosso intervalo, B5. No entanto, como OFFSET não pode ser redimensionado com um valor negativo, precisamos fazer alguns ajustes para que fiquemos em B3. A equação geral para isso será fazer
| 1 | CONTAGEM (…) - N + 1 |
Pegamos a contagem de toda a coluna, subtraímos quantas queremos retornar (já que vamos redimensionar para agarrá-las) e, em seguida, adicionamos 1 (já que estamos essencialmente iniciando nosso deslocamento na posição zero).
Aqui você pode ver que configuramos um intervalo para obter a soma, a média e o máximo dos últimos N meses. Em E1, inserimos o valor de 3. Em E2, nossa fórmula é
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
A seção destacada é nossa equação geral que acabamos de discutir. Não precisamos compensar nenhuma coluna. Em seguida, vamos redimensionar o intervalo para 3 células de altura (determinado pelo valor em E1) e 1 coluna de largura. Nosso SUM então assume esse intervalo e nos dá o resultado de $ 1.850. Também mostramos que você pode calcular a média do máximo desse mesmo intervalo simplesmente mudando a função externa de SUM para o que a situação exigir.
Listas de validação dinâmica OFFSET
Usando a técnica mostrada no último exemplo, também podemos construir intervalos nomeados que podem ser usados na validação de dados ou gráficos. Isso pode ser útil quando você deseja configurar uma planilha, mas espera que nossas listas / dados mudem de tamanho. Digamos que nossa loja está começando a vender frutas e atualmente temos 3 opções.
Para fazer uma lista suspensa de validação de dados que podemos usar em outro lugar, vamos definir o intervalo nomeado MyFruit como
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Em vez de COUNT, estamos usando COUNTA, pois estamos lidando com valores de texto. Por causa disso, porém, nosso CONT.valor será um maior, pois contará a célula de cabeçalho em A1 e fornecerá um valor de 4. Se compensarmos em 4 linhas, no entanto, terminaremos na célula A5, que está em branco. Para ajustar isso então, subtraímos o 1.
Agora que temos nossa configuração de intervalo nomeado, podemos configurar alguma validação de dados na célula C4 usando um tipo de lista, com fonte:
| 1 | = MyFruit |
Observe que a lista suspensa mostra apenas nossos três itens atuais. Se adicionarmos mais itens à nossa lista e voltarmos para a lista suspensa, a lista mostrará todos os novos itens sem que tenhamos que alterar nenhuma das fórmulas.
Cuidados ao usar OFFSET
Conforme mencionado no início deste artigo, OFFSET é uma função volátil. Você não notará isso se estiver usando em apenas algumas células, mas se começar a envolvê-lo em centenas de cálculos e rapidamente notará que seu computador gasta uma quantidade considerável de tempo recalculando cada vez que você faz alguma alteração .
Além disso, como OFFSET não nomeia diretamente as células que está olhando, é mais difícil para outros usuários aparecerem mais tarde e alterarem suas fórmulas, se necessário.
Em vez disso, seria aconselhável usar tabelas (introduzidas no Office 2007) que permitem referências estruturais. Isso ajudou os usuários a serem capazes de fornecer uma referência única que se ajustava automaticamente em tamanho conforme novos dados eram adicionados ou excluídos.
A outra opção a ser usada em vez de OFFSET é a poderosa função INDEX. INDEX permite que você construa todas as faixas dinâmicas que vimos neste artigo sem o problema de ser uma função volátil.
Notas Adicionais
Use a função OFFSET para retornar um valor de célula (ou um intervalo de células) compensando um determinado número de linhas e colunas de uma referência inicial. Ao procurar apenas por uma única célula, as fórmulas OFFSET alcançam o mesmo propósito que as Fórmulas INDEX, usando uma técnica ligeiramente diferente. O verdadeiro poder da Função OFFSET reside em sua capacidade de selecionar um intervalo de células a ser usado em outra fórmula.
Ao usar a função OFFSET, você define uma célula inicial inicial ou intervalo de células. Em seguida, você indica o número de linhas e colunas a serem deslocadas dessa célula inicial. Você também pode redimensionar o intervalo; adicionar ou subtrair linhas ou colunas.
Voltar para a lista de todas as funções no Excel
OFFSET no Google Sheets
A função OFFSET funciona exatamente da mesma forma no Planilhas Google e no Excel:
