还剩9页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
Excel中单元格的引用如果你问我,Excel中最重要的问题是什么?”我会毫不犹豫地告诉你“公式”然后你又追问我,“那么,公式中最重要的又是什么?”于是我再次毫不犹豫地告诉你“单元格的引用”客观地讲,公式的运用是Excel区别于Word和Access的重要特征,而公式又是由引用的单元格和运算符号或函数构成,因此,单元格的引用就成为Excel中最基本和最重要的问题不懂得怎样引用单元格,就无法利用公式对数据进行操作,不懂得不同引用样式间的区别,也就无法根据不同的情况使用不同的引用样式来正确、便捷地处理公式和数据怎么样,这么重要的内容你不想看看吗?
一、A1和R1C1引用样式引用的作用在于标识工作表上的单元格或单元格区域,并指明公式中所使用的数据的位置通过引用,可以在公式中使用工作表不同部分的数据,或者在多个公式中使用同一单元格的数值还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据Excel支持两种单元格的引用样式,即A1方式和R1C1样式
1.A1引用样式这是Excel默认的引用样式列以大写英文字母表示,从A开始到IV结束,共计256歹!J行以阿拉伯数字表示,从1开始到65536结束,共计65536行由于每个单元格都是行和列的交叉点,所以其位置完全可以由所在的行和列来决定,因此,通过该单元格所在的行号和列标就可以准确地定位一个单元格描述某单元格时,应当顺序输入列字母和行数据,列标在前行号在后例如,A1即指该单元格位于A列1行,是A列和1行交叉处的单元格如果要引用单元格区域,应当顺序输入区域左上角单元格的引用、冒号()和区域右下角单元格的引用以下是引用的示例引用卜达式位于列B和行5的单元格B5注意当正在修改单元格中的内容时,不能为单元格命名
4.使用现有的行列标志为单元格命名1选定需要命名的区域,把行列标志也包含进去2在“插入”菜单中,指向“名称”,再单击“指定”命令3在“名称在”选项框中,通过选定“首行”、“最左列”、“尾行”或“最右列”复选框来指定包含标志的位置注意使用这个过程指定的名称只引用包含数值的单元格,而不包含现有的行列志
5.修改名称1在“插入”菜单“名称”子菜单中选中“定义”命令,显示“定义名称”对话框2在“当前工作簿中名称”列表框中,选中欲更改的名称3在“当前工作簿中名称”文本框或“引用位置”文本框中,编辑、修改欲更改的名称或引用的单元格位置4单元“确定”按钮
6.删除名称1在“插入”菜单“名称”子菜单中选中“定义”命令,显示“定义名称”对话框2在“当前工作簿中名称”列表框中,选中欲删除的名称3单击“删除”按钮
7.引用名称1当名称在同一工作簿时选中“插入”菜单“名称”子菜单中的“粘贴”命令,在“粘贴名称”列表框中选中欲插入的引用名称,单击“确定”按钮,则该名称被插入到当前位置当然,也可以在插入点直接键入欲引用的单元格或单元格范围的名称,如“二销售总额-成本总额”2当名称不在同一工作簿时打开源工作簿和目的工作簿,并使其水平平铺或垂直平铺,然后在目的工作簿插入点直接键入,格式为欲引用单元格所在的工作簿名称!引用名称”,如“二衡水师专98年5月份工资表!工资总额*120%+100”在这里,“衡水师专98年5月份工资表”是引用的工作簿名称,“工资总额”是引用的单元格名称注意如果源工作簿保存在My Documents文件夹中,则可以不打开源工作簿,引用时也勿需使用路径如果源工作簿没有保存在My Documents文件夹中,则必须打开源工作簿,或者在引用时需添加该工作簿所在的路径
五、三维引用如果需要分析某一工作簿中多张工作表的相同位置处的单元格或单元格区域中的数据,最快捷的方法就是使用三维引用三维引用包含一系列工作表名称和单元格或单元格区域引用Microsoft Excel将使用存储在起始引用名称和结束引用名称之间的所有工作表使用三维引用来引用多个工作表上的同一单元格或区域工作簿必须包含多张工作表
1.三维引用1单击需要输入公式的单元格2键入“=”等号,再输入函数名称,接着再键入左圆括号3单击需要引用的第一个工作表标签4按住“Shift”键,单击需要引用的最后一个工作表标签5选定需要引用的单元格或单元格区域6完成公式
8.使用三维引用为多个工作表上的单元格命名1在“插入”菜单中,指向“名称”,再单击“定义”命令2在“在当前工作簿的名称”编辑框中,键入名称3如果“引用位置”编辑框中包含引用,请选定等号=和这个引用,再列E中行15到行30的单元格区E15:E30域行15中列B到列E的单元格区域B15:E15行5中的所有单元格5:5从行5到行10的所有单元格5:10列H中的所有单元格H:H从列H到列J中的所有单元格H:J
2.R1C1引用样式在R1C1引用样式中,Excel使用“R”加行数字和“C”加列数字来指示单元格的位置例如,R1C1即指该单元格位于第1行第1歹U在宏中计算行和列的位置时,或者需要显示单元格相对引用时,R1C1样式是很有用的如果要引用单元格区域,应当顺序输入区域左上角单元格的引用、冒号()和区域右下角单元格的引用以下是引用的示例引用表达式位于行5和列2的单元格R5C2列5中行15到行30的单元R15C5:R30C5格区域行15中列2到列5的单元格R15C2:R15C5区域行5中的所有单元格R5:R5从行5到行10的所有单元格R5:R10列8中的所有单元格C8:C8从列8到列11中的所有单元C8:C11格
二、绝对引用和相对引用
1.单元格的绝对引用不论包含公式的单元格处在什么位置,公式中所引用的单元格位置都是其工作表的确切位置在街道地址比喻中就像一个特定的住址,如“人民路32号”单元格的绝对引用通过在行号和列标前加一个美元符号“$”来表示,如$人$
1、$B$2,以此类推
2.单元格相对引用相对引用是像A1这样的单元格引用,该引用指引Excel从公式单元格出发如何找到引用的单元格在街道比喻中类似于指路,即告之从出发地如何走到目的地,如“往前走三个路口”
3.单元格的混合引用混合引用是指包含一个绝对引用坐标和一个相对引用坐标的单元格引用,或者绝对引用行相对引用列如B$5,或者绝对引用列相对引用行如$
854.R1C1引用样式同A1引用样式一样,R1C1引用样式也可以分为单元格的相对引用和单元格的绝对引用R1C1格式是绝对引用,如R3c5是指该单元格位于第3行第5歹(J格式是相对引用,其中“口”中的数值标明引用的单元格的相对位置,如果引用的是左面列或上面行中的单元格还应当在数值前添加“-”如引用下面一行右面两列的单元格时表示为引用上面一行左面两列的单元格时表示为“R[-1]C[-2]”,而引用上面一行右面两列的单元格时则表示为“R[T]C
[2]引用含义R[-2]C对在同一列、上面两行的单兀格的相对引用R
[2]C
[2]对在下面两行、右面两列的单兀格的相对引用R2C2对在工作表的第二行、第二列的单元格的绝对引用R[-l]对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用
5.绝对引用与相对引用的区别1复制粘贴公式时使用单元格的相对引用复制粘贴公式时,粘贴后公式的引用将被更新例如,单元格C6中包含公式“二B6+C5”,是指C6单元格中的数值为其左侧单元格和上方单元格数值的和,这是单元格的相对引用当复制C6单元格中的公式并将其粘贴到D8时,粘贴后公式中已不再是“二B6+C5”,而成为“X8+D7”,即单元格的引用被更新,并指向与当前公式位置相对应的单元格,数值仍为其左侧单元格和上方单元格数值的和使用单元格的绝对引用复制粘贴公式时,粘贴后公式的引用不发生改变例如,单元格C6中包含公式“二$B$6+$C$5”,这是单元格的绝对引用当复制C6单元格中的公式并将其粘贴到D8时,粘贴后的公式仍旧为“二$B$6+$C$5”当然,一个公式中也允许同时使用绝对引用和相对引用例如,单元格C6中包含公式“二$B$6+C5”,当复制C6单元格中的公式并将其粘贴到D8时,粘贴后的公式则为“二$B$6+D7”示例一如图1单元格的绝对引用和相对引用该表为一个工资发放表每个职工工资的实发金额均应为“基本工资+职务工资+奖金-水电费”,如果我们在F2单元格中键入相对引用单元格的公式“二B2+C2+D2-E2”,回车之后即可得出高平原的工资金额然后,再将该公式复制并粘贴到F
3、F
4、F5和F6单元格,则F3中的公式改变为“=B3+C3+D3-E3”,F4中的公式改变为“二B4+C4+D4-E4”,依次类推,而这个结果正是我们所需要的如果我们在F2中键入绝对引用单元格的公式“二$B$2+$C$2+$D$2-$E$2会怎么样呢?回车之后当然也可得出高平原的工资金额,然而当我们再将该公式复制并粘贴到F
3、F
4、F5和F6单元格时,F
3、F
4、F5和F6中的公式均为“二$B$2+$C$2+$D$2-$E$2”,而这个结果却不是我们所需要的如果单位决定给每个职工上浮工资30%,那么F2单元格中的公式则应为“二B2+C2+D2*$C$8-E2”然后,再将该公式复制并粘贴到F
3、F
4、F5和F6单元格,则F3中的公式改变为“=B3+C3+D3*$C$8-E3”,F4中的公式改变为“=B4+C4+D4*$C$8-E4”,依次类推,而这个结果正是我们所需要的在这里由于对单元格$C$8采用的是绝对引用样式,所以复制粘贴时它并不改变如果我们将F2中的公式写为“XB2+C2+D2*C8-E2”会怎么样呢?当将其复制粘贴到F
3、F4时-,F3中的公式将改变为“二B3+C3+D3*C9-E3”,F4中的公式将改变为“=B4+C4+D4*C10-E4”,而这个结果却不是我们所需要的由此可见,当我们不希望改变对某个单元格引用时,应当对其采用绝对引用样式示例二如图2单元格的混合引用在该图中我们要分别计算一分公司和二分公司的销售额计算方法有两种低效率的方法在单元格D3中键入公式二B3*C3”,然后将该公式复制并粘贴到单元格D4到D7再O在单元格F3中键入公式二B3*E3”,然后将该公式复制并粘贴到单元格F4到F7该方式采用的是单元格的相对引用高效率的方法在单元格D3中键入公式“=$B3*C3”,然后将该公式复制并粘贴到单元格D4到D7,再粘贴到单元格F3到F7该方式采用的是单元格的混合引用,即对B列采取绝对引用的样式,而对其它的列和行采取相对引用的样式结果如何?D4中的公式为J$B4*C
4、D5中的公式为J$B5*C5”,依次类推F3中的公式为“;$B3*E
3、F4中的公式为“二$B4*E4”,依次类推
(2)剪切粘贴公式时当剪切粘贴(即移动)公式时,公式中的单元格无论是绝对引用还是相对引用,移动后公式的内容均不改变
(3)自动填充公式时通过拖曳填充柄的方式,可以将公式自动填充到相邻的单元格中其自动填充效果同复制粘贴公式时的结果完全相同因此,如果在相邻单元格中填充公式时,最好采用自动填充的方式,即快捷又方便使用单元格的相对引用时,例如单元格C6中包含公式“二B6+C5”,那么当向下拖曳该单元格的自动填充钮时,在C7单元格中将显示公式“二B7+C6”,在C8单元格中将显示公式“=B8+C7”,依次类推使用单元格的绝对引用时,例如单元格C6中包含公式“二$B$6+$C$5,那么当向下拖曳该单元格的自动填充钮时,在C
7、C
8、C9……等单元格中都将显示公式“二$B$6+$C$5”当公式中同时使用绝对引用和相对引用时,例如单元格C6中包含公式J$B$6+C5”,那么当向下拖曳该单元格的自动填充钮时,在C7单元格中将显示公式“二$B$6+C6”,在C8单元格中将显示公式J$B$6+C7”,依次类推4录制宏时如果在录制宏时选中了某些单元格,则该宏在每次运行时都将选中原来的这些单元格而无论事先是否选中了其他单元格,因为宏在这时记录的是单元格绝对引用如果要让宏在选择单元格时不考虑活动单元格的位置,则需要将宏设置成为记录单元格相对引用单击“停止录制”工具栏上的“相对引用按钮,Microsoft Excel将转而按照相对引用格式继续记录宏,直到退出Microsoft Excel或再次单击“相对引用”按钮为止如果要使宏选中指定的单元格,执行某个操作,再选中另一个偏离该活动单元格一定位置的单元格,可以在记录该宏时混合使用相对和绝对引用如果要以相对引用格式记录宏,应当确认“相对引用”按钮已按下;而要以绝对引用格式记录宏,应当确认“相对引用”按钮未被按下
三、复杂引用
1.引用同一工作簿中的其它工作表引用同一工作簿中的其它工作表时格式如下被引用的工作表!被引用的单元格例如,我们欲引用Sheet8工作表中的F18单元格,表达式为“欲ee用!F18”在输入单元格引用地址时,除了可以使用键盘键入外,还可以使用鼠标直接进行操作操作步骤如下1打开目的工作表并选取目的单元格2键入“二”3单击Sheet8工作表标签4单击F18单元格5按回车键完成键入,此时编辑栏中将显示JSheet8!F18”一般来讲,使用鼠标选取引用方式时,Excel均默认为是单元格的相对引用
2.引用其它工作簿中的工作表引用同一工作簿中的其它工作表时格式如下[被引用的工作簿名称]被引用的工作表!被引用的单元格例如,欲在Bookl工作簿Sheet3工作表的E8单元格中引用Book2工作簿Sheet2工作表中的F9单元格,表达式为“[Bookl]Sheet3!$F$9”在输入单元格引用地址时,除了可以使用键盘键入外,还可以使用鼠标直接进行操作操作步骤如下1同时打开目的工作簿和源工作簿2在“窗口”菜单中选中“重排窗口”命令,在弹出的“重排窗口”对话框中,选中“水平平铺”或“垂直平铺”选项按钮,然后单击“确定”按钮,使两个窗口同时可见3单击Bookl工作簿中的Sheet3工作表标签,单击E8单元格4单击Book2工作簿中的任一点激活该工作簿,单击Sheet2工作表标签,单击F9单元格5按回车键,此时编辑栏中显示为“[Bookl]Sheet3!$F示一般来讲,使用鼠标选取引用方式时,Excel均默认为是单元格的绝对引用
四、名称引用当生成的公式需要引用工作表中的数据时,可以使用其中的行、列标志来引用数据例如,数据表中标志为“销售量”的数据列包含销售量,标志为“一分公司”的数据行包含与某一部门相关的各类数据,通过输入公式“二一分公司销售量”,可以查看“一公司”的销售量标志之间的空格是交叉引用运算符,它将使公式返回标志为“一公司”的行和标志为“销售量”的列交叉处的单元格的值如果待操作的数据没有标志,或者如果需要使用存储在同一工作簿不同工作表中的数据,可以创建名称来描述单元格或区域,然后使用时只需引用名称即可在默认状态下,名称使用单元格绝对引用
1.名称引用的优点在工作表中使用名称引用的好处多多,大致可归纳为以下几点1使用名字可减少在公式或命令中发生错误引用“实发金额”肯定要比引用“F2:F6”的出错机会少许多2可以重新定义名称代表的单元格,而所有公式中该名称所代表的单元格将随着新的定义而更新3使用名称可以更容易地辨识该单元格的内容和含义,公式中的描述性名称也使人们更容易理解公式的含义“基本工资”要比B2:B6”的含义清楚明白的多4在不同工作表中可以使用相同的名称Sheetl中可以定义“实发金额”,Sheet2中也可以定义“实发金额”,这在三维引用中可以大显身手5在同一工作簿中,既使是不同工作表间也可以直接调用名称,而勿需贯以工作表名称当然,在不同工作表中定义相同名称的情况除外
2.定义名称的规则1名称中只能包含下列字符汉字、A-Z、0-
9、小数点和下划线2名称的第一个字符必须是字母、文字或小数点除第一个字符外,其他字符可以使用符号3名称中不能有空格小数点和下划线可以用作分字符,例如,First.Quarter或Sales_Taxo4名称可以包含大、小写字符Microsoft Excel在名称中不区分大小写例如,如果已经创建了名称Sales,接着又在同一工作簿中创建了名称SALES,则第二个名称将替换第一个5每个名称最多不能超过255个字符6名称不能与单元格引用相同如B
1998、$K$
6、R3c8等7避免使用Excel中的固定词汇如DATABASE或AUTOOPEN
3.为单元格或单元格区域命名1选定需要命名的单元格、单元格区域或非相邻选定区域2单击编辑栏左端的名称框3为单元格键入名称4按回车键。