Note: The other languages of the website are Google-translated. Back to English

Como verificar se a célula contém um dos vários valores no Excel?

Supondo que você tenha uma lista de strings de texto na coluna A, agora, você deseja testar cada célula se ela contém um dos vários valores com base em outro intervalo D2: D7. Se ele contiver algum texto específico em D2: D7, exibirá True, caso contrário, exibirá False conforme a captura de tela a seguir. Neste artigo, falarei sobre como identificar uma célula se ela contiver um dos vários valores em outro intervalo.


Verifique se uma célula contém um dos vários valores de uma lista com fórmulas

Para verificar se o conteúdo de uma célula contém algum dos valores de texto em outro intervalo, as seguintes fórmulas podem ajudá-lo, faça o seguinte:

Insira a fórmula abaixo em uma célula em branco onde deseja localizar o resultado, B2, por exemplo, em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula e se a célula tiver algum dos valores de texto em outro intervalo específico, se tornará True, caso contrário, se tornará False. Veja a imagem:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0

Dicas:

1. Se desejar usar “Sim” ou “Não” para indicar o resultado, aplique a seguinte fórmula e obterá o seguinte resultado conforme necessário, consulte a captura de tela:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

2. Nas fórmulas acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.


Mostra as correspondências se a célula contiver um dos vários valores de uma lista com fórmulas

Às vezes, você pode querer verificar se uma célula contém um valor na lista e, em seguida, retornar esse valor; se vários valores corresponderem, todos os valores correspondentes na lista serão exibidos conforme a captura de tela abaixo. Como você poderia resolver essa tarefa no Excel?

Para exibir todos os valores correspondentes se a célula contiver um do texto específico, aplique a fórmula abaixo:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

Nota: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula, veja a captura de tela:

Dicas:

A função TEXTJOIN acima está disponível apenas para Excel 2019 e Office 365, se você tiver versões anteriores do Excel, deve aplicar a fórmula abaixo:

=IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")

Nota: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a célula da fórmula para o lado direito até que a célula em branco seja exibida e, em seguida, continue arrastando a alça de preenchimento para baixo para outras células, e todos os valores correspondentes foram exibidos conforme a captura de tela abaixo:


Destaque as correspondências se a célula contiver um dos vários valores de uma lista com um recurso útil

Se você quiser destacar uma cor de fonte específica para os valores correspondentes, se a célula contiver um dos vários valores de outra lista, nesta seção, apresentarei um recurso fácil, Marcar palavra-chave of Kutools for Excel, com esse utilitário, você pode destacar uma ou mais palavras-chave específicas de uma vez dentro das células.

NOTAPara aplicar estes Marcar palavra-chave recursos, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique os recursos de forma rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique Kutools > Texto > Marcar palavra-chave, veja a captura de tela:

2. No Marcar palavra-chave caixa de diálogo, execute as seguintes operações:

  • Selecione o intervalo de dados que você deseja destacar os textos correspondentes do Variação caixa de texto;
  • Selecione as células que contêm as palavras-chave que você deseja realçar com base, você também pode inserir as palavras-chave manualmente (separadas por vírgula) no Palavra-chave caixa de texto
  • Por fim, você deve especificar uma cor de fonte para destacar os textos por cheque Marque as cores da palavra-chave opção. 

3. Então clique Ok botão, todos os textos correspondentes foram destacados como a imagem abaixo mostrada:


Mais artigos relativos:

  • Compare duas ou mais strings de texto no Excel
  • Se você quiser comparar duas ou mais strings de texto em uma planilha com ou sem distinção entre maiúsculas e minúsculas, conforme a captura de tela a seguir mostrada, neste artigo, falarei sobre algumas fórmulas úteis para você lidar com essa tarefa no Excel.
  • Se a célula contiver texto, será exibida no Excel
  • Se você tiver uma lista de sequências de texto na coluna A e uma linha de palavras-chave, agora, você precisa verificar se as palavras-chave aparecem na sequência de texto. Se as palavras-chave aparecem na célula, exibindo-a, caso contrário, a célula em branco é exibida conforme a captura de tela a seguir.
  • Encontre e substitua vários valores no Excel
  • Normalmente, o recurso Localizar e Substituir pode ajudá-lo a localizar um texto específico e substituí-lo por outro, mas, às vezes, você pode precisar localizar e substituir vários valores simultaneamente. Por exemplo, para substituir todo o texto "Excel" por "Excel 2019", "Outlook" por "Outlook2019" e assim por diante, conforme a captura de tela abaixo mostrada. Neste artigo, vou apresentar uma fórmula para resolver essa tarefa no Excel.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (50)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
E se a coluna "texto específico" contiver frases? Por exemplo "remover duplicado" "converter texto" "encontrar" como verificar então?
Este comentário foi feito pelo moderador no site
Em vez de True ou False quero imprimir o nome do texto encontrado, como posso fazer isso?
Este comentário foi feito pelo moderador no site
verifique a fórmula dada por skyyang... Isso funciona bem
Este comentário foi feito pelo moderador no site
Fórmula muito útil.
Podemos pegar esse valor encontrado na pesquisa? (ou seja, quero verificar se o campo de endereço contém o código postal de 4 dígitos dentro do intervalo, se sim, mostre o valor do código postal de 4 dígitos.)
David
Este comentário foi feito pelo moderador no site
Olá David,
Minha fórmula de matriz pode ajudá-lo:
=IF(SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))>0,INDEX($D$2:$D$4,SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))))
Depois de colar a fórmula, pressione as teclas Shift + Ctrl + Enter juntas.
Esta fórmula é um pouco complexa, por favor altere as referências das células conforme sua necessidade.
Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Incrível, funciona exatamente do jeito que eu precisava. Obrigado campeões.
Este comentário foi feito pelo moderador no site
Feliz em te ajudar!
Este comentário foi feito pelo moderador no site
oi, você poderia explicar como você conseguiu essa função acima? Estou tentando fazer a mesma coisa, mas não consigo alterar as referências de célula corretamente.
Este comentário foi feito pelo moderador no site
Sim, por favor, eu também preciso disso. Não consigo ver porque $D$2 está bloqueado?
Este comentário foi feito pelo moderador no site
Isso é incrível! Um grande problema para mim, no entanto: e se houver células em branco na minha lista "procurar" D2:D4. Para mim, isso estraga a fórmula e eu apenas recebo "NUM" em alguns lugares. OBRIGADO!
Este comentário foi feito pelo moderador no site
essa fórmula funciona quando há algumas palavras nas células de pesquisa e algumas palavras na pesquisa nas células? basicamente eu tenho listas de ingredientes e quero localizar algum ingrediente específico dentro dessa lista e o ingrediente específico pode ser mais de uma palavra
Este comentário foi feito pelo moderador no site
eu tenho uma lista de ingredientes na coluna A, cada célula tem todos os ingredientes de um produto, então não é apenas uma palavra, na coluna D eu tenho uma lista de ingredientes que estou tentando encontrar na coluna A, como faço isso? esta fórmula não parece funcionar 100% para isso
obrigado
Este comentário foi feito pelo moderador no site
Eu e outra garota estamos tentando resolver isso há 2 dias para um problema de dados que precisamos resolver. Obrigado por esta expressão. É exatamente o que precisávamos.
Este comentário foi feito pelo moderador no site
A lista na coluna D pode ter 15000 palavras e ainda assim a fórmula permanece a mesma mencionada acima? Estou obtendo resultados esperados com apenas alguns valores, mas no momento em que a lista salta para milhares, tudo mostra #REF.
Este comentário foi feito pelo moderador no site
skyyang,

Não sei se você vai ler isso, mas muito obrigado!

Você é um gênio!!!
Este comentário foi feito pelo moderador no site
Como eu faria isso funcionar se houvesse células em branco na coluna D?
Este comentário foi feito pelo moderador no site
hi
veja o arquivo anexo, estou tentando descobrir se algum dos ingredientes da coluna D existe na coluna A e exibir qualquer ingrediente encontrado na coluna B, isso é possível?
Este comentário foi feito pelo moderador no site
o primeiro anexo não foi carregado, veja o anexo abaixo
Este comentário foi feito pelo moderador no site
감사합니다 정말 진심으로 감사드립니다
한달 여간 을 에서 만 찾다가 구글 을 통해 찾으니 이렇게 바로 정확 한 정보 가 뜨네 요 정말 감사 드립니다!
Este comentário foi feito pelo moderador no site
Obrigado heaps, isso funcionou.
Este comentário foi feito pelo moderador no site
유용한 자료 감사합니다!
Este comentário foi feito pelo moderador no site
usado =SUMPRODUCT(--ISNUMBER(SEARCH(list,A2))) junto com a formatação condicional para ajudar a comparar os valores de uma coluna com outra. funciona muito bem com o Excel 2007. Estou quebrando a cabeça há dias.
Este comentário foi feito pelo moderador no site
SIM, NÃO 가아닌 메칭되는 값(KTE,remove) 들어있는 값이 보이게 넣을수는 없나요?

중복되는 값 배열일 경우 배열로 B열 C열......... 기재는 가능한가요
Este comentário foi feito pelo moderador no site
Alguma maneira de retornar o texto que corresponde à célula em vez de um sim ou não?
Este comentário foi feito pelo moderador no site
Mesma questão; Podemos retornar o texto que foi correspondido em vez de sim/não?
Este comentário foi feito pelo moderador no site
Seria possível alterar o resultado desta fórmula de apenas 'TRUE' , 'FALSE', 'Yes' ou 'No', para o valor do texto específico?


Obrigado
Este comentário foi feito pelo moderador no site
Olá, Nils,
Se você quiser usar outro texto em vez do "Sim" ou "Não", basta substituir o "Sim", "Não" por outros textos, tente a fórmula abaixo:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"seu texto específico1","seu texto específico2")

Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Jane perguntou abaixo "Mesma pergunta; Podemos retornar o texto que foi correspondido em vez de sim/não?" - também interessado na resposta
Este comentário foi feito pelo moderador no site
=SE(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Sim,"Não")
Este comentário foi feito pelo moderador no site
Ei,
Eu tentei esta fórmula, mas está mostrando TRUE para tudo?
Este comentário foi feito pelo moderador no site
Por favor, consulte a
Este comentário foi feito pelo moderador no site
A fórmula está incorreta. Está mostrando "true" para qualquer valor
Este comentário foi feito pelo moderador no site
Olá, Dd,
A fórmula funciona bem na minha planilha do Excel, você pode fornecer sua planilha de problemas como captura de tela aqui?
Obrigado!
Este comentário foi feito pelo moderador no site
ei, eu tenho o mesmo problema. Ele retorna verdadeiro todas as vezes, mesmo quando deve ser falso. Esta é a fórmula:
=SOMAPRODUTO(--ÉNÚMERO(PESQUISA($AH$2:$AH$122;C2)))>0

$AH$2:$AH$12 é a lista de palavras
C2 é um texto de um tweet.

retorna verdadeiro mesmo que o tweet não contenha nenhuma das palavras da lista
Este comentário foi feito pelo moderador no site
Exatamente o que eu estava procurando! funciona perfeitamente
Este comentário foi feito pelo moderador no site
Perfeito!! Demanda complexa acompanhada pela combinação de poucas fórmulas conhecidas
Este comentário foi feito pelo moderador no site
Isso tem sido muito útil, mas estou apenas perdendo a parte final.
Como expandir IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No") para também verificar a diferenciação entre maiúsculas e minúsculas dos valores na lista?
Obrigado antecipadamente
Este comentário foi feito pelo moderador no site
Olá, Scott,
Para verificar os valores com distinção entre maiúsculas e minúsculas, aplique a fórmula abaixo:
=SE(SUMPRODUCT(--ISNUMBER(FIND($D$2:$D$6,A2))),"Sim","Não")

Por favor, tente, obrigado!
Este comentário foi feito pelo moderador no site
Oi - E se eu precisar pesquisar a linha pelas palavras-chave em vez de apenas a célula, ou seja, A2 - P2 em vez de apenas A2 nesta fórmula? =SE(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Sim","Não")
Este comentário foi feito pelo moderador no site
Olá, Margo,
Para aplicar esta fórmula aos dados de uma linha, você só precisa substituir a referência de célula única pela referência de linha conforme abaixo:
=SE(SUMPRODUCT(--ISNUMBER(SEARCH($R$2:$R$6,A2:P2))),"Sim","Não")
Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
É possível fazer isso com uma correspondência parcial das palavras-chave? Este método é apenas uma correspondência exata
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL