vba 添加数字直到达到价值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26274633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add Numbers Until Value Reached
提问by Mahesh Vemuri
I want to have a threshold value in one cell(A1) and take it as a reference for adding cells.
我想在一个单元格（A1）中有一个阈值，并将其作为添加单元格的参考。
Suppose I have
假设我有
A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6
A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6
I want to add cells based on A1(Threshold).
As A1 is 10, cells from A6:A5 should be added - Result:10
If A1 is 6 then cell A6 should be returned- Result:6
If A1 is 16 then cells from A6:A3 should be added - Result:19
我想添加基于 A1（阈值）的单元格。
由于 A1 为 10，应添加来自 A6:A5 的单元格 - 结果：10
如果 A1 为 6，则应返回单元格 A6 - 结果：6
如果 A1 为 16，则应添加来自 A6:A3 的单元格 - 结果：19
Is this possible without VBA? Can i get count of number of cells in return along with sum?
没有VBA这可能吗？我可以得到细胞数和总和的回报吗？
回答by CallumDA
You can use the INDIRECT()function.
您可以使用该INDIRECT()功能。
=SUM(INDIRECT("A6:A"&ROUNDDOWN(A1/2,0)))
for the count use
为计数使用
=COUNT(INDIRECT("A6:A"&ROUNDDOWN(A1/2,0)))
回答by CallumDA
I've added some progression SUMoperation with ROWand OFFSETfor the following. Note that I have modified and added to your sample data for more thorough results.
我已经添加了一些进展SUM操作与ROW和OFFSET理由如下：请注意，我已修改并添加到您的示例数据中以获得更全面的结果。
??????
??????
The SUMformula in C2 is =SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1))anf the COUNTis derived in D2 with =MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1. TBH, I didn't experiment much with zeroes in the data as I was unsure whether you would want to count them in the progression or not.
SUMC2 中的公式是=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1))anfCOUNT是在 D2 中导出的=MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1。TBH，我没有对数据中的零进行太多实验，因为我不确定您是否要在进程中计算它们。
回答by CallumDA
It's hard to hit a moving target but for your revised parameters try the following.
很难击中移动目标，但对于修改后的参数，请尝试以下操作。
=SUM(OFFSET($A$6,0-MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,)),0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1,1))?
=SUM(OFFSET($A$6,0-MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,)),0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1,1))?
=MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1
=MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1
While Excel expects to calculate in a 'down-and-to-the-right'progression, the OFFSET()function will accept parameters to both relocate the starting point and reshape the height and width of the range of cells being summed. Generally, working 'down-and-to-the-right'will allow to leave some breathing room for expansion but you will have to be very careful that you do not attempt to move upwards past row 1 (#REF!error).
虽然 Excel 期望以“向下和向右”的进程进行计算，但该OFFSET()函数将接受参数以重新定位起点并重塑要求和的单元格范围的高度和宽度。通常，“向下和向右”工作将允许为扩展留出一些喘息空间，但您必须非常小心，不要试图向上移动超过第 1 行（#REF!错误）。
The formula could be simplified if there was any guarantee that nothing of numerical value was ever going to be below A6 but that point has not been addressed so my formulas halt the sum operation at A6.
如果有任何保证任何数值都不会低于 A6，则可以简化公式，但尚未解决这一点，因此我的公式停止了 A6 处的求和运算。
