如何用Google表格统计跨小时日历事件的时段忙碌情况

一、核心思路 #

要实现跨小时事件的逐小时计数，关键是将单个跨时段事件拆分为对应覆盖小时数的多行数据，每行对应一个被覆盖的小时节点。

二、具体公式实现步骤 #

假设原始事件数据在Sheet1，列结构为：

• A列：事件标题
• B列：开始时间（格式需为yyyy-mm-dd hh:mm:ss）
• C列：结束时间（格式需为yyyy-mm-dd hh:mm:ss）

在新工作表（如Sheet2）的A1单元格输入以下数组公式，自动生成拆分后的数据集：

=ARRAYFORMULA(
  IFERROR(
    SPLIT(
      FLATTEN(
        BYROW(Sheet1!A2:C, LAMBDA(row,
          IF(INDEX(row,1)="",,
            TEXTJOIN("|",,
              INDEX(row,1),
              SEQUENCE(
                HOUR(INDEX(row,3)-INDEX(row,2)) + IF(MINUTE(INDEX(row,3))>0,1,0),
                1,
                INDEX(row,2),
                TIME(1,0,0)
              )
            )
          )
        )
      ),
      "|"
    )
  )
)

公式细节说明： #

• BYROW遍历原始事件的每一行，自动跳过空行
• HOUR(结束时间-开始时间)+IF(MINUTE(结束时间)>0,1,0)：精准计算事件覆盖的总小时数（例：14:30到17:10，会判定为覆盖14、15、16三个小时）
• SEQUENCE生成从开始时间起，每小时递增的时间序列
• TEXTJOIN+FLATTEN+SPLIT组合：将事件标题与每个小时时间拼接后转成单行，再拆分回独立列

三、生成统计维度辅助列 #

在Sheet2的C、D、E列添加以下公式，自动生成统计所需的维度字段：

• C列（Year）：=ARRAYFORMULA(TEXT(B2:B, "yyyy"))
• D列（Day-of-the-week）：=ARRAYFORMULA(TEXT(B2:B, "dddd"))（用ddd可显示缩写格式）
• E列（Hour-of-the-day）：=ARRAYFORMULA(HOUR(B2:B))

四、制作可复用模板 #

1. 将Sheet1设为「事件输入表」，仅保留A（标题）、B（开始时间）、C（结束时间）三列，提示用户直接替换这里的事件数据即可
2. Sheet2设为「拆分后数据表」，保留上述公式，会自动同步更新拆分结果
3. 新建Sheet3制作数据透视表：
   • 行维度：Year + Hour-of-the-day
   • 列维度：Day-of-the-week
   • 值字段：对事件标题执行「计数」操作

五、可选免费日历分析工具（补充） #

• Google日历自带「忙碌时段」分析：进入日历设置-「日历详情」，可查看系统自动统计的常规忙碌模式
• Time Analytics：支持导入ICS文件，生成小时/周维度的忙碌统计报表
• Calendly Analytics：若使用Calendly管理预约，可直接查看时段占用情况

内容的提问来源于stack exchange，提问作者Cyril Duchon-Doris