Neste artigo você aprenderá como criar Lista de Dados ou menu DropDown no Excel.
Ao longo do artigo e também na vídeo-aula você aprenderá diversas técnicas como desde uma lista de validação fixa no Excel até uma lista com autocompletar.
Como Criar uma Lista de Dados Fixa no Excel
Para criar uma lista de dados fixa no Excel uma solução é usar a validação de dados.
A validação de dados tem por finalidade impedir que dados sejam lançados incorretamente, como um item fora de uma lista, um texto em um campo de data, número…
Para criar uma lista de dados fixa no Excel usando a validação de dados você deve selecionar o local aonde deseja inserir a lista de validação.
Em seguida clique em Dados->Validação de dados.
Clique em Permitir e selecione a opção Lista e selecione o intervalo aonde você tem os dados que desejam que conste na lista de validação.
Com isso você já terá uma lista com os itens constantes na sua lista.
Esta lista necessariamente não precisa ser à partir e um conjunto de células, pode ser também à partir de um intervalo nomeado ou ainda de textos digitados.
Na coluna horário incluímos apenas os horários permitidos usando uma lista.
Para isso usamos a mesma técnica anterior, apenas digitando os textos conforme na imagem abaixo.
Com isso poderão ser selecionados ou digitados apenas os valores 11:00, 18:30 ou 19:00. Separe os valores da lista com ;.
Lista de Validação com Tamanho Variável
Uma das formas de retornar uma lista de dados com tamanho variável é usando as funções de matrizes dinâmicas ÚNICO e CLASSIFICAR.
A função Único do Excel permite extrair uma lista sem duplicidades à partir de uma lista com itens duplicados. Isso dinamicamente.
Para o exemplo, temos uma lista conforme acima, de produtos da empresa, e desejamos extrair somente as categorias e colocar em uma lista para seleção.
Então usamos a função =ÚNICO(Tabela9[Categoria]) para que sejam retornados apenas valores sem repetição.
Também pode ser aplicada a função CLASSIFICAR em conjunto, e assim termos uma lista única de dados e classificada.
Para isso usamos então as funções =CLASSIFICAR(ÚNICO(Tabela9[Categoria])).
E após isso precisamos ainda colocar em formato de lista de validação de dados no Excel.
Para isso clique em Dados->Validação de dados e selecione Lista em Permitir.
Em fonte, ao invés de selecionar todos os itens, selecione apenas a primeira célula, a célula que possui a fórmula da lista única e classificada.
Digite # ao final da referência.
Isso fará com que a validação de dados entenda que se trata de uma matriz dinâmica, ou seja, os itens devem ser listados à partir da célula da fórmula.
E o resultado é então uma lista única e classificada na validação, e se tiverem novos itens, automaticamente serão retornados estes itens para a lista de validação.
Outro ponto importante é que esta fórmula que vimos também pode ser adicionada como um nome definido em Fórmulas->Definir Nome e assim usar este nome definido na lista de validação.
Lista de Dados Encadeadas ou em Cascata no Excel
Há vezes em que temos listas de dados encadeadas, ou em cascata no Excel.
Um exemplo típico é quando temos uma lista de UF e cidades e desejamos listar apenas as cidades de uma determinada UF.
No exemplo acima temos então a necessidade de selecionar uma UF e ao lado dela selecionar uma cidade.
Para isso usamos a seguinte fórmula, para listar apenas os itens da UF AC que está na célula J9.
=DESLOC($B$9;CORRESP($I$9;Tabela3614[UF];0);;CONT.SE(Tabela3614[UF];$I$9))
A lista de UF e Cidade está agrupada por UF e classificada por UF, importante salientar que só funcionará deste jeito esta técnica.
A função acima é um DESLOC usando CORRESP para definir aonde começa, aonde tem a primeira uf AC e depois temos a função CONT.SE para definir a quantidade de vezes que a palavra AC aparece e com isso temos uma relação de cidades com início e tamanho variável.
Lembro ainda que o download está no final deste artigo, pode baixar para ver o exemplo pronto.
O resultado é a lista abaixo.
Fazemos então a inclusão desta fórmula em Gerenciador de nomes->Fórmulas e usamos na validação de dados o nome que usamos, no caso ListaCidades.
Clique no local aonde deseja adicionar a validação de dados e clique em Dados->Validação de dados e selecione Lista.
Temos agora uma lista de cidades conforme a UF que estiver na célula J9.
Para fazer com que esta lista mude conforme a UF selecionada podemos usar a função CÉL.
A função CÉL extrai informações da planilha ou da célula, no caso, da célula que acabou de ser alterada.
Com isso temos que ao alterar qualquer célula o conteúdo irá alterar na célula J9 que controla a lista de cidades que aparecem na lista de dados.
Então ao alterarmos na coluna UF temos esta lista alterada e podemos selecionar a cidade na lista de cidades.
Lista de Validação Encadeadas ou em Cascata com VBA
A lista de dados que vimos no item anterior tem algumas particularidades, como a necessidade de selecionar a UF sempre que mudar de linha.
Podemos fazer com VBA de outra forma para que esse código da UF seja mudado automaticamente
Então, pode usar a mesma função DESLOC que vimos acima e toda a estrutura com o gerenciador de nomes, mas não use a função CÉL.
Usaremos um código VBA que irá pegar a UF à partir da linha da coluna C.
Clique sobre a célula aonde há a UF, aonde irá controlar a UF, como no exemplo anterior e defina o nome para ufselecionada.
Primeiro habilite a guia desenvolvedor no Excel.
Em seguida clique na planilha na árvore de projeto que deseja colocar a alteração dinâmica da UF.
Clique duas vezes sobre e cole o seguinte código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Calculos.Range("ufselecionada").Value = Cells(Target.Row, 2).Value
End If
End Sub
O código acima irá ser chamado ao mudar de célula selecionada
E ao mudar a célula será verificado se a coluna atual é a 3, ou seja, a coluna da cidade.
Se for a célula ufselecionada, que controla a UF que será listada na lista de dados retorna as cidades será alterada com a informação da coluna B que tem a UF relacionada.
Lista de Validação Incremental no Excel
Outra forma interessante de usar listas no Excel é de forma incremental.
Por exemplo, temos uma lista de TVs e desejamos selecionar as qualidades que fazem parte de cada uma à partir de uma lista previamente cadastrada, e desejamos que sejam separados por vírgula.
A primeira etapa é a de criar uma lista suspensa fixa.
Selecione a coluna aonde irá criar a lista de dados incremental no Excel.
E clique em Dados->Validação de dados, selecione a lista.
Abra o VBA clicando na guia Desenvolvedor no botão Visual Basic ou pressione ALT+F11.
Coloque o seguinte código na planilha:
'Código para adicionar dados á uma seleção apartir de uma lista de validações.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strSep As String
strSep = ", "
Application.EnableEvents = False
On Error Resume Next
If Target.Count > 1 Then GoTo Sair
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo Sair
If rngDV Is Nothing Then GoTo Sair
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If newVal <> "" Then
If oldVal = "" Then
Target.Value = newVal
Else
Target.Value = oldVal & strSep & newVal
End If
End If
End If
Sair:
Application.EnableEvents = True
End Sub
O código acima fará com que ao selecionar um item, ele seja adicionado na linha e com uma vírgula para assim você ter uma lista de qualidades de cada televisor.
Lista de Validação com Autocompletar
O Excel também permite que criemos listas com autocompletar ou lista com completar ao digitar.
No momento não há ainda no Excel este recurso nativo e não deverá existir em versões anteriores ao 2021.
Para criar uma lista com autocompletar no Excel em qualquer versão siga o seguinte procedimento.
Clique na guia Desenvolvedor e selecione o botão Caixa de Combinação (Controle Active X)
Desenhe ele em qualquer lugar da planilha.
Clique sobre ele e no botão Propriedades.
Altere o nome dele para TempCombo.
Na guia Desenvolvedor clique em Visual Basic, clique duas vezes sobre a planilha aonde desenhou e irá criar a lista de autocompletar.
Cole o código seguinte:
Option Explicit
'Baseado em código da www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Ocultar caixa de combinação e mover a próxima célula com Enter e Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'Nada
End Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets(Me.Name)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.CutCopyMode Then
'Permite copiar e colar na planilha
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'Abrir a lista suspensa automaticamente
Me.TempCombo.DropDown
End If
errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End Sub
Este código irá fazer com que ao digitar sobre uma lista de validação que existir na planilha, automaticamente a tempCombo serja desejada sobre a célula e abra para que você possa digitar e ela ficará como autocompletar.
Ou seja, ao digitar ela vai completar e ao dar Enter o valor será digitado na célula.
Download planilha lista de validação no Excel – DropDown
Clique no botão abaixo para realizar o download da planilha de TextoDivisão Excel, com exemplo de dados:
