Google Sheets如何基于query计算字段按净收益/期望值排序查询结果

解决方法 #

你遇到的计算仅在同时存在盈亏交易时生效的问题，核心原因是QUERY的聚合结果中，缺失的盈利/亏损统计值会返回NULL，数值计算时遇到NULL就会整体返回空值，只要把空值转成0就能解决。同时把所有字段的计算整合到同一个数组公式中，就能统一排序。

你可以直接使用下面的公式，所有计算和排序一步完成，不需要额外搭配单元格级别的ArrayFormula：

=SORT(
  QUERY(
    {Transactions!B2:B,Transactions!C2:F},
    "SELECT 
      Col1,
      IFNULL(COUNT(Col2),0),
      IFNULL(SUM(Col4),0),
      IFNULL(COUNT(Col2),0)/(IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0)),
      IFNULL(COUNT(Col3),0),
      IFNULL(SUM(Col5),0),
      IFNULL(COUNT(Col3),0)/(IFNULL(COUNT(Col3),0)+IFNULL(COUNT(Col2),0)),
      IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0),
      IFNULL(SUM(Col4),0)+IFNULL(SUM(Col5),0),
      (IFNULL(SUM(Col4),0)*IFNULL(COUNT(Col2),0)/(IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0)))+(IFNULL(SUM(Col5),0)*IFNULL(COUNT(Col3),0)/(IFNULL(COUNT(Col3),0)+IFNULL(COUNT(Col2),0)))
    WHERE Col1 IS NOT NULL AND (Col2 IS NOT NULL OR Col3 IS NOT NULL)
    GROUP BY Col1
    LABEL
      IFNULL(COUNT(Col2),0) 'Win',
      IFNULL(SUM(Col4),0) '$-Win',
      IFNULL(COUNT(Col2),0)/(IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0)) '%-Win',
      IFNULL(COUNT(Col3),0) 'Lost',
      IFNULL(SUM(Col5),0) '$-Lost',
      IFNULL(COUNT(Col3),0)/(IFNULL(COUNT(Col3),0)+IFNULL(COUNT(Col2),0)) '%-Lost',
      IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0) 'Total Transactions',
      IFNULL(SUM(Col4),0)+IFNULL(SUM(Col5),0) 'Net Gains',
      (IFNULL(SUM(Col4),0)*IFNULL(COUNT(Col2),0)/(IFNULL(COUNT(Col2),0)+IFNULL(COUNT(Col3),0)))+(IFNULL(SUM(Col5),0)*IFNULL(COUNT(Col3),0)/(IFNULL(COUNT(Col3),0)+IFNULL(COUNT(Col2),0))) 'Exp. Value'
    ",
    0
  ),
  10, FALSE
)

公式最后10, FALSE代表按第10列（期望值）倒序排列，如果要按净收益排序，改成9, FALSE即可。

如果觉得上面的写法嵌套太多可读性差，也可以拆分两层QUERY实现，逻辑更清晰：

=SORT(
  QUERY(
    QUERY(
      {Transactions!B2:B,Transactions!C2:F},
      "SELECT 
        Col1,
        COUNT(Col2),
        SUM(Col4),
        COUNT(Col2)/(COUNT(Col2)+COUNT(Col3)),
        COUNT(Col3),
        SUM(Col5),
        COUNT(Col3)/(COUNT(Col3)+COUNT(Col2))
      WHERE Col1 IS NOT NULL AND (Col2 IS NOT NULL OR Col3 IS NOT NULL)
      GROUP BY Col1
      ",0
    ),
    "SELECT
      Col1,
      IFNULL(Col2,0),
      IFNULL(Col3,0),
      IFNULL(Col4,0),
      IFNULL(Col5,0),
      IFNULL(Col6,0),
      IFNULL(Col7,0),
      IFNULL(Col2,0)+IFNULL(Col5,0),
      IFNULL(Col3,0)+IFNULL(Col6,0),
      IFNULL(Col3,0)*IFNULL(Col4,0)+IFNULL(Col6,0)*IFNULL(Col7,0)
    LABEL
      IFNULL(Col2,0) 'Win',
      IFNULL(Col3,0) '$-Win',
      IFNULL(Col4,0) '%-Win',
      IFNULL(Col5,0) 'Lost',
      IFNULL(Col6,0) '$-Lost',
      IFNULL(Col7,0) '%-Lost',
      IFNULL(Col2,0)+IFNULL(Col5,0) 'Total Transactions',
      IFNULL(Col3,0)+IFNULL(Col6,0) 'Net Gains',
      IFNULL(Col3,0)*IFNULL(Col4,0)+IFNULL(Col6,0)*IFNULL(Col7,0) 'Exp. Value'
    ",0
  ),
  10, FALSE
)

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