Assistir ao vídeo - Extrair dados usando uma lista suspensa no Excel
Neste tutorial, mostrarei como criar um filtro suspenso no Excel para que você possa extrair dados com base na seleção do menu suspenso.
Conforme mostrado na foto abaixo, criei uma lista suspensa com nomes de países. Assim que seleciono qualquer país no menu suspenso, os dados desse país são extraídos à direita.
Observe que, assim que seleciono Índia no filtro suspenso, todos os registros da Índia são extraídos.
Extrair dados da seleção da lista suspensa no Excel
Aqui estão as etapas para criar um filtro suspenso que extrairá os dados do item selecionado:
- Crie uma lista única de itens.
- Adicione um filtro suspenso para exibir esses itens exclusivos.
- Use colunas auxiliares para extrair os registros do item selecionado.
Vamos mergulhar fundo e ver o que precisa ser feito em cada uma dessas etapas.
Crie uma lista única de itens
Embora possa haver repetições de um item em seu conjunto de dados, precisamos de nomes de item exclusivos para que possamos criar um filtro suspenso usando-o.
No exemplo acima, a primeira etapa é obter a lista exclusiva de todos os países.
Aqui estão as etapas para obter uma lista exclusiva:
- Selecione todos os países e cole em alguma outra parte da planilha.
- Vá para Dados -> Remover Duplicados.
- Na caixa de diálogo Remover Duplicados, selecione a coluna onde você tem a lista de países. Isso lhe dará uma lista exclusiva, conforme mostrado abaixo.
Agora usaremos essa lista exclusiva para criar a lista suspensa.
Veja também: O guia definitivo para localizar e remover duplicatas no Excel.
Criação do filtro suspenso
Aqui estão as etapas para criar uma lista suspensa em uma célula:
- Vá para Dados -> Validação de dados.
- Na caixa de diálogo Validação de dados, selecione a guia Configurações.
- Na guia Configurações, selecione "Lista" no menu suspenso e, no campo "Origem", selecione a lista exclusiva de países que geramos.
- Clique OK.
O objetivo agora é selecionar qualquer país da lista suspensa, e isso deve nos dar a lista de registros para o país.
Para fazer isso, precisaríamos usar colunas e fórmulas auxiliares.
Crie colunas auxiliares para extrair os registros do item selecionado
Assim que você fizer a seleção no menu suspenso, precisará do Excel para identificar automaticamente os registros que pertencem ao item selecionado.
Isso pode ser feito usando três colunas auxiliares.
Aqui estão as etapas para criar colunas auxiliares:
- Coluna auxiliar # 1 - Insira o número de série para todos os registros (20, neste caso, você pode usar a função ROWS () para fazer isso).
- Coluna auxiliar # 2 - Use esta função de função IF simples: = IF (D4 = $ H $ 2, E4, ””)
- Esta fórmula verifica se o país na primeira linha corresponde ao do menu suspenso. Portanto, se eu selecionar Índia, ele verifica se a primeira linha tem Índia como país ou não. Se for True, ele retorna o número da linha, caso contrário, retorna em branco (“”). Agora, quando selecionamos qualquer país, apenas os números das linhas são exibidos (na segunda coluna auxiliar) que contém o país selecionado. (Por exemplo, se a Índia for selecionada, será semelhante à foto abaixo).
Agora precisamos extrair os dados apenas para essas linhas, que exibe o número (pois é a linha que contém aquele país). No entanto, queremos esses registros sem os espaços em branco, um após o outro. Isso pode ser feito usando uma terceira coluna auxiliar
- Terceira Coluna Auxiliar - Use a seguinte combinação de funções IFERROR e SMALL:
= IFERROR (PEQUENO ($ F $ 4: $ F $ 23, E4), ””)
Isso nos daria algo como mostrado abaixo na foto:
Agora, quando temos o número juntos, só precisamos extrair os dados desse número. Isso pode ser feito facilmente usando a função INDEX (use esta fórmula nas células onde você precisa que o resultado seja extraído):
= IFERROR (ÍNDICE ($ B $ 4: $ D $ 23, $ G4, COLUNAS ($ J $ 3: J3)), ””)
Esta fórmula tem 2 partes:
ÍNDICE - Extrai os dados com base no número da linha
IFERROR - Esta função retorna em branco quando não há dados
Aqui está um instantâneo do que você finalmente consegue:
Agora você pode ocultar os dados originais, se desejar. Além disso, você pode ter os dados originais e os dados extraídos em duas planilhas diferentes.
Vá em frente. use essa técnica e impressione seu chefe e colegas (um pouco de exibicionismo nunca é uma coisa ruim).
Baixe o arquivo de exemplo
Você gostou do tutorial? Deixe-me saber sua opinião na seção de comentários.
Você também pode achar úteis os seguintes tutoriais:
- Filtro Dinâmico do Excel - Extraia dados conforme você digita.
- Pesquisa dinâmica no Excel usando formatação condicional.
- Crie uma lista suspensa dinâmica com sugestões de pesquisa.
- Como extrair uma substring no Excel usando fórmulas.
- Como filtrar células com formatação de fonte em negrito no Excel.
