Ecel数据链接技巧

Excel数据链接技巧——在Excel2000中，用户可以通过〃链接〃将硬盘中其他Excel文件中的数据链接到所创建的工作表中，从而使编制出来的表格更加简洁、方便、实用。 在Excel中，用户可以采用不同的链接方式，例如可以将同一工作表中的不同单元格或一个单元格区域中的数据链接在一起;也可以将不同工作表中的单元格或单元格区域中的数据（不论是否是同属一个工作簿）链接在一起。当链接中的源数据发生变化时，Excel还可以更新所对应链接点中的数据。下面我们举例来说明如何设置这种链接。一、 链接工作表 假设某公司每个销售部门建有各自的销售情况工作表，当公司主管需要了解各销售部门的销售利润情况以及公司销售利润总和时，就要翻看所有销售部门的销售报表，工作量相当大。如果利用Excel中的数据链接功能，只需新建一个链接工作表（汇总表），将所有销售报表中的相关数据全部链接到汇总表中，只要打开该汇总表，就可以轻松地获得各部门销售表中的数据。操作方法如下。 1．在同一工作簿中的不同工作表中使用公式链接 （1）进行链接操作以前，在〃销售报表〃工作簿中新建一个工作表（汇总表）。 （2）在Excel中打开〃销售报表〃工作簿，将新建的〃汇总表〃设为当前工作表。 （3）可以使用以下2种方法设置链接。 方法一:直接在要链接数据的单元格中键入计算公式和链接对象及其数据所在区域，这种方法比较直观和快捷。此例中，您可以选中〃汇总表〃中的单元格C4,并在该单元格中键入〃二销售部1!D4+销售部2!D4〃或〃二SUM（销售部1!D4,销售部2!D4）〃即可。――说明：在语句〃销售部1!D4"中，〃销售部1〃为所引用的工作表名称，〃!〃为分隔工作表引用和单元格引用，而〃D4〃则为对工作表上单元格或单元格区域的引用。 方法二：采用Excel中的设置功能来设置链接，方法如下：――选中〃汇总表〃中的单元格C4,然后选择〃插入〃*〃函数〃，打开〃粘贴函数〃对话框。在该对话框中的〃函数分类〃列表框中选择〃常用函数〃选项，在〃函数名〃列表框中选择〃SUM〃函数（也可以选择其他函数命令），按〃确定〃按钮，打开〃SUM〃对话框。将光标置于〃Number1〃文本框中，然后将〃销售部1"工作表置为当前工作表，并单击该表格中的C4单元格；同样，将光标置于〃Number2〃文本框中，然后将〃销售部2〃工作表置为当前工作表，并单击该表格中的C4单元格，这时将在光标位置出现〃销售部2!C4〃字样。单击〃确定〃按钮，返回〃汇总表〃表格。可以看到在该表的C4单元格中已填入了数据,该数据就是2个销售报表中利润额之和，在编辑栏中还显示该单元格数据的计算公式（如图1所示）。冷厂品种电视销售总颔邂题B囹文件应編辑⑥视囲迥插AQ)榕式辺工冃工I冷厂品种电视销售总颔邂题B囹文件应編辑⑥视囲迥插AQ)榕式辺工冃工I範露⑹宙口轉帮肋⑪ 亠團凶A234二SUH(玛售觀呼童慎部ADQci口~"T琏接蛙式MicrcseftExc«L一镇售报喽］: ..' ■I「I 2．在不同工作簿中的工作表中使用公式链接 Excel不仅可以在同一工作簿中的不同工作表之间创建链接，还可以在不同工作簿中的工作表之间创建链接。假设上例中的"汇总表"是在另一个"公司报表"工作簿中，其操作步骤如下:----(1)将〃公司报表〃与〃销售报表〃同时打开置于同一Excel窗口中，并将〃公司报表〃工作簿置为当前工作簿。 (2)在将要包含公式的〃公司报表〃工作簿中选定希望输入外部引用的单元格(即链接点)，本例为该工作簿中〃汇总表〃工作表中的C4单元格，接下来也有2种设置方法，其设置方法基本同〃在同一工作簿中不同工作表中使用公式链接〃中的设置方法相似，所不同的是在设置该单元格中键入数据的公式中还要添加上所链接数据源的工作簿名及其路径，如如"=SUM('C:\［销售报表.xls］销售部1'!D4,'C：\［销售报表.xls］销售部2'!D4)〃。 编者注：外部引用是指对其他Excel工作簿中工作表单元格或区域的引用，或是对其他工作簿中名称的引用。在本例中指的是对〃销售报表〃工作簿中各销售部工作表中D4单元格中数据的引用。 3．链接数据的自动更新 用以上链接方式创建的工作簿一般不需要用户来维护，如果为链接提供数据的单元格发生变化时，包含链接点的工作簿中的相关数据将会自动更新。但需要注意的是，只有当包含链接点的工作簿与为链接提供数据的工作簿(源工作簿)同时打开的情况下改变源工作簿中的相关数据，或者在更新源工作簿中的相关数据后接着打开包含链接点的工作簿，Excel才会自动更新该链接。若是在关闭源工作簿后再打开包含链接的工作簿，Excel就不会自动更新链接数据，此时就必须进行人工更新链接，其操作方法如下。 (1)更新全部链接点数据 打开包含链接的工作簿时将弹出一个对话框，询问是否更新当前工作簿中的链接数据，单击〃是〃将更新该工作簿中的所有链接点中的数据。 (2)更新指定链接点数据 如果您只需要更新链接点工作簿中部分链接点的数据，则应在询问对话框中单击〃否〃，在保持原来的内容不变的情况下，打开该工作簿。然后选择〃编辑〃*〃链接〃，打开〃链接〃对话框。在该对话框中的〃源文件〃列表框中选择指定链接点数据的源对象，单击〃立即更新〃即可。如果需要选定同一源文件的多个链接，可以按住Ctrl键，然后单击各个链接对象。----根据以上情况，Excel可通过2种方式显示链接点工作簿上的公式，这取决于提供数据的工作簿（源工作簿）是否打开。----（1）当源工作簿打开时，公式中的内容使用相对链接，如〃［销售报表.xls］销售部1!D4〃。----（2）当源工作簿关闭时，公式中的内容使用绝对链接，如〃'C:\Path\［销售报表.xls］销售部1'!D4〃。二、 链接源工作表中单元格的名称----上面对源单元格引用的是代表该单元格的区域代码，如E4、D4:C5或$A$3等，这在链接点较少的情况下是可行的，但在链接点比较多、间隔时间过长的情况下就不容易辨认其代表的内容了。为此，Excel又提供了另外一种对单元格的引用方法，即对单元格名称的引用。其原理是事先为工作表中要作为源数据的单元格区域确定一个名称，在以后需要对其进行引用时，引用其名称即可。由于用户在确定其名称时，可以根据该单元格区域中的数据内容来确定，所以在设置时就很容易辨认其内容了。具体设置方法如下。 （1）将源数据工作簿和要链接点工作簿同时打开。 （2）为源单元格区域确定名称。将包含链接点工作簿置为当前工作簿，然后选择〃插入〃*〃名称〃，打开〃定义名称〃对话框（如图2所示）。在〃在当前工作簿中的名称〃项目中为源单元格确定一个名称，在本例中是〃一销售部利润〃。将〃引用位置〃项目中的内容删除（因为该引用位置并不是所需引用的单元格的位置），并将光标置于该文本框中，再选中源单元格（该单元格可以是任何工作簿中的工作表中的单元格，但必须是当前已打开的工作簿），本例中是〃销售报表〃工作簿中〃销售部1〃工作表中的D4单元格。此时Excel将在〃引用位置〃项目中自动填上该单元格的路径及所在区域代码。单击〃添加〃按钮，再单击〃确定〃按钮即可。这样，名称〃一销售部利润〃就可以代表〃［销售报表.xls］销售部1!$D$4〃来引用了。 由于本例中有2个源单元格数据需要链接，可以将另一个源数据的单元格区域的名称确定为〃二销售部利润〃。 （3）将源单元格区域链接到链接点。将包含链接点工作簿置为当前工作簿（本例为〃公司报表"）,并选中作为链接点的单元格（本例为〃公司报表〃工作簿中〃汇总表〃工作表中的C4单元格），选择〃插入〃*〃函数〃，打开〃粘贴函数〃对话框，在其中选择〃SUM〃函数（根据需要也可选择其他函数），单击〃确定〃按钮，打开〃SUM〃对话框。将光标置于〃Number1〃文本框中，选择〃插入〃*〃名称〃，单击〃粘贴〃命令项，打开〃粘贴名称〃对话框。在其列表框里选中〃一销售部利润〃，单击〃确定〃按钮，在〃Number1〃的文本框中自动填入〃一销售部利润〃。同理操作，在〃Number2〃的文本框中自动填入〃二销售部利润〃,单击"确定"按钮返回包含链接点工作簿（本例为"公司报表"），此时在编辑栏中显示的公式就为〃二SUM（—销售部利润，二销售部利润）"，链接点中数据的含义一目了然。三、打开链接方式中的源文件 如果所链接的源文件与当前文件不是同一个工作簿文件，在众多的Excel数据文件中寻找相关的源文件就是比较困难的，下面向您介绍一个打开链接方式中的源文件的简单方法。具体操作如下: 将"公司报表"工作簿置为当前工作簿，然后选择"编辑"*"链接"，打开"链接"对话框，在源文件列表框中列出了与当前工作簿相链接的文件的名称和位置。选中您需要打开的源文件，然后单击"打开源文件"按钮，源文件就会显示在Excel的窗口中。四、 更改链接方式中的源文件 有的时候用户需要变动已被链接和引用的工作表在硬盘中的存储位置，当该类文件的存储路径发生变化时，链接点工作簿就会因找不到源工作簿而不能正常链接数据。这时，您需要在"链接"对话框的"源文件"列表框中选择已更改文件路径的源工作簿，然后单击"更改源"按钮，即可将该源文件的路径修正过来。18、MATCH函数函数名称：MATCH主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：MATCH（lookup_value,lookup_array,match_type）参数说明：Lookup_value代表需要在数据表中查找的数值；Lookup_array表示可能包含所要查找的数值的连续单元格区域；Match_type表示查找方式的值（-1、0或1）。如果match_type为-1，查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列；如果match_type为1,查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列；如果match_type为0,查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果省略match_type，则默认为1。应用举例：如图4所示，在F2单元格中输入公式：=MATCH（E2,B1:B11,0）,确认后则返回查找的结果“9”。F2 - A=MATCH(E2,B1:B11J0)ABCDEF1■姓名性别语文10401T1網0丁送f卅口'3.'10402.丁271.0410403T371.0510404T470.0610405T575^0710406T672.0'810407T7男92,0910408T8男01010409丁9女67.01110410T10女隠0特别提醒：Lookup_array只能为一列或一行。20、MID函数函数名称：MID主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。应用举例：假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中输入公式：=MID(A47,4,3),确认后即显示出“天极网”的字符。特别提醒：公式中各参数间，要用英文状态下的逗号“，”隔开。27、RIGHT函数函数名称：RIGHT主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。使用格式：RIGHT(text,num_chars)参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：假定A65单元格中保存了“我喜欢天极网”的字符串，我们在C65单元格中输入公式：=RIGHT(A65,3),确认后即显示出“天极网”的字符。特别提醒：Num_chars参数必须大于或等于0,如果忽略，则默认其为1如果num_chars参数大于文本长度，则函数返回整个文本。34、VLOOKUP函数函数名称：VLOOKUP主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：VLOOKUP(lookup_value,table_array,col」ndex_num,range_lookup)参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col」ndex_num为在table_array区域中待返回的匹配值的列序号(当Col_index_num为2时，返回table_array第2列中的数值，为3时，返回第3列的值……)；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。应用举例：参见图7,我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE),确认后，只要在B65单元格中输入一个学生的姓名(如丁48)，D65单元格中即刻