夜雨聆风
Excel公式一拖就废!90%的人栽在“引用方式”上
点击蓝字 关注我吧！
5
助理 小黄
23:26
老鱼，我用VLOOKUP一次
返回多列数据，向右和向下
拉疯狂报错，全是#N/A
嗯~~大概率是引用方式
没设好。
在Excel中，公式引用看起来很基础，实际上是高阶玩法的基础。掌握了它，才能在复杂公式中游刃有余。
后台发送“引用”，可以免费获得练习文件哦！
一、什么是引用？
简单来说，“引用”就是在告诉EXCEL：“去哪个表格里去拿数据。”
比如，在C1单元格输入=A1+B1，就是在引用A1和B1单元格的值来计算。
Excel中有3种引用方式，它们的区别在于：当把公式复制到其他位置时，引用的单元格地址会不会跟着变。
-
相对引用，如：A1；
-
绝对引用，如：$A$1；
-
混合引用，如：A$1或$A1。
二、3种引用解析
1.相对引用“聪明的跟屁虫”
【场景】
计算每个员工的奖金，奖金等于工资×10%。
【方法】
在D2输入式=C2*10%，意思是：取D2左边的第1格（C2）*10%。
把公式复制到D3，就自动变成了：取D3左边的第1格（C3）*10%，以此类推。
【总结】
相对引用是Excel中的默认方式。它只认”相对位置”(前后左右、第几个)。无论公式拖到哪里，它都会死守这个相对位置，自动指向新的单元格。
2.绝对引用：固定的“定海神针”
【场景】
还是计算员工奖金，奖金比例10%固定在C1单元格。
【方法】
在D3输入公式=C3*$C$1，当公式复制到D4单元格时，变成=C4*$C$1，以此类推。
【总结】
3.混合引用：灵活的“混血儿”
【场景】
制作九九乘法表。
【方法】
在B2单元格输入=$A2*B$1，再向右和向下填充就可以了！
【逻辑】
1.$A2：锁列不锁行。
-
向下复制公式，行会随着向下拖动变化（2→3→4→5→……）；
-
向右复制公式，列固定在A列。
2.B$1：锁行不锁列。
-
向下复制公式，行固定在第1行；
-
向右复制公式，列会随着向右拖动变化（B→C→D→E→……）。
各单元格公式↑
混合引用是解决双向填充（既向右又向下）的神器。
三、经典实操：为什么VLOOKUP报错
小黄要解决的问题是，要从花名册中，提取多个人的多项信息。写一个公式，向右、向下引用返回全部数据。
【错误做法】
在K2输入：
=VLOOKUP(J2,B1:H13,MATCH(K1,B1:H1,0),0)，直接向右和向下引用，会疯狂报错。
向右、向下引用报错↑
为什么会这样？我们以L2单元格的公式为例说明。
公式里全部是相对引用，向右拉到L2单元格时，Excel会“自作聪明”地调整所有参数，变成错误公式：
=VLOOKUP(K2,C1:I13,MATCH(L1,C1:I1,0),0)
L2单元格的公式↑
L2的错误公式和正确公式对比：
正确写法：
在K2输入
=VLOOKUP($J2,$B$1:$H$13,MATCH(K$1,$B$1:$H$1,0),0)，再向右和向下引用，就会得到正确的结果。
【补充说明】
MATCH函数的功能是：返回”查找值”在”查找区域”中的相对位置序号(从1开始计数)。
语法是=MATCH(查找值,查找区域,[匹配方式])。
K2单元格中，公式里的第3参数=MATCH(K$1,$B$1:$H$1,0)：
-
查找值：K1(岗位)；
-
查找区域：B1~H1，B1是第1列，C1是第2列，D1(岗位)是第3列；
-
函数最终返回3。
四、神奇快捷键F4
除了按键盘上的：Shift+4键输入按$，还可以按F4键（笔记本电脑按Fn+F4）。
比如，D2的公式是=C2*10%，选中其中的C2：
-
按1次F4：$C$2，绝对引用；
-
再按1次F4：C$2，混合引用，锁行；
-
再按1次F4：$C2，混合引用，锁列；
-
再按1次F4：C2，相对引用。
公式写错不可怕，可怕的是不知道哪里“跑偏啦”！
推荐阅读：
点赞、关注
再划走哦！