函数与公式是Excel非常强悍的工具,当然也是最难的部分,函数之多让人看上去眼花缭乱,再加上函数的各种嵌套和数组,不禁让人望而却步。且不说函数的复杂多变,想进阶Excel大师的你总归要硬着头皮跨越这道坎儿。可是,用过函数和公式的童鞋都知道,要学函数,先懂引用。引用被称为学习函数与公式路上的拦路虎,不得不除,下面就让我们揭开引用的面纱,一招制敌。
Excel中引用分为三种,即相对引用,绝对引用和混合引用,前两个比较好理解,用起来也比较方便,至于混合引用……,真的就容易被“混合”了。我们一个一个来说一下。
先看看相对引用。请看下图,
我们用A列与B列的对应数据求乘积,直接在C2单元格输入=A2*B2,回车,下拉就完成了。当我们点击相应的位置的时候会发现,乘积一列所引用的单元格都是一一对应的,也就是随着我们的拖动而移动,就像是我们在初中物理中学到的一个概念叫做相对运动。
再看一下绝对引用,现在我们需要将D列的每一个数值都乘以E2,即将每个数值都缩小十倍,当然我们可以用将E2复制,然后选择性粘贴(乘)到D列。
现在我们用公式来计算,假如我们跟刚才一样,用D2*E2回车,当我们下拉的时候发现下面的数据全是0了,这是因为E列与D列的对应位置都是空置,在计算的时候当0看待。我们希望是把E2锁定,让它在拖动的时候不发生变化,在Excel中我们称之为绝对引用。操作起来也不难,俗话说“有钱能使鬼推磨”,在这里也适用,我们只需要给E2单元格撒点钱就可以让它老老实实待着了。这里的钱就是美元喽,毕竟这是美国公司开发的软件哦,这钱该怎么撒呢?点击E2,然后按一下F4,会发现在E和2之前都有一个美元符号了,意思就是将其列号和行号都锁定,即为绝对引用,这时我们再向下拖动的时候就是我们要的结果了。
列与行都锁定是绝对引用,这个好理解,如果只锁定其中一个呢,就是混合引用了。很多人在混合引用的时候容易出错,不知道该锁定哪一个,今天我们来个了断。请看下面,
我们需要做一个99乘法口诀,希望通过拖动快速完成计算,该怎么操作呢?我们先来分析一下,在B2输入=A2*B1,我们在向右拖动的时候希望A2不动,上面的一行跟着动,当向下拖动的时候我们希望上面的一行不动,左边的一列跟着动。先看向右移动的时候,在第2行的时候希望是A2乘以上面一行,在第三行的时候希望是A3乘以上面一行,以此类推。实质上我们只是将A这一列的列号锁定,行号是需要跟着变动的,因此需要在A前面加一个美元符号,按三次F4就可以了。相对应的,我们向下拖动的时候是希望第一行每个单元格的行号锁定(按两次F4)不变。这样我们在B2单元格输入=$A2*B$1,向右拖动,再向下拖动,搞定。不知你有没有发现规律,在混合引用的时候,即如果引用一列,则锁定列号,如果引用一行,则锁定行号,加在一起是不是像一个桥梁,即两头加美元,中间不需要,所以我们可以将其形象地称之为“过桥引用”。
更多的时候我们并不是同时引用两个单元格的内容,而是只用其中一个,如在使用VLOOKUP函数返回多列数据时需要将第一个参数采取混合引用,即将列号锁定。同样的,使用INDEX与MATCH嵌套使用返回多列时体现的更为明显,即将“过桥引用”分成两个“独木桥”使用(后面的文章会讲到),其实道理是一样的,只要你理解了“过桥引用”,就能灵活应对各种使用环境。