计算出售存货/股票的利润/资本收益 [英] Calculate Profit/Capital Gains on Sold Inventory/Shares
问题描述
我有一张Google表格(适用Excel公式)，其中包含2000笔交易，其结构如下：
Link to Google Sheet with example calcs here.
我需要计算利润(即FIFO资本收益)，并将此金额放在第5栏(如图所示)。这既可以使用传统公式实现，也可以使用Google App脚本实现。最好是在Google Sheets中。
我发现很难通过公式将卖出订单与买入相匹配，因为有些卖出订单会被拆分到多个买入行中。如果有人能提供撞击这个问题的公式或解决方案，我们将不胜感激。This is an updated question to the original here.
推荐答案
初步上下文
下面的屏幕截图指的是：
Google Sheetshere
重要更新：Re：2000多行，请参阅我上一条评论中的链接(对于OneDrive模板，s.t.过期，此建议书底部有错误&太大而无法导入的屏幕截图) 仅供参考：(Office 365-Excel-FNS比如‘过滤’等，所以#名字！打开此链接后将无处不在；不过，共享此链接应可作为在正确版本的Excel中复制上述屏幕截图的方便方法)要求
- Office 365
- 右侧有足够的空间或能够根据需要使用额外的纸张进行计算。
方法
高级|外卖
·依赖于另外两个表：数量"耗尽"表(此处-G：P)和相应表。利润向量(此处-R：Z)·可以探索压缩表示法(2x2表，每个汇总表，此处：第19：32行)以简化问题(尽管这些仍在进行中[‘WIP’])
·VB也可能是有吸引力的替代方案
低级|过程性
- 如果没有异常复杂的情况，无法找到单个单元格函数(但是，我怀疑这是可能的，例如使用FilterXML？)
- 数量标签调换过滤负数(灰色阴影，G3：J3)
- 这些值依次耗尽(从左到右，即依赖于数据按日期升序排序(在此图中，数据首先按‘水果’排序，因为这些数据在利润计算的上下文中假定是独立的)。
- 例如，-5数量(G3)偏移+10(B3)得到+5，而+5又导致-35数量(H3)得到-30。不能进行进一步的贡献(从左到右)；下一行(5)：+20有效(给定)，不抵消-5(G3，已"报销"，因此抵消20抵消-30(H4)以获得-10收益率，以此类推。
- 为容纳更长的水果/利润列表而设置的工作表
- 应用于差价的剩余‘数量’(~G：J)结果表，产生‘利润向量’(R：U)，再与相应的负数相加，得出利润得分(黄色阴影单元格)
函数
突出功能(所有功能都应该在Google Sheet中提供，但为了完整起见)：
1)利润计算。(E4，向下拖动)：=IF(B4<0,SUM(INDEX($R$4:$AA$13,0,MATCH(A4&C4,$R$2:$AA$2&$R$3:$AA$3,0))),"")
可以根据需要向下/向右拖动(在本例中，转到：z13)
2)数量表-内容(G4)
=IF($A4=G$2,IF(G$3<>"",IF(G$2=E$1,IF($B4<0,IF(G3<0,G3,0), IF(E4<0,0,E4)+IF(G3<0,G3,0)), IF(G$3<>"", IF($B4<0,IF(G3<0,G3,0),$B4 + IF(G3<0,G3,0))))),G3)
3)数量表头(G2、G3)
=MID(TRANSPOSE(FILTER(A4:A13&B4:B13,B4:B13<0)),1,SEARCH("-",TRANSPOSE(FILTER(A4:A13&B4:B13,B4:B13<0)))-1)
=-1*MID(TRANSPOSE(FILTER(A4:A13&B4:B13,B4:B13<0)),SEARCH("-",TRANSPOSE(FILTER(A4:A13&B4:B13,B4:B13<0)))+1,LEN(TRANSPOSE(FILTER(A4:A13&B4:B13,B4:B13<0))))
4)利润表-内容(R4，向下/向右拖动)
=IF(R$2<>"",IF(G3>0,0,IF(G4>0,ABS(G3),G4-G3))*(R$3-$C4),"")
5)利润表头(R2、R3)：
=MID(TRANSPOSE(FILTER(A4:A13&"-"&$C$4:$C$13,B4:B13<0)),1,SEARCH("-",TRANSPOSE(FILTER(A4:A13&"-"&$C$4:$C$13,B4:B13<0)))-1)
=1*MID(TRANSPOSE(FILTER(A4:A13&"-"&$C$4:$C$13,B4:B13<0)),SEARCH("-",TRANSPOSE(FILTER(A4:A13&"-"&$C$4:$C$13,B4:B13<0)))+1,LEN(TRANSPOSE(FILTER(A4:A13&"-"&$C$4:$C$13,B4:B13<0))))
杂项
- 派生和探索的替代解决方案包括三角形框架 (第17-32行)。
- 这些表格列出了值ASSOC之间的交互作用。 有正负量正/负量 (和线芯。日期)
- 需要注意的是，这些都是不必要的(就功能而言，前一个‘节’
继续按照以前的思路.
6a)第一个汇总表(数量)：行标题和列标题(分别按A22键向下拖动，C19键向右拖动)：
=FILTER(D4:D13,1*IF($A$21="*",1,(A4:A13=$A$21))*(B4:B13<0))
=TRANSPOSE(FILTER(D4:D13,1*IF($A$21="*",1,IFERROR((A4:A13=$A$21),1))*(B4:B13>0)))
6b)第一个汇总表(数量)：运行合计(c19，根据需要向下/向右拖动，本例中：到p19)
=SUM($C21:C21,$B22#)
6c)第一个汇总表(数量)：内容(c22，可根据需要拖下或今晚)
=IF(OR(C$20="",$A22=""),"",IF($A22>C$20,MAX(0,C21+MIN(0,SUM($B22:B22))),C21))
7)第二汇总表(利润)：c29，不可拖拽
=MIN(C$21,C$21+$B22)*($B29-C$28)
注意；第二个汇总表(行/列标题)的其他功能紧跟在第一个之后。此外，在水果选择单元格(A21)中放置‘*’将返回ALL(如前所述，这仍是在制品)。
更大上传的屏幕截图：
这篇关于计算出售存货/股票的利润/资本收益的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
