Ir para o conteúdo principal

Como criar listas suspensas dependentes com valores exclusivos apenas no Excel?

Pode ser fácil para nós criar uma lista suspensa dependente no Excel, mas, se os dados de origem contiverem valores duplicados, todos os valores duplicados também serão aplicados, o que pode ser irritante. Para excluir os valores duplicados e manter apenas os únicos ao criar uma lista suspensa dependente, este tutorial apresentará alguns truques para você.

Crie listas suspensas dependentes com valores exclusivos apenas por recursos do Excel

Crie listas suspensas dependentes com valores exclusivos apenas por um recurso incrível


Crie listas suspensas dependentes com valores exclusivos apenas por recursos do Excel

É um pouco problemático criar listas suspensas dependentes com valores exclusivos apenas no Excel, você deve aplicar a operação abaixo passo a passo:

Etapa 1: criar nomes de intervalo para o primeiro e o segundo dados da lista suspensa

1. Clique Fórmulas > Definir o Nome, veja a captura de tela:

2. No Novo nome caixa de diálogo, insira um nome de intervalo Categoria no Nome caixa de texto (você pode digitar qualquer outro nome que precisar) e, em seguida, digite esta fórmula =OFFSET($A$2,0,0,CONT.A($A$2:$A$100)) no Refere-se a caixa de texto e, finalmente, clique no OK botão:

Note: A2: A100 é a lista de dados na qual você criará a primeira lista suspensa, se você tiver dados grandes, basta alterar a referência da célula necessária.

3. Continue criando um nome de intervalo para o segundo menu suspenso, clique em Fórmulas > Definir o Nome para abrir a caixa de diálogo Novo nome, insira um nome de intervalo Alimentação no Nome caixa de texto (você pode digitar qualquer outro nome que precisar) e, em seguida, digite esta fórmula =OFFSET($B$2,0,0,CONT.A($B$2:$B$100)) no Refere-se a caixa de texto e, finalmente, clique no OK botão:

Note: B2: B100 é a lista de dados na qual você criará a lista suspensa dependente, se você tiver dados grandes, basta alterar a referência da célula necessária.

Etapa 2: Extraia os valores exclusivos e crie a primeira lista suspensa

4. Agora, você deve extrair os valores exclusivos para os primeiros dados da lista suspensa inserindo a seguinte fórmula em uma célula, pressionando Ctrl + Shift + Enter teclas juntas e, em seguida, arrastando a alça de preenchimento para as células até que os valores de erro sejam exibidos, veja a captura de tela:

=INDEX(Category,MATCH(0,COUNTIF($D$1:D1,Category),0))
Note: Na fórmula acima, Categoria é o nome do intervalo que você criou na etapa 2 e D1 é a célula acima da sua célula de fórmula, altere-a de acordo com sua necessidade.

5. Em seguida, crie um nome de intervalo para esses novos valores exclusivos, clique em Fórmulas > Definir o Nome para abrir o Novo nome caixa de diálogo, insira um nome de intervalo Categoria única no Nome caixa de texto (você pode digitar qualquer outro nome que precisar) e, em seguida, digite esta fórmula =OFFSET($D$2, 0, 0, COUNT(SE($D$2:$D$100="", "", 1)), 1) no Refere-se a caixa de texto, por fim, clique OK botão para fechar o diálogo.

Note: D2: D100 é a lista de valores exclusivos que você acabou de extrair, se você tiver dados grandes, basta alterar a referência da célula que você precisa.

6. Nesta etapa, você pode inserir a primeira lista suspensa. Clique em uma célula onde deseja inserir a lista suspensa e, em seguida, clique em Data > Validação de dados > Validação de dados, E no Validação de dados caixa de diálogo, selecione Lista do Permitir menu suspenso e insira esta fórmula: =Categoria única no fonte caixa de texto, veja a captura de tela:

7. Então clique OK botão, a primeira lista suspensa sem valores duplicados é criada conforme a captura de tela abaixo:

Etapa 3: Extraia os valores exclusivos e crie a lista suspensa dependente

8. Extraia os valores exclusivos para a lista suspensa secundária, copie e cole a fórmula abaixo em uma célula e pressione Ctrl + Shift + Enter simultaneamente, arraste a alça de preenchimento para as células até que os valores de erro sejam exibidos, veja a captura de tela:

=INDEX(Food,MATCH(0,COUNTIF($E$1:E1,Food)+(Category<>$H$2),0))
Note: Na fórmula acima, Alimentação é o nome do intervalo que você criou para os dados da lista suspensa dependente, Categoria é o nome do intervalo que você criou para os primeiros dados da lista suspensa e E1 é a célula acima da sua célula de fórmula, H2 é a célula que você inseriu na primeira lista suspensa, altere-as de acordo com sua necessidade.

9. Em seguida, vá criando um nome de intervalo para esses valores exclusivos secundários, clique em Fórmulas > Definir o Nome para abrir o Novo nome caixa de diálogo, insira um nome de intervalo Comida única no Nome caixa de texto (você pode digitar qualquer outro nome que precisar) e, em seguida, digite esta fórmula =OFFSET($E$2, 0, 0, COUNT(SE($E$2:$E$100="", "", 1)), 1) no Refere-se a caixa de texto. Por fim, clique OK botão para fechar o diálogo.

Note: E2: E100 é a lista secundária de valores exclusivos que você acabou de extrair, se você tiver dados grandes, basta alterar a referência da célula necessária.

10. Depois de criar o nome do intervalo para os valores exclusivos secundários, agora você pode inserir a lista suspensa dependente. Por favor clique Data > Validação de dados > Validação de dados, E no Validação de dados caixa de diálogo, selecione Lista do Permitir menu suspenso e insira esta fórmula: =Alimento único no Source caixa de texto, veja a captura de tela:

11. Clique OK botão, as listas suspensas dependentes com apenas valores exclusivos são criadas com sucesso conforme a demonstração abaixo:


Crie listas suspensas dependentes com valores exclusivos apenas por um recurso incrível

O método acima pode ser dor de cabeça para a maioria de nós, aqui, apresentarei uma ferramenta útil - Kutools for Excel, Com o seu Lista suspensa dinâmica recurso, você pode resolver esse problema com facilidade.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Clique Kutools > Lista suspensa > Lista suspensa dinâmica, veja a captura de tela:

2. No Lista suspensa de dependentes caixa de diálogo, faça as seguintes operações:

  • Selecionar Modo B: Lista suspensa dependente de 2 a 5 níveis do Moda seção;
  • Selecione os dados nos quais você deseja criar a lista suspensa dependente com base no Intervalo de dados caixa;
  • Em seguida, selecione o intervalo de saída onde você deseja colocar a lista suspensa dependente da Faixa de Saída caixa.

3. Em seguida, clique no Ok botão, as listas suspensas dependentes são inseridas na seleção enquanto os valores duplicados também são excluídos. Veja abaixo demonstração:

Clique para baixar o Kutools para Excel agora!


Mais artigos relativos:

  • Crie uma lista suspensa com imagens no Excel
  • No Excel, podemos criar de forma rápida e fácil uma lista suspensa com valores de células, mas, você já tentou criar uma lista suspensa com imagens, ou seja, ao clicar em um valor da lista suspensa, seu relativo a imagem será exibida imediatamente conforme a demonstração abaixo. Neste artigo, falarei sobre como inserir uma lista suspensa com imagens no Excel.
  • Criar lista suspensa com várias caixas de seleção no Excel
  • Muitos usuários do Excel tendem a criar uma lista suspensa com várias caixas de seleção para selecionar vários itens da lista por vez. Na verdade, você não pode criar uma lista com várias caixas de seleção com a validação de dados. Neste tutorial, vamos mostrar a você dois métodos para criar uma lista suspensa com várias caixas de seleção no Excel.
  • Criar lista suspensa de dependentes de vários níveis no Excel
  • No Excel, você pode criar uma lista suspensa dependente de forma rápida e fácil, mas você já tentou criar uma lista suspensa dependente de vários níveis como mostrado a seguir? Neste artigo, falarei sobre como criar uma lista suspensa dependente de vários níveis no Excel.
  • Criar lista suspensa, mas mostrar valores diferentes no Excel
  • Na planilha do Excel, podemos criar rapidamente uma lista suspensa com o recurso Validação de Dados, mas você já tentou mostrar um valor diferente ao clicar na lista suspensa? Por exemplo, eu tenho os seguintes dados de duas colunas na Coluna A e Coluna B, agora, eu preciso criar uma lista suspensa com os valores na coluna Nome, mas, quando eu seleciono o nome da lista suspensa criada, o correspondente valor na coluna Número é exibido conforme a captura de tela a seguir. Este artigo apresentará os detalhes para resolver essa tarefa.

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations