在有条件的情况下累计总和达到一定值时查找行 [英] Find row when Cumulative sum reaches certain value with condition

问题描述

我有一个包含3列日期，项目和编号的表格.每行表示在该日期收到了每个项目的多少个项目.我正在尝试查找该月累计金额达到100或更多的日期.每个月都有一个针对每个项目的目标，该目标将保存在另一张工作表中，但为简单起见，我们可以假定其固定数量为100.

I have a table with 3 columns Date, Item and Number. Each row indicates how many items of each Item was received on that date. I am trying to find the date on which cumulative sum reaches 100 or more in that month. Each month will have a target for each item which will be saved in another sheet but for simplicity we can assume that its a fixed number of 100.

Example Data:
Date       Item        Number
1/2/2018   A           10
2/2/2018   B           10
2/2/2018   A           15
5/2/2018   C           25
6/2/2018   A           50
7/2/2018   B           10
7/2/2018   C           10
8/2/2018   A           25
9/2/2018   A           20

我正在寻找一个公式，该公式应该对与上述类似的数据起作用，并将结果显示为8/2/2018，这是日期A的累计金额达到100的日期.

I am looking for the formula which should act on the data similar to above and give the result as 8/2/2018 which is the date on which the cumulative sum for Item A reached 100.

每个月将有不同的目标编号，并且将有不同的条目数.

Each month will have different target number, and will have different number of entries.

我尝试使用SUMIF并添加其他列等，但是此数据只是大数据的一部分，每个项目的限制都保存在不同的工作表中，因此不易合并.预先感谢您的帮助.

I have tried using SUMIF and adding a additional column etc but this data is just part of a big data and each item limit is saved in a different sheet etc which is not easy to merge. Thanks in advance for help.

推荐答案

仅在Excel中，您可以使用Offset开发包含1,2,3 ... 9行的项目和数字的数组，然后使用SUMIF来添加每行他们中的.然后使用匹配"找到第一个= 100，然后使用索引"找到匹配日期(在F2中):

In Excel only, you can use Offset to develop an array of items and numbers containing 1,2,3...9 rows and then SUMIF to add each of them up. Then use Match to find the first one =100 and Index to find the matching date (in F2):

=INDEX(A2:A10,MATCH(100,SUMIF(OFFSET(B2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)),"A",OFFSET(C2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1))),0))

必须使用 Ctrl Shift Enter 输入为数组公式.

Must be entered as an array formula using CtrlShiftEnter.

编辑

要找到第一个总和> = 100(在G2中):

To find the first sum which is >=100 (in G2):

=INDEX(A2:A10,MATCH(TRUE,SUMIF(OFFSET(B2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)),"A",OFFSET(C2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)))>=100,0))

总和恰好为100

总和未精确到100(第9行中的数字已更改为24):

Sum which does not reach exactly 100 (the number in row 9 has been changed to 24):

这篇关于在有条件的情况下累计总和达到一定值时查找行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！