Função DESLOC: A Forma Fácil de Deslocar Células no Excel

A função DESLOC é a fórmula do Excel utilizada para deslocar células e intervalo de células de uma forma simples e intuitiva!

É crucial entender como essa fórmula é estruturada para compreender melhor seus usos com outras funções.

Saiba mais!

A função DESLOC retorna uma referência deslocada a partir de uma célula inicial. A sintaxe é =DESLOC(ref; lins; cols; [altura]; [largura]): ref é o ponto de partida, lins desloca para baixo (positivo) ou para cima (negativo) e cols desloca para a direita ou para a esquerda. Altura e largura são opcionais e definem o tamanho do intervalo retornado — por isso o DESLOC costuma vir combinado com SOMA ou CORRESP.

O que é a função DESLOC do Excel?

Talvez você ainda não tenha visto nem utilizado essa função, pois ela não é muito abordada, no entanto é uma função muito interessante.

Isso porque ela te permite “deslocar” células, e o que isso quer dizer?

Podemos deslocar os intervalos de células para facilitar a busca por informações.

Um exemplo que eu sempre gosto de dar sobre essa fórmula é que ela se assemelha a um jogo de xadrez, porque o que fazemos é exatamente mover uma referência em linhas e colunas.

Sintaxe da função DESLOC

A sintaxe da função DESLOC no Excel envolve três principais elementos: a referência, as linhas e as colunas.

A referência é o ponto de partida, de onde se começará a deslocar enquanto a função percorre. 

O número de linhas determina o movimento vertical da função. Se o número é positivo, ele avança para além da referência, para baixo. Se o número for negativo, ele avança para cima.

O número de colunas, por sua vez, determina o movimento horizontal da função. Se positivo, a função percorre para a direita; quando negativo, ela desloca para a esquerda.

Além disso, há também a altura e a largura. A altura corresponde ao número de linhas, ao passo que a largura diz respeito ao número de colunas. 

Quando utilizar essa função?

A função DESLOC do Excel é muito útil em algumas ocasiões específicas. 

Uma delas é para encontrar um valor cujo endereço ainda não se sabe, mas que está dentro de um intervalo conhecido, com o ponto de partida estabelecido.

Além disso, pode ser usado para determinar a soma de um conjunto de dados, bem como a média, o máximo e o mínimo. 

Também é útil no caso de uma tabela que vai ganhando linhas e colunas, aos poucos. 

Outra função muito importante é como veremos neste tutorial: quando se deseja mostrar a soma de um conjunto de valores de acordo com a escolha do usuário na validação.

Nesse caso, é preciso conjugar a função de validação de dados, a soma, uma função chamada CORRESP e então a função DESLOC.

O que a função faz é simplesmente se deslocar sempre que o usuário selecionar uma opção, para a direita ou para esquerda. Para saber qual é o índice, é preciso pegar a função CORRESP.

Explicaremos com mais detalhes a seguir. 

Como usar a função DESLOC no Excel?

Neste ponto acredito que já esteja curioso para saber como a função funciona, não é mesmo?

Vamos primeiro analisar uma base de dados para facilitar o entendimento da função DESLOC no Excel.

Tabela de dados reduzida
Tabela de dados reduzida

Vamos considerar essa seção da tabela para a explicação.

Antes de utilizar a função é importante saber cada um dos argumentos, vamos lá?

Função DESLOC Excel
Função DESLOC Excel
  • Ref – É a célula que temos como referência (vamos utilizar a célula B2);
  • Lins – É a quantidade de linhas que vamos deslocar (positivo para baixo, negativo para cima);
  • Cols – É a quantidade de linhas que vamos deslocar (positivo para direita, negativo para esquerda);
  • Altura – A quantidade de células que vamos considerar como resultado dentro de uma coluna, ou seja, vamos deslocar o intervalo, mas quero como resultado 2 células a partir dessa posição.
  • Largura – A quantidade de células que vamos considerar como resultado dentro de uma linha.

Vamos agora imaginar que essa referência vai se mover igual ao cavalo do xadrez!

Ele se move em L, então vamos imaginar que vamos andar 2 linhas e 1 coluna para formar esse L.

Função DESLOC no Excel com argumentos
Função DESLOC no Excel com argumentos

Com isso vamos 2 linhas para baixo e 1 para a direita, e qual o resultado teremos?

Visualização do deslocamento feito
Visualização do deslocamento feito

Então na célula em que escrevemos a fórmula teremos o resultado em azul, R$8.641.

Com isso podemos deslocar uma célula ou conjunto de células e obter um resultado do mesmo tamanho (em células) ou um tamanho variado dependendo dos critérios de altura e largura.

Mas agora você deve estar se perguntando, “Sim…mas onde é que eu vou utilizar isso?”.

E eu vou te mostrar isso agora, até porque essa fórmula geralmente é utilizada com a função CORRESP.

Que é uma função que permite retornar um valor de acordo com a posição de uma informação.

Ícone ExcelExcel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Black Friday · Lote Especial · Lote 1 · Último Lote

De R$ 1.497 56% OFF54% OFF53% OFF

por 12x R$ 6467,5069

Preço do Lote Especial por tempo limitadoPreço do Lote Especial só até amanhã, 13/11, às 23h59Preço do Lote Especial até 13/11, às 23h59Último dia do Lote Especial: o preço vale até hoje, às 23h59Preço do Lote 1 por tempo limitadoPreço do Lote 1 só até amanhã, 20/11, às 23h59Preço do Lote 1 até 20/11, às 23h59Último dia do Lote 1: o preço vale até hoje, às 23h59Preço do Último Lote por tempo limitadoPreço do Último Lote só até amanhã, 27/11, às 23h59Preço do Último Lote até 27/11, às 23h59Último dia do Último Lote: o preço vale até hoje, às 23h59

Começar agoraQuero a oferta de Black FridaySeta para a direita
Fundo ExcelTelas Excel
Luz Excel

Aplicação da função DESLOC!

Preparado para colocar a mão na massa?

Se ainda não sabe como funciona a função CORRESP é importante, pois vamos utilizá-la como exemplo, pode verificar na Aula 2 dessa série.

Exemplo de somatório com DESLOC
Exemplo de somatório com DESLOC

Este é o exemplo que vamos fazer, queremos descobrir o total (somatório) dos valores de abr/20.

Então primeiramente vamos utilizar a função CORRESP para buscar a informação de mês dentro da tabela.

Função CORRESP
Função CORRESP

A célula J2 é onde temos o mês, e o intervalo de B1 a G1 é onde temos os cabeçalhos dos meses da nossa tabela. Por fim o 0 é para mostrar o tipo de correspondência que é exata.

O resultado dessa fórmula será 4, pois do intervalo que selecionamos o mês de abril é o quarto mês.

Isso é importante, pois agora sabemos quantas colunas precisamos deslocar para fazer nosso cálculo de total.

Somatório com a função DESLOC
Somatório com a função DESLOC

Neste caso vamos construir a função DESLOC juntamente com a função SOMA para somar todo esse intervalo.

Você pode estar se perguntando, “Mas porque começamos selecionando o intervalo de referência sendo da coluna A, se não temos dados nela?”.

Isso acontece por conta da nossa função CORRESP, como teremos o resultado de 1 a 6 por conta das datas, vamos deslocar de 1 a 6 colunas, então se selecionarmos a coluna do mês de janeiro, então o mínimo de deslocamento seria 1.

Isso quer dizer que ao selecionar o mês de janeiro teríamos o resultado de fevereiro, e por esse motivo estamos selecionando a coluna com as informações de equipe.

Fórmula após alterar o mês
Fórmula após alterar o mês

Então mesmo alterando o mês escolhido as fórmulas serão atualizadas automaticamente para puxar o valor correto da soma de acordo com o intervalo que selecionamos.

Conclusão

Você achava que seria tão fácil utilizar uma função diferente que pode te auxiliar nas suas planilhas?

Foi possível verificar o quão importante essa função é, pois permite o deslocamento tanto em linhas quanto em colunas e com isso permite a utilização com outras fórmulas para auxiliar.

Veja que ela é muito útil principalmente quanto não temos apenas 1 colunas com informações, como por exemplo, de data. Isso quer dizer que não temos 1 única coluna com as informações de data.

Como pôde ver, tivemos várias, então conseguimos fazer a nossa soma tranquilamente utilizando as fórmulas DESLOC e CORRESP.

Que tal agora praticar e começar a colocar a utilizar essa função nas suas planilhas?

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: O DESLOC devolve uma referência deslocada a partir de uma célula inicial. A sintaxe é =DESLOC(ref; lins; cols; [altura]; [largura]): lins move para baixo, cols move para a direita e valores negativos invertem o sentido. Sozinho ele quase não aparece: o uso comum é dentro do SOMA, com o CORRESP calculando quantas colunas deslocar.

Neste vídeo (20 min):

  • 0:55 - mostra a meta: total de vendas que muda com o mês escolhido
  • 2:35 - revisa o SOMA somando o intervalo de uma coluna
  • 3:45 - cria a lista suspensa dos meses com validação de dados
  • 5:05 - apresenta o CORRESP, que devolve a posição de um item na lista
  • 8:45 - explica o deslocamento de uma referência em linhas e colunas
  • 9:50 - mostra a soma de intervalo fixo parada enquanto o mês muda
  • 10:35 - envolve a referência com DESLOC dentro do SOMA
  • 11:30 - define zero linhas e uma coluna para chegar em janeiro
  • 13:00 - troca o número de colunas e vê o mês somado mudar
  • 14:10 - substitui o número de colunas pelo resultado do CORRESP
  • 15:20 - junta tudo numa fórmula só, que responde à lista suspensa
  • 17:00 - propõe o desafio de deslocar linhas para somar por equipe

Trechos do vídeo:

  • A função CORRESP descobre qual é a posição de um determinado item dentro de uma lista.
  • A soma com intervalo fixo continua mostrando janeiro mesmo depois de você trocar o mês.
  • Deslocando a referência uma coluna para a direita, a soma passa a pegar o mês seguinte.

Perguntas frequentes

1. O que a função DESLOC faz no Excel?

Ela retorna uma célula ou um intervalo a partir de um ponto de referência, deslocando o número de linhas e colunas que você informar. Como esse deslocamento pode vir de outra fórmula, a referência muda sozinha conforme o dado escolhido.

2. Quando vale a pena usar o DESLOC?

Quando o endereço do valor não é fixo: somar a coluna do mês escolhido em uma validação de dados, calcular máximo, mínimo ou média de um intervalo que cresce, ou montar intervalos dinâmicos para listas e gráficos.

3. Como combinar o DESLOC com outras funções?

A dupla mais comum é SOMA + DESLOC + CORRESP: o CORRESP descobre a posição da coluna e o DESLOC monta o intervalo que a SOMA vai somar. Com CONT.VALORES, o DESLOC também cria intervalos que se ajustam sozinhos quando novas linhas entram. E para as limitações do PROCV, ÍNDICE + CORRESP e o PROCX costumam resolver melhor.

4. O DESLOC funciona em qual versão do Excel?

Em todas as versões atuais, incluindo o Microsoft 365 e o Excel para a web. Vale saber que ela é uma função volátil, ou seja, recalcula a cada alteração da planilha — use com moderação em arquivos muito grandes.

Hashtag Free Excel Básico

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?

Posts mais recentes de Excel Avançado

Posts mais recentes da Hashtag Treinamentos