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:
- 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.
- 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 .
- Digitar o DESLOC
- Selecionar a célula C6 como Referência
- Parâmetro de linha célula G9
- Parâmetro de coluna célula G10.
Sua fórmula deve ficar assim → =DESLOC($C$6;G9;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.
- Vamos digitar =soma(desloc( selecionar a mesma referência do exemplo 1.
- Marcar com ; “Ponto e virgula” 3 vezes os parâmetros de linha e coluna que nesse exemplo não iremos utilizá-los.
- 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.
- Clicar no Menu Dados > Validação > Permitir Lista
- Campo fonte vamos digitar o DESLOC
- Usar a célula b6 como referência
- Marcar; 3 vezes pois novamente não iremos usar as referências de linha e coluna.
- 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.
Abraços e até a próxima.