知识介绍——2.3.3 单元格引用资料文件与下载

单元格引用

Excel中最重要的问题是公式，公式中最重要的问题是单元格的引用。公式的运用是Excel区别于Word和Access的重要特征，而公式又是由引用的单元格和运算符号或函数构成，因此，单元格的引用就成为Excel中最基本和最重要的问题。只有熟练掌握怎样引用单元格的知识，才可以利用公式正确的对数据进行操作；只有懂得不同引用样式间的区别，才能根据不同的情况使用不同的引用样式来正确、便捷地处理公式和数据。

一、 绝对引用和相对引用

1．单元格的绝对引用

不论包含公式的单元格处在什么位置，公式中所引用的单元格位置都是其工作表的确切且唯一的位置。单元格的绝对引用通过在行号和列号前加一个美元符号“$”来表示，如$A$1、$B$2，以此类推。

2．单元格相对引用

相对引用是像A1这样的单元格引用，该引用指引Excel从公式单元格出发如何找到引用的单元格。

3．单元格的混合引用

混合引用是指包含一个绝对引用坐标和一个相对引用坐标的单元格引用，或者绝对引用行相对引用列如B$5，或者绝对引用列相对引用行如$B5。

4．R1C1引用样式

同A1引用样式一样，R1C1引用样式也可以分为单元格的相对引用和单元格的绝对引用。R1C1格式是绝对引用，如R3C5是指该单元格位于第3行第5列。R[1]C[1]格式是相对引用，其中“[]”中的数值标明引用的单元格的相对位置，如果引用的是左面列或上面行中的单元格还应当在数值前添加“-”。如引用下面一行右面两列的单元格时表示为“R[1]C[2]”，引用上面一行左面两列的单元格时表示为“R[-1]C[-2]”，而引用上面一行右面两列的单元格时则表示为“R[-1]C[2]”。表2-5为R1C1格式引用单元格示例表。

表2-5 R1C1格式引用单元格示例表

5．绝对引用与相对引用的区别

（1）复制粘贴公式时

使用单元格的相对引用复制粘贴公式时，粘贴后公式的引用将被更新。

使用单元格的绝对引用复制粘贴公式时，粘贴后公式的引用不发生改变。

如果一个个公式中同时使用绝对引用和相对引用时，粘贴后公式的相对引用部分被更新而绝对引用不发生改变。

试以成绩表（图2-8所示）进行分析。

分析1：在单元格D2中输入公式“=B2+C2”时，回车后可得到张三同学的总分值。再将该公式复制并粘贴至D3、D4、D5、D6、D7单元格，则D3单元格的公式改变为“=B3+C3”，D4单元格的公式变为“=B4+C4”，D5单元格的公式为“=B5+C5”，D6单元格的公式为“=B6+C6”，D7单元格的公式“=B7+C7”。全部同学的总分计算完毕。

分析2：在单元格D2中输入公式“=$B$2+$C$2”时，回车后可得到张三同学的总分值。再将该公式复制并粘贴至D3、D4、D5、D6、D7单元格，D3至D7单元格的公式仍为“=$B$2+$C$2”，计算结果都是张三同学的总分。

分析3：在单元格D2中输入公式“=$B2+$C2”时，回车后可得到张三同学的总分值。

再将该公式复制并粘贴至D3、D4、D5、D6、D7单元格，D3至D7单元格的公式分别为“=$B3+$C3”、“=$B4+$C4”、“=$B5+$C5”、“=$B6+$C6”、“=$B7+$C7”，因为公式中列名前加“$”为绝对引用，所以列名都没改变，但行号改变。也可以准确计算出全部同学的总分值。

分析4：“语文名次”、“数学名次”和“总名次”的计算。

分析的计算可以使用RANK函数。RANK函数的第一个参数为要排序的数字即成绩所对应的单元格（每位同学的成绩单元格是不同的，用相对引用实现），第二个参数为排序范围即从第一位同学至最后一位同学的成绩对应单元格（这个范围应该是固定的，用绝对引用实现）。

“语文名次”的实现：在单元格E2输入公式“=RANK(B2,B$2:B$7”后按回车键，再将该公式复制粘贴至E3至E7即可求出所有同学的“语文名次”值。

“数字名次”和“总名次”的实现可以重复“语文名次”的实现过程。

另有一种更快速的方法是：首先在E2单元格输入公式“=RANK(B2,B$2:B$7”后按回车键。然后将鼠标移至E2单元格右下角当鼠标形状为黑色十字时向右拖动至G2单元格，再将鼠标移至G2单元格右下角（此时E2、F2和G2共三个单元格处于选定状态）当鼠标形状为黑色十字即自动填充柄时向下拖动至最后一位同学对应名次处。这样即可将全部同学的三科名次值计算出来。具体原因请同学们自行分析。

（2）剪切粘贴公式时

当剪切粘贴（即移动）公式时，公式中的单元格无论是绝对引用还是相对引用，移动后公式的内容均不改变。

（3）自动填充公式时

通过拖曳填充柄的方式，可以将公式自动填充到相邻的单元格中。其自动填充效果同复制粘贴公式时的结果完全相同。因此，如果在相邻单元格中填充公式时，最好采用自动填充的方式，即快捷又方便。

二、复杂引用

1．引用同一工作簿中的其它工作表

引用同一工作簿中的其它工作表时格式如下：被引用的工作表！被引用的单元格。例如，我们欲引用Sheet1工作表中的F18单元格，表达式为“Sheet1!F18”。

在输入单元格引用地址时，除了可以使用键盘键入外，还可以使用鼠标直接进行操作。操作步骤如下：

（1）打开目的工作表并选取目的单元格。

（2）键入“=”。

（3）单击Sheet1工作表标签。

（4）单击F18单元格。

（5）按回车键完成键入，此时编辑栏中将显示“=Sheet1!F18”。

一般来讲，使用鼠标选取引用方式时，Excel均默认为是单元格的相对引用。

2．引用其它工作簿中的工作表

引用同一工作簿中的其它工作表时格式如下：[被引用的工作簿名称]被引用的工作表！被引用的单元格。例如，欲在Book1工作簿Sheet3工作表的E8单元格中引用Book2工作簿Sheet2工作表中的F9单元格，表达式为“[Book1]Sheet3!$F$9”。

在输入单元格引用地址时，除了可以使用键盘键入外，还可以使用鼠标直接进行操作。操作步骤如下：

（1）同时打开目的工作簿和源工作簿。

（2）在“窗口”菜单中选中“重排窗口”命令，在弹出的“重排窗口”对话框中，选中“水平平铺”或“垂直平铺”选项按钮，然后单击“确定”按钮，使两个窗口同时可见。

（3）单击Book1工作簿中的Sheet3工作表标签，单击E8单元格。

（4）单击Book2工作簿中的任一点激活该工作簿，单击Sheet2工作表标签，单击F9单元格。

（5）按回车键，此时编辑栏中显示为“[Book1]Sheet3!$F$9”。

一般来讲，使用鼠标选取引用方式时，Excel均默认为是单元格的绝对引用。

三、名称引用

当生成的公式需要引用工作表中的数据时，可以创建名称来描述单元格或区域，然后使用时只需引用名称即可。例如在图2-16中，将B2至B7共6个单元格定义为“yuwen”名称，SUM(yuwen)即相当于sum(B2:B7)。在默认状态下，名称使用单元格绝对引用。

1．名称引用的优点

在工作表中使用名称引用的好处多多，大致可归纳为以下几点：

（1）使用名字可减少在公式或命令中发生错误。引用“yuwen”肯定要比引用“B2B7”的出错机会少许多。

（2）可以重新定义名称代表的单元格，而所有公式中该名称所代表的单元格将随着新的定义而更新。

（3）使用名称可以更容易地辨识该单元格的内容和含义，公式中的描述性名称也使人们更容易理解公式的含义。“yuwen”要比“B2:B6”的含义清楚明白的多。

（4）在不同工作表中可以使用相同的名称。Sheet1中可以定义“yuwen”，Sheet2中也可以定义“yuwen”。

（5）在同一工作簿中，既使是不同工作表间也可以直接调用名称，无需标以工作表名称。当然，在不同工作表中定义相同名称的情况除外。

2．定义名称的规则

（1）名称中只能包含下列字符：汉字、A-Z、0-9、小数点和下划线。

（2）名称的第一个字符必须是字母、文字或小数点。除第一个字符外，其他字符可以使用符号。

（3）名称中不能有空格。小数点和下划线可以用作分字符，例如，First.Quarter或Sales_Tax。

（4）名称可以包含大、小写字符。MicrosoftExcel在名称中不区分大小写。例如，如果已经创建了名称Sales，接着又在同一工作簿中创建了名称SALES，则第二个名称将替换第一个。

（5）每个名称最多不能超过255个字符。

（6）名称不能与单元格引用相同。如B1998、$K$6、R3C8等。

（7）避免使用Excel中的固定词汇。如DATABASE或AUTO-OPEN。

3．为单元格或单元格区域命名

（1）选定需要命名的单元格、单元格区域或非相邻选定区域。

（2）单击编辑栏左端的名称框。

（3）为单元格键入名称。

（4）按回车键。

注意：当正在修改单元格中的内容时，不能为单元格命名。

4．使用现有的行列标志为单元格命名

（1）选定需要命名的区域，把行列标志也包含进去。

（2）在“插入”菜单中，指向“名称”，再单击“指定”命令。

（3）在“名称在”选项框中，通过选定“首行”、“最左列”、“尾行”或“最右列”复选框来指定包含标志的位置。

注意：使用这个过程指定的名称只引用包含数值的单元格，而不包含现有的行列标志。

5．修改名称

（1）在“插入”菜单“名称”子菜单中选中“定义”命令，显示“定义名称”对话框。

（2）在“当前工作簿中名称”列表框中，选中欲更改的名称。

（3）在“当前工作簿中名称”文本框或“引用位置”文本框中，编辑、修改欲更改的名称或引用的单元格位置。

（4）单元“确定”按钮。

6．删除名称

（1）在“插入”菜单“名称”子菜单中选中“定义”命令，显示“定义名称”对话框。

（2）在“当前工作簿中名称”列表框中，选中欲删除的名称。

（3）单击“删除”按钮。

7．引用名称

（1）当名称在同一工作簿时

单击“插入”/“名称”子菜单中的“粘贴”命令，在“粘贴名称”列表框中选中欲插入的引用名称，单击“确定”按钮，则该名称被插入到当前位置。当然，也可以在插入点直接键入准备引用的单元格或单元格范围的名称，如“=SUM(YUWEN)”。

（2）当名称不在同一工作簿时

打开源工作簿和目的工作簿，并使其水平平铺或垂直平铺，然后在目的工作簿插入点直接键入，格式为：“欲引用单元格所在的工作簿名称！引用名称”，如“=成绩汇总表！平均成绩*0.6”。在这里，“成绩汇总表”是引用的工作簿名称，“平均成绩”是引用的单元格名称（前提是已经将“平均成绩”命名为单元格G2：G60的名称）。

注意：如果源工作簿保存在My Documents件夹中，则可以不打开源工作簿，引用时也勿需使用路径。如果源工作簿没有保存在My Documents文件夹中，则必须打开源工作簿，或者在引用时需添加该工作簿所在的路径。