Excel:在文本中查找文本而不查找更长的文本(超字符串)

Excel:在文本中查找文本而不查找更长的文本(超字符串)

昨天我问这个问题,该问题的答案帮助我构建了用例/角色分析表。答案推动我继续前进,但又遇到了新的障碍。

我有一个用例包含大量信息的表。角色被输入到一列中,用户角色,以逗号分隔,示例值为BU Field, NBU Field, Contractor Field。角色的各个列都有公式,用于查看该角色(或“全部”)是否在用户角色1对于特定用例的列,如果是,则将值设置为,0如果不是,则将值设置为:

=IF(OR(ISNUMBER(FIND(R$1,$K2)),$K2="All"),1,0)

问题是,如果FIND()正在寻找BU FieldK2并且 的值是NBU Field,那么它就会找到它。我考虑过搜索BU Field(以空格开头),但这并没有考虑以 开头的值BU Field。我不明白ISNUMBER这里的函数是如何工作的(用谷歌搜索示例,按原样实现),所以不能真正把它分开来使用类似的东西LEFT()来得到正确的解决方案。

这就是我现在所拥有的:

R 年代
1 用户角色 波士顿大学球场 NBU 球场
2 波士顿大学球场 1 0
3 NBU 球场 1 1
4 BU 球场、NBU 球场 1 1
5 全部 1 1

[屏幕截图链接]

我想得到R3等于0,因为K3不包含BU Field本身。我该如何BU Field在字符串中查找可能包含或不包含的NBU Field内容而不返回误报?

理想情况下,我(或未来的其他用户)应该能够通过简单地将新角色输入到第 1 行和 K 列来添加新角色,而无需更改公式。

答案1

你走在正确的轨道上我想过寻找BU Field

您只需在搜索字符串的开头和结尾添加一个空格,并将逗号替换为space

=--IFERROR(--(FIND(" " & G$1 & " "," " & SUBSTITUTE($F2,","," ") & " "))>0,$F2="ALL")

在此处输入图片描述

答案2

这是你的工作公式

=IF(OR(ISNUMBER(FIND(R$1,$K2)),$K2="All"),1,0)

让我们分解一下

=IF(TEST,1,0)

您正在测试一个条件并返回一个1如果为真则返回的值和一个0如果不为真则返回的值。

您正在使用OR,因此有两种不同的方法可以满足您的测试,以逗号分隔。第一个:

FIND(R$1,$K2)

说“在 K2 中的字符串中查找 R1 中的字符串并给出它的位置。”FIND返回一个整数或错误。因此,

ISNUMBER(FIND(R$1,$K2))

是一个布尔测试,用于检查搜索字符串是否在搜索字符串内;如果未找到,FIND则返回错误并且ISNUMBER为 false。

第二次测试:

$K2="All"

够简单了。

因此,如果在“用户角色”中找到字段标题,或者“用户角色”为“全部”,则单元格中将显示 1。

结果如下:

在此处输入图片描述

@r2d3 给了你一个好主意,但你要求更多细节。以下是我的做法:

=IF(OR(ISNUMBER(FIND(SUBSTITUTE(R$1,"NBU","nonduplicated string"),SUBSTITUTE($K2,"NBU","nonduplicated string"))),$K2="All"),1,0)

在重复的字符串对中,取较长的一个(包含另一个)并将SUBSTITUTE其更改为函数中两个项中都不重复的内容FIND

在此处输入图片描述

答案3

检查“bird,fox,cat”中是否存在“cat”,但在“infield,pitcher,catcher”中找不到它,这是一个常见问题。常见的解决方案是添加“”(逗号)位于两个字符串的开头和结尾:““ (和 ”“ 和 ”) 出现在“鸟、狐狸、猫“ 但不是 ”内野手、投手、捕手”。 在你的情况下,我们必须使用“ “(逗号+空格),因为这是您的分隔符。

所以你的公式就变成了

=IF(OR(ISNUMBER(FIND(", "&R$1&", ", ", "&$K2&", ")), $K2="All"), 1, 0)

作为罗恩演示无需讨论,如果你想 从或 场景中获取1或 值,你不需要使用0TRUEFALSE

如果(逻辑值,1,0)
您可以使用
--逻辑值
Excel 函数中使用的“--”是什么意思?  因此公式可以简化为

=--OR(ISNUMBER(FIND(", "&R$1&", ", ", "&$K2&", ")), $K2="All")

答案4

要搜索“BU 字段”但排除“NBU 字段”内的匹配项,请进行一些字符串预处理:

将“NBU Field”替换为“”,然后搜索“BU Field”。这样你就不会得到误报。

2021 年 2 月 14 日 1:07 CET 对评论做出回应:

假设文本在 K1 中,我们通过将部分字符串“NBU Field”替换为“”来过滤它:L1=substitute(K1;“NBU Field”;“”)

如果您随后将 find 函数应用于 L1,则所有错误正例来源都已被删除。如果您愿意,您可以链接这两个函数。

相关内容