1

Я хочу использовать формулу для заполнения ячеек "РЕЗУЛЬТАТЫ". Мне нужно, чтобы проверить, содержит ли соседняя "ПРОВЕРЕННАЯ КЛЕТКА" какое-либо из слов, перечисленных в ячейках "КЛЮЧЕВЫЕ СЛОВА".

В идеале я мог бы добавить новые слова в список ключевых слов по желанию. Итак, я пытался сделать диапазон что-то вроде "$ A $ 2:$ A".

Пример - [C2]: «Содержит ли [B2] какие-либо слова из {$ A $ 2: $ A}»?

Я не смог найти способ сделать это с помощью формулы. Есть идеи?

[A1] Ключевые слова

[A2] собака

[A3] белка

[A4] лось

[A5] кошка

[B1] ПРОВЕРЕННАЯ КЛЕТКА

[B2] Ajj Moose Adfli

[B3] AMSCRAM

[B4] 124 liML

[B5] собака Лимфо

[C1] РЕЗУЛЬТАТЫ

[C2] да

[C3] нет

[C4] нет

[C5] да

|источник

2 ответа2

3

Вы можете сделать это с помощью формулы массива. Для фиксированного диапазона формула в C2 будет иметь вид

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

Обязательно введите это как формулу массива, используя Ctrl+Shift+Enter. Вы поймете, что это сработало, когда вы получите фигурные скобки {} на обоих концах формулы.

Если вы хотите, чтобы диапазон был динамическим, один хороший способ - определить именованный диапазон. Если вы попытаетесь использовать все столбцы A:A то все вернет yes потому что функции SEARCH и FIND возвращают TRUE если искомая строка пуста. Поскольку A:A будет содержать пробелы в вашем списке, все ваши результаты будут yes и это не поможет. Вместо этого нам нужно определить динамический именованный диапазон. Есть несколько способов сделать это, но мне нравится открывать "Диспетчер имен" (он находится в середине ленты "Формулы" в разделе "Определенные имена"). Откройте его и нажмите "Новый" в левом верхнем углу. Дайте ему имя, например « Keywords и формулу "Относится к".

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

Скриншот

Теперь вы можете изменить формулу в C1 чтобы она была такой (все еще вводится с помощью Ctrl+Shift+Enter)

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

Предостережение № 1

SEARCH регистр. Если вы хотите поиск с учетом регистра, замените его на FIND .

Предостережение № 2

Формула для динамического именованного диапазона предполагает, что в списке ключевых слов нет пробелов. Он будет ссылаться на все ячейки от A2 до ячейки перед первым пробелом. Конечно, это также предполагает, что где-то в A:A есть пробел, что означает, что ваш список ключевых слов должен иметь длину менее 1 048 575 элементов.

Предостережение № 3

Вы можете сделать это без указанного диапазона, но ваша формула становится длиннее и сложнее следовать. Тем не менее, он сохраняет все это в одной формуле. Если ваши данные очень большие, я бы порекомендовал именованный диапазон, поскольку это ускорит вычисления. Чтобы объединить все это, формула в C2 будет

=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")
|источник
0

Попробуйте эту небольшую пользовательскую функцию (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

Пользовательские функции (UDF) очень просты в установке и использовании:

  1. ALT-F11 открывает окно VBE
  2. ALT-I ALT-M открывает новый модуль
  3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

  1. откройте окно VBE, как указано выше
  2. очистить код
  3. закройте окно VBE

Чтобы использовать UDF из Excel:

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

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .