[Excel Avançado] – Função Desloc

Fala Galera. Tudo bem ? Neste Post vou mostrar para vocês como utilizar essa importante função do Excel que é  o DESLOC.  

Costumo dizer nos meus treinamentos que existem algumas funções mágicas do Excel e que são muito poderosas. Então  se você quer ter um nível avançado do Excel precisa dominar o Desloc.

A ideia do desloc, é  de fato Deslocar virtualmente uma célula na planilha através de parâmetros que podem ser o número da linha, coluna, altura e largura.   

Ficou um pouco confusa essa definição certo ? Vamos para os exemplos que com certeza ficará mais fácil a compreensão.

A gente tem aqui 3 exemplos bem práticos e que mostram como utilizar o desloc. Para isso, vamos  tomar como base a Tabela 1, que basicamente é  uma lista de Nome,  Idade e Aniversario.

Basicamente o DESLOC tem duas grandes formas de usar:

1. Deslocar uma célula para cima e para baixo que no excel seriam as linhas, e para esquerda e direira que nesse caso seriam as colunas.
2. A segunda função do desloc é deslocar não somente uma célula apenas mas o intervalo de uma célula, guarda isso que a gente vai ver com mais detalhe lá na frente.

1ª Forma do Desloc

No Exemplo 1 tem a primeira forma do DESLOC , o nosso objetivo aqui é trazer a idade e o Aniversario da Diego Lima. Poderíamos nesse exemplo usar a função PROCV para localizar esses dados,  mas como o objetivo é mostrar a função DESLOC vamos imaginar que não poderíamos fazer isso com o PROCV ok .

Para entender a função vamos pegar como referência a célula C6 que contém o número 25, ou seja, a idade do Carlos. Se eu quiser deslocar a minha referência de célula que esta na C6 para a C7 por exemplo, eu tenho que deslocar mais 1 linha para baixo .

1. Digitar o DESLOC
2. Selecionar a célula C6 como Referência
3. Parâmetro de linha célula G9
4. Parâmetro de coluna célula G10.

Observe que ele trouxe o número 25 da idade do Carlos pois a referência de linha e coluna está vazia, e o Excel entende nesse caso como zero.

Mas, o nosso objetivo é trazer a idade do Diego, logo temos que deslocar a célula 2 linhas para baixo. Então no parâmetro Coluna iremos digitar justamente o número 2. Observe que trouxe justamente a idade do Diego.

Se a gente quisesse a idade da Amanda seria 3 no parâmetro linha. Muito fácil utilizar a função certo ?

Agora, vamos aprender a deslocar através de coluna. Para isso vamos repetir a mesma formula que digitamos anteriormente. E nos parâmetros vamos digitar na linha o mesmo 2 para encontrar a linha que corresponde ao Diego, e na coluna vamos digitar o número 1, pois precisamos trazer o Aniversário do Diego.

Para a direita na coluna e para baixo na linha é sempre um número positivo, isso significa que se a gente digitar um número negativo iremos para o sentido oposto.

Se eu digitar -1 na coluna irar aparecer o nome do Diego.  Pronto, você já aprendeu uma forma de usar a o desloc. Agora vamos para a segunda forma.

2ª Forma do Desloc

A segunda forma tem mesma lógica da primeira porem é utilizado para deslocar um intervalo.

Nesse exemplo 2 precisamos somar a idade de todos. Obvio que basta só colocar a função soma aqui e selecionar o intervalo de C6:C9. Porém vamos fazer a função Soma com intervalo gerado pelo a função  Desloc para aprender.

1. Vamos digitar =soma(desloc( selecionar a mesma referência do exemplo 1.
2.  Marcar com ; “Ponto e virgula” 3 vezes os parâmetros de linha e coluna que nesse exemplo não iremos utilizá-los.
3.  Referenciar nas células g18 e g19 os parâmetros de altura e largura.

Sua fórmula deve ficar assim → =SOMA(DESLOC($C$6;;;G18;G19))

Esse erro de REF é que nossa referência ainda não está completa, vamos fazer isso agora. A lógica é quase parecida com o do exemplo  anterior, como eu quero deslocar para baixo precisamos deslocar o número positivo, a diferença aqui é que nesse caso eu vou deslocar apenas o intervalo, logo não partimos do 0, e sim do 1 ou seja, na  altura temos que aumentar o intervalo 4 vezes pois são 4 linhas para somar todas as idades, seguindo a mesma lógica na largura precisamos digitar o número 1 pois estamos criando um intervalo de 4 linhas e 1 coluna.

Surgi então o total 130 que é soma das 4 idades.

Vamos para o Exemplo 3 para fixar essa segunda forma de usar o DESLOC.

Vamos combinar ele com uma validação de dados do tipo lista para na célula B16 listar os 4 nomes aqui.

1. Clicar no Menu Dados > Validação > Permitir Lista
2.  Campo fonte vamos digitar o DESLOC
3. Usar a célula b6 como referência
4. Marcar; 3 vezes pois novamente não iremos usar as referências de linha e coluna.
5. E referenciar igual no exemplo anterior, a altura na célula c18 e a largura na C19.

Sua fórmula deve ficar assim → =DESLOC($B$6;;;C18;C19)

Nos parâmetros vamos digitar o mesmo 4 na altura pois são 4 linhas na nossa referência e digitar 1 na altura pois estamos no intervalo de 1 coluna.

Pronto, surgiu a validação de dados.

Baixe a planiha usada aqui

Abraços e até a próxima.