为什么在Excel公式中使用$：相对和绝对单元格参考

Excel 公式中的单元格引用中的美元符号 ($) 常常令用户困惑，但其原理很简单。美元符号在 Excel 单元格引用中只有一个作用：它告诉 Excel 在将公式复制到其他单元格时，是否更改引用。本教程将详细讲解此功能。

Excel 单元格引用的重要性怎么强调都不为过。了解绝对引用、相对引用和混合引用的区别，您就掌握了 Excel 公式和函数强大功能的一半。

大家可能都见过 Excel 公式中的美元符号 ($) 并想知道它是什么。事实上，您可以通过四种不同的方式引用同一个单元格，例如 A1、$A$1、$A1 和 A$1。

Excel 单元格引用中的美元符号只影响一件事——它指示 Excel 在将公式移动或复制到其他单元格时如何处理引用。简而言之，在行和列坐标前使用 $ 符号会创建一个不会更改的绝对单元格引用。不使用 $ 符号，引用就是相对的，它会更改。

如果您正在为单个单元格编写公式，您可以使用任何引用类型，无论如何公式都是正确的。但是，如果您打算将公式复制到其他单元格，则选择合适的单元格引用类型至关重要。如果您感觉幸运，可以抛硬币试试 :) 如果您想认真对待，那么请花几分钟时间学习 Excel 中绝对和相对单元格引用的来龙去脉以及何时使用哪一个。

- 什么是 Excel 单元格引用

• 相对单元格引用
• 绝对单元格引用
• 在一个公式中使用相对和绝对单元格引用
• Excel 中的混合单元格引用
• 整列和整行引用
• 在不同引用类型之间切换 (F4 键)

什么是 Excel 单元格引用？

简单来说，Excel 中的单元格引用就是单元格地址。它告诉 Microsoft Excel 在哪里查找要在公式中使用的值。

例如，如果您在单元格 C1 中输入简单的公式 =A1，Excel 将从单元格 A1 中提取值到 C1：

如前所述，只要您为单个单元格编写公式，就可以自由使用任何引用类型，无论是否使用美元符号 ($) ，结果都相同：

但是，如果您想在工作表中移动或复制公式，选择正确的引用类型对于公式正确复制到其他单元格至关重要。以下部分将对每种单元格引用类型进行详细解释并提供公式示例。

注意。除了A1 引用样式（其中列由字母定义，行由数字定义）之外，还存在R1C1 引用样式，其中行和列都由数字标识（R1C1 表示第 1 行，第 1 列）。

由于 A1 是 Excel 中的默认引用样式，并且大多数时候都使用它，因此本教程只讨论 A1 类型引用。如果当前有人使用 R1C1 样式，您可以通过单击文件 > 选项 > 公式，然后取消选中R1C1 引用样式复选框来关闭它。

Excel 相对单元格引用（无 $ 符号）

Excel 中的相对引用是没有行和列坐标中 $ 符号的单元格地址，例如A1。

当包含相对单元格引用的公式复制到另一个单元格时，引用会根据行和列的相对位置而变化。默认情况下，Excel 中的所有引用都是相对的。以下示例显示了相对引用是如何工作的。

假设您在单元格 B1 中有以下公式：

=A1*10

如果您将此公式复制到同一列中的另一行，例如单元格 B2，则公式将调整为第 2 行 (A2*10)，因为 Excel 假设您希望将 A 列的每一行的值乘以 10。

如果您将包含相对单元格引用的公式复制到同一行中的另一列，Excel 将相应地更改列引用：

如果您将包含相对单元格引用的 Excel 公式复制或移动到另一行和另一列，则列和行引用都会更改：

如您所见，在 Excel 公式中使用相对单元格引用是在整个工作表中执行相同计算的一种非常方便的方法。为了更好地说明这一点，让我们讨论一个真实的例子。

在 Excel 中使用相对引用 - 公式示例

假设您的工作表中有一列美元价格（B 列），您想将其转换为欧元。已知美元 - 欧元兑换率（撰写本文时为 0.93），第 2 行的公式很简单，即=B2*0.93。请注意，我们正在使用 Excel 相对单元格引用，没有美元符号。

按 Enter 键将计算公式，结果将立即显示在单元格中。

提示。默认情况下，Excel 中的所有单元格引用都是相对引用。因此，在编写公式时，您可以通过单击工作表上的相应单元格来添加相对引用，而不是手动键入单元格引用。

要向下复制公式，请将鼠标悬停在填充柄（所选单元格右下角的小方块）上。当您这样做时，光标将变为细黑色十字，您可以按住并将其拖动到要自动填充的单元格上。

就是这样！公式已复制到其他单元格，其相对引用已针对每个单元格正确调整。为了确保每个单元格中的值计算正确，请选择任何单元格并在公式栏中查看公式。在此示例中，我选择了单元格 C4，并看到公式中的单元格引用相对于第 4 行，这正是它应该的样子：

Excel 绝对单元格引用（带 $ 符号）

Excel 中的绝对引用是在行或列坐标中带有美元符号 ($) 的单元格地址，例如$A$1。

美元符号将引用固定到给定的单元格，以便无论公式移动到哪里，它都保持不变。换句话说，在单元格引用中使用 $ 允许您复制 Excel 公式而无需更改引用。

例如，如果单元格 A1 中有 10，并且您使用绝对单元格引用($A$1)，则公式=$A$1 5将始终返回 15，无论将该公式复制到哪些其他单元格。另一方面，如果您使用相对单元格引用(A1)编写相同的公式，然后将其向下复制到列中的其他单元格，则将为每一行计算不同的值。下图演示了差异：

注意。尽管我们一直在说 Excel 中的绝对引用永远不会改变，但实际上，当您在工作表中添加或删除行和/或列时，它确实会改变，这会改变引用的单元格的位置。在上例中，如果我们在工作表顶部插入新行，Excel 足够聪明，可以调整公式以反映该更改：

在实际工作表中，在 Excel 公式中仅使用绝对引用的情况非常罕见。但是，有很多任务需要同时使用绝对引用和相对引用，如下面的示例所示。

注意。绝对单元格引用不应与绝对值混淆，绝对值是不考虑其符号的数字的大小。

在一个公式中使用相对和绝对单元格引用

您可能经常需要一个公式，其中一些单元格引用会根据公式复制到的列和行进行调整，而其他单元格引用则保持固定在特定单元格上。换句话说，您必须在一个公式中使用相对和绝对单元格引用。

示例 1. 用于计算数字的相对和绝对单元格引用

在我们之前的美元和欧元价格示例中，您可能不想在公式中硬编码汇率。相反，您可以将该数字输入到某个单元格中，例如 C1，并使用美元符号 ($) 在公式中固定该单元格引用，如下面的屏幕截图所示：

在此公式 (B4*$C$1) 中，有两种单元格引用类型：

• B4 - 相对单元格引用，会针对每一行进行调整，以及
• $C$1 - 绝对单元格引用，无论公式复制到哪里都不会更改。

这种方法的优点是您的用户可以根据可变汇率计算欧元价格，而无需更改公式。一旦转换率发生变化，您只需更新单元格 C1 中的值即可。

示例 2. 用于计算日期的相对和绝对单元格引用

在一个公式中使用绝对和相对单元格引用的另一个常见用途是根据今天的日期计算 Excel 中的日期。

假设您在 B 列中有一列交货日期，并且您使用 TODAY() 函数在 C1 中输入当前日期。您想知道每件商品的运输时间，您可以使用以下公式计算：=B4-$C$1

同样，我们在公式中使用了两种引用类型：

• 相对引用用于包含第一个交货日期的单元格 (B4)，因为您希望此单元格引用根据公式所在的行的不同而变化。
• 绝对引用用于包含今天日期的单元格 ($C$1)，因为您希望此单元格引用保持不变。

总而言之，每当您想要创建一个始终引用同一单元格的 Excel 静态单元格引用时，请确保在公式中包含美元符号 ($) 以在 Excel 中创建绝对引用。

Excel 混合单元格引用

Excel 中的混合单元格引用是指列字母或行号固定的一种引用。例如，$A1 和 A$1 是混合引用。但是每个是什么意思呢？这很简单。

您还记得，Excel 绝对引用包含 2 个美元符号 ($) ，它们会锁定列和行。在混合单元格引用中，只有一个坐标是固定的（绝对的），另一个（相对的）将根据行或列的相对位置而变化：

• 绝对列和相对行，例如 $A1。当包含此引用类型的公式复制到其他单元格时，列字母前的 $ 符号会将引用锁定到指定的列，使其永远不会更改。相对行引用（无美元符号）会根据公式复制到的行而变化。
• 相对列和绝对行，例如 A$1。在此引用类型中，行引用不会更改，列引用会更改。

下面您将找到使用这两种混合单元格引用类型的示例，希望这能使事情更容易理解。

在 Excel 中使用混合引用 - 公式示例

在此示例中，我们将再次使用我们的货币转换表。但是这次，我们不会仅限于美元 - 欧元转换。我们将要做的是将美元价格转换为多种其他货币，所有这些都使用单个公式！

首先，让我们在某一行中输入转换率，例如第 2 行，如下面的屏幕截图所示。然后，您可以为左上角单元格（此示例中的 C5）编写一个公式来计算欧元价格：

=$B5*C$2

其中 $B5 是同一行中的美元价格，C$2 是美元 - 欧元转换率。

现在，将公式向下复制到 C 列中的其他单元格，然后通过拖动填充柄用相同的公式自动填充其他列。结果，您将拥有 3 个不同的价格列，这些列根据同一列中第 2 行中的相应汇率正确计算。要验证这一点，请选择表中的任何单元格并在公式栏中查看公式。

例如，让我们选择单元格 D7（在英镑列中）。我们在这里看到的是公式=$B7*D$2，它采用 B7 中的美元价格并将其乘以 D2 中的值，即美元 - 英镑转换率，这正是我们想要的 :)

现在，让我们了解 Excel 如何准确知道要采用哪个价格以及要将其乘以哪个汇率。您可能已经猜到，这是混合单元格引用起作用的地方 ($B5*C$2)。

• $B5 - 绝对列和相对行。在这里，您只在列字母前添加美元符号 ($) 以将引用固定到 A 列，因此 Excel 始终使用所有转换的原始美元价格。行引用（无 $ 符号）未锁定，因为您希望分别计算每一行的价格。
• C$2 - 相对列和绝对行。因为所有汇率都位于第 2 行，所以您通过在行号前添加美元符号 ($) 来锁定行引用。现在，无论您将公式复制到哪一行，Excel 都会始终在第 2 行中查找汇率。并且由于列引用是相对的（无 $ 符号），它将针对公式复制到的列进行调整。

如何在 Excel 中引用整列或整行

当您使用具有可变行数的 Excel 工作表时，您可能希望引用特定列中的所有单元格。要引用整列，只需键入两次列字母并在两者之间添加冒号，例如A:A。

整列引用

与单元格引用一样，整列引用可以是绝对的和相对的，例如：

• 绝对列引用，例如 $A:$A
• 相对列引用，例如 A:A

同样，您在绝对列引用中使用美元符号 ($) 将其锁定到特定列，以便在将公式复制到其他单元格时，整列引用不会更改。

当将公式复制或移动到其他列时，相对列引用将发生更改，当您将公式复制到同一列中的其他单元格时，它将保持不变。

整行引用

要引用整行，您可以使用相同的方法，只是您键入行号而不是列字母：

• 绝对行引用，例如 $1:$1
• 相对行引用，例如 1:1

理论上，您还可以创建混合整列引用或混合整行引用，例如 $A:A 或 $1:1。我说“理论上”，因为我想不出这种引用的任何实际应用，尽管示例 4 证明了具有这种引用的公式的工作方式完全符合预期。

示例 1. Excel 整列引用（绝对和相对）

假设您在 B 列中有一些数字，并且您想找出它们的总和和平均值。问题是每周都会向表中添加新行，因此为固定范围的单元格编写通常的 SUM() 或 AVERAGE() 公式并不是可行的方法。相反，您可以引用整个 B 列：

=SUM($B:$B) - 使用美元符号 ($) 创建绝对整列引用，将公式锁定到 B 列。

=SUM(B:B) - 编写没有 $ 符号的公式以创建相对整列引用，当您将公式复制到其他列时，它将发生更改。

提示。在编写公式时，单击列字母以将整列引用添加到公式中。与单元格引用一样，Excel 默认情况下会插入相对引用（无 $ 符号）：

同样，我们编写一个公式来计算整个 B 列的平均价格：

=AVERAGE(B:B)

在此示例中，我们使用的是相对整列引用，因此当我们将它复制到其他列时，我们的公式会得到正确的调整：

注意。在 Excel 公式中使用整列引用时，切勿在同一列中的任何位置输入公式。例如，似乎在一个空单元格中输入公式 =SUM(B:B) 在 B 列最底部的单元格中输入公式 =SUM(B:B) 以在同一列的末尾显示总和可能是一个好主意。不要这样做！这将创建一个所谓的循环引用，公式将返回 0。

示例 2. Excel 整行引用（绝对和相对）

如果 Excel 表格中的数据按行而不是按列组织，则可以在公式中引用整行。例如，这就是我们如何计算第 2 行的平均价格：

=AVERAGE($2:$2) - 绝对整行引用通过使用美元符号 ($) 锁定到特定行。

=AVERAGE(2:2) - 当将公式复制到其他行时，相对整行引用将发生更改。

在此示例中，我们需要相对整行引用，因为我们有 3 行数据，我们希望通过复制相同的公式来计算每一行的平均值：

示例 3. 如何引用整列（不包括前几行）

这是一个非常热门的问题，因为工作表中的前几行通常包含一些介绍性条款或解释性信息，您不希望将它们包含在计算中。遗憾的是，Excel 不允许像 B5:B 这样的引用，它将包含从第 5 行开始的 B 列中的所有行。如果您尝试添加此类引用，您的公式很可能会返回 #NAME 错误。

相反，您可以指定最大行，以便您的引用包含给定列中的所有可能行。在 Excel 2016、2013、2010 和 2007 中，最大值为 1,048,576 行和 16,384 列。较早版本的 Excel 的行最大值为 65,536，列最大值为 256。

因此，要查找下表（B 列到 D 列）中每个价格列的平均值，请在单元格 F2 中输入以下公式，然后将其复制到单元格 G2 和 H2：

=AVERAGE(B5:B1048576)

如果您使用 SUM 函数，您也可以减去要排除的行：

=SUM(B:B)-SUM(B1:B4)

示例 4. 在 Excel 中使用混合整列引用

正如我在前面几段中提到的那样，您还可以在 Excel 中创建混合整列或整行引用：

• 混合列引用，例如 $A:A
• 混合行引用，例如 $1:1

现在，让我们看看当您将包含此类引用的公式复制到其他单元格时会发生什么。假设您在某个单元格中输入公式=SUM($B:B)，在此示例中为 F2。当您将公式复制到相邻的右侧单元格 (G2) 时，它会更改为=SUM($B:C)，因为第一个 B 用 $ 符号固定，而第二个没有。结果，公式将对 B 列和 C 列中的所有数字求和。不确定这是否有任何实际价值，但您可能想知道它是如何工作的：

警告！不要在一个工作表中使用过多的整列/整行引用，因为它们可能会降低 Excel 的速度。

如何切换绝对、相对和混合引用 (F4 键)

在编写 Excel 公式时，当然可以手动键入 $ 符号以将相对单元格引用更改为绝对或混合引用。或者，您可以按 F4 键来加快速度。要使用 F4 快捷键，您必须处于公式编辑模式：

1. 选择包含公式的单元格。
2. 按 F2 键或双击单元格进入编辑模式。
3. 选择要更改的单元格引用。
4. 按 F4 在四种单元格引用类型之间切换。

如果您选择了没有 $ 符号的相对单元格引用，例如 A1，则重复按 F4 键会在包含两个美元符号的绝对引用（例如 $A$1）、绝对行 A$1、绝对列 $A1 之间切换，然后返回到相对引用 A1。

注意。如果您在没有选择任何单元格引用的情况下按 F4，则光标左侧的引用将自动被选中并更改为另一种引用类型。

我希望您现在完全理解相对和绝对单元格引用是什么，并且带有 $ 符号的 Excel 公式不再是一个谜。在接下来的几篇文章中，我们将继续学习 Excel 单元格引用的各个方面，例如引用另一个工作表、3D 引用、结构化引用、循环引用等等。在此期间，感谢您的阅读，希望下周能在我们的博客上见到您！

以上是为什么在Excel公式中使用$：相对和绝对单元格参考的详细内容。更多信息请关注PHP中文网其他相关文章！