Assistir ao vídeo - Pesquisar e destacar dados usando formatação condicional
Se você trabalha com grandes conjuntos de dados, pode haver necessidade de criar uma funcionalidade de pesquisa que permite destacar rapidamente células / linhas para o termo pesquisado.
Embora não haja uma maneira direta de fazer isso no Excel, você pode criar a funcionalidade de pesquisa usando a Formatação Condicional.
Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo (na imagem). Possui colunas para Nome do produto, Representante de vendas e País.
Agora você pode usar a formatação condicional para pesquisar uma palavra-chave (inserindo-a na célula C2) e destacar todas as células que contêm essa palavra-chave.
Algo como mostrado abaixo (onde eu insiro o nome do item na célula B2 e pressiono Enter, toda a linha é destacada):
Neste tutorial, vou mostrar como criar essa pesquisa e destacar a funcionalidade no Excel.
Posteriormente no tutorial, iremos um pouco mais adiante e veremos como torná-lo dinâmico (para que seja realçado enquanto você digita na caixa de pesquisa).
Clique aqui para baixar o arquivo de exemplo e acompanhe.
Pesquisar e destacar células correspondentes
Nesta secção. Vou mostrar como pesquisar e destacar apenas as células correspondentes em um conjunto de dados.
Algo conforme mostrado abaixo:
Aqui estão as etapas para pesquisar e destacar todas as células que possuem o texto correspondente:
- Selecione o conjunto de dados ao qual deseja aplicar a Formatação Condicional (A4: F19 neste exemplo).
- Clique na guia Página inicial.
- No grupo Estilos, clique em Formatação Condicional.
- Nas opções suspensas, clique em Nova Regra.
- Na caixa de diálogo ‘Nova regra de formatação’, clique na opção ‘Usar uma fórmula para determinar quais células formatar’.
- Insira a seguinte fórmula: = A4 = $ B $ 1
- Clique no botão ‘Formatar…’.
- Especifique a formatação (para destacar as células que correspondem à palavra-chave pesquisada).
- Clique OK.
Agora digite qualquer coisa na célula B1 e pressione Enter. Ele irá destacar as células correspondentes no conjunto de dados que contêm a palavra-chave em B1.
Como é que isso funciona?
A Formatação Condicional é aplicada sempre que a fórmula especificada retorna TRUE.
No exemplo acima, verificamos cada célula usando a fórmula = A4 = $ B $ 1
A Formatação Condicional verifica cada célula e verifica se o conteúdo da célula é igual ao da célula B1. Se for o mesmo, a fórmula retorna TRUE e a célula é destacada. Se não for o mesmo, a fórmula retorna FALSE e nada acontece.
Clique aqui para baixar o arquivo de exemplo e acompanhe.
Pesquisar e destacar linhas com dados correspondentes
Se quiser destacar a linha inteira em vez de apenas as células correspondentes, você pode fazer isso ajustando um pouco a fórmula.
Abaixo está um exemplo em que toda a linha é destacada se o tipo de produto corresponder ao da célula B1.
Aqui estão as etapas para pesquisar e destacar a linha inteira:
- Selecione o conjunto de dados ao qual deseja aplicar a Formatação Condicional (A4: F19 neste exemplo).
- Clique na guia Página inicial.
- No grupo Estilos, clique em Formatação Condicional.
- Nas opções suspensas, clique em Nova Regra.
- Na caixa de diálogo ‘Nova regra de formatação’, clique na opção ‘Usar uma fórmula para determinar quais células formatar’.
- Insira a seguinte fórmula: = $ B4 = $ B $ 1
- Clique no botão ‘Formatar…’.
- Especifique a formatação (para destacar as células que correspondem à palavra-chave pesquisada).
- Clique OK.
As etapas acima procurariam o item especificado no conjunto de dados e, se encontrar o item correspondente, destacará a linha inteira.
Observe que isso só verificará a coluna do item. Se você inserir o nome de um representante de vendas aqui, não funcionará. Se você quiser que funcione para o nome do representante de vendas, será necessário alterar a fórmula para = $ C4 = $ B $ 1
Nota: O motivo pelo qual ele destaca toda a linha e não apenas a célula correspondente é que usamos um sinal $ antes da referência de coluna ($ B4). Agora, quando a formatação condicional analisa células em uma linha, ela verifica se o valor na coluna B dessa linha é igual ao valor na célula B1. Portanto, mesmo quando está analisando A4 ou B4 ou C4 e assim por diante, está verificando apenas o valor B4 (já que bloqueamos a coluna B usando o cifrão).
Você pode ler mais sobre referências absolutas, relativas e mistas aqui.
Pesquisar e destacar linhas (com base na correspondência parcial)
Em alguns casos, você pode querer destacar as linhas com base em uma correspondência parcial.
Por exemplo, se você tem itens como Quadro Branco, Quadro Verde e Quadro Cinza, e deseja destacar todos eles com base na palavra Quadro, você pode fazer isso usando a função PESQUISAR.
Algo conforme mostrado abaixo:
Aqui estão as etapas para fazer isso:
- Selecione o conjunto de dados ao qual deseja aplicar a Formatação Condicional (A4: F19 neste exemplo).
- Clique na guia Página inicial.
- No grupo Estilos, clique em Formatação Condicional.
- Nas opções suspensas, clique em Nova Regra.
- Na caixa de diálogo ‘Nova regra de formatação’, clique na opção ‘Usar uma fórmula para determinar quais células formatar’.
- Insira a seguinte fórmula: = AND ($ B $ 1 ””, ISNUMBER (SEARCH ($ B $ 1, $ B4)))
- Clique no botão ‘Formatar…’.
- Especifique a formatação (para destacar as células que correspondem à palavra-chave pesquisada).
- Clique OK.
Como é que isso funciona?
- A função SEARCH procura a string de pesquisa / palavra-chave em todas as células em uma linha. Ele retorna um erro se a palavra-chave da pesquisa não for encontrada e um número se encontrar uma correspondência.
- A função ISNUMBER converte o erro em FALSE e os valores numéricos em TRUE.
- A função AND verifica uma condição adicional - que a célula C2 não deve estar vazia.
Portanto, agora, sempre que você digita uma palavra-chave na célula B1 e pressiona Enter, ela destaca todas as linhas que contêm as células que contêm essa palavra-chave.
Dica bônus: Se você quiser que a pesquisa faça distinção entre maiúsculas e minúsculas, use a função FIND em vez de SEARCH.
Clique aqui para baixar o arquivo de exemplo e acompanhe.
Pesquisa dinâmica e funcionalidade de destaque (destaques conforme você digita)
Usando os mesmos truques de formatação condicional descritos acima, você também pode dar um passo adiante e torná-la dinâmica.
Por exemplo, você pode criar uma barra de pesquisa onde os dados correspondentes são destacados conforme você digita na barra de pesquisa.
Algo conforme mostrado abaixo:
Isso pode ser feito usando controles ActiveX e pode ser uma boa funcionalidade a ser usada ao criar relatórios ou painéis.
Abaixo está um vídeo onde mostro como criar isso:
Você achou este tutorial útil? Deixe-me saber sua opinião na seção de comentários.
Você também pode gostar dos seguintes tutoriais do Excel:
- Filtro Dinâmico do Excel - Extrai dados conforme você digita.
- Crie uma lista suspensa com sugestões de pesquisa.
- Criando um Mapa de Calor no Excel.
- Realce linhas com base em um valor de célula no Excel.
- Destaque a linha e a coluna ativas em um intervalo de dados no Excel.
- Como destacar células em branco no Excel.
