DESLOC Excel – Como deslocar intervalos no Excel

Nessa publicação vou te mostrar como utilizar a fórmula DESLOC Excel através de exemplos práticos e intuitivos para facilitar o aprendizado!

A fórmula DESLOC retorna um valor ou intervalo a partir de uma célula de referência, deslocando linhas e colunas: =DESLOC(referência; linhas; colunas). Valores positivos descem e vão para a direita; negativos fazem o contrário. Combinada com CORRESP, ela monta intervalos dinâmicos, como somar a coluna do mês selecionado.

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

O que você aprende neste vídeo

Resposta rápida: A aula ensina a função DESLOC do Excel para somar as vendas do mês escolhido numa lista suspensa. Você aprende a criar a lista com Validação de Dados, deslocar o intervalo de total com =SOMA(DESLOC(ref; lins; cols)) e usar CORRESP para descobrir quantas colunas deslocar, de modo que o total se atualize sozinho quando o mês muda.

Neste vídeo (22 min):

• 0:32 - A base de exemplo: vendas de cada produto mês a mês, com uma coluna de total que soma o ano com SOMA
• 1:10 - O resultado a construir na aba Gabarito: escolher o mês numa lista e ver o total de vendas e o número auxiliar daquele mês
• 1:54 - Ctrl + Shift + seta para baixo seleciona a coluna inteira do mês, e a barra de status mostra a soma da seleção
• 3:40 - Criando a lista de meses em Dados, Ferramentas de Dados, Validação de Dados, com a opção Lista e a fonte nos meses da aba Base
• 7:02 - =SOMA() com o intervalo de fevereiro digitado na mão: trocar o mês para junho ou agosto não muda o total
• 8:17 - A lógica do deslocamento: a partir da coluna de total, 1 coluna à direita é janeiro, 2 é fevereiro e 12 é dezembro
• 11:33 - DESLOC dentro de SOMA e os argumentos que ela pede: ref, lins, cols, altura e largura
• 13:21 - lins em 0 e cols em 2: o intervalo B2:B51 do total vira D2:D51, a coluna de fevereiro
• 14:33 - Com o 2 escrito direto na fórmula, mudar o mês para maio não atualiza o resultado
• 15:57 - CORRESP devolve a posição do mês na lista de meses, que é o mesmo número de colunas a deslocar
• 17:29 - Tipo de correspondência 0 para busca exata: maio retorna 5 e outubro retorna 10
• 18:43 - Trocando o 2 fixo pela célula auxiliar: outubro desloca 10 colunas e a soma confere com a coluna de outubro da base

Trechos do vídeo:

• DESLOC parte de uma célula ou intervalo de referência e devolve outro, deslocado pelo número de linhas e de colunas informado nos argumentos lins e cols
• Um intervalo digitado na mão dentro de SOMA fica preso àquela coluna: para o total acompanhar o mês escolhido, o número de colunas a deslocar tem de vir de uma célula que muda junto
• CORRESP com correspondência exata devolve a posição do mês na lista de janeiro a dezembro, e essa posição é justamente quantas colunas separam a coluna de total da coluna do mês
• A Validação de Dados do tipo Lista mostra uma seta com as opções na célula e impede que se digite qualquer valor fora dela
• Ctrl + Shift + seta para baixo seleciona do primeiro ao último valor preenchido da coluna, e a barra de status mostra na hora a soma, a média e a contagem da seleção

Para baixar a planilha utilizada nessa aula clique aqui!

O que é a fórmula DESLOC Excel?

A fórmula DESLOC como o próprio nome sugere, ela serve para deslocar, ou seja, podemos obter um valor tendo uma célula como referência. Isso funciona da seguinte forma, podemos pensar como um jogo de xadrez.

Neste caso temos o peão na casa B2 do nosso tabuleiro fictício e o cavalo na casa C2. Como o peão anda apenas para frente, queremos saber em qual casa ele vai estar se deslocarmos ele uma casa, para isso vamos utilizar a fórmula DESLOC.

Nesta fórmula temos 3 argumentos, o primeiro é a nossa referência, que neste caso seria uma de nossas peças de xadrez. O segundo argumento é referente a quantas linhas vamos “andar” e o terceiro é referente a quantas colunas vamos “andar”. (Lembrando que o positivo e o negativo vão indicar o sentido, linhas positivas vão para baixo e colunas positivas vão para a direita, as negativas farão o oposto).

No caso do peão como ele só anda uma casa e só anda na mesma coluna, isso quer dizer que não teremos uma variação de coluna apenas de linha.

Então para o peão temos a seguinte fórmula. A referência é a posição inicial e ele vai se mover apenas uma linha para baixo, com isso ele deveria parar na célula B3, pois continua na mesma coluna.

Para o cavalo temos uma particularidade, pois ele pode se mover em qualquer direção e ele “anda” em L, ou seja, ele tem um deslocamento em linhas e em colunas. A fórmula que deveríamos colocar para que ele chegue por exemplo na célula D4 seria a seguinte:

O cavalo sempre terá essas movimentações de 2 e 1, ou 1 e 2 (podendo ser negativo para ir ao sentido oposto), porque é assim que ele se movimente no xadrez. Então com esse movimento teremos o cavalo na casa D4 porque ele vai se movimentar duas linhas para baixo (sentido positivo) e uma coluna para a direita (sentido positivo).

Portanto a fórmula DESLOC nada mais é do que um deslocamento a partir de uma referência, ou seja, vamos obter um dado a partir de movimentações tendo um ponto inicial.

Quando utilizar essa fórmula?

Vamos utilizar essa fórmula para facilitar a utilização de algumas fórmulas para que não tenhamos que fazer atividades manuais, como é o caso do nosso exemplo. Temos uma lista de produtos vendidos durantes os 12 meses do ano e queremos saber o somatório de um mês selecionado.

Para que o usuário não tenha que pegar o intervalo sempre de forma manual podemos utilizar a fórmula DESLOC para deixar uma coluna de base e a partir do número do mês podemos deslocar uma quantidade x de colunas até chegar no mês selecionado.

Como utilizar a fórmula DESLOC Excel?

Neste caso vamos utilizar essa fórmula para obter o somatório de todos os produtos de uma empresa em um determinado mês. Temos a tabela que iremos utilizar como exemplo abaixo.

É possível observar que temos diversos produtos na coluna A, já na segunda coluna temos o somatório de todas as vendas daquele produto durante os 12 meses do ano. Então temos um controle total das vendas de todos os produtos durante o ano de 2019.

O nosso objetivo é obter a soma de todos os produtos em um mês selecionado como temos na figura abaixo.

Então a partir da seleção do mês seremos capazes de obter o somatório de todas as vendas naquele mês em específico, e esse somatório será feito com o auxílio da fórmula DESLOC Excel.

Para obtermos um resultado exatamente como o que temos na aba gabarito o primeiro passo é inserir na célula B1 uma validação de dados dos meses que temos na nossa tabela. Abaixo temos o link para aula de validação de dados, caso tenham dúvidas de como utilizar.

https://www.hashtagtreinamentos.com/validacao-de-dados

Feito isso será possível escolher qualquer um dos meses que está sendo mostrado na tabela para obter o somatório final.

Para o somatório não vamos poder simplesmente utilizar a fórmula SOMA sozinha, pois não temos uma referência para esses meses que se atualize sozinha, ou seja, precisamos relacionar o nosso mês em questão com o intervalo que vamos somar.

Como já sabemos como a fórmula DESLOC Excel funciona vamos precisar de uma referência, ou seja, uma coluna (pois queremos os valores mensais e eles se encontram em colunas) de referência e em seguida a quantidade de colunas que vamos deslocar, por isso temos o campo auxiliar, neste campo vamos colocar quantas colunas temos que deslocar da coluna de referência de acordo com o mês que foi selecionado.

Vamos fazer esse procedimento utilizando a fórmula CORRESP, essa é uma fórmula bem simples com dois argumentos. O primeiro deles é o que estamos procurando e o segundo é o intervalo em que estamos procurando. O resultado que essa fórmula nos dá é a posição do que estamos procurando dentro daquela matriz.

Então se formos procurar o mês de fevereiro dentre a seleção de janeiro até dezembro esse mês estará na segunda posição. Desta forma podemos escrever a fórmula desta maneira.

No primeiro argumento temos a célula B1 que é onde fizemos a validação de dados para escolher o mês desejado e no segundo argumento temos a seleção (na aba da tabela) dos meses indo de janeiro a dezembro. Então o Excel vai retornar à posição do que estamos procurando dentro deste intervalo.

Então se alteramos o mês selecionado para outro qualquer devemos ter uma mudança no nosso valor auxiliar, pois a fórmula se atualiza automaticamente.

Vamos agora criar a fórmula para somar os valores do mês selecionado. A fórmula é bem simples, é a fórmula SOMA que vai pegar os valores da coluna que corresponde ao mês selecionado. Então se escolhermos o mês de agosto teremos que pegar toda a coluna de agosto, ou seja, teremos que deslocar uma coluna de referência 8 vezes e para que isso fique correto a nossa coluna de referência tem que ser a coluna B da tabela inicial.

Primeiramente temos a fórmula SOMA, pois queremos somar os valores todos daquele mês em questão. Logo em seguida temos a fórmula DESLOC Excel para obtermos o exato mês que foi selecionado, essa coluna é a B porque os meses já começam a partir da coluna C, e como o mês de janeiro é o primeiro mês já teríamos 1 deslocamento, então temos que pegar uma coluna anterior para que a conta fique correta e tenhamos o deslocamento para o mês também correto.

A referência Base!B2:B51 significa que estamos pegando uma referência da aba Base (por isso Base!) e logo em seguida temos B2:B51 que nos diz que estamos obtendo os dados da célula B2 até a B51, pois são as células que contém os dados de todos os produtos.

Aqui temos que o somatório de todos os produtos do mês de agosto é igual a R$26.885, para conferir isso basta ir até a aba Base e selecionar todos os dados da coluna de agosto. Ao selecionar lá embaixo no canto direito do Excel logo abaixo da barra de rolagem temos alguns dados e um deles é a soma desses dados, que é exatamente o que obtivemos na fórmula.

Caso ainda não entendam por que estamos utilizando a coluna B como referência aí vai mais uma explicação.

O nosso intervalo de seleção para criar o valor auxiliar vai da célula C1 até N1, que corresponde a 12 células que são os 12 meses do ano. Como o nosso mês inicia do mês 1, nossa fórmula DESLOC Excel já teria que fazer 1 deslocamento, então se nossa referência fosse a coluna C teríamos que fazer um cálculo antes para adaptar, então para ficar mais fácil vamos utilizar a referência como a coluna B, pois o menor valor que temos é 1.

Então ao escolher janeiro que é o menor valor o Excel vai deslocar a nossa seleção em 1 coluna (lembrando que não vamos deslocar em linha, pois nossos dados estão sempre nas mesmas linhas). E a coluna logo ao lado do total é exatamente janeiro.

Aprendemos então como utilizar a fórmula DESLOC para obter o somatório de um mês selecionado pelo usuário sem ter que fazer nada manual, ou seja, assim que o usuário muda o mês de seleção os cálculos mudam automaticamente.

Para acessar outras publicações do nosso blog, é só clicar em: www.hashtagtreinamentos.com/blog

Você está procurando por curso de excel? Clique para saber mais! Aqui na Hashtag temos as melhores opções de curso pra você!

Perguntas frequentes

Hashtag Treinamentos

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

---

---