Excel中单元格引用模式方法与技巧探讨

Excel中单元格引用模式方法与技巧探讨

陈艺

(四川文理学院教务处,四川达州 635000)

[摘要]：在从事计算机教学的过程中发现学生对学习Word、Excel等实用技术的热情很高，但不同于计算机专业的系统性、专业性的学习，对某些知识点的认识和理解不够，也就不能在实际中得到很好的应用。本文就Excel中三种单元格引用模式之间的关系以及应用作简要介绍，对这一知识点作详解与扩展。

[关键词]：计算机教学 Excel 单元格引用模式

0 引言

Excel是一款数据处理系统与报表制作的计算机软件，可以利用多种公式或函数对数据进行算术和逻辑运算，具有强大的表格数据计算与分析能力，被广泛的应用于财务、统计、金融、审计等各个领域。[1]

在Excel中，无论是简单数值计算还是函数计算，无论是图表分析还是高级的数据分析，单元格的引用功能都或多或少的起着重要作用。在复制或自动填充公式时，如果公式中有单元格的引用，则自动填充的公式会根据单元格引用的情况产生不同的变化。[2]Excel之所以有如此功能是由单元格的引用所致。

1 单元格引用的定义及作用

单元格是Excel中的术语，指用单元格在表中的坐标位置的标识。那单元格引用又是什么呢？简单来讲，就是使用单元格或单元格区域来参与运算的方式。单元格引用分为A1和R1C1两种引用样式。在A1引用样式中，由该单元格所在列标和行号表示其位置，如B2，表示B列第2行。在R1C1引用样式中，R表示行(row)、C表示列(column)，R6C3表示第6行第3列，即C6单元格。后面皆使用A1引用样式作分析介绍。[3]

单元格引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。[4]还可以引用同一个工作簿中不同工作表上的单元格和

[收稿日期]

[作者简介]陈艺（1980- ），女，四川平昌人，讲师，主要从事计算机基础教育与研究。

其他工作簿中的数据。例如Sheet1!A2表示Sheet1工作表的A2单元格；[BOOK3] Sheet1!B3表示BOOK3工作簿Sheet1工作表的B3单元格。引用相同工作簿中不同工作表中的单元格称为三维引用，引用不同工作簿中的单元格称为链接。

2 单元格引用的分类及其之间的关系

单元格引用位置是基于工作表中的行号列标，单元格地址根据它被复制到其它单元格后是否会改变，分为相对引用、绝对引用和混合引用。

2.1相对引用：

把一个含有单元格地址引用的公式复制到一个新的位置或用一个公式填入一个

选定范围时，公式中的单元格地址会根据情况而发生改变。在Excel中，默认引用为相对引用。相对引用反映了改单元格地址与引用单元格之间的位置关系，当将引用该地址的公式或函数复制到其他单元格时，这种相对位置关系也随之被复制。[5]

表示方法：相对引用地址直接用单元格的列标+行号表示，如A5、B4等。

2.2绝对引用：

把一个含有单元格地址引用的公式复制到一个新的位置或用一个公式填入一个

选范围时，公式中的单元格地址保持不变，即该地址不随复制或填充的目的单元格的变化而变化。[6]绝对地址在被复制到其它单元格时，其单元格地址不会改变。

表示方法：绝对地址在引用单元格前加符号“$”，如$A$6+$B$3等。

2.3混合引用：

在一个单元格地址中，既有相对地址又有绝对地址的混合引用，包括具有绝对列和相对行，或是绝对行和相对列。例如E $5或者$E5，我们把这类引用称为“混合引用”。如果“$”符号在行号前，表示该行位置是“绝对不变”的，而列位置会随着目的位置的变化而变化。[7]如果“$”符号在列号前，表示该列位置是“绝对不变”的，而行位置会随着目的位置的变化而变化。

2.4三种引用之间的关系

相对引用、绝对引用和混合引用反映的是向其他单元格或者单元格区域复制公式或者自动填充时，单元格地址是如何变化的关系。

在Excel中，正确使用功能键F4，可以对单元格的相对引用和绝对引用进行切换。

例如，对于某单元格所输入的公式为“=SUM(C5:C9)”。选中整个公式，按下F4键，该公式内容变为“=SUM($C$5:$C$9)”，表示对行、列所在单元格均进行绝对引用。第二次按下F4键，公式内容又变为“=SUM(C$5:C$9)”，表示对行进行绝对引用，对列进行相对引用。第三次按下F4键，公式则变为“=SUM($C5:$C9)”，表示对行进行相对引用，对列进行绝对引用。第四次按下F4键时，公式变回到初始状态“=SUM(C5:C9)”，即对行和列所在的单元格均进行相对引用。[8]

3 实例解析三种引用模式应用

Excel具有强大的数据库管理功能，可以管理和分析大量的数据信息。在使用Excel过程中，对数据的分析处理是必不可少的，因此对单元格引用的操作就必不可少，如管理某一公司的产品销售数据统计表，统计每一个地区的销售总额、销售排名、各类产品的销售业绩，超额完成和未完成任务量等等。另外还希望把销售业绩好的地区用红色突出显示，并对销售情况和市场需求作深入分析，为决策者对市场做出正确的判断和分析提供数据指标。

3.1 相对引用与混合引用应用于实例介绍

如图表1所示的工作表，求各种产品的销售额的方法：在G3单元格中输入公式“= E3*F3”或者选中G3单元格，在公式编辑栏中输入公式按回车键，公式输入计算完毕。再选中G3单元格，右击选择“复制”，再通过粘贴的方法把G3单元格的公式粘贴到G4单元格内并同时得出计算结果，通过编辑栏可以看到G4单元格中所粘贴的公式来自G3单元格，这里使用的就是相对引用模式；或者是使用Excel自带的公式填充柄功能在单元格中填充复制公式，选中G3单元格，当鼠标指针变成黑十字形时，按住鼠标左键不放，向下拖拽鼠标直至要填充单元格的末尾位置时即可。如图表2所示，列的引用未改变，行的引用改变了。

图表1 相对引用示意图

图表2 混合引用示意图

3.2 绝对引用调用函数应用于实例介绍

如图表2所示，我们用复制公式的方法自动计算出了各种产品销售额之后，首先对“销售排名”按降序排序，再调用Excel内置的RANK函数计算排列名次。Excel提供了自动计算序数RANK 函数。利用这个函数可以返回一个数值在一组数值中的排位，使用的语法为：RANK(Number，Ref，Order)，其中：Nnmber为需要找到排位的数字，Ref为包含一组数字的数组或引用，即是对参与计算的数字单元格区域的绝对引用，Order为一数字，是用来说明排序的方式 (如果Order为0或省略，Excel将Ref当做按降序排列的数据清单进行排位。如果Order不为0，Excel将Ref当做按升序排列的数据清单进行排位)。[9]在图表3所示的工作表中，选中H3单元格，输入公式：“=RANK(G3，$G$3： $G$14)”，引用后，发现图表3表中的名次是间隔的，RANK函数对重复数的排

位相同，但重复数的存在会影响后续数值的排位。如果既要反映并列，又要连续的排名次，就需要使用到IF函数了，执行真假值的判断。在H15单元格中输入公式：

“=IF(G15=G14，H14，H14+1)”，意思是如果这个产品的销售额与排列前一个的值一样，那么排序也一样，否则要比前一个的名次排后一位。然后通过公式填充柄完成其他名次的排列(见图表3)。

图表3 绝对引用示意图

4 结束语

本文详细讨论了单元格引用模式在Excel中的应用。可以说，单元格引用在Excel 公式计算、数据统计与管理中扮演着重要的角色。不懂得怎样引用单元格，就无法利用公式对数据进行操作。不懂得不同引用模式之间的区别，也就无法根据不同的情况使用不同是引用模式来正确、快速地处理公式和数据。因此，学好单元格的引用是学好Excel的前提和基础。

参考文献:

[1] 刘毅，周学文.计算机文化基础教程[M]，地质出版社，2007.8

[2] 刘玉萍，刘成安.大学计算机基础教程[M]，西安交通大学出版社，2009.7

[3] 张延龙，王建兰．提高Excel中数据输入效率的技巧[J]，山东气象，2004.2

[4] 葛兴初．Excel操作技巧七则[J]，中国科技信息，2005.7

[5] 肖华等编著．精通Oifce2007，北京：清华大学出版社，2007

[6] 冯叔民．如何调用EXCEL软件中的统计分析功能[J]，统计与信息论坛，2001(1)

[7] 刘羽平，黄大喜．巧用EXCEL函数自动进行成绩量化处理[J]，哈尔滨：电脑学习，2010(3)

[8] 朱建平等．Excel在统计工作中的应用[M]，北京：清华大学出版社，2007—01

[9] 东方人华编．Oifce2003中文版入门与提高[M]，北京：清华大学出版社，2004—07．