Até agora, nesta série de validação de dados, aprendemos a criar uma lista suspensa normal e uma lista suspensa dinâmica usando diferentes técnicas com validação de dados no Excel.
E, hoje, neste capítulo, mostraremos como criar uma lista suspensa de dependentes no Microsoft Excel, usando diferentes métodos.
A lista suspensa dependente também é conhecida como validação de dados em cascata e limita as opções em uma lista suspensa, dependendo do valor selecionado na outra célula que contém a validação de dados. Em outras palavras, depende do valor selecionado na primeira lista suspensa que determina os valores a serem exibidos na segunda lista suspensa.
Este é um cenário muito comum de trabalhar com grandes dados ou alguns relatórios dinâmicos, onde você deseja ter 2WL a célula exibe uma lista que depende do item da lista escolhido na primeira lista suspensa.
Como sabemos que no Excel, existem muitas maneiras de fazer uma tarefa específica e, da mesma forma, existem muitas maneiras de criar validação de dados dependentes no Excel. E, hoje, vamos demonstrar 5 técnicas diferentes para criar uma lista de validação de dados dependentes.
Os dados brutos podem estar em qualquer ordem ou formato e sempre que você não pode alterar os dados ou formato para obter o que está procurando.
Então, pegamos um conjunto de dados, mas em 3 formatos diferentes para obter a lista suspensa dependente. E, como você pode ver, nossos dados estão no lado esquerdo que vai da coluna A à coluna E, e teremos nossa saída esperada no lado direito que está na coluna J e K. A coluna J terá a validação primária lista enquanto a coluna K será dependente e exibirá os valores dependendo do valor selecionado na coluna J.
1st Exemplo:-
2WL Exemplo:-
3rd Exemplo:-
1st Exemplo:-
Temos uma lista de produtos para cada código de produto da coluna A8 a E13. E, queremos selecionar o código do produto em J10, dependendo do código do produto selecionado, um nome de produto na célula K10.
Primeiro Método:-
O primeiro método é muito simples e curto e requer apenas 3 etapas para obter a lista suspensa correspondente. No entanto, ele só funciona com sucesso até que você não faça nenhuma alteração em seu alcance. Depois de modificar seus dados, você terá que modificar o intervalo nomeado primeiro para obter a validação de dados em cascata atualizada.
Siga as etapas abaixo:-
- Selecione a tabela inteira de A8 a E13
- Em seguida, vá para a guia “Fórmulas” e, na categoria “Nomes definidos”, clique em “Criar a partir da seleção”
- Você também pode usar o atalho de teclado CTRL + SHIFT + F3
- A caixa de diálogo Criar nomes a partir das seleções aparecerá
- Ele pede para confirmar quais linhas e colunas serão usadas para criar os nomes de outras linhas e colunas. Confirmamos usar a "linha superior" para criar os nomes e desmarcar os 2WL opção e então clicamos em OK
Observação: - Espaços e outros caracteres especiais, exceto sublinhado e ponto, não são permitidos como nomes. Por padrão, ele será convertido em sublinhado. Portanto, use o caractere sublinhado e ponto final para separar as palavras. Além disso, a primeira letra não pode ser um número; deve ser uma letra, um sublinhado ou uma barra invertida.
- Agora, para confirmar que cada faixa possui um nome, vamos ao “Gerenciador de Nomes” (pressione CTRL + F3)
- Lá podemos ver todos os 5 intervalos nomeados disponíveis
- E também podemos ver que cada nome de intervalo tem sublinhado em vez de espaço em branco no meio da string
Agora, vamos criar uma lista suspensa:-
- Selecione a célula J10 e pressione ALT ++ D + L para abrir a caixa de diálogo de Validação de Dados
- Selecione Lista> e insira o intervalo A8: E8 na guia Fonte
- Clique em OK
- Agora vamos criar uma lista dependente na célula K10
- Abra a caixa de diálogo de Validação de Dados pressionando a tecla ALT + D + L
- Selecione Lista, na fonte insira esta função: - = INDIRETO (SUBSTITUIR ($ J $ 10, "", "_"))
Na validação de dados, para criar a lista dependente, usamos a função INDIRETO para retornar o valor com base na lista de validação de dados primária. E, para substituir o sublinhado por espaço, usaremos a função SUBSTITUTE dentro da função INDIRECT.
- Clique em OK
Quando selecionamos qualquer código de produto na célula J10, a lista de produtos do código de produto selecionado aparecerá na célula K10. Por exemplo: - Selecionamos ETV 501, agora você pode ver a lista de produtos dependentes aparecendo na célula K10
Observação: - Sempre que você vai adicionar o nome do produto e o código do produto que não aparecerá na lista.
Por exemplo: - Adicionamos o Produto 26 sob o código de produto ETV 505, mas quando selecionamos o produto ETV 505, o produto adicionado não aparece na lista suspensa.
Então, é assim que você pode criar uma lista suspensa de dependentes usando uma técnica simples em apenas 3 etapas simples.
2WL Exemplo:-
Neste exemplo, veremos como obter uma lista suspensa dependente quando você tiver seus dados conforme mostrado nesta tabela vertical.
Usaremos dois métodos diferentes para criar uma lista suspensa dependente. Ambas são técnicas quase semelhantes. No entanto, um está sem o intervalo nomeado e o outro terá o intervalo nomeado.
1st Método:-
Para fazer o mesmo, usaremos as funções OFFSET, MATCH e COUNTIF juntas.
Como sabemos que a função OFFSET é usada para criar a faixa dinâmica, portanto, para criar a lista de “validação de dados dinâmicos”, usamos a função OFFSET para retornar a faixa dinâmica.
MATCH é usado para retornar a posição relativa de um item em uma lista no Excel. E, aqui, ele nos ajudará a corresponder à categoria selecionada na lista suspensa primária em nosso intervalo na planilha e retornará um número.
E CONT.SE é usado para obter a contagem de células que correspondem aos critérios. E, aqui, usaremos isso para contar o número de linhas a serem exibidas usando a função CONT.SE.
Siga as etapas abaixo:-
- Selecione a célula J21, na qual criaremos nossa lista primária de validação de dados
- Pressione a tecla ALT + D + L para abrir a caixa de diálogo de Validação de Dados
- Selecione a lista da categoria de permissão
- Clique na guia Fonte e selecione o intervalo de B20: B24
- E clique em OK
- Vá para a célula K21 e abra a caixa de diálogo de validação de dados mais uma vez
- Em seguida, selecionamos Lista e, na fonte, inserimos a função abaixo:
- = OFFSET ($ E $ 19, CORRESPONDÊNCIA ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Clique em OK
- Na célula K21, podemos ver todos os valores correspondentes do código do produto selecionado: -
Portanto, é assim que você pode obter a lista dependente tomando referências de células na função.
2WL Método:-
No próximo método, usaremos intervalo nomeado na mesma função para obter a validação de dados em cascata. Primeiro, devemos criar a lista dinâmica para o código do produto. No caso de qualquer novo produto adicionado aos dados, o menu suspenso deve ser atualizado para exibir o mesmo.
Para fazer o mesmo, siga as etapas abaixo:-
- Selecione B19 e pressione CTRL + F3 para abrir a janela "Gerenciador de Nomes"
- Agora, clicamos em “Novo” e a caixa de diálogo “Definir nome” aparece
- Podemos ver que o nome já aparece na caixa de nome - isso porque selecionamos B9 antes de abrir a janela “Gerenciador de nomes”. E, como B19 contém texto, se quisermos, podemos alterá-lo para algum outro nome.
- Insira a fórmula de menção abaixo: -
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Clique em OK
Como criamos uma lista dinâmica para produtos exclusivos, agora criaremos uma faixa dinâmica para a faixa de código de produto que está na coluna D.
Siga as mesmas etapas que seguimos para o produto exclusivo: -
- Selecione a célula D19, abra a caixa de diálogo Definir Nome
- Você vai encontrar o nome já está lá
- Em refere-se, insira a fórmula abaixo: -
= OFFSET ('Lista suspensa de dependentes'! $ D $ 20,0,0, COUNTA ('Lista suspensa de dependentes'! $ D $ 20: $ D $ 35))
- Clique em OK
- Agora, as duas faixas dinâmicas estão prontas. Então, vamos para J22 e pressione “ALT + D + L” e selecione “List”
- Na origem, teremos o intervalo nomeado que definimos para "Código de produto exclusivo", então, pressionamos F3 para ver todos os intervalos nomeados disponíveis
- Podemos ver o intervalo denominado “Código de produto único”, então clicamos nele e, em seguida, clicamos em OK e pressionamos enter
- No momento em que pressionamos Enter, temos uma seta suspensa na célula J22, que contém a lista de códigos exclusivos de produto
- Selecione a célula K22 e abra a caixa de diálogo “Validação de Dados”
- Usaremos a mesma função que usamos no último método, mas com intervalo nomeado
- Selecione a lista e, em seguida, na fonte, insira a fórmula abaixo: -
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Clique em OK
- Agora, temos a lista suspensa principal e também a lista secundária de produtos
- Selecione o produto “ETV-101” em J22, e no K22, podemos ver apenas os nomes que se enquadram neste produto “ETV-101”. E, quando mudamos qualquer produto (“ETV-103) em J22, K22 exibe os valores correspondentes para aquele código
Agora veremos o que acontece quando adicionamos qualquer novo código de produto à lista? Essas listas suspensas serão atualizadas?
Vamos adicionar um novo produto à lista; Siga as etapas abaixo: -
- Adicione o código do produto na lista de Unique_Prod_Code
- Adicione também Product_Code e Product_Name nos dados: -
- Agora verifique a lista suspensa - o código e o nome do produto adicionado está aparecendo
3rd Exemplo:-
Temos os cabeçalhos dinâmicos diretamente da tabela e adicionaremos novos produtos à linha. A tabela está no mesmo formato que usamos para 1st método.
4º Método:-
Siga as etapas abaixo:-
- Selecione o título A40: E40
- Crie o intervalo dinâmico para os títulos primeiro, abra a caixa de diálogo "Definir nome"
- Escreva “Título” no lugar do nome e, em seguida, em “refere-se a”, insira a fórmula mencionada abaixo: -
- Insira a função abaixo: -
- = OFFSET ('Lista suspensa dependente'! $ A $ 40 ,,,, COUNTA ('Lista suspensa dependente'! $ 40: $ 40))
- Clique em OK
- A faixa dinâmica de “direção” está pronta agora
E, agora vamos criar o intervalo nomeado para cada título, siga as etapas abaixo: -
- Selecione a tabela de A40 a E50
- Atalho de teclado CTRL + SHIFT + F3
- Nós desmarcamos o 2WL opção
- E, antes de clicarmos em OK, certifique-se de que 1st a opção “linha superior” está selecionada
- Agora estamos prontos com os dois intervalos
Agora, vamos preparar a lista suspensa de pais
- Selecione a célula J42
- Abra a caixa de diálogo de validação de dados
- Em seguida, após selecionar “List”, pressionamos F3 na origem para obter o intervalo nomeado para títulos. Clicamos em “Título” e depois clicamos em OK e pressionamos enter. Agora temos a lista de pais em J42
- Para criar a lista de detalhes do item, selecione a célula K42
- Abra a caixa de diálogo de Validação de Dados pressionando a tecla ALT + D + L
- Selecione Lista e insira a função abaixo na guia Fonte: -
- = OFFSET (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))
- Clique em OK
Agora selecione algum item no J42, digamos que selecionamos “Item 01” e vejamos a lista suspensa K42. E, como os 3 métodos anteriores, temos uma lista dependente aqui também.
Quais as novidades? No primeiro exemplo, você não poderia adicionar nenhum produto à lista, mas aqui, você pode adicionar qualquer novo produto. Então, digamos que adicionemos um novo produto a este item. Vamos para A45 e digitamos “ETV-501 Prod 05” e depois voltamos para K42 e aqui está. Você pode ver, o novo produto foi adicionado.
- Agora adicione alguns produtos no novo item
Quando selecionamos “Item 06”, vamos para K42 e clicamos na lista suspensa. Surpreendentemente, nada acontece quando clicamos na seta suspensa. Isso porque criamos tudo dinâmico e esquecemos de criar uma faixa dinâmica para a tabela, e é por isso que os produtos não são exibidos na lista de filhos.
Para fazer isso, precisamos usar técnicas diferentes. Existem dois métodos de fazer isso. Você pode criar a tabela ou simplesmente usar apenas a função OFFSET. E, no próximo método, usaremos a função OFFSET e veremos o truque para expandir o intervalo da tabela também.
- Então, primeiro vamos para J43 e pressionamos “ALT + D + L”
- Selecionamos “List” e, em seguida, na fonte, pressionamos F3 e selecionamos “Heading” e clicamos em OK e, em seguida, pressionamos enter
- Agora, vamos para K43, e após selecionar “Lista”, vamos para “Fonte” e entramos na função mencionada abaixo
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))
- Clique em OK
Agora, voltamos e selecionamos “Item 06 na célula J43 e voltamos para K43 e clicamos na seta suspensa. Mas, desta vez, a lista mostra os produtos que adicionamos para o novo item. E selecionamos o primeiro produto “ETV-506 Prod 01”.
É assim que você pode criar a lista suspensa dependente usando métodos diferentes em qualquer tipo de formato de dados.
Vídeo: Como criar uma lista suspensa dependente (em cascata) no Excel usando 5 técnicas diferentes no Microsoft Excel
Clique no link do vídeo para obter uma referência rápida sobre como usá-lo. Inscreva-se em nosso novo canal e continue aprendendo conosco!
Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook.
Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail