英文:
Dynamic rolling sum in excel
问题
如何根据另一列计算列的滚动总和？
示例：
输入数据是下表中的前3列。我想要获取的结果列在绿色高亮显示。我想根据给定的相应“每日销售”来获取动态总和。
英文:
How can I calculate the rolling sum of a column based on another column?
Example:
Input data are the first 3 columns in the below table. The result column that I am trying to get is highlighted in green. I want to get the dynamic sum of 'Daily sales' based on the corresponding 'No of days' given.
答案1
得分: 2
以下是您要翻译的内容：
"Assuming there is sample data missing, hence the lower rows will yield different results, here is yet another alternative:
Formula in D1:
=MAP(B1:B12,C1:C12,LAMBDA(x,y,SUM(x:INDEX(B:B,ROW(x)+y-1))))
Or just don't use LAMBDA() at all, but a simple SUMIFS():
=SUMIFS(B1:B12,A1:A12,">="&A1:A12,A1:A12,"<="&A1:A12+C1:C12-1)
英文:
Assuming there is sample data missing, hence the lower rows will yield different results, here is yet another alternative:
Formula in D1:
=MAP(B1:B12,C1:C12,LAMBDA(x,y,SUM(x:INDEX(B:B,ROW(x)+y-1))))
Or just don't use LAMBDA() at all, but a simple SUMIFS():
=SUMIFS(B1:B12,A1:A12,">="&A1:A12,A1:A12,"<="&A1:A12+C1:C12-1)
答案2
得分: 1
这个公式有效。将'OFFSET'函数放入'SUM'函数中以输入动态数组。
英文:
This formula worked. Add the 'OFFSET' function inside the 'SUM' function to input the dynamic array.
答案3
得分: 1
=如果您使用Microsoft-365，则可以尝试以下公式以获取动态溢出结果。
=MAP(C2:C13,LAMBDA(x,SUM(OFFSET(x,0,-1,x))))
英文:
If you are on Microsoft-365 then could try the following formula for dynamic spill results.
=MAP(C2:C13,LAMBDA(x,SUM(OFFSET(x,0,-1,x))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论