excel你一定要知道的神奇有趣的相对引用
发布时间:2023-04-20 14:51:31
下图所示的工作表中,我们要求水果的销售额,很显然,使用列B中的单价乘以列C中的销量即得到销售额。因此,要得到苹果的销售额,就在单元格D2中输入公式:
=B2*C2
同理,要得到其他水果的销售额,就在列D中相应的单元格输入列B乘以列C单元格的公式。如果数据很多,这样一个一个输入将费时费力。实际上,在单元格D2中输入公式后,我们可以拖动复制该单元格公式至下方所有要计算的单元格,快速计算出结果,如下图所示。
Excel会自动调整,让公式使用正确的单元格,从而得到我们想要的结果。
上面的操作,是我们在Excel中经常会进行的操作,通过在一个单元格中输入公式,然后拖动公式到指定的单元格,自动获得想要的结果,非常方便快捷。
为什么能够这样呢?
其实,这些看似轻松平常的操作背后,蕴藏着Excel的相对引用原理。
回到上面的例子,再看看单元格D2中的公式,表面上看,表示单元格B2的值乘以单元格C3的值,但实际上应该是“本单元格左侧第2个单元格的值乘以左侧第1个单元格的值”。公式向下复制后,虽然单元格变了,但表示的意思仍然是本单元格左侧第2个单元格的值与左侧第1个单元格的值相乘。
也就是说,我们看到的只是表象。在使用相对引用时,Excel是以相对于输入单元格的位置来处理单元格地址的。这就是Excel的相对引用原理。
理解相对引用原理非常重要,它是理解Excel公式运算以及在名称、数据有效性和条件格式等应用的基础。下面再举一个例子。
如下图所示的工作表,在单元格C5中输入公式:
=A1+200
表面上看,是求单元格A1中的值加上200后的结果,而实际上Excel认为是将基于当前单元格向上4行向左2列的单元格的值加上200。
拖动单元格C5复制公式至单元格C6,结果如下图所示,基于当前单元格C6,相关单元格相应地调整为单元格A2。
将单元格C5复制到单元格E7,此时公式变为:
=C3+200
即,公式中相关的单元格调整为基于当前单元格E7向上移4行向左移2列的单元格C3,如下图所示。
上述操作也表明,相对引用是Excel默认使用的引用方式。因此,要结合工作表实际要求来正确使用。如果不正确的使用相对引用,有时会得到错误的结果。
如下图所示的工作表,交税金额为工资总额乘以税率。我们先在单元格B1中输入公式:=A2*D2,然后向下拖至单元格B5,显然除B2中的值正确外,其它单元格中的值都是错误的。
这是因为按照Excel默认的相对引用方式,会自动调整相关单元格,特别是列D中的单元格,如下图所示。列D中的单元格除D2外,都为空,因此除单元格B2外,其它单元格的值为0。
然而,我们需要的是列A中的单元格随当前单元格的位置改变而自动调整,但列D中的单元格永远固定在单元格D2。此时,就需要运用单元格的绝对引用,如下图所示。
在单元格B2中输入公式:
=A2*$D$2
向下拖动复制公式至单元格B5,得到正确的结果。
在上面的公式中,$D$2是绝对引用,不会随着单元格自动调整。
当我们需要一直使用某单元格或者单元格区域时,就应该使用绝对引用。
当然,还可以使用混合引用,例如D$2表明行不变而列可以变化,$D2表明列不变而行可以变化。
例如下图所示的工作表,我们要计算3种不同税率下的交税金额。在单元格B5中输入公式:
=$A5*A$2
向下拖动至单元格B8,向右拖动至列D。绝对引用的行或者列不变,而使用相对应用的行或者列将自动调整获取正确的单元格数据。
示例1:在条件格式中的相对引用
如下图所示的工作表,要求对分数小于60的单元格背景色设置为红色。使用公式:
=$B2<60
列固定而行随着单元格调整。
注:条件格式中使用的公式中的单元格引用,是基于公式中所选单元格区域左上角的引对引用。
示例2:在数据有效性中的相对引用
与条件格式一样,在设置数据有效性的公式中包含的单元格引用,也是其于所选区域左上角单元格的相对引用。
如下图所示的工作表,我们在列A中设置了数据有效性,使得用户在列A中不能输入重复数据。
其中,使用了公式:
=COUNTIF(A:A,A1)=1
当我们选择列A中任一单元格时,公式会自动调整到当前单元格,如下图所示。
注意到,公式中使用的A:A也是相对引用,因此可以很容易地将列A中的不允许输入重复值的功能复制应用到其他列。
示例3:在名称中的相对引用
选择工作表单元格A1,在“新建名称”对话框中定义名称GetRightCellValue:
=Sheet7!B1
即获取当前单元格右侧单元格中的值。
定义名称后,在工作表中输入该名称时,会获取所在单元格右侧单元格中的值,如下图所示。
如果想要该名称在工作簿其他工作表中也可使用,那么可以将该名称修改如下:
=!B1
猜你喜欢
- 用惯了Office2007的朋友们都应该知道,在Office程序中左上方有个“Office按钮”,我们单击进入“Office按钮”后可以使用
- iOS 14.5 是一个重大更新,引入了一大堆新功能,包括戴着口罩时用苹果手表解锁 iPhone,还增加了海外双 SIM 卡 5G 支持、新
- 制作PPT时,我们常会给幻灯片添加背景音乐,使其在演讲播放时更能渲染会场的气氛。那么Word呢?在某些特定的文档中,是否也能插入音乐?哈哈,
- 很多同龄人安装完win10操作系统后,可能部分用户在使用win10操作系统时会出现问题,提示win10操作系统无法启动,需要对其进行修复。关
- 在WPS文字中,调整页码字号的操作方法:1、在页脚处双击鼠标,进入页脚编辑状态;2、选中方框中的页脚数字;3、单击开始选项卡,在字号处选择所
- 从Excel2002开始新添了“文本到语音”的功能,这项功能使Excel可以将单元格中的数字或文字内容读出来,利用这个功能可以帮助校对表格中
- 在本教程中我们来看看word2003里“绘图”工具的具体使用方式吧!在本教程中我们来看看word2003里“绘图”工具的具体使用方式吧!Wo
- 在做一些文档排版策划的时候,为了美化Word文档,通常会给文档添加背景,那么word背景边框怎么弄?接下来就教大家操作方法,一起get起来把
- 马上开学了,很多老师学生又开始准备学习用具了,年早些时候,微软宣布将向纽约市的公立学校学生免费赠送Office 365订阅,如果Micros
- Microsoft office 2019专业增强版怎么激活?我们都知道正常的office软件都是需要激活才能使用的,但是很多用户不知道如何
- word怎么绘制重点流程图?word中想要制作一个重点流程图,该怎么制作呢?下面我们就来看看word重点流程图的制作方法,需要的朋友可以参考
- 使用win10系统后,经常会去逛win10商店,看看有什么新奇有趣的新应用。但有的小伙伴发现网络没问题,却无法连接win10商店。对于这个问
- 现在有一张清单,其中只列出了要调整工资人员的名单和具体调资金额,要求必须按清单从工资表中查找相应的人员记录逐一修改工资。如果按一般方法逐一查
- 我们在日常生活或者工作中难免会需要使用到远程连接的功能,但是有不少小伙伴在远程连接的时候突然就出现提示发生身份函数错误的情况,那么遇到这种情
- 如何将建设银行txt打款明细导入到excel表格?建设银行在进行批量代发打款后,导出的打款明细是TXT格式的,其中,账号,姓名,交易状态,金
- 最近,小伙伴反应下载win10镜像文件,但不知道如何恢复系统。我们该怎么办其实操作非常简单,只,只需要在备份中安装镜像。今天,边肖带来了详细
- 首次接触KMS自动激活,服务器是来之前一位同事配置好的,大概去检查了一下,服务器操作系统是Server 2012 R企业版,加了域,防火墙加
- 前面我们了解了在Word2007中分栏的设置,相信很多朋友已经学会了,而如果想要对网上下载的分栏文档中进行取消,该怎么办呢?下面小编就来和大
- 我们在编辑Word文档的时候通常会用到分隔符,分隔符包括分页符、分栏符以及分节符等,通过在文字中插入分隔符,可以把将Word文档分成多个部分
- 若要在Word 2013中的文本周围创建边框,可以使用功能区中的“边框”命令按钮,也可以调用“边框和底纹”对话框。如何使用“边框”命令按钮W