14 Função DESLOC Como utilizar no Excel

A Função DESLOC retorna uma célula ou intervalo de células a partir de uma célula de referência com um número específico de linhas e colunas. Assim como outras funções do Excel, esta função é muito eficiente quando combinada com outras funções auxiliares. A Função DESLOC permite desenvolver fórmulas mais complexas e é muito útil para criação de intervalos dinâmicos e localização de dados na planilha.

Como funciona a Função DESLOC

A Função DESLOC retorna um intervalo de células a partir de um ponto de partida específico com altura e largura pré-estabelecidos.

Uma maneira fácil de entender como funciona a Função DESLOC é compará-la à um “mapa do tesouro”. O local marcado com “X” é o retorno da função que, por sua vez, pode ser uma única célula ou um conjunto de células.

Então, de acordo com o mapa do tesouro acima a Função DESLOC irá funcionar da seguinte forma:

=DESLOC(Partindo da célula A1, desça 5 linhas (você estará na célula A6), então siga 3 colunas para a direita (você estará na célula D6), incluindo a célula D6 você encontrará o tesouro que está em um intervalo de uma célula de altura por uma célula de largura).

No Excel a Função do mapa do tesouro é escrita da seguinte forma:

=DESLOC(A6;5;3;1;1)

Sintaxe: =DESLOC(ref; lins; cols; altura; largura)

• ref: é o ponto de partida do mapa do tesouro / planilha. Pode ser uma única célula ou um intervalo de células;
• lins: é o número de linhas que o Excel deve percorrer a partir do ponto de partida. Pode ser um valor positivo ou negativo;
• cols: é o número de colunas que o Excel deve percorrer a partir do ponto de partida. Pode ser um valor positivo ou negativo;
• altura: é o número de linhas que “X” ocupa. Neste exemplo é 1;
• largura: é o número de colunas que “X” ocupa. Neste exemplo também é 1.

Exemplos da Função DESLOC

Como usar a Função DESLOC e CORRESP em conjunto com SOMA

No exemplo abaixo a célula B3 contém uma lista suspensa com os doze meses do ano. Ao trocar o valor da célula B3 o somatório na célula B4 se altera dinamicamente de acordo com o mês selecionado.

• A célula de referência, ou seja, o ponto de partida para a Função DESLOC é a célula A7
• A Função CORRESP percorre o intervalo de células A8:A19 e retorna o número da posição que “mês” está localizado dentro do intervalo. Então este resultado é usado para determinar quantas linhas para baixo, a partir da célula A7, a Função DESLOC deve se mover. Neste caso são 4 linhas para baixo.
• A coluna B é o início do intervalo que será somado. Portanto o número 1 determina que a função deve se deslocar uma coluna para direita a partir da célula A11.
• A altura do intervalo, ou seja, o número de linhas que deseja-se somar é 1.
• A largura do intervalo, ou seja, o número de colunas que deseja-se somar é 4.

Leitura recomendada: Como criar uma lista suspensa no Excel

Fórmula de Soma com Intervalo Dinâmico 

Ao inserir uma nova linha logo acima daquela que contem uma fórmula de soma, por exemplo, deve-se atualizar o alcance do intervalo que compreende a soma para que este passe a considerar o valor adicionado.Uma solução para eliminar esta necessidade é criar um intervalo dinâmico com a Função DESLOC.

No exemplo abaixo os o valor total do frete está contido na linha 14. A célula E14 contém a fórmula:

=SOMA(E4:(DESLOC(E14;-1;0)))

Diante disso se houver a necessidade de inserir uma nova linha entre as linhas 13 e 14 o intervalo da soma será atualizado dinamicamente sem a necessidade de editar a fórmula da célula E14.

Há muitas outras utilidades para a Função DESLOC. É fundamental conhecê-la para saber identificar em que situações o seu uso é o mais apropriado.

É proibido a reprodução parcial ou integral dos artigos deste blog. Copiar na íntegra qualquer conteúdo aqui publicado, implica em crime previsto no Código Penal 

Agora é com você!

Baixe aqui as planilhas utilizadas neste exemplo e pratique.

E você, em que situações costuma usar a Função DESLOC?

Se você gostou, compartilhe na sua rede social: Facebook, Twitter ou Google+ usando os botões abaixo, isso ajuda a incentivar o trabalho.