Como Preencher Automaticamente Outras Células Após Selecionar um Valor de uma Lista Suspensa no Excel: Um Guia Abrangente
Ao trabalhar com o Excel, automatizar a entrada de dados pode aumentar significativamente a produtividade. Uma tarefa comum é preencher automaticamente dados relacionados após selecionar um valor de uma lista suspensa. Este guia explora quatro métodos abrangentes, desde funções nativas do Excel até VBA e ferramentas de terceiros como Kutools.
Primeiro de Tudo: Criar uma Lista Suspensa
Método 1: Preenchimento Automático Usando a Função PROCV
Método 2: Preenchimento Automático Usando as Funções ÍNDICE e CORRESP
Método 3: Preenchimento Automático Usando Kutools para Excel
Método 4: Preenchimento Automático Usando uma Função Definida
Método 4: Preenchimento Automático Usando uma Função Definida
Primeiro de Tudo: Criar uma Lista Suspensa
Antes de implementar qualquer método de preenchimento automático, você precisa ter uma lista suspensa configurada. Essa lista atuará como o gatilho para preencher células relacionadas.
Passos:
Passo 1. Prepare a fonte de dados.
Passo 2. Crie a lista suspensa.
Vá para a célula onde você deseja a lista suspensa (por exemplo, Planilha1!D2)
Navegue até Dados > Validação de Dados > Validação de Dados.
No diálogo de Validação de Dados, escolha Lista na seção Permitir e selecione a fonte de dados. Clique em OK.
Uma vez que sua lista suspensa esteja configurada, você pode prosseguir para implementar qualquer um dos seguintes métodos de preenchimento automático.
Método 1: Preenchimento Automático Usando a Função PROCV
PROCV é uma das funções mais usadas para recuperação de dados no Excel. Quando combinada com uma lista suspensa, ela pode buscar rapidamente dados relacionados de uma tabela de referência.
Passos:
Na célula adjacente à lista suspensa (por exemplo, E2), insira:
🔓 Explicação da fórmula:
- Procura o valor em D2 na primeira coluna de A2:B5. Se encontrado, retorna o valor correspondente da coluna 2 (coluna B). Se não for encontrado, retorna um erro (#N/D).
- FALSO significa que deve ser uma correspondência exata.
Passo 2. Pressione a tecla Enter.
✨ Notas
- Use SEERRO() para ocultar erros se nenhum valor for selecionado:
=PROCV(D2;$A$2:$B$5;2;FALSO) - Não pode procurar à esquerda da coluna-chave.
Método 2: Preenchimento Automático Usando as Funções ÍNDICE e CORRESP
ÍNDICE e CORRESP são uma dupla poderosa que supera o PROCV em flexibilidade. Ele suporta buscas à esquerda e permanece estável mesmo se as colunas forem reorganizadas.
Passos:
Na célula adjacente à lista suspensa (por exemplo, E2), insira:
🔓 Explicação da fórmula:
- CORRESP(D2; $A$2:$A$5; 0)
Procura por D2 no intervalo A2:A5. 0 significa correspondência exata (como FALSO no PROCV).
Retorna a posição (número da linha) onde D2 foi encontrado. - ÍNDICE($B$2:$B$5; ...)
Pega o número da linha do CORRESP.
Retorna o valor correspondente de B2:B5.
Passo 2. Pressione a tecla Enter.
✨ Notas
- Tanto o intervalo de retorno (ÍNDICE) quanto o intervalo de busca (CORRESP) devem estar alinhados linha a linha.
- Pode pesquisar à esquerda ou à direita.
- Mais durável que o PROCV.
Método 3: Preenchimento Automático Usando Kutools para Excel
Kutools oferece uma abordagem baseada em GUI que elimina a necessidade de fórmulas. É especialmente útil para usuários que desejam resultados rápidos sem entrar nas funções do Excel.
Passos:
Passo 1. Na célula adjacente à lista suspensa (por exemplo, E2), vá para Kutools > Assistente de Fórmulas > Pesquisa e Referência > Pesquisar uma lista de valores.
Passo 2. Selecione Intervalo da Tabela, Valor de Pesquisa e o Número da Coluna. Clique em OK.
✨ Notas
- Kutools permite que você aplique isso a todo um intervalo de uma só vez.
- A ferramenta é muito amigável para iniciantes e reduz erros manuais.
- Fácil de usar.
- Não requer fórmulas.
Cansado de tarefas repetitivas e fórmulas complexas no Excel? Kutools para Excel é seu potencializador de produtividade tudo-em-um! Com mais de 300 recursos poderosos—edição em lote, preenchimento inteligente, filtro automático—você trabalhará 10 vezes mais rápido. Baixe agora e leve suas habilidades no Excel para o próximo nível!
Método 4: Preenchimento Automático Usando uma Função Definida
Para usuários que precisam de uma solução dinâmica e automatizada além dos limites das fórmulas, o VBA oferece controle máximo e personalização lógica.
Passos:
Passo 1. Pressione as teclas Alt + F11 para abrir o editor VBA.
Passo 2. Clique em Inserir > Módulo.
Passo 3. Cole o código abaixo no Módulo.
'Update by Extendoffice
Function GetProductInfo(productName As String, colIndex As Integer) As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'the sheet that the data source in
Dim rng As Range
Set rng = ws.Range("A2:B5") 'the range of data source
Dim r As Range
For Each r In rng.Rows
If r.Cells(1, 1).Value = productName Then
GetProductInfo = r.Cells(1, colIndex).Value
Exit Function
End If
Next
GetProductInfo = "Not found"
End Function
Passo 4. Retorne à planilha e na célula adjacente à lista suspensa (por exemplo, E2), insira:
Passo 5. Pressione a tecla Enter.
✨ Notas
- Requer uma pasta de trabalho habilitada para macros (.xlsm)
Perguntas Frequentes
Q1: E se meu intervalo de dados mudar frequentemente?
Use intervalos nomeados ou tabelas dinâmicas para manter as referências.
Q2: Posso usar o PROCV para buscas à esquerda?
Não, considere usar ÍNDICE+CORRESP ou Kutools para esse caso.
Q3: O Kutools é seguro para uso?
Sim, ele é amplamente utilizado e confiável, mas sempre baixe do site oficial.
Q4: O VBA funcionará em todas as versões do Excel?
A maioria das versões desktop oferece suporte, mas ele vem desativado por padrão e não é compatível com o Excel Online.
Q5: O Kutools é gratuito para usar?
O Kutools para Excel não é uma ferramenta totalmente gratuita, mas oferece um teste grátis, seguido por uma opção de compra única:
- Teste grátis de 30 dias com funcionalidade completa—nenhum cartão de crédito necessário.
- Licença perpétua para um único usuário: cerca de US $49, incluindo 2 anos de atualizações e suporte gratuitos.
- Após o período de 2 anos de suporte, você pode continuar usando a versão que possui indefinidamente—apenas sem novas atualizações.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins