Como usar UNIQUE() para preencher uma lista suspensa no Microsoft Excel

Preencher uma lista suspensa com uma lista dinâmica é fácil graças à função de matriz dinâmica UNIQUE() do Microsoft Excel.

O recurso de validação de dados do Microsoft Excel fornece uma maneira fácil de criar uma lista suspensa no nível da planilha. Se você basear a lista em dados, provavelmente desejará uma lista de itens exclusivos, o que é facilmente realizado graças à função de matriz dinâmica UNIQUE() do Microsoft Excel. Junte-os e você terá uma maneira fácil de preencher o menu suspenso com uma lista exclusiva. Neste tutorial, mostrarei como juntar esses dois elementos para preencher um menu suspenso com uma lista exclusiva.

VEJA: Comandos do Windows, Linux e Mac que todos precisam saber (PDF grátis) (TechRepublic)

Estou usando a área de trabalho do Microsoft 365 em um sistema Windows 10 de 64 bits. A função UNIQUE() do Excel está disponível apenas com uma do Microsoft 365, Excel para a Web, Excel 2021, Excel para iPad, iPhone e tablets e telefones Android. Para sua conveniência, você pode baixar o arquivo .xlsx de demonstração.

O que é a função UNIQUE() do Excel?

A função de array dinâmico, UNIQUE() já existe há algum tempo, então é possível que você já saiba como usá-la. Se for esse o caso, sinta-se à vontade para pular esta seção. Como uma função independente, UNIQUE() retorna uma lista de valores únicos usando esta sintaxe:

ÚNICO(matriz, [by_col], [exactly_once])

O argumento array é o intervalo do Excel que você deseja reduzir a uma lista exclusiva. O argumento by_col é um valor booleano: TRUE compara colunas e retorna colunas exclusivas; FALSE é o padrão e comparará linhas com linhas e retornará linhas exclusivas.

O argumento exatamente_once também é um valor booleano: TRUE retorna todas as linhas ou colunas distintas que ocorrem exatamente uma vez no intervalo ou na matriz; FALSE, o padrão, retorna todas as linhas ou colunas distintas do intervalo.

Agora, vamos usar esta função para criar uma lista única.

Como criar uma lista única no Excel

O Microsoft Excel oferece e a dois menus suspensos diferentes: validação de dados e um controle de caixa de combinação.

Por enquanto, a validação de dados é a única lista suspensa dinâmica, portanto, este tutorial não abrangerá o controle da caixa de combinação. Por dinâmico, quero dizer que tudo é atualizado automaticamente à medida que você atualiza os dados de origem.

Figura A mostra uma tabela simples chamada Comissão2 — esses são os dados de origem. Como você pode ver, os valores da região se repetem. A primeira coisa que precisamos é de uma lista exclusiva de valores da coluna Region.

É aí que entra UNIQUE(). Você pode usar o recurso Filtro Avançado do Excel para criar uma lista exclusiva, mas precisa atualizá-la ao modificar os dados de origem. Usando UNIQUE() podemos criar um conjunto de resultados de array que é atualizado automaticamente.

Figura A

Agora, vamos usar UNIQUE() para criar uma lista exclusiva de valores de região e, em seguida, basear um menu suspenso de validação de dados nessa lista. Primeiro, vamos criar a lista mostrada na coluna H. Insira a seguinte expressão em H3:

=SORT(UNIQUE(Comissão2[Region]))

ou

=SORT(ÚNICO(menu suspenso!$E$3:$E$13))

se você não estiver usando um objeto Table. No entanto, essa técnica não será dinâmica, a menos que os dados de origem estejam em um objeto Table. Tecnicamente SORT() não é necessário, mas uma lista ordenada é mais fácil de usar.

Figura B mostra os resultados — uma matriz dinâmica que compreende uma lista exclusiva classificada de valores de região da Tabela de Comissão2. Você pode dizer que o resultado é uma matriz porque H3 é a única célula que contém uma expressão e o Excel exibe uma borda azul ao redor da lista.

Com a lista no lugar, é hora de basear uma lista de validação de dados nela.

Figura B

Como basear uma lista suspensa de validação de dados em uma matriz no Excel

Agora estamos prontos para criar o menu suspenso de validação de dados que exibirá a lista exclusiva na coluna H. Para começar, selecione J2 e faça o seguinte:

1. Clique na guia Dados.
2. Clique em Validação de Dados no grupo Ferramentas de Dados.
3. Na caixa de diálogo resultante, escolha Lista no menu suspenso Permitir.
4. Destaque ou insira =$H$3:$H$7 como a Fonte (Figura C).
5. Clique OK.

Figura C

Figura D

Clique no novo menu suspenso para ver a lista exclusiva, mostrada em Figura D. Formatei a célula para que seja fácil encontrar a lista suspensa. Você pode pensar que terminamos, mas a lista suspensa é dinâmica. Vamos dar uma olhada no que isso significa.

Retorne à Tabela Commission2 e selecione a última célula, F13. Pressione Tab e o Excel exibirá um novo registro. Vá até a coluna Região e insira Southcentral.

Como você pode ver em Figura Enão apenas a lista de matrizes dinâmicas na coluna H é atualizada, mas também a lista suspensa de validação de dados!

Figura E

Preencher uma lista suspensa com uma lista exclusiva não é difícil, mas é muito mais fácil quando você usa UNIQUE(). Se você quiser aprender mais técnicas UNIQUE(), leia Como usar a função UNIQUE() para retornar uma contagem de valores exclusivos no Excel.