(巧用Excel函数来实现工作表间的数据关联.docVIP

巧用Excel函数来实现工作表间的数据关联 开学不久，教导处交给我一个光荣的任务：填报学年报表。在学年报表中，要求填写在校所有学生的姓名、性别、家庭住址等各种信息。由于以前每学年都要填写，所以我认为这并不是一件很难的事，只要把以前的报表找出来稍改一下不就行了吗。可是，教导主任的一句“今年所有的班级已经全部重新分了班”却让我叫苦不迭。我差点儿晕倒，这样一个个的查找，再复制、粘贴，不是要把人累死吗？教导主任大概也知道工作量比较大，安慰我说：“这样吧，周末来加加班，给你多开点儿补贴。”我无可奈何地点了点头。????思路分析：目前在Excel中有两张表，一张表是原来的学年报表(即“原表”，含学生的各种信息，如图1所示)，一张表是现在的学生花名册(即“新表”，只有学生的姓名和性别信息，如图2所示)。只要能将“原表”中的信息根据“新表”中的学生姓名关联到“新表”中，就可以完成任务了。 ????还是到论坛上去讨教吧。果然，一会儿功夫，高手们告诉我，用VLOOKUP函数可以解决这个问题，并说：“很简单！”呵呵，很简单？我可没用过呢！不过，既然已经知道方法了，我的心里就有底了。于是，到Excel帮助里把VLOOKUP的使用方法好好研究了一番，终于用它解决了问题。下面是解决以上问题的方法：????选择“新表”中的C2单元格，如图3所示。单击[fx]按钮，出现“插入函数”对话框。在类别中选择“全部”，然后找到VLOOKUP函数，单击[确定]按钮，出现“函数”参数对话框。第一个参数为需要在数据表首列进行搜索的值，在这里就是搜索学生姓名，也就是“新表”中的A2单元格。第二个参数是需要在其中搜索数据的信息表，这里也就是整个“原表”的数据，即“原表!A2:G34”。为了防止出现问题，这里，我们加上“$”，即“原表!$A$2:$G$34”，这样就变成绝对引用了。第三个参数为满足条件的单元格在数组区域内中的列序号，在本例中，也就是在“原表!$A$2:$G$34”这个区域中，根据第一个参数返回第几列的值，这里我们填入“3”，也就是返回出生年月的值。第四个参数为指定在查找时是要求精确匹配还是大致匹配，如果填入“0”，则为精确匹配。这可含糊不得的，我们需要的是精确匹配，所以填入“0”（请注意：Excel帮助里说“为0时是大致匹配”，但很多人使用后都认为，微软在这里可能弄错了，为0时应为精确匹配），此时的情形如图4所示。按[确定]按钮退出，即可看到C2单元格已经出现了正确的结果。????把C2单元格向右拖动复制到D2单元格，这时会出现错误，原因在哪里呢？原来，D2的公式自动变成了“=VLOOKUP(B2,原表!$A$2:$G$34,3,0)”，我们需要手工改一下，把它改成“=VLOOKUP(A2,原表!$A$2:$G$34,4,0)”，即可显示正确数据。继续向右复制，同理，把后面的E2、F2等中的公式适当修改即可。一行数据出来了，对照了一下，数据正确无误，再对整个工作表进行拖动填充，整个信息表就出来了。 ????最后说明一下：使用这个公式的时候，要求学生名称不能有重名，如果有的话，要先稍改一下，比如在名称后面加个“大”或“小”以示区分。????看看时间，还没超过一个小时就完成了任务，虽然学习公式花了一些时间，但“磨刀不误砍柴工”，还是很合算的！当我把打印好的表格交给教导主任时，他一下愣住了……本文介绍如题所示的操作过程； 　　范例环境： 　　在F:\盘根目录下，存在一工作薄，名称为“成绩表.xls”，其对应的路径是：“F:\成绩表.xls”； 　　该工作薄第一个工作表名称是：Sheet1，里面存放的是学生期末考试成绩，其中，E列从E3开始，就是学生的考试成绩； 　　下面我们要实现的是，在关闭F:\成绩表.xls情况下，打开Excel软件，新建一工作薄，在其工作表中的指定单元格，读取F:\成绩表.xls中的指定的成绩数据进行填充； 　　①：在新建的工作表中直接按下组合键：Alt+F11，打开Microsoft Visual Basic窗口；如果打开的窗口没有出现代码窗口，那么，请在当前窗口执行操作：“视图”→“代码窗口”； 　　②：在代码窗口中输入如下的代码： Private Function GetValue(path, filename, sheet, ref)??? 从关闭的工作薄返回值??? Dim MyPath As String??? 确定文件是否存在??? If Right(path, 1) \ Then path = path \??? If Dir(path filename) = Then??????? GetValue = 无法找到指定的Excel文件??????? Exit Fun