vba 如果单元格包含 1 个或多个关键字，请更改不同单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5394063/
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
If cell contains 1 or more keywords, change value of a different cell
提问by NickG
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:
此外，我将在不同的工作表或列上列出关键字列表例如工作关键字列表 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 SelectionChangefunction. But is this possible to do without VBA such as a conditional formatting?
我知道这在 VBA 中是可能的，甚至可以在SelectionChange功能上“即时”完成。但这是否可以在没有 VBA 的情况下实现，例如条件格式？
回答by jtolle
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:
只要您不太关心可能不正确地查找单词的某些部分，这仅用公式就很容易做到。不过，请暂时忽略该警告。首先，这是一个公式，它会告诉您是否在源字符串中的任何位置找到多个字符串中的任何一个：
=OR(NOT(ISERROR(FIND(<array of strings to look for>,<string to look in>))))
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:
这需要作为数组公式输入才能工作。您可以通过使用 Ctrl-Shift-Enter 输入它来完成此操作。要了解它的工作原理，请考虑 Excel 在评估真实示例时的作用：
=OR(NOT(ISERROR(FIND({"a","b","c"},"q b q"))))
'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:
'FIND' 查找一个字符串在另一个字符串中的位置。当使用第一个参数的数组调用时，它将返回一个位置数组（如果未找到搜索字符串，则返回 #VALUE！）。您可以通过输入该公式，然后在其中的表达式上使用 F9 键来跟踪评估：
=OR(NOT(ISERROR({#VALUE!,3,#VALUE!})))
=OR(NOT({TRUE,FALSE,TRUE}))
=OR({FALSE,TRUE,FALSE})
=TRUE
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
因此，对于您的示例，假设您在 $B$6:$B$8 中有要搜索的字符串，在 $D$2:$D$3 中有要搜索的字符串，在 $E$2:$E$3 中有要搜索的字符串。你可以把公式
=OR(NOT(ISERROR(FIND(D:D,$B6))))
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.
在单元格 D6 中，将其作为数组公式输入，然后将其拖动到范围 D6:E8 中以查找 B 中哪些字符串在其中包含工作或播放单词。然后您可以使用这些结果来驱动进一步的公式或条件格式。
However, as mentioned above, you'll note that any substring within the string being searched will get found, so
但是，如上所述，您会注意到正在搜索的字符串中的任何子字符串都会被找到，因此
=OR(NOT(ISERROR(FIND({"a","b","c"},"bad"))))
will evaluate to TRUE. (And if your fun list includes "id", the "id" in "davids" will match.)
将评估为 TRUE。（如果您的有趣列表包含“id”，则“davids”中的“id”将匹配。）
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.
与 Excel 的情况一样，如果您正在使用有限的数据集做您理解的事情，您可能不会关心这一点。但是它可以挫败将这种公式用作一般“应用程序”的一部分的尝试，该应用程序的用户不知道花哨的数组技巧或“FIND”究竟是做什么的。（你可以通过在你的搜索词等后面加一个空格来解决这个问题，但如果你把它交给其他人，那只是更神秘的伏都教等待被打破。）但是，对于快速而肮脏的扫描，这很好.
回答by user2163442
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
在A栏输入以下内容：1：马牛猪鸡2：狗猫鸟3：鱼猫4：马猫老鼠5：猿人海豚6：狗猫花栗鼠
In Cell B1 enter the following (and copy into B2:B6):
在单元格 B1 中输入以下内容（并复制到 B2:B6）：
=if(sum(iserror(find("cat",A1)),iserror(find("dog",A1)),iserror(find("fish",A1)),iserror(find("bird",A1)))<4,"House pet","")
The result in B1and B5should appear blank whereas the result in B2, B3, B4, and B5should read "House pet".
在结果B1和B5应显示为空白，而结果B2，B3，B4，并B5应为“宠物”。
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!
附：不记得我用我的OR ISERROR数组做了什么，但是您提供的这个数组非常适合我的 80 个关键字列表。谢谢！
