匹配第n个实例 [英] Match nth instance
问题描述
我发现了一些使用VBA或CSE公式的示例,但是我尝试的任何CSE公式似乎都不起作用。但是由于对我的约束,我无法使用VBA或CSE,所以我从来没有看过太深。
这是与第一个实例匹配的公式:$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ,0))
这是在单元格A4中;我想A5:A11匹配下面的六个实例。
这是否可能在香草配方中?
这可以使用数组功能(Ctrl + Shift + Enter)完成,但我个人不喜欢使用这些功能。
我的建议将是以下代码:
A4 = INDEX($ A $ 13:$ A $ 5000,MATCH(start *, $ A $ 13:$ A $ 5000,0))
B4 = MATCH(start *,$ A $ 13 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ B4.0),MATCH(start *,OFFSET($ A $ 13:$ A $ 5000,B4,0),0))
并将其复制到A11
B5 = MATCH(start *,OFFSET($ A $ 13:$ A $ 5000,B4, 0),0)+ B4
将其复制到B11
基本上这个推搜索区域的数量等于上一个匹配的总和,然后找到下一个匹配。
I have an array of data in a spreadsheet that I want to automagically pull the first seven instances that start with a specific string to seven independent cells so it can easily be seen what the full cell contents are. I have composed a formula that successfully finds the first occurrence but I can't find a way to extend this to the second, third etc. I have thought of using the previous cell's match as input to the next cell, but everything I've tried doesn't seem to work.
I have found some examples that use VBA or CSE formulas, but any CSE formulas I have tried don't seem to work. But due to constraints imposed on me I can't use VBA or CSE anyway so I never looked too deeply in to it.
Here is the formula that matches the first instance:
=INDEX($A$13:$A$5000,MATCH("start*",$A$13:$A$5000,0))
This is in Cell A4; I would like A5:A11 to match the next six instances.
Is this possible in a vanilla formula?
This can be done using an "array" function (Ctrl + Shift + Enter) but I personally do not like using those.
My suggestion would be the following instead:
A4 =INDEX($A$13:$A$5000,MATCH("start*",$A$13:$A$5000,0))
B4 =MATCH("start*",$A$13:$A$5000,0)
A5 =INDEX(OFFSET($A$13:$A$5000,B4,0),MATCH("start*",OFFSET($A$13:$A$5000,B4,0),0))
And copy this down to A11
B5 =MATCH("start*",OFFSET($A$13:$A$5000,B4,0),0)+B4
And copy this down to B11
Basically this "pushes" the search area down an amount equal to the sum of the previous matches, and then finds the next match.
这篇关于匹配第n个实例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!