Выходное согласованное значение в зависимости от диапазона

Question

У меня есть электронная таблица с двумя вкладками: одна представляет собой данные поискового запроса из кампании Adwords, а другая представляет собой список фрагментов текста.

Я хочу использовать функцию для возврата проверки, если условия поиска содержат

Вкладка условий поиска

+-----------------------------+--------+------+
|         Search term         | Clicks | Cost |
+-----------------------------+--------+------+
| nike running shoes          |     50 | $31  |
| soccer cleats               |     30 | $40  |
| lace up boots               |     40 | $45  |
| spikeless adidas golf shoes |     20 | $15  |
| red/blue converse classics  |     15 | $20  |
| flyknit nike for men        |     25 | $30  |
+-----------------------------+--------+------+

Вкладка «Фрагменты текста»

nike
adidas
converse

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

вывод будет выглядеть так:

+-----------------------------+--------+------+--------------+
|         Search term         | Clicks | Cost | matched term |
+-----------------------------+--------+------+--------------+
| nike running shoes          |     50 | $31  | nike         |
| soccer cleats               |     30 | $40  |              |
| lace up boots               |     40 | $45  |              |
| spikeless adidas golf shoes |     20 | $15  | adidas       |
| red/blue converse classics  |     15 | $20  | converse     |
| flyknit nike for men        |     25 | $30  | nike         |
+-----------------------------+--------+------+--------------+

Я пытался использовать функцию ПОИСК в диапазоне: =SEARCH(tab2!A1:A63,A2) но я получаю # #VALUE!

Answer 1

Я предлагаю следующее решение, основанное на том, как я понимаю ваш вопрос. В этом примере термин поиска находится на листе с именем tab1!С3:F8. Фрагменты текстовых строк находятся в tab2!С3: С5.

Теперь в F3 поместите следующую формулу и из панели формул нажмите CTRL + SHIFT + ENTER, чтобы создать формулу массива. Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива, и перетащить ее вниз по всей длине таблицы. Чтобы это работало, вы должны оставить одну ячейку свободной над списком таблицы фрагментированных строк, т.е. начать фрагментированный список в строке 2 и далее на листе tab2

=IF(MIN(IF(ISERROR(SEARCH('tab2'!$C$3:$C$5,C3)),9^99,ROW('tab2'!$C$3:$C$5)-ROW('tab2'!$C$2)))<9^99,INDEX('tab2'!$C$3:$C$5,MIN(IF(ISERROR(SEARCH('tab2'!$C$3:$C$5,C3)),9^99,ROW('tab2'!$C$3:$C$5)-ROW('tab2'!$C$2)))),"")

Обновить:

Если вы используете Google Spreadsheets, эта же формула тоже подойдет. Нажатие CTRL + SHIFT + ВВОД оборачивает формулу в имя функции ArrayFormula. Смотрите скриншот ниже.

Answer 2

С Sheet1 содержащим поисковые термины, и Sheet2 содержащим фрагменты, рассмотрим следующую Пользовательскую функцию:

Public Function GetKeyWord(s As String, rng As Range) As String
    Dim s2 As String, r As Range
    s2 = LCase(" " & s & " ")

    GetKeyWord = ""

    For Each r In rng
        If InStr(1, s2, " " & r.Value & " ") > 0 Then
            GetKeyWord = r.Value
            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:

= MyFunction (А1)

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

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

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