Как искать строку в столбце подстрок

Question

У меня есть файл данных, где есть такая таблица:

(DATA TABLE)
           REF_ID              |     COST    |   TYPE
======================================================
123_COMPUTER_XYZ               | 50000       | (to be filled)
45623_LAPTOP_AOS               | 90000
BHJ_WALLET_ASODO               | 1000
BUIGQ32_TRIMMER                | 200
...

Теперь я хочу добавить еще один столбец в таблицу, в котором type продукта автоматически указывается с использованием таблицы поиска, которую я определил следующим образом:

(TYPE TABLE)
PRODUCT SUBSTRING    |  TYPE
=====================================
COMPUTER             | computer
LAPTOP               | computer
WALLET               | personal
TRIMMER              | personal-care
...

Я хочу использовать столбец PRODUCT SUBSTRING в TYPE TABLE для поиска и поместить соответствующее значение TYPE в 3-й столбец DATA TABLE.

Я посмотрел на многие вопросы:

• Найти подстроку в столбце - Excel
• Поиск и возврат значения на основе совпадения подстроки
• http://www.mrexcel.com/forum/excel-questions/8800-substring-search-within-array-strings-part-2-a.html

но все они спрашивают об этом наоборот, где искомая строка (поле REF_ID) является подстрокой TYPE-TABLE.

Вы можете мне помочь?

Answer 1

Вот решение, основанное на коротком UDF().

Скажем, таблица поиска находится в столбцах F и G от строки 1 до максимум 100 строк. Сначала вставьте этот UDF() в стандартный модуль:

Public Function GetType(sIN As String) As String
   Dim LookupTable As Range, nItems As Long
   Set LookupTable = Range("F1:G100")
   nItems = 100

   For i = 1 To nItems
      If LookupTable(i, 1) = "" Then Exit For
      If InStr(1, sIN, LookupTable(i, 1)) > 0 Then
         GetType = LookupTable(i, 2)
         Exit Function
      End If
   Next i
   GetType = "UN-FOUND"
End Function

Затем в ячейку C1 введите:

=GetType(A1)

и скопируйте вниз:

Пользовательские функции (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

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

Answer 2

Вот решение вашей проблемы:

=INDEX(Table2[TYPE],MAX((ROW(Table2[PRODUCT SUBSTRING])-1)*SIGN(IFERROR(SEARCH(Table2[PRODUCT SUBSTRING],[@[REF_ID]]),0))))
Это формула массива, вам нужно ввести ее, нажав CTRL+Shift+Enter.

где:

• IFERROR(SEARCH(Table2[PRODUCT SUBSTRING],[@[REF_ID]]),0)) проверяет, какая подстрока включена в фактический REF_ID (дает начальную позицию для нее, 0 для всех других подстрок)
• SIGN(...) - преобразовывает предыдущий список в 1 для найденной подстроки
• (ROW(Table2[PRODUCT SUBSTRING])-1) - номер строки минус один (позиция в списке, если заголовок находится в первой строке)
• MAX(...) - поскольку единственное значение для найденной подстроки не равно нулю, эта функция извлекает свою позицию
• =INDEX(Table2[TYPE],MAX(...)) - введите из списка

Обновить

Приведенная выше формула относится к таблицам, для преобразования диапазонов в таблицы выберите: Вставить - Таблица (в примере table1 - это таблица слева, поэтому сначала преобразуйте ее)

Формула со стандартными ссылками:
=INDEX(G$2:G$7,MAX((ROW(F$2:F$7)-1)*SIGN(IFERROR(SEARCH(F$2:F$7,A3),0))))