添加数字直到达到值 [英] Add Numbers Until Value Reached

问题描述

我想在一个单元格（A1）中有一个阈值，并将其作为添加单元格的参考。

假设我有

A1 - 10

A2 - 4

A3 - 2

A4 - 3 < br>
A5 - 4

A6 - 6

我想添加基于A1（阈值）的单元格。

由于A1为10，应添加A6：A5的单元格 - 结果：10

如果A1为6，则应返回单元格A6。结果：6

如果A1为16，则应添加来自A6：A3的单元格 - 结果：19

没有VBA，这是可能的吗？我可以通过总和来计算单元格的数量吗？

解决方案

我已经添加了一些进度 SUM 操作与 ROW 和 OFFSET 以下。请注意，我已修改并添加到您的样本数据中以获得更全面的结果。

C2中的 SUM 公式为 = 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 COUNT 在D2中导出， = MAX（INDEX（（SUBTOTAL（9，OFFSET（$ A $ 2，0,0，ROW（1:99），1））< $ A $ 1）* ROW（1:99）,,））+ 1 。 TBH，我没有对数据中的零进行实验，因为我不确定你是否想要进行计数。

I want to have a threshold value in one cell(A1) and take it as a reference for adding cells.

Suppose I have

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

Is this possible without VBA? Can i get count of number of cells in return along with sum?

解决方案

I've added some progression SUM operation with ROW and OFFSET for the following. Note that I have modified and added to your sample data for more thorough results.

      

The SUM formula 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 COUNT is 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.

这篇关于添加数字直到达到值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！