procurando por um conjunto de cadeias no Excel

Question

Eu tenho um conjunto de termos e um conjunto de strings que podem ou não conter um ou mais desses termos. Gostaria de pesquisar se há correspondência para um ou mais termos.

Termos de pesquisa (células A1:A17 no exemplo abaixo)

* bob  
* linda  
* sam 

Cadeias de pesquisa (coluna E no exemplo abaixo)

* I'm sam  
* I'm lucy  
* Please find sam or bob or lucy

Atualmente, estou usando uma função OR com várias funções de pesquisa aninhadas. Por exemplo: =IF(OR(ISNUMBER(SEARCH(A$2,E33)),ISNUMBER(SEARCH(A$3,E33)),....,ISNUMBER(SEARCH(A$17,E33))),1,0)

Eu prefiro alguma maneira de usar o equivalente da substring SQL IN (1,2,3,4)

    

Answer 1

Bem, você entende como pesquisar uma palavra em uma string:

SEARCH(A$2, E1)

pesquisa o valor de A2 ("bob") em E1 (que pode ser algo como "Sou sam"). Isso retorna #VALUE , pois "bob" não aparece em "Sou sam". No entanto,

SEARCH("sam", "I'm sam")

retorna 5 , porque "sam" aparece em "Sou sam", começando no quinto caractere.

Você está pedindo o truque para dimensionar isso para um grande número de sequências de pesquisa. O truque é usar matriz de fórmulas . Uma fórmula de matriz parece um pouco assim:

=SEARCH(A$2:A$17, E1)

que pesquisa E1 para cada dos valores em A2:A17 , criando uma matriz de 16 resultados. Para os dados de exemplo, esses resultados parecem { #VALUE ; %código%; %código%; …}.

See important note at the bottom of this answer.

Você não pode usar isso sozinho, porque uma célula precisa avaliar um valor, não uma matriz. Mas você pode fazer algo parecido com

=COUNT(SEARCH(A$2:A$17, E1))

que conta os números no resultado #VALUE - isto é, diz quantas das palavras 5 ("bob", "linda", "sam", etc.) foram encontradas em SEARCH . Então, seu

=IF( OR(ISNUMBER(SEARCH(A$2,E1)),…,ISNUMBER(SEARCH(A$17,E1))), 1, 0)

A fórmula

( A se pelo menos uma palavra foi encontrada, E1 caso contrário) pode ser substituída por

=IF(COUNT(SEARCH(A$2:A$17, E1))>0, 1, 0)

ou simplesmente

=--(COUNT(SEARCH(A$2:A$17, $E1))>0)

(Colocar 1 (duplo menos) na frente de um valor 0 / -- transforma em TRUE ou FALSE , respectivamente.)

Mas você provavelmente quer fazer mais do que apenas determinar se corresponde a uma das sequências de caracteres; você provavelmente quer saber qual um. Bem, lembre-se quando eu disse isso 1 avaliado para o array { 0 ; %código%; %código%; …} Vamos extrair o SEARCH(A$2:A$17, E1) disso:

 =MIN(IFERROR(SEARCH(A$2:A$17, $E1), ""))

• #VALUE é um pouco como #VALUE , exceto o contrário; ele retorna 5 se o parâmetro for um valor de erro e 5 se for um valor normal (número, booleano, data ou string).

• ISERROR é uma função muito conveniente;

  IFERROR(calculated_value, default_value)

  é curto para

  IF(ISERROR(calculated_value), default_value, calculated_value)

  isto é, ele substitui um valor padrão (normalmente um valor de non -error) por um valor de erro.

• Portanto, ISNUMBER altera o resultado da matriz acima para { TRUE ; %código%; %código%; …}.
• E então FALSE extrai o menor número do array, pulando as cordas vazias. Normalmente, esse será o número somente , exceto em casos como "Por favor, encontrar sam ou bob ou lucy", onde existem várias correspondências. Nesses casos, será a primeira correspondência na string IFERROR (aqui, IFERROR(SEARCH(A$2:A$17, $E1), "") , onde “sam” aparece).

Então, agora faça

=MATCH(MIN(IFERROR(SEARCH(A$2:A$17, E1), "")), SEARCH(A$2:A$17,E1))

Isso ( "" ) procura "" (ou 5 ou qualquer outro) nos resultados completos da pesquisa para ver onde isso ocorre. Nos exemplos acima, isso retorna MIN( the_above ) , porque o E (ou 13 ou qualquer outro) é o terceiro elemento no resultado da pesquisa - porque corresponde a "sam", que é a terceira palavra de pesquisa.

Na imagem abaixo,

• MATCH (número de correspondências) = 5
• 13 (localização da primeira correspondência) = 3

• 5 (index (número da string) da primeira correspondência) =
  13

  

Notasimportantes:

• Quandovocêdigitaumafórmuladematriz(ouseja,qualquerumadasfórmulasdiscutidasacima),digite-odigitandoCtrl+Deslocar+Enter.Issofarácomqueapareçacomchavesemtornodele.Nãodigiteaschavesmanualmente.
• Asfórmulasacimaassumemqueseusdadosaserempesquisados("eu sou sam", etc.) iniciam na célula F1 . Ajuste conforme necessário. Insira as fórmulas correspondentes à primeira linha de seus dados e arraste para baixo.
• Evite células vazias em =COUNT(SEARCH(A$2:A$17, E1)) , porque G1 encontrará a string vazia em todas as strings.

Answer 2

Eu respondi uma pergunta muito semelhante a isso e criei uma demonstração de trabalho usando o complemento de consulta de energia.

Aqui está a pergunta anterior & resposta:

Posso separar uma lista por palavras-chave?