如何在Excel中计算含卖出权重的加密货币买卖平均持仓价格

1. G列剩余持仓量计算 #

你要求的G列逻辑可以用一个通用公式实现，不需要区分有没有历史卖出操作，假设数据从第2行开始（第1行为表头）：

• G2单元格输入公式：=SUMIF(B$2:B2;"BUY";F$2:F2) - SUMIF(B$2:B2;"SELL";F$2:F2)
• 选中G2下拉填充到所有数据行即可

公式说明：用混合引用锁定统计起始行，下拉时自动扩展统计范围到当前行，自动累加所有买入量减去所有卖出量，完全符合你要求的计算规则。

2. 优化平均持仓成本计算 #

针对清仓后成本重置的需求，分两种场景给出方案：

场景1：逐行计算截止到当前行的平均持仓成本（推荐，可回溯历史成本） #

在I2单元格输入公式后下拉填充：

=IF(G2=0;0;LET(
    上一次清仓行;IFERROR(XLOOKUP(0;G$1:G1;ROW(G$1:G1);1);1);
    清仓后总买入额;SUMIFS(D$2:D2;B$2:B2;"BUY";ROW(D$2:D2);">"&上一次清仓行);
    清仓后总买入量;SUMIFS(F$2:F2;B$2:B2;"BUY";ROW(F$2:F2);">"&上一次清仓行);
    清仓后总买入额/清仓后总买入量
))

适用版本：Excel 365/2021及以上，支持LET、XLOOKUP函数
公式说明：先判断当前持仓是否为0，是则直接返回0；否则找到上一次持仓归零的行，仅统计该行之后的买入数据计算平均成本，自动规避已清仓的历史数据干扰。

场景2：单个单元格直接返回最新平均成本（无需辅助列） #

如果你不想新增列，可直接在I4单元格输入以下公式，自动计算最新的持仓平均成本：

=LET(
    数据范围;B2:F150;
    逐行持仓;SCAN(0;SEQUENCE(ROWS(数据范围));LAMBDA(a;i;a+IF(INDEX(数据范围;i;1)="BUY";INDEX(数据范围;i;5);-INDEX(数据范围;i;5))));
    最新持仓;INDEX(逐行持仓;ROWS(逐行持仓));
    上一次清仓行;IFERROR(XLOOKUP(0;逐行持仓;SEQUENCE(ROWS(逐行持仓));0);0);
    清仓后总买入额;SUM(FILTER(INDEX(数据范围;;4);(INDEX(数据范围;;1)="BUY")*(SEQUENCE(ROWS(数据范围))>上一次清仓行)));
    清仓后总买入量;SUM(FILTER(INDEX(数据范围;;5);(INDEX(数据范围;;1)="BUY")*(SEQUENCE(ROWS(数据范围))>上一次清仓行)));
    IF(最新持仓=0;0;清仓后总买入额/清仓后总买入量)
)

旧版Excel兼容方案 #

如果你使用的是不支持LAMBDA、LET函数的旧版Excel，先按第一步填充G列，再用以下公式计算平均成本：

=IF(LOOKUP(1;0/(G:G<>0);G:G)=0;0;
SUMIFS(D:D;B:B;"BUY";ROW(D:D);">"&LOOKUP(1;0/(G:G=0);ROW(G:G)))/
SUMIFS(F:F;B:B;"BUY";ROW(F:F);">"&LOOKUP(1;0/(G:G=0);ROW(G:G)))
)

内容的提问来源于stack exchange，提问作者AndreaNobili