Excel绝对参考（製作及使用方法）-英雄云拓展知识分享

在 Excel 的公式中援用单元格时，默许援用类型是相对援用。 当公式针对相干列和行复制到其他单元格时，这些援用将产生变化。 如果要保持援用不变，而不管公式复制到哪里，就需要使用绝对援用。

• 什幺是绝对援用
• 如何做绝对援用
• 在示例中使用绝对援用
• 计算佔总数的百分比
• 查找一个值并返回对应的匹配值
• 2次点击批量使用tools使单元格援用绝对化
• 相对援用和混合援用
• 要记住的事情

免费下载示例文件

视频：绝对参考

什幺是绝对援用

绝对援用是 Excel 中的一种单元格援用。

与将公式复制到其他单元格时针对其相对位置产生变化的相对援用相比，不管公式被复制或移动到何处，绝对援用都将保持不变。

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

当您想要援用将被复制到多个单元格的公式中的固订单元格或区域，但又不想更改援用时，绝对援用很有用。

举例来看，区域A4:C7 包括产品的价格，你想针对单元格B2 中的税率获得每一个产品的应纳税额。

如果在公式中使用相对援用，如“=B5*B2”，当您向下拖动自动填充句柄以利用该公式时，会返回一些毛病的结果。 由于对单元格 B2 的援用将相对公式中的单元格产生变化。 现在，C6单元格中的公式为“=B6*B3”，C7单元格中的公式为“=B7*B4”

但是，如果您使用公式“=B2*$B$5”对单元格 B2 的绝对援用将确保在使用自动填充句柄向下拖动公式时所有单元格的税率保持不变，结果是正确的。

如何做绝对援用

在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

在示例中使用绝对援用

这部份提供了 2 个示例来讲明什么时候和如何在 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 的薪水。

2次点击批量使用tools使单元格援用绝对化

不管您选择手动键入还是使用 F4 快捷方式，您在 Excel 中一次只能更改一个公式。 如果要在 Excel 中使数百个公式中的单元格援用成为绝对值，则 转换参照 的工具 Excel的tools 只需单击 2 次便可帮助您处理工作。

选择要使单元格援用成为绝对的公式单元格，单击 库工具 > 更多（外汇） > 转换参照。 然后选择 绝对 选项并单击 Ok or 登记. 现在所选公式的所有单元格援用都已转换为绝对援用。

相对援用和混合援用

除绝对援用以外，还有另外两类援用类型：相对援用和混合援用。

相对参考 是 Excel 中的默许援用类型，在行和列援用之前没有美元符号 ($)。 当具有相对援用的公式被复制或移动到其他单元格时，援用将针对它们的相对位置自动更改。

举例来看，当您在“=A1+1”等单元格中键入公式，然后向下拖动自动填充手柄将此公式填充到下一个单元格时，公式将自动更改成“=A2+1”。

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

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

首先，您可使用单元格 C3 中的公式“=B2*C3”将单元格 B1 中的 3 乘以第一列中的数字 (1)。 但是，当您向右拖动自动填充手柄以填充其他单元格时，您会注意到除第一个之外的所有结果都不正确。

这是由于当你向右复制公式时，行位置不会改变，但列位置会从B3变成C3，D3等。结果，右侧单元格（D3，E3，等）更改成“=C3*D2”、“=D3*E2”等，而实际上你希望它们是“=B3*D2”、“=B3*E2”等。

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

=$B3*C2

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

然后，您需要将单元格 C1 中的数字 2 乘以下面各行中的数字。

向下复制公式时，C2单元格的列位置不变，但行位置由C2变成C3、C4等，结果下面单元格的公式变成“=$B4C3”， "=$B5C4" 等会产生不正确的结果。

要解决此问题，请将“C2”更改成“C$2”，以避免在向下拖动自动填充句柄以填充公式时行援用产生变化。

=$B3*C$2

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

要记住的事情

• 单元格援用摘要

  种别	例	概要
  绝对参考	1澳元	将公式复制到其他单元格时永久不会改变
  相对参考	A1	将公式复制到其他单元格时，行和列援用都会针对相对位置产生变化
  混合参考	$A1/A$1	当公式复制到其他单元格但列援用固定时，行援用产生变化/当公式复制到其他单元格但行援用固定时，列援用产生变化；

• 通常，移动公式时，绝对援用永久不会改变。 但是，当在工作表的顶部或左边添加或删除行或列时，绝对援用将自动调剂。 举例来看，在公式“=$A$1+1”中，当您在工作表顶部插入一行时，公式将自动更改成“=$A$2+1”。

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