如何在Excel数据透视表中实现每月平均值及求和/平均切换功能

这个问题我之前帮团队里的人解决过，核心是得搞清楚你要的「每月平均」是先把每个月的总工时算出来，再对这些月度总工时取平均，而不是直接把所有员工的所有工时混在一起算平均。下面给你两种实操性强的方案，按需选：

这是最灵活的方法，适合需要快速切换视图的场景：

• 第一步：把你的数据表导入Power Pivot
  选中数据区域 → 点击顶部「数据」选项卡 → 选择「添加到数据模型」，这样数据就进入Power Pivot的模型里了
• 第二步：创建两个核心度量值
  打开Power Pivot窗口 → 点击「主页」选项卡 → 「新建度量值」，分别输入：

  总工时 = SUM('你的数据表名称'[工时])
  

  这个是计算所有选中时间范围内的总工时；然后创建月度平均的度量值：

  月度平均工时 = AVERAGEX(VALUES('你的数据表名称'[月份]), [总工时])
  

  解释：VALUES('你的数据表名称'[月份])会提取当前筛选范围内的所有不重复月份，然后对每个月份计算总工时，最后取这些月度总工时的平均值，完全符合你的需求
• 第三步：配置切换视图的方式
  你有两种选择：
  1. 用切片器切换：
     • 新建一个辅助表（比如在Excel里插一列，内容是「总工时」和「月度平均工时」），把这个表也添加到数据模型
     • 新建一个切换度量值：

       切换视图 = IF(SELECTEDVALUE('辅助表'[视图类型])="总工时", [总工时], [月度平均工时])
       

     • 把「切换视图」度量值拖到数据透视表的「值」区域，然后给辅助表添加切片器，用户点切片器就能一键切换视图
  2. 用表单控件切换：
     • 打开「开发工具」选项卡 → 插入「单选按钮」（表单控件），添加两个按钮，分别命名为「总工时」和「月度平均」
     • 把两个单选按钮关联到同一个单元格（比如A1），第一个按钮值设为1，第二个设为2
     • 修改切换度量值：

       切换视图 = IF(A1=1, [总工时], [月度平均工时])
       

     • 之后点单选按钮就能直接切换数据透视表和图表的显示内容
• 最后别忘了把时间轴切片器和这个数据透视表关联，确保时间筛选对两个视图都生效

如果不想用Power Pivot，也可以用两个数据透视表配合隐藏来实现：

• 第一步：创建第一个数据透视表
  行区域放「月份」，值区域放「工时」的求和项，这个表会显示每个月的总工时
• 第二步：基于第一个表创建第二个数据透视表
  选中第一个数据透视表的结果 → 插入新的数据透视表，值区域放「月度总工时」的平均值，这个表就是你要的每月平均工时
• 第三步：把两个数据透视表对应的图表都放到仪表盘，让它们位置完全重叠
• 第四步：用表单控件控制显示/隐藏
  • 同样用「开发工具」里的单选按钮，关联到一个单元格
  • 如果你会点VBA，可以写一段简单的代码，比如当单元格值为1时显示总工时图表、隐藏平均图表；值为2时相反
  • 不会VBA的话，也可以手动设置：把两个图表放在同一位置，需要看哪个就手动显示/隐藏另一个，或者用单元格链接配合隐藏行/列来间接控制

不管用哪种方案，记得把时间轴切片器和所有相关的数据透视表都关联起来，这样切换时间范围时，总工时和月度平均都会同步更新。

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