单元格绝对援用与相对援用-英雄云拓展知识分享

在编写公式时，援用单元格或单元格区域是最平常不过的操作了。Excel将单元格援用分成绝对援用、相对援用和混合援用，如果行号列标前面有$号，则是绝对援用，否则是相对援用。

认识单元格援用

我们使用下图1至图4来演示单元格援用。

如图1所示，使用绝对援用，即援用单元格的行号列标前都带有$号。单元格C2中的公式为：

=$A+10

下拉至单元格C8，结果均为11。

如果我们查看这些单元格中的公式，会发现都与单元格C2中的公式相同。这表示，使用绝对援用的单元格，不管怎样变化，该单元格都保持不变。

在公式中，常使用绝对援用来作为单元格区域的起始点。

图1

图2所示为相对利用，在单元格F2中的公式为：

=A1+10

向下拉时，随着行的变化，单元格援用也会随之变化，所得到的结果也随着变化。举例来看，单元格F8中的公式为：

=A7+10

可将图2中的列C与列F比较，体会绝对援用与相对援用的不同。

图2

图3所示为混合援用，行动绝对援用而列为相对援用。

可以看出，由于行不变，因此同一列中的结果相同，不同列中的结果产生变化。

图3

图4所示也为混合援用，行动相对援用而列为绝对援用。

此时，行随着单元格产生变化但列总是A列，因此两列的结果相同。

图4

从上面的介绍可以看出，绝对援用不会随单元格的变动而变化，很好理解。但相对援用会作出相应的调剂，而这正是公式的精巧的地方。

下面我们重点介绍Excel的相对援用原理。

相对援用原理

下图5所示的工作表中，我们要求水果的销售额，很明显，使用列B中的单价乘以列C中的销量即得到销售额。因此，这种情况下，要得到苹果的销售额，就在单元格D2中输入公式：

=B2*C2

图5

同理，要得到其他水果的销售额，就在列D中相应的单元格输入列B乘以列C单元格的公式。如果数据很多，这样一个一个输入将费时费力。事实上，在单元格D2中输入公式后，我们可以拖动复制该单元格公式至下方所有要计算的单元格，快捷计算出结果，以下图6所示。

图6

Excel会自动调剂，让公式使用正确的单元格，从而得到我们想要的结果。

上面的操作，是我们在Excel中常常会进行的操作，经过在一个单元格中输入公式，然后拖动公式到指定的单元格，自动取得想要的结果，非常方便快捷。

为何能够这样呢？

其实，这些看似轻松平常的操作背后，蕴藏着Excel的相对援用原理。

回到上面的例子，再看看单元格D2中的公式，表面上看，表示单元格B2的值乘以单元格C3的值，但实际上应当是“本单元格左边第2个单元格的值乘以左边第1个单元格的值”。公式向下复制后，虽然单元格变了，但表示的意思依然是本单元格左边第2个单元格的值与左边第1个单元格的值相乘。

也就是说，我们看到的只是表象。在使用相对援用时，Excel是以相对输入单元格的位置来处理单元格地址的。

这就是Excel的相对援用原理。

理解相对援用原理非常重要，它是理解Excel公式运算和在名称、数据行之有效的性和条件格式等利用的基础。下面再举一个例子。

以下图7所示的工作表，在单元格C5中输入公式：

=A1+200

表面上看，是求单元格A1中的值加上200后的结果，而实际上Excel认为是将基于当前单元格向上4行向左2列的单元格的值加上200。

图7

拖动单元格C5复制公式至单元格C6，结果以下图8所示，基于当前单元格C6，相干单元格相应地调剂为单元格A2。

图8

将单元格C5复制到单元格E7，此时公式变成：

=C3+200

即，公式中相干的单元格调剂为基于当前单元格E7向上移4行向左移2列的单元格C3，以下图9所示。

图9

上述操作也表示，相对援用是Excel默许使用的援用方式。因此，这种情况下，要结合工作表实际要求来正确使用。如果不正确的使用相对援用，有时会得到毛病的结果。

以下图10所示的工作表，交税金额为工资总额乘以税率。我们先在单元格B1中输入公式：=A2*D2，然后向下拖至单元格B5，明显除B2中的值正确外，其它单元格中的值都是毛病的。

图10

这是由于依照Excel默许的相对援用方式，会自动调剂相干单元格，特别是列D中的单元格，以下图11所示。列D中的单元格除D2外，都为空，因此除单元格B2外，其它单元格的值为0。

图11

但是，我们需要的是列A中的单元格随当前单元格的位置改变而自动调剂，但列D中的单元格永久固定在单元格D2。此时，就需要应用单元格的绝对援用，以下图12所示。

在单元格B2中输入公式：

=A2*$D$2

向下拖动复制公式至单元格B5，得到正确的结果。

图12

在上面的公式中，$D$2是绝对援用，不会随着单元格自动调剂。

当我们需要一直使用某单元格或单元格区域时，就应当使用绝对援用。

固然，还可使用混合援用，例如D$2表示行不变而列可以变化，$D2表示列不变而行可以变化。

例以下图13所示的工作表，我们要计算3种不同税率下的交税金额。在单元格B5中输入公式：

=$A5*A$2

向下拖动至单元格B8，向右拖动至列D。绝对援用的行或列不变，而使用相对利用的行或列将自动调剂获得正确的单元格数据。

图13

相对援用利用示例

示例1：在条件格式中的相对援用

以下图14所示的工作表，要求对分数小于60的单元格背风景设置为红色。使用公式：

=$B2<60

列固定而行随着单元格调剂。

图14

注：条件格式中使用的公式中的单元格援用，是基于公式中所选单元格区域左上角的引对援用。

示例2：在数据行之有效的性中的相对援用

与条件格式一样，在设置数据行之有效的性的公式中包括的单元格援用，也是其于所选区域左上角单元格的相对援用。

以下图15所示的工作表，我们在列A中设置了数据行之有效的性，使得用户在列A中不能输入重复数据。

图15

其中，使用了公式：

=COUNTIF(A:A,A1)=1

当我们选择列A中任一单元格时，公式会自动调剂到当前单元格，以下图16所示。

图16

注意到，公式中使用的A:A也是相对援用，因此可以很容易地将列A中的不允许输入重复值的功能复制利用到其他列。

示例3：在名称中的相对援用

选择工作表单元格A1，在“新建名称”对话框中定义内涵名称GetRightCellValue：

=Sheet7!B1

即获得当前单元格右边单元格中的值，如图17所示。

图17

定义内涵名称后，在工作表中输入该名称时，会获得所在单元格右边单元格中的值，以下图18所示。

图18

如果想要该名称在工作簿其他工作表中也可以使用，那末可以将该名称修改以下（如图19所示）：

=!B1

图19

结语

要想编写出强大且优雅的公式，一定要理解并熟练掌握Excel的单元格援用原理，特别是相对援用原理。在后面的一些文章所介绍的公式中，我们会看到这些原理无所不在。