Excel 中的公式引用

公式是 Microsoft Excel 工作表中重要且最常用的概念。在使用公式时，最重要的概念是单元格引用。尽管看起来很简单，但公式引用常常让许多 Excel 用户感到困惑。本教程将涵盖公式单元格和范围引用的定义、Excel 中单元格引用的类型、实现单元格引用的步骤等等！

什么是公式引用？

您创建的 Excel 公式大多包含对单元格或范围的引用。这些引用非常重要，因为它们允许您的公式根据这些单元格或范围中包含的数据动态操作。让我们来理解一下单元格引用和范围引用的区别。

单元格引用（通常也称为单元格地址）定义为列（以字母表示）和行（以数字表示）的交集，它指定了您 Excel 工作表中的一个特定单元格。

示例：单元格引用 B2 指的是 B 列和第 2 行交汇处的单元格；B3 指的是 B 列和第 3 行交汇处的单元格，以此类推。

其中，

范围引用定义为左上角单元格地址和右下角单元格地址，中间用冒号分隔。

示例：单元格范围 B3:B6 包含从 B1 到 B6 的 4 个单元格。

如何在 Excel 中创建引用

要在您的 Excel 工作表中创建单元格引用，请遵循以下步骤：

1. 将光标放在您想输入公式的单元格中。
2. 用等号 (=) 开始输入公式。
3. 执行以下操作之一：
   • 直接在单元格或公式栏中输入引用，或者
   • 选择您想要引用的单元格。
4. 输入公式的其余部分，然后按 Enter 键执行它。
5. 例如，在下面的例子中，您可以看到我们在单元格 A1 和 A2 中有两个值。现在，要将这两个值相加，我们将输入 = B2 的单元格引用 (B2)，输入加号，B3 的单元格引用 (B3)，然后按 Enter 键。

您将看到以下输出。

要在您的 Excel 工作表中创建范围引用，请遵循以下步骤：

1. 将光标放在您想输入公式的单元格中。
2. 用等号 (=) 开始输入公式。
3. 执行以下操作之一：
   • 输入您希望引用开始的第一个单元格引用
   • 输入一个冒号 (:) 来分隔第一个和最后一个单元格
   • 指定您希望范围引用结束的最后一个单元格。
4. 输入公式的其余部分，然后按 Enter 键执行它。
   例如，如果您想将单元格 B3、B4、B5、B6 中的值相加。一种方法是写 = SUM(B3 + B4 + B5 + B6)。但这是一种冗长的方法。使用范围概念可以更快地完成同样的公式。输入等号 (=)，后跟 Excel 的 SUM 函数，引用单元格 B3:B6，然后按 Enter 键。
   您将获得以下输出
   
5. 在处理 Excel 的行和列时，有时您可能需要引用整行或整列。在这种情况下，直接点击您的行号顶部或列字母。
6. 例如，如果您希望计算第 2 行中所有单元格的总和，输入等号 (=)，后跟 Excel 的 SUM 函数，然后点击第 2 行的顶部以在您的公式中包含第 2 行的引用。它将显示为 B:B。

公式引用的类型

在公式中使用单元格（或范围）引用时，您可以使用三种类型的引用——相对引用、绝对引用和混合引用。当您为单个 Excel 单元格编写公式时，您可以选择任何引用类型。但有时，我们不重新输入公式，而是将其复制并粘贴到其他单元格。我们必须应用适当的地址类型来适应这种情况，因为相对和绝对单元格引用在复制到其他单元格时的行为是不同的。

相对单元格引用

相对引用定义为在行和列坐标中没有 $ 符号的单元格或范围引用，例如 B1 或 B2:B12。默认情况下，您 Excel 工作表中存在的所有单元格引用都是相对引用。

当您将带有相对单元格引用的数据公式复制到不同的单元格时，行和列的引用也会改变，因为引用是相对于当前行和列的偏移量。默认情况下，Excel 在公式中创建相对单元格引用。

当重新定位或跨不同 Excel 单元格复制时，相对引用会根据当前行和列的相对位置而改变。有时，我们想在不同的行或列中重复相同的公式，我们只需将公式拖动到不同的单元格。在这些情况下，您需要使用相对单元格引用。

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

=B3*10

接下来，当我们将单元格 C3 中的公式复制到同一列的另一行时。在下图中，我们已将其复制到单元格 C4，因为我们在单元格 C3 中使用了相对引用，在复制公式时，它将自动调整为第 4 行（B4*10）。

如果您将相同的公式进一步向下复制到同一列中，它将相应地继续更改列引用并给出输出。

如果您将包含相对单元格引用的公式复制到另一行和另一列，您会注意到 Excel 将同时更改行和列坐标。

相对单元格引用是使用 Excel 公式时最常用的单元格引用。这是因为它非常方便，有助于轻松地在整个工作表中执行相同的计算。为了更好地理解它，让我们讨论一个使用相对引用解决的现实生活中的例子。

在 Excel 中使用相对引用的示例

问题：在下面的列中，给出了不同产品的美元价格。您想将价格转换为印度卢比，以便进行进一步的计算和分析。已知当前的美元-印度卢比兑换率为 74.45。使用 Excel 相对单元格引用，将美元价格转换为印度卢比。

要使用 Excel 相对单元格引用将美元价格转换为欧元，请遵循以下步骤：

1. 在数据中，由于美元价格在 B 列中。在 C 列中添加一个辅助列，我们将在此输入将美元转换为印度卢比的公式。
   
2. 公式以=单元格引用 x 74.45 开始。由于数据在单元格 C3 中，所以我们的公式变成 = C3 x 74.45
   
3. 按 Enter 键计算公式，Excel 将立即在第一个辅助单元格中显示输出。
   
4. 我们知道默认情况下，所有单元格引用都是相对的。因此，我们将公式复制到其他单元格。
5. 选择您输入公式的单元格。将鼠标光标移到矩形框的末端。
6. 您会注意到光标将变成一个细小的黑色十字。将其向下拖动到列中以自动填充 Excel 单元格。您将得到以下输出。

就这样！您会注意到相对单元格引用公式被复制到其他单元格，其中单元格值会为每个单元格自动调整。要验证每个单元格中的公式是否计算准确，请选择任何一个单元格并查看公式栏中的公式。例如，这里我们选择了单元格 D7，并看到公式中的单元格引用是相对于第 7 行的，完全符合预期。

绝对单元格引用

“绝对引用定义为在将相同公式复制到其他单元格时保持不变的单元格或范围引用。当您想用特定 Excel 单元格中的值进行多次计算，或者当您需要将公式复制到不同单元格而不修改引用时，绝对地址非常有用。”

使用绝对引用的优点是，当您复制公式时，行和列坐标不会改变，因为引用是实际的单元格地址。要表示绝对单元格引用，请始终在列坐标和行坐标前使用两个美元符号。

例如，绝对引用 $B$6 意味着列和行坐标都被固定。

例如，如果您想将附加分数（在单元格 C1 中）与获得的总分（在 B 列中）相加。我们将以等号 (=) 开始公式，选择分数字段的单元格引用，并加上附加分数，其中我们将为单元格 C1 传递一个绝对引用。我们将得到以下公式：

=B4*$C$1

通过拖动细小的黑色十字将公式向下复制到 C 列。相对单元格引用会为所有单元格更改，而绝对引用在所有单元格中都是恒定的，并且将始终锁定在同一个单元格 ($C$1) 上。

为了更好地理解它，让我们讨论一个可以轻松使用绝对单元格引用概念解决的现实生活中的例子。

在 Excel 中使用相对引用的示例

问题：在 Excel 工作表中使用相对和绝对单元格引用的概念，通过一个公式计算实现销售目标的剩余天数。

在 Excel 中计算日期是绝对和相对单元格引用的常见用途之一。请按照以下步骤在 Excel 中用一个公式计算今天的日期：

1. 在上面的例子中，我们在 C 列有一个目标销售日期列表，当前日期输入在单元格 C1 中。在 D 列中添加一个辅助列，我们将在此输入公式以计算实现销售目标的剩余天数。

2. 您可以使用以下公式计算每个客户经理剩余的天数。

在上面的公式中，我们使用了相对单元格引用和绝对单元格引用的组合。每个公式引用都完成了给定的任务：

• 目标销售日期值的单元格的相对引用（C4），因为当您将公式向下复制到其他行值时，此单元格引用会改变。
• 包含今天日期的单元格的绝对引用（$C$1），因为此单元格引用在不同单元格中保持锁定。

3. 选择您输入公式的单元格。将鼠标光标移到矩形框的末端。您会注意到光标将变成一个细小的黑色十字。将其拖动到您想要自动填充的单元格上。

混合单元格引用

“混合单元格引用定义为 Excel 中的单元格或范围引用，其中列或行坐标中的一个被固定。”

注意：要使用混合引用，请按 F4 键。

Excel 用户常常混淆 Excel 绝对引用和 Excel 混合引用，并错误地互换使用它们。但两者是不同的。

Excel 绝对引用在行和列上都包含美元符号 ($)。因此，它冻结了列和行坐标，而在混合单元格引用的情况下，一次只有一个坐标是固定的（绝对），另一个（相对）会根据行或列的相对位置而改变。

换句话说，混合单元格引用是绝对列和相对行的组合。例如，$A1 和 A$1 是混合引用。但是这两个混合引用之间有什么区别，因为它们都包含单元格 A1？

1. 绝对列字母和相对行号：$A1
   列 A 是锁定的或绝对引用，行是相对的。如果此公式被复制到 Excel工作表中的其他单元格，列字母前的 $ 符号将固定到指定的列 A，使其永远不会改变。没有美元符号，相对行引用会根据公式被复制到的行而改变。
2. 相对列字母和绝对行号：A$1。
   这里列 A 是相对的，会根据单元格而改变，而行是绝对的。该公式中行的引用被锁定，不会改变。

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

示例 1：使用货币转换表，仅用一个公式将美元货币转换为不同货币！

由于美元是世界范围内的标准货币，我们以美元为单位给出了服务价格。在处理国际业务时，经常会被要求转换价格。让我们在某一行（比如说第 2 行）输入转换率，如下面的屏幕截图所示。然后按照以下步骤将美元货币转换为其他货币：

1. 在上面的例子中，我们必须将美元价格转换为不同的货币。我们将在 D 列（价格，印度卢比）、E 列（价格，澳元）和 F 列（价格，欧元）中分别添加三个辅助列。
   
2. 您可以使用以下公式将美元转换为其他货币。
   =$C5*C$2
   
3. 选择键入公式的单元格。将鼠标光标移到矩形框的末端。您会注意到光标会变成一个细小的黑色十字。将其拖动到要自动填充的单元格上。
   
4. 正如您在上面的屏幕截图中看到的，我们使用混合单元格引用类型计算出了 3 个不同价格列的输出。由于我们拖动了公式，让我们验证一下。从输出表中选择任何一个随机单元格，并交叉检查公式栏中的公式。
   例如，让我们选择单元格 E8（在“价格，澳元”辅助列中）。其公式栏中显示的公式是 =$C8*D$2，它从单元格 C8 中获取美元汇率，并将其乘以单元格 D2 中的澳元转换值。太棒了！一切都正确，并且方向与我们想要的一致。
   

上面的公式很简单，但你们中的大多数人可能会感到困惑，并想知道 Excel 究竟是如何知道要取哪个价格值以及要乘以哪个汇率的。是的，你猜对了。这完全是因为混合单元格引用。让我们理解一下上面使用的公式 (=$C5*C$2) 的实现：

• $C5 - 它代表绝对列字母和相对行号。美元符号仅加在列前以锁定它，因此 Excel 始终使用美元价格进行所有价格转换。在此公式中，行引用是相对的（没有使用美元符号），因为您想为每一行单独计算价格。如果它被复制到 Excel 工作表中的其他单元格，列字母前的 $ 符号将固定到指定的列 A，使其永远不会改变。没有美元符号，相对行引用会根据公式被复制到的行而改变。
• C$2 - 它代表相对列字母和绝对行号。在此公式中，所有汇率都在第 2 行中，因此我们通过在行号旁边添加美元符号来锁定行坐标。如果它被复制到 Excel 工作表中的其他单元格，无论如何，行号前的 $ 符号都将固定到指定的第 2 行，使其永远不会改变。没有美元符号，相对行引用会根据公式被复制到的列而改变。

在公式单元格引用（绝对、相对和混合）之间切换

Microsoft Excel 提供了多种方式来做同样的事情。在使用公式时，您可以手动输入 $ 并实现各种单元格引用，或将相对引用转换为绝对引用或绝对引用转换为混合引用。但最快最简单的方法是使用 F4 键。尽管它会为您加快速度，但 F4 快捷键有一个工作条件，即必须处于公式编辑模式。

1. 选择包含您公式的单元格。
2. 双击单元格以启用公式编辑模式。您也可以按 F2 快捷键来启用它。
3. 选择您希望更改的单元格引用。例如，我们选择了单元格引用 A3。
4. 接下来，按 F4 键 在四种单元格引用类型之间切换 $ 符号。
5. 我们选择了一个不含美元 ($) 符号的相对单元格引用 (A3)，连续按 F4 键将依次切换为绝对引用（行和列上各有一个美元符号，如 $A$3）、混合绝对行 (A$3)、混合绝对列 ($A3)，然后它将带您回到原始的相对引用 A3。