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 ou acesse o nosso Canal do YouTube para mais vídeos!
Para baixar a planilha utilizada nessa aula clique aqui!
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.
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.
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ê!
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador