如何针对该数据库正确使用Pivot Table实现合理数据透视？
2026-7-27
正确使用数据透视表处理该能源数据的方法
原始数据结构
| Date | power art | unit | energy_1 | energy_2 |
|---|---|---|---|---|
| Jan | Number of power feeding systems | Number | 100 | 100 |
| Jan | net rated power | MW | 100 | 100 |
| Jan | power feed | KwH | 100 | 100 |
| Feb | Number of power feeding systems | Number | 100 | 100 |
| Feb | net rated power | MW | 100 | 100 |
| Feb | power feed | KwH | 100 | 100 |
当前错误结果
| Date | energy sources | Number of power feeding systems | net rated power | power feed |
|---|---|---|---|---|
| Jan | energy_1 | 100 | 100 | 100 |
| Jan | energy_1 | 100 | 100 | 100 |
| Jan | energy_1 | 100 | 100 | 100 |
| Jan | energy_2 | 100 | 100 | 100 |
| Jan | energy_2 | 100 | 100 | 100 |
| Jan | energy_2 | 100 | 100 | 100 |
| Feb | energy_1 | 100 | 100 | 100 |
| Feb | energy_1 | 100 | 100 | 100 |
| Feb | energy_1 | 100 | 100 | 100 |
| Feb | energy_2 | 100 | 100 | 100 |
| Feb | energy_2 | 100 | 100 | 100 |
| Feb | energy_2 | 100 | 100 | 100 |
问题根源
原始数据是指标（power art）在行，能源类别（energy_1/energy_2）在列的结构，直接透视时字段摆放逻辑错误，导致每行指标都生成重复汇总行。必须先将能源类别从列转成行（逆透视），再做透视整理。
正确操作步骤
步骤1：逆透视能源列（Excel Power Query）
- 选中原始数据区域（含表头），点击数据选项卡 → 从表格/区域，进入Power Query编辑器。
- 选中
energy_1和energy_2列，右键选择逆透视列，生成属性和值两列。 - 将
属性列重命名为energy sources，关闭编辑器并上载数据到新工作表。
逆透视后的中间数据：
| Date | power art | unit | energy sources | 值 |
|---|---|---|---|---|
| Jan | Number of power feeding systems | Number | energy_1 | 100 |
| Jan | net rated power | MW | energy_1 | 100 |
| Jan | power feed | KwH | energy_1 | 100 |
| Jan | Number of power feeding systems | Number | energy_2 | 100 |
| Jan | net rated power | MW | energy_2 | 100 |
| Jan | power feed | KwH | energy_2 | 100 |
| Feb | Number of power feeding systems | Number | energy_1 | 100 |
| Feb | net rated power | MW | energy_1 | 100 |
| Feb | power feed | KwH | energy_1 | 100 |
| Feb | Number of power feeding systems | Number | energy_2 | 100 |
| Feb | net rated power | MW | energy_2 | 100 |
| Feb | power feed | KwH | energy_2 | 100 |
步骤2：创建正确的数据透视表
基于逆透视后的中间数据插入透视表，按以下逻辑拖拽字段：
- 行区域：
Date、energy sources - 列区域：
power art - 值区域：
值（设置汇总方式为求和，避免重复行）
最终得到的合理结构：
| Date | energy sources | Number of power feeding systems | net rated power | power feed |
|---|---|---|---|---|
| Jan | energy_1 | 100 | 100 | 100 |
| Jan | energy_2 | 100 | 100 | 100 |
| Feb | energy_1 | 100 | 100 | 100 |
| Feb | energy_2 | 100 | 100 | 100 |
内容的提问来源于stack exchange，提问作者erdem
相关产品推荐
相关产品推荐
