Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Lista suspensa do Excel: criar, editar, remover e operações mais avançadas

Uma lista suspensa é semelhante a uma caixa de listagem que permite aos usuários escolher um valor de uma lista de seleção. Este tutorial vai demonstrar as operações básicas da lista suspensa: criar, editar e remover a lista suspensa no Excel. Além disso, este tutorial fornece operações avançadas para lista suspensa para aprimorar sua funcionalidade e resolver mais problemas do Excel.

Este tutorial cobre principalmente: (Clique em qualquer título no índice abaixo ou à direita para navegar até o conteúdo correspondente.)


Crie uma lista suspensa simples

Para usar uma lista suspensa, você precisa aprender como criá-la primeiro. Esta seção fornece 6 maneiras de ajudá-lo a criar uma lista suspensa no Excel.

Crie uma lista suspensa a partir de um intervalo de células

Demonstre aqui as etapas para criar uma lista suspensa de um intervalo de células no Excel. Por favor faça o seguinte

1. Selecione um intervalo de células para localizar a lista suspensa.

Tips: Você pode criar uma lista suspensa para várias células não contíguas ao mesmo tempo, mantendo o botão Ctrl enquanto seleciona as células uma por uma.

2. Clique Dados > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, sob o Configurações guia, configure da seguinte forma.

3.1) No Permitir lista suspensa, selecione lista;
3.2) No fonte caixa, selecione o intervalo de células cujos valores você exibirá na lista suspensa;
3.3) Clique no OK botão.

Notas:

1) Você pode marcar ou desmarcar o Ignorar em branco dependendo de como você deseja lidar com as células em branco no intervalo selecionado;
2) Certifique-se de que Lista suspensa na célula caixa está marcada. Se esta caixa estiver desmarcada, a seta suspensa não aparecerá ao selecionar a célula.
3) No fonte , você pode digitar manualmente valores separados por vírgula, conforme mostrado na imagem abaixo.

Agora a lista suspensa foi criada. Ao clicar na célula da lista suspensa, uma seta será exibida ao lado dela, clique na seta para expandir a lista e, em seguida, você pode escolher um item dela.

Crie uma lista suspensa dinâmica a partir da tabela

Você pode converter seu intervalo de dados em uma tabela do Excel e, em seguida, criar uma lista suspensa dinâmica com base no intervalo da tabela.

1. Selecione o intervalo de dados original e pressione o botão Ctrl + T chaves.

2. Clique OK no surgimento Criar a tabela caixa de diálogo. Em seguida, o intervalo de dados é convertido em tabela.

3. Selecione um intervalo de células para colocar a lista suspensa e clique em Dados > Validação de dados > Validação de dados.

4. No Validação de dados caixa de diálogo, você precisa:

4.1) Selecione Lista no Permitir lista suspensa;
4.2) Selecione o intervalo da tabela (excluindo o cabeçalho) no fonte caixa;
4.3) Clique no OK botão.

Em seguida, listas suspensas dinâmicas são criadas. Ao adicionar ou remover dados do intervalo da tabela, os valores na lista suspensa serão atualizados automaticamente.

Crie uma lista suspensa dinâmica com fórmulas

Além de criar uma lista suspensa dinâmica a partir do intervalo da tabela, você também pode usar uma fórmula para criar uma lista suspensa dinâmica no Excel.

1. Selecione as células nas quais deseja a saída das listas suspensas.

2. Clique Dados > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, configure como segue.

3.1) No Permitir caixa, selecione Lista;
3.2) No fonte caixa, insira a fórmula abaixo nela;
= OFFSET ($ A $ 13,0,0, CONTAGEM ($ A $ 13: $ A $ 24), 1)
Importante: Nesta fórmula, $ A $ 13 é a primeira célula do intervalo de dados e $ A $ 13: $ A $ 24 é o intervalo de dados em que você criará listas suspensas.
3.3) Clique no OK botão. Veja a imagem:

Em seguida, listas suspensas dinâmicas são criadas. Ao adicionar ou remover dados de um intervalo específico, os valores nas listas suspensas serão atualizados automaticamente.

Crie uma lista suspensa de um intervalo nomeado

Você também pode criar uma lista suspensa de um intervalo nomeado no Excel.

1. Em primeiro lugar, crie um intervalo nomeado. Selecione o intervalo de células que você criará com base no intervalo nomeado e, em seguida, digite o nome do intervalo no Nome caixa e pressione entrar chave.

2. Clique Dados > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, configure como segue.

3.1) No Permitir caixa, selecione Lista;
3.2) Clique no botão fonte caixa e, em seguida, pressione a F3 chave.
3.3) No Colar Nome caixa de diálogo, selecione o nome do intervalo que você criou agora e clique no botão OK botão;
Dicas: Você também pode inserir manualmente = nome do intervalo no fonte caixa. Nesse caso, vou entrar = Cidade.
3.4) Clique OK quando ele retorna ao Validação de dados caixa de diálogo. Veja a imagem:

Agora, a lista suspensa usando dados de um intervalo nomeado é criada.

Crie uma lista suspensa de outra pasta de trabalho

Supondo que haja uma pasta de trabalho chamada “SourceData”, E você deseja criar uma lista suspensa em outra pasta de trabalho com base nos dados deste“SourceData”Pasta de trabalho, faça o seguinte.

1. Abra a pasta de trabalho “SourceData”. Nesta pasta de trabalho, selecione os dados que você criará na lista suspensa com base, digite um nome de intervalo no Nome caixa e, em seguida, pressione a entrar chave.

Aqui, nomeio o intervalo como Cidade.

2. Abra a planilha que você inserirá na lista suspensa. Clique Fórmulas > Definir o Nome.

3. No Novo nome caixa de diálogo, você precisa criar um intervalo nomeado com base no nome do intervalo que você criou na pasta de trabalho “SourceData”, configure conforme a seguir.

3.1) Insira um nome no Nome caixa;
3.2) No Refere-se a caixa, digite a fórmula abaixo nela.
= SourceData.xlsx! City
3.3) Clique OK para salvá-lo

Notas:

1). Na fórmula, SourceData é o nome da pasta de trabalho que contém os dados nos quais você criará uma lista suspensa; Cidade é o nome do intervalo especificado na pasta de trabalho SourceData.
2). Se houver espaço ou outros caracteres como -, # ... incluindo o nome da pasta de trabalho de dados de origem, você precisará colocar o nome da pasta de trabalho entre aspas simples, como = 'Source Data.xlsx'! Cidade.

4. Abra a pasta de trabalho que você irá inserir na lista suspensa, selecione as células para a lista suspensa e clique em Dados > Validação de dados > Validação de dados.

5. No Validação de dados caixa de diálogo, configure como segue.

5.1) No Permitir caixa, selecione Lista;
5.2) Clique no botão fonte caixa e, em seguida, pressione a F3 chave.
5.3) No Colar Nome caixa de diálogo, selecione o nome do intervalo que você criou agora e clique no botão OK botão;
Tips: Você também pode inserir manualmente = nome do intervalo no fonte caixa. Nesse caso, vou entrar = Teste.
5.4) Clique OK quando ele retorna ao Validação de dados caixa de diálogo.

Agora, as listas suspensas foram inseridas no intervalo selecionado. E os valores suspensos são de outra pasta de trabalho.

Crie facilmente uma lista suspensa com uma ferramenta incrível

Aqui recomendo fortemente o Crie uma lista suspensa simples utilidade de Kutools for Excel. Com este recurso, você pode criar facilmente uma lista suspensa com valores de células específicos ou criar uma lista suspensa com listas personalizadas predefinidas no Excel.

1. Selecione as células que deseja inserir na lista suspensa e clique em Kutools > Lista suspensa > Crie uma lista suspensa simples.

2. No Crie uma lista suspensa simples caixa de diálogo, configure como segue.

3.1) No Aplicar a , você pode ver que o intervalo selecionado é exibido aqui. Você pode alterar o intervalo de células aplicado conforme necessário;
3.2) No fonte seção, se você deseja criar listas suspensas com base nos dados de um intervalo de células ou apenas precisa inserir valores manualmente, selecione o Insira um valor ou faça referência a um valor de célula opção. Na caixa de texto, selecione o intervalo de células ou digite os valores (separados por vírgulas) em que você criará a lista suspensa;
3.3) Clique OK.

Importante: Se você deseja criar uma lista suspensa com base na predefinição de lista personalizada no Excel, selecione o Listas personalizadas opção no fonte seção, escolha uma lista personalizada no Listas personalizadas e, em seguida, clique no OK botão.

Agora, as listas suspensas foram inseridas no intervalo selecionado.


Editar lista suspensa

Se você deseja editar a lista suspensa, os métodos nesta seção podem lhe fazer um favor.

Edite uma lista suspensa com base em um intervalo de células

Para editar uma lista suspensa com base em um intervalo de células, faça o seguinte.

1. Selecione as células que contêm a lista suspensa que deseja editar e clique em Dados > Validação de dados > Validação de dados.

2. No Validação de dados caixa de diálogo, altere as referências de célula no fonte caixa e, em seguida, clique no botão OK botão.

Edite uma lista suspensa com base em um intervalo nomeado

Suponha que você adicione ou exclua valores no intervalo nomeado e a lista suspensa seja criada com base neste intervalo nomeado. Para aparecer os valores atualizados nas listas suspensas, faça o seguinte.

1. Clique Fórmulas > Gerente de Nomes.

Tips: Você pode abrir o Gerente de Nomes janela pressionando o Ctrl + F3 chaves.

2. No Gerente de Nomes janela, você precisa configurar da seguinte maneira:

2.1) No Nome caixa, selecione o intervalo nomeado que deseja atualizar;
2.2) No Refere-se a seção, clique no botão para selecionar o intervalo atualizado para sua lista suspensa;
2.3) Clique no Fechar botão.

3. Então um Microsoft Excel caixa de diálogo aparece, clique no Sim para salvar as alterações.

Em seguida, as listas suspensas com base neste intervalo nomeado são atualizadas.


Remover lista suspensa

Esta seção fala sobre como remover a lista suspensa do Excel.

Remova a lista suspensa com o Excel integrado

O Excel fornece um recurso integrado para ajudar a remover a lista suspensa da planilha. Faça o seguinte.

1. Selecione o intervalo de células que contém a lista suspensa que deseja remover.

2. Clique Dados > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, clique no botão Limpar Tudo botão e, em seguida, clique em OK para salvar as mudanças.

Agora, as listas suspensas são removidas do intervalo selecionado.

Remova listas suspensas facilmente com uma ferramenta incrível

Kutools for Excel fornece uma ferramenta útil - Limpe a restrição de validação de dadoss para ajudar a remover facilmente a lista suspensa de um ou vários intervalos selecionados de uma vez. Faça o seguinte.

1. Selecione o intervalo de células que contém a lista suspensa que deseja remover.

2. Clique Kutools > Impedir Digitação > Limpar restrições de validação de dados. Veja a imagem:

3. Então um Kutools for Excel uma caixa de diálogo aparecerá perguntando se você deseja limpar a lista suspensa, clique no OK botão.

Em seguida, as listas suspensas neste intervalo selecionado são removidas imediatamente.


Adicionar cor à lista suspensa

Em alguns casos, pode ser necessário fazer uma lista suspensa codificada por cores para distinguir os dados nas células da lista suspensa rapidamente. Esta seção fornece dois métodos para ajudá-lo a resolver o problema em detalhes.

Adicionar cor à lista suspensa com Formatação Condicional

Você pode criar regras condicionais para a célula que contém a lista suspensa para torná-la codificada por cores. Faça o seguinte.

1. Selecione as células que contêm a lista suspensa que você deseja codificar por cores.

2. Clique Início > Formatação condicional > Gerenciar regras.

3. No Gerenciador de linhas de formatação condicional caixa de diálogo, clique no botão Nova regra botão.

4. No Nova regra de formatação caixa de diálogo, configure como segue.

4.1) No Selecione um tipo de regra caixa, escolha o Formate apenas células que contenham opção;
4.2) No Formate apenas células com seção, selecione Texto Específico na primeira lista suspensa, selecione contendo na segunda lista suspensa e, a seguir, selecione o primeiro item da lista de fontes na terceira caixa;
Tips: Aqui, seleciono a célula A16 na terceira caixa de texto. A16 é o primeiro item da lista de fontes que criei com base na lista suspensa.
4.3) Clique no Formato botão.
4.4) No formatar células caixa de diálogo, vá para o Preencher guia, escolha uma cor de fundo para o texto especificado e clique no OK botão. Ou você pode escolher uma determinada cor de fonte para o texto conforme necessário.
4.5) Clique no OK botão quando ele retorna para o Nova regra de formatação caixa de diálogo.

5. Quando ele retorna ao Gerenciador de regras de formatação condicional caixa de diálogo, repita os passos 3 e 4 acima para especificar as cores para outros itens suspensos. Após terminar de especificar as cores, clique no OK para salvar as mudanças.

A partir de agora, ao selecionar um item da lista suspensa, a célula será destacada com a cor de fundo especificada com base no texto selecionado.

Adicione cores facilmente à lista suspensa com uma ferramenta incrível

Aqui apresente o Lista suspensa colorida característica de Kutools for Excel para ajudá-lo a adicionar cores facilmente à lista suspensa do Excel.

1. Selecione as células que contêm a lista suspensa que você deseja adicionar cor.

2. Clique Kutools > Lista suspensa > Lista suspensa colorida.

3. No Lista suspensa colorida caixa de diálogo, faça o seguinte.

3.1) No Aplicar a seção, selecione o Célula da lista suspensa opção;
3.2) No Intervalo de validação de dados (lista suspensa) , você pode ver que as referências de células selecionadas são exibidas dentro. Você pode alterar o intervalo de células conforme necessário;
3.3) No lista de itens (todos os itens suspensos no intervalo selecionado são exibidos aqui), selecione um item e você especificará uma cor para ele;
3.4) No Selecione a cor seção, escolha uma cor de fundo;
Importante: Você precisa repetir os passos 3.3 e 3.4 para especificar cores diferentes para os outros itens;
3.5) Clique no OK botão. Veja a imagem:

Tips: Se você deseja destacar as linhas com base na seleção da lista suspensa, escolha o Linha de intervalo de dados opção no Aplicar a seção e, em seguida, selecione as linhas que você destacará na Destacar linhas caixa.

Agora, as listas suspensas são codificadas por cores conforme as imagens abaixo mostradas.

Destacar células com base na seleção da lista suspensa

Destacar linhas com base na seleção da lista suspensa


Crie uma lista suspensa de dependentes no Excel ou no Google Planilha

Uma lista suspensa dependente ajuda a exibir as opções dependendo do valor selecionado na primeira lista suspensa. Se você precisar criar uma lista suspensa de dependentes (cascarding) na planilha do Excel ou no google sheet, os métodos nesta seção podem lhe fazer um favor.

Crie uma lista suspensa dependente na planilha do Excel

A demonstração abaixo exibe a lista suspensa dependente na planilha do Excel.

Por favor, clique Como criar uma lista suspensa em cascata dependente no Excel? para um tutorial de guia passo a passo.

Crie uma lista suspensa dependente na planilha do google

Se você deseja criar uma lista suspensa de dependentes na planilha do google, consulte Como criar uma lista suspensa de dependentes na planilha do Google?


Crie listas suspensas pesquisáveis

Para as listas suspensas que contêm uma longa lista de itens em uma planilha, não é fácil escolher um determinado item da lista. Se você se lembrou dos caracteres iniciais ou vários caracteres consecutivos de um item, você pode fazer o recurso de pesquisa em uma lista suspensa para filtrá-lo facilmente. Esta seção demonstrará como criar uma lista suspensa pesquisável no Excel.

Supondo que os dados de origem que você deseja criar uma lista suspensa com base nos locais na coluna A da Planilha1, conforme a captura de tela abaixo mostrada. Faça o seguinte para criar uma lista suspensa pesquisável no Excel com esses dados.

1. Em primeiro lugar, crie uma coluna auxiliar ao lado da lista de dados de origem com uma fórmula de matriz.

Neste caso, eu seleciono a célula B2, insiro a fórmula abaixo nela e pressiono o botão Ctrl + mudança + entrar chaves para obter o primeiro resultado.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

Selecione a primeira célula de resultado e arraste seu Pega de enchimento todo o caminho até chegar ao final da lista.

Importante: Nesta fórmula de matriz, $ A $ 2: $ A $ 50 é o intervalo de dados de origem no qual você criará a lista suspensa. Altere-o com base no seu intervalo de dados.

2. Clique Fórmulas > Definir o Nome.

3. No Editar nome caixa de diálogo, configure como segue.

3.1) No Nome caixa, digite um nome para o intervalo nomeado;
3.2) No Refere-se a caixa, insira a fórmula abaixo nela;
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)
3.3) Clique no OK botão. Veja a imagem:

Agora você precisa criar a lista suspensa com base no intervalo nomeado. Nesse caso, criarei uma lista suspensa pesquisável na Planilha2.

4. Abra a Planilha2, selecione o intervalo de células para a lista suspensa e clique em Dados > Validação de dados > Validação de dados.

5. No Validação de dados caixa de diálogo, faça o seguinte.

5.1) No Permitir caixa, selecione Lista;
5.2) Clique no fonte caixa e, em seguida, pressione a F3 chave;
5.3) No popping up Colar Nome caixa de diálogo, selecione o intervalo nomeado que você criou na etapa 3 e clique em OK;
Tips: Você pode inserir diretamente o intervalo nomeado como = intervalo nomeado no fonte caixa.
5.4) Clique no Alerta de Erro guia, desmarque o Mostrar alerta de erro após dados inválidos serem inseridos e, finalmente, clique no OK botão.

6. Clique com o botão direito na guia da folha (Folha2) e selecione Ver código no menu do botão direito.

7. Na abertura Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: crie uma lista suspensa pesquisável no Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Appliations janela.

Agora as listas suspensas pesquisáveis ​​são criadas. Se quiser selecionar um item, basta inserir um ou vários caracteres consecutivos desse item na célula suspensa, clicar na seta suspensa e, em seguida, o item baseado no conteúdo inserido é listado na lista suspensa. Veja a imagem:

Importante: Este método diferencia maiúsculas de minúsculas.


Crie uma lista suspensa, mas mostre valores diferentes

Supondo que você tenha criado uma lista suspensa, ao selecionar um item dela, você deseja que outra coisa seja exibida na célula. Como mostra a demonstração abaixo, você criou uma lista suspensa com base na lista de nomes de países; ao selecionar o nome do país no menu suspenso, você deseja exibir a abreviatura do nome do país selecionado na célula suspensa. Esta seção fornece o método VBA para ajudá-lo a resolver o problema.

1. No lado direito dos dados de origem (a coluna do nome do país), crie uma nova coluna contendo a abreviatura dos nomes dos países que você deseja exibir na célula suspensa.

2. Selecione a lista de nomes de países e a lista de abreviações, digite um nome no Nome caixa e, em seguida, pressione o entrar chave.

3. Selecione as células para a lista suspensa (aqui eu seleciono D2: D8) e clique em Dados > Validação de dados > Validação de dados.

4. No Validação de dados caixa de diálogo, configure como segue.

4.1) No Permitir caixa, selecione Lista;
4.2) No fonte caixa, selecione o intervalo de dados de origem (a lista de nomes de países, neste caso);
4.3) Clique OK.

5. Depois de criar a lista suspensa, clique com o botão direito na guia da planilha e selecione Ver código no menu do botão direito.

6. Na abertura Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: Mostrar valores diferentes na lista suspensa

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Notas:

1) No código, o número 4 na linha Se Target.Column = 4 Em seguida, representa o número da coluna da lista suspensa que você criou nas etapas 3 e 4. Se a sua lista suspensa estiver localizada na coluna F, substitua o número 4 por 6;
2) O “suspensa”Na quinta linha é o nome do intervalo que você criou na etapa 2. Você pode alterá-lo conforme necessário.

7. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

A partir de agora, ao selecionar um determinado nome de país da lista suspensa, a abbreation correspondente do nome do país selecionado será exibida na célula.


Crie uma lista suspensa com caixas de seleção

Muitos usuários do Excel tendem a criar listas suspensas com várias caixas de seleção para que possam selecionar vários itens da lista apenas marcando as caixas de seleção.

Como mostra a demonstração abaixo, ao clicar na célula que contém a lista suspensa, uma caixa de listagem aparece. Na caixa de listagem, há uma caixa de seleção antes de cada item. Você pode marcar as caixas de seleção para exibir os itens correspondentes na célula.

Se você deseja criar uma lista suspensa com caixas de seleção no Excel, consulte Como criar uma lista suspensa com várias caixas de seleção no Excel?.


Adicionar autocomplete à lista suspensa

Se você tiver uma lista suspensa de validação de dados com itens grandes, precisará rolar para cima e para baixo na lista para encontrar o apropriado ou digitar a palavra inteira diretamente na caixa de listagem. Se a lista suspensa puder ser preenchida automaticamente ao digitar a primeira letra, tudo ficará mais fácil.

Para fazer o preenchimento automático da lista suspensa em uma planilha no Excel, consulte Como preencher automaticamente ao digitar na lista suspensa do Excel?.


Filtre os dados com base na seleção da lista suspensa

Esta seção demonstrará como aplicar fórmulas para criar um filtro de lista suspensa para extrair dados com base na seleção da lista suspensa.

1. Em primeiro lugar, você precisa criar uma lista suspensa com os valores específicos nos quais você extrairá os dados.

Tips: Siga as etapas acima para crie uma lista suspensa no Excel.

Crie uma lista suspensa com uma lista única de itens

Se houver duplicatas em seu intervalo e você não quiser criar uma lista suspensa com a repetição de um item, você pode criar uma lista exclusiva de itens como segue.

1) Copie as células que você criará na lista suspensa com base em Ctrl + C e cole-as em um novo intervalo.

2) Selecione as células no novo intervalo, clique em Dados > Remover Duplicados.

3) No Remover Duplicados caixa de diálogo, clique no botão OK botão.

4) Em seguida, um Microsoft Excel aparece para informar quantas cópias foram removidas, clique OK.

Agora que você obtém a lista exclusiva de itens, pode criar uma lista suspensa com base nessa lista exclusiva agora.

2. Em seguida, você precisa criar três colunas auxiliares da seguinte maneira.

2.1) Para a primeira coluna auxiliar (aqui eu escolho a coluna D como a primeira coluna auxiliar), insira a fórmula abaixo na primeira célula (exceto o cabeçalho da coluna) e pressione o botão entrar chave. Selecione a célula de resultado e arraste o Pega de enchimento todo o caminho para baixo até chegar ao fundo do intervalo.
= LINHAS ($ A $ 2: A2)
2.2) Para a segunda coluna auxiliar (a coluna E), insira a fórmula abaixo na célula E2 e pressione o botão entrar chave. Selecione E2 e arraste o Pega de enchimento para a parte inferior do intervalo.
Nota: Se nenhum valor for selecionado na lista suspensa, aqui os resultados das fórmulas serão exibidos em branco.
= SE (A2 = $ H $ 2, D2, "")
2.3) Para a terceira coluna auxiliar (a coluna F), insira a fórmula abaixo em F2 e pressione o botão entrar chave. Selecione F2 e arraste o Pega de enchimento para a parte inferior do intervalo.
Importante: Se nenhum valor for selecionado na lista suspensa, os resultados das fórmulas serão exibidos em branco.
= IFERROR (PEQUENO ($ E $ 2: $ E $ 17, D2), "")

3. Crie um intervalo com base no intervalo de dados original para produzir os dados extraídos com as fórmulas abaixo.

3.1) Selecione a primeira célula de output (aqui eu seleciono J2), insira a fórmula abaixo nela e pressione o botão entrar chave.
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")
3.2) Selecione a célula de resultado e arraste o Pega de enchimento à direita, duas células.
3.3) Mantenha o intervalo J2: l2 selecionado, arraste a alça de preenchimento todo o caminho para baixo até atingir o fundo do intervalo.

Notas:

1) Se nenhum valor for selecionado na lista suspensa, os resultados das fórmulas serão exibidos em branco.
2) Você pode ocultar as três colunas auxiliares conforme necessário.

Agora que um filtro de lista suspensa foi criado, você pode facilmente extrair dados do intervalo de dados original com base na seleção da lista suspensa.


Selecione vários itens da lista suspensa

Por padrão, a lista suspensa permite que os usuários selecionem apenas um item por vez em uma célula. Ao selecionar novamente o item em uma lista suspensa, o item selecionado anteriormente será substituído. No entanto, se você for solicitado a selecionar vários itens de uma lista suspensa e exibir todos eles na célula suspensa como mostrado na demonstração abaixo, como você pode fazer?

Para selecionar vários itens da lista suspensa no Excel, consulte Como criar uma lista suspensa com várias seleções ou valores no Excel?. Este tutorial fornece dois métodos em detalhes para ajudá-lo a resolver o problema.


Definir valor padrão (pré-selecionado) para a lista suspensa

Por padrão, uma célula da lista suspensa é exibida em branco, a seta suspensa só aparece quando você clica na célula. Como descobrir quais células contêm listas suspensas em uma planilha rapidamente?

Esta seção demonstrará como definir o valor padrão (pré-selecionado) para a lista suspensa no Excel. Faça o seguinte.

Antes de aplicar os dois métodos abaixo, você precisa criar uma lista suspensa e fazer algumas configurações como segue.

1. Selecione as células para a lista suspensa e clique em Dados > Validação de dados > Validação de dados.

Tips: Se você já criou a lista suspensa, selecione as células que contêm a lista suspensa e clique em Dados > Validação de dados > Validação de dados.

2. No Validação de dados caixa de diálogo, configure como segue.

2.1) No Permitir caixa, selecione Lista;
2.2) No fonte caixa, selecione os dados de origem que você exibirá na lista suspensa.
Tips: Para a lista suspensa que você já criou, pule essas duas etapas.
2.3) Em seguida, vá para o Alerta de Erro guia, desmarque o Mostrar alerta de erro após dados inválidos serem inseridos caixa;
2.4) Clique no OK botão.

Depois de criar a lista suspensa, aplique um dos métodos abaixo para definir o valor padrão para eles.

Defina o valor padrão para a lista suspensa com fórmula

Você pode aplicar a fórmula abaixo para definir o valor padrão para a lista suspensa que você criou conforme as etapas acima mostradas.

1. Selecione a célula da lista suspensa, insira a fórmula abaixo nela e pressione o botão entrar para exibir o valor padrão. Se as células da lista suspensa forem consecutivas, você pode arrastar o Pega de enchimento da célula de resultado para aplicar a fórmula a outras células.

= IF (C2 = "", "--Escolha um item da lista--")

Notas:

1) Na fórmula, C2 é uma célula em branco ao lado da célula da lista suspensa, você pode especificar qualquer célula em branco conforme necessário.
2) --Escolha um item da lista-- é o valor padrão a ser exibido na célula da lista suspensa. Você também pode alterar o valor padrão com base na sua necessidade.
3) A fórmula só funciona antes de selecionar itens no menu suspenso, após selecionar o item no menu suspenso, o valor padrão será substituído e a fórmula será eliminada.
Defina o valor padrão para todas as listas suspensas em uma planilha de uma vez com o código VBA

Supondo que existam muitas listas suspensas localizadas em diferentes intervalos em sua planilha, para definir o valor padrão para todas elas, você precisa aplicar a fórmula repetidamente. Isso é demorado. Esta seção fornece um código VBA útil para você definir o valor padrão para todas as listas suspensas em uma planilha de uma só vez.

1. Abra a planilha contendo as listas suspensas para as quais deseja definir o valor padrão e pressione o botão outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Móduloe, em seguida, cole o código VBA abaixo na janela Código.

Código VBA: defina o valor padrão para todas as listas suspensas em uma planilha de uma vez

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

Notas: No código acima, - Escolha da lista - é o valor padrão a ser exibido na célula da lista suspensa. Você também pode alterar o valor padrão com base na sua necessidade.

3. aperte o F5 , em seguida, uma caixa de diálogo Macros aparece, certifique-se de que DropDownListToDefault é selecionado no Nome da Macro e, em seguida, clique no Corrida botão para executar o código.

Em seguida, o valor padrão especificado é preenchido nas células da lista suspensa imediatamente.


Aumentar o tamanho da fonte da lista suspensa

Normalmente, a lista suspensa tem um tamanho de fonte fixo, se o tamanho da fonte for tão pequeno para ler, você pode tentar o método VBA abaixo para aumentá-lo.

1. Abra a planilha contendo as listas suspensas para as quais deseja aumentar o tamanho da fonte, clique com o botão direito na guia da planilha e selecione Ver código no menu do botão direito.

2. No Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: aumenta o tamanho da fonte das listas suspensas em uma planilha

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Importante: aqui xZoom = 130 no código significa que você aumentará o tamanho da fonte de todas as listas suspensas na planilha atual para 130. Você pode alterá-lo conforme necessário.

3. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

A partir de agora, ao clicar na célula suspensa, o nível de zoom da planilha atual será ampliado, clique na seta suspensa, você pode ver o tamanho da fonte de todos os itens suspensos também são aumentados.

Depois de selecionar o item da lista suspensa, você pode clicar em qualquer célula fora da célula suspensa para retornar ao nível de zoom original.


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    通りすがりのわんこ · 3 months ago
    こちらはOffice365ですが、どうやらそのコーディングでは動作しないようです。
    代わりに初歩的ですが、以下にて動作を確認出来ました。

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim xZoom As Variant
    If (Target.Row >= 11 And Target.Row <= 35 And Target.Column >= 3 And Target.Column <= 6) Then
    ActiveWindow.zoom = 150
    Else
    ActiveWindow.zoom = 60
    End If
    End Sub