Como utilizar a função DESLOC no Excel com artigo com imagens, vídeo-aula e download da planilha de exemplo gratuita.
Sintaxe da Função Desloc Excel
A função Desloc do Excel tem por finalidade redimensionar e deslocar o intervalos de células.
A sintaxe da função é: =DESLOC(ref; lins; cols; [altura]; [largura])
- ref: Intervalo de células a ser deslocado e/ou redimensionado.
- lins: Quantidade de linhas que será deslocado o endereço pra cima ou pra baixo. Positivo para baixo e negativo para cima.
- cols: Quantidade de colunas deslocadas à esquerda ou direita. Positivos à direita e negativos à esquerda.
- [altura]: Aumenta ou reduz a quantidade de linhas. Positivo aumenta e negativo reduz a quantidade de linhas.
- [largura]: Aqui aumentamos ou reduzimos a quantidade de colunas. Positivo aumenta a quantidade de linhas e negativo reduz.
Como Alterar Tamanhos de Intervalos de Células com Fórmula no Excel
No download da planilha no botão ao final deste artigo, temos uma planilha para auxiliar no entendimento da função DESLOC.
Veja que abaixo temos uma célula colorida, ela é alterada conforme os parâmetros passados na tabela à direita da planilha.
Nela pode alterar o endereço da referência de célula também no primeiro campo.
Perceba que abaixo temos a alteração do parâmetro linhas, ao colocar 5, deslocamos para 5 linhas abaixo.
Digitando 2 em colunas deslocamos duas colunas à direita.
Em largura alteramos para 2 e com isso é aumentada a largura em duas colunas.
A fórmula toda seria: =DESLOC(B7;5;2;40;2).
Criar uma Lista de Tamanho Variável Excel
No exemplo abaixo temos o aumento do tamanho de uma lista utilizando a função Desloc pela quantidade de valores da lista.
Desta forma usamos a função =DESLOC(C6;;;CONT.VALORES($C$6:$C$21)).
Com isso podemos criar uma lista de validação de dados.
Clique em Fórmulas->Gerenciador de Nomes e clique em Definir Nome. Coloque esta função: =DESLOC(’27-30 DESLOC 2′!$C$6;;;CONT.VALORES(’27-30 DESLOC 2′!$C$6:$C$21)) e coloque um nome coo ListaCidades.
Veja que temos na fórmula o uso da função CONT.VALORES, ela irá contar a quantidade de valores que temos lista e alterar o tamanho conforme adicionarmos valores.
Nele usamos a função que citamos anteriormente.
Clique em Dados->Validação de dados->Lista e digite =ListaCidades, desta forma teremos na célula uma lista que poderemos selecionar os itens da cidade.
Então na célula temos a lista de cidades para selecionarmos.
Gráfico com Tamanho Variável Usando Desloc Excel
Outra situação comum que temos é a necessidade de gráficos que se ajustam de tamanho conforme a quantidade de registros na lista ou tabela.
Abaixo temos uma lista com valores que desejamos criar um gráfico.
Para isso usamos uma fórmula muito semelhante a utilizada no exemplo anterior.
A primeira parte é criar um intervalo definido para valor e outro para o nome do vendedor.
Para isso crie este nome definido _Valor: =DESLOC(’27-30 DESLOC 3′!$D$6;;;CONT.VALORES(’27-30 DESLOC 3′!$C$6:$C$19))
E para _Vendedor use a função: =DESLOC(’27-30 DESLOC 3′!$C$6;;;CONT.VALORES(’27-30 DESLOC 3′!$C$6:$C$19))
O intervalo definido _Valor se refere aos valores do gráfico e _Vendedor ao intervalo de vendedores.
Após isso crie um gráfico selecionando o intervalo normal, na tabela e configure como desejar.
Depois clique com o botão direito em editar gráfico e altere em séries os valores da série para o intervalo conforme abaixo, veja que está o caminho completo do nome do arquivo e do intervalo definido.
Faça o mesmo para os rótulos de eixo do gráfico, conforme temos abaixo
Com isso temos que ao alterar os dados na tabela o gráfico é redimensionado automaticamente conforme a quantidade de linhas.
Download da Planilha Função Desloc Excel
Para realizar o download desta planilha de exemplo clique no botão abaixo.