英文:
Using Office365 Excel array formulas, how can YTD totals be reset to zero for each new person?
问题
给定一个包含姓名、月份和每个月金额的列表，如何在不改变数据顺序的情况下计算每个人的年初累计总额？对于每个人，数据按月份顺序排列，该人的下一个记录是相同的月份或更晚的月份。
你可以看到我的公式是如何工作的，除了它将所有人的金额累加在一起。需要使年初累计金额特定于每个人（请参见期望结果部分）。
这个公式没有为每个人重置：
=LET(data,A4:C12, name,CHOOSECOLS(data, 1), amt,CHOOSECOLS(data, 3),
calc, LAMBDA(a, v, a + v),
ytdAmt, SCAN(0, amt, calc),
resp, HSTACK(name, ytdAmt),
resp )
数据如下：
| 数据 | | | | 期望结果 | |
|:-----:|:-----:|:------:|---|:-------:|:------:|
| | | | | | |
| 姓名 | 月份 | 金额 | | 姓名 | 年初累计 |
| Joe | 1 | 100 | | Joe | 100 |
| Joe | 2 | 100 | | Joe | 200 |
| Sandy | 2 | 300 | | Sandy | 300 |
| Julie | 3 | 400 | | Julie | 400 |
| Tom | 1 | 100 | | Tom | 100 |
| Sandy | 2 | 200 | | Sandy | 500 |
| Sandy | 2 | 100 | | Sandy | 600 |
| Tom | 5 | 100 | | Tom | 200 |
| Joe | 2 | 100 | | Joe | 300 |
使用Excel版本2304。
英文:
Given a list of names, months, and an amount for each month, how can a YTD total column be calculated for each person without changing the order of the data? For each person, the data is in month order where the next record for that person is the same month or a later month.
You can see where my formula works, except it accumulates everyone together. Need the YTD amount to be specific to the person (see the desired results section).
The formula is not resetting for each person:
=LET(data,A4:C12, name,CHOOSECOLS(data, 1), amt,CHOOSECOLS(data, 3),
calc, LAMBDA(a, v, a + v),
ytdAmt, SCAN(0, amt, calc),
resp, HSTACK(name, ytdAmt),
resp )
| Data | Desired Result | ||||
|---|---|---|---|---|---|
| Name | Month | Amt | Name | YTD | |
| Joe | 1 | 100 | Joe | 100 | |
| Joe | 2 | 100 | Joe | 200 | |
| Sandy | 2 | 300 | Sandy | 300 | |
| Julie | 3 | 400 | Julie | 400 | |
| Tom | 1 | 100 | Tom | 100 | |
| Sandy | 2 | 200 | Sandy | 500 | |
| Sandy | 2 | 100 | Sandy | 600 | |
| Tom | 5 | 100 | Tom | 200 | |
| Joe | 2 | 100 | Joe | 300 |
Using Excel version 2304.
答案1
得分: 1
以下是翻译好的内容：
尝试使用MAP()函数：
=LET(
x, A4:A12,
y, C4:C12,
HSTACK(
x,
MAP(
x,
y,
LAMBDA(a, b,
LET(
c, A4,
d, C4,
SUMIF(
a:c,
a,
b:d
)
)
)
)
)
)
=LET(_data,A4:C12,
_name,INDEX(_data,,1),
_amt,INDEX(_data,,3),
_ytdTotal,LAMBDA(_names,_amt,LET(_firstName,A4,_firstAmt,C4,SUMIF(_firstName:_names,_names,_amt:_firstAmt))),
VSTACK({"Name","Amt"},HSTACK(_name,MAP(_name,_amt,_ytdTotal))))
不使用硬编码的单元格地址，除了数据：
• 在单元格E4中使用的公式：
=LET(d, A4:C12,x, INDEX(d,,1),y,INDEX(d,,3),
HSTACK(x,MAP(x,y,LAMBDA(a,b,LET(c, INDEX(x,1),d, INDEX(y,1),SUMIF(a:c,a,b:d)))))
)
英文:
Try using MAP() Function:
=LET(
x, A4:A12,
y, C4:C12,
HSTACK(
x,
MAP(
x,
y,
LAMBDA(a, b,
LET(
c, A4,
d, C4,
SUMIF(
a:c,
a,
b:d
)
)
)
)
)
)
=LET(_data,A4:C12,
_name,INDEX(_data,,1),
_amt,INDEX(_data,,3),
_ytdTotal,LAMBDA(_names,_amt,LET(_firstName,A4,_firstAmt,C4,SUMIF(_firstName:_names,_names,_amt:_firstAmt))),
VSTACK({"Name","Amt"},HSTACK(_name,MAP(_name,_amt,_ytdTotal))))
Without hard-coding any cell addresses except the data:
• Formula used in cell E4
=LET(d, A4:C12,x, INDEX(d,,1),y,INDEX(d,,3),
HSTACK(x,MAP(x,y,LAMBDA(a,b,LET(c, INDEX(x,1),d, INDEX(y,1),SUMIF(a:c,a,b:d))))))
答案2
得分: 1
以下是翻译好的部分：
你可以尝试以下代码，它会输出完整的结果：
=LET(A,A2:A10, C,C2:C10, seq,SEQUENCE(ROWS(A)),
HSTACK(A, MAP(A,seq, LAMBDA(n,i,SUM(FILTER(C, (A=n) * (seq<=i)))))))
这是输出结果的图示：
在MAP的每次迭代中，它会根据当前名称（n）和所有先前的行来筛选列C的值，然后对它们求和。最后使用HSTACK来返回名称和计算结果。
英文:
You can try the following, it spills the entire result:
=LET(A,A2:A10, C,C2:C10, seq,SEQUENCE(ROWS(A)),
HSTACK(A, MAP(A,seq, LAMBDA(n,i,SUM(FILTER(C, (A=n) * (seq<=i)))))))
Here is the output:
On each iteration of MAP it filters the values of column C by the current name (n) and all previous rows, then sum it. Finally HSTACK to return the names and the calculation.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论