英文:
Return last value based on date with criteria on google sheets
问题
这是表格的内容:
ColA=日期,ColB=代码 #,ColC=总金额,ColD=截至目前的金额,ColE=累计金额
我想要实现的是根据最新日期,将Column D的值放入Column E;但ColB的值应保持不变。
我尝试使用INDEX + MATCH + MAX,但有些东西丢失了,我无法指出。
我在这里分享表格链接,你可以查看。
我还在每个单元格旁边放了一些注释,这可能会有所帮助。谢谢!
链接:https://docs.google.com/spreadsheets/d/1YADVkKPNp0TmSVtz6X9EtgqAnTNTocNBWFwtQzt5okE/edit?usp=sharing
英文:
Here are the contents of my table:
ColA= date, ColB=Code #, ColC=Whole Amt, ColD=Amt as of now, ColE=Accumulated Amt
What i want to happen is to return the value from Column D and put it in Column E based on the latest date; however, the value of ColB value should be the same.
I tried using INDEX + MATCH + MAX, but something is missing and I can't point it out.
I am sharing here the link of the sheet so you can explore.
I also put some comments beside each cell as it may help. Thank you!
https://docs.google.com/spreadsheets/d/1YADVkKPNp0TmSVtz6X9EtgqAnTNTocNBWFwtQzt5okE/edit?usp=sharing
答案1
得分: 1
你可以尝试:
=map(B2:B,D2:D,lambda(Σ,Λ,if(Σ="",,if(countif(B1:Σ,Σ)=1,0,sumif(B1:Σ,Σ,D1:Λ)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论