# 把旧表改成“一行一条记录”，函数和透视表才跑得起来

合并单元格、两行表头、小计行为什么会让公式和透视表出错，以及怎么把它们改造成规范表格

> 数据整理 · Excel 表格 · 透视表准备 · 约 6 分钟 · 09 月 26 日

## 本篇要点

1. 函数、透视表和后续自动化都以“表头只有一行、一行一条记录、一列一个字段”为前提，旧表改成这个形状之后，汇总流程才可能重复使用。
2. 合并单元格只有左上角那一格真的存着内容，其余位置是空的，所以 SUMIF 之类的函数会漏掉被合并盖住的行，而且不会报错。
3. 透视表只有一条硬规则：数据源第一行必须是每一列的表头；表头空白或被合并会报“字段名无效”，中间的空行、小计行会造成分段错误或让合计翻倍，被合并留下的空白还会单独分出一组“（空白）”。
4. 把合并格盖住的值补齐，要按顺序做：先取消合并（值留在最上面一格），再选中该列数据段用“定位条件 → 空值”，然后敲 `=`、按 ↑、按 Ctrl+Enter。Ctrl+Enter 把同一个相对公式同时写进所有选中空格，每个空格取到自己上方那一格的值，连续多段也能依次接下去。
5. 补完必须选择性粘贴为“值”，否则排序或删行会让公式引用错位。
6. 如果这一列本就有该空缺的格子，只处理合并造成的部分；如果原来合并的几个格子装着不同的值，那要靠原始数据核对，填空补不回来。
7. 两行表头要压成一行、拼成唯一列名；想保持两行显示，可在同一单元格里用 Alt+Enter 换行，它仍是一个字段名。重复性工作可以交给 Power Query 记成步骤，以后刷新即可。
8. 小计行、总计行属于半条记录，必须从明细区移走，合计交给透视表或报表区域。
9. 改完后按 Ctrl+T 建成 Excel 表格并命名，它不允许有合并单元格，会随新数据自动扩展，让透视表和公式下次刷新即可。
10. 把月份横向摆成列的布局是另一种不规范，要先把方向正过来，和本篇填空式的修补不是一回事。

---

你手上那张表，大概是这么来的：别人做好的月报模板，区域那一列为了好看，把同一个区域合并成一格；表头分了两行，第一行写“1月”“2月”，第二行写“销售额”“利润”；中间还夹着一行“小计”。

你把它拿来想做个汇总，公式写下去结果对不上，透视表干脆弹出“字段名无效”。问题不在公式，而在这张表的**形状**。

## 规范的表格长什么样

先看一张能跑起来的表，假设是一份订单明细：

- 第一行是表头，每一列一个名字：日期、区域、产品、数量、单价。
- 第 2 行往下，每一行是一笔订单，五列填满，中间没有空行。
- “区域”这一列从上到下写的都是区域名，没有一格是空的。

三条要求可以概括成一句话：**表头只有一行，一行是一条记录，一列是一个字段。**“字段”就是指这一列装的是什么信息——日期、区域、数量。整列都在回答同一个问题，数据类型也一致。

## 为什么必须是这个形状

这不是洁癖，是后面所有工具的入口条件。

**第一，函数的参数是一整列。** 你写 `=SUMIF(B:B,"华东",D:D)`，意思是“在 B 列里找华东，把对应行的 D 列加起来”。这个式子偷偷假设了两件事：B 列只装区域、每一行都有区域值。现在看一张合并过的表：区域列把 5 行合并成一个“华东”。合并单元格有个关键特性——**只有左上角那一格真的存着内容，被合并掉的格子其实是空的**[5]。所以这 5 行里，只有第 1 行的 B 列是“华东”，后面 4 行是空白。`SUMIF` 找不到它们，结果就少算了 4 行的数量，而且不会报错，只是悄悄地少。

**第二，透视表只有一条硬规则：数据源第一行必须是每一列的表头**[2]。表头空着、或者被合并单元格盖住，透视表就读不出字段名，弹出“字段名无效”[3]。同样地，数据中间只要有空行，透视表就只认第一段；有小计行，它会把小计当成一条独立记录再加一遍，合计直接翻倍。还有一点不显眼：被合并盖住的位置本来就是空白，透视表会把这些行归进一个叫“（空白）”的分组，本来属于华东的 4 笔订单全跑到那里去了。

**第三，自动化要的是“下次不用重做”。** 把区域变成 Excel 表格（Ctrl+T）之后，你在表格下面再加一行，表格自动变大，公式 `=SUM(订单表[数量])` 自动跟上，透视表刷新就带上新数据。这种表格内部不允许有合并单元格。同理，Power Query——Excel 里用来记录并重放数据清洗步骤的工具——也是一步步按列名操作的，表头多一行，每来一批新数据都得手工再调一遍。

## 三种旧表怎么改

### 一、类别列被合并了

目的是把“华东”补到它盖住的那几行上去，让每一行都自己带着区域名。做法分四步：

1. **先取消合并。** 选中这一列，开始 → “合并后居中”旁边的下拉箭头 → 取消单元格合并。值会留在最上面那一格，其余位置变成空白，这一步本身不会丢东西。
2. **只选中空格。** 选中这一列有数据的那一段（比如 A2:A13），开始 → 查找和选择 → 定位条件（或者按 F5 再点“定位条件”）→ 选“空值” → 确定。现在被选中的只剩这一段里的空格。
3. **一次填满。** 不要点鼠标，直接敲一个 `=`，再按一次方向键 ↑。这时公式栏里是 `=A2` 这种指向上方一格的形式。接着按 `Ctrl+Enter`：它和普通回车不一样，普通回车只写进当前一格，`Ctrl+Enter` 会把同一个公式**同时写进所有被选中的单元格**，而相对引用会按各自的位置自动调整，于是每个空格都取到自己上面那一格的值[4]。原来有几段合并、每段几行都没关系，上一格补好之后，紧跟着的下一格取到的就是补好的值，一格一格接下去。
4. **固化成值。** 把这一列复制，选择性粘贴为“值”。因为刚才补进去的是公式，一排序、一删行，引用就会错位，补好的内容会跟着乱掉。

还有两个边界。如果这一列里本来就有该空缺的格子（比如“备注”允许不填），不要整列这么处理，只处理合并造成的那部分。反过来，如果原来合并进去的几个格子本来就装着不同的值，那一合成就只剩下左上角那个了——这种情况得回头找原始数据核对，不是填空能补回来的。

### 二、表头占了两行

Excel 的字段名是一个单元格里的一段文字，不能横跨两行。所以“1月”在第一行、“销售额”在第二行这种结构，必须压成一行，名字拼成“1月销售额”。手动做就是：在表头上方插一行，把每一列的完整名字写全，再删掉原来那两行。

如果领导就是要求上面显示成两行，也有办法：**在同一个单元格里按 `Alt+Enter` 手动换行**，把“1月”和“销售额”放进一个格子。显示上是两行，实际还是一个单元格、一个字段名，透视表照样认[2]。用空格拼成“1月 销售额”也行。

数据每月都来一批的话，这套动作可以交给 Power Query 记成步骤，以后新文件直接刷新，不用再手工做一遍。

### 三、中间的小计行、空行、总计行

这些行和别的行不一样：它们没有日期、没有产品，只有几个合计数。它们是**半条记录**，放在明细区里会同时破坏前面两条规则。做法是全部删掉，只保留明细。合计交给透视表或另开的报表区域去做，不要混进数据源。

## 改完立刻做一件事

选中整个数据区，按 `Ctrl+T` 变成 Excel 表格，给它起个名字（比如“订单表”），之后建透视表或写公式都以它为数据源。往表格下面粘新数据，它会自己长大，透视表点一下刷新就带上新内容。这才是“下次不用重做”的起点。

最后划一条边界：还有一类不规范不是靠填空能修的——把 1 月、2 月、3 月横着摆在列上，每列一个月份。那不是“缺了值”，而是“该竖着放的信息躺下了”。这种情况要先把方向正过来，属于另一种改造，值得单独讲。

## 术语表

- 一行一条记录、一列一个字段：表格的每一行是一笔独立、完整的业务记录，每一列只装同一类信息，是函数和透视表能读懂数据的前提。
- 表头行：数据区第一行、每列一个唯一名称，是透视表识别字段的唯一依据，不能空、不能跨行、不能合并。
- 定位条件 → 空值：Excel 里把选定范围内所有空格一次性选中的功能，用来批量处理合并单元格留下的空白。
- Ctrl+Enter：把输入的内容同时写进所有被选中的单元格，配合相对引用就能让每个空格取到自己上方一格的值。
- 取消合并单元格：把合并格还原成独立单元格，值只留在左上角那一格，其余位置变成空白。
- 粘贴为值：把公式的结果固化成静态内容，避免排序、删行时引用错位。
- Excel 表格（Ctrl+T）：被命名的数据区域，会随新增行自动扩展，支持按列名写公式，并且不允许存在合并单元格。
- Power Query：Excel 里用来记录并重放数据清洗步骤的工具，新数据来了直接刷新，不必重做一遍操作。

## 来源

1. [Microsoft Support：Guidelines for organizing and formatting data on a worksheet — 同类信息放同一列、区域内不留空行空列](https://support.microsoft.com/en-US/Excel/guidelines-for-organizing-and-formatting-data-on-a-worksheet)
2. [Microsoft Press Store：Creating a basic pivot table — 透视表要求第一行有列标题，Alt+Enter 可在单元格内显示两行表头](https://www.microsoftpressstore.com/articles/article.aspx?p=3204799)
3. [Excel Campus：Pivot Table Field Name Is Not Valid — 表头空白或含合并单元格导致报错](https://www.excelcampus.com/pivot-tables/pivot-table-field-name-not-valid/)
4. [Excel Campus：3 Ways to Fill Down Blank Cells in Excel — 定位条件选空值、`=` 加 ↑、Ctrl+Enter 一次填充](https://www.excelcampus.com/functions/fill-down-blank-cells/)
5. [Microsoft Support：Merge and unmerge cells in Excel — 合并只保留左上角内容，取消合并的操作与结果](https://support.microsoft.com/en-us/excel/get-started/merge-and-unmerge-cells-in-excel)
6. [Microsoft Learn：Unpivot columns（Power Query） — 把横向排列的列转成属性—值两列](https://learn.microsoft.com/en-us/power-query/unpivot-column)

---

原文：https://pangzhengboyin.com/articles/excel-tabular-data-shape-and-cleanup-98a9d6a5

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com