Verifique se uma célula contém alguma das palavras de uma lista dinâmica

Question

Eu quero usar uma fórmula para preencher as células "RESULTS". Eu preciso verificar se o "CHECKED CELL" adjacente contém qualquer uma das palavras listadas nas células "KEYWORDS".

O ideal seria poder adicionar novas palavras à lista Palavras-chave à vontade. Então, eu tenho tentado fazer o intervalo algo como "$ A $ 2: $ A".

Exemplo - [C2]: "[B2] contém alguma das palavras em {$ A $ 2: $ A}"?

Eu não consegui encontrar uma maneira de conseguir isso com uma fórmula. Alguma idéia?

[A1] KEYWORDS

[A2] cachorro

[A3] esquilo

[A4] alce

[A5] gato

[B1] CÉLULA VERIFICADA

[B2] alkj alce adfli

[B3] AMSCRAM

[B4] 124 liML

[B5] cachorro LImfo

[C1] RESULTADOS

[C2] sim

[C3] não

[C4] não

[C5] sim

    

Answer 1

Você pode fazer isso com uma fórmula de matriz. Para um intervalo fixo, a fórmula em C2 seria

=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")

Certifique-se de inserir isso como uma fórmula de matriz usando Ctrl + Deslocar + Enter . Você saberá que funcionou quando você colocar chaves {} em qualquer extremidade da fórmula.

Se você quiser que o intervalo seja dinâmico, uma boa maneira é definir um intervalo nomeado. Se você tentar usar toda a coluna A:A , tudo retornará yes , porque as funções SEARCH e FIND retornarão TRUE se a sequência que você está procurando estiver em branco. Como A:A conteria espaços em branco na sua lista, todos os seus resultados serão yes e isso não é útil. Em vez disso, precisamos definir um intervalo nomeado dinâmico. Existem algumas maneiras de fazer isso, mas eu gostaria de abrir o "Gerenciador de nomes" (está no meio da faixa de opções "Fórmulas" na seção "Nomes definidos"). Abra isso e clique em "Novo" no canto superior esquerdo. Dê um nome como Keywords e uma fórmula "Refere-se a"

=OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2)

AgoravocêpodemudarafórmulaemC1paraisto(aindadigitadocomCtrl+Shift+Enter)

=IF(MAX(IFERROR(SEARCH(Keywords,$B2),0))>0,"yes","no")

Advertência # 1

SEARCH não faz distinção entre maiúsculas e minúsculas. Se você quiser uma pesquisa que diferencia maiúsculas de minúsculas, substitua-a por FIND .

Advertência # 2

A fórmula para o intervalo nomeado dinâmico pressupõe que não haja espaços em branco na lista de palavras-chave. Ele fará referência a todas as células de A2 até a célula antes do primeiro espaço em branco. Obviamente, isso também pressupõe que há um espaço em branco em A:A , o que significa que sua lista de palavras-chave precisa ter menos de 1.048.575 itens.

Caveate # 3

Você pode fazer isso sem o intervalo nomeado, mas sua fórmula é longa e difícil de seguir. No entanto, ele mantém tudo empacotado em uma única fórmula. Se seus dados forem muito grandes, eu recomendaria o intervalo nomeado, pois isso acelera os cálculos. Para combinar tudo, a fórmula em C2 seria

=IF(MAX(IFERROR(SEARCH(OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2),$B2),0))>0,"yes","no")

    

Answer 2

Experimente esta pequena função definida pelo usuário (UDF):

Public Function MultiMatch(sIN As String, rng As Range) As String
    Dim r As Range
    MultiMatch = "no"

    For Each r In rng
        If InStr(1, sIN, r.Text) > 0 Then
            MultiMatch = "yes"
            Exit Function
        End If
    Next r
End Function

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

= MultiMatch (B1, $ A $ 1: $ A $ 4)

Para saber mais sobre macros em geral, consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!