如何用PowerPivot实现Excel嵌套数据透视表：先求和再按月求平均

原始数据（已翻译表头） #

需求明确 #

1. 第一步：按「姓名」和「日期」对「数值」字段求和
2. 第二步：基于第一步的结果，按「姓名」分组，计算每个月份的「数值」平均值

以下是几种可行的实现方案：

方案一：PowerPivot 分步实现 #

步骤1：导入数据到PowerPivot模型 #

选中原始数据区域，点击顶部菜单栏的「PowerPivot」选项卡 → 「添加到数据模型」，将数据导入PowerPivot。

步骤2：创建「每日求和」度量值 #

在PowerPivot界面的「计算」选项卡中，点击「新建度量值」，输入DAX公式：

每日求和 = SUM('表名'[数值])

注：把公式里的「表名」替换成你实际导入的表名称

步骤3：添加「年月」计算列 #

如果原始数据没有单独的月份分组列，右键点击PowerPivot表的空白列标题 → 「添加计算列」，输入DAX公式：

年月 = FORMAT('表名'[日期], "yyyy-MM")

此公式会把日期转换成「年-月」格式，方便按月份分组统计。

步骤4：创建「月度平均值」度量值 #

再次点击「新建度量值」，输入DAX公式：

月度平均值 = AVERAGEX(
    SUMMARIZE('表名', '表名'[姓名], '表名'[年月], "每日合计", [每日求和]),
    [每日合计]
)

这个公式会先按「姓名」和「年月」分组计算每日求和结果，再对每个姓名下的月度每日求和值取平均。

步骤5：生成透视表查看结果 #

点击PowerPivot界面的「透视表」按钮，选择透视表放置位置后，按以下设置拖拽字段：

• 行区域：「姓名」
• 列区域：「年月」
• 值区域：「月度平均值」
  即可得到最终的每个姓名对应各月份的平均值结果。

方案二：Excel普通透视表+辅助列 #

不用PowerPivot的话，可通过辅助列配合两次透视表实现：

步骤1：添加「年月」辅助列 #

在原始数据旁插入新列，命名为「年月」，输入Excel公式：

=TEXT(A2,"yyyy-MM")

下拉填充到所有行，将日期转换为年月格式。

步骤2：第一次透视表（完成第一步求和） #

选中原始数据区域，插入透视表，设置：

• 行区域：「姓名」、「日期」
• 值区域：「数值」（右键值字段，设置为「求和」）
  将透视表结果复制粘贴为数值，得到按姓名和日期求和的中间数据。

步骤3：第二次透视表（完成第二步求平均） #

选中第一步得到的中间数据，插入新的透视表，设置：

• 行区域：「姓名」
• 列区域：「年月」（可从日期列提取，或直接用辅助列的「年月」）
• 值区域：「数值」求和结果（右键值字段，设置为「平均值」）
  即可得到最终的月度平均值。

方案三：Power Query 一键式实现 #

用Power Query可直接完成两次分组计算，无需手动操作透视表：

步骤1：导入数据到Power Query #

选中原始数据区域，点击顶部菜单栏的「数据」选项卡 → 「从表格/区域」，进入Power Query编辑器。

步骤2：第一步分组求和 #

点击「转换」选项卡 → 「分组依据」，按以下设置填写：

• 分组依据：勾选「姓名」、「日期」
• 新列名：输入「每日求和」
• 操作：选择「求和」
• 列：选择「数值」
  点击确定，得到按姓名和日期求和的结果。

步骤3：添加「年月」列 #

点击「添加列」选项卡 → 「自定义列」，命名为「年月」，输入公式：

= Date.ToText([日期], "yyyy-MM")

步骤4：第二步分组求平均 #

再次点击「转换」选项卡 → 「分组依据」，按以下设置填写：

• 分组依据：勾选「姓名」、「年月」
• 新列名：输入「月度平均值」
• 操作：选择「平均值」
• 列：选择「每日求和」
  点击确定后，点击「关闭并上载」，结果会直接加载到Excel表格中。

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