Um dos recursos mais úteis da validação de dados é a capacidade de criar uma lista suspensa que permite aos usuários selecionar um valor de uma lista predefinida. As listas suspensas tornam mais fácil para os usuários inserirem apenas dados que atendam aos seus requisitos.
As listas suspensas são fáceis de criar e usar. Mas, quando você começar a usar menus suspensos em suas planilhas, inevitavelmente se deparará com um desafio: como você pode fazer os valores de uma lista suspensa dependerem dos valores de outra? Em outras palavras, como você pode tornar uma lista suspensa dinâmica?
como inserir um rodapé no excel
aqui estão alguns exemplos:
- uma lista de cidades que depende do país selecionado
- uma lista de sabores que depende do tipo de sorvete
- uma lista de modelos que depende do fabricante
- uma lista de alimentos que depende da categoria
Esse tipo de lista é chamado dropdowns dependentes , já que a lista depende de outro valor. Eles são criados com validação de dados, usando uma fórmula personalizada com base no Função INDIRETA e intervalos nomeados . Isso pode parecer complicado, mas na verdade é muito simples e um ótimo exemplo de como o INDIRETO pode ser usado.
Continue lendo para ver como criar listas suspensas dependentes no Excel.
Exemplo de lista suspensa dependente
No exemplo mostrado abaixo, a coluna B fornece um menu suspenso para a categoria de alimentos e a coluna C fornece opções na categoria escolhida. Se o usuário selecionar 'Frutas', verá uma lista de frutas, se selecionar 'Nozes', verá uma lista de nozes e, se selecionar 'Vegetais', verá uma lista de vegetais.
A validação de dados na coluna B usa esta fórmula personalizada:
=category E a validação de dados na coluna C usa esta fórmula personalizada:
= INDIRECT (B5)
Onde a planilha contém os seguintes intervalos nomeados:
categoria = E4: G4
vegetal = F5: F10
porca = G5: G9
fruta = E5: E11
Como isso funciona
A chave para esta técnica é intervalos nomeados + a função INDIRETA. INDIRECT aceita valores de texto e tenta avaliá-los como referências de células. Por exemplo, INDIRETO pegará o texto 'A1' e o transformará em uma referência real:
= INDIRECT ('A1') =A1
Da mesma forma, INDIRECT irá converter o texto 'A1: A10' no intervalo A1: A10 dentro de outra função:
= SUM ( INDIRECT ('A1:A10') = SUM (A1:A10)
À primeira vista, você pode achar esta construção irritante, ou mesmo sem sentido. Por que complicar uma bela fórmula simples com INDIRETO?
Tenha certeza, há método para a loucura :)
A beleza do INDIRETO é que ele permite que você use texto exatamente como uma referência de célula . Isso oferece dois benefícios principais:
- Você pode montar uma referência de texto dentro de uma fórmula, o que é útil para certos tipos de referências dinâmicas .
- Você pode obter valores de texto em uma planilha e usá-los como uma referência de célula em uma fórmula.
No exemplo desta página, estamos combinando a última ideia com intervalos nomeados para construir listas suspensas dependentes. INDIRECT mapeia o texto para um intervalo nomeado, que é então resolvido para uma referência válida. Portanto, neste exemplo, estamos pegando os valores de texto na coluna B e usando INDIRETO para convertê-los em referências de células, combinando intervalos nomeados existentes, como este:
qual é a tabela dinâmica no excel
= INDIRECT (B5) = INDIRECT ('nut') =G5:G9
B5 resolve para o texto 'porca' que resolve para o intervalo G5: G9.
Como configurar listas suspensas dependentes
Esta seção descreve como configurar as listas suspensas dependentes mostradas no exemplo.
1. Crie as listas de que você precisa. No exemplo, crie uma lista de frutas, nozes e vegetais em uma planilha.
2. Crie intervalos nomeados para cada lista: categoria = E4: G4, vegetal = F5: F10, noz = G5: G9 e fruta = E5: E11.
Importante: os valores em E4, F4 e G4 devem corresponder aos três últimos intervalos nomeados acima (vegetais, nozes e frutas). Em outras palavras, você deve certificar-se de que os intervalos nomeados que você criou foram nomeados para corresponder aos valores que aparecerão na lista suspensa Categoria.
Observação: se os intervalos nomeados são novos para você, veja esta pagina .3. Crie e teste uma regra de validação de dados para fornecer uma lista suspensa para Categoria usando a seguinte fórmula personalizada:
=category 
Nota: apenas para ficar claro, o intervalo nomeado 'categoria' é usado apenas para legibilidade e conveniência - o uso de um intervalo nomeado aqui não é obrigatório. Observe também que a validação de dados com uma lista funciona bem com intervalos horizontais e verticais - ambos serão apresentados como um menu suspenso vertical.
4. Crie uma regra de validação de dados para a lista suspensa dependente com uma fórmula personalizada com base na função INDIRETA:
= INDIRECT (B5)
Nesta fórmula, INDIRECT simplesmente avalia os valores na coluna B como referências, que os vincula aos intervalos nomeados definidos anteriormente.
fórmula para contar linhas no excel
5. Teste as listas suspensas para se certificar de que respondem dinamicamente aos valores na coluna B.
Observação: a abordagem que estamos adotando aqui não diferencia maiúsculas de minúsculas. O intervalo nomeado é chamado de 'noz' e o valor em B6 é 'Porca', mas a função INDIRETA resolve corretamente para o intervalo nomeado, embora as maiúsculas e minúsculas sejam diferentes.
Lidando com espaços
Intervalos nomeados não permitem espaços, então a convenção usual é usar caracteres de sublinhado. Então, por exemplo, se você deseja criar um intervalo nomeado para sorvete , você usaria sorvete. Isso funciona bem, mas as listas suspensas dependentes serão quebradas se tentarem mapear 'sorvete' para 'sorvete'. Para corrigir esse problema, você pode usar uma fórmula personalizada mais robusta para validação de dados:
= INDIRECT ( SUBSTITUTE (A1,' ','_'))
Essa fórmula ainda usa INDIRETO para vincular o valor do texto em A1 a um intervalo nomeado, mas antes que INDIRETO seja executado, a função SUBSTITUTE substitui todos os espaços por sublinhados. Se o texto não contiver espaços, SUBSTITUTE não terá efeito.
Arquivo de prática
O arquivo de exemplo está anexado abaixo - verifique-o para ver como funciona. Se você quiser aprender a técnica sozinho, recomendo que crie seu próprio arquivo. A melhor maneira de aprender é fazendo.
Autor Dave Bruns Anexos
dropdown.xlsx dependente
