如题所说,公式没写错为什么下拉填充会出错?想必很多童鞋都遇到过这个问题,但只要稍一检查,便会发现其中的“猫腻”!

其实,就是单元格引用方式的问题。在excel中,单元格引用分为绝对引用、相对引用和混合引用。怎么来理解这三个名词呢,下面作者君就以一个实例来介绍。

在下图中,我们要根据姓名来查找其相对的业绩。

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

我们可以直接写入VLOOKUP函数公式:=VLOOKUP(F3,B3:C15,2,0)。

然后下拉填充公式,却发现在查找言承旭的业绩时,出现错误了。

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

我们将目光放到上图编辑栏中公式上,能够发现查找区域发生了变化,从B3:C15转变成了B5:C17。

为什么查找区域会变化呢?这就是因为公式中的单元格区域为相对引用,它会随着下拉填充而移动单元格,但又保持单元格区域的单元格个数不变。

如果我们将公式中的查找区域进行锁定,那么结果就不会出错了吧?

是的,将区域进行锁定,这是通俗的讲法,实则就是对单元格进行绝对引用。

我们输入公式:=VLOOKUP(F3,$B$3:$C$15,2,0)

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

尽管下拉填充,绝对引用的查找区域不会发生变动,而使得公式正常执行。

而接下来作者君还要快速匹配这名同志的提成,同样是直接拉动填充公式,可想而知,不管是相对引用还是绝对引用,都会使结果出错。

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

因此我们便会应用到单元格引用中的混合引用,即既有绝对引用又有相对引用。

如下图中公式:=VLOOKUP($F3,$B$3:C$15,COLUMN()-5,0)

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

$F3,简单理解就是锁定了F列,但不锁定行,因为F3表示的是F列第三行,那么$B$3:C$15,即表示锁定了单元格区域起始单元格B3不变,结尾单元格C15保持行不变,但列C可向右拉取变动。

所以,这个单元格区域就是混合引用,讲白了,就是锁定行不锁定列,或者锁定列不锁定行,以方便向下或向右拉取填充公式。

接下来我们看下向右拉取填充后的公式:=VLOOKUP($F3,$B$3:D$15,COLUMN()-5,0)

Excel公式没写错下拉填充却出错,原来问题出在单元格引用!-天天办公网

很明显查找区域从C列向右扩展到D列,通过第三个参数中的COLUMN函数,更新计算返回的列,而得到准确的结果。

这样,通过公式中单元格区域的混合引用,而达到只写一个公式便可以向右向下拉取填充,快速匹配需要的值。

总结今天的内容,主要就是对单元格三种引用的介绍,大家记住几个关键词,绝对引用是锁定,混合引用一头动,相对引用皆可动。