浏览 AI使用教程
Excel 透视表怎么做：围绕业务问题设计字段和汇总
做透视表前先把业务问题写成指标和维度：谁是分子、谁是分母、按什么比较。字段拖得再多，也替代不了这个口径。
按实际操作顺序推进
每完成一段就检查当前输出，确认结果符合预期后再继续。
会拖字段不等于会做透视表，先说清它要回答什么
销售同事把地区、品类、销售额全拖进透视表，得到一张很大的汇总，却回答不了“华东哪类产品的退款率异常”。问题不是不会点菜单，而是没有先定义分子、分母和比较维度。
- 透视表标题直接写出业务问题和时间范围
- 任一汇总值可以双击回到对应明细
- 刷新数据后字段和口径没有失效
动手前，把材料和不能改的部分定下来
| 需要确认 | 本例内容 |
|---|---|
| 现有材料 | 六行教学订单，字段为订单号、下单月、地区、品类、实付金额、退款金额和状态；金额统一元。问题只看2026-08已完成订单，各地区/品类的金额退款率。 |
| 不能越过的边界 | 退款率按退款金额/实付金额计算，不与退款订单数混用；取消订单排除；总计行不能拿来替代地区比较。 |
| 要交付的结果 | 问题—维度—指标透视表 |
Excel 透视表怎么做，从“写一句业务问题”开始做
写一句业务问题
本例问题是“2026-08已完成订单中，各地区/品类的金额退款率是多少”，分子为退款金额合计，分母为实付金额合计。
确认数据粒度
一行代表什么，日期、类别、金额和状态是否可聚合，重复是否合理。
分配维度与指标
行列用于比较维度，值使用求和、计数、去重计数或平均，并明确空值与小计。
加入筛选而不改变口径
筛选器和切片器服务于查看，不静默排除异常；默认状态在标题中说明。
用手工小计验证
选一个小范围重算数量和金额，检查刷新后新数据进入正确范围。
六行订单足够检查分子、分母与筛选
| 订单号 | 下单月 | 地区 | 品类 | 实付金额 | 退款金额 | 状态 |
|---|---|---|---|---|---|---|
| A101 | 2026-08 | 华东 | 配件 | 500 | 50 | 已完成 |
| A102 | 2026-08 | 华东 | 配件 | 500 | 34 | 已完成 |
| A103 | 2026-08 | 华东 | 配件 | 2000 | 0 | 取消 |
| A104 | 2026-08 | 华东 | 主机 | 1000 | 20 | 已完成 |
| A105 | 2026-08 | 华南 | 配件 | 400 | 0 | 已完成 |
| A106 | 2026-08 | 华南 | 主机 | 600 | 60 | 已完成 |
复制到新工作表，金额列必须是数字。原表每订单只一行；先排查重复订单号，不能把重复明细当新增订单。
先汇总两种金额，再算比值
- 01
将数据转为表格
含单行标题选中A1:G7，按Ctrl+T并确认包含标题，表名设为Orders。
- 02
创建透视表
新增“汇总”工作表；回源表选择插入→数据透视表，使用Orders，放到“汇总”!$A$3。
- 03
拖放字段
行=地区；列=品类；筛选=下单月和状态；值放实付金额与退款金额，分别选求和，自定义名称改“实付总额”“退款总额”。再放订单号，选计数。
- 04
固定筛选条件
下单月选2026-08，状态只选已完成；标题写出这两个条件。取消单不能留在分母。
- 05
计算同粒度比值
在透视表外输入下方GETPIVOTDATA，格式改百分比。不要把每单退款率求平均；分母为0时标不适用，不强写0%。
- 06
刷新并回查
新增数据后在透视表中右击刷新，检查新行确已进入Orders。双击84的汇总格应回到A101/A102两条明细。
华东/配件的同粒度金额退款率
华东/配件的同粒度金额退款率
=IF(GETPIVOTDATA("实付总额",$A$3,"地区","华东","品类","配件")=0,"不适用",GETPIVOTDATA("退款总额",$A$3,"地区","华东","品类","配件")/GETPIVOTDATA("实付总额",$A$3,"地区","华东","品类","配件"))公式放在汇总工作表。若值名称未自定义为上面两项，先改名称，或在输入=后点击对应汇总格让Excel生成实际字段引用。
手算对照，不把试算称成实测
| 地区/品类 | 分子/分母 | 预期退款率 |
|---|---|---|
| 华东/配件 | (50+34)/(500+500) | 8.4%，2单 |
| 华东/主机 | 20/1000 | 2% |
| 华南/配件 | 0/400 | 0% |
| 华南/主机 | 60/600 | 10% |
若华东/配件出现2.8%，检查2000元取消单是否错误进入分母；若只显示计数，先检查金额列是否被读成文本。
完成后的问题—维度—指标透视表
教学预期：华东/配件两单实付1000元、退款84元，金额退款率8.4%；取消单A103不进入分母。两条教学订单不足以判断异常原因。若真实业务出现同类情况，先回看对应订单及退款原因，再决定是否需要扩大样本。
为什么“金额看起来正确，退款率却超过 100%”还不能交付
金额看起来正确，退款率却超过 100%
- 原因
- 分子分母粒度不同或取消单未排除
- 怎么改
- 返回明细检查同一订单是否重复，固定过滤条件后重算分子和分母