Excel 怎么切换绝对引用和相对引用
公式往下拖就错位,多半是引用没锁对。讲清 $ 加在字母前还是数字前、F4 怎么轮换,以及混合引用什么时候该用。
把公式往下拖一格,结果全错,或者整列算出来都是同一个数,八成是引用没锁对。相对引用会跟着拖动方向跑,绝对引用钉死在原地,混合引用只钉一半。这篇只讲这三者的区别和切换方法,不涉及具体业务公式。
入口在哪
没有单独的菜单,也没有开关。绝对引用和相对引用是直接写在公式里的记号:同样是左上角那一格,写成 A1 是相对,写成 $A$1 就是绝对。要改的地方就在公式编辑栏里的那个单元格地址上。
两种改法:手工在字母前和数字前各敲一个 $;或者把光标停在那个地址中间,按 F4 循环切换。看的是「编辑栏里这一格地址长什么样」,不是单元格本身。
具体步骤
- 先把第一个公式写对,回车确认结果。
- 双击这个单元格进入编辑,或者点一下编辑栏,把光标停在地址中间,比如停在
B2的 B 和 2 之间。 - 按 F4,地址会按四步循环:
B2→$B$2→B$2→$B2→ 回到B2。停在需要的那一步。 - 判断该停在哪:往下拖、往右拖都不该变的(比如税率、单价这类单独放在一格的值),用
$B$2两边都锁。 - 只有行不该变(比如每页第一行是表头),用
B$2,列照常跟着走;只有列不该变,用$B2。 - 回车,再拖动填充柄,抽查最后一行手工算一遍。
几个坑
- 只锁了一半还以为是绝对引用:
$B2往下拖没问题,往右拖就错位,症状是「有一半对、一半不对」。 - 整列引用不能用 F4 锁:像
B:B这种写法没有行号可锁,要固定范围就改成B$2:B$100。 - 全表锁死之后再拖动,每一行都指向同一个格子,整列变成同一个数。这时候不是公式坏了,是锁多了。
- 用鼠标点选格子插入地址时要趁地址还在编辑状态按 F4;已经回车了就再进一次编辑,否则按下去没有反应。
- 把公式复制到别的工作表,相对引用会平移到同位置的格子,很容易引用到一片空白。
- Mac 版 Excel 的切换键不是 F4,而是 Cmd+T,或者在公式里手动敲
$;WPS 与 Windows 版 Excel 的 F4 行为一致。
怎么验证
三个动作就够了。第一,拖动之后点开任意一个结果单元格,看编辑栏里的地址有没有跟着变,该变没变、不该变变了,都说明锁错了。第二,双击公式,Excel 会用彩色框把引用区域标出来,框住的位置和被引用的数据对不上就是错。第三,切到公式视图扫一遍,谁在变谁没变一眼能看出来。
最稳的还是挑一行手算:拿计算器按同一套逻辑算一遍,数对上再往下填。
常见问题
按 F4 没反应怎么办
笔记本键盘的 F 键常常被设成功能键(调亮度、调音量),要加按 Fn 才生效,不同品牌布局不一样,按不动就试 Fn+F4。真想常用,可以在系统设置里把功能键改成标准 F 键。
能不能不打 $ 直接手写
能,而且熟练之后更快。要点是 $ 必须紧贴字母或紧贴数字,写在 A 和 1 中间会被当作文本,Excel 会提示公式有问题。
为什么整列算出来是同一个值
因为填充时地址被锁死了,每一行都指向同一格。把需要跟着行走的那一半 $ 去掉,再重新拖动即可,不用重写公式。
一句话总结:绝对引用和相对引用只解决一件事——让不该动的地址别动;判断该锁谁,就看公式往哪个方向拖、哪个方向必须保持不变。