.
A função DESLOC permite que você faça referência indireta a uma célula ou a um intervalo de células no Excel. Com OFFSET, você pode escolher um ponto de referência inicial e, em seguida, inserir um endereço nas células de destino em vez de se referir diretamente a elas.
Isso torna a função OFFSET extremamente útil em planilhas dinâmicas do Excel. Você pode usar a função DESLOC por conta própria, mas seu verdadeiro potencial só aparece quando você a aninha dentro de outras funções no Excel. Continue lendo para saber tudo sobre OFFSET no Excel com três exemplos.
O que é a função DESLOC no Excel?
A função OFFSET retorna uma referência a uma célula ou a um intervalo de células. OFFSET recebe cinco parâmetros, e sua sintaxe é a seguinte:
=OFFSET(reference, rows, cols, height, width)OFFSET assumirá a posição do referência célula e, em seguida, mover-se de lá pelo número de linhas e colse, em seguida, retorne a referência da célula de destino.
Se o altura e largura forem definidos como maiores que um, um intervalo de células nessa área será retornado. Altura e largura são dois parâmetros opcionais. Se você deixá-los em branco, OFFSET os lerá como 1 e retornará uma única referência de célula.
Como usar a função OFFSET no Excel
Para usar OFFSET no Excel, você precisa inserir os três parâmetros necessários. Assim que você definir a célula de referência, OFFSET se moverá dessa célula para as células abaixo pelo número do linhas parâmetro. Ele se moverá para a direita pelo número do cols parâmetro.
A partir daí, OFFSET retornará uma referência à célula de destino. Se você definir parâmetros para altura e largura também, OFFSET retornará referências a um intervalo de células, onde a célula de destino original será a célula superior esquerda.
Isso pode parecer complicado no papel, mas é muito mais fácil de entender na prática. Vamos ver como a função OFFSET funciona com alguns exemplos.
1. Exemplo de função OFFSET: referindo-se a uma única célula
Para começar, vamos usar a função OFFSET por conta própria para entender como ela navega na planilha. Neste exemplo, temos vários números em uma planilha do Excel. Vamos usar a função OFFSET para nos referirmos a C4.
Quando a função OFFSET é usada claramente, ela produzirá o conteúdo da célula de destino. Portanto, devemos obter o conteúdo da célula C4, que é o número cinco, assim que inserirmos a fórmula. Vamos começar.
- Selecione uma célula para inserir a fórmula. vamos usar celular G1.
- Na barra de fórmulas, insira a fórmula abaixo:
=OFFSET(A1, 3, 2)Esta fórmula chama a DESLOCAMENTO função para assumir a posição de célula A1em seguida, desça por 3 células e, em seguida, mova para a direita 2 células para alcançar a célula C4.
- Imprensa Digitar.
Uma vez que você pressiona Digitar e a fórmula é decretada, a célula retornará 5. Este é o número da célula C4, então sua fórmula funcionou! Você também pode usar o recurso de avaliação para entender melhor as fórmulas do Excel, como OFFSET.
2. Exemplo de função OFFSET: referindo-se a um intervalo de células
Usando os dois parâmetros opcionais, altura e largura, você também pode retornar um intervalo de células com a função DESLOC no Excel. Vamos continuar de onde paramos no último exemplo.
Desta vez, vamos retornar as células C4 a D9 usando OFFSET no Excel. Assim como antes, vamos usar A1 como nosso ponto de referência inicial.
- Selecione a célula onde você deseja inserir a fórmula. vamos usar celular G1 novamente.
- Na barra de fórmulas, insira a fórmula abaixo:
=OFFSET(A1, 3, 2, 6, 2)Essa fórmula funciona de maneira muito semelhante à anterior, exceto que, uma vez alcançada a célula de destino (C4), ela levará 6 linhas e 2 colunas em vez da célula de destino sozinha. C4 será a primeira célula no novo intervalo.
- Imprensa Digitar.
Agora o intervalo de células C4 a D9 deve aparecer onde você colocou a fórmula. A saída será uma matriz.
3. Exemplo de função OFFSET: Fórmulas compostas
Os dois exemplos anteriores foram um aquecimento para ver como OFFSET funciona no Excel. OFFSET geralmente é aninhado com outras funções do Excel em cenários práticos. Vamos demonstrar isso com um novo exemplo.
Neste exemplo, temos as receitas de um projeto paralelo ao longo dos anos de 2017 a 2021. Na parte superior, temos uma célula que deve mostrar a receita total em determinados anos a partir de 2021.
O objetivo aqui é criar uma planilha dinâmica usando OFFSET no Excel. Quando você altera o número de anos para o total, a fórmula que calcula esse valor também deve ser atualizada.
Para atingir esse objetivo, vamos usar a função OFFSET junto com a função SUM. Em vez de nos referirmos diretamente às células, usaremos OFFSET. Dessa forma, podemos obter dinamicamente o intervalo de células que serão incorporados ao SUM.
Neste exemplo, o ano 2021 está na célula H5 e o número de anos para o total está na célula C1. Usamos formatação personalizada na célula que contém os anos. O valor real é o número de anos e o texto Anos é um sufixo. Vamos começar criando uma fórmula para calcular o total. Vamos usar a função SOMA no Excel.
- Selecione a célula onde você deseja calcular o total.
- Na barra de fórmulas, insira a fórmula abaixo:
=SUM(OFFSET(H5,0, 0, 1, -C1))Esta fórmula consiste em DESLOCAMENTO e SOMA. Na primeira etapa, OFFSET irá para a célula H5. A partir daí, ele passará 0 linhas e 0 colunas e, em seguida, retornará um intervalo de células a partir de H5. Esta seleção será 1 célula em altura, e -C1 células de largura. Isso significa que OFFSET retornará um intervalo a partir de H5 e movendo-se para a esquerda por C1 células.
- Na etapa final, a função SUM adicionará os números no intervalo e os produzirá.
- Imprensa Digitar.
O número que você obtém como o total agora depende do número de anos que você inseriu. Se o número de anos for definido como 1, você obterá o total apenas para o ano de 2021.
Observe que o total é atualizado instantaneamente quando você altera o número de anos. Se você alterar o número de anos para 7, a função SUM calculará o total de 2015 a 2021.
Planilhas Dinâmicas com OFFSET
A função OFFSET no Excel tem muitos parâmetros, e isso pode causar confusão por si só, mas quando você começar a usá-la, você aprenderá que, como muitas outras funções do Excel, OFFSET é uma função amigável.
Embora não haja muito uso para o OFFSET sozinho, quando combinado com outras funções do Excel, o OFFSET lhe dá o poder de criar planilhas dinâmicas e sofisticadas.
.
