Найти ключевые слова в абзацах текста (в Excel)

Question

Изменить: Все ответы работают отлично, но для большого набора данных, макро подход работал лучше. Попробуйте все из них и посмотрите, что работает лучше для вас.

Я пытаюсь решить эту проблему в Excel для повышения производительности (я сделал это в R, но это медленно). По сути, мне нужно сопоставить каждое ключевое слово (из списка ключевых слов) с текстом (по существу, абзацами) в столбце.  Вот иллюстрация:

Приведенные выше данные находятся в источнике этого вопроса в текстовом виде, пригодном для копирования и вставки.

Я искал некоторые текстовые функции (например, FIND и SEARCH, но они только возвращают позиции), поэтому я не уверен, что это можно сделать в Excel.

Answer 1

Отказ от ответственности:

Сложно оценить эффективность следующей "формулы массива" для огромного набора ключевых слов. Он работает с разумным набором данных и дает "правильные" результаты.

Формула массива для E2 (тогда столбец автозаполнения E):

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

CTRL SHIFT ENTER

Как я уже говорил, он был протестирован на "достаточно большом" наборе, скажем, нескольких тысячах, поэтому я опубликовал его как общее решение для подобных ситуаций, но у меня есть сомнения по поводу вашего огромного набора в 700 КБ (!).

Также, вероятно, рекомендуется не сохранять формулы, а только использовать их для расчета, затем фиксировать значения и, наконец, удалять пустые ячейки. НТН

Answer 2

Я предполагаю, что ваш дисплей, отображающий вывод для A6 в E5 (потому что вывод для A5 - «none») является ошибкой, и вы действительно хотите вывод для A6 в E6 .

Я нахожу это неестественным, имея ключевые слова в столбце.  Выход для Ai находится в Ei , а значение в Ci самом деле не имеет ничего общего с другими вещами в строке i.  Это скорее ситуация m × n , где у вас есть m ячеек (абзацев) текста для поиска и n ключевых слов для поиска.  Ну, у нас есть способы справиться с этим.

Мое решение использует n +1 вспомогательных столбцов.  Поскольку n здесь равно 5, это означает 6 вспомогательных столбцов, которые могут быть столбцами с F по K (Как обычно с помощью вспомогательных столбцов, вы можете поместить их в любое место - вы можете поместить их в столбцы AA через AF если хотите - и можете скрыть их.)

Первый (F) оставлен пустым.  Войти

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

в ячейку G1 и перетащите / заполните вправо, к K1 .  При этом используется текущий номер столбца (относительно начала блока вспомогательных столбцов) в качестве индекса ключевых слов в столбце C , тем самым реплицируя ключевые слова в строке 1 (ячейки с G1 по K1).

Далее введите

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

в клетку G2 .  Перетащите / заполните вправо, в ячейку K2 , а затем вниз, чтобы покрыть m строк, имеющих данные в столбце A При этом выполняется поиск абзаца в столбце A текущей строки по ключевому слову i , которое находится в строке 1 текущего столбца.  Если он находит его (т. Е. Если SEARCH(…) возвращает число; т. Е. Если ISNUMBER(SEARCH(…)) имеет значение true), он генерирует ключевое слово, которому предшествует запятая и пробел.  Если ключевое слово не найдено, функция IF(…) оценивается как пустая строка.  (Если вы хотите сравнение с учетом регистра, замените SEARCH на FIND .)  Затем, в любом случае, результат объединяется со значением из ячейки слева. Это дает в столбце K список разделенных запятыми ключевых слов, присутствующих в абзаце в столбце A текущей строки.

Затем введите

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

в E2 , и перетащите / заполните вниз, чтобы покрыть строки, которые имеют данные в столбце A Это говорит о том , если значение в колонке K равна нулю, то оценить пустое значение, в противном случае ,  от начала значения в колонке K

Вот изображение результата, который я получил для ваших входных данных:

(Нажмите для увеличения изображения.)

Обратите внимание, что в ячейке E6 (вывод для A6) мое решение перечисляет совпадающие ключевые слова в том порядке, в котором они отображаются в столбце C , в то время как на рисунке они перечислены в том порядке, в котором они отображаются в ячейке A6 .  Если это проблема, отредактируйте свой вопрос, чтобы сказать так, и я посмотрю, смогу ли я это исправить.

Answer 3

Попробуйте этот короткий макрос:

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

например:

EDIT # 1:

Наша маленькая хитрость, чтобы захватить только полные слова, состоит в том, чтобы окружить каждое ключевое слово пробелами, а каждое предложение - пробелами.

Это означает, что [пробел] [пробел] не будет соответствовать театру !

Sub KeyWord_II_TheSequel()
    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
        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