Intervalos dinâmicos no Excel: usando uma lista de validação de dados

Usando as funções DESLOC e CONT.VALORES é possível criar Intervalos Dinâmicos no Excel que variam de acordo com os dados inseridos pelo usuário.

O objetivo é criar uma lista de validação de dados – para que não seja necessário digitar a especialidade – e criar fórmulas para retornar o nome do médico e o horário.

Vamos dividir a resolução do problema em 5 etapas:

• Criar uma validação de dados com lista suspensa;
• Criar uma fórmula dinâmica usando as funções DESLOC e CONT.VALORES;
• Transformar a fórmula em uma fórmula nomeada;
• Associar o nome à validação de dados.
• Buscar os dados usando fórmulas de pesquisa.

Criando a validação de dados

Baixe a planilha para praticar

• Com o arquivo aberto, clique na planilha Consultas e selecione o intervalo D4:D9.
• Clique na guia Dados → grupo Ferramentas de Dados → Validação de Dados.
• Na guia Configurações, em Permitir, escolha Lista.
• Em Fonte, clique na planilha Cadastro e selecione o intervalo C3:C17.

• Clique em OK.
• Clique na seta da validação de dados e veja a lista de especialidades.

Intervalos dinâmicos no Excel

Ao cadastrar novas especialidades na planilha Cadastro, estas especialidades não vão aparecer na lista de validação de dados, pois o intervalo vai só até a célula C17.

Para que as novas informações inseridas entrem automaticamente na lista de validação de dados, vamos criar uma fórmula dinâmica com as funções DESLOC e CONT.VALORES.

Entenda como cada função funciona individualmente.

Função DESLOC

Esta funçã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.

A função DESLOC é considerada uma função auxiliar, ela é muito importante em combinação com outras funções. Dificilmente ela terá uso sozinha.

Por exemplo, a fórmula SOMA(DESLOC(C2;1;2;3;1)) calcula o valor total de um intervalo formado por 3 linhas e 1 coluna que está 1 linha abaixo e 2 colunas à direita da célula C2.

São 5 os argumentos da função DESLOC. Veja a seguir:

• Ref – a referência na qual você deseja basear o deslocamento.
• Lins – o número de linhas, acima ou abaixo, a que se deseja que a célula do resultado se refira.
• Cols – o número de colunas, à esquerda ou à direita, a que se deseja que a célula do resultado se refira.
• Altura – a altura, em número de linhas, que se deseja para a referência fornecida.
• Largura – a largura, em número de colunas, que se deseja para a referência fornecida.

É importante conhecer os argumentos da função, pois na validação de dados não é possível usar o assistente de função. Vamos ter que digitar a fórmula.

Função CONT.VALORES

Esta função é bem simples. Ela conta o número de células que não estão vazias em um intervalo.

A função CONT.VALORES conta as células contendo qualquer tipo de informação, incluindo valores de erro e texto vazio (“”), com exceção das células vazias.

Apesar de a função ser simples, ela é super importante neste contexto, pois vai definir até onde o intervalo da lista vai. Ou seja, até onde encontrar dados preenchidos na coluna.

Criando os intervalos dinâmicos no Excel

O intervalo dinâmico no Excel será criado com uma fórmula que combina duas funções. Para criar a fórmula, clique em uma célula vazia da planilha Consultas e digite a fórmula:

=DESLOC(Cadastro!$C$3;0;0;CONT.VALORES(Cadastro!$C$3:$C$3000);1)

Com esta fórmula estamos criando um intervalo que começa na célula C3 da planilha Cadastro e vai até onde encontrar a última célula preenchida do intervalo C3:C3000.

O número 3000 é um exemplo. Você pode usar um valor maior ou menor, de acordo com sua necessidade.

E-book Gratuito: 7 Recursos Essenciais que você precisa conhecer no Excel

Cadastre seu e-mail e receba o e-book

Baixar

Usando fórmula nomeada

Poderíamos usar a fórmula diretamente na validação de dados, mas em vez disso vamos criar um nome para esta fórmula.

O uso de nomes vai facilitar a inserção da fórmula em células diferentes, além de tornar mais simples a possível manutenção da fórmula.

Vamos ao passo a passo da criação do nome:

• Selecione a fórmula que foi criada e pressione Ctrl+C para copiar.
• Clique na guia Fórmulas → grupo Nomes Definidos → Definir Nome.
• Em Nome, digite especialidades.
• Em Refere-se a, pressione Ctrl+V para colar a fórmula.

• Clique em OK para criar o nome.

Usando o nome na validação de dados

• Na planilha Consultas, selecione o intervalo D4:D9.
• Clique na guia Dados → grupo Ferramentas de Dados → Validação de Dados.
• Em Fonte, apague o intervalo =Cadastro!$C$3:$C$17, digite =especialidades e clique em OK.
• Clique na planilha Cadastro e cadastre outra especialidade, como por exemplo, Cirurgião Plástico.
• Clique na planilha Consultas e clique na lista de validação de dados.
• A especialidade cadastrada apareceu na lista.

Usando o nome nas fórmulas

Vamos aproveitar a fórmula e criar outro nome que servirá como referência para a base de dados.

Esta nova fórmula substituirá o intervalo da função ÍNDICE. Vamos ao passo a passo:

• Clique na guia Fórmulas → grupo Nomes Definidos → Gerenciador de Nomes.
• Clique no nome especialidades e em seguida no botão Editar.
• Em Refere-se a, selecione a fórmula e pressione Ctrl+C para copiar.
• Clique no botão Novo. Em Nome, digite dados. Em Refere-se a, pressione Ctrl+V para colar.
• Altere a fórmula para:

=DESLOC(Cadastro!$B$3;0;0;CONT.VALORES(Cadastro!$C$3:$C$3000);4)

Com isso, estamos criando um intervalo que começa na coluna B (nome do médico) e vai até a coluna dia da semana, configurando assim, um intervalo de 4 colunas.

• Clique em OK. Em seguida feche a caixa de diálogo Gerenciador de Nomes.
• Clique na planilha Consultas, selecione a célula E4 e digite a fórmula:

=SEERRO(ÍNDICE(Dados;CORRESP(D4;especialidades;0);1);"")

• Copie a fórmula para baixo. Clique na célula F4 e digite a fórmula:

=SEERRO(ÍNDICE(Dados;CORRESP(D4;especialidades;0);3);"")

• Copie a fórmula para baixo.

Cadastre novos dados na planilha Cadastro e veja a atualização na lista de validação de dados e nas fórmulas.

Para saber mais sobre as funções ÍNDICE e CORRESP, clique aqui.

A videoaula para este assunto foi dividida em 2 partes:

• Videoaula sobre ÍNDICE e CORRESP
• Videoaula sobre Intervalos Dinâmico no Excel

Participe

O que achou dos Intervalos Dinâmicos no Excel? Deixe sua opinião nos comentários.

Se este conteúdo foi relevante para você, convide seus amigos para que eles também fiquem sabendo deste material.

Um abraço e até a próxima.

E-book Gratuito: 7 Recursos Essenciais que você precisa conhecer no Excel

Cadastre seu e-mail e receba o e-book

Baixar