Encontre palavras-chave em parágrafos de texto (no Excel)

Question

Edit: Todas as respostas funcionam muito bem, mas para um grande conjunto de dados a abordagem macro funcionou melhor. Experimente todos eles e veja o que funciona melhor para você.

Estou tentando resolver esse problema no Excel para desempenho (fiz isso em R, mas está lento). Basicamente, eu preciso combinar cada palavra-chave (de uma lista de palavras-chave) com o texto (essencialmente parágrafos) em uma coluna. Aqui está uma ilustração:

Os dados acima estão na origem desta questão em forma de texto, adequado para copiar e colar.

Eu tenho procurado algumas funções de texto (como FIND e SEARCH, mas elas só retornam posições), então não tenho certeza se isso pode ser feito no Excel.

    

Answer 1

Experimente esta macro curta:

Sub KeyWord()
    Dim Na As Long, Nc As Long, ary, s As String
    Dim r As Range, a, i As Long, outpt As String

    Na = Cells(Rows.Count, "A").End(xlUp).Row
    Nc = Cells(Rows.Count, "C").End(xlUp).Row

    ReDim ary(1 To Nc)
    i = 1
    For Each r In Range("C1:C" & Nc)
        ary(i) = r.Text
        i = i + 1
    Next r

    For i = 1 To Na
        s = Cells(i, "A").Value
        outpt = ""
        For Each a In ary
            If InStr(1, s, a) > 0 Then
                outpt = outpt & "," & a
            End If
        Next a
        If outpt = "" Then
        Else
            Cells(i, "E").Value = Mid(outpt, 2)
        End If
    Next i

End Sub

por exemplo:

EDIT#1:

Nossopequenotruqueparacapturarapenaspalavrascompletasécercarcadapalavra-chavecomespaçosecadasentençacomespaços.

Issosignificaque[espaço]o[espaço]nãocorresponderáteatro!:

SubKeyWord_II_TheSequel()DimNaAsLong,NcAsLong,ary,sAsStringDimrAsRange,a,iAsLong,outptAsStringNa=Cells(Rows.Count,"A").End(xlUp).Row
    Nc = Cells(Rows.Count, "C").End(xlUp).Row

    ReDim ary(1 To Nc)
    i = 1
    For Each r In Range("C1:C" & Nc)
        ary(i) = r.Text
        ary(i) = " " & ary(i) & " "
        i = i + 1
    Next r

    For i = 1 To Na
        s = Cells(i, "A").Value
        s = " " & s & " "
        outpt = ""
        For Each a In ary
            If InStr(1, s, a) > 0 Then
                outpt = outpt & "," & a
            End If
        Next a
        If outpt = "" Then
        Else
            Cells(i, "E").Value = Mid(outpt, 2)
        End If
    Next i

End Sub

    

Answer 2

Aviso:

É difícil estimar o desempenho da seguinte "fórmula de matriz" em um grande conjunto de palavras-chave. Ele funciona em um conjunto de dados razoável e produz resultados "corretos".

A Fórmula de Matriz para E2 (Em seguida, a coluna de preenchimento automático E) é:

E2:
=TEXTJOIN(", ",TRUE,REPT(C$2:C$99999,ISNUMBER(SEARCH(C$2:C$99999,A2))))

CTRL SHIFT ENTER

Como eu disse foi testado em um conjunto "razoavelmente grande", digamos alguns milhares, Então eu postei isso como uma solução geral para situações semelhantes, mas tenho dúvidas para o seu enorme conjunto de 700k (!).

Também é recomendável não manter as fórmulas, mas usá-las apenas para cálculos, depois fixar os valores e finalmente remover as células vazias. HTH

    

Answer 3

Suponho que sua exibição mostrará a saída de A6 em E5 (porque a saída para A5 é "nenhum") é um erro, e você realmente quer a saída para A6 em E6 .

Eu acho que não é natural, ter as palavras-chave em uma coluna. A saída para Ai está em Ei , e o valor em Ci realmente não tem nada a ver com as outras coisas na linha i . É mais uma situação m × n , onde você tem m células (parágrafos) de texto para pesquisar, e n palavras-chave para procurar. Bem, nós temos maneiras de lidar com isso.

Minha solução usa colunas auxiliares +1 em n . Como n aqui é 5, isso significa 6 colunas auxiliares, que podem ser Colunas F a K . (Como de costume com colunas auxiliares, você pode colocá-los onde quiser - você pode colocá-los em Colunas AA a AF se você quiser - e você pode escondê-los.)

O primeiro ( F ) é deixado em branco. Digite

=OFFSET($C$1, COLUMN()-COLUMN($F:$F), 0)

na célula G1 e arraste / preencha para a direita, para K1 . Isso usa o número da coluna atual (relativo ao início do bloco de colunas auxiliares) como um índice na coluna C , as palavras-chave, replicando assim as palavras-chave na Linha1 (células G1 a K1 ).

Em seguida, insira

=F2 & IF(ISNUMBER(SEARCH(G$1, $A2)), ", " & G$1, "")

na célula G2 . Arraste / preencha para a direita, para a célula K2 , e depois para baixo para cobrir as linhas m que possuem dados na Coluna A . Isso pesquisa o parágrafo na coluna A da linha atual para a palavra-chave i SEARCH(…) retornar um número; ou seja, se ISNUMBER(SEARCH(…)) for verdadeiro), ele gera a palavra-chave, precedida por uma vírgula e um espaço. Se não encontrar a palavra-chave, a função IF(…) é avaliada como uma string nula. (Se você quiser uma comparação com distinção entre maiúsculas e minúsculas, substitua SEARCH por FIND .) Então, de qualquer forma, o resultado é concatenado ao valor da célula à esquerda. Isso produz, na coluna K , uma lista separada por vírgulas das palavras-chave presentes no parágrafo na coluna A da linha atual.

Em seguida, digite

=IF(K2="", "", RIGHT(K2, LEN(K2)-2))

em E2 e arraste / preencha para baixo para cobrir as linhas que possuem dados na coluna A . Isto diz, se o valor na Coluna K for nulo, então avalie para null, caso contrário, retire o ,  do início do valor na coluna K .

Veja uma imagem do resultado que obtive para seus dados de entrada:

(Click para uma imagem maior.)

Observe na célula E6 (a saída para A6 ) que minha solução lista as palavras-chave correspondentes na ordem em que aparecem na coluna C , enquanto sua ilustração os lista na ordem em que aparecem na célula A6 . Se isso for um problema, edite sua pergunta para dizer isso e veremos se posso corrigi-la.