我正在寻找一个公式或正则表达式,可以用来在一个单元格中的字符串中查找值,如果找到,则将另一个字符串添加到另一列的相邻单元格中。
例如,我有一份费用清单,列在可乐按商家:
- 南多斯
- 优步
- 麦当劳
- PureGym
我想将这些归类为胶束B:
[ Find "Nandos|McD " in ColA and write "Food" in the equivalent cell in ColB ]
[ Find "Uber" in ColA and write "Travel" in the equivalent cell in ColB ]
[ Find "PureGym" in ColA and write "Gym" in the equivalent cell in ColB ]
所以我最终得到:
- Nandos - 食物
- Uber - 旅行
- 麦当劳 - 食品
- PureGym - 健身房
答案1
查找函数(例如 VLOOKUP 或 INDEX 与 MATCH 的组合)是 Excel 中非常常见的基本函数,因此值得熟悉它们。为了帮助您入门,这里有一篇关于 VLOOKUP 的很好的解释这里。
以下是使用您的示例的要点:
一些虚构的数据位于 A 列和 C 列中。在工作表的另一个位置或另一个工作表中,您有查找表。我将其放在 F 列和 G 列中。B 列是您要填写每个条目的类别的地方。B2 中的公式为:
=VLOOKUP(A2,$F$2:$G$5,2,0)
您只需根据需要复制它即可。A 列中对商家的引用未固定,因此当您将公式复制到页面下方时,它将反映当前行。F:G 中的查找表使用绝对寻址(美元符号)固定,因此当您复制公式时它将继续指向该表。第三个参数告诉 VLOOKUP 从表的第二列检索结果。最后一个参数(0
或false
)告诉 VLOOKUP 对商家名称进行精确匹配。
增强型解决方案
您在评论中提出了一个挑战,假设 A 列中的商家条目是一个随机文本字符串,其中包含查找表中的商家名称。您可以这样做:
在第 6 行中,我添加了您评论中的示例。我还在查找表中商家名称旁边为表格行添加了序号。B 列中的公式(公式窗口中的 B6 显示):
=INDEX($G$2:$G$5,SUMPRODUCT(ISNUMBER(SEARCH($F$2:$F$5,A6))*$E$2:$E$5))
这将使用 INDEX 根据 SUMPRODUCT 生成的表行从 G 列中提取类别。
SUMPRODUCT 允许您对某个范围执行数组操作。它使用 SEARCH 函数来查看 F 列中的每个商家名称是否包含在 A 列的商家字符串中。搜索返回一个位置或错误,但我们只关心它是否存在,这将是一个数字结果。ISNUMBER 返回一个1
表示真或0
表示假的数值,该数值乘以 E 列中添加的表格行号。SUMPRODUCT 的结果将是匹配商家的表格行。
如果您想让商家名称更显眼,您可以在 A 的右侧插入一列,以仅显示存储在查找表中的商家名称。您可以使用与上述相同的公式,但让 INDEX 范围指向 F 列而不是 G 列。
答案2
正则表达式函数:-=INDEX($F$1:$F$4,MAX(IFERROR(SEARCH("*"&$E$1:$E$4&"*",E11)*ROW($E$1:$E$4),0)))
粘贴后点击ctrl++shiftenter