如何在子字符串列中查找字符串

如何在子字符串列中查找字符串

我有一个数据文件,其中有一张这样的表:

(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 SUBSTRINGTYPE TABLE 中的列作为查找并将相应的值TYPE放入 DATA TABLE 的第 3 列。

我看过很多问题:

但所有查询都是反过来的,其中要搜索的字符串(REF_ID 字段)是子串类型表。

你能帮助我吗?

答案1

这是一个基于简短的解决方案UDF()

假设查找表是按列FG从行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 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF:

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要从 Excel 使用 UDF:

=我的函数(A1)

要了解有关宏的更多信息,请参阅:

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

必须启用宏才能使其工作!

答案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))))

相关内容