Criar Listas de Dados com Base em Outra Lista Excel

Função de Conversão de Data Excel
25 de julho de 2010
Gráfico de Ponteiro
Gráfico de Ponteiro Excel – Velocímetro
26 de julho de 2010

Resultado - Clique para ampliar

CRIAR LISTAS DE DADOS COM BASE EM OUTRA LISTA Excel

Hoje faremos a criação de uma lista baseada em outra lista. Para isso veremos um exemplo de DESLOC e CORRESP.

Criar as Listas

  • Primeiro crie a lista de dados conforme a figura abaixo:
Tabela de Dados

Tabela de Dados

  • Nesta lista de dados temos as sucursais e os vendedores que compõem cada uma.
  • Selecione as células B1:E1 referente as sucursais e nomeie o intervalo como Sucursais.
  • Selecione o intervalo A1:E6 e nomeie o intervalo como Vendedores.
Sucursais

Sucursais

Criando as Listas de Dados

  • Selecione a célula C10, clique na aba Dados->Validação de Dados->Validação de Dados e configure as opções conforme a figura:
Lista das Sucursais

Lista das Sucursais

  • Clique em OK, teremos criado a primeira lista, que trará as sucursais cadastradas.

Criando a Lisa de vendedores

  • Clique na aba Fórmulas e selecione o botão Gerenciador de Nomes.
  • Clique no botão Novo.
Lista Vendedores

Lista Vendedores

  • No campo Nome digite ListaVendedores, no campo Refere-se a: digite =DESLOC(Vendedores;0;CORRESP(Plan1!$C$10;Sucursais;0);5;1)

Entendendo a fórmula:

A fórmula DESLOC retorna uma célula ou uma lista de dados a partir de um deslocamento de linhas e colunas, conforme a fórmula DESLOC(ref, lins, cols, [altura], [largura]).

  1. ref: é a célula ou o intervalo de células aonde se basear.
  2. lins: o número de linhas acima ou abaixo.
  3. cols: o número de colunas á direita ou esquerda.
  4. altura: o número de linhas, é um campo opcional.
  5. largura: a largura do intervalo, também opcional, mas normalmente o valor é 1.

A fórmula CORRESP funciona retornando a posição de um item especificado de uma lista, conforme a fórmula CORRESP(valor_procurado, matriz_procurada, [tipo_correspondência]).

  1. valor_procurado: valor que deseja localizar.
  2. matriz_procurada: intervalo de células aonde procurar.
  3. tipo_correspondência:  conforme a figura:
Tipo de dados

Tipo de dados – Clique para ampliar

Ou seja, iremos trazer a lista de dados deslocando conforme a sucursal em que o vendedor está.

Lista de Vendedores:

  • Selecione a célula D10, clique em Dados->Validação de Dados e configure conforme a figura:

Validação de dados Excel lista

  • Clique em OK, e estará pronto, ao mudar o valor da célula C10, a lista da célula D10 será automaticamente alterada.
Resultado

Resultado – Clique para ampliar

É isso pessoal, obrigado pela visita ao Blog.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

40 Comentários

  1. Douglas Ribeiro disse:

    Valeu Marcos…..
    Já Atualizei….
    Seu blog tb eh excelente…
    mto bacana sua iniciativa….
    Parabéns….
    Acho que estamos oferecendo um material bacana que pode ajudar muita gente….
    é isso aí….
    abraços
    Douglas Ribeiro

  2. Tiago disse:

    Olá, não entendi (não consegui ir adiante)a parte q fala da aba formulas…

    “Criando a Lisa de vendedores

    Clique na aba Fórmulas e selecione o botão Gerenciador de Nomes.
    Clique no botão Novo.” ???

    Aguardo explicação…
    Valeu.

  3. […] 4. Faça uma lista com os períodos apresentados e digite a fórmula ao lado, inclusive para a loja =DESLOC(B1;0;$B$15), sendo que  você pode ler mais sobre esta fórmula em: http://guiadoexcel.com.br/criar-listas-de-dados-com-base-em-outra-lista. […]

  4. Raul Oliveira disse:

    Cara,

    Pelo amor de deus, me ajuda aqui.
    Até essa etapa eu ja tinha feito, porem agora estou tentando fazer 4 etapas seguidas.

    Tem alguma forma de fazer isso? Realmente esta muito complicado.

  5. adeilton marinho disse:

    Marcos como faço para mim cadastar no seu blog???

    Aguardo retorno,

    Adeilton Marinho

  6. Cauhê Ferreira disse:

    Cara mais que bom, EXCELENTE!

    consegui fazer sem mistérios, mas… rsrs eu estou desenvolvendo um trampo pra um cliente particular e eu preciso fazer na planilha três listas, correspondência com três listas ( seleciono a primeira, a segunda responde a primeira e a terceira responde a segunda), pra que a segunda responda a primeira eu faço o mesmo processo?
    agradeço a atenção desde já pelo excelente post! vlw

  7. Cauhê Ferreira disse:

    valeu Marcos pela resposta rápida!
    novamente, Excelente!

  8. Cauhê Ferreira disse:

    Marcos eu tentei fazer hoje aquelas três correspondências
    mas acho que pequei na hora de organizar os dados você poderia me dar uma dica?
    desde já agradeço novamente.

  9. Cauhê Ferreira disse:

    Marcos se v puder me dar uma ajuda com a minha duvida agradeço rsrs tá tenso achar uma solução kkk VLW

  10. Giovani Costa disse:

    Olá, Fiz todos os passos corretamente mas não consegui cumprir o último passo do post… colocando na lista de validação “=Vendedor_Sucursal” da a seguinte mensagem; “um intervalo nomeado especificado por você não pode ser encontrado”.

    Aguardo ajuda!

  11. Catarina disse:

    Oi. Eu também não consegui cumprir o ultimo passo como Giovani 🙁 Alguém tem solução?
    Obrigada.

  12. stef disse:

    eu também estou com o mesmo erro :S não consigo por a segunda lista porque não está criada o “Vendedor_Sucursal” e nem mesmo a por o “ListaVendedores” que tinhamos criado…cria uma lista na célula mas nao aparece nenhuma opçao :S Obrigado 🙂

  13. stef disse:

    já consegui 🙂 obrigado

  14. Anthony disse:

    eu também estou com o mesmo erro :S não consigo por a segunda lista porque não está criada o “Vendedor_Sucursal” e nem mesmo a por o “ListaVendedores” que tinhamos criado…cria uma lista na célula mas nao aparece nenhuma opçao :S Obrigado

    o que pode ser???
    me ajudem plz

  15. guto disse:

    boa dia.. o link está quebrado, se você puder me enviar por email eu ficarei agradecido.

    obrigado antecipadamente pela ajuda

  16. Renata disse:

    Olá,
    Também tive problemas com o último passo, aparece que a lista não está criada. Como solucionar?
    Obrigada

  17. […] Leia este artigo em português: http://guiadoexcel.com.br/criar-listas-de-dados-com-base-em-outra-lista […]

  18. Roberto disse:

    Como apagar as linhas em branco da sublista?

  19. […] Leia este artigo em português: http://guiadoexcel.com.br/criar-listas-de-dados-com-base-em-outra-lista […]

  20. Erik disse:

    Amigo, esta parte está errada
    Selecione a célula D10, clique em Dados->Validação de Dados e configure conforme a figura:
    http://i0.wp.com/guiadoexcel.com.br/wp-content/uploads/sites/866/2010/07/vendedor_sucursal.png

    tentei escrever na fonte: “=Vendedor_Sucursal” e deu erro. Baixei sua planilha e o correto é “=Listadevendedores”

    Conserte aí, pois está causando confusão na hora de fazer

    Obrigado

  21. Erik disse:

    Encontrei mais um erro no seu tutorial. Na primeira parte:

    Tabela de Dados

    Nesta lista de dados temos as sucursais e os vendedores que compõem cada uma.
    Selecione as células B1:E1 referente as sucursais e nomeie o intervalo como Sucursais.
    Selecione o intervalo A1:E6 e nomeie o intervalo como Vendedores.

    O correto é “Selecione o intervalo “B2:E6” e nomeie o intervalo como Vendedores.

    Não tem como fazer a fórmula seguindo um tutorial errado. A planilha está correta, mas o tutorial não.

  22. Rodrigo disse:

    Marcos, bom noite.

    Primeiramente excelente post.

    Minha dúvida é…

    Partindo do ponto que já escolhi anteriormente uma sucursal e um vendedor. Os dados estão visíveis em C10 e D10.

    Teria alguma forma de “zerar” D10 ao fazermos uma nova seleção de sucursal em C10, isto é, os dados visíveis em D10 serem apagados/limpos e esta ficar “aguardando” uma nova seleção???

    Faço este questionamento, pois, estamos lidando com uma lista dinâmica e se não há este “reload” podem ocorrer erros na correlação entre estes campos – caso o usuário “passe batido” por esta célula que já está preenchida.

    Desde já agradeço pela atenção.

  23. LETICIA disse:

    Marcos, bom noite.

    Primeiramente excelente post.

    Minha dúvida é…

    Partindo do ponto que já escolhi anteriormente uma sucursal e um vendedor. Os dados estão visíveis em C10 e D10.

    Teria alguma forma de “zerar” D10 ao fazermos uma nova seleção de sucursal em C10, isto é, os dados visíveis em D10 serem apagados/limpos e esta ficar “aguardando” uma nova seleção???

    Faço este questionamento, pois, estamos lidando com uma lista dinâmica e se não há este “reload” podem ocorrer erros na correlação entre estes campos – caso o usuário “passe batido” por esta célula que já está preenchida.

    Desde já agradeço pela atenção.

  24. Crystopher disse:

    Boa tarde.

    Tentei fazer o teste no meu computador mas não funcionou.
    Quando faço a nomenclatura da ListaVendedores e tento usá-la na Lista Suspensa, diz que tem um erro na fórmula.

  25. Rita disse:

    Boa tarde não consigo abrir o arquivo, fiz extração do arquivo, mas quando vou digitar usuário e senha acusa erro.

  26. Arthur disse:

    Olá Marcos !
    Estou tendo um erro nos dados correspondentes as “SUCURSAIS” , pois quando escrevo os nome que são compostos , ou seja contém mais de uma palavra separada por espaços eu recebo um erro do Excel dizendo que o nome não é valido e não consigo criar a lista associada. Funcionou perfeito para as informações que contém apenas uma palavra, mas nesses casos não deu.
    Tem alguma forma de resolver esse problema ?
    Lembrando que eu preciso que os dados sejam escritos com espaço na planilha, não podendo colocar uma _ ou algum outro caractere para separar os nomes.

  27. Felipe disse:

    Boa tarde Marcos,

    Primeiro, muito obrigado pela dica. No meu caso, a fórmula referenciada como “listavendedores” retorna o valor da próxima categoria/sucursal. Ex: Seleciono Florianópolis e é exibido os nomes Henrique, Luiz e Mário. Pode me ajudar?

  28. Dorival disse:

    Boa tarde!!!

    Por favor preciso fazer uma lista suspensa com duas colunas, onde eu possa inserir os dados da segunda coluna.

    Obrigado

  29. Everton Ferreira disse:

    Rapaz. Não sabe como me salvou com esse tutorial…

    Muito Obrigado!

  30. Izabel Nascimento disse:

    Boa tarde,

    Como eu amarro uma linha com opções a outra linha relacionadas?

    Ex.:

    Tenho a Linha 01

    01. Produtivo
    02. Não produtivo

    Quero amarrar a linha 02 conforme a seleção que fiz na linha 01.

    01. Produtivo tenho as seguintes opções:

    01.01. Corporativo
    01.02. Negócios
    01.03. Comercial

    02. Não Produtivo
    02.01. RH
    02.02. Fiscal
    02.03. Ativo

Deixe uma resposta

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

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.