O Filtro do Excel é uma das funcionalidades mais utilizadas quando você trabalha com dados. Nesta postagem do blog, mostrarei como criar uma caixa de pesquisa de filtro dinâmico do Excel, de forma que filtre os dados com base no que você digita na caixa de pesquisa.
Algo conforme mostrado abaixo:
Há uma funcionalidade dupla para isso - você pode selecionar o nome de um país na lista suspensa ou pode inserir manualmente os dados na caixa de pesquisa, e ela mostrará todos os registros correspondentes. Por exemplo, quando você digita “I”, são fornecidos todos os nomes de países com o alfabeto I nele.
Assistir ao vídeo - Criando uma caixa de pesquisa de filtro dinâmico do Excel
Criação de uma caixa de pesquisa de filtro dinâmico do Excel
Este filtro Dynamic Excel pode ser criado em 3 etapas:
- Obter uma lista única de itens (países, neste caso). Isso seria usado na criação do menu suspenso.
- Criando a caixa de pesquisa. Aqui eu usei uma caixa de combinação (controle ActiveX).
- Configurando os dados. Aqui, eu usaria três colunas auxiliares com fórmulas para extrair os dados correspondentes.
Esta é a aparência dos dados brutos:
DICA ÚTIL: quase sempre é uma boa ideia converter seus dados em uma tabela do Excel. Você pode fazer isso selecionando qualquer célula no conjunto de dados e usando o atalho de teclado Control + T.
Etapa 1 - Obter uma lista exclusiva de itens
- Selecione todos os países e cole em uma nova planilha.
- Selecione a lista de países -> Vá para Dados -> Remover Duplicados.
- Na caixa de diálogo Remover Duplicados, selecione a coluna na qual você tem a lista e clique em OK. Isso removerá duplicatas e fornecerá a você uma lista exclusiva, conforme mostrado abaixo:
- Uma etapa adicional é criar um intervalo nomeado para esta lista exclusiva. Para fazer isso:
- Vá para a guia Fórmula -> Definir nome
- Na caixa de diálogo Definir nome:
- Nome: CountryList
- Escopo: Livro de exercícios
- Refere-se a: = UniqueList! $ A $ 2: $ A $ 9 (eu tenho a lista em uma guia separada chamada UniqueList em A2: A9. Você pode consultar o local onde sua lista exclusiva reside)
NOTA: Se você usar o método ‘Remover Duplicados’ e expandir seus dados para adicionar mais registros e novos países, você terá que repetir esta etapa novamente. Como alternativa, você também pode criar uma fórmula para tornar esse processo dinâmico.
Etapa 2 - Criação da caixa de pesquisa de filtro dinâmico do Excel
Para que essa técnica funcione, precisaríamos criar uma "caixa de pesquisa" e vinculá-la a uma célula.
Podemos usar a caixa de combinação no Excel para criar este filtro de caixa de pesquisa. Dessa forma, sempre que você inserir algo na caixa de combinação, também será refletido em uma célula em tempo real (conforme mostrado abaixo).
Aqui estão as etapas para fazer isso:
- Vá para a guia Desenvolvedor -> Controles -> Inserir -> Controles ActiveX -> Caixa de combinação (controles ActiveX).
- Se você não tiver a guia Desenvolvedor visível, aqui estão as etapas para ativá-la.
- Se você não tiver a guia Desenvolvedor visível, aqui estão as etapas para ativá-la.
- Clique em qualquer lugar da planilha. Isso irá inserir a caixa de combinação.
- Clique com o botão direito na caixa de combinação e selecione Propriedades.
- Na janela Propriedades, faça as seguintes alterações:
- Célula vinculada: K2 (você pode escolher qualquer célula onde deseja mostrar os valores de entrada. Estaremos usando esta célula na configuração dos dados).
- ListFillRange: CountryList (este é o intervalo nomeado que criamos na Etapa 1. Isso mostraria todos os países no menu suspenso).
- MatchEntry: 2-fmMatchEntryNone (isso garante que uma palavra não seja completada automaticamente enquanto você digita)
- Com a Caixa de combinação selecionada, vá para a guia Desenvolvedor -> Controles -> Clique em Modo de design (isso tira você do modo de design e agora você pode digitar qualquer coisa na caixa de combinação. Agora, tudo o que você digitar será refletido na célula K2 em tempo real)
Etapa 3 - Definindo os dados
Por fim, vinculamos tudo por colunas auxiliares. Eu uso três colunas auxiliares aqui para filtrar os dados.
Coluna auxiliar 1: Insira o número de série para todos os registros (20 neste caso). Você pode usar a fórmula ROWS () para fazer isso.
Coluna auxiliar 2: Na coluna auxiliar 2, verificamos se o texto inserido na caixa de pesquisa corresponde ao texto nas células da coluna do país.
Isso pode ser feito usando uma combinação das funções IF, ISNUMBER e SEARCH.
Aqui está a fórmula:
= SE (ISNUMBER (SEARCH ($ K $ 2, D4)), E4, "")
Esta fórmula irá pesquisar o conteúdo na caixa de pesquisa (que está ligada à célula K2) na célula que contém o nome do país.
Se houver uma correspondência, esta fórmula retorna o número da linha, caso contrário, retorna um espaço em branco. Por exemplo, se a caixa de combinação tiver o valor ‘US’, todos os registros com o país como ‘US’ terão o número da linha e o restante ficará em branco (“”)
Coluna auxiliar 3: Na coluna auxiliar 3, precisamos obter todos os números das linhas da coluna auxiliar 2 empilhados. Para fazer isso, podemos usar uma combinação das fórmulas IFERROR e SMALL. Aqui está a fórmula:
= IFERROR (PEQUENO ($ F $ 4: $ F $ 23, E4), "")
Esta fórmula empilha todos os números de linha correspondentes. Por exemplo, se a caixa de combinação tiver o valor US, todos os números de linha com ‘US’ serão empilhados.
Agora, quando temos os números das linhas empilhados, só precisamos extrair os dados desse número. Isso pode ser feito facilmente usando a fórmula do índice (insira esta fórmula onde deseja extrair os dados. Copie-a na célula superior esquerda onde deseja que os dados sejam extraídos e arraste-a para baixo e para a direita).
= IFERROR (ÍNDICE ($ B $ 4: $ D $ 23, $ G4, COLUNAS ($ I $ 3: I3)), "")
Esta fórmula tem 2 partes:
ÍNDICE - Extrai os dados com base no número da linha.
IFERROR - Retorna em branco quando não há dados.
Aqui está um instantâneo do que você finalmente consegue:
A caixa de combinação é tanto uma lista suspensa quanto uma caixa de pesquisa. Você pode ocultar os dados originais e as colunas auxiliares para mostrar apenas os registros filtrados. Você também pode ter os dados brutos e as colunas auxiliares em alguma outra planilha e criar esse filtro dinâmico do Excel em outra planilha.
Seja criativo! Experimente algumas variações
Você pode experimentar e personalizá-lo de acordo com seus requisitos. Você pode querer criar vários filtros do Excel em vez de um. Por exemplo, você pode desejar filtrar registros em que o representante de vendas é Mike e o país é o Japão. Isso pode ser feito exatamente seguindo as mesmas etapas, com algumas modificações na fórmula nas colunas auxiliares.
Outra variação pode ser filtrar dados que começam com os caracteres inseridos na caixa de combinação. Por exemplo, quando você insere 'I', você pode querer extrair países começando com I (em comparação com a construção atual onde também forneceria Cingapura e Filipinas, pois contém o alfabeto I).
Como sempre, a maioria dos meus artigos é inspirada nas perguntas / respostas dos meus leitores. Eu adoraria receber seus comentários e aprender com você. Deixe sua opinião na seção de comentários.
Observação: caso esteja usando o Office 365, você pode usar a função FILTRO para filtrar rapidamente os dados conforme você digita. É mais fácil do que o método mostrado neste tutorial.
