Найдите, сколько слов из ячейки найдено в массиве

Question

У меня есть два столбца с данными. Первый содержит несколько терминов, а второй содержит отдельные слова. что я имею

Я ищу способ определить, какие слова из каждой ячейки из первого столбца появляются во втором, поэтому результат должен выглядеть примерно так (мне не нужны запятые): что мне нужно

Мой вопрос чем-то похож на поиск ячеек в Excel из диапазона, где значение поиска находится внутри ячейки, но не совсем так, потому что мне нужно определить, какие слова появляются во втором столбце, и может быть более одного слова.

Я тоже пробовал =INDEX($D$2:$D$7;MATCH(1=1;INDEX(ISNUMBER(SEARCH($D$2:$D$7;A2));0);)) но он также возвращает только одно слово.

Answer 1

Если вы хотите использовать VBA, вы можете определить пользовательскую функцию:

Public Function SearchForWords(strTerm As String, rngWords As Range) As String
    Dim cstrDelimiter As String: cstrDelimiter = Chr(1) ' A rarely used character
    strTerm = cstrDelimiter & Replace(strTerm, " ", cstrDelimiter) & cstrDelimiter ' replace any other possible delimiter here
    SearchForWords = vbNullString
    Dim varWords As Variant: varWords = rngWords.Value
    Dim i As Long: For i = LBound(varWords, 1) To UBound(varWords, 1)
        Dim j As Long: For j = LBound(varWords, 2) To UBound(varWords, 2)
            If InStr(1, strTerm, cstrDelimiter & varWords(i, j) & cstrDelimiter) <> 0 Then
                SearchForWords = SearchForWords & varWords(i, j) & ", "
            End If
        Next j
    Next i
    Dim iLeft As Long: iLeft = Len(SearchForWords) - 2
    If 0 < iLeft Then
        SearchForWords = Left(SearchForWords, Len(SearchForWords) - 2)
    End If
End Function

И вы можете использовать его из таблицы Excel следующим образом:

=SearchForWords(A2;$D$2:$D$7)

Answer 2

У меня есть частичное решение:

=IF(1-ISERROR(SEARCH(" "&D2:D7&" "," "&A2&" ")),D2:D7&", ","")

Эта формула возвращает массив слов, содержащихся в ячейке (диапазоны соответствуют вашей картинке). Этот массив разреженный: он содержит пустые строки для каждого пропущенного слова. И это предполагает, что слова всегда разделяются одним пробелом (это может быть улучшено при необходимости).

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

Вам понадобится VBA, но если вы используете VBA, вам вообще не стоит беспокоиться о первой части, поскольку вы можете делать все что угодно.

Answer 3

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

[править] Я заметил, что это неправильно подбирает "доску" на "доске", но это должно быть легко исправлено.

=IFERROR(IF(FIND(C$1,$A2,1)>0,C$1 & ", "),"")

Просто конкатенируйте результаты

=CONCATENATE(C2,D2,E2,F2,G2,H2)

или же

=LEFT(CONCATENATE(C2,D2,E2,F2,G2,H2),LEN(CONCATENATE(C2,D2,E2,F2,G2,H2))-2)

снять последнюю запятую и пробел

Я отредактировал это, чтобы исправить проблему с "доской"

новая формула для C2

=IF(OR(C$1=$A2,ISNUMBER(SEARCH(" "&C$1&" ",$A2,1)),C$1 & " "=LEFT($A2,LEN(C$1)+1)," " & C$1=RIGHT($A2,LEN(C$1)+1)),C$1 & ", ","")

Новая формула для B2, чтобы поймать ошибку, если нет слов

=IFERROR(LEFT(CONCATENATE(C2,D2,E2,F2,G2,H2,I2),LEN(CONCATENATE(C2,D2,E2,F2,G2,H2,I2))-2),"")