Question

我有一栏,其中有一些叙述。

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

关键词清单:

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.

我知道这一点在考试和测验局是可能的,甚至可以在<条码>上“在考生/密码>功能上”。但是,如果没有有条件的格式安排等条件性安排,这样做是可能的?

Answer 1

只要你对可能不适当地找到部分字句不 care,那就非常容易地采用公正的公式。第二次。第一,这里的公式将告诉你,在来源范围内,是否发现过任何几条插手:

=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"))))

土著和部落居民协会发现另一个人种的情况。当用阵列提出第一个论点时,它将恢复一系列立场(即“评估点!”)。如果发现搜查线。你们可以通过采用这一公式来追踪评价,然后在其中使用F9关键词:

=OR(NOT(ISERROR({#VALUE!,3,#VALUE!})))
=OR(NOT({TRUE,FALSE,TRUE}))
=OR({FALSE,TRUE,FALSE})
=TRUE

因此,举例来说,你说,你想用6美元(8美元)、你的工作笔记为2美元(3美元)和你的游戏笔记为2美元(3美元)。您可以提出公式

=OR(NOT(ISERROR(FIND(D$2:D$3,$B6))))

在D6室,把它作为阵容方案,然后通过D6:E8的范围加以拖.,以发现B中哪些人在其中工作或玩言。然后,你可以利用这些结果来推动进一步的公式化或有条件的安排。

然而,如上所述,你注意到,在搜捕区内发现任何潜水器,都会发现。

=OR(NOT(ISERROR(FIND({"a","b","c"},"bad"))))

将对西非经货联盟进行评估。 (如果你的名字包括“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.

Answer 2

我为少数国家集中了另一种方式。正如有人说的那样,这种情况迅速繁琐。

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

在B1单元中,有以下条目(复制件进入B2:B6):

=if(sum(iserror(find("cat",A1)),iserror(find("dog",A1)),iserror(find("fish",A1)),iserror(find("bird",A1)))<4,"House pet","")

<代码>B1和B5 <代码>B2、B3、B4和B5应改为“House pet”。

如果关键词清单过于庞大,则会十分谨慎。我也成功地利用了这一清单(如问题所暗示的),但是,如果时间太长,你没有多少空间来区分所有可能性,更不用说公式中的风险错误。

我无法得到我的阵列——因为我前面的那个职位现在会给我更长的名单上的这个尝试。

页: 1 不要忘记我与我的<代码>OR ISERROR阵列做些什么,但你今天的这个阵列为我的80个关键词清单提供了出色的工作。感谢!

友情链接