Como verificar se uma célula contém um dos vários valores no Excel?
Em muitos cenários empresariais, analíticos ou de revisão de dados, você pode ter uma lista de sequências de texto na Coluna A e querer examinar se cada célula contém algum dos valores especificados, como os exibidos no Intervalo D2:D7. Por exemplo, em pesquisas, registros ou listas de produtos, é fundamental identificar se as entradas incluem palavras-chave relevantes, códigos de produto ou termos proibidos. Se uma célula incluir algum dos itens da sua lista especificada, você pode querer que o Excel retorne Sim; caso contrário, retorne Não, conforme ilustrado na captura de tela abaixo. Este artigo explica maneiras práticas de verificar se uma célula contém um dos vários valores de outro intervalo, apresentando diferentes abordagens conforme a versão do Excel e a necessidade do usuário.
Verifique se uma célula contém um dos vários valores de uma lista usando fórmulas
Para determinar se uma célula contém algum dos valores de texto de outro intervalo, você pode usar uma fórmula matricial que funciona de forma eficiente mesmo para conjuntos de dados grandes. Este método é especialmente útil quando você precisa dos resultados como valores lógicos (Sim/Não), para utilizar em outras fórmulas, filtragens ou testes lógicos. Esta técnica é compatível com a maioria das versões modernas do Excel.
Digite a seguinte fórmula em uma célula em branco (por exemplo, na célula B2, ao lado dos seus dados originais) e depois arraste a alça de preenchimento para baixo para aplicar a outros itens conforme necessário. Se a célula contiver algum dos valores de texto do seu intervalo especificado, ela retorna Sim; caso contrário, retorna Não. Veja a captura de tela:
Dicas e Observações:
- Esta fórmula faz uma verificação que não diferencia maiúsculas de minúsculas. Se você precisar diferenciar maiúsculas de minúsculas, considere utilizar uma coluna auxiliar ou combinar fórmulas de maneira mais avançada.
- Para adaptar o resultado para “Sim” ou “Não” em vez de Sim/Não, utilize a fórmula ajustada abaixo:
- D2:D7 é o seu Intervalo de valores (a “lista”); A2 é a célula que será testada.
- Fique atento a células em branco ou dados não-textuais, pois a função SEARCH exige um texto válido e valores em branco podem gerar resultados inesperados (“Sim”).
Exiba as correspondências se a célula contiver um dos vários valores de uma lista utilizando fórmulas
Em alguns casos, pode ser mais informativo exibir quais valores da lista realmente aparecem em cada célula, em vez de apenas mostrar Sim/Não. Por exemplo, ao analisar descrições de produtos ou comentários em busca de palavras-chave específicas, você pode desejar retornar todos os valores encontrados para análise ou relatório. Você pode usar a fórmula a seguir para mostrar todos os valores correspondentes separados por vírgulas, conforme ilustrado:
Digite esta fórmula em uma célula em branco (exemplo: B2), e ela irá listar todos os valores de D2:D7 encontrados dentro de A2, separados por vírgulas:
Observação: Aqui, D2:D7 é o conjunto de valores a ser pesquisado e A2 é a célula onde será feita a busca.
Após digitar a fórmula, pressione Ctrl + Shift + Enter. Em seguida, arraste a alça de preenchimento para baixo para aplicar a fórmula às demais linhas, como demonstrado na captura de tela do resultado:
- A função TEXTJOIN está disponível apenas no Excel2019 e Office365. Em versões anteriores do Excel, utilize a seguinte fórmula matricial, digitando-a em uma célula em branco e pressionando Ctrl + Shift + Enter:
Arraste a fórmula para a direita para ocupar o máximo de colunas possível e capturar todas as correspondências. Depois, arraste para baixo para cada linha. As colunas extras permanecerão em branco caso haja menos correspondências. Este formato é especialmente útil quando você precisa listar os resultados em colunas separadas:
Se você encontrar erros, confira as áreas selecionadas, certifique-se de que a região D2:D7 está correta e confirme se está usando o delimitador apropriado da sua localidade (vírgula ou ponto e vírgula).
Destaque as correspondências se a célula contiver um dos vários valores de uma lista com um recurso prático
Se você deseja destacar visualmente as palavras-chave ou frases que correspondem a qualquer valor da sua lista dentro de cada célula, isso facilita a identificação dos dados importantes para revisão ou ação. O recurso Marcar Palavras-chave no Kutools para Excel foi desenvolvido para este cenário, permitindo destacar rapidamente qualquer instância das palavras que você especificou no Intervalo de dados, sem precisar criar fórmulas ou código VBA. Em planilhas grandes ou conjuntos complexos de dados, este recurso é especialmente útil quando a inspeção manual não é viável.
Após instalar o Kutools para Excel, siga estes passos:
1. Acesse Kutools > Texto > Marcar Palavras-chave para abrir a caixa de diálogo, conforme mostrado:
2. Na caixa de diálogo Marcar Palavras-chave, realize o seguinte:
- Selecione o intervalo de dados desejado na caixa Intervalo.
- Escolha ou digite manualmente as palavras-chave na caixa Palavra-chave (separadas por vírgula).
- Defina uma cor de destaque usando a opção Cor da Fonte da Palavra-chave.
3. Clique em OK. Todas as palavras encontradas dentro do Intervalo selecionado serão destacadas com a cor de fonte escolhida:
- Este recurso modifica diretamente o formato de exibição das palavras-chave marcadas, tornando mais intuitiva a revisão ou o compartilhamento dos resultados por equipes que não utilizam fórmulas.
- É especialmente eficiente quando sua lista de palavras-chave ou o intervalo de texto é muito grande, ou você precisa destacar vários critérios de uma só vez.
- Sempre revise o intervalo selecionado e as entradas de palavras-chave antes de confirmar para evitar destaques indesejados.
Verifique se uma célula contém um dos vários valores usando Usar Formatação Condicional
O recurso padrão Usar Formatação Condicional do Excel é outra abordagem eficaz para destacar células que contenham qualquer valor da sua lista especificada. Essa solução é útil para identificar facilmente linhas relevantes durante revisões de dados, verificações de erro ou tarefas de conformidade. Ao contrário do recurso Marcar Palavras-chave do Kutools, esta abordagem não exige complementos, tornando-a acessível em instalações padrão do Excel.
Veja como usar Usar Formatação Condicional com uma fórmula:
- Selecione as células do seu Intervalo de dados (exemplo: A2:A20) que você deseja rastrear.
- Acesse a guia Página Inicial, clique em Usar Formatação Condicional > Nova Regra.
- Na caixa de diálogo Nova Regra de Formatação, selecione Usar uma fórmula para determinar quais células serão formatadas.
- Digite esta fórmula, considerando que D2:D7 contém seus valores e A2 é a primeira célula de dados:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Clique em Formatar, defina o formato desejado (como cor de preenchimento) e pressione OK.
Todas as células contendo qualquer item da lista D2:D7 serão destacadas automaticamente.
- Este método é dinâmico: se você atualizar o Intervalo D2:D7, a formatação será ajustada automaticamente.
- Usar Formatação Condicional é apenas para exibição: marca as células visualmente, mas não fornece resultados em coluna separada ou para cálculos adicionais.
- A formatação condicional baseada em fórmula é poderosa, porém, com conjuntos de dados muito grandes, o desempenho pode diminuir devido à computação repetida.
- Observe que SEARCH não diferencia maiúsculas de minúsculas. Para tornar essa abordagem sensível a maiúsculas/minúsculas, são necessárias técnicas mais avançadas ou colunas auxiliares.
Mais artigos relacionados:
- Compare duas ou mais sequências de texto no Excel
- Se você deseja comparar duas ou mais sequências de texto em uma Worksheet e escolher diferenciar maiúsculas de minúsculas ou não, conforme exibido na captura de tela, este artigo apresenta fórmulas úteis para você resolver essa tarefa no Excel.
- Se célula contém texto então exibir no Excel
- Se você possui uma lista de sequências de texto na Coluna A e uma linha de palavras-chave, agora precisa verificar se essas palavras-chave aparecem na sequência de texto. Se a palavra-chave estiver na célula, ela será exibida; caso contrário, a célula fica em branco, como ilustra a captura de tela.
- Contar palavras-chave que a célula contém com base em uma lista
- Se você deseja contar o número de palavras-chave presentes em uma célula baseado em uma lista de células, a combinação das funções SOMA, ISNUMBER e SEARCH pode ajudar a resolver este problema no Excel.
- Localizar e substituir múltiplos valores no Excel
- Normalmente, o recurso Localizar e Substituir pode ajudá-lo a encontrar um texto específico e substituí-lo por outro, mas, às vezes, pode ser necessário localizar e substituir diversos valores ao mesmo tempo. Por exemplo, substituir todo o texto “Excel” por “Excel2019”, “Outlook” por “Outlook2019” e assim por diante, conforme a captura de tela. Neste artigo, apresento uma fórmula para solucionar essa tarefa no Excel.
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