跳到主要内容

数据透视表

问题​

数据透视表是什么？如何用数据透视表快速汇总和分析数据？

答案​

什么是数据透视表​

数据透视表（PivotTable）是 Excel 中最强大的数据分析功能，可以对大量数据进行快速汇总、分组、交叉分析。

原始数据→透视结果
逐行明细记录拖拽字段按维度聚合的汇总表

创建步骤​

  1. 选中数据区域 → 插入 → 数据透视表
  2. 将字段拖入四个区域：
区域放什么作用
行（Rows）分类维度表格行标签（产品、地区）
列（Columns）次要维度表格列标签（月份、渠道）
值（Values）数值度量聚合计算（求和、计数、均值）
筛选（Filters）筛选条件全局筛选器

常用技巧​

值字段设置​

汇总方式用途示例
求和总量各地区总收入
计数频次各产品下单次数
平均值均值各渠道平均客单价
最大/最小值极值各月最高单笔订单
值显示方式 → 列汇总的百分比占比各品类收入占总收入比例

分组功能​

数据类型分组方式用途
日期按月/季/年月度汇总、季度对比
数值按区间客单价分布（0-50, 50-100...）
文本手动选择分组将城市分组为区域

计算字段​

在透视表中添加自定义计算：

插入 → 计算字段
名称: 利润率
公式: =利润/收入

切片器（Slicer）​

  • 可视化的筛选控件，比下拉筛选更直观
  • 支持多选、跨透视表联动
  • 插入 → 切片器 → 选择字段

数据透视图​

数据透视表 + 图表 = 数据透视图（PivotChart）

  • 透视表筛选时图表自动更新
  • 适合制作交互式报表

实用场景​

场景行列值
各地区月度收入地区月份SUM(收入)
各渠道用户数渠道—COUNTA(用户ID)
各品类平均单价品类—AVERAGE(单价)
各月退货率月份—COUNT(退货)/COUNT(订单)
交叉分析品类渠道SUM(销量)

常见面试问题​

Q1: 数据透视表的局限性是什么？​

答案：

  • 数据量限制：Excel 行数上限约 104 万行
  • 无法做复杂的数据清洗（需 Power Query）
  • 无法处理非结构化数据
  • 性能：大量数据或复杂计算时较慢
  • 无版本控制：多人协作困难

Q2: 为什么透视表的求和结果和预期不一致？​

答案（排查步骤）：

  1. 数据类型错误：数值列被识别为文本（检查单元格格式）
  2. 空值处理：空单元格可能跳过或算 0
  3. 重复数据：源数据有重复行导致多算
  4. 汇总方式错误：默认可能选了"计数"而非"求和"

Q3: 如何用透视表做同比分析？​

答案：

  1. 行区域放"月份"，值区域放"收入"
  2. 右键值字段 → "值显示方式" → "与上年同期的差异百分比"
  3. 或创建计算字段：=本期收入 - 上期收入

相关链接​