在指定位置匹配一组字符串中的字符?

huangapple go评论55阅读模式
英文:

Matching a character in a range of strings at specified positions?

问题

我想要一次性检查一整个字符串范围,以查看它们是否包含用户指定的位置上的字母Y。

我的数据格式如下:

Y____Y_Y_Y
_Y__Y_____
Y_Y_Y_Y___
...

这些都是具有完全相同长度的字符串,但Y和_可以出现在任何位置。

我希望检查这些字符串,看看在特定位置是否有Y。我有一个复选框来决定是否要检查该位置或不检查。

位置 复选框
1 TRUE
2 FALSE
3 TRUE
4 FALSE
5 FALSE
6 TRUE

然后,我想要检查在这个示例中字母Y是否会出现在位置1、3和6。

我能够逐行检查它们是否在指定位置包含Y,使用以下公式:
=IFERROR(FIND("Y",ArrayFormula(JOIN("",IFERROR(MID(D2,$F$2:$F,1),))))>0,FALSE)

D2包含要检查的单个字符串。
$F$2:$F包含要提取的要检查的位置。
MID获取指定位置上的所有字母,然后连接成一个字符串。
然后检查该字符串是否包含任何Y,如果是则返回TRUE。

我的问题是,我不想将公式拖动到下面的单元格中,以便逐个检查每个字符串,我希望一次性完成。将MID公式更改为D2:D25这样的范围不起作用,因为我认为MID不能同时检查多个字符串和多个位置。

有没有一种方法可以从整个范围中提取指定位置上的字母并检查它们是否包含字母"Y"呢?

提供的测试电子表格供参考

英文:

I'm wanting to check an entire range of strings in one go, to see if they contain the letter Y at specific positions decided by the user.

I have data in the format:

Y____Y_Y_Y
_Y__Y_____
Y_Y_Y_Y___
...

These are all Strings of the exact same length, but the Y's and _'s can be in any position.

I wish to check these Strings and see if there are Y's at specific positions. I have a tickbox on whether to check that position or not.

Position Tickbox
1 TRUE
2 FALSE
3 TRUE
4 FALSE
5 FALSE
6 TRUE

I then want to check if the letter Y would appear at position 1, 3 and 6 in this example.

I am able to check one line at a time to see if they contain a Y at any of the specified positions using the formula:
=IFERROR(FIND("Y",ArrayFormula(JOIN("",IFERROR(MID(D2,$F$2:$F,1),))))>0,FALSE)

D2 contains the single string to check.
$F$2:$F contains the extract positions to check.
The MID takes all the letters at the specified positions, which is then joined into 1 string.
This string is then checked to see if it contains any Y's, returning TRUE if so.

My problem is that rather than dragging down the formula onto the cells below, so it checks each string individually, I wish to do this in one line. Simply changing the MID formula to be a range for D2:D25 for example does not work, as I believe MID cannot check both multiple strings and multiple positions at once.

Is there a way to extract letters at specified positions and check whether they contain a letter "Y" from an entire range?

Provided test spreadsheet for reference

答案1

得分: 1

你的公式无法正常工作的原因是因为是因为 JOIN 函数,而不是 MID 函数。如果一个函数默认接受数组,那么当与 ArrayFormula 一起使用时,它不会按你的预期工作。

尝试使用以下公式代替:

=ARRAYFORMULA(IF(D2:D="",,0<MMULT(N("Y"=MID(D2:D,TOROW(F2:F,1),1)),N(TOCOL(F2:F,1))^0)))
英文:

The reason your formula doesn't work it's because of the JOIN function not the MID function. If a function takes arrays by default, it won't work as you would expect when used with ArrayFormula.

Try this instead:

=ARRAYFORMULA(IF(D2:D=&quot;&quot;,,0&lt;MMULT(N(&quot;Y&quot;=MID(D2:D,TOROW(F2:F,1),1)),N(TOCOL(F2:F,1))^0)))

答案2

得分: 0

=map(D2:D,lambda(Σ,if(Σ="",,countif(index(mid(Σ,tocol(F2:F,1),1)),"Y")>0)))

英文:

You may try:

=map(D2:D,lambda(Σ,if(Σ=&quot;&quot;,,countif(index(mid(Σ,tocol(F2:F,1),1)),&quot;Y&quot;)&gt;0)))

在指定位置匹配一组字符串中的字符?

huangapple
  • 本文由 发表于 2023年6月15日 06:24:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76477948.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定