为什么这个过滤函数返回值错误？
在下面的公式中，#Value!返回错误。
=FILTER(FIND(";"&AS4756:AS4762&{6,7},AG4756),ISNUMBER(FIND(";"&AS4756:AS4762&{6,7},AG4756)))
其中 ag4756 是 8034000;Y8033343543543;Y;38918;BS7 9XL;9;Male;N;N;N
， AS4756:AS4762 是
BS
NP
SN
GL
BA
CF
TA
当我选择评估公式时，它返回字符串 BS7 的数字 31 ，以及所有其他错误。我试图从结果中过滤掉错误，所以我只得到不是错误的结果。对于 BS7，它也正确返回 TRUE，对于所有其他，它返回 false。但下一步会导致错误。有人可以帮忙吗？
编辑：我想返回以 BS7 开头的子字符串的位置。或者BS6，或者NP6，或者NP7，等等。
In the formula below, a #Value! error is returned.
=FILTER(FIND(";"&AS4756:AS4762&{6,7},AG4756),ISNUMBER(FIND(";"&AS4756:AS4762&{6,7},AG4756)))
Where ag4756 is 8034000;Y8033343543543;Y;38918;BS7 9XL;9;Male;N;N;N
and AS4756:AS4762 is
BS
NP
SN
GL
BA
CF
TA
When I chose evaluate formula, it returns the number 31 for the string BS7, and errors for all others. I'm trying to filter out the errors from the result, so I only get the result which is not an error. It also correctly returns TRUE for BS7, and false for all the others. But then the next step results in the error. Can anyone help?
edit: I want to return the position of the substring that starts with BS7. Or BS6, or NP6, or NP7, and so on.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好了，希望以下内容对您有所帮助；我们假设数据（垂直）
{BS;NP;SN;GL;BA;CF;TA}位于A1:A7中，查找字符串位于 <代码>B1。然后，您连接第二个（水平）数组{6,7}，它将构成以下矩阵：现在在
B1使用FIND(";"&A1:A7&{6,7},C1)将导致：下一步是收集数字价值。但是，对于第二个参数，您提供了一个 7*2 矩阵，而不是一维垂直或水平值数组。因此，
FILTER()将在该步骤上出错，返回“#VALUE!”！即便如此，如果您尝试使用MMULT()技巧来正确检索此数组，您仍然需要处理您试图从填充的第一个参数中过滤矩阵的事实有错误。因此，我已经展示了如何从一个相对简单的公式中获取位置，该公式也可以处理错误：并且要获取正确的子字符串，请使用：
现在这给我们留下了您想要更多潜在子字符串的可能性被退回。如果是这样，请在评论中告诉我。
Alright, I hope the following helps you out a bit; let's assume data (vertical)
{BS;NP;SN;GL;BA;CF;TA}to be inA1:A7and the lookup-string to be inB1. You are then concatenating a 2nd (horizontal) array{6,7}which would make up the following matrix:Now to find these values in
B1usingFIND(";"&A1:A7&{6,7},C1)would result in:The next step for you would be to gather the numeric value. However, for the 2nd parameter, you have provided a 7*2 matrix and not a 1D-vertical or horizontal array of values. Therefor
FILTER()will error out on exactly that step, returning the '#VALUE!'! And even so, if you would try to useMMULT()tricks to retrieve this array correctly you'd still need to deal with the fact you are trying to filter a matrix from the 1st parameter that is filled with errors. Therefor, I've shown how you could get the position from a relative easy formula that would deal with errors too:And to get the correct substring, use:
Now that leaves us with the probability you have more potential substrings you'd like to be returned. If that is the case, let me know in the comments.