2014巧用函数来实现工作表间的数据关联.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