在范围内找到求和结果的行数 [英] Find row number of sum result in range
问题描述
我在以下事情上伤脑筋:
I'm breaking my head on the following:
我将 15
作为 B1
在 A1:A4
中,每个单元格的值为 5
.(我确实这些都是不同的值).
In A1:A4
for each cell the value is 5
. (I real these are all different values).
我希望excel逐一求和 A1:A4
的值,如果该总和达到搜索值,则返回行号.因此,对于此示例,我想要结果3(= 5 + 5 + 5)如果没有精确匹配,则返回小于搜索值的最接近匹配.
I want excel to SUM the values of A1:A4
one by one and if the search value is reached by that sum, return the row number. So for this example I want result 3 (=5+5+5)
If no exact match then return the closest match smaller than the search value.
我尝试了类似 = MATCH(1,B1 = SUM(A1:A4),1)
的操作这显然行不通,但是我不知道如果没有VBA,这是否有可能.有人知道一种方法(没有帮助器列).
I tried something like =MATCH(1,B1=SUM(A1:A4),1)
which doesn't work obviously, but I don't know if it's even possible without VBA.
Does anybody know a way (without helper column).
推荐答案
您可以尝试:
=MATCH(TRUE,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4)))>=B1,0)
我正在使用Excel O365,但是任何其他版本都可能需要上述内容的CSE输入(我不确定).请注意 OFFSET()
是易失的!
I'm using Excel O365, but any other version may need CSE-entering of the above (I'm unsure). Do note OFFSET()
is volatile!
上面的代码将返回累积总和第一个> = B1
的行.如果根据您的问题的目标是获得最接近的匹配但小于 B1
,以防不存在完全匹配:
The above would return the row where the cumulative sum first >=B1
. If your goal as per your question was to get the closest match but smaller than B1
in case there is no exact match:
=MATCH(B1,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4))))
这篇关于在范围内找到求和结果的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!