Excel 绝对引用（如何创建和使用）

在 Excel 中的公式中引用单元格时，默认的引用类型是相对引用。当公式被复制到其他单元格时，这些引用会根据其相对的列和行位置发生变化。如果您希望无论公式复制到哪里都保持某个引用不变，则需要使用绝对引用。

• 什么是绝对引用
• 如何创建绝对引用
• 使用绝对引用的示例
• 计算占总值的百分比
• 查找一个值并返回相应的匹配值
• 使用 Kutools 两步批量将单元格引用设为绝对引用
• 相对引用与混合引用
• 注意事项

免费下载示例文件

视频：绝对引用

什么是绝对引用

绝对引用是 Excel 中的一种单元格引用类型。

与相对引用不同，相对引用在公式复制到其他单元格时会基于其相对位置发生改变，而绝对引用则无论公式复制或移动到何处都会保持不变。

通过在公式中的列和行引用前添加美元符号 ($) 来创建绝对引用。例如，要为单元格 A1 创建绝对引用，应将其表示为 $A$1。

当您希望在公式中引用一个固定的单元格或区域，并且该公式会被复制到多个单元格时，但又不希望引用发生变化，绝对引用非常有用。

例如，区域 A4:C7 包含产品的价格，您希望根据单元格 B2 中的税率计算每个产品的应缴税额。

如果在公式中使用相对引用，如“=B5*B2”，当您向下拖动自动填充柄应用此公式时，会返回错误结果。因为对单元格 B2 的引用会根据公式中单元格的相对位置发生变化。现在，C6 单元格中的公式变为“=B6*B3”，C7 单元格中的公式变为“=B7*B4”。

但如果使用绝对引用单元格 B2 的公式“=B5*$B$2”，可以确保在使用自动填充柄向下拖动公式时，所有单元格的税率保持不变，结果正确。

使用相对引用		使用绝对引用

如何创建绝对引用

要在 Excel 中创建绝对引用，请在公式中的列和行引用前添加美元符号 ($)。有两种方法可以创建绝对引用：

手动向单元格引用添加美元符号

在单元格中输入公式时，您可以手动在想要设置为绝对引用的列和行引用前添加美元符号 ($)。

例如，如果您想将单元格 A1 和 B1 中的数字相加并将它们都设为绝对引用，只需将公式输入为“=$A$1+$B$1”。这将确保在公式被复制或移动到其他单元格时，单元格引用保持不变。

或者，如果您想更改现有公式中的引用为绝对引用，可以选择该单元格，然后进入编辑栏添加美元符号 ($)。

使用快捷键 F4 将相对引用转换为绝对引用

1. 双击带有公式的单元格以进入编辑模式；
2. 将光标放在您想设置为绝对引用的单元格引用上；
3. 按键盘上的“F4”键切换引用类型，直到在列和行引用前都加上美元符号；
4. 按“Enter”键退出编辑模式并应用更改。

F4 键可以在相对引用、绝对引用和混合引用之间切换。

A1 → $A$1 → A$1 → $A1 → A1

如果您想在公式中将所有引用设为绝对引用，请在编辑栏中选择整个公式，按下“F4”键切换引用类型，直到在列和行引用前都加上美元符号。

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

使用绝对引用的示例

本部分提供两个示例，展示何时以及如何在 Excel 公式中使用绝对引用。

示例 1 计算占总值的百分比

假设您有一个数据区域 (A3:B7)，其中包含每种水果的销售额，而单元格 B8 包含这些水果的销售总额，现在您想计算每种水果销售额占总额的百分比。

计算占总值百分比的通用公式：

Percentage = Sale/Amount

在公式中使用相对引用来获取第一种水果的百分比，如下所示：

=B4/B8

当向下拖动自动填充柄计算其他水果的百分比时，将返回 #DIV/0! 错误。

因为当您拖动自动填充柄将公式复制到下方单元格时，相对引用 B8 会根据其相对位置自动调整为其他单元格引用（B9、B10、B11）。而单元格 B9、B10 和 B11 是空的（零），当除数为零时，公式返回错误。

要修复这些错误，在这种情况下，您需要在公式中将单元格引用 B8 设为绝对引用 ($B$8)，以防止在将公式移动或复制到任何地方时发生变化。现在公式更新为：

=B4/$B$8

然后向下拖动自动填充柄计算其他水果的百分比。

示例 2 查找一个值并返回相应的匹配值

假设您想在 D4:D5 的名称列表中查找并根据范围 (A4:B8) 提供的员工姓名和相应年薪返回他们的对应工资。

查找的通用公式为：

=VLOOKUP(lookup_value, table_range, column_index, logical)

如果在公式中使用相对引用来查找值并返回相应的匹配值，如下所示：

=VLOOKUP(D4,A4:B8,2,FALSE)

然后向下拖动自动填充柄查找下方的值，将返回错误。

当您向下拖动填充柄将公式复制到下方单元格时，公式中的引用会自动向下调整一行。因此，对表格范围 A4:B8 的引用变为 A5:B9。由于 Lisa 在范围 A5:B9 中找不到，公式返回错误。

为避免错误，请在公式中使用绝对引用 $A$4:$B$8 而不是相对引用 A4:B8：

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

然后向下拖动自动填充柄获取 Lisa 的工资。

使用 Kutools 两步批量将单元格引用设为绝对引用

无论您选择手动输入还是使用 F4 快捷键，每次只能更改一个公式。如果您想在数百个公式中将单元格引用设为绝对引用，“Kutools for Excel”的“转换单元格引用”工具可以帮助您通过两次点击完成任务。

要在多个公式中将单元格引用设为绝对引用，请选择公式单元格并点击“Kutools” > “更多” > “转换单元格引用”。然后选择“至绝对”选项并点击“确定”或“应用”。现在所选公式的所有单元格引用都已转换为绝对引用。

相对引用与混合引用

除了绝对引用外，还有另外两种引用类型：相对引用和混合引用。

相对引用 是 Excel 中的默认引用类型，其行和列引用前没有美元符号 ($)。当包含相对引用的公式被复制或移动到其他单元格时，引用会根据其相对位置自动变化。

例如，当您在一个单元格中输入公式“=A1+1”，然后向下拖动自动填充柄将此公式填充到下一个单元格时，公式会自动更改为“=A2+1”。

混合引用由绝对引用和相对引用组成。换句话说，混合引用使用美元符号 ($) 在公式被复制或填充时固定行或列。

以乘法表为例，行和列列出从 1 到 9 的数字，您将把这些数字互相相乘。

首先，您可以在单元格 C3 中使用公式“=B3*C2”将单元格 B3 中的 1 与第一列中的数字 (1) 相乘。然而，当您向右拖动自动填充柄填充其他单元格时，您会发现除第一个结果外，所有结果都是错误的。

这是因为当您将公式复制到右侧时，行位置不会改变，但列位置从 B3 变为 C3、D3 等。结果，右侧单元格（D3、E3 等）中的公式变为“=C3*D2”、“=D3*E2”等，而实际上您希望它们是“=B3*D2”、“=B3*E2”等。

在这种情况下，您需要添加美元符号 ($) 锁定“B3”的列引用。使用以下公式：

=$B3*C2

现在，当您向右拖动公式时，结果是正确的。

然后您需要将单元格 C2 中的数字 1 与下方行中的数字相乘。

当您向下复制公式时，单元格 C2 的列位置不会改变，但行位置从 C2 变为 C3、C4 等。结果，下方单元格中的公式变为“=$B4C3”、“=$B5C4”等，这会产生错误的结果。

要解决这个问题，将“C2”更改为“C$2”，以便在向下拖动自动填充柄填充公式时保持行引用不变。

=$B3*C$2

现在您可以向右或向下拖动自动填充柄获取所有结果。

注意事项

• 单元格引用总结

  类型	示例	总结
  绝对引用	$A$1	当公式复制到其他单元格时永远不会改变
  相对引用	A1	当公式复制到其他单元格时，行和列引用都会根据相对位置变化
  混合引用	$A1/A$1	当公式复制到其他单元格时，行引用变化但列引用固定/列引用变化但行引用固定；

• 通常，当公式移动时，绝对引用不会改变。但是，当在工作表的顶部或左侧添加或删除行或列时，绝对引用会自动调整。例如，在公式“=$A$1+1”中，当您在工作表顶部插入一行时，公式会自动更改为“=$A$2+1”。

  

• “F4”键可以在相对引用、绝对引用和混合引用之间切换。