绝对单元格引用和相对引用

编写公式时，引用单元格或单元格区域是最常见的操作。Excel将单元格引用分为绝对引用、相对引用和混合引用。如果行号列标前面有$符号，则是绝对引用，否则是相对引用。

理解单元格引用。

我们使用下面的图1到图4来演示单元格引用。

如图1所示，使用了绝对引用，也就是说，被引用单元格的行号和列标签前面有一个$符号。单元格C2中的公式为：

=$ 110澳元

下拉到单元格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单元格的值，实际上应该是“这个单元格左边第二个单元格的值乘以左边第一个单元格的值”。公式向下复制后，虽然单元格发生了变化，但仍然意味着该单元格左侧第二个单元格的值乘以左侧第一个单元格的值。

也就是说，我们看到的只是表象。使用相对引用时，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所示。除了D2，D列的所有单元格都是空的，所以除了B2，所有单元格的值都是0。

图11。

但是，我们需要的是A列的单元格会随着当前单元格的位置变化而自动调整，而D列的单元格在单元格D2中始终是固定的。此时，您需要使用单元格的绝对引用，如下图12所示。

在B2单元格中输入公式：

=A2*$D$2

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

图12。

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

当我们需要一直使用一个单元格或单元格区域时，我们应该使用绝对引用。

当然，也可以使用混合引用。例如，D$2表示行不变但列可以改变，d $2表示列不变但行可以改变。

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

=$ A5 * 2澳元

向下拖动到单元格B8，向右拖动到列d。绝对引用的行或列不变，而相对应用的行或列将自动调整，以获得正确的单元格数据。

图13。

相对参考文献应用示例。

示例1:条件格式的相对引用。

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

=$B260

列是固定的，行根据单元格进行调整。

图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的单元格引用原理，尤其是相对引用原理。在以下文章介绍的公式中，我们会看到这些原则无处不在。