Excel na Web
Coloque seu e-mail aqui para receber GRATUITAMENTE o meu EBOOK →

→ 10 Funções que podem fazer de você um perito em EXCEL

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ê!


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.

Ricardo Fialkoski é o fundador do blog Excel na Web. Trabalha com Excel há mais de dez anos desenvolvendo Soluções Avançadas em Excel e VBA.

14 comentários :

  1. Ótimo tutorial!
    Bem detalhado e bem explicado.
    Continue nos ajudando.
    Obrigado

    ResponderExcluir
    Respostas
    1. Muito obrigado!
      É muito gratificante saber que de alguma forma estou colaborando com esses materiais.

      Abraços e sucesso com suas planilhas!

      Excluir
  2. Parabéns! Texto de fácil entendimento e muito esclarecedor

    ResponderExcluir
    Respostas
    1. Obrigado! Sempre que puder deixe um comentário aqui no blog, ok? Eles são meus melhores indicadores para ver se estou acertando no conteúdo! Grande abraço!

      Excluir
  3. Excelente! Ótima dinâmica para ensinar, deixa tudo muito claro.

    Abraços

    ResponderExcluir
    Respostas
    1. Obrigado Anderson, é muito bom saber que os tutoriais tem sido útil para você. Um abraço!

      Excluir
  4. Encelente! Foi muito didático!

    ResponderExcluir
  5. Parabéns Ricardo! Acontece muito comigo: necessitar que o Excel execute um procedimento, procuro as funções e não consigo "enxergar" quais (em geral são compostas como nos exemplos acima) e como utilizá-las. Suas dicas são por demais úteis. Obrigado mesmo!

    ResponderExcluir
    Respostas
    1. Obrigado pelas palavras Dorival! É exatamente isso! o Excel é sensacional mas as vezes não conhecemos o poder de uma determinada Função que pode ser muito útil se aninhada com outra Função do Excel. Em breve vou postar uma serie de Funções Compostas para solução de problemas rotineiros no Excel. Abraços!

      Excluir
  6. Excelência é como podemos qualificar suas dicas e ensinamentos. Úteis demais.

    ResponderExcluir
  7. Muitíssimo obrigado.. estava com dúvidas sobre como utilizar essa função DESLOC+CORRESP. por mais que estudemos as vezes nos deparamos com casos como este que se nao fosse desenhado eu não entenderia. Foi muito esclarecedor. Nota 10 Ricardo.

    ResponderExcluir
    Respostas
    1. Olá Paulo! Fico feliz por ter ajudado você!

      Sempre buscamos por aqui ficar atentos aos detalhes para passar as melhores técnicas para os nossos leitores!

      Um abraço!

      Excluir

Comentário(s):