混合引用：钉住行或钉住列

混合引用是中间的第三种状态

前面讲引用时把四种写法列成了一张表，其中两种带一个美元符号的被归到混合引用这一类。它不是过渡状态，而是一个独立且极常用的工具，只是因为它容易和绝对引用混在一起讲，才常被忽略。混合引用的定义很干脆：行和列锁住其中一个，另一个放开。

=$A1 锁列不锁行，往下拉行号会变，往右拉列不动。=A$1 锁行不锁列，往右拉列标会变，往下拉行不动。两者的共同点是只锁一个维度，所以它们适合的场景也集中在一种类型：二维表里横竖各有一个需要固定的参照。

理解混合引用的关键在于把行和列当成两条独立的轴。引用一个格子，本质上是给出了一个坐标：列的字母加行的数字。锁列就是固定横坐标，锁行就是固定纵坐标。一个坐标点要定位到另一条直线上的某个位置，只需要固定其中一个维度就够。混合引用就是这个原理的直接应用。

二维汇总表是最典型的主场

假设有一张销售表，A 列是区域名，第 1 行是月份名，中间是金额。现在要在右侧做一个交叉汇总，行的方向按区域、列的方向按月份分别统计。这种表如果用手写公式，或者用普通引用填充，很容易出现错位。

正确的做法是让公式里同时锁住行标题所在的列和列标题所在的行。假设区域标题在 A 列，月份标题在第 1 行，汇总区从 G2 开始。写 =$A2*B$1 这种形式的结构，向下填充时区域引用跟着行号走，向右填充时月份引用跟着列标走，两个方向互不干扰。这个写法和前面乘法表的原理完全一致，区别只在于业务含义不同。

判断该锁哪一边有个口诀：横着看锁行，竖着看锁列。意思是，如果你要往下填充（纵向复制），就检查公式里哪个引用不应该随行号变化，那个引用要锁行，也就是在数字前加美元符号；如果你要往右填充，就检查哪个引用不应该随列标变化，那个要锁列，在字母前加。听起来绕，实际上对着乘法表练两遍就能形成条件反射。

用 VLOOKUP 做双向查表

混合引用在查找场景里有个漂亮的应用，就是把 VLOOKUP 的返回列序号变成动态的。正常写 VLOOKUP 时第三参数是写死的数字，比如要取第 3 列就写 3。想做一张查询表，横向列出要查的字段名，让公式自己判断该取第几列，就得让这个数字活起来。

写法是用 MATCH 去找字段名在表头里排第几：=VLOOKUP($A2,数据区,MATCH(B$1,表头行,0),0)。这条公式里有三处引用都用到了混合或绝对：$A2 锁住查找值所在的 A 列，向下填充时换成下一个姓名；B$1 锁住字段名所在的第一行，向右填充时换成下一个字段；数据区用绝对引用锁住不动。一条公式填满整片查询区，字段增加或调整顺序都不用改公式。

这个写法的价值在于可维护。如果哪天要把返回的字段从“部门”改成“职级”，不用去翻公式改那个数字 3，只要改表头的字段名，公式自动跟着变。表头成了唯一的口径来源，改一处全局生效。传统的做法是每个字段单独写一条公式，字段顺序一变就要全表返工，成本差出一个量级。关于这种动态列的更多组合，参见多条件聚合的四种写法对比。

对角线填充：混合引用的隐藏能力

有一种技巧只有混合引用能做：对角线填充。普通的填充是横平竖直的，向下或向右一整片。但如果你想要的是沿对角线展开，比如一张表里每行的数据要错开一格排列，单纯用相对引用做不到，因为相对引用在两个方向同时偏移。

思路是让行和列的反方向互相抵消。假设 A 列有一串原始数据需要错位铺开，在目标区左上角写一条只锁一个维度的公式，然后同时向右和向下填充，偏移量会在其中一个方向累积、另一个方向被锁住，形成错位效果。这条技巧在排班表、座位表、以及把一维列表转成上三角或下三角矩阵时非常好用。它的适用面不宽，但真需要的时候找不到替代方案。

容易出错的地方

第一个坑是美元符号加错位置。锁列是在字母前，锁行是在数字前，写反了得到的恰好是另一种效果。$A1 和 A$1 差一个字符，行为完全不同，公式不会报错，结果却错得离谱。写完后把公式向下和向右各填一格，看看指向对不对，这是最直接的验证方法。

第二个坑是混合引用和表格的冲突。区域一旦转成正式表格，引用就变成了结构化引用，不需要也不应该再用美元符号。这时候继续沿用混合引用虽然还能工作，但表格的结构优势就白费了。表格里增删行列时，结构化引用自动跟随，而用美元符号写死的引用不会自动扩展。用表格就用结构化引用，别混着来。

第三个坑是复制到别的工作表。跨表复制时，混合引用的行为和在原表里一致，按偏移调整。但工作表的切换会让人产生“这是另一张表了引用应该重新来”的错觉，实际不会。如果目标表的结构和源表不同，最好重新写引用，别直接粘。

第四个坑是模板文件的相对基准。做模板时公式写在某个格子，复制出去给别人用，相对引用的偏移是基于公式位置计算的，所以模板复制到不同位置依然正确。但如果有谁不小心用剪切方式移动了公式，引用基准会整体平移，之前算好的偏移关系可能全部错位。模板公式最好锁定关键引用，或者干脆用名称定义。

从现象倒推该锁哪一边

写引用时如果一时拿不准，可以用反推法。先在最后一个格子（填充范围的右下角）写出你期望的正确公式，然后和起始格子的公式对比，看哪些部分变了、哪些没变。变了的维度不加美元符号，没变的加。这个方法不依赖直觉，只依赖对比，几乎不会出错。

举个例子，起始格写 =B2*C2，你希望在最后一格该引用的是 C 列的行标题和某行的列标题。通过对比就能发现，B 列这个引用在填充过程中应该固定不变，所以加锁列；C 列的行号应该跟着走，所以不锁。对比完成后回头改起始公式，一次到位。

这个方法的好处是它把“引用怎么写”这个抽象问题，转变成了“目标公式长什么样”的具体问题。人对具体的东西判断更准。练几次之后不用再走这个流程，看到表格布局就能直接写出正确的引用。

混合引用在排班和错位表里的用法

排班表是把混合引用用到极致的一类表。假设一个月三十一天横排成列，员工姓名竖排成行，需要在每人每格填上当天是第几个工作日。判断一个日期是当月的第几个工作日，公式需要同时引用行里的日期和列的日历表，还要排除周末。这类公式的核心思路是把行、列两个维度的固定参照分别锁住，让中间的偏移关系自己去跑。

更常见的错位需求是把一列长数据铺成矩阵。比如一份人员名单，A 列是姓名共 30 人，现在要排成 6 行 5 列的座位表。最笨的办法是一个个复制粘贴，聪明的办法是用混合引用加错位填充：在目标区左上角写一条引用行号和列标组合出来的位置公式，锁住其中一个维度，另一个放开，填充时自然形成错位展开。这种技巧在做考勤表、值日表、分组名单时特别省事，一次填充顶手工排半小时。

错位填充的原理值得说清楚。正常的方形填充是行和列各自独立递增，得到的引用是行列都在走。想要的是错位，就得让一个维度递增的同时另一个维度被固定住，这样每次填充只在一个方向推进。混合引用恰好提供了这个能力，锁住一个维度等于关掉了那个方向的推进，剩下的方向按步长往前走。想明白这层，各种奇怪的排版需求都能推导出写法。

横向汇总和纵向汇总的公式可以共用

做双向汇总时，很多人会分别写两套公式：横向一套用 SUM 算行合计，纵向一套算列合计。其实用混合引用加一个通用写法，两边可以共用同一条公式的填充逻辑。

思路是把合计区放在交叉表的外围，行合计取的是当前行整段数据，列合计取的是当前列整段数据。写公式时把数据区的起始位置锁住，另一端随填充位置变化，这样横向填充得到行合计，纵向填充得到列合计。一条公式覆盖整圈合计区，填充的时候贴着数据区外沿走一圈即可。这个写法的好处是数据区变化时只需要改公式一次，不用去兼顾行和列两套逻辑。

这种共用写法的关键还是那句口诀：横着填锁行，竖着填锁列。既然合计区横竖都要填，就把数据区的一端全锁、另一端全放，让填充自己决定推进方向。遇到不规则的表格，先画一下填充路径，看每一步要指向哪里，引用的写法自然就出来了。

一个具体的例子。数据区是 B2 到 F11，要在 B12 到 F12 加列合计，在 G2 到 G11 加行合计。列合计的公式写在 B12，需要始终引用 B 列的第 2 行到第 11 行，横向填充时列标要跟着走，所以行号锁住：=SUM(B$2:B$11)。行合计的公式写在 G2，需要始终引用第 2 行的 B 列到 F 列，纵向填充时行号要跟着走，所以列标锁住：=SUM($B2:$F2)。两条公式各自的锁法截然不同，但都是混合引用，且都只锁了一个维度。理解了这一点，任意形状的汇总区都能应付。

跨工作表做二维汇总

混合引用在跨表汇总时的价值更明显。假设十二个月的数据分别放在十二张工作表里，表名是一月到十二月，现在要做一张年度汇总，行是部门，列是月份。每一格要引用对应月份表里对应部门的数字，这就要求公式能同时定位到正确的工作表和正确的行。

一种写法是先让月份名成为列标，用 INDIRECT 拼出工作表名，再用混合引用定位行：=INDIRECT(B$1&"!$C"&ROW())。这条公式里，B$1 给出月份名，锁行不锁列，向右填充时切到二月、三月；$C 锁住部门所在的 C 列，ROW 函数取当前行号，向下填充时自动换成下一个部门。一条公式填满整张汇总表，加一个月只需多插一列表名。

INDIRECT 的问题前面说过，它易失且拖慢大表。如果只是十二个月、几百行规模，用起来没什么感觉；如果表格很大，更稳的做法是把十二张表先合并成一张长表，加一个月份列，然后用 SUMIFS 按月份和部门两个条件取值。两种思路各有场景，小表图省事用第一种，大表图稳定用第二种。

跨表引用还要注意表名的规范。用中文表名没问题，但如果有空格或特殊符号，引用时要加单引号，比如 '一月 明细'!$A$1。表名最好不要随月份改名，一旦改名所有引用它的公式都会变成错误值。模板文件尤其要注意这一点，写公式时留好表名结构，别让后来的人随手改。

常见问题

混合引用和绝对引用怎么快速区分

看美元符号的数量。两个都是绝对引用，一个都没有是相对引用，恰好一个是混合引用。记住这个数量对应关系就不会混淆。至于混合引用锁的是行还是列，看美元符号在哪个字符前面：在字母前锁列，在数字前锁行。

二维汇总表一定要用混合引用吗

不一定，但用了会省很多事。替代方案有两种：一是每个格子单独写一条公式，不用填充，工作量随表格变大而暴增；二是把数据转成表格用结构化引用，靠表名和列名定位，也不需要混合引用。混合引用是普通区域里最经济的选择，表格化之后就不再需要它。

为什么填充后只有对角线上的结果是对的

说明引用只锁了一个维度，而你的表格需要两个维度各固定一个。这时候会看到结果沿对角线正确，其余错位。补上另一个维度的美元符号即可。这种错误的表现很有特点，看到对角线规律就能立刻判断是引用锁得不够。

数据透视表的字段还能用混合引用吗

可以，但通常没必要。透视表的取值一般靠透视缓存，公式引用的是透视表区域的固定格子，本身不需要偏移。需要引用透视表结果做二次计算时，用绝对引用锁住那个具体格子即可，不涉及混合引用。真正需要混合引用的还是普通区域的成片填充。

表格结构化引用能不能锁定某一行

结构化引用里可以写 表1[[#标题],[金额]] 这类形式来指向特定行，但它和美元符号是两套机制。结构化引用靠关键字定位，不受填充偏移影响，所以不存在锁不锁的问题。想在表格里取固定的某一列做参照，直接在公式里写列名就行，不用想美元符号。

公式审查时怎么快速看出引用错了

把公式复制到范围外的一格里，看它引用谁。或者选中公式按 F2 进入编辑，Excel 会用不同颜色标出引用区域，肉眼能看出指向哪里。批量检查时，在公式选项卡里点显示公式，整片区域展开成公式文本，比对相邻几行的引用偏移规律，一眼就能发现哪一行的锁法和其他不一样。