sexta-feira, 25 de maio de 2012

Dr. Planilha: Como Restringir o Preenchimento de uma Célula Usando Lista Suspensa no #Excel #dica

Vou ensinar hoje uma dica bem interessante para planilhas onde há a necessidade de se restringir o que o usuário pode ou não informar em determinadas células. Mais do que isso, obrigaremos o usuário a escolher um valor de uma lista de valores que serão pré-definidos por nós.

Em nosso exemplo consideraremos que a planilha é um cadastro de clientes, onde informaremos, entre outras coisas, a sigla do Estado onde ele reside. Nosso objetivo será o de evitar que o usuário informe valores diferentes das siglas das unidades federativas do Brasil.

PASSO 1: Criar a lista das unidades federativas
Para criar essa lista nós utilizaremos o recursos de intervalos nomeados. Se você não sabe o que são intervalos nomeados, ou ficar com alguma dúvida no decorrer desta explicação, leia o artigo: Como Usar Intervalos Nomeados nas Fórmulas do #Excel #dica.

A primeira coisa que temos que fazer é criar uma lista contendo todas unidades federativas do Brasil, conforme ilustrado abaixo. Crie essa lista na Plan2, pois usaremos a Plan1 para nosso Cadastro de Clientes.

PASSO 2: Configurar a restrição usando uma lista suspensa com as UFs
Vamos renomear a Plan1 para "Cadastro de Clientes" e criaremos uma tabela básica conforme ilustrado a seguir. Para facilitar a visualização e conferência você poderá configurar esta tabela de forma que ela fique zebrada, com linhas pintadas alternadamente. Não fiz isso no exemplo, mas se você quiser fazer, dê uma lida no artigo Como Fazer uma Tabela Zebrada no #Excel #dica.


Configuração para Excel 2003:
  1. Selecione a célula D2 e clique no menu superior "Dados" e depois em "Validação".
  2. Na janela que se abre, na aba "Configuração", no campo "Permitir" selecione a opção "Lista".
  3. Será liberada a opção "Fonte", preencha com "=sigla_uf" (sem as aspas).

Configuração para Excel 2007/2010:
  1. Selecione a célula D2 e clique na aba "Dados" e depois em "Validação de Dados" que fica no grupo "Ferramentas de Dados".
  2. Na janela que se abre, na aba "Configuração", no campo "Permitir" selecione a opção "Lista".
  3. Será liberada a opção "Fonte", preencha com "=sigla_uf" (sem as aspas).
A janela de configuração da validação é igual para o Excel 2003/2007/2010 e deverá estar preenchida conforme figura exemplo a seguir.

A célula D2 está configurada, o resultado final é o que podemos ver na lista abaixo. Caso o usuário tente informar valores diferentes do que está na lista ele receberá uma mensagem de aviso alertando que os dados são inválidos.

Para finalizar ainda temos que fazer uma coisa, apenas a célula D2 está configurada, porém queremos que todas as células abaixo contenham a mesma formatação. Para fazer isso siga os passos abaixo:
  1. Selecione a célula D2 e pressione CTRL+C
  2. Selecione todas as células da coluna D abaixo que deseja replicar a validação recém criada
  3. Clique com o botão direito do mouse e depois selecione a opção "Colar Especial"
  4. Na tela "Colar Especial", grupo "Colar", selecione a opção "Validação" e clique em OK para salvar.
Está feito, já pode entregar a tabela para os usuários digitarem os dados sem medo.

Disponibilizei abaixo a planilha de exemplo na versão Excel 2003 e Excel 2007/2010.

Um grande abraço do Dr. Planilha!

8 comentários:

  1. consegui validar os dados normalmente..
    Porém, quando insiro um dado não válido o Excel aceita normalmente, não exibindo a mensagem de erro.

    ResponderExcluir
    Respostas
    1. Olá amigo(a) Anônimo!

      Verifique na tela de "Validação", na aba "Alerta de Erro", se está marcada a opção "Mostrar alerta de erro após a inserção de dados inválidos" e se no campo "Estilo" está selecionado o valor "Parar".

      Com exceção disso, a regra deveria estar funcionando para você.

      Abraços do Dr. Planilha!

      Excluir
    2. Sim.. está tudo conforme o explicado.
      Qualquer dado é aceito normalmente..

      Excluir
    3. Bom dia!

      Se preferir, me mande um exemplo por e-mail que eu dou uma olhada.

      pergunteARROBAdoutorplanilha.com.br

      Abraços!

      Excluir
    4. Desculpe por não enviar, mas se tratava de uma planilha do trabalho, com dados internos da companhia, por isso não pude enviar.

      Mas, acho que consegui concertar...
      Eu desmarquei a opção "Ignorar em branco"

      Depois disso ficou normal.. exibindo a mensagem de erro quando inserido o arquivo inválido.

      Obrigado pela atenção!

      Excluir
    5. Tranquilo, fico feliz que tenha resolvido.

      Abraços!

      Excluir
  2. como faço pra inserir novos dados na lista suspensa já existente?

    ResponderExcluir
  3. Dr. Planilha, Boa tarde,


    Estou com uma dificuldade tremena,

    Na minha planilha de orçamento que estou elaborando,
    Todas as minha linha contem 5 colunas nos quais as células estão preenchidas, e apenas uma célula na cor amarela para inserir dado (Valor) no final gerando meu valor total.

    Exemplo
    Porem na segunda, terceira e quarta linha não vou preencher a célula(Valor)pois não preciso no momento......mas na decima sim e quadragézima sétima também,e assim sucessivamente,

    Gostaria de Gerar no mesmo arquivo em planilha separada, uma formula que contenha as linha inteira,onde foram apenas preenchidos os campos em amarelo, escrevo isso porque tenho mil itens mas soh vou cotar 18 por exemplo, ai gostaria de aparecer apenas as que preenchi na outra planilha, mas não apenas a célula que preenchi em sim a linha inteira aonde foi preenchida a célula?

    Por favor, pode me ajudar o quanto antes,

    Muito Obrigado
    diego.faria@elemec.com.br

    ResponderExcluir

Use esse espaço para enviar suas dúvidas ou fazer críticas e sugestões. Todos os comentários serão postados após passarem pela moderação do Dr. Planilha para que seja mantido o alto nível do conteúdo do blog.

Related Posts Plugin for WordPress, Blogger...