Поиск диапазона слов в диапазоне предложений

Question

На основании следующего примера:

Я хочу найти слова из диапазона $D$2:$D$6 в предложениях из диапазона $A$2:$A$8 и использовать значение из бокового столбца (здесь Tag). Пока я делаю это:

B2 formula
------------------
=IF(NOT(ISERROR(SEARCH($D$2,A2))),$E$2,
IF(NOT(ISERROR(SEARCH($D$3,A2))),$E$3,
IF(NOT(ISERROR(SEARCH($D$4,A2))),$E$4,
IF(NOT(ISERROR(SEARCH($D$5,A2))),$E$5,
IF(NOT(ISERROR(SEARCH($D$6,A2))),$E$6,
"other"
)
)
)
)
)

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

Есть ли способ повторить вышеуказанное решение с помощью формулы на основе диапазона?

Answer 1

Вам нужно использовать формулы массива. В ячейке B2 введите:

=IFERROR(OFFSET($E$1,MATCH(1E+37,SEARCH(D$2:D$6,A2),1),0),"other")

и нажмите Ctrl+Shift+Enter, чтобы ввести его как формулу массива.

Он использует функцию SEARCH чтобы сопоставить каждое слово в вашем списке, и находит последнее, которое меньше "большого числа" (здесь 1E+37). Если найдено более одного совпадения, он найдет тот, который находится дальше всего в списке (например, "красная машина и черная кошка" вернут "объект"). В случае, если ничего не найдено, бит IFERROR дает вам "другое".

Конечно, вам придется расширять его, поскольку ваш список становится длиннее. (Всегда помните Ctrl+Shift+Enter при редактировании!)

Answer 2

Вы можете использовать эту обычную формулу в B2 скопированы

=LOOKUP(2^15,SEARCH(D$2:D$6,A2),E$2:E$6)

Смотрите обсуждение и больше об этой формуле здесь

Answer 3

Чтобы решить эту проблему, я использовал несколько VBA на кнопке с макросом на странице с надписью «Обновление», чтобы вы могли добавить лист как необходимый;

Private Sub CommandButton1_Click()
Dim Target, cell As Range
Set Target = Range(Range("A1"), Range("A65536").End(xlUp))

    Dim term, tag As String

        term = "cat"
        tag = "animal"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "dog"
        tag = "animal"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        i = "car"
        k = "object"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "plane"
        tag = "object"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "sister"
        tag = "person"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

    End Sub

Основной формат;

term= "cat"
tag = "animal"
For Each cell In Target
    If InStr(1, cell, i, 1) Then cell.Offset(0, 1).Value = k
Next cell

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

Стоит также отметить, что ваше уравнение ЕСЛИ использует двойной минус, вы можете просто использовать ЕСЛИ (ПОИСК вместо ЕСЛИ (НЕ (ИЗЕРРОР (ПОИСК).