Função DESLOC no Excel: Como Usar?

junho 9, 2022 admin 0 comentários

Você sabe como usar a função DESLOC no Excel? Ainda não? Então este post é para você!

A função DESLOC é uma das funções mais comuns do Excel, principalmente quando usada juntamente com outras funções. Ela basicamente retorna uma célula ou intervalo de células que são um número especificado de linhas e colunas da célula original ou intervalo de células.

A princípio isso tudo pode parecer um pouco confuso, mas essa função é mais simples do que parece. E para que você possa conhecer mais sobre ela e saber como usá-la na prática, basta conferir os detalhes a seguir.

Função DESLOC no Excel: Sintaxe e uso básico

A função DESLOC é uma função interna do Excel e sua finalidade é retornar um intervalo, que é um número especificado de linhas e colunas de um intervalo de células ou uma única célula. Sua sintaxe é:

=DESLOC(ref; lins; cols; altura; largura)

Onde:

ref: Argumento obrigatório. Ele será o ponto de partida, que servirá como base para o deslocamento.
lins: Argumento obrigatório. Representa o número de linhas que deseja que o Excel se mova a partir do ponto de partida (ref).
cols: Argumento obrigatório. Representa o número de colunas que deseja que o Excel se mova a partir do ponto de partida (ref).
altura: Argumento opcional. É a altura em número de linhas, usada para referência.
largura: Argumento opcional. É a largura em número de colunas, usada para referência.

Para melhor compreensão, suponha que você tenha uma tabela com duas colunas conforme demonstrado na imagem abaixo.

Nesta tabela, suponha que você queira encontrar qual cor está na coluna B na terceira linha. Com a função DESLOC você pode encontrar esta cor facilmente veja o exemplo:

Como funciona a função DESLOC:

Na função DESLOC, você deverá colocar o ponto de partida, neste exemplo é a célula A1. Como queremos encontrar o item que está na linha 3 e coluna B, então inserimos o número da linha e da coluna na fórmula.

Basicamente a função irá fazer uma busca de acordo com as coordenadas inseridas na fórmula. Ou seja, a partir da célula A1, ela irá percorrer até a linha 3 para baixo e andar uma coluna para a direita conforme mostra imagem abaixo.

Exemplos práticos com a função DESLOC no Excel

Agora que vimos os conceitos e usos básicos da função DESLOC, vamos nos aprofundar mais na função e conferir alguns exemplos práticos.

Exemplo 1: Como somar um intervalo de valores com a função DESLOC

Neste primeiro exemplo, suponha que você tenha uma planilha com as vendas de alguns vendedores nos meses de Janeiro a Março.
Neste exemplo, vamos somar as vendas do vendedor Otávio e da vendedora Gisele nos meses de Janeiro e Fevereiro. Veja abaixo a tabela:

Para realizar a soma vamos usar as funções SOMA e DESLOC juntas, da seguinte maneira:

=SOMA(DESLOC(A1;3;1;2;2))

Explicando a fórmula:

Note que estamos dizendo para o Excel somar os valores de acordo com as informações trazidas pela função DESLOC. E a fórmula funcionará da seguinte maneira:

A função DESLOC irá encontrar onde estão as informações a serem somadas que no caso, são as vendas de Otávio e da Gisele nos meses de janeiro e fevereiro.
Neste caso, o ponto de partida da função DESLOC será a célula A1 e como as informações a serem somadas estão a partir da linha 3, este será o segundo argumento. O terceiro argumento será o número da coluna onde a informação se encontra, no exemplo, a coluna 1.
Como iremos somar também as vendas que estão na linha 5, então precisamos inserir na função DESLOC o argumento altura e largura, que são representados pelos números de linhas e coluna a serem somadas, ou seja, serão 2 linhas e 2 colunas.

Exemplo 2: Usar DESLOC para somar valores de acordo com determinado critério

Outra funcionalidade da função DESLOC é realizar a soma de valores de acordo com determinado critério. E para que você possa compreender como funciona, vamos usar a mesma tabela do exemplo anterior.

Mas neste exemplo, queremos somar os valores de acordo com o critério que será escolhido. Confira o exemplo:

No exemplo, queremos que na célula G4 apareça a soma das vendas do vendedor que estiver na célula F4. Para isso, vamos usar a função DESLOC juntamente com as funções SOMA e CORRESP.

=SOMA(DESLOC(B1:D1;CORRESP(F4;A2:A8;0);))

Explicando a fórmula:

A função CORRESP irá encontrar o número da linha onde o critério se encontra. Sendo assim, a função CORRESP irá procurar na tabela o nome do vendedor que está na célula F4 e retornar a sua posição no intervalo de A2:A8. No caso, ela irá retornar a posição 3, pois o Otávio está na linha 3.
Agora que o Excel já sabe que irá somar as informações que estão na linha 3, então a função SOMA somará estes valores.

Com esta fórmula, se alteramos o nome a ser pesquisado, o valor será atualizado automaticamente. Veja o exemplo abaixo.

Exemplo 3: Como realizar buscas com dois critérios usando DESLOC

A função DESLOC juntamente com a função CORRESP pode ser usada como uma alternativa para tradicional função PROCV, pois com estas duas funções é possível realizar buscas com mais de um critério.

Utilizado o mesmo exemplo acima, neste caso queremos encontrar a venda de determinado vendedor em determinado mês. Para isso, iremos utilizar a função DESLOC e CORRESP juntas da seguinte maneira:

=DESLOC(A1;CORRESP(G3;A2:A8;0);CORRESP(G4;B1:D1;0);1;1)

Explicando a fórmula:

A primeira função CORRESP está buscando a posição relativa do vendedor que está inserido na célula G3. Por exemplo, o vendedor Luís está na posição 3 da matriz.
A segunda função CORRESP busca a posição relativa do Mês que está inserido na célula G4. Por exemplo, o mês de Março está na posição 4 da matriz Mês.
Desta forma, a função DESLOC terá que basicamente cruzar as informações com base no resultado das duas funções CORRESP.

Exemplo 4: Criando lista suspensa com a função DESLOC

Se você precisa criar uma lista suspensa dinâmica, ou seja, uma lista que é atualizada de acordo com as informações que você insere na sua base de dados, então saiba que a função DESLOC pode ser muito útil nesta tarefa.

Para que você possa entender como fazer esta tarefa, suponha que você tenha uma tabela com alguns nomes, e deseja que eles apareçam em uma lista suspensa, de acordo com os nomes que forem sendo inseridos na lista. Para isto, siga as etapas:

1. Selecione a célula que irá receber a lista suspensa. Em seguida, acesse a guia Dados > Validação de Dados > Validação de Dados.

2. Na caixa de diálogo de Validação de Dados, selecione a opção Permitir Lista na guia Configurações.

3. No campo fonte insira a seguinte fórmula:

=DESLOC(A1;0;0;CONT.VALORES(A1:A100);1)

Explicando a fórmula:

Como nossa lista de informações começa na célula A1, então função DESLOC foi aplicada a partir de A1.
Nos argumentos da função DESLOC, deixamos 0 (zero) para linha e coluna, pois não queremos mexer na referência, mantendo-a em A1
A função CONT.VALORES, irá retornar a quantidade de células preenchidas em um intervalo.
No exemplo usamos o intervalo de A1:A100 para cobrir uma lista que pode conter até 99 nomes, mas você pode aumentar como desejar.
O último parâmetro é mantido como 1, pois só queremos uma coluna.

Como resultado temos:

Baixe aqui a planilha com os exemplos apresentados neste artigo.

Confira também as seguintes dicas de Excel:

E então, o que achou da função DESLOC no Excel? Caso tenha ficado com alguma dúvida, basta deixar o seu comentário abaixo.