# 第一次做数据透视表：行、列、值、筛选四个框，各拖进去什么

从 8 行订单明细拖出“客户 × 月份”汇总表，看懂字段默认落位、日期自动分组，以及为什么换个摆法就是另一张报表

> 数据透视表 · 数据整理 · 表格计算 · 约 7 分钟 · 10 月 28 日

## 本篇要点

1. 数据透视表把明细变成汇总表，靠的只是四个框：行、列、值、筛选，里面不写任何公式。
2. 行决定什么竖着分行，列决定什么横着分列，值决定格子里算什么数，筛选决定哪些记录有资格进表。
3. 非数值字段拖进窗格默认落进行，数值字段默认落进值，日期字段默认落进列，这是 Excel 按字段类型做的猜测。
4. 数值字段在值区域默认求和，非数值字段默认计数，所以出现“计数项:金额”通常说明金额列里混了文本。
5. Excel 2016 起日期字段进透视表会被自动包成年/季度/月层级，只要月度口径就把“年”“季度”拖出列区域，或用右键“组合”只选月。
6. 同一份明细换个摆法就是另一张报表，因为透视表只记录字段放在哪个框、用哪种汇总，每次改动都重新扫描源数据。
7. 透视表只呈现明细里真实存在的组合，“这个客户这个月没下单”表现为空格；SUMIFS 对同样情况返回 0。
8. 透视表读的是创建时选定的区域和一份快照，源数据在区域外新增行时刷新无效，必须更改数据源或一开始就用表格做源。
9. 每种摆法下总计都应保持不变，可以当校验码；双击任一汇总格子会列出背后的明细行，用来确认数字由哪些记录组成。

---

这篇要你亲手做出一张表：客户在左、月份在上，每个格子里是这个客户这个月的金额合计。用一份 8 行的明细就够，换成你手上几千行的真表，做法一个字不用改。

做完你还会看到另一件事：同一份明细，换个摆法就是另一张报表，而明细一个单元格都没动过。

准备两样：一份规范明细——标题只占一行，一行一条记录，日期是真日期，没有合并单元格和小计行（这几点为什么不能将就，[前面那篇](https://pangzhengboyin.com/articles/excel-tabular-data-shape-and-cleanup-98a9d6a5)讲过）；Excel 2016 或更新版本，下面用 Windows 版界面，Mac 版菜单名略有差别，四个框的位置一样。

## 先看一眼这份明细

放在工作表 `明细` 的 A1:E9：

| 订单号 | 日期 | 客户 | 区域 | 金额 |
|---|---|---|---|---|
| A001 | 2024/3/2 | 甲公司 | 华东 | 1200 |
| A002 | 2024/3/15 | 乙公司 | 华北 | 800 |
| A003 | 2024/3/20 | 甲公司 | 华东 | 1500 |
| A004 | 2024/4/1 | 丙公司 | 华东 | 900 |
| A005 | 2024/4/8 | 乙公司 | 华南 | 600 |
| A006 | 2024/3/28 | 甲公司 | 华东 | 700 |
| A007 | 2024/4/15 | 乙公司 | 华北 | 1100 |
| A008 | 2024/3/30 | 丙公司 | 华南 | 400 |

八行金额加起来是 7200。记住这个数，每一步都用它验算。

## 插入一张空透视表

点明细里任意一个有数据的单元格，然后“插入”→“数据透视表”。确认“表/区域”是 `明细!$A$1:$E$9`，位置选“新工作表”，确定。

左边出现一块空白的方框区域，右边出现“数据透视表字段”窗格：上半部分是字段清单（每个前面一个勾选框），下半部分是四个空方框——**筛选、列、行、值**（新版里第一个叫“筛选器”）。

四个框都是空的，所以左边那张表除了一行“行标签”的空壳，什么都没有。

## 四个框分别管什么

- **行**：把字段竖着分类，每个值占一行。
- **列**：把字段横着分类，每个值占一列。
- **值**：格子里算出来的那个数。
- **筛选**：整张表的总开关，选一项，其余记录不参与。

它们合起来决定一个格子：

```mermaid
flowchart LR
  S["明细里的每一行"] --> F{"筛选：值对得上的记录才进表"}
  F --> R["行：每个值占一行"]
  F --> C["列：每个值占一列"]
  R --> G["一个格子"]
  C --> G
  V["值：这个格子算合计还是计数"] --> G
```

一行明细要落进某个格子，得同时符合行上的条件（是这个客户）和列上的条件（是这个月）。四个框里只有“值”不负责分类。

## 第一步：客户拖进“行”

勾上“客户”。它是文字字段，Excel 默认把它放进**行**区域——非数值字段默认进行、日期字段默认进列、数值字段默认进值，这是 Excel 按字段类型做的一次猜测 [1]。

左边出现甲公司、乙公司、丙公司三行，底下有“总计”，但还没有数字。顺手把“行标签”改成“客户”。

## 第二步：金额拖进“值”

勾上“金额”。它是数值字段，默认落进“值”，计算方式默认是**求和**；非数值字段放进来时默认是**计数** [2]。

表头显示成“求和项:金额”，点一下可以改成“金额”，不影响计算。右下角总计应该是 **7200**，对不上就说明源区域选漏了行，或者金额列里混着文本——那种情况下你会看到“计数项:金额”，用 `ISNUMBER` 抽查一格就能确认。

## 第三步：日期拖进“列”

这一格最容易卡。勾上“日期”后，它默认进列，但从 Excel 2016 起，日期字段一进透视表就会被自动包成“年—季度—月—日期”的层级 [3]，上方出现多层表头，格子被拆碎。

你只要“月”：在列区域里把“年”和“季度”拖出去（或选中后按 Delete），只留“月”。处理完应该看到 3月、4月 两列。

如果显示的是“3月2日”“3月15日”这样逐日的日期，说明自动分组关掉了。右键任一日期的单元格 →“组合”，选“月”，确定。

![日期字段分组前（逐日一行）与分组后（按季度、月份汇总）的对比](https://support.microsoft.com/en-us/excel/media/o15-xl-groupeddates.jpg)

上面是按具体日期一行一行排，下面被归成了季度和月份，也就是你要的样子。

现在和明细对一遍：

| | 3月 | 4月 |
|---|---|---|
| 甲公司 | 3400 | （空） |
| 乙公司 | 800 | 1700 |
| 丙公司 | 400 | 900 |
| 总计 | 4600 | 2600 |

甲公司 3 月那 3400 是 1200+1500+700，乙公司 4 月那 1700 是 600+1100，四格都对得上，说明行、列、值放对了。

注意甲公司 4 月那格是**空的，不是 0**——这个差别后面还要用。

## 第四步：区域拖进“筛选”

把“区域”拖到筛选框。表格上方多出一个下拉框，现在显示“（全部）”。改成“华北”，整张表只剩乙公司一行：3 月 800、4 月 1100，总计 1900。看完切回“（全部）”。

这就是筛选和行的区别：行是“摊开来给我看”，筛选是“不算进来”。

## 换个摆法，就是另一张报表

现在做这篇真正想让你看见的事：把行和列上的字段互换，什么都不用重写。

把“客户”从行拖到列，把“日期”从列拖到行：

| | 甲公司 | 乙公司 | 丙公司 |
|---|---|---|---|
| 3月 | 3400 | 800 | 400 |
| 4月 | （空） | 1700 | 900 |

数字一个没变，只是站的角度换了。

再试两个：把“客户”拖出窗格，只留日期在行，得到一张按月的金额走势；把“金额”拖出去、把“订单号”拖进值，得到一张单数表——甲公司 3 月 3 单、乙公司 4 月 2 单，总计 8 单，和上一篇用 COUNTIFS 数出来的是同一件事（[SUMIFS 与 COUNTIFS](https://pangzhengboyin.com/articles/sumifs-countifs-conditional-totals-by-month-9d1c7b29)）。

为什么能这么随便？因为**透视表里没有“每格一条公式”这种东西**。它只记住两件事：哪个字段放在哪个框、值区域用哪种汇总。你换了字段，它就按新摆法把整份明细重新算一遍；源数据一个字节都没改，它只是数据的一个视图。

这正是它和 SUMIFS 那张汇总表的根本差别：用 SUMIFS，每个格子要单独写一条公式、自己搭行头列头、锁好 `$`、换月份还得复制公式；透视表只要求你说清“按什么分行、按什么分列、格子里算什么”。但也因此，SUMIFS 对不存在的组合返回 0，透视表给的是空格——甲公司 4 月根本没下单，那格组合就不存在。

## 做完要确认的三件事

**一、每种摆法下总计都该是 7200。** 这个数不随摆法变化。变了就检查筛选是不是没切回“（全部）”。

**二、空格要能解释。** 双击那个格子（或双击总计里的某个数），Excel 会新建一张表，把这个数字背后的所有明细行列出来——这是核对透视表最快的手段。空格的情况，列出的行数就是零。

**三、明细加了行，透视表不会自己变大。** 它读的是创建时那块区域 `A1:E9`，用的是当时的一份快照 [1]。你在第 10 行加一条记录，右键“刷新”数字也不变，因为那一行不在区域内。要么“数据透视表分析”→“更改数据源”扩大区域，要么一开始就 Ctrl+T 把明细建成表格，表格新增的行刷新后会自动纳入。

这张表下个月可以直接接着用。它还没做的两件事——格子里显示“占总额多少”，以及把求和换成平均值——都在“值字段设置”里，下一篇接着讲。

## 思维导图

```mermaid
flowchart LR
  S["8 行订单明细"] --> P["插入数据透视表"]
  P --> A["四个框：行 列 值 筛选"]
  A --> R["行：客户 → 竖着三行"]
  A --> C["列：日期 → 分组后 3月 4月"]
  A --> V["值：金额 → 默认求和"]
  A --> F["筛选：区域 → 只算选中部分"]
  R --> O["客户 × 月份 汇总表"]
  C --> O
  V --> O
  O --> X["行列表互换 → 同一份数据的另一张报表"]
  O --> K["空格 = 组合不存在；总计 7200 可校验"]
  O --> U["源数据加行：刷新，且区域必须覆盖新行"]
```

## 术语表

- 数据透视表：不写公式，只指定“按什么分类、算什么数”，就能把明细汇总成一张交叉表的工具。
- 行、列、值、筛选四个区域：行和列分别决定竖着、横着怎么分类，值决定格子里算什么，筛选决定哪些记录参与。
- 值区域的默认汇总方式：数值字段默认求和，非数值字段默认计数，所以在值区域看到“计数”往往意味着那一列的数值被存成了文本。
- 日期层级自动分组：Excel 2016 起日期字段进入透视表会被拆成年、季度、月、日几层，多余的要拖出去。
- 刷新与快照：透视表基于一份快照计算，源数据变了必须刷新，而刷新也只覆盖创建时选定的那块区域。
- 钻取（双击格子看明细）：双击汇总单元格会生成一张列出其背后所有明细行的新表。

## 来源

1. [Microsoft Support: Create a PivotTable to analyze worksheet data — 创建步骤、字段默认落位、刷新与“基于快照”、表格作数据源时新增行自动纳入](https://support.microsoft.com/en-us/excel/get-started/create-a-pivottable-to-analyze-worksheet-data)
2. [Microsoft Support: Change the summary function for a field in a PivotTable — 值区域数值默认求和、非数值默认计数](https://support.microsoft.com/en-us/excel/change-the-summary-function-or-custom-calculation-for-a-field-in-a-pivottable)
3. [Microsoft Support: Group or ungroup data in a PivotTable — 日期字段自动分组与右键“组合”按月份、季度分组](https://support.microsoft.com/en-us/excel/get-started/group-or-ungroup-data-in-a-pivottable)
4. [Contextures: Turn Off Automatic Date Grouping in Excel — 日期自动分组从 Excel 2016 起的版本差异与关闭选项位置](https://www.contextures.com/exceldategrouping.html)

---

原文：https://pangzhengboyin.com/articles/excel-pivottable-first-four-areas-5e3f9d27

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com