我有一个数据文件,其中有一张这样的表:
(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
放入 DATA TABLE 的第 3 列。
我看过很多问题:
- 查找列中的子字符串 - Excel
- 根据子字符串匹配查找并返回值
- http://www.mrexcel.com/forum/excel-questions/8800-substring-search-within-array-strings-part-2-a.html
但所有查询都是反过来的,其中要搜索的字符串(REF_ID 字段)是子串类型表。
你能帮助我吗?
答案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) 非常容易安装和使用:
- ALT-F11 打开 VBE 窗口
- ALT-I ALT-M 打开新模块
- 粘贴内容并关闭 VBE 窗口
如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx
要删除 UDF:
- 调出如上所示的 VBE 窗口
- 清除代码
- 关闭 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))))