Lista de dados com tamanho dinâmico – Excel

Ponto de Equilíbrio Excel - Atingir Meta
Ponto de Equilíbrio Excel – Atingir Meta
15 de agosto de 2011
Gráfico com Alteração Automática por Loja - Funções Desloc e Corresp do Excel
Gráfico com Alteração Automática por Loja – Funções Desloc e Corresp do Excel
24 de agosto de 2011

Lista de dados com tamanho dinâmico – Excel

Objetivo: Demonstrar como criar uma validação de dados para uma lista de valores aonde possam ser cadastrados novos valores, sem deixar campos em branco.

Esta dica foi baseada em um vídeo de Robert Martin a quem agradeço.

A validação de dados no Excel permite garantir a integridade de dados de uma coluna. Como podemos ver no artigo: http://guiadoexcel.com.br/validacao-de-dados-excel. Uma das validações possíveis é a feita a partir de uma lista de valores, aonde somente poderão constar nas células definidas com esta validação os dados desta lista. Esta validação possui um probleminha, se você for incluir mais dados nesta lista de validação será necessário incluir valores entre esta lista, ou deixar espaços em branco abaixo da lista, o que ocasiona que na lista de validação também hajam valores em branco, criando uma lista não muito profissional. Para resolver esta deficiência vamos fazer o seguinte: 1. Crie uma lista de valores em uma coluna, para este exemplo utilizamos a coluna G: 2. Clique na guia Fórmulas->Definir Nome 3. Na janela que segue digite os seguintes dados:

  • Nome: Digite um nome para o intervalo, no caso colocamos o nome Lista
  • Comentário: É bom colocar algum comentário descrevendo o objetivo destes dados, pois depois de um tempo pode ficar difícil saber a serventia quando houverem muitos nomes definidos em uma planilha.
  • Refere-se a: Neste campo você deve digitar a seguinte fórmula: =DESLOC(Plan1!$G$1;0;0;CONT.VALORES(Plan1!$G:$G);1)

Sendo que esta fórmula faz o deslocamento da altura do intervalo nomeado deste intervalo. Note que no campo destinado a altura da fórmula DESLOC foi utilizada a fórmula CONT.VALORES que permite que a altura da lista de dados seja maior ou menor, conforme a necessidade. Esta é a chave da solução do problema, vamos agora aplicá-la. 4. Selecione a célula aonde quer aplicar a validação, no nosso caso A1, e clique na guia Dados->Validação de dados 5. Selecione em Permitir: a opção Lista e em fonte digite =Lista. Agora a validação está correta, lembrando que o intervalo nomeado também pode ser referente a outra planilha dentro do mesmo arquivo, o que permite uma melhor organização do arquivo.

Download “Lista de dados com tamanho dinâmico” ListaDadosExcelTamanhoDinamico-1.zip – Baixado 42 vezes – 16 KB

Abraço

Marcos Rieper

6 Comentários

  1. Walter Bernal disse:

    Olá Marcos, tudo bem?

    + um ótimo artigo parabéns! 🙂

  2. Silmar disse:

    Marcos, parabéns! Seu site está cada vez melhor, inúmeros artigos muito interessantes. Gostaria de aproveitar para sugerir que você faça alguns artigos sobre “Excel + Internet explorer”, ou seja, como acessar sites, clicar sobre combos/listbox etc. Sobre este assunto não há no Brasil nenhum site abordando este assunto. Por ser um assunto pouco explorado, acredito que seu site poderia ter praticamente todos os acessos de pessoas interessadas neste assunto. Abraços e sucesso!

  3. Giovana disse:

    Olá Marcos, gostei do seu site.. e gostaria de pedir se possível para você nos ensinar como fazer gráficos de semaforo e velocímetro.

    Abçs

  4. […] a fórmula =DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1;1), que é explicada no artigo : http://guiadoexcel.com.br/lista-de-dados-com-tamanho-dinamico-excel e que faz com que o endereço se refira a intervalos variáveis, que é o que precisaremos aqui, […]

  5. Michel disse:

    Bom dia, Marcos.

    Muito obrigado pelas dicas, estão sendo muito úteis. Essa de lista de dados com tamanho dinâmico “me quebrou uma árvore”, gostei mesmo.

    Abraços

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.