Fórmula Desloc

Função DESLOC

Descrição

Retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas.

Sintaxe

DESLOC(ref, lins, cols, [altura], [largura])

A sintaxe da função DESLOC tem os seguintes argumentos

• Ref  Obrigatório. A referência na qual você deseja basear o deslocamento. Ref deve ser uma referência a uma célula ou intervalo de células adjacentes; caso contrário, DESLOC retornará o valor de erro #VALOR!.
• Lins  Obrigatório. O número de linhas, acima ou abaixo, a que se deseja que a célula superior esquerda se refira. Usar 5 como o argumento de linhas, especifica que a célula superior esquerda na referência está cinco linhas abaixo da referência. Lins podem ser positivas (que significa abaixo da referência inicial) ou negativas (acima da referência inicial).
• Cols  Obrigatório. O número de colunas, à esquerda ou à direita, a que se deseja que a célula superior esquerda do resultado se refira. Usar 5 como o argumento de colunas, especifica que a célula superior esquerda na referência está cinco colunas à direita da referência. Cols pode ser positivo (que significa à direita da referência inicial) ou negativo (à esquerda da referência inicial).
• Altura  Opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.
• Largura  Opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.
•  

Essa fórmula é uma das mais importantes do Excel. Dificilmente encontra-se conteúdo didático sobre a fórmula, mas seu poder é fundamental para criarmos um sistema em Excel. Basicamente, a fórmula serve para procurar ou montar intevalos de seleção!
A planilha que criei para escrever este post está disponível aqui: combobox
=desloc(qual célula que será deslocada? ; quantas linhas? ; quantas colunas? ; qual a altura do intervalo? ; Qual a largura do intervalo?)
Esta fórmula serve para criarmos uma seleção de células, ou selecionar uma célula. Vamos a um exemplo prático:
Selecionando uma célula:
Imagine que você tem um banco de dados que possua 2 colunas e 10 linhas digitadas conforme segue:

Vendedor	Número de Registro no sistema
Daniel	12
Eduardo	2340
Bruno	20492
Carol	1034
Ana Carolina	1004
Rosangela	4902
Alessandra	49
Gleice	593
Tatiane	3956

N
Objetivo: criar um combobox incluindo todos os nomes dos vendedores e prevendo a inclusão de novos vendedores na listagem. No Excel 2007, selecione a aba “Desenvolvedor”, depois clique em “inserir” e selecione a “Caixa de Combinação”:

Criar um combo box

1) OBS: Caso a aba “Desenvolvedor” não esteja disponível, clique no “Botão do Office”, em seguida em “Opções do Excel”:

Disponibilizando a aba "Desenvolvedor" - Passo 1

2) Depois cá em “Mais Usados” e selecione a caixa: “Mostrar guia Desenvolvedor na Faixa de Opções”

Disponibilizando a guia "Desenvolvedores" - Passo 2

3) Continuando o entendimento da fórmula DESLOC, eis onde queremos chegar:

Lista - Combobox - Vendedores

Para isso, siga os passos:
1) O primeiro passo é criar o intervalo “vendedores” com nossa fórmula “Desloc”. Para isso, clique na aba “Fórmulas”, e depois em “Gerenciar nomes”:
2) Clique em “Novo” e insira o nome  “vendedores” e depois a fórmula DESLOC:

Criando a seleção de células "Vendedores"

Veja que no campo “Refere-se a:” já está escrita a fórmula Desloc. Vamos entender:
=desloc(qual célula que será deslocada? ; quantas linhas? ; quantas colunas? ; qual a altura do intervalo? ; Qual a largura do intervalo?)
=DESLOC(plan1$A$1 –> Esta será a célula que será deslocada. Ou seja, lembra do nosso objetivo? Nós queremos criar um intervalo de seleção de células. Neste caso, queremos criar um intervalo que selecione todas as células que são nome de vendedores. Assim, conforme solicita a fórmula desloc, “qual célula que será deslocada”, no nosso caso, se refere a célula da planilha1, A1 “=desloc(plan1$A$1)”. Entenda que a célula A1 será deslocada 1 linha para baixo, pois a seleção dos nomes dos vendedores inicia na célula “A2″. É a partir dessa célula que o intervalo “Vendedores” inicia. Dessa forma é possível entender a continuidade da fórmula desloc:

Seleção das células que são Vendedores. O intervalo "Vendedores"

=desloc(qual célula que será deslocada? ; quantas linhas?
=desloc(plan1$A$1 ; 1 –> Colocamos o número um após o primeiro “;” pois o intervalo deve iniciar da célula A2 em diante, ou seja. A célula A1 será deslocada uma célula para baixo para inciar o intervalo!
A próxima pergunta da fórmula é: “quantas colunas?”: Ora, se nosso intevalo está na mesma coluna do início da nossa fórmula, não é necessário descolocar nenhuma coluna. Assim, podemos inserir o valor 0 (zero), pois os vendedores estão na mesma coluna:
=desloc(qual célula que será deslocada? ; quantas linhas? ; quantas colunas?
=DESLOC(plan1$A$1 ; 1 ; 0
A próxima pergunta da fórmula é: “qual a altura do intervalo“. Aqui utilizamos uma “manha” para conseguir a altura, que nada mais significa: quantos vendedores existem? Ou seja, qual a altura do intervalo (cumprimento vertical).
A fórmula que utilizamos é a “Cont.Valores(intervalo)”. Esta fórmula conta quantas células tem valor em um determinado intervalo (seleção). Assim, se eu contar quantas células estão preenchidas na “coluna A” inteira chegaremos ao valor de 11, pois na “coluna A” temos o título e mais 10 vendedores! (Faça o teste desta fórmula no seu Excel!)
Assim, o resultado da fórmula CONT.VALORES(Plan1!$A:$A)-1 será 10! Ou seja, há 11 registros nas células da “coluna A” menos o título. Assim, a altura de nosso intervalo será 10. Com a fórmula cont.valores nós resolvemos o futuro problema caso haja novos vendedores! Não é legal???
=desloc(qual célula que será deslocada? ; quantas linhas? ; quantas colunas? ; qual a altura do intervalo?
=DESLOC(Plan1!$A$1;1;0;CONT.VALORES(Plan1!$A:$A)-1
E para finalizar, qual a largura do intervalo: Nós iremos selecionar somente 1 coluna correto? Ou seja, somente a coluna dos vendedores. Assim, a largura de nosso intervalo é 1, ou seja, somente uma coluna.
Formula final!
=desloc(qual célula que será deslocada? ; quantas linhas? ; quantas colunas? ; qual a altura do intervalo? ; Qual a largura do intervalo?)
=DESLOC(Plan1!$A$1;1;0;CONT.VALORES(Plan1!$A:$A)-1;1)
Por fim, agora basta selecionarmos o valor “vendedores” na caixa de combinação para que a lista esteja disponível:
1) Clique com botão direito em cima do combo box (Drop-down 1) e clique em seguida em “Formatar controle”

Formatar controle!

2) Agora basta digitar “vendedores” no campo “Intervalo de entrada:” e clique em “OK”

Insira o nome "vendedores"

Pronto, sua lista foi criada com sucesso!!!

Lista criada com sucesso!

Para finalizar, segue a planilha que utilizei como base: combobox