vba excel：如何识别包含从关键字列表中提取的文本关键字的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12395971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel: how can I identify rows containing text keywords taken from a list of keywords
提问by Kunjan
I have one column (call it A) of data where each cell contains a long string of words e.g.:
我有一列（称为 A）数据，其中每个单元格都包含一长串单词，例如：
COLUMN A
HORNBACH BAUMARKT ETOY, ETOY
ALIGRO, CHAVANNES-PR
DIPL. ING. FUST AG,ETO, ETOY
AGIP SUISSE SA 224, LAUSANNE
AMAZON MEDIA EU, LUXEMBOURG
MIGROS M EPALINGES, EPALINGES
HORNBACH BAUMARKT ETOY, ETOY
MANOR AG - 390, BASEL
MANOR AG - 390, BASEL
GLOBUS LAUSANNE, LAUSANNE
I also have another list of keywords in another column (call it B) e.g.
我在另一列中还有另一个关键字列表（称为 B），例如
COLUMN B
MSFT
Amazon
Hornbach
Jumbo
OBI
Lipo
Ikea
Coop
Migros
Casino
This is what I would like to do:
这就是我想做的：
For each keyword K in Col B Check each cell in Col A to see if the entry exists as a sub-string If it does, then enter the keyword K in an adjacent cell in Column C If not, then leave the adjacent cell in Column C untouched Repeat for next keyword K
对于 Col B 中的每个关键字 K 检查 Col A 中的每个单元格以查看该条目是否作为子字符串存在 如果存在，则在 C 列的相邻单元格中输入关键字 K 如果不是，则将相邻单元格留在列中C 不变 重复下一个关键字 K
The result should be:
结果应该是：
COLUMN A ----------------------------------> COLUMN C
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach
ALIGRO, CHAVANNES-PR
DIPL. ING. FUST AG,ETO, ETOY
AGIP SUISSE SA 224, LAUSANNE
AMAZON MEDIA EU, LUXEMBOURG ---------------> Amazon
MIGROS M EPALINGES, EPALINGES -------------> Migros
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach
MANOR AG - 390, BASEL
MANOR AG - 390, BASEL
GLOBUS LAUSANNE, LAUSANNE
I can see how to do this using VBA type structures... but surely there must be a way to do this using built in Excel functions - INDEX, HLOOKUP, SEARCH... etc.
我可以看到如何使用 VBA 类型结构来做到这一点......但肯定有一种方法可以使用内置的 Excel 函数来做到这一点 - INDEX、HLOOKUP、SEARCH......等。
I've tried but not succeeded. If anyone has a better idea, please let me know.
我试过但没有成功。如果有人有更好的主意，请告诉我。
回答by barry houdini
Assuming only 1 matching word per row at most you could use this formula in C1 copied down
假设每行最多只有 1 个匹配的单词，您可以在 C1 中使用此公式复制下来
=IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")
=IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")
IFERROR function is available in Excel 2007 or later versions only, for earlier Excel versions try this modification
IFERROR 函数仅在 Excel 2007 或更高版本中可用，对于早期的 Excel 版本尝试此修改
=LOOKUP("zzz",IF({1,0},"",LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10)))
=LOOKUP("zzz",IF({1,0},"",LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10)))
If you want to get multiple matches, in separate cells then you can use this "array formula" in C1, confirmed with CTRL+SHIFT+ENTERand copied down and across as far as you might need (commensurate with the maximum possible matches)
如果您想在单独的单元格中获得多个匹配项，那么您可以在 C1 中使用此“数组公式”，确认CTRL+SHIFT+ENTER并根据需要向下和复制（与最大可能匹配项相称）
=IFERROR(INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")
=IFERROR(INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")
If A1 contains 3 words on the list then those will be populated in C1, D1 and E1 and F1 etc. will remain blank
如果 A1 在列表中包含 3 个单词，则这些单词将填充在 C1、D1 和 E1 中，而 F1 等将保留为空白
Revised as per comments:
根据评论修改：
The first range in the formula (first argument of INDEX), defines the range from which the result is taken so to change that to column Z just change that part, i.e.
公式中的第一个范围（INDEX 的第一个参数）定义了从中获取结果的范围，以便将其更改为 Z 列只需更改该部分，即
=IFERROR(INDEX($Z$1:$Z$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")
=IFERROR(INDEX($Z$1:$Z$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")
