在Google Sheets中基于案件负载计算单案件人工成本

核心逻辑 #

• 针对同一Case Owner的案件时间重叠区间，将员工工时按同时处理的案件数分摊，避免直接统计总工时导致的成本虚高
• 单案件成本计算公式：（各重叠区间时长 ÷ 该区间内员工同时处理案件数）之和 × 时薪

假设表格结构 #

假设你的表格列定义如下：

• A: Case ID（案件编号）
• B: Case Owner（案件负责人）
• C: Start Time（案件开始时间）
• D: End Time（案件结束时间）
• E: Case Labor Cost（待计算的单案件人工成本）

公式实现（以E2单元格为例） #

在E2单元格输入以下公式后，下拉填充至所有案件行即可：

=SUM(ARRAYFORMULA(
  (IFERROR(
    DATEDIF(
      MAX(C2, INDIRECT("C"&ROW($C$2:$C))),
      MIN(D2, INDIRECT("D"&ROW($D$2:$D))),
      "h"
    ) + 
    (DATEDIF(
      MAX(C2, INDIRECT("C"&ROW($C$2:$C))),
      MIN(D2, INDIRECT("D"&ROW($D$2:$D))),
      "m"
    ) % 60)/60,
    0
  ) / 
  COUNTIFS(
    $B$2:$B, B2,
    $C$2:$C, "<="&MIN(D2, INDIRECT("D"&ROW($D$2:$D))),
    $D$2:$D, ">="&MAX(C2, INDIRECT("C"&ROW($C$2:$C)))
  )
)) * 20

公式拆解 #

1. 重叠时长计算：

   • MAX(C2, INDIRECT("C"&ROW($C$2:$C)))：取当前案件开始时间与同Owner其他案件开始时间的较大值，确定重叠区间起点
   • MIN(D2, INDIRECT("D"&ROW($D$2:$D)))：取当前案件结束时间与同Owner其他案件结束时间的较小值，确定重叠区间终点
   • DATEDIF组合：计算重叠区间的总小时数（含分钟转小时的换算），无重叠时返回0

2. 分摊系数计算：

   • COUNTIFS：统计当前重叠区间内，同一Case Owner同时在处理的案件数量，作为工时分摊的分母

3. 最终成本计算：

   • 将各重叠区间的（分摊后时长）求和，再乘以时薪20美元，得到单案件实际人工成本

示例场景验证 #

比如Case 2的时间线分为两段：

• 第一段仅与Case 1重叠：员工同时处理2个案件，该段时长按1/2分摊
• 第二段与Case 1、Case 3重叠：员工同时处理3个案件，该段时长按1/3分摊
• Case 2最终成本 =（第一段时长÷2 + 第二段时长÷3）×20

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