# 拖公式为什么把结果拖坏：相对引用、绝对引用、混合引用与 $ 该加在哪一段 拖动填充时引用怎么变、$ 冻结的是哪一段、F4 怎么循环切换，以及哪些格子必须锁死 > Excel 公式与引用 · Excel 函数基础 · 约 7 分钟 · 09 月 23 日 ## 本篇要点 1. 公式里的引用默认记录的是相对方位，拖动或复制公式时，每个引用会按位移量同时调整行号和列号：往下拖行号加，往右拖列号加。 2. $ 是冻结符，写在列字母前冻结列，写在行号前冻结行，被冻结的那一段不参与位移。 3. 四种引用只是冻结程度不同：A1 都不冻、$A$1 都冻、A$1 只冻行、$A1 只冻列；混合引用指后两种。 4. 判断 $ 加在哪，只问一句：这个引用在我拖动的方向上该不该跟着走；该走就不加，不该走就在对应那一段前加。 5. 只往一个方向拖时用不到混合引用，因为它决定的是“另一个方向”的行为；混合引用只在同一块区域既往下拖又往右拖的二维填充里才有意义。 6. 乘法表 B2 写 =$A2*B$1 是混合引用的标准例子：$ 锁住标签所在的 A 列和第 1 行，放开数据要走的行列。 7. 在编辑公式状态下按 F4 可循环切换：A1 → $A$1 → A$1 → $A1 → 回到 A1。 8. 税率、单价、总分这类全表共用的单个格子，以及查找时那张整块参照区域，都必须锁住；但每行取本行数据的公式不该加 $，加错比不加更糟。 9. 第一格算对不代表拖下来对，检查要挑最后一个格子看公式栏；$ 只影响复制行为，不改变单个公式的计算结果。 --- 上一篇讲 Word 时说过一句关键的话：页码不是挂在“页”上，而是挂在“节”上。Excel 里有一件性质很像的事——公式里的引用不是记住“某个格子”，而是记住一个**相对方位**。正因为如此，公式往下拖的时候，Excel 会主动去改你写的引用。改对了皆大欢喜，改错了就是“第一格明明是对的，拖下来一排全错”。 这篇就讲清楚这一件事：拖动公式时引用怎么变，$ 是拿来干什么的，什么时候必须加。 ## 拖动的时候，Excel 到底改了什么 在 D2 里写 `=B2*C2`，你心里想的是“B2 乘 C2”，Excel 记录的是“同一行里，我左边第二列那个格子，乘我左边第一列那个格子”。这是默认的**相对引用**：它记的是相对位置 [1]。 于是把它往下一格拖到 D3，Excel 保留这个相对距离，公式自动变成 `=B3*C3`。再往下拖到 D5，就是 `=B5*C5`。规律很简单： - 往下拖 $n$ 格：每个引用的行号 $+n$，列号不变。 - 往右拖 $n$ 格：列号 $+n$，行号不变。 - 复制公式再粘贴到别处，调整方式和拖动一模一样，同样是按位移改写 [1]。 这个默认行为大部分时候是贴心的：一列数量、一列单价，金额公式往下拖，每一行自然就对上了自己那一行。 ## 什么时候这个默认行为就闯祸了 **现场一：固定单价。** 单价放在 `F1` 这一个格子里，D 列是数量，金额公式写成 `=D2*F1`。第一格没错，往下拖到 D3 就变成 `=D3*F2`——F2 是空的，乘出来是 0，再往下几行也都是 0。 **现场二：固定分母。** 要算每个人成绩占全班总分的比例，总分放在 `B8`，公式 `=B2/B8`。拖到下一行成了 `=B3/B9`，分母一路往下跑，比例完全不对。 两个现场的共同点是：被引用的那个格子是**所有行共用的同一个格子**，它不该跟着公式走，可默认的相对引用一定会让它走。要让某一部分“不动”，就得手动拦一下——拦的工具就是 `$`。 ## $ 的作用：冻结紧跟在它后面的那一段 `$` 只有一个含义：**它后面的那一部分不参与位移**。 - `$` 写在列字母前面 → 冻结列，往右拖时列不变。 - `$` 写在行号前面 → 冻结行，往下拖时行号不变。 四种写法的差别就是冻结的程度不同： - `A1`：列、行都不冻结，叫**相对引用**，往哪个方向拖都跟着走。 - `$A$1`：列、行都冻结，叫**绝对引用**，怎么拖都不变。 - `A$1`：冻结行、放开列，叫**混合引用**，往下拖时行号不动，往右拖时列号照变。 - `$A1`：冻结列、放开行，也叫**混合引用**，往右拖时列不动，往下拖时行号照变 [1]。 微软给出的对照表最直观：一个引用被往右下各复制两格之后 [1]： | 原来的写法 | 拖到右下两格后 | | --- | --- | | `$A$1` | `$A$1`（不变） | | `A$1` | `C$1`（列变了，行没变） | | `$A1` | `$A3`（列没变，行变了） | | `A1` | `C3`（都变了） | ## 记不住 $ 的位置，就问一句话 不用背，也不用理解什么底层机制。对公式里的每一个引用，只问一句： > 在我要拖的这个方向上，它该不该跟着走？ 该跟着走，就不加 `$`；不该跟着走，就在那个方向对应的那一段前面加 `$`。 这里的关键词是“方向”。**往下拖时，只有行号会变，列号本来就不会变。** 所以在这种场景里，你要决定的只是“行号锁不锁”。 回到固定单价那个例子：公式 `=D2*F1` 只要往下拖，希望 F1 永远不变。既然只往下拖，列号一直是 F、不会变，写成 `F$1` 就够用了。实际中大家习惯写成 `$F$1`，那是双保险——万一以后还要往右拖，`F$1` 会变成 `G$1`，而 `$F$1` 依然稳。多锁一段不额外付出代价，所以“两个都锁”是最省心的写法。 反过来也成立：如果公式只往右拖，行号本来就不变，锁不锁行无所谓，只需要考虑列。 ## 混合引用什么时候才真正有用 如果公式只往一个方向拖（考试里绝大多数情况），你其实只会用到“相对”和“绝对”，`A$1` 和 `$A1` 这两种混合引用看着很玄，其实用不上。 混合引用登场的场合只有一个：**同一块区域，既要往下拖，又要往右拖。** 拿一张乘法表举例。`A1` 空着，`B1`、`C1`、`D1` 分别是列标签 1、2、3；`A2`、`A3`、`A4` 分别是行标签 1、2、3。在 `B2` 里写： ``` =$A2*B$1 ``` 然后把 `B2` 往右、往下拖满 `B2:D4` 这一块。 - `$A2`：`$` 锁住 A 列，所以往右拖时，行标签始终还在 A 列取；行号没锁，所以往下拖时能跟到下一行的标签。 - `B$1`：`$` 锁住第 1 行，所以往下拖时，列标签始终还在第 1 行取；列号没锁，所以往右拖时能跟到下一列的标签。 验算一下角落和中间：`B2` 得到 `$A2*B$1`，也就是 $1\times1=1$；往右拖到 `D2` 是 `$A2*D$1`，$1\times3=3$；往下拖到 `B4` 是 `$A4*B$1`，$3\times1=3$；右下角 `D4` 是 `$A4*D$1`，$3\times3=9$。整张表都对。 如果这里偷懒写成 `=A2*B1`，`B2` 的结果碰巧也是 1，看不出来问题，但拖到右下角 `D4` 就变成了 `=C4*D3`——取到的已经不是行列标签了，结果自然对不上。 这就是混合引用的全部用处：**锁住“标签所在的那一行或那一列”，放开数据要走的方向。** ## 用 F4 循环切换，不用手敲 $ 把 `$` 一个一个敲进去容易敲漏，也容易敲到错的位置。更省事的办法是用 F4。 光标放进公式里（双击单元格进入编辑，或者点在公式栏里那个引用上），按 F4，引用会按顺序循环 [2]： `A1` → `$A$1` → `A$1` → `$A1` → 回到 `A1` 几个容易踩的点： - 必须处在**编辑公式**的状态，只是单击选中了单元格，按 F4 没有反应。 - F4 只影响光标所在、或者你刚选中的那一个引用，同一个公式里别的引用不受影响。 - 多按几次就能转回原来的样子，不用怕按错。 - 有些笔记本的 F4 被功能键占用，需要按 Fn+F4。 ## 考场上最该锁死的几处 归纳起来，凡是“所有行共用同一个格子”的地方都要锁： - 税率、单价、总分、平均分这类只写在一个单元格里的值，公式里写成 `$F$1`、`$B$8` 这种绝对引用。 - 二维填充时，作为行标签的那一列、作为列标签的那一行，用 `$A2`、`B$1` 这类混合引用。 - 公式里如果引的是一整块区域（比如按姓名去某张表里查成绩），这块区域通常要整块锁住，写成 `$A$2:$D$100`，因为公式往下拖时，这张参照表本身不该挪动。到时候会发现，出错的根源和今天讲的是同一件事。 用之前记住一句判断：**这个引用，对我拖动的方向来说是“自己这一行的数据”还是“全表共用的东西”？** 前者放手，后者锁死。 ## 自检和边界 拖完公式以后，别只看第一格——第一格恰恰是错了也看不出来的那一格。挑最后一个格子点一下，看公式栏里的引用是不是你预期的那几个，这是最快的检查方式。 也要防止把 `$` 用过头： - 不锁不一定是错。`=B2*C2` 这种“每一行都拿自己这一行的两个数”的公式本来就该跟着走，硬加上 `$` 会让所有行都去乘第一行的数，全错。 - `$` 只在公式会被复制、被拖动的时候才有意义。它不改变单个公式的计算结果：第一格的值，加不加 `$` 完全一样。 - 拖错了不要一格一格手改。把原始公式的引用改对，重新拖一遍；或者直接 Ctrl+Z 撤销回到拖动之前，成本更低。 ## 术语表 - 相对引用：默认写法（如 A1），拖动公式时行号和列号都会按位移量改变，适合“取本行自己的数据”。 - 绝对引用：写成 $A$1，列和行都被冻结，公式怎么拖都指向同一个格子，适合税率、单价、总分这类全表共用的值。 - 混合引用：只冻结列（$A1）或只冻结行（A$1），在既要往下拖又要往右拖的二维填充里，用来锁住行标签所在列或列标签所在行。 - 冻结符 $：写在列字母前冻结列，写在行号前冻结行，被冻结的部分不参与位移。 - 拖动填充的位移规则：往下拖 n 格行号加 n、列号不变；往右拖 n 格列号加 n、行号不变；复制粘贴公式的调整方式与拖动相同。 - F4：在编辑公式的状态下，把光标所在或选中的引用在相对、绝对、混合四种写法之间循环切换。 ## 来源 1. [Microsoft Support: Switch between relative, absolute, and mixed references — 引用类型含义与复制两格右两格的对照表](https://support.microsoft.com/en-us/excel/switch-between-relative-absolute-and-mixed-references) 2. [Excel Quicker: Absolute and relative references — F4 循环切换顺序与生效条件](https://www.excelquicker.com/tips/recent-tips/absolute-and-relative-references/) 3. [Microsoft Support: Overview of formulas in Excel — 相对、绝对、混合引用行为总览](https://support.microsoft.com/en-us/excel/get-started/overview-of-formulas-in-excel) 4. [Microsoft Support: Create or change a cell reference — 在公式栏中选中引用并用 F4 切换类型](https://support.microsoft.com/en-us/excel/create-or-change-a-cell-reference) --- 原文：https://pangzhengboyin.com/articles/excel-relative-absolute-mixed-cell-references-74cbba48 > **庞征博引** · 想学的，慢慢都会 > > 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。 > > 开始你自己的连载 → https://pangzhengboyin.com