如果单元格包含 1 个或多个关键字，则更改不同单元格的值
我有一个列，其中包含一些字符串描述。
例如：
Bob davids mowing the lawn tipping cows
此外，我将在不同的工作表或列上列出关键字列表 例如工作关键字列表1：
davids work
播放关键字列表：
mowing cows
因此，当主列填充了这些文本字符串时，我希望它们自动检查每个关键字列表以查看这些单词是否存在，当找到匹配项时，将列表的标题（工作/播放）放入下一个单元格中到它。
我知道这在 VBA 中是可能的，甚至可以在 SelectionChange 函数中“即时”完成。但是，如果没有 VBA（例如条件格式），是否可以做到这一点？
I have a column with some string description in it.
for example:
Bob davids mowing the lawn tipping cows
In addition I will have on a different sheet or column a list of keywords
For example work keyword list 1:
davids work
play keyword list:
mowing cows
So as the main column is populated with these text strings, I would like them checked automatically each keyword list to see if those words exist, and when it finds a match, place the title of the list (work/play) in the cell next to it.
I know this is possible in VBA and can even do it "on the fly" in SelectionChange function. But is this possible to do without VBA such as a conditional formatting?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只需使用公式就可以很容易地做到这一点，只要您不太关心可能不正确地查找单词的部分即可。不过，暂时忽略这个警告。首先，这里有一个公式，它会告诉您是否在源字符串中的任何位置找到了多个字符串中的任何一个：
这需要作为数组公式输入才能工作。您可以通过使用 Ctrl-Shift-Enter 输入来完成此操作。要了解其工作原理，请考虑 Excel 在评估实际示例时所做的操作：
“FIND”查找一个字符串在另一个字符串中的位置。当使用第一个参数的数组调用时，它将返回位置数组（如果未找到搜索字符串，则返回#VALUE！）。您可以通过输入该公式然后在其中的表达式上使用 F9 键来跟踪评估：
因此，对于您的示例，假设您要在 $B$6:$B$8 中搜索字符串，在 $D$2 中搜索您的工作字符串:$D$3，以及您在 $E$2:$E$3 中演奏的琴弦。您可以将公式放入
单元格 D6 中，将其作为数组公式输入，然后将其拖动到 D6:E8 范围内，以查找 B 中的哪些字符串包含工作或播放单词。然后，您可以使用这些结果来驱动进一步的公式或条件格式。
但是，如上所述，您会注意到正在搜索的字符串中的任何子字符串都会被发现，因此
计算结果将为 TRUE。 （如果您的有趣列表包含“id”，则“davids”中的“id”将匹配。）
与 Excel 的常见情况一样，如果您正在使用有限的数据集做您理解的事情，您可能不关心这。但它可能会挫败将这种公式用作一般“应用程序”的一部分的尝试，该应用程序的用户不知道花哨的数组技巧或“FIND”的确切作用。 （你可以通过在搜索词后面加一个空格等来解决这个问题，但如果你把它交给其他人，这只是更神秘的巫术等待被打破。）不过，对于快速而肮脏的扫描来说，这很好。
This is pretty easy to do with just formulas, as long as you don't care too much about possibly improperly finding parts of words. Ignore that caveat for a second though. First, here is a formula that will tell you if any of several strings are found anywhere within a source string:
This needs to be entered as an array formula for it to work. You do that by entering it with Ctrl-Shift-Enter. To understand how it works, consider what Excel does in evaluating a real example:
'FIND' finds the position of one string within another. When called with an array for the first argument, it will return an array of positions (or #VALUE! if the search string isn't found). You can trace the evaluation by entering that formula and then using the F9 key on expressions within it:
So, for your example, say you had your strings you want searched in $B$6:$B$8, your work strings in $D$2:$D$3, and your play strings in $E$2:$E$3. You could put the formula
in cell D6, enter it as an array formula, and then drag it through the range D6:E8 to find which strings in B had work or play words in them. Then you could use those results to drive further formulas or conditional formatting.
However, as mentioned above, you'll note that any substring within the string being searched will get found, so
will evaluate to TRUE. (And if your fun list includes "id", the "id" in "davids" will match.)
As is often the case with Excel, if you're doing something you understand with a limited data set, you might not care about this. But it can defeat an attempt to use this kind of formula as part of a general "application" that has users who don't know fancy array tricks or exactly what 'FIND' does. (You can sort of get around that by putting a space after your search words, etc., but that is just more mysterious voodoo waiting to be broken if you hand it someone else.) For a quick and dirty scan, though, it's fine.
我为少数人准备了另一种方法。正如所暗示的那样，这很快就会变得很麻烦。
在 A 列中输入以下内容：
1：马牛猪鸡
2：狗猫鸟
3：鱼猫
4：马猫老鼠
5：猿人海豚
6: 狗猫花栗鼠
在单元格 B1 中输入以下内容（并复制到 B2:B6 中）：
B1和B5中的结果应显示为空白，而 <code> 中的结果应显示为空白B2、B3、B4和B5应为“House pet”。如果关键字列表变得太大，请小心。我也成功地将其用于简短列表（如问题中所暗示的），但如果太长，您输入所有可能性的空间有限 - 更不用说公式中的风险错误了。
我无法让我的数组工作 - 感谢上面的帖子，我现在也要去尝试一下我的较长列表。
附：不记得我用
OR ISERROR数组做了什么，但您提供的这个数组非常适合我的 80 个关键字列表。谢谢！I've put together another way of doing this for a small number. As was intimated, this gets cumbersome quickly.
Input the following into column A:
1: horse cow pig chicken
2: dog cat bird
3: fish cat
4: horse cat mouse
5: ape human dolphin
6: dog cat chipmunk
In Cell B1 enter the following (and copy into B2:B6):
The result in
B1andB5should appear blank whereas the result inB2,B3,B4, andB5should read "House pet".Careful if the list of keywords gets too big. I used this successfully for short lists too (as implied in the question) but you have limited space to type all the possibilities if its too long - not to mention risk errors in the formula.
I couldn't get my arrays to work - thanks for the post above I'm going to go give this a try now too for my longer lists.
ps. Don't remember what I was doing with my
OR ISERRORarray but this array you've provided worked beautifully for my list of 80 keywords. Thanks!