如何在Google Sheets中按月份和项目自动拆分并汇总工时记录

一、按月份+项目的工时概览 #

新建工作表「月份-项目工时概览」，在A1单元格输入以下公式：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      IF(B2:G="",,
        TEXT(A2:A,"MMMM-YY")&"|"&B1:G1&"|"&B2:G)
    )
  ),
  "SELECT SPLIT(Col1,'|')[0], SPLIT(Col1,'|')[1], SUM(SPLIT(Col1,'|')[2]) 
   WHERE SPLIT(Col1,'|')[2] > 0 
   GROUP BY SPLIT(Col1,'|')[0], SPLIT(Col1,'|')[1] 
   ORDER BY SPLIT(Col1,'|')[0], SPLIT(Col1,'|')[1]",
  1
)

公式说明： #

• 用FLATTEN+ARRAYFORMULA把原表的宽格式（项目为列）转成每行包含「月份|项目|工时」的字符串格式
• 通过SPLIT拆分字符串，提取月份、项目、工时字段
• 用GROUP BY按月份和项目分组汇总工时，同时过滤工时为0的记录，最后按月份、项目排序

二、按项目汇总的总工时概览 #

新建工作表「项目总工时概览」，在A1单元格输入以下公式：

=QUERY(TRANSPOSE({B1:G1; SUM(B2:G)}), "SELECT * WHERE Col2>0", 1)

公式说明： #

• 先计算每个项目列的总工时，与项目表头组成二维数组
• 用TRANSPOSE把横排的项目列表转成竖排结构
• 用QUERY过滤掉总工时为0的项目，最终呈现项目名和对应总工时的列表

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