嗨，我明白你的问题了——你想计算持仓股票的平均买入成本，但之前的公式把已经卖掉的股票也算进去了，导致结果出错对吧？

为什么你的原有公式不对 #

你用的公式：

=AVERAGE.WEIGHTED(FILTER(E2:E; B2:B="buy"; C2:C="GEM"); FILTER(D2:D; B2:B="buy"; C2:C="GEM"))

是把所有GEM的买入记录都纳入了加权平均计算，包括你已经卖出的那1000股（成本$100）。按这个公式算出来的结果是(100×1000 + 50×1000 + 40×1000 + 30×1000)÷4000 = 55，自然和你想要的结果不符。核心问题是没剔除已经卖出的份额对应的成本。

正确的解决方案：基于先进先出（FIFO）的平均成本计算 #

股票交易中通常用先进先出原则计算持仓成本（即先买的股票先卖），我们可以用这个逻辑来构建公式，自动剔除已卖出份额的成本：

=(SUMIFS(E2:E*D2:D, B2:B="BUY", C2:C="GEM") - SUMPRODUCT(
  FILTER(D2:D, B2:B="SELL", C2:C="GEM"),
  XLOOKUP(
    SEQUENCE(SUMIF(B2:B,"SELL",C2:C="GEM")),
    SCAN(0, FILTER(D2:D, B2:B="BUY", C2:C="GEM"), LAMBDA(a,b,a+b)),
    FILTER(E2:E, B2:B="BUY", C2:C="GEM"),
    ,1
  )
)) / (SUMIFS(D2:D,B2:B="BUY",C2:C="GEM")-SUMIFS(D2:D,B2:B="SELL",C2:C="GEM"))

公式拆解：

1. 总买入成本：SUMIFS(E2:E*D2:D, B2:B="BUY", C2:C="GEM") 计算所有GEM买入的总成本（你的例子中是$220,000）。
2. 卖出部分对应的成本：
   • FILTER(D2:D, B2:B="SELL", C2:C="GEM") 获取所有GEM卖出的股数（你的例子中是1000股）。
   • SCAN(...) 计算每笔买入后的累计股数（你的例子中是[1000, 2000, 3000, 4000]）。
   • XLOOKUP(...) 按先进先出原则，匹配卖出股数对应的买入价格（你的例子中卖出的1000股对应最早的$100成本）。
   • SUMPRODUCT 算出卖出部分的总成本（你的例子中是$100,000）。
3. 持仓总成本：总买入成本减去卖出部分成本（你的例子中是$220,000 - $100,000 = $120,000）。
4. 持仓股数：总买入股数减去总卖出股数（你的例子中是4000 - 1000 = 3000）。
5. 平均成本：持仓总成本 ÷ 持仓股数（你的例子中是$120,000 ÷ 3000 = $40）。

简化版（仅针对你的示例场景） #

如果你的交易记录只有一笔卖出，且是完全卖出最早的买入份额，也可以用更简单的公式：

=(SUMIFS(E2:E*D2:D,B2:B="BUY",C2:C="GEM",ROW(E2:E)>ROW(FILTER(A2:A,B2:B="SELL",C2:C="GEM"))))/SUMIFS(D2:D,B2:B="BUY",C2:C="GEM",ROW(E2:E)>ROW(FILTER(A2:A,B2:B="SELL",C2:C="GEM")))

这个公式直接筛选出卖出记录之后的买入交易，计算它们的加权平均，结果也是$40。

备注：内容来源于stack exchange，提问作者YorgenFridman