Excel pivottable如何计算每日人员时间差及Grand Total平均值

问题根因 #

数据透视表默认总计逻辑是直接对源字段做聚合，不会复用明细行的自定义计算规则：你用MAX(time)-MIN(time)计算单人单日工时的时候，总计行会直接取全表最大时间减全表最小时间，结果必然不符合预期。自带的计算字段功能也不支持先按「人员+日期」粒度算差值再做聚合，属于透视表原生逻辑限制，不是配置操作错误。

方案1：源数据预处理（稳定性最高，无公式错位问题） #

直接在原始数据源提前计算好单日单人工时，再生成透视表，所有总计逻辑会自动匹配需求：

• 打开原始数据表，在现有字段旁新增列，列名设为work_hours
• 在列的第一行数据单元格输入公式，Excel 365/2021版本直接回车，旧版本按Ctrl+Shift+Enter确认数组公式：

  =IF([@action]="out",
      MAXIFS([time],[person],[@person],[date],[@date])
      -MINIFS([time],[person],[@person],[date],[@date]),
      ""
  )
  

• 公式会自动在每人每日的签退行填充当日总工时，签到行留空
• 将work_hours列的单元格格式设置为[h]:mm，避免超过24小时的工时显示异常
• 刷新透视表，将work_hours字段拖入值区域：
  • 明细行值汇总方式选求和，自动展示每人每日工时，替代原来的最大/最小时间差值计算
  • 底部Grand Total行打开值字段设置，把汇总方式改成平均值，即可得到全量记录的平均工时

方案2：透视表外使用GETPIVOTDATA写自适应公式（无需修改源数据） #

之前下拉辅助列出现日期匹配错位，是因为使用了普通单元格引用，换成透视表专用的GETPIVOTDATA函数可以自动绑定行维度，不会出现错位：

• 保留现有透视表配置，确认值区域已经放入time字段的最大值、最小值两项
• 在透视表右侧新增列的第一行数据行输入公式：

  =GETPIVOTDATA("Max of time",$A$3,"person",$A4,"date",$B4)
  -GETPIVOTDATA("Min of time",$A$3,"person",$A4,"date",$B4)
  

  公式中$A$3替换为当前透视表的左上角单元格地址，$A4、$B4对应透视表中人员列、日期列的当前行单元格
• 直接下拉填充公式，会自动匹配每一行的人员、日期维度计算工时，不会出现日期匹配错误
• 列底部新增总计行，用AVERAGE()函数引用这一列所有工时单元格，即可得到平均工时

参考测试源数据 #

person  date    time    action
person a    06/06/2022  08:00:00    in
person a    06/06/2022  19:00:00    out
person b    06/06/2022  10:00:00    in
person b    06/06/2022  18:00:00    out
person a    07/06/2022  08:00:00    in
person a    07/06/2022  19:00:00    out
person b    07/06/2022  10:00:00    in
person b    07/06/2022  18:00:00    out
person a    08/06/2022  08:00:00    in
person a    08/06/2022  19:00:00    out
person b    08/06/2022  10:00:00    in
person b    08/06/2022  18:00:00    out
person a    09/06/2022  08:00:00    in
person a    09/06/2022  19:00:00    out
person b    09/06/2022  10:00:00    in
person b    09/06/2022  18:00:00    out
person a    10/06/2022  08:00:00    in
person a    10/06/2022  19:00:00    out
person b    10/06/2022  10:00:00    in
person b    10/06/2022  18:00:00    out

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