# 公式拖下去就错了：相对引用、绝对引用和混合引用分别锁住了什么

$ 符号到底锁住引用的哪一部分，什么时候必须锁，以及拖完公式后怎么核对

> Excel 函数 · 公式引用 · 表格计算 · 约 7 分钟 · 09 月 26 日

## 本篇要点

1. 公式里的 `B2` 记的是“相对公式所在单元格的位置”，不是固定地址；因此往下拖会改行号，往右拖会改列标，这正是拖满一整列能自动对齐的原因。
2. 只有在某个引用不该跟着走的时候才会出错，比如整列都要乘同一个汇率格 `F1`，写成 `B2*F1` 往下拖会变成 `B3*F2`，空单元格在乘除里当 0，结果不报错却是 0。
3. `$` 只锁它紧跟着的那一部分：`$B2` 锁列、`B$2` 锁行、`$B$2` 两者都锁、`B2` 两者都不锁；往下拖只动行号，往右拖只动列标。
4. 判断方法是问两句：往下拖时该不该跟着走，往右拖时该不该跟着走；两种“锁多了”和“该锁没锁”各有典型症状——每行结果一样，或除第一格外全是 0。
5. SUMIF 是两种需求同时出现的典型：`$B$2:$B$100` 和 `$D$2:$D$100` 要全锁，条件 `G2` 不能锁，否则数据区会整体下滑一行，悄悄漏掉一条记录。
6. `$B2`、`B$2` 这类混合引用的不可替代之处，是在交叉表里让一半跟着行跑、一半跟着列跑，比如 `=$P4 * B$3` 一次拖满整块区域。
7. 在编辑栏里选中引用后按 `F4` 可在 `A1`、`$A$1`、`A$1`、`$A1` 之间循环切换；拖完后要抽查最下面、最右面那一格，或用“公式 → 显示公式”把全部公式显示出来核对。
8. 复制粘贴与拖动遵循同一套偏移规则，`Ctrl+X` 剪切移动公式则不改引用；整列引用 `B:B` 因为整列无法位移，天然不受影响。

---

上一篇我们把旧表改成了“表头只有一行、一行一条记录”的规范形状。形状对了，下一步自然就是在这张表上写一个公式，然后把右下角那个小方块往下拖，填满一整列。

很多人到这一步才发现：第一个格子算得对，拖下去结果就乱了。

问题几乎从来不在公式本身，而在公式里那些形如 `B2` 的引用——**它们不是一个固定地址，而是一个会跟着公式走的位置。**

## 公式里的 `B2`，其实是“从我这里往左两格”

假设一张订单表：A 列日期、B 列数量、C 列单价、D 列算金额。

在 D2 写 `=B2*C2`，回车，结果正确。现在把 D2 往下拖到 D10。

D3 里不是 `=B2*C2`，而是 `=B3*C3`；D10 里是 `=B10*C10`。每一行都在乘自己那一行的数量和单价。

原因是：`B2` 在 Excel 眼里不是“B2 这个格子”，而是“从我所在的格子出发，往左两格、同一行”。D2 往左两格是 B2；公式复制到 D3 之后，出发点变成 D3，往左两格就落在 B3 上了。这就是**相对引用**：记的是相对位置，不是绝对坐标。

拖动时，Excel 按你移动的行数、列数，为每一个相对引用重新算位置 [1]。往下拖一格，所有相对部分的行号加 1；往右拖一格，所有相对部分的列标加 1。

所以“跟着走”不是 Excel 的毛病，恰恰是它的设计。正因为会跟着走，你才只需写一次公式就能拖满整列，不用手敲一百遍。

## 什么时候“跟着走”就是错的

换一个场景。B 列是美元报价，你想在 C 列算人民币，汇率写在一个固定格子里，比如 `F1` 填 7.1。

C2 写 `=B2*F1`，往下拖。C3 变成 `=B3*F2`——`F2` 是空的。Excel 在乘除运算里把空单元格当 0 用，于是 C3 显示 0，而且**不报错**。继续往下拖，除了第一行，整列都是 0。

你真正想说的是：B 列跟着行走（第 3 行就乘第 3 行的报价），但汇率永远在 `F1` 那一格，不该动。

办法是在不想动的那部分前面加一个 `$`。C2 改成：

`=B2*$F$1`

再往下拖，C3 里是 `=B3*$F$1`，C10 里是 `=B10*$F$1`，整列都乘同一个汇率。

于是有两种典型症状，可以当成报警灯：

- **拖下去每一格结果完全一样**：该跟着走的被锁死了。
- **除了第一格，下面全是 0 或莫名其妙偏小**：该锁的没锁，引用滑到了空白格或别的区域。

## `$` 锁住的，只是它紧跟着的那一小部分

最常见的误解是以为 `$` 会把整个引用锁死。不是。一个引用由两部分组成——**列字母**和**行号**。而拖动也只分别影响这两部分：往下拖改行号，往右拖改列标。

`$` 贴在谁前面，就锁住谁：

- `$` 贴在列字母前，比如 `$B2`，往右拖时列标不动。
- `$` 贴在行号前，比如 `B$2`，往下拖时行号不动。
- 两个都贴，`$B$2`，整格钉死。
- 一个都不贴，`B2`，两头都跟着走。

四种写法各拖一格之后长这样：

| 写法 | 往下一格 | 往右一格 |
| --- | --- | --- |
| `B2` | `B3` | `C2` |
| `$B$2` | `$B$2` | `$B$2` |
| `B$2` | `B$2` | `C$2` |
| `$B2` | `$B3` | `$B2` |

官方文档的说法是同一个意思：`$A$1` 是绝对引用；`A$1` 和 `$A1` 是混合引用，只有没加 `$` 的那一半会随复制调整 [1][2]。

所以写公式前不用背表，问两个问题就够：

1. 把这个公式**往下拖**时，这个引用该不该跟着往下走？不该，就在行号前加 `$`。
2. 把它**往右拖**时，该不该跟着往右走？不该，就在列标前加 `$`。

两个都该跟着走，什么也不加；两个都不该，就都加。

## 一个天天要用的例子：SUMIF

假设一份明细：`B2:B100` 是区域，`D2:D100` 是数量。G 列从 G2 开始列着几个要统计的区域名，H2 要算其中第一个区域的合计。在 H2 写：

`=SUMIF($B$2:$B$100, G2, $D$2:$D$100)`

这个公式里同时出现两种需求：

- 查找区域 `$B$2:$B$100` 和求和区域 `$D$2:$D$100`：整片数据不能动，所以两端都加 `$`。往下拖到 H3，它还是 `$B$2:$B$100`。
- 条件 `G2`：你要它跟着换。拖到 H3，它变成 G3，正好是下一个区域名。

如果忘了锁区域，H3 会变成 `=SUMIF(B3:B101, G3, D3:D101)`：起点掉了一行，末端滑到第 101 行，于是第 2 行的数据整条被漏掉，多出来的空行没有影响。结果是数字偏小一点点，还不报错，很容易被误判成“数据本身有问题”。

如果这张汇总表还要往右拖——右边几列换成不同月份的求和——那条件那一列也该锁住列，写成 `$G2`，这样往右拖时它始终看着 G 列。

## 混合引用真正不可替代的用场

`$B2` 和 `B$2` 这两种写法，典型用场是一张**交叉表**：行方向是一类东西，列方向是另一类东西，每个格子要把“本行的东西”和“本列的东西”配在一起。

举个具体例子。第 2 行 B2:M2 是 1 月到 12 月的月份标题；第 3 行 B3:M3 是各月的分摊比例；A4:A10 是七个区域的名称；P4:P10 放着各区域的全年目标。现在要在 B4:M10 这块区域里算出每个区域每个月的目标金额。

在 B4 写：

`=$P4 * B$3`

然后先往右拖到 M4，再选中 B4:M4 往下拖到第 10 行。看它怎么走：

- `$P4` 锁列、放开行：往右拖时它一直盯着 P 列；往下拖时行号跟着变，第 5 行取到 `$P5`，也就是下一个区域的全年目标。
- `B$3` 锁行、放开列：往下拖时它一直盯着第 3 行；往右拖时列标跟着变，取到 `C$3`，也就是下一个月的比例。

两半各管一个方向，一个跟着行跑、一个跟着列跑，一块公式就铺满整张表。凡是“横竖都要对齐”的表格，都离不开这两种混合写法。

## 不用手打 `$`，按 F4

在编辑状态、或者选中编辑栏里的那段引用时按一下 `F4`，它会在下面这个循环里转 [3][1]：

`A1` → `$A$1` → `A$1` → `$A1` → `A1`

不用记顺序，盯着编辑栏看，转到你想要的那个就停手。如果选中的是一整段区域（比如 `B2:B100`），按 `F4` 会给两个端点一起加 `$`，这正好是 SUMIF 里需要的样子。

两个小提醒：Mac 版 Excel 上这个快捷键是 `Command + T` [1]；不少笔记本把 F4 设成了音量或亮度键，需要先按住 `Fn` 再按。

## 拖完，抽查最后一格

这一步只花五秒钟，能省掉半小时。公式填完后，**点最下面那一格、最右面那一格**，看编辑栏里的公式是不是指向了正确的格子。第一格对不代表最后一格对——恰恰相反，引用如果错了，最后一格的偏差最大。

想一次看全，用功能区的“公式 → 显示公式”切换一下，整张表会把公式原文直接显示出来，再点一次切回结果。

## 两个容易踩的边界

**复制粘贴和拖动是同一件事。** `Ctrl+C` 复制一格公式，粘到下面或右边，相对引用照样按位置移动；拖填充柄只是这件事的快捷方式。跨工作表粘贴也一样，按你贴到的位置重新定位。但 `Ctrl+X` 剪切移动公式时，引用不会变——公式只是换了个地方待着，它指的还是原来那些格子。

**整列引用没有这个问题。** 很多人习惯写 `=SUMIF(B:B, G2, D:D)`，往下拖多少行都不用加 `$`，因为 `B:B` 表示整列，整列本来就动不了。省事，代价是范围过大、数据多时会拖慢计算；数据量大的时候，写清楚 `$B$2:$B$100` 更稳妥。

## 术语表

- 相对引用：写成 `B2` 的引用记的是“相对公式所在单元格的位置”，复制到别处时行号列标会跟着一起移动。
- 绝对引用：写成 `$B$2`，列和行前面都有 `$`，复制到任何位置都指向同一格。
- 混合引用：`$B2`（锁列、放开行）和 `B$2`（锁行、放开列），用来让引用的一个方向固定、另一个方向跟着走。
- 偏移规则：往下拖只改行号、往右拖只改列标，只有没被 `$` 锁住的那一半会变。
- F4 切换：在编辑栏里选中引用后按 F4，在 `A1`、`$A$1`、`A$1`、`$A1` 之间循环。
- 显示公式：用功能区的“公式 → 显示公式”切换，整张表把公式原文显示出来，用来核对拖动后引用是否指向了正确的格子。

## 来源

1. [Microsoft Support: Switch between relative, absolute, and mixed references — 复制规则、$A$1 与 A$1、$A1 的区别、F4 切换与 Mac 版 Command + T](https://support.microsoft.com/en-us/excel/switch-between-relative-absolute-and-mixed-references)
2. [Microsoft Support: Overview of formulas in Excel — 相对、绝对、混合引用在复制或填充时的调整方式](https://support.microsoft.com/en-us/excel/get-started/overview-of-formulas-in-excel)
3. [Excel Dashboard School: Toggle absolute and relative references — F4 的完整循环顺序 A1 → $A$1 → A$1 → $A1 → A1](https://exceldashboardschool.com/shortcut-to-toggle-absolute-and-relative-references/)

---

原文：https://pangzhengboyin.com/articles/excel-relative-absolute-mixed-references-73404c11

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