Google Sheets – 将单元格的值相加,直到达到X或Y值,然后返回X或Y的答案

huangapple go评论84阅读模式
英文:

Google Sheets - Add value of cells until it reaches X or Y values, then return answer for X or Y

问题

我正在使用Google表格,尝试弄清楚如何编写难以解释的逻辑。

第1列开始从第1行到第5行编译总和计数;如果总和 >= X,则应输出"成功";如果总和 <= Y,则应输出"失败"。

例如,从单元格第1行、第01列开始,直到总和达到 >= "4"。如果达到4,总和应停止并输出"成功",如果总和达到 <= -1,则应停止并输出"失败"。

我想将此应用于数据表中的许多不同行。希望你能理解我的意思。谢谢。

     第01列
1    1            
2    2
3    -3 
4    4
5    -2
英文:

I'm using Google Sheets and I'm trying to figure out how to write the logic for something difficult to explain.

Col 1 starts to compile a sum count from rows 1-5; If the sum is >= X it should output "Success"; If the sum <= Y it should output value "Fail".

For example, sum cells Row 1, Col 01 -> Row 5, Col 1 until sum reaches >= "4". If it reaches 4, the summing should stop and output "Success" AND if summing results in reaching <= -1 it should stop and output "Fail".

I'd like to apply this to many different rows within the data table. Hopefully you can understand where I'm going with this. Thanks.

     Col 01
1    1            
2    2
3    -3 
4    4
5    -2         

答案1

得分: 1

SCAN允许你运行累积和。例如:

=SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, a+v))

它返回累积总和的值,因为它将先前的值(a)与新值(v)相加。现在你只需要添加IF语句:

=SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, IF(a+v>=4, "成功", IF(a+v<=-1, "失败", a+v))))

现在,在成功或失败之后,累积总和将停止,因为不能将其添加到字符串中。这就是为什么你可以使用IFERROR来清除这些值,并且如果你想要精确缩小结果到那些单元格,还可以使用TOCOL:

=TOCOL(IFERROR(SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, IF(a+v>=4, "成功", IF(a+v<=-1, "失败", a+v))))), 1)

所以,只需要一个公式在B2中,你可以得到这个结果:

英文:

SCAN lets you run cumulative sums. For example:

=SCAN(0, TOCOL(A2:A,1),LAMBDA(a,v,  a+v ))

It returns the value of the cumulative sum, because it sums the previous value (a) with the new one (v). Now you only need to add the IF statements:

=SCAN(0,TOCOL(A2:A,1),LAMBDA(a,v,
IF(a+v&gt;=4,&quot;Success&quot;,IF(a+v&lt;=-1,&quot;Fail&quot;,a+v))))

Now, after Success or Fail, the cumulative sum will stop since it's not possible to add to a string. That's why you can add IFERROR to empty those values, and TOCOL optionally if you want to narrow the results exactly to those cells:

=TOCOL(IFERROR(SCAN(0,TOCOL(A2:A,1),LAMBDA(a,v,IF(a+v&gt;=4,&quot;Success&quot;,IF(a+v&lt;=-1,&quot;Fail&quot;,a+v))))),1)

So, with only one formula in B2 you get this result:

Google Sheets – 将单元格的值相加,直到达到X或Y值,然后返回X或Y的答案

答案2

得分: 0

这绝对是一个冗长的方法,但这是我的答案:
在A列,您放置所有数据。
在B1中,输入以下内容:(根据数据范围更改A1:A5)

=MAP(A1:A5, LAMBDA(val, IF(CELL("row", val)=1,A1,SUM(INDIRECT("A1:"&ADDRESS(IF(CELL("row", val) - 1<1, 1, CELL("row", val) - 1),CELL("col", val),4))))))

这基本上在B列中放入了列A中单元格上方所有内容的总和。
在下一部分中,假设单元格D2是最小值,E2是最大值。
在C列中,C1将是:

=IF(B1>=E2,"SUCCESS",IF(B1<=D2,"FAIL",""))

这将在满足条件时将其设置为SUCCESS和FAIL。
在列C的所有后续行中,输入以下内容:

=IF(INDEX(A:Z,ROW()-1,COLUMN())="",IF(INDEX(A:Z,ROW(), COLUMN()-1)>=E2,"SUCCESS",IF(INDEX(A:Z,ROW(), COLUMN()-1)<=D2,"FAIL",""))," ")

这与第一列相同,但如果上方的单元格已经这样做,就不会将单元格设置为成功或失败。
可选地,在不同的单元格中,您可以输入:(根据数据范围更改C1:C5)

=SWITCH(SUM(MAP(C1:C5,LAMBDA(val, SWITCH(val, "SUCCESS", 1, "FAIL", -1, "", 0, " ", 0, 2)))),1,"SUCCESS", 0, "", -1,"FAIL", "ERROR")

这告诉您是否有任何标记为成功或失败。
这是最终产品:Google Sheets – 将单元格的值相加,直到达到X或Y值,然后返回X或Y的答案

英文:

This is definitely the long way to do it, but here is my answer:
In column A, you put all your data.
In B1, put in this: (change A1:A5 based of range of data)

=MAP(A1:A5, LAMBDA(val, IF(CELL(&quot;row&quot;, val)=1,A1,SUM(INDIRECT(&quot;A1:&quot;&amp;ADDRESS(IF(CELL(&quot;row&quot;, val) - 1&lt;1, 1, CELL(&quot;row&quot;, val) - 1),CELL(&quot;col&quot;, val),4))))))

This essentially puts in column B the sum of everything above the cell in column A.
In this next part, say that cell D2 is the minimum value, and E2 is the maximum value.
In column C, C1 is going to be:

=IF(B1&gt;=E2,&quot;SUCCESS&quot;,IF(B1&lt;=D2,&quot;FAIL&quot;,&quot;&quot;))

this sets it to SUCCESS and FAIL if the conditions are met.
On all the subsequent rows in column C, enter in:

=IF(INDEX(A:Z,ROW()-1,COLUMN())=&quot;&quot;,IF(INDEX(A:Z,ROW(), COLUMN()-1)&gt;=E2,&quot;SUCCESS&quot;,IF(INDEX(A:Z,ROW(), COLUMN()-1)&lt;=D2,&quot;FAIL&quot;,&quot;&quot;)),&quot; &quot;)

This does the same as the first column, but does not set the cell to success or fail if one above has already done so.
Optionally, in a different cell you can put: (change C1:C5 based on range of data)

=SWITCH(SUM(MAP(C1:C5,LAMBDA(val, SWITCH(val, &quot;SUCCESS&quot;, 1, &quot;FAIL&quot;, -1, &quot;&quot;, 0, &quot; &quot;, 0, 2)))),1,&quot;SUCCESS&quot;, 0, &quot;&quot;, -1,&quot;FAIL&quot;, &quot;ERROR&quot;)

This tells you if any of them at all got labeled as success or fail.
Here the final product: Google Sheets – 将单元格的值相加,直到达到X或Y值,然后返回X或Y的答案

huangapple
  • 本文由 发表于 2023年8月5日 09:39:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76839820.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定