Como posso implementar a lista de validação de dados dependentes dinâmicos no Excel

Question

Fontes na web mostram como implementar listas de validação de dados dependentes, mas tudo que encontrei são estáticos e não são atualizados automaticamente.

Acredito que quem for capaz de responder a essa pergunta já sabe como funcionam as listas de validação de dados em nível profissional, mas para fins de alunos como eu, darei uma breve descrição (sugiro leitura adicional nos vídeos do Google e do YouTube).

As listas de validação de dados permitem criar uma estrutura de dados definida em sua pasta de trabalho. Eles são mais destinados à interação do usuário. Se você quisesse que um usuário da pasta de trabalho inserisse apenas um conjunto de dados, por exemplo, Donuts, Bolos, Muffins e Crumpets, você poderia criar uma lista usando um intervalo nomeado, digamos "Lanches" ou poderia criar uma tabela e nomear sua mesa como "Lanches". Onde o nome fornecido é uma palavra (ou seja, não existem espaços no nome fornecido)

A desvantagem de usar listas é que se tivéssemos que adicionar outro lanche à lista chamado Croissants, precisaríamos redefinir as listas originalmente criadas. As tabelas contornam essa ineficiência no sentido de que você pode facilmente adicionar uma nova linha a uma tabela anexando-a da última linha ou escrevendo a linha seguinte após a linha atual da tabela e pressionando Enter.

Agora, para validação de dados, recomendo visitar o link abaixo para uma exposição detalhada, pois a explicação torna esta questão muito longa.http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

Uma lista de validação dependente é, portanto, uma lista baseada no valor selecionado pelo usuário em uma lista de validação anterior, por exemplo, os tipos de Donut incluem anel gelado, cobertura de chocolate, donuts com geléia e creme. Os tipos de muffin incluem banana, mirtilo e chocolate. Os tipos de bolo incluem veludo vermelho, cenoura e coco. Finalmente, os tipos de Crumpets incluem Inglês, Escocês e Pikelets.

Portanto, se uma célula tiver um menu suspenso na lista de categorias principais, como você cria uma lista de validação dependente dinâmica com base nas subcategorias.

Por lista de validação dependente dinâmica, quero dizer que todas as listas de colunas são tabelas conforme mostrado abaixo.

Agora a questão é como faço para implementar uma célula adjacente, por exemplo, se a célula C13 tem uma lista de validação para a tabela de lanches, como faço para tornar a célula D13 uma lista de validação baseada na entrada C13 selecionada pelo usuário (ou seja, se C13 for selecionado como Donuts, então a possível seleção da lista de validação do D13 serão os dados da coluna da tabela Donuts).

Por favor, é importante ressaltar que a lista de validação original em C13 teria sido criada utilizando a função INDIRETO por se tratar de uma tabela e não de um intervalo nomeado.

Answer 1

Maneira fácil em 4 etapas simples

Passo 1 Crie suas tabelas:

Passo 2 Nomeie suas tabelas: selecione as tabelas e renomeie-as, para que correspondam ao valor da tabela primáriaTabela 1, por exemplo. A Tabela2 com o cabeçalho "Bolos" deve ser nomeadaBolo, já que esse é o valor na tabela primáriaTabela 1.
Mas como? Clique no canto da tabela para selecioná-la, insira um novo rótulo onde dizMesa 2ou similar e pressione Enter. Não se preocupe se ainda disser "Tabela2"
etc.Observação!Isso não é necessário para a tabela primária, por exemplo. Tabela 1.)

etapa 3 Selecione onde deseja sua primeira lista, vá até a aba de dados e entre em validação de dados. Selecione "lista" e digite: =INDIRECT("Table1")na janela de origem:

Passo 4 Selecione uma célula para a segunda lista, que dependerá da primeira lista. Retorne à validação de dados, escolha “lista” e digite =INDIRECT(K2)onde “K2” é a localização da primeira lista.

FEITO

Para uma terceira lista, dependente da segunda lista, repita o passo 4 e consulte a segunda lista. Boa sorte!

Answer 2

Você apenas terá que acreditar na minha palavra de que isso funciona, mas eu escrevi uma macro (muito confusa, em código espaguete) exatamente para esse propósito. Infelizmente, o código não é nada elegante, mas ei, funciona! Como se costuma dizer... “Faça funcionar, faça certo, faça rápido”.

Etapa 1: o código VBA

Você precisará de três coisas:

1. Módulo Arrays de Chip Pearson--- o código VBA real está no final.

2. Módulo de classificação de Chip Pearson- código VBA real também no final.

3. Minha macro terrivelmente organizada

Para colá-los em módulos VBA em sua pasta de trabalho, pressione Alt+ F11, encontre seu projeto no Project Explorer, clique com o botão direito na pasta Módulos e selecione "Inserir Módulo". Cole o código de cada um dos 3 links acima em módulos separados.

Enquanto estiver no Editor VBA, abra a caixa de diálogo Referências ( Alt+ e Tdepois Enter) e marque 'Microsoft Scripting Runtime'.

Passo 2: Estruturando seus dados

Com isso resolvido, o que você deseja agora é uma planilha com seus dados (chamada, digamos, de 'Dados') como se fosse um banco de dados bem estruturado. Veja como eu faria isso:

Observe que criei mais duas planilhas. Um chamado Validation para onde ocorre a lógica de validação de detalhamento e outro chamado Main para o conteúdo real que é importante para o usuário final.

Etapa 3: planilha principal

Vamos para a planilha de dados e configurar a seguinte estrutura:

Selecione a célula C2e dê um nome a ela Snack.Selected. Para nomear uma célula, selecione-a e pressione Alt M M De digite o nome na caixa de diálogo que aparece, exatamente como mostra a imagem. Por enquanto, insira um valor como 'Bolo' apenas para que a próxima etapa faça mais sentido.

Vá em frente e nomeie a célula como C3'Type.Selected', mas deixe-a vazia por enquanto.

Etapa 4: buscar valores de nossa tabela na planilha de dados

Acesse sua planilha de Validação e configure a seguinte estrutura:

Como você pode ver na captura de tela, você deve selecionar as células B3:B20e digitar a seguinte fórmula, que é onde minha macro realmente entra em ação:

=MultiLookup("Data","Snack",TRUE,TRUE)

Em vez de pressionar Enter, pressione Ctrl+Shift+Enter porque este é umFórmula de matriz

Se funcionar como anunciado, você verá uma lista de seus lanches seguida de alguns erros #N/A. Os erros são esperados - significam apenas que você come menos lanches do que esperava com base no tamanho B3:B20.

Explicando os parâmetros na fórmula:

1. Planilha onde os dados estão localizados (“Dados”)
2. Campo que você deseja dessa planilha ("Lanche")
3. Remover duplicatas (ou seja, agrupadas)? (VERDADEIRO)
4. Classificado em ordem alfabética? (VERDADEIRO)

Ok, parece muito trabalho por pouco valor, mas aí vem o grande final. Nas células C3:C20, você colocará a seguinte fórmula:

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

Deverá mostrar os tipos de Bolo disponíveis, qual é o valor que você digitou na planilha Principal no passo anterior, lembra?

Isso funciona adicionando dois argumentos à fórmula:

5. Campo para filtrar ("Lanche")
6. Selecione apenas valores iguais a (Snack.Selected)

Assim, quando você altera seus dados em Principal, a coluna Tipos é atualizada automaticamente!

Etapa 5: Concluindo a validação

Para finalizar, vamos finalizar nossa validação de dados criando os nomes Snack.Choicese Type.Choices. Nomeie as células B1e C1em Validação respectivamente. Você também pode simplesmente digitar nesta pequena caixa para nomear uma célula:

Na célula B1, você precisa colocar a seguinte fórmula:

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

O que isso faz é criar uma referência de texto para Validation!$B$3:$B$6 que é onde suas opções de lanches estão localizadas. Copie essa fórmula para a direita e pronto!

Volte para sua planilha Principal e vamos nos referir a essas células nas regras de validação para Snack e Type.

Selecione célula C2e kbd>Alt A V Vpara criar validação de dados. Escolha 'Lista' e defina a fonte como =INDIRECT(Snack.Choices). Observe que não há citações por aíSnack.Choices

Faça o mesmo na célula C3, mas defina a fonte como =INDIRECT(Type.Choices).

Usamos INDIRECT()porque os valores de Snack.Choicese Type.Choicessão referências indiretas (ou seja, texto) a intervalos em nossa pasta de trabalho.

Agora experimente as opções de validação para ver tudo em ação.

Deixe-me saber se você tiver alguma dúvida!