Neste artigo, aprenderemos como usar a função OFFSET no Excel.
O que é Função OFFSET?
A função de deslocamento é uma função de recuperação de informações. Se você tiver uma tabela enorme contendo certos dados e quiser recuperar dados dela, a melhor maneira de fazer isso é inserir uma fórmula de deslocamento para a tabela nessa planilha ou em outra planilha na mesma pasta de trabalho. Assim, depois de criar a tabela de recuperação de deslocamento, você simplesmente precisa inserir os dados, por exemplo, ‘mês’ e você obterá as ‘vendas’ ou ‘receita’ desse mês a partir dessa tabela.
Examinar a tabela é outra opção, mas você consideraria a opção se tivesse planilhas e planilhas de dados, todas contendo uma tabela de dados de 1000 colunas? É aqui que o deslocamento é combinado com outras funções.
O conceito de tabela dinâmica vem do deslocamento. Uma tabela dinâmica é uma tabela geral e dessa combinação específica de dados e gráficos é obtida como e quando necessário. Também as tabelas dinâmicas podem ser criadas da mesma maneira. Para essas tabelas, você terá que criar a fórmula de deslocamento do Excel
Sintaxe de OFFSET
| = OFFSET (referência, linhas, colunas, [altura], [largura]) |
Linhas - você pede ao excel para mover o número de linhas para obter as informações. Nesse caso, ele deve se mover ao redor da mesa e, portanto, simplesmente deixar um espaço em branco denotado por uma vírgula (,). Caso contrário, para mover uma coluna para a frente, forneça o valor 1 e para mover uma coluna antes forneça o valor -1.
Colunas - isso direciona a função para o número de colunas. O sistema de valores é o mesmo da linha.
Altura - a altura da mesa, neste caso A11.
Largura - largura da mesa, neste caso D11.
Depois de configurar a função de deslocamento, a execução de um teste é importante para eliminar quaisquer erros.
Tomemos uma tabela que contém Nome, id de e-mail, data de nascimento e idade. A tabela começa na célula A1, contendo o título ‘Nome’. Os dados correm para, digamos, A11. E as linhas vão até D1. A tabela inteira executa A1: D11. Você deseja um sistema de recuperação que obtenha o nome quando você insere a id do e-mail, ou a id do e-mail quando você insere o nome junto com outros detalhes. Você cria uma fórmula satisfazendo os 5 argumentos.
O ponto de referência é a célula a partir da qual a pesquisa deve começar. Nesse caso, deve ser A2. Esse é o padrão geral.
Exemplo:
Por exemplo, você fornece a referência de B4 e a linha de deslocamento 0, e a coluna de deslocamento 1, então o valor de C4 será retornado. Está confuso? Vamos ver mais alguns exemplos. O último exemplo da função OFFSET informa como fazer SUM dinamicamente.
Função OFFSET para obter valor da direita e esquerda de uma célula
Temos esta mesa.
Agora, se eu quiser obter o valor de 2 células diretamente para B2 (o que significa D2). Vou escrever esta fórmula OFFSET:
| = OFFSET (B2,0,2) |
A função OFFSET moverá 2 células à direita da célula B4 e retornará seu valor. Veja a imagem abaixo
Se eu quiser obter o valor de 1 célula à esquerda para B2 (o que significa A2). Vou escrever esta fórmula OFFSET:
| = OFFSET (B2,0, -1) |
O sinal de menos (-) indica o deslocamento para o movimento reverso.
Função OFFSET para obter valor abaixo e acima de uma célula
Então, novamente na tabela acima, se eu quiser obter o valor de 2 células acima para B3 (que significa B5). Vou escrever esta fórmula OFFSET:
| = OFFSET (B2,2,0) |
Se eu quiser obter o valor de 1 célula à esquerda para B2 (o que significa A2). Vou escrever esta fórmula OFFSET:
| = OFFSET (B2, -2,0) |
- Dica profissional: Trate um OFFSET como um ponteiro de gráfico onde Referência é a origem e a linha e a coluna são os eixos xe y.
Função OFFSET para somar intervalo dinamicamente
Vamos ver este exemplo.
Na tabela acima, linha total no final da tabela. Com o tempo, você adicionará linhas a esta tabela. Em seguida, você precisará alterar o intervalo da soma na fórmula. Aqui podemos ter ajuda da função OFFSET para somar dinamicamente. Atualmente na célula C11 temos = SOMA (C2: C10). Substitua isso com a fórmula abaixo.
| = SUM (C2: OFFSET (C11, -1,0)) |
Esta fórmula pega apenas a primeira linha de dados e soma o intervalo acima da linha total.
Offset para obter o array
A função OFFSET tem dois argumentos opcionais, [altura] e [largura]. Não tenha Eles como certos. Quando a função OFFSET é fornecida com os argumentos [altura] e [largura], ela retorna uma matriz bidimensional. Se apenas um deles for passado como argumento, ele retorna na matriz dimensional de valores.
Se você digitar esta fórmula em qualquer célula:
| = SUM (OFFSET (C1,1,0,9,3)) |
Ele somará os valores no intervalo começando de C2 a 9 linhas abaixo e 3 colunas à direita.
OFFSET (C1,1,0,9,3): Isso se traduzirá em {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Concluindo, o deslocamento é uma função muito útil do Excel que pode ajudá-lo a resolver muitos tópicos estrangulados. Deixe-me saber como você usa a função OFFSET em sua fórmula e onde ela mais o ajudou.
A função de pesquisa é igual ao deslocamento, mas utiliza funções como Match, Counta e IF para trabalhar uma tabela dinâmica.
Usar offset no Excel requer habilidade e bom conhecimento das funções do Excels e como elas funcionam juntas. Retirar sem nenhum erro é o maior obstáculo.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- Esta fórmula funciona com texto e números.
- Existem cinco argumentos do deslocamento que precisam ser satisfeitos para obter uma informação livre de erros. Se você escrever uma fórmula incorreta, ocorrerá um erro Ref.
Espero que você tenha entendido O que é a função de compensação do Excel e como usá-la no Excel. Explore mais artigos sobre pesquisa do Excel e valores de correspondência usando fórmulas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Função VLOOKUP para calcular nota no Excel : Para calcular as notas IF e IFS não são as únicas funções que você pode usar. A VLOOKUP é mais eficiente e dinâmica para tais cálculos condicionais. Para calcular notas usando VLOOKUP, podemos usar esta fórmula.
17 coisas sobre VLOOKUP do Excel : VLOOKUP é mais comumente usado para recuperar valores correspondentes, mas VLOOKUP pode fazer muito mais do que isso. Aqui estão 17 coisas sobre VLOOKUP que você deve saber para usar com eficácia.
PROCURAR o primeiro texto de uma lista no Excel : A função VLOOKUP funciona bem com caracteres curinga. Podemos usar isso para extrair o primeiro valor de texto de uma determinada lista no Excel. Aqui está a fórmula genérica.
Data LOOKUP com último valor na lista : Para recuperar a data que contém o último valor, usamos a função LOOKUP. Esta função verifica a célula que contém o último valor em um vetor e usa essa referência para retornar a data.
Como recuperar o preço mais recente no Excel : É comum atualizar os preços em qualquer negócio e usar os preços mais recentes para qualquer compra ou venda é obrigatório. Para recuperar o preço mais recente de uma lista no Excel, usamos a função LOOKUP. A função LOOKUP obtém o preço mais recente.
Artigos populares:
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.