[FUNÇÕES] DESLOC: Criando uma lista suspensa variável

Este artigo tem como objetivo responder uma dúvida que um usuário postou num fórum. A dúvida é a seguinte:

Gosto muito do recurso VALIDAÇÃO DE DADOS e queria fazer com que por exemplo o dado da célula “H3” exibisse uma lista suspensa com os dias de fevereiro dentro do intervalo do dia 1 ao dia 13 conforme eu determinei na quantidade de dias letivos para o mês de fevereiro na célula “Q3”.

DESLOC - LISTA SUSPENSA 01

Vamos a uma alternativa de solução usando a função DESLOC:

1º) Para ilustrar a solução, reproduzi parcialmente as colunas que nos interessam para a solução, no caso a coluna H e coluna Q, e adicionei uma nova coluna V que contém uma serie contínua até o máximo de dias letivos. É nesta série que a função DESLOC irá gerar a lista de acordo com os dias letivos de desejados.

DESLOC - LISTA SUSPENSA 02

2º) O próximo passo é desenvolver a fórmula que servirá para a nossa validação de dados. É aqui que aplicamos a poderosa função DESLOC, que cria um novo intervalo virtual de acordo com os argumentos passados.

A função DESLOC possui 5 argumentos que vou relacionar abaixo conforme publicação oficial da Microsoft:

Sintaxe

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

  • 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.

Vamos entender na prática estes argumentos aplicados no nosso problema:

  • ref    será a célula de partida do intervalo que desejamos criar. No nosso caso a escolha mais lógica seria a célula V2, pois assim podemos zerar os próximos 2 argumentos, pois não será necessário “deslocar” nenhuma linha ou coluna para gerar o intervalo. É essencial que esta referência seja absoluta, ou seja, $V$2, pois para todas as linhas o ponto de partida é sempre o mesmo, não podendo variar, assim travamos com o cifrão a referência tanto para linha como para coluna.
  • lins    Será 0 (zero), pois conforme explicado no argumento ref, não precisamos deslocar nenhuma linha.
  • cols    Será 0 (zero), pois conforme explicado no argumento ref, não precisamos deslocar nenhuma coluna.
  • altura    Será a célula Q3, pois ela que define quantos elementos do intervalo deverão compor a série. A nossa referência deverá ser mista, mantendo a coluna absoluta e a linha relativa, ou seja, será “$Q3”, pois ao aplicar a fórmula para outra linha, desejamos que a fórmula considere a linha “relativa” a célula onde está a fórmula, e a coluna neste caso sempre será a mesma.
  • largura    Será 1, pois nossa série tem somente 1 coluna de largura.

Assim, a nossa fórmula de validação que gerará o intervalo desejado será:

=DESLOC($V$2;0;0;$Q3;1)

3º) Chegou a hora de aplicar a fórmula na validação de dados, e como desenvolvemos ela com base na linha 3, é importante selecionar o intervalo que receberá a validação partindo da linha 3, e então aplicar a validação de dados aplicando a fórmula no campo da fonte:

DESLOC - LISTA SUSPENSA 03

Pronto! Agora cada célula da coluna H que recebeu a validação terá sua lista de validação de acordo com a quantidade de dias letivos definidos na coluna Q.

Baixe o arquivo com modelo deste exercício aqui: Modelo ExcelManíacos – DESLOC – LISTA SUSPENSA

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s