Note: The other languages of the website are Google-translated. Back to English

Excel绝对参考(制作及使用方法)

在 Excel 的公式中引用单元格时,默认引用类型是相对引用。 当公式根据相关列和行复制到其他单元格时,这些引用将发生变化。 如果要保持引用不变,而不管公式复制到哪里,就需要使用绝对引用。

免费下载示例文件 doc样本


视频:绝对参考

 


什么是绝对引用

 

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

与将公式复制到其他单元格时根据其相对位置发生变化的相对引用相比,无论公式被复制或移动到何处,绝对引用都将保持不变。

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

当您想要引用将被复制到多个单元格的公式中的固定单元格或范围,但又不想更改引用时,绝对引用很有用。

例如,区域A4:C7 包含产品的价格,你想根据单元格B2 中的税率获取每个产品的应纳税额。

如果在公式中使用相对引用,如“=B5*B2”,向下拖动自动填充句柄以应用此公式时会返回一些错误的结果。 由于对单元格 B2 的引用将相对于公式中的单元格发生变化。 现在,C6单元格中的公式为“=B6*B3”,C7单元格中的公式为“=B7*B4”

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

使用相对引用   使用绝对引用
文档绝对参考 3 1   文档绝对参考 4 1

如何做绝对引用

 

在Excel中做绝对引用,需要在公式中的列引用和行引用前加上美元符号($)。 创建绝对引用有两种方式:

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

在单元格中键入公式时,您可以在要使其成为绝对引用的列和行引用之前手动添加美元符号 ($)。

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

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

使用快捷键 F4 将相对引用转换为绝对引用
  1. 双击含有公式的单元格,进入编辑模式;
  2. 将光标放在要绝对化的单元格引用上;
  3. 新闻 F4 键盘上的键切换引用类型,直到在列和行引用之前添加美元符号;
  4. 新闻 输入 键退出编辑模式并应用更改。

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

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

绝对参考 f4 切换 1

如果要使公式中的所有引用都成为绝对引用,请在公式栏中选择整个公式,然后按 F4 切换引用类型的键,直到在列和行引用之前添加美元符号。

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

绝对参考 f4 切换 2


在示例中使用绝对引用

 

这部分提供了 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次点击批量使用Kutools使单元格引用绝对化

 

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

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

说明:

相对引用和混合引用

 

除了绝对引用之外,还有另外两种引用类型:相对引用和混合引用。

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

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

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

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

文档绝对参考 15 1

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

文档绝对参考 16 1

这是因为当你向右复制公式时,行位置不会改变,但列位置会从B3变为C3,D3等。结果,右边单元格(D3,E3,等)更改为“=C3*D2”、“=D3*E2”等,而实际上你希望它们是“=B3*D2”、“=B3*E2”等。

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

=$B3*C2

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

文档绝对参考 17 1

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

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

文档绝对参考 18 1

要解决此问题,请将“C2”更改为“C$2”,以防止在向下拖动自动填充句柄以填充公式时行引用发生变化。

=$B3*C$2

文档绝对参考 19 1

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

文档绝对参考 20 1


要记住的事情

 
  • 单元格引用摘要

    类型 例如: 总结
    绝对参考 1澳元 将公式复制到其他单元格时永远不会改变
    相对参考 A1 将公式复制到其他单元格时,行和列引用都会根据相对位置发生变化
    混合参考

    $A1/A$1

    当公式复制到其他单元格但列引用固定时,行引用发生变化/当公式复制到其他单元格但行引用固定时,列引用发生变化;
  • 通常,移动公式时,绝对引用永远不会改变。 但是,当在工作表的顶部或左侧添加或删除行或列时,绝对引用将自动调整。 例如,在公式“=$A$1+1”中,当您在工作表顶部插入一行时,公式将自动更改为“=$A$2+1”。

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


最佳办公效率工具

Kutools for Excel 解决您的大部分问题,并将您的工作效率提高 80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能. 支持 Office / Excel 2007-2021 和 365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能 30 天免费试用。 60 天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
按评论排序
注释 (0)
还没有评分。 成为第一位评论!
这里还没有评论

关注我们

版权所有 © 2009 - extendoffice.com。 | 版权所有。 供电 ExtendOffice。 | 网站地图
Microsoft和Office徽标是Microsoft Corporation在美国和/或其他国家的商标或注册商标。
受Sectigo SSL保护