Google Sheets公式实现投资组合数据分组：区分已售资产与持仓

需求背景 #

现有投资组合变动数据集（含日期、股票代码Ticker、交易类型Buy/Sell、股数Shares），需拆分两类列表：

• 已售资产（Sold Assets）：同一Ticker的买卖交易已完全结清的头寸
• 持仓（Open Positions）：同一Ticker买卖后剩余未结清的头寸

此前按Ticker汇总股数的方法无法处理重复买卖的场景，需用纯公式/查询语句实现。

1. 计算持仓（Open Positions） #

汇总式持仓（按Ticker展示净持仓） #

假设数据在A:D列（A=日期，B=Ticker，C=交易类型，D=股数），用QUERY公式直接计算每个Ticker的净持仓，筛选出仍有剩余持仓的条目：

=QUERY(
  {A:D, ARRAYFORMULA(IF(C:C="Buy", D:D, -D:D))},
  "SELECT Col2, SUM(Col5) 
   WHERE Col2 IS NOT NULL 
   GROUP BY Col2 
   HAVING SUM(Col5) > 0 
   LABEL Col2 'Ticker', SUM(Col5) 'Open Shares'"
)

逻辑说明：

• 用辅助列Col5将Buy记为正股数、Sell记为负股数
• 按Ticker分组求和，筛选求和结果>0的即为剩余持仓

逐笔式持仓（展示未结清的具体交易） #

若需保留每笔未被完全抵消的交易，先添加累计持仓辅助列（如E列）：

=ARRAYFORMULA(IF(A:A="",,SUMIFS(IF(C:C="Buy", D:D, -D:D), B:B, B:B, A:A, "<="&A:A)))

再筛选出每个Ticker最后一笔交易的累计持仓>0的所有记录：

=FILTER(
  A:D,
  ARRAYFORMULA(
    VLOOKUP(B:B&"|"&MAXIFS(A:A, B:B, B:B), {B:B&"|"&A:A, E:E}, 2, FALSE) > 0
  )
)

2. 计算已售资产（Sold Assets） #

汇总式已售（按Ticker展示已结清股数） #

筛选出净持仓为0的Ticker，展示其已结清的总股数：

=QUERY(
  {A:D, ARRAYFORMULA(IF(C:C="Buy", D:D, -D:D))},
  "SELECT Col2, ABS(SUM(Col5)/2) 
   WHERE Col2 IS NOT NULL 
   GROUP BY Col2 
   HAVING SUM(Col5) = 0 
   LABEL Col2 'Ticker', ABS(SUM(Col5)/2) 'Sold Shares'"
)

逻辑说明：净持仓为0时，总买入=总卖出，取绝对值的一半即为已售股数。

逐笔式已售（展示完全结清的具体交易） #

筛选出每个Ticker最后一笔交易的累计持仓为0的所有记录：

=FILTER(
  A:D,
  ARRAYFORMULA(
    VLOOKUP(B:B&"|"&MAXIFS(A:A, B:B, B:B), {B:B&"|"&A:A, E:E}, 2, FALSE) = 0
  )
)

内容的提问来源于stack exchange，提问作者Walter Köhlenberg