Blog

Postado em em 31 de outubro de 2019

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!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

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.

 

Exemplo de um jogo de xadrez (fictício)

Exemplo de um jogo de xadrez (fictício)

 

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.

 

Fórmula DESLOC

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.

 

Fórmula DESLOC para o peão

Fórmula DESLOC para o peão

 

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.

 

Resultado do deslocamento do peão

Resultado do deslocamento do peão

 

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:

 

Fórmula DESLOC para o cavalo

Fórmula DESLOC para o cavalo

 

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).

 

Resultado do deslocamento do cavalo

Resultado do deslocamento do cavalo

 

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.

 

Tabela inicial com as vendas dos produtos mensais

Tabela inicial com as vendas dos produtos mensais

 

É 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.

Validação de Dados – Lista Suspensa Condicionada no Excel

 

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

 

Resultado da validação de dados

Resultado da validação de dados

 

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.

 

Fórmula CORRESP para obter o valor auxiliar referente ao mês selecionado

Fórmula CORRESP para obter o valor auxiliar referente ao mês selecionado

 

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.

 

Resultado da fórmula CORRESP

Resultado da fórmula CORRESP

 

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.

 

Modificando o mês para verificar a atualização do valor auxiliar

Modificando o mês para verificar a atualização do valor auxiliar

 

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.

 

Fórmula de SOMA com DESLOC para obter a soma do mês selecionado

Fórmula de SOMA com DESLOC para obter a soma do mês selecionado

 

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.

 

Resultado da fórmula SOMA com DESLOC

Resultado da fórmula SOMA com DESLOC

 

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.

 

Verificando o resultado da fórmula com a soma automática do Excel

Verificando o resultado da fórmula com a soma automática do Excel

 

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

 

Demonstração do por quê utilizar a coluna B como referência

Demonstração do por quê utilizar a coluna B como referência

 

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

 


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?