Excel中引用单元格.doc

Excel中单元格的引用如果你问我，“Excel中最重要的问题是什么？”我会毫不犹豫地告诉你：“公式。”然后你又追问我，“那么，公式中最重要的又是什么？”于是我再次毫不犹豫地告诉你：“单元格的引用”。客观地讲，公式的运用是Excel区别于Word和Access的重要特征，而公式又是由引用的单元格和运算符号或函数构成，因此，单元格的引用就成为Excel中最基本和最重要的问题。不懂得怎样引用单元格，就无法利用公式对数据进行操作，不懂得不同引用样式间的区别，也就无法根据不同的情况使用不同的引用样式来正确、便捷地处理公式和数据。怎么样，这么重要的内容你不想看看吗？ 一、A1和R1C1引用样式 引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。Excel支持两种单元格的引用样式，即A1方式和R1C1样式。 1A1引用样式 这是Excel默认的引用样式。列以大写英文字母表示，从A开始到IV结束，共计256列。行以阿拉伯数字表示，从1开始到65536结束，共计65536行。由于每个单元格都是行和列的交叉点，所以其位置完全可以由所在的行和列来决定，因此，通过该单元格所在的行号和列标就可以准确地定位一个单元格。描述某单元格时，应当顺序输入列字母和行数据，列标在前行号在后。例如，A1即指该单元格位于A列1行，是A列和1行交叉处的单元格。如果要引用单元格区域，应当顺序输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用。以下是引用的示例。引用表达式位于列B和行5的单元格B5列E中行15到行30的单元格区域E15:E30行15中列B到列E的单元格区域B15:E15行5中的所有单元格5:5从行5到行10的所有单元格5:10列H中的所有单元格H:H从列H到列J中的所有单元格H:J 2R1C1引用样式 在R1C1引用样式中，Excel使用“R”加行数字和“C”加列数字来指示单元格的位置。例如，R1C1即指该单元格位于第1行第1列。在宏中计算行和列的位置时，或者需要显示单元格相对引用时，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号”。单元格的绝对引用通过在行号和列标前加一个美元符号“$”来表示，如$A$1、$B$2，以此类推。 2单元格相对引用 相对引用是像A1这样的单元格引用，该引用指引Excel从公式单元格出发如何找到引用的单元格。在街道比喻中类似于指路，即告之从出发地如何走到目的地，如“往前走三个路口。” 3单元格的混合引用 混合引用是指包含一个绝对引用坐标和一个相对引用坐标的单元格引用，或者绝对引用行相对引用列如B$5，或者绝对引用列相对引用行如$B5。 4R1C1 引用样式 同A1引用样式一样，R1C1引用样式也可以分为单元格的相对引用和单元格的绝对引用。R1C1格式是绝对引用，如R3C5是指该单元格位于第3行第5列。R1C1格式是相对引用，其中“”中的数值标明引用的单元格的相对位置，如果引用的是左面列或上面行中的单元格还应当在数值前添加“-”。如引用下面一行右面两列的单元格时表示为“R1C2”，引用上面一行左面两列的单元格时表示为“R-1C-2”， 而引用上面一行右面两列的单元格时则表示为“R-1C2”。引用含义R-2C对在同一列、上面两行的单元格的相对引用R2C2对在下面两行、右面两列的单元格的相对引用R2C2对在工作表的第二行、第二列的单元格的绝对引用R-1对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用 5绝对引用与相对引用的区别 （1）复制粘贴公式时 使用单元格的相对引用复制粘贴公式时，粘贴后公式的引用将被更新。例如，单元格C6中包含公式“=B6+C5”，是指C6单元格中的数值为其左侧单元格和上方单元格数值的和，这是单元格的相对引用。当复制C6单元格中的公式并将其粘贴到D8时，粘贴后公式中已不再是“=B6+C5”，而成为“=C8+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”，回车之后即可得出高平原的工资金额。然后，再将该公式复制并粘贴到F3、F4、F5和F6单元格，则F3中的公式改变为“=B3+C3+D3-E3”，F4中的公式改变为“=B4+C4+D4-E4”，依次类推，而这个结果正是我们所需要的。如果我们在F2中键入绝对引用单元格的公式“=$B$2+$C$2+$D$2-$E$2”会怎么样呢？ 回车之后当然也可得出高平原的工资金额，然而当我们再将该公式复制并粘贴到F3、F4、F5和F6单元格时，F3、F4、F5和F6中的公式均为“=$B$2+$C$2+$D$2-$E$2”，而这个结果却不是我们所需要的。 如果单位决定给每个职工上浮工资30%，那么F2单元格中的公式则应为“=（B2+C2+D2）*$C$8-E2”。 然后，再将该公式复制并粘贴到F3、F4、F5和F6单元格，则F3中的公式改变为“=（B3+C3+D3）*$C$8-E3”，F4中的公式改变为“=（B4+C4+D4）*$C$8-E4”，依次类推，而这个结果正是我们所需要的。在这里由于对单元格$C$8采用的是绝对引用样式，所以复制粘贴时它并不改变。如果我们将F2中的公式写为“=（B2+C2+D2）*C8-E2”会怎么样呢？当将其复制粘贴到F3、F4时，F3中的公式将改变为“=（B3+C3+D3）*C9-E3”，F4中的公式将改变为“=（B4+C4+D4）*C10-E4”，而这个结果却不是我们所需要的。由此可见，当我们不希望改变对某个单元格引用时，应当对其采用绝对引用样式。 示例二（如图2）：单元格的混合引用在该图中我们要分别计算一分公司和二分公司的销售额。计算方法有两种。低效率的方法： 在单元格D3中键入公式“=B3*C3”，然后将该公式复制并粘贴到单元格D4到D7。再在单元格F3中键入公式“=B3*E3”，然后将该公式复制并粘贴到单元格F4到F7。该方式采用的是单元格的相对引用。 高效率的方法： 在单元格D3中键入公式“=$B3*C3”，然后将该公式复制并粘贴到单元格D4到D7，再粘贴到单元格F3到F7。该方式采用的是单元格的混合引用，即对B列采取绝对引用的样式，而对其它的列和行采取相对引用的样式。结果如何？D4中的公式为“=$B4*C4”、D5中的公式为“=$B5*C5”，依次类推。F3中的公式为“=$B3*E3”、F4中的公式为“=$B4*E4”，依次类推。 （2）剪切粘贴公式时 当剪切粘贴（即移动）公式时，公式中的单元格无论是绝对引用还是相对引用，移动后公式的内容均不改变。 （3）自动填充公式时 通过拖曳填充柄的方式，可以将公式自动填充到相邻的单元格中。其自动填充效果同复制粘贴公式时的结果完全相同。因此，如果在相邻单元格中填充公式时，最好采用自动填充的方式，即快捷又方便。 使用单元格的相对引用时，例如单元格C6中包含公式“=B6+C5”，那么当向下拖曳该单元格的自动填充钮时，在C7单元格中将显示公式“=B7+C6”，在C8单元格中将显示公式“=B8+C7”，依次类推。 使用单元格的绝对引用时，例如单元格C6中包含公式“= $B$6+$C$5”，那么当向下拖曳该单元格的自动填充钮时，在C7、C8、C9等单元格中都将显示公式“=$B$6+$C$5”。 当公式中同时使用绝对引用和相对引用时，例如单元格C6中包含公式“=$B$6+C5”，那么当向下拖曳该单元格的自动填充钮时，在C7单元格中将显示公式“=$B$6+C6”， 在C8单元格中将显示公式“=$B$6+C7”，依次类推。 （4）录制宏时 如果在录制宏时选中了某些单元格，则该宏在每次运行时都将选中原来的这些单元格而无论事先是否选中了其他单元格，因为宏在这时记录的是单元格绝对引用。如果要让宏在选择单元格时不考虑活动单元格的位置，则需要将宏设置成为记录单元格相对引用。单击“停止录制”工具栏上的“相对引用”按钮，Microsoft Excel将转而按照相对引用格式继续记录宏，直到退出 Microsoft Excel 或再次单击“相对引用”按钮为止。 如果要使宏选中指定的单元格，执行某个操作，再选中另一个偏离该活动单元格一定位置的单元格，可以在记录该宏时混合使用相对和绝对引用。如果要以相对引用格式记录宏，应当确认“相对引用”按钮已按下；而要以绝对引用格式记录宏，应当确认“相对引用”按钮未被按下。 三、复杂引用 1引用同一工作簿中的其它工作表 引用同一工作簿中的其它工作表时格式如下：被引用的工作表！被引用的单元格。例如，我们欲引用Sheet8工作表中的F18单元格，表达式为“Sheet8!F18”。 在输入单元格引用地址时，除了可以使用键盘键入外，还可以使用鼠标直接进行操作。操作步骤如下： （1）打开目的工作表并选取目的单元格。 （2）键入“=”。 （3）单击Sheet8工作表标签。 （4）单击F18单元格。 （5）按回车键完成键入，此时编辑栏中将显示“=Sheet8!F18”。 一般来讲，使用鼠标选取引用方式时，Excel均默认为是单元格的相对引用。 2引用其它工作簿中的工作表 引用同一工作簿中的其它工作表时格式如下：被引用的工作簿名称被引用的工作表！被引用的单元格。例如，欲在Book1工作簿Sheet3工作表的E8单元格中引用Book2工作簿Sheet2工作表中的F9 单元格，表达式为“Book1Sheet3!$F$9”。 在输入单元格引用地址时，除了可以使用键盘键入外，还可以使用鼠标直接进行操作。操作步骤如下： （1）同时打开目的工作簿和源工作簿。 （2）在“窗口”菜单中选中“重排窗口”命令，在弹出的“重排窗口”对话框中，选中“水平平铺”或“垂直平铺”选项按钮，然后单击“确定”按钮，使两个窗口同时可见。 （3）单击Book1工作簿中的Sheet3工作表标签，单击E8单元格。 （4）单击Book2工作簿中的任一点激活该工作簿，单击Sheet2工作表标签，单击F9单元格。 （5）按回车键，此时编辑栏中显示为“Book1Sheet3!$F$9”。 一般来讲，使用鼠标选取引用方式时，Excel均默认为是单元格的绝对引用。 四、名称引用 当生成的公式需要引用工作表中的数据时，可以使用其中的行、列标志来引用数据。例如，数据表中标志为“销售量”的数据列包含销售量，标志为“一分公司”的数据行包含与某一部门相关的各类数据，通过输入公式“=一分公司 销售量”，可以查看“一公司”的销售量。标志之间的空格是交叉引用运算符，它将使公式返回标志为“一公司”的行和标志为 “销售量”的列交叉处的单元格的值。如果待操作的数据没有标志，或者如果需要使用存储在同一工作簿不同工作表中的数据，可以创建名称来描述单元格或区域，然后使用时只需引用名称即可。在默认状态下，名称使用单元格绝对引用。 1名称引用的优点 在工作表中使用名称引用的好处多多，大致可归纳为以下几点： （1）使用名字可减少在公式或命令中发生错误。引用“实发金额”肯定要比引用“F2:F6”的出错机会少许多。 （2）可以重新定义名称代表的单元格，而所有公式中该名称所代表的单元格将随着新的定义而更新。 （3）使用名称可以更容易地辨识该单元格的内容和含义，公式中的描述性名称也使人们更容易理解公式的含义。“基本工资”要比“B2:B6”的含义清楚明白的多。 （4）在不同工作表中可以使用相同的名称。Sheet1中可以定义“实发金额”，Sheet2中也可以定义“实发金额”，这在三维引用中可以大显身手。 （5）在同一工作簿中，既使是不同工作表间也可以直接调用名称，而勿需贯以工作表名称。当然，在不同工作表中定义相同名称的情况除外。 2定义名称的规则 （1）名称中只能包含下列字符：汉字、A-Z、0-9、小数点和下划线。 （2）名称的第一个字符必须是字母、文字或小数点。除第一个字符外，其他字符可以使用符号。 （3）名称中不能有空格。小数点和下划线可以用作分字符，例如，First.Quarter 或 Sales_Tax。 （4）名称可以包含大、小写字符。Microsoft Excel 在名称中不区分大小写。例如，如果已经创建了名称 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）当名称在同一工作簿时 选中“插入”菜单“名称”子菜单中的“粘贴”命令，在“粘贴名称”列表框中选中欲插入的引用名称，单击“确定”按钮，则该名称被插入到当前位置。当然，也可以在插入点直接键入欲引用的单元格或单元格范围的名称，如“=销售总额-成本总额”。 （2）当名称不在同一工作簿时 打开源工作簿和目的工作簿，并使其水平平铺或垂直平铺，然后在目的工作簿插入点直接键入，格式为：“欲引用单元格所在的