Validação de dados com lista variável e tamanho variável no Excel

Recibo de Pagamento de Salário / Holerite em Excel / Contracheque
10 de julho de 2016
Curso de Excel Básico Guia do Excel
Lançamento do Curso Excel Básico
18 de julho de 2016

Validação de dados com lista variável e tamanho variável no Excel

Validação de dados com lista variável . Você já teve a necessidade de criar uma lista baseada em outra lista? Por exemplo, uma lista aonde você tenha as unidades federativas (estados) e na outra as cidades daquela cidade na mesma linha?

Este artigo demonstra como criar uma validação de dados com lista variável e tamanho variável no Excel, ou seja, criar uma lista de dados baseada em um critério, como o estado, e listar os seus itens ajustando inclusive a quantidade de registros exibidos.

Vamos ver como criar passo-a-passo esta lista.

  1. Crie 3 planilhas:
    • Cadastro: aonde criaremos um cadastro simples de clientes;
    • Base: servirá como base para a lista de cidades e estados;
    • Cálculos: realizaremos cálculos para que a lista de cidades seja criada nesta planilha.
  2. Na planilha Cadastro crie um cadastro simples conforme a imagem, digitando apenas os nomes, não se preocupe com a formataçãoValidação de dados com lista variável e tamanho varíavel no Excel 1
  3. Selecione a lista até a linha 2 e selecione em Página Inicial a opção Formatar como tabela, selecionando um estilo e marque a opção minha tabela contém cabeçalhos. Automaticamente a planilha já será configurada.
  4. Na coluna Id utilize a seguinte fórmula =lin()-1, para que seja gerado um cadastro um novo código de cliente á medida em que seja inserida nova linha.
  5. Clique em Base e digite a lista de UFs e na célula A1 digite as UFs seguintes:[table id=3 /]
  6. Na célula D1 digite a lista de cidades do Brasil, baixe no link abaixo a lista:
  7. Download “Planilha Lista de cidades do Brasil” Lista-de-cidades-do-Brasil.zip – Baixado 46 vezes – 107 KB

  8. Na célula C1 digite a função =D1&”-“&CONT.SE($D$1:D1;D1) e arraste para todas as linhas, esta função irá criar uma chave única para todas as cidades, de modo que possamos usar o PROCV para retorná-los em cálculos.Validação de dados com lista variável e tamanho varíavel no Excel 2
  9. Na planilha Cálculos digite em A1 SC, na célula B1 digite 1, na B2 digite 2, selecione B1 e B2 e arraste a seleção até a linha B700 de modo que tenhamos uma lista sequencial.
  10. Na célula C1 digite a função =SEERRO(PROCV($A$1&”-“&B1;Base!C:F;4;0);1), esta função irá realizar uma busca das cidades conforme o Estado selecionado. Arraste esta fórmula até a linha C700.
  11. Clique na guia Fórmulas e no botão Gerenciador de Nomes
  12. Clique no botão Novo, nomeie como Estado e digite a fórmula =DESLOC(Base!$A$1;0;0;CONT.VALORES(Base!$A:$A);1) esta fórmula irá retornar a lista de estados.Validação de dados com lista variável e tamanho variável no Excel 3
  13. Clique novamente na guia fórmulas em Gerenciador de nomes e no botão Novo
  14. Nomeie-o como Cidade e digite a fórmula =DESLOC(Cálculos!$C$1;0;0;CONT.SE(Cálculos!$C$1:$C$700;”<>1″);1) esta fórmula irá retornar as cidades conforme o estado digitado em A1 na planilha Cálculos.
  15. Clique na planilha Cadastro e nas células abaixo da coluna UF, clique na guia Dados na opção Validação de Dados.
  16. Selecione a opção Lista e em Fonte digite =Estado. Isso fará com que neste campo sejam exibidos todos os Estados para seleção.Validação de dados com lista variável e tamanho variável no Excel 4
  17. Selecione as linhas abaixo da coluna Cidade
  18. Clique na guia Dados e na opção Validação de dados
  19. Selecione a opção Lista e em Fonte digite =Cidade
  20. Agora habilite a guia Desenvolvedor e clique em no botão Visual Basic
  21. Clique duas vezes sobre a Planilha 1 (Cadastro) e digite o seguinte código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 5 Then
        Sheets("Cálculos").Range("a1").Value = Range("D" & Target.Row).Value
    End If
End Sub

Este código fará com que ao selecionar uma célula da coluna 5, o sistema automaticamente altere o valor da célula Cálculos!A1 para a UF da linha atual, mudando toda vez que for alterada a linha.

Estados e cidades Excel

Download “Validação de dados com lista variável e tamanho varíavel no Excel” Validação-de-dados-com-lista-variável-e-tamanho-varíavel-no-Excel.zip – Baixado 64 vezes – 205 KB

Abraço

Marcos Rieper

5 Comentários

  1. Sandra Helena disse:

    mudou a minha vida!! srrsrsrsrrs adorooooo

  2. Francis Valdo de Oliveira Tunes disse:

    Olá! Sou mesmo um iniciante em Excel, ou melhor, era, até acessar aqui. Muito bom mesmo. Já estou até ajudando algumas pessoas a fazer algumas “coisinhas” no Excel. kkkkkkk Pode um negócio desse!!!

  3. Francis disse:

    Muito bom mesmo. Comecei procurando uma curiosidade primária minha, sobre Excel. Achei um vídeo com a responsabilidade de Marcos Rieper. Pronto!!! Me identifiquei com a fala deste… desde então é um vídeo após o outro, é claro que alguns videos, como esse último (estados/cidades) uma tabela, tive que voltar várias vezes, é lógico. Mas hoje tenho minha próprio tabela, que há de ser muito útil. Enfim, de alguém que estava procurando somar/subtrair células, eu estou desenvolvendo planilhas na área de avançadas. Valeu Sr Marcos Rieper.

  4. Flávio disse:

    Boa tarde, Marcos.

    É possível estender este método? Isto é, por exemplo, seleciono o estado numa célula, ele atualiza a lista de cidades na seguinte e numa terceira célula, a partir da cidade selecionada ele atualizar uma lista de bairros?

    Grato.

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.