如果单元格包含一个或多个关键字，请更改不同单元格的值 [英] If cell contains 1 or more keywords, change value of a different cell

问题描述

例如：

 
 Bob davids 
修复草坪
倾吐牛

此外，我将在不同的表或列上列出关键字
例如工作关​​键字列表1：

 
 davids 
工作

播放关键字列表：

 
 mowing 
 cows 

所以主列填充使用这些文本字符串，我希望他们自动检查每个关键字列表以查看这些单词是否存在，并且当找到匹配项时，将列表标题（工作/播放）放在旁边的单元格中。

我知道这在VBA中是可能的，甚至可以在 SelectionChange 函数中即时执行。但是，这样做可能没有VBA，如条件格式化？

解决方案

这是很简单的公式，因为只要你不在乎太多可能不正确地找到部分的话。忽略了一点警告。首先，这里是一个公式，可以告诉你是否有任何字符串在源字符串中的任何地方找到：

  = OR NOT（ISERROR（FIND（< string of string to looking for>，< string to look in>））））
  

这需要作为数组公式输入才能正常工作。您可以使用Ctrl-Shift-Enter输入。要了解它是如何工作的，请考虑Excel在评估一个实例中的作用：

  = OR（NOT（ISERROR（FIND a，b，c}，qbq））））
  

FIND'找到一个字符串在另一个字符串中的位置。当用第一个参数的数组调用时，它将返回一个位置数组（如果未找到搜索字符串，则返回#VALUE！）。您可以通过输入该公式来跟踪评估，然后在其中的表达式上使用F9键：

  = OR（NOT（ISERROR （{#VALUE！，3，＃VALUE！}）））
 = OR（NOT（{TRUE，FALSE，TRUE}））
 = OR（{FALSE，TRUE，FALSE}）
 = TRUE 
  

所以，对于你的例子，说你有你要在$ B中搜索的字符串$ 6：$ B $ 8，您的工作字符串$ D $ 2：$ D $ 3，您的播放字符串为$ E $ 2：$ E $ 3。您可以将公式

  = OR（NOT（ISERROR（查找（D $ 2：D $ 3，$ B6））））单元格D6中的
  

，将其作为数组公式输入，然后拖动到范围D6：E8找到B中的哪些字符串在其中工作或播放单词。然后，您可以使用这些结果来驱动更多的公式或条件格式。

但是，如上所述，您将注意到正在搜索的字符串中的任何子字符串将被找到，所以

  = OR（NOT（ISERROR（FIND（{a，b，c}，坏））））
  

将评估为TRUE。 （如果您的乐趣列表包含id，davids中的id将匹配。）

通常情况下，如果您是用有限的数据集来做你所了解的事情，你可能不在乎。但是，它可以打败使用这种方式作为一般应用程序的一部分，用户不知道花哨的阵列技巧或者什么是查找。 （你可以通过在你的搜索词之后放一个空格，等等，但是如果你把别人打了，那只是更神秘的巫术等待被破坏。）对于一个快速而肮脏的扫描，虽然，这很好。

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?

解决方案

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:

=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:

=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

=OR(NOT(ISERROR(FIND(D$2:D$3,$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.

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.)

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.

这篇关于如果单元格包含一个或多个关键字，请更改不同单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！