Desde que os menus suspensos de Validação de Dados foram adicionados ao Excel em 1997, as pessoas têm tentado descobrir uma maneira de alterar o segundo menu suspenso com base na seleção do primeiro menu suspenso.
Por exemplo, se você escolher Fruta em A2, a lista suspensa em A4 oferecerá Maçã, Banana, Cereja. Mas se você escolher Ervas de A2, a lista em A4 ofereceria anis, manjericão, canela. Ao longo dos anos, surgiram muitas soluções. Já cobri isso pelo menos duas vezes no Podcast:
- O método clássico usou muitos intervalos nomeados, conforme mostrado no episódio 383.
- Outro método usou fórmulas OFFSET no episódio 1606.
Com o lançamento das novas fórmulas de Dynamic Array em Public Preview, a nova função FILTER nos dará outra maneira de fazer a Validação Dependente.
Digamos que este seja o seu banco de dados de produtos:
Use uma fórmula de =SORT(UNIQUE(B4:B23))em D4 para obter uma lista única das classificações. Este é um novo tipo de fórmula. Uma fórmula em D4 retorna muitas respostas que se espalharão por muitas células. Para se referir ao Spiller Range, você usaria em =D4#vez de =D4.
Selecione uma célula para conter o menu Validação de dados. Escolha Alt + DL para abrir a validação de dados. Altere Permitir para "Lista". Especifique =D4#como a fonte da lista. Observe que o Hashtag (#) é o Spiller - significa que você está se referindo a todo o Spiller Range.
O plano é que alguém escolha uma classificação no primeiro menu suspenso. Então, uma fórmula de =FILTER(A4:A23,B4:B23=H3,"Choose Class First")em E4 retornará todos os produtos dessa categoria. Observe que o uso de "Choose Class First" como o terceiro argumento opcional. Isso evitará um erro de #VALUE! erro de aparecer.
Pode haver um número diferente de itens na lista, dependendo da categoria selecionada. Configurar a Validação de dados apontando para =E4#expandirá ou diminuirá com o comprimento da lista.
Assistir vídeo
Transcrição de vídeo
Learn Excel From, Podcast Episódio 2248: Dependent Validation Using Arrays.
Bem, ei. Isso foi abordado duas vezes antes no podcast, como fazer a validação dependente e o que é a validação dependente, você pode escolher, primeiro, uma categoria e, em resposta, a segunda lista suspensa mudará apenas para o itens dessa categoria e, antes, isso era complicado, e com os novos arrays dinâmicos que foram anunciados em setembro de 2018 … e eles estão sendo lançados, então você precisa ter o Office 365. Agora mesmo, 10 de outubro, ouvi que eles estão em cerca de 50% dos usuários internos do Office, então eles estão sendo implementados muito lentamente. Provavelmente, vai demorar até o primeiro semestre de 2019 antes de você conseguir isso, mas nos permitirá fazer a validação dependente de uma maneira muito mais fácil.
Portanto, tenho duas fórmulas aqui. A primeira fórmula é a UNIQUE de todas as classificações e eu a enviei para o comando SORT. Então, isso me dá 1 fórmula retornando 5 resultados e que vive em D4. Então, aqui onde quero escolher a validação dos dados, vou (DL - 1:09)… a FONTE vai ser = D4 #. Isso # - estamos chamando de spiller - certifique-se de que retorna todos os resultados de D4. Então, se eu adicionar uma nova categoria aqui e isso crescer, D4 # vai pegar aquela quantia extra, certo? (= CLASSIFICAR (ÚNICO (B4: B23)))
Então, essa primeira validação é bastante simples, mas agora que sabemos que escolhemos CITRUS - isso vai ser mais difícil - quero filtrar a lista na coluna A onde o item na coluna B é igual ao item escolhido , tudo bem? Então, primeiro temos que deixá-los escolher algo e então, uma vez que eu sei que é CITRUS, então me dê LIMA, LARANJA e TANGERINE, eles escolheriam outra coisa. BAGA. Veja isso. As revistas científicas dizem que banana é uma baga. Eu não concordo com isso. Não parece uma baga para mim, mas não me culpe. Estou apenas, sabe, usando a Internet. BANANA, IDOSO e FRAMBOESA.
Agora, você sabe, o problema com isso é que alguém vai inicialmente vir aqui sem ter escolhido nada, e, nesse caso, temos ESCOLHER A CLASSE PRIMEIRO que é aquele terceiro argumento que diz se nada for encontrado, certo? Então, você sabe, dessa forma, se começarmos neste cenário, a escolha vai ser ESCOLHER A CLASSE PRIMEIRO. A ideia é que eles escolham CLASSE, VEGETAL, isso atualiza, e então esses itens vêm dessa lista. A VALIDAÇÃO DE DADOS aqui, claro, bom, isso é outro spiller, = E4 # para fazer funcionar, certo? Então, isso é legal. (= FILTRO (A4: A23, B4: B23 = H3, ”Escolha a classe primeiro”))
Confira meu livro Excel Dynamic Arrays. Isso é … vai ser de graça até o final de 2018. Confira o link aqui na descrição do YouTube, como você pode fazer o download, para este exemplo mais 29 exemplos de como usar esses itens.
Bem, conclua por hoje. Os arrays dinâmicos nos fornecem outra maneira de fazer a validação dependente. Se você não está no Office 365 e ainda não os tem, sinta-se à vontade para voltar, suponho, ao vídeo 1606, que mostra a maneira antiga de fazer isso.
Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
Baixar arquivo Excel
Para fazer o download do arquivo excel: dependente-validação-usando-arrays.xlsx
Para aprender mais sobre Dynamic Arrays, verifique Excel Dynamic Arrays Straight To The Point.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Nunca exclua um arquivo do Excel sem primeiro fazer o backup."
Mike Alexander
