Note: The other languages of the website are Google-translated. Back to English

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:

Nota: 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:

Nota: 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))
Nota: 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.

Nota: 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 Dados > Validação de dados > Validação de dados, E no Validação de dados caixa de diálogo, selecione Lista de 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))
Nota: 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.

Nota: 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 Dados > Validação de dados > Validação de dados, E no Validação de dados caixa de diálogo, selecione Lista de 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 de 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.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office 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!
parte inferior da aba do escritório
Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos