VLOOKUP的应用（精选9篇）
VLOOKUP的应用 篇1
1 背景
使用Excel不仅可以制作各类表格,而且可以计算、管理和分析表格数据。Excel强大的统计、分析功能来自于它的公式和函数。如果你是单位的财务人员,可能会被无穷的数据折磨得头昏脑涨。有一个单位会计负责材料、成本,每个月总有那么二天会坐在电脑桌前忙着查找复制数据,更新产品的最新单价。在从其他部门拷贝过来的产品新单价表格里,输入产品编号点击查找,然后右键复制其最新的价格,再将其粘到自己的表格里,循环往复周而复始。后来采用VLOOKUP函数,这项工作就从二天减少到了半个小时。在实际工作中,很多人体会到了使用VLOOKUP函数的好处,但还是听到有人抱怨说VLOOKUP有的时候好用,有的时候不好用。其实是他没有完全搞清楚VLOOKUP的使用规则,才导致达不到他的查找目标。正是基于这个原因,有必要详细地分析一下VLOOKUP函数。
2 函数的语法结构
VLOOKUP是一个垂直查询函数。给定一个查找的目标,搜索指定单元格区域的第一列,然后返回该区域相同行上任何指定单元格的值。其函数格式为:
VLOOKUP(Lookup_value,Table_array,Col_index_num,[Range_lookup])
2.1 参数说明
Lookup_value查找目标是必选参数。指要在表格或区域的第1列搜索到的目标值,就是你指定的要查找的内容或单元格引用。
Table_array查找范围是必选参数。指定了从哪个范围中进行查找,一般是指要查找的数据所在的单元格区域,也可以从一个常量数组或内存数组中查找。
Col_index_num返回值的列号是必选参数。它是一个整数值,指出了最终返回数据所在的列号。需要注意的是,它是“返回值”在第二个参数给定的区域中的列号。
Range_lookup精确/模糊查找是可选参数。它是一个逻辑值,取值为True或者False,指定希望VLOOKUP查找精确匹配值还是近似匹配值,最后一个参数是决定函数精确和模糊查找的关键。如果该参数为True或被省略,则返回近似匹配值;如果为False,那么将只查找精确匹配值。
2.2 必选参数使用注意事项
下面以实例来解析一下这四个参数的使用。
例1:如图1所示,要求根据表二中的图书名称,查找图书名称所对应的定价。
第二个参数“查找范围”是很容易出错的地方,必须引起足够的重视。首先查找区域的第一列必须是查找目标要搜索的值,本例中查找表二的“图书名称”,那么在表一中选定的查找区域的第一列就必须是图书名称,而不能从图书编号列开始选取查找区域。即应该是$B$3:$C$10,而不能是$A3:$C$10,因为查找的“图书名称”不在$A3:$C$10区域的第一列。其次该查找区域一定要包含返回值所在的列,本例中要返回的值是定价,表一的C列定价一定要包括在这个查找范围内。
第三个参数是指查找区域范围内的第几列,不是所在工作表中的列号。本例中我们要返回的是“定价”,它是第二个参数查找范围$B$3:$C$10的第2列,如果写成工作表中的列号3就是错误的。如果本例中要查找的是图书名称所对应的出版社,则第2个参数应该设置为$B$3:$D$10,第3个参数的值应该设置为3,因为“出版社”在该区域的第3列中。
3 精确/模糊查找
Range_lookup可选参数如果指定值是0或FALSE就表示精确查找,如果查找区域的第1列中有两个或多个值与Lookup_value匹配,则使用第1个找到的值。而值为1或TRUE时则表示模糊查找,这种情况下如果找不到精确匹配值,则返回小于Lookup_value的最大值。该参数缺省值为模糊查找,精确即完全一样,模糊即近似匹配的意思。
3.1 对话框提示中的问题
在使用该函数的时候我发现一个问题,就是微软的Excel中该函数参数提示是有问题的。如图2所示是在Excel2010中,使用VLOOKUP函数自动弹出的对话框,对话框中明显指出该参数“如果为TRUE或忽略,精确匹配”。
为了验证究竟哪一个说法正确,只能实际上机操作去探究。从上机实践中就会发现,该参数为FALSE是精确匹配,所以我个人认为软件中的提示是有偏差的。很多人在使用该函数时总是抱怨时常出错,这与软件“提示不当”应当有很直接的关系。
3.2 模糊查找
Range_lookup参数为FALSE时,因为是精确查找,所以不需要对Table_array查找范围的第1列中的值进行排序。但是如果该参数设置为模糊查找,那么就必须按升序排列Table_array查找区域第1列中的值,否则VLOOKUP函数可能无法返回正确的值。
如图3所示,它是大气特征表“密度”、“粘度”和“温度”的对应关系,这些值是在1个大气压下或海平面0摄氏度对空气的测定。
例2:要求根据表二中的大气密度,查找表一中大致“密度”所对应的“粘度”。
公式中搜索A列中的值1,在A列中由于找不到等于1的精确值,所以使用近似匹配找到小于1的最大值0.946,然后返回同一行中B列的值2.17。单元格F4中的公式也是使用近似匹配搜索A列中的值2,在A列中找到小于等于2的最大值,即最后一行的1.29,然后返回同一行中B列的值1.71。
4 多列查找与函数的出错处理
4.1 多列查找时公式的复制
VLOOKUP函数的第3个参数是查找返回数据所在的列号,如果我们需要查找返回多列数据值时,这个列数值需要一个一个的更改,例如返回Table_array查找区域第2列中的值,Col_index_num参数设置为2,如果需要返回Table_array第3列中的值,就需要把Col_index_num参数修改为3,以此类推……,如果需要返回若干列的值就会非常麻烦。那么是否可以让第3个参数自动改变呢?向右复制公式时2自动变为3、4、5等,这就需要用到COLUMN函数。
在EXCEL中有一个函数COLUMN,它可以返回一引用的列号,比如:
当然,COLUMN(B1)也可以用COLUMN(B2)、COLUMN(B3)等替代。
例3:要求根据如图4所示的大气密度,查找图3中表一大致“密度”所对应的“粘度”和“温度”。
复制公式时单元格引用会自动发生变化,即COLUMN(B1)随公式向右复制时会变成COLUMN(C1)、COLUMN(D1)……,这样列号2就可以自动转换成数字3、4……,通过使用COLUMN(B1)转化成可以自动递增的数字。由于向右复制公式时第1个参数E3会根据相对位置变成F3,所以“列”要设置成绝对地址$E3。
4.2 函数的出错处理
在进行查找时,如果第三个参数Col_index_num值小于1,则VLOOKUP返回错误值#VALUE!;如果该参数值大于Table_array的列数,则VLOOKUP函数返回错误值#REF!。当第四个参数Range_lookup为FALSE时,进行精确查找,如果找不到精确匹配值,则返回错误值#N/A。
有时我们并不希望在文件中出现这些错误信息,那如何避免出现这些错误值呢?在Excel2003中有一个ISERROR(value)函数,它用来检查一个值是否为错误(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!),返回TRUE或FALSE。可以利用该错误处理函数把错误值转换成空值或0,即:
=IF(ISERROR(VLOOKUP(参数略…)),"",VLOOKUP(参数略…))
EXCEL2010中提供了一个新函数IFERROR(value,value_if_error),该函数的功能是:如果表达式是一个错误,则返回value_if_error,否则返回表达式自身的值。该函数处理起来比ISERROR简单多了,语句如下:
=IFERROR(VLOOKUP(参数略…),"")
VLOOKUP函数查找时出现错误值,除了上面提到的“实在是没有所要查找到的值”这个原因外,还有以下几方面的原因:首先,查找的字符串或被查找的字符中含有空格或看不见的空字符。可以用=号对比验证一下,如果结果是FALSE,就表示两个单元格看上去相同,其实内容不同。其次,参数设置错误。如第二个参数数据源区域,查找的值不是区域的第一列,或者需要返回的字段不在区域里。最后还有可能是数据格式不同,如果查找值是文本,而被查找的是数字类型,就会查找不到。解决方法是将其进行类型转换,文本转换成数值可以将其*1或/1,数值转换成文本可以将其&""。
3结束语
以上只是对VLOOKUP函数的应用进行了分析,其实Excel提供了大量的、类型丰富的实用函数,可以通过各种运算符及函数构造出各种公式以满足各类应用的需要。使用者也不仅仅局限于财务人员,人事档案人员以及各个行业各部门的人员也都广泛地使用着这些函数,它给我们的工作提供了相当的便利,帮我们做好管理分析,极大地提高了工作的效率和效果。
参考文献
[1]于双元.全国计算机等级考试二级教程——MS Office高级应用[M].北京:高等教育出版社,2016.
[2]冯梅,吴静.Vlookup函数的应用[J].成都航空职业技术学院学报,2013(4):57-59.
[3]赵春兰.Excel2010应用教程[M].北京:人民邮电出版社,2015.
VLOOKUP的应用 篇2
“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。
一、功能
在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。
二、语法
标准格式：
VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)
三、语法解释
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为：
VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)
1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。
2.Table_array 为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。
⑴如果 range_lookup 为 TRUE或省略，则 table_array 的第一列中的数值必须按升序排列，否则，函数 VLOOKUP 不能返回正确的数值。
如果 range_lookup 为 FALSE，table_array 不必进行排序。
⑵Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。
3.Col_index_num 为table_array 中待返回的匹配值的列序号。
Col_index_num 为 1 时，返回 table_array 第一列中的数值；
Col_index_num 为 2 时，返回 table_array 第二列中的数值，以此类推。
如果Col_index_num 小于 1，函数 VLOOKUP 返回错误值 #VALUE!；
如果Col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。
4.Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。
四、应用例子
A B C D编号 姓名 工资 科室2005001 周杰伦 2870 办公室2005002 萧亚轩 2750 人事科2005006 郑智化 2680 供应科2005010 屠洪刚 2980 销售科2005019 孙楠 2530 财务科2005036 孟庭苇 2200 工 会
A列已排序（第四个参数缺省或用TRUE）
VLOOKUP(2005001,A17,2,TRUE)等于“周杰伦”
VLOOKUP(2005001,A17,3,TRUE)等于“2870”
VLOOKUP(2005001,A17,4,TRUE)等于“办公室”
VLOOKUP(2005019,A1:D7,2,TRUE)等于“孙楠”
VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200”
VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 会”
VLOOKUP(2005036,A1:D7,4)等于“工 会”
若A列没有排序，要得出正确的结果，第四个参数必须用FALAEVLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰伦”VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870”
VLOOKUP(2005001,A1:D7,4,FALSE)等于“办公室”VLOOKUP(2005019,A1:D7,2,FALSE)等于“孙楠”VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200”
VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 会”
五、关于TRUE和FALSE的应用
先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。
用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。
笔者觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。
最近爱上了VLOOKUP，有人还对它进行了更新。因为它的漏洞就是只能返回重复值得第一个值。下面就详细来叙述一下吧！
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 为需要在Table_array第一列中查找的数值。
可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。
Table_array 为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。
该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。
Col_index_num 为table_array中待返回的匹配值的列序号。
如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。
如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!；
如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；
近似匹配查询一般用于数值的查询，table_array的第一列必须按升序排列；否则不能返回正确的结果。
如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。
此时，table_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。
VLOOKUP 经常会出现错误的#N/A,下面是几种可能性： 数据有空格或者数据类型不一致。
可以在lookup_value 前用TRIM（）将空格去除。
如果格式不一致，可以将数值强制转换成文本，lookup_value之后用&跟""表示的空字符串。将文本转换成数值，lookup_value*1进行运算。假设有以下两个表格：表1：
姓名 身份证 学科 成绩
张三 310111 物理 A
李斯 310112 化学 B王五 310113 政治 A赵六 310114 物理 B
张三 310111 政治 C
赵六 310114 语文 B刘备 310115 英语 D
表2：
姓名 身份证 住址 学科 成绩
曹操 319723 陕西马超 310197 甘肃赵云 319784 黑龙江
李斯 310112 浙江刘备 310115 江苏张三 310111 上海
请问：如何快速的将表2的学科和成绩自动填入？直接使用VLOOKUP即可。示例如下：
=VLOOKUP($B1,Sheet1!$B$1C$5,2,FALSE)
涵义：在Sheet1的单元格区间B1:C5，查找符合条件为：第一列与当前工作表单元格B1的值相等的第二列的值。
$B1 条件值
Sheet1!$B$1C$5 要查找区间（注意：请使用绝对引用）2 查找的结果为区域中的第几列FALSE 表示精确查找
VLOOKUP的应用 篇3
我校的教务管理非常繁琐, 主要有教师的排课及计算课酬、学生的成绩管理、教材的管理及日常教学活动的管理, 而VLOOKUP函数在处理这些问题时都有不俗的表现。
●在计算课酬方面的应用
我们学校规定, 不同的职称每节课的课酬不同, 因此可先建立一张工作表“课酬标准”, 把不同职称的单节课课酬用一张数据表列出来 (如图1) 。把每个教师的总课时在另一张工作表“课时统计”中统计出来 (如图2) 。在工作表“课酬统计”中, 按“姓名”、“职称”、“总课时:节”、“总课酬:元”的顺序建立表头数据。然后在C2单元格中输入公式:“=VLOOKUP (A2, 课时统计!$A$2:$B$7, 2, 0) ”;在D2单元格中输入公式:“=VLOOKUP (B2, 课酬标准!$A$1:$B$6, 2, 0) *C2”即可 (如图3) 。
VLOOKUP函数的功能是:在表格或数值数组的首列查找指定的数值, 并由此返回表格或数组当前行中指定列处的数值。它的语法为:VLOOKUP (lookup＿value, table＿array, col＿index＿num, range＿lookup) 。其中的参数Lookup＿value为需要在数组第一列中查找的数值。Table＿array为需要在其中查找数据的数据表。Col＿index＿num为table＿array中待返回的匹配值的列序号。Range＿lookup为一逻辑值, 指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略, 则返回近似匹配值, 也就是说, 如果找不到精确匹配值, 则返回小于lookup＿value的最大数值;如果range＿value为FALSE, 函数VLOOKUP将返回精确匹配值。如果找不到, 则返回错误值#N/A。
在C 2单元格中输入公式:“=V L O O K U P (A 2, 课时统计!$A$2:$B$7, 2, 0) ”, 它的功能是在工作表“课时统计”中的A2∶B7区域中查找单元格A2的值, 如果找到, 则返回该数组当前行的第2列的数值 (总课时) ;在D2单元格中输入公式:“=VLOOKUP (B2, 课酬标准!$A$1:$B$6, 2, 0) *C2”, 它的功能是在工作表“课酬标准”中的A1∶B6区域查找B2单元格的值, 如果找到, 则返回该数组当前行的第2列的数值 (单节课酬) , 并把它和总课时相乘就可以轻松得出总课酬数了。
●在学生成绩管理方面的应用
教务管理人员一般会用Excel来保存学生成绩等方面的信息, 有时需要给这些表格建立很多个副表, 有时在一个表格中要使用另一个表格中的某些数据, 怎样可以使这些复制的表格中的数据或引用的数据能够随原表的修改而修改呢?使用VLOOKUP函数就可以达到目的。例如, 在每个学期末, 我校都要把成绩单寄往学生家里, 这就需要制作每个学生的成绩单, 如果这个任务是由班主任完成, 可用Word的“邮件合并”功能来做, 而在我校, 这个任务是由教务科来完成, 因为每个班的科目不同, 如果还用Word的“邮件合并”功能来做, 每个班需要制作不同的主文档, 这样会浪费很多时间, 这时, 可以让VLOOKUP函数登场了。
首先, 把任意一个班的成绩单复制到一个新工作簿的sheet1工作表。然后, 把其中的表头数据复制到sheet2工作表。接下来, 在sheet2工作表的A3单元格中输入数值1, 在B3单元格中输入公式:“=VLOOKUP (A3, Sheet1!$A$3:$Y$100, 2, FALSE) ”;注意:这里的引用区域采用“$A$3:$Y$100”, 是充分估计一个班的人数和考试科目, 使公式具有通用性。然后在C3∶N3单元格中类似地输入相应公式, 只需把前面公式中函数的第3个参数相应改为3～14即可, 如在C3单元格中输入公式:“=VLOO KUP (A3, Sheet1!$A$3:$Y$100, 3, FAL SE) ”;在N3单元格中输入公式:“=VL OOKUP (A3, Sheet1!$A$3:$Y$100, 14, F ALSE) ”;可得到该班第1个学生的成绩单 (如图4) 。
因为该班只有11门课, 所以只需在B3∶N3输入公式, 如果课程数量更多, 则需要继续在第3行右边的列中输入公式。充分估计班级的课程数目 (如15门) , 可以在O3∶R3单元格中根据前面所述的规律继续输入公式。
接下来把该成绩单格式化, 如加上表格边框等, 然后选取单元格区域A2∶N4, 并向下填充到充分区域。如果要制作100人的班级的成绩单, 则要填充到第300行, 本例中第一个班只有40人, 只需要填充到第120行, 而从第41个学生开始的成绩单中会填充“#N/A”这样的值 (如图5) 。最后, 选择该班的有效数据区域进行打印即可。
当要打印其他班级的成绩单时, 把新班级的数据复制到sheet1工作表, 注意表头数据必须在1、2行。然后把sheet2工作表中原来的表头数据清除, 再把新班级的表头数据复制到sheet2工作表的第1、2行, 这时, 新班级的第一个学生的成绩单已经有了 (如图6) 。
选择单元格区域A2∶L4, 向下填充, 如该班只有36人, 则填充到第108行即可, 然后选择该班的有效数据区域进行打印。
制作新班级的成绩单时, 切忌把原来班级第一个成绩单中第3行多余的数据删除 (如图6的M3和M4单元格中的“0”) , 因为删除了其中的公式, 如果其他班级课程数目多时就不能有效地引用数据。
VLOOKUP的应用 篇4
vlookup是垂直方向的判断，如果是水平方向的判断可使用Hlookup函数。
vlookup函数是查找函数，是用于纵向查找的函数。在 VLOOKUP 中的 V 代表垂直。
函数vlookup的用法和功能：用VLOOKUP来实现查找和引用功能。vlookup函数在表格左侧的行标题中查找指定的内容，当找到时，再挑选出该行对应的指定列的单元格内容。
excel vlookup函数使用方法：vlookup函数的语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
excel vlookup函数使用方法：vlookup函数各参数解析
一，lookup_value：lookup是查找的意思，value是值，就是要查找的值。
二，table_array：“你要到哪去查找这个值?”就在这个table_array的区域里。选定这个区域，公式将在这个区域对lookup_value进行查找。
三，col_index_num：col是column单词的缩写，是列的意思，index是索引的意思，合起来就是你在table_array区域中要找的值所在的列数，比如你要找一列电话号码，是在table_array区域的第三列，那么col_index_num就是3，这是一个相对引用的概念。
四，range_lookup：为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。
说明：如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。
如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。
如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。
excel vlookup函数使用方法：举例说明
假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价：
A B
1 农产品名称 单价
2 小麦 0.56
3 水稻 0.48
4 玉米 0.39
5 花生 0.51
…………………………………
100 大豆 0.45
Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2：
A B C D
1 农产品名称 数量 单价 金额
2 水稻 1000 0.48 480
3 玉米 0.39 780
…………………………………………………
在D2单元格里输入公式：
=C2*B2 ;
在C2单元格里输入公式：
=VLOOKUP(A2，Sheet1!A2:B100，2，FALSE)。
如用语言来表述，就是：在Sheet1表A2:B100区域的第一列查找Sheet2表单元格A2的值，查到后，返回这一行第2列的值。
这样，当Sheet2表A2单元格里输入的名称改变后，C2里的单价就会自动跟着变化。当然，如Sheet1中的单价值发生变化，Sheet2中相应的数值也会跟着变化。
VLOOKUP的应用 篇5
1 预备知识
1.1 Vlookup函数
功能:用于在表格或数组的首列查找指定的值,并由此返回表格数组当前行中其他列的值。
格式:Vlookup(lookup_value,table_array,col_index_num,ran ge_lookup)
参数说明:
lookup_value:表示需要在表格数组第一列中查找的数值。
table_array:表示两列或多列数据。
col_index_num:表示table_array中待返回的匹配值的列序号。
range_lookup:表示一逻辑值,指定函数Vlookup查找精确的匹配值还是近似匹配值。如果为TRUE或省略,则返回精确匹配值或近似匹配值。如果找不到精确匹配值,则返回小于lookup_value的最大数值;如果range_lookup为FALSE,函数Vlookup将查找精确匹配值,如果找不到,则返回错误值“#N/A!”。
注意事项:
如果range_lookup为TRUE或省略,则table_array的第一列中的数值必须按升序排列,否则,函数Vlookup不能返回正确的数值。
1.2 IF函数
功能:用于在公式中设置判断条件,然后根据判断结果TRUE或FALSE返回不同的值。
格式:IF(logical_test,value_if_true,value_if_false)
参数说明:
logical_test:表示要测试的值或表达式,计算结果为TRUE或FALSE。
value_if_true:表示当参数logical_test的结果为TRUE时返回的值。
value_if_false:表示当参数logical_test的结果为FALSE时返回的值。
注意事项:
(1)在Excel2003下,IF最多可以嵌套7层;而在Excel2007下,IF最多可以嵌套64层。
(2)参数的括号必须成对,前后对应。
2 准备工作
2.1 从财务部门获取“职工工资表”工作表
如图1所示。
2.2 设计“党费明细表”工作表
主要由以下字段组成:序号、所属党支部、姓名、岗位工资、薪级工资、职务补贴、地方补贴、地方补贴、考勤奖、合计、扣税、税后基数、应缴比例、应缴党费;之后序号、所属党支部、姓名,如图2所示。
3 具体实现
(1)利用Vlookup函数,填充“岗位工资”、“薪级工资”、“职务补贴”、“地方补贴”、“考勤奖”4个字段。
(2)利用SUM函数,填充“合计”字段。
在I6单元格中输入“=SUM(D6:H6)”
(3)利用IF函数,填充“扣税”字段。
1)以下是“速算税率表”,如表1所示。
2)在“速算税率表”中,“计算基数之和”指的是“合计”字段,计算公式是:
全月应纳税所得额=计算基数之和-3500.00
应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数
3)在J6单元格中输入。“=IF(I6<3500,0,IF(I6<5000,(I6-3500)*3%,IF(I6<8000,(I6-3500)*10%-105,IF(I6<12500,(I6-3500)*20%-555,IF(I6<38500,(I6-3500)*25%-1005,IF(I6<58500,(I6-3500)*30%-2755,IF(I6<83500,(I6-3500)*35%-5505,(I6-3500)*45%-13505)))))))”。
(4)利用公式填充“税后基数”字段。
在K6单元格输入“=I6-J6”
(5)利用IF函数,填充“应缴比例”字段,该字段的意思是“党费应缴比例”。
以下是“在职党员应缴党费比例表”,如表2所示。
首先选中L6单元格,在该单元格上单击鼠标的右键,在快捷菜单中选择“设置单元格格式”,在随后出现的对话框中,在“分类”下选择“百分比”,在“小数位数”处输入2,单击“确定”按钮。
在L6单元格中输入“=IF(K6>10000,2%,IF(K6>5000,1.5%,IF(K6>3000,1%,0.5%)”
(6)利用INT函数,填充“应缴党费”字段。
在M6单元格输入“=INT((K6*L6+0.09)*10)/10”
(7)至此为止,第一个人的党费计算已经结束,选定第D6到M6自动填充其余内容,如图3所示。
4 结语
通过上面的实例,相信大家已经深切地体会到Excel函数的强大功能。Vlookup函数适合对已有的各种基本数据加以整合,避免重复输入数据,整合的数据具有连结性,修改基本数据,整合表即会自动更新数据,非常有用。IF函数会使实际工作中繁琐、复杂的运算变得相当简便,达到事功倍的效果。在日常的生活和工作中,灵活地使用Excel函数会使从繁重的工作中解放出来,并大大地提高工作效率和准确率。
参考文献
[1]赛贝尔资讯.Excel函数应用500例[M].北京:清华大学出版社,2008.
[2]宋翔.Excel公式与函数大辞典[M].北京:人民邮电出版社,2010.
[3]姜雪茸.教学中的IF函数应用进阶[J].计算机光盘软件与应用,2012,13.
VLOOKUP的应用 篇6
一、关于VLOOKUP函数
VLOOKUP函数是Excel中几个最重要的函数之一, 用于在表格或数组的首列查找指定的值, 由此返回表格数组当前行中其它列的值。其语法是:
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
参数说明
Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。
Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。
col_index_num为table_array中待返回的匹配值的列序号。col_index_num为1时, 返回table_array第一列的数值, col_index_num为2时, 返回table_array第二列的数值, 以此类推。如果col_index_num小于1, 函数VLOOKUP返回错误值#VALUE!;如果col_index_num大于table_array的列数, 函数VLOOKUP返回错误值#REF!。
Range_lookup为一逻辑值, 指明函数VLOOKUP查找时是精确匹配, 还是近似匹配。如果为false或0, 则返回精确匹配, 如果找不到, 则返回错误值#N/A。如果range_lookup为TRUE或1, 函数VLOOKUP将查找近似匹配值, 也就是说, 如果找不到精确匹配值, 则返回小于lookup_value的最大数值。
二、关于数据透视表
数据透视表用来从Excel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表, 可以快速分类汇总和比较大量的数据, 并可以随时选择其中页、行和列中的不同元素, 以快速查看原数据的不同统计结果, 同时还可以随意显示和打印出你所感兴趣区域的明细数据。
三、数据透视表的用途
数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格, 能帮助用户分析、组织数据。例如, 计算平均数、标准差、建立列联表、计算百分比, 建立新的数据子集等。建好数据透视表后, 可以对数据透视表重新安排, 以便从不同的角度查看数据。数据透视表的名字来源于它具有“透视”表格的能力。从大量看似无关的数据中寻找背后的联系, 从而将纷繁的数据转化为有价值的信息, 以供研究和决策所用。
四、VLOOKUP数据透视表在报表中的应用
某医院为了了解临床路径开展情况, 要求提供2014年医保和非医保人群3个病种出院人次、总费用及平均费用, 并打印各病种的对应病例的相关信息清单。3种病种分别是, 剖宫产 (手术编码是74.100%, %为1或2) , 输卵管妊娠 (ICD编码是O00.100) 。子宫肌瘤 (ICD编码是D 25.900) 以上3个病种均为手术病例。
五、明确需求
分析应用需求, 可以知道出院人次、总费用及平均费用为所需查询的数据项。住院时期、是否医保用户、临床路径病种是条件规定的分类项。与之相关的数据库字段有:住院号、姓名、医疗付款方式、入院时间、出院日期、出院科别、住院天数、主要诊断ICD、主要诊断描述、手术编码、手术描述、费用总计、西药等。都是病案首页录入的内容。
六、对病案首页数据的必要处理加工
目前的病案管理系统都具有查询和报表这一管理信息系统所必备的两大功能。然而, 其提供的查询功能很难满足工作中所有的查询需求, 若要求软件开发公司重新进行设计, 不仅要增加额外的设计费用, 而且也不能及时满足得到我们所需要的查询结果。若采用其提供的导出EXCEL文件的方式, 利用其的数据透视表和Vlookup函数功能。就可以得到我们的结果。
为了实现多条件的查询, 我们需要在导出的EXCEL文件中, 增加五个数据列, 对数据进行加工处理。具体五列的首行字段名分别定义为:“住院时期”、“是否医保用户”、“测算病种”、“是否临床路径”、“临床路径名称”。
七、创建病案首页数据透视表
数据透视表分为四个区域 (见图2) , 分别为页字段区、行字段区、列字段区、数据项区。并具有“数据透视表字段列表”和“数据透视表工具栏”。
八、基于数据透视表的病案首页数据分析:
8.1移动各字段到数据透视表的对应区域。按照查询要求, 将“数据透视表字段列表”中的“住院时期”字段, 将其拖入到数据透视表“页字段区”;点击“数据透视表字段列表”中的“是否医保用户”拖入到“行字段区”, 依次点击“数据透视表字段列表”中的“是否临床路径”和“测算病种”, 先后拖放到数据透视表“列字段区”。
而对于数据透视表的“数据项区”, 我们可以将“住院号”和“费用总计”两个字段拖入到其中。其中“费用总计2”则表示第二次拖入“费用总计”。
8.2设定各数据字段的统计方法。数据透视表的“数据项区”的三个字段“住院号”、“费用总计”、“费用总计2”默认的统计方法是求和。我们必须将“住院号”和“费用总计2”的统计方法修改为所要求的“出院人次”和“平均费用”。
具体方法是鼠标右键单击“求和项:住院号”, 出现“字段设置”子表单, 在弹出的对话框“汇总方式”框中选择“数据计数”, 并将名字改为“出院人次”。结果如图4所示。同理“求和项1:费用总计”设置为“住院总费用”, “求和项2:费用总计2”设置为“平均费用”。
8.3显示“2014年医保和非医保人群非临床路径三个病种出院人次、费用总计和平均费用”的查询结果。在“是否临床路径”中选择“否”, 在“测算病种”中同时选择“剖宫产、输卵管妊娠、子宫肌瘤”, 则可以显示如图3所示查询结果:
若我们选择“是否临床路径”的“是”, 选择病种也为同时选择以上3种病种。
8.4病案首页的数据透视表可以用来完成平均住院日、平均术前住院日、病人的治愈好转率、产科的围产情况统计表、手术情况分析、窒息情况分析、住院病人的年龄性别构成等等, 并可完成相关的透视图。
摘要：目的:分析VLOOKUP与数据透视表相结合实现病案首页数据的查询的方法技术。方法:通过从医院的病案首页数据中使用VLOOKUP函数提取到的临床路径病种信息, 说明使用数据透视表的步骤。结果:VLOOKUP和数据透视表相结合完成的数据查询技术可以使我们对字段从新的角度进行设置, 转换我们的分析数据的角度, 改变传统的统计方式, 从而可以更加高效、快捷、准确地分析数据。结论:VLOOKUP函数及数据透视表具有高效低付出的性能, 值得我们学习和掌握。
关键词：VLOOKUP函数,数据透视表,病案,数据,查询
参考文献
[1]赖伏虎, 向其国, 罗洁樱, 等.数据透视表在病案首页数据中的应用[J].医药世界, 2006 (52) .
[2]冯骏.VLOOKUP命令在工资计算中的应用[J].中国管理信息化, 2010 (14) .
VLOOKUP的应用 篇7
1 Vlookup函数介绍
VLOOKUP函数
在表格或数值数组的首列查找指定的数值, 并由此返回表格或数组中该数值所在行中指定列处的数值。
VLOOKUP函数的完整语法如下:VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
即:VLOOKUP (查找值, 区域, 列序号, 逻辑值)
·“查找值”:为需要在数组第一列中查找的数值, 它可以是数值、引用或文字符串。
·“区域”:数组所在的区域, 如“B2:E10”, 也可以使用对区域或区域名称的引用, 例如数据库或数据清单。
·“列序号”:即希望区域 (数组) 中待返回的匹配值的列序号, 为1时, 返回第一列中的数值, 为2时, 返回第二列中的数值, 以此类推;若列序号小于1, 函数VLOOKUP返回错误值#VALUE!;如果大于区域的列数, 函数VLOOKUP返回错误值#REF!。
·“逻辑值”:为TRUE或FALSE。它指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略, 则返回近似匹配值, 也就是说, 如果找不到精确匹配值, 则返回小于“查找值”的最大数值;如果“逻辑值”为FALSE, 函数VLOOKUP将返回精确匹配值。如果找不到, 则返回错误值#N/A。如果“查找值”为文本时, “逻辑值”一般应为FALSE。
2 实例介绍VLOOKUP函数的用法
实例:劳资部门收到人事部门发来一份《××年职称晋升人员名单》的e xc el电子表格, 要求完成《××年职称晋升人员岗位工资变动审批花名册》 (图表1) 。
劳资部门一般都存有《员工工资基本信息表》 (图表2) , 以及《工资标准表》 (图表3) 。
要完成如图表1的工资变动花名册, 首先需要在员工工资基本信息表中查找到相关的人员基本情况填好, 然后需要根据新的职称级别, 查找到相应的工资金额。如果该单位较大, 员工人数较多的话, 在三张表格之间以纯手工查找相互关联信息的工作量会相当大, 此时如果我们应用好VLOOKUP函数的话, 则会显得相当简便:
⑴首先我们将《××年职称晋升人员名单》这一电子表格中需要调整工资人员的姓名和晋升后的职称和职级复制到表1的相应的A列、I列和J列;
⑵在表1的B6单元格填入公式“=VLOOKUP (A6, [员工工资基本信息表.xls]Sheet1!$A$4:$I$10, 2, FALSE) ”, 该公式的含意是:以A6单元格中的文本信息——姓名为目标查找值, 在《员工工资基本信息表》中的sheet1表中的数据区域A4:I10内进行匹配, 如果找到则返回数据区域内的第2列上的对应数值。应当注意的是数据区域$A$4:$I$10表达式中的“$”符号我们不能漏掉, 否则在下面表格的公式复制中会产生错误, 数据区域会发生变化。余下的C、D、E、F、G、H、G列中的公式我们只要把B6中公式的返回值“2”改成相应的“3、4、5、6、7、8”, 就能得到相应的数值列。由于我们的查找值是文本类型的数据, 所以相应的逻辑值我们使用“FALSE”。
⑶在表1的K6单元格中, 我们填入公式“=VLOOKUP (J6, [岗位工资标准.xls]Sheet1!$B$5:$C$17, 2, FALSE) ”, 该公式的含意是以J6单元格中的文本信息——岗位工资级别为目标查找值, 在《岗位工资标准表》中的SHEET1表中的数据区域B5:C17内进行匹配, 如果找到则返回数据区域内的第2列上的对应数值。
⑷选中表1的第六行中所有输入公式的单元格向下拖曳, 完成表格内所有需要进行公式复制的单元格。
到这里, 一张需要在三张不同表格之间进行关联数据查找的复杂表格我们就轻松完成了。通过该实例, 我们能够学习到VLOOKUP函数强大的数据查找功能, 在日常工作中灵活的运用该函数, 能使我们的工作变得简单、轻松、高效。
摘要：Vlookup函数是Excel中一个非常实用的函数, 在没有数据库系统支持, 不能使用数据库查询语句的情况下, 能解决一些常用的数据查找问题。文章通过实例分析了Vlookup函数的用法。
VLOOKUP的应用 篇8
1 规则介绍
VLOOKUP函数在Excel中称之为纵向查找函数 ,“V”即代表列的意思。由此可以很直观地了解到, VLOOKUP函数是按列查找的。它的工作原理就是以数据表的某一列数值为参照对象, 在设定的范围内查找与该对象, 即查找范围内的第一列一致的数值, 并返回该数值所在行中指定列的的数值。函数完整形式及语法规则如下:
函数括号中的4个参数是必不可少的, 下面分别对参数和函数的使用作必要的说明。
1.1 Lookup_value
这里表示需要在数据表某一列查找的数值, 它可以是数值、引用、文本字符串或参照地址。 对于这一参数有两点是需要注意的:
(1) 参照对象的单元格格式及内容与查找范围内第一列单元格格式及内容要一致, 否则无法返回需要查找的值; 若出现相同值, 则会显示查找中遇到的第一个对应值。尤其当参照对象为数值型时, 如果查找的单元格格式为字符型, 虽然显示的是一样, 但它们“基因”不同, 不是一家人, 当然就不进一家门。比如查找对象为3210, 要求在查找范围内查找并返回与3210对应位置的值。假设前者为数值型, 后者为字符型, 看起来都是3210, 但它们的单元格格式类型不同, 这样的话就会返回一个错误值, 表示为查找不到匹配的值。
(2) 在设置参照对象时 , 有时需要将它的值固定在一个单元格内, 那么在使用下拉方式或复制公式将函数添加到新的单元格中时, 就要用到“$”这个符号来起到固定的作用。比如只要以A1单元格为查找对象, 就可以把lookup_value的值写成“$A$1”, 这样的话无论你怎么下拉或复制, 函数将始终以A1的值作为参照来查找并返回对应的值。
1.2 Table_array
表示需要在其中查找数据的数据表范围, 可以是同一工作表或同一文件不同工作表中的一个区域, 也可以是不同文件的工作表中的一个区域。需要强调的是, lookup_value的值必须在table_array中处于第一列。此参数同样有两点需要注意的:
(1) 可以参照lookup_value参数, 不再赘述。
(2) 这其实是与lookup_value参数类似的小技巧。在设置查找范围参数时, 同样可以运用“$”来固定查找范围。比如, 要查找的范围是从D3到H500的区域, 那么, 可以把范围设置成$D$3:$H$500, 这就表示无论怎样下拉或复制公式到新的单元格中, 始终在D3到H500的范围内查找, 而不会因为下拉公式而顺序表示为D4:H501、D5:H502、D6:H503等等, 导致无法返回需要查找的值。
1.3 col_index_num
表示需要返回的匹配值在查找范围内的第几列, 它应该是一个正整数。比如此参数值为1, 则返回查找范围的第一列的值; 参数值为3, 则返回查找范围的第三列的值, 依次类推。一般此参数的值应在查找范围的列数之内, 小于1就没有实际意义, 函数会返回错误值 #VALUE!; 如果大于查找范围总的列数, 则函数会返回错误值 #REF!。
1.4 Range_lookup
这是一个逻辑值, 它可以是false或ture, 也可以写成0或1。false和0表示精确查找, 如果找不到就会返回错误值 #N/A ; ture和1表示模糊查找或近似查找 , 它的原理是先精确查找, 如果找不到精确匹配的值, 再去找接近的值, 再找不到就会返回错误值 #N/A。日常工作尤其涉及人员、工资等的操作中更多地会运用到精确查找。
2 VLOOKUP 函数运用举例
了解了VLOOKUP函数相关参数、语法规则和几个注意点或小技巧, 下面以工资发放表编制为例来看看实例操作。
建议为单位的整个体人员信息建立一个Excel文件, 比如姓名、工号、部门、 身份证号、银行账号、电话号码等。因 为人员信息涉及银行账号等重要信息, 所以最好把这个文件保护起来, 设置打开密码和修改密码, 防止误操作。
人员信息表如图1所示。
要编制一张包含姓名、银行账号、金额3个项目的工资发放表, 要在“人员信息”(图1) 的A2:D16区域中提取图2中对应人员的建行账号, 并显示在对应人员姓名的右侧单元格中。图2为VLOOKUP函数运用出错举例。
可以在单元格中直接输入公式或者运用“插入函数”功能编辑公式。 当前函数计算框显示公式为“=VLOOKUP (B2, 人员信息! A2:D16,4,FALSE)”, 对应前面讲到的函数语法规则, 该公式中对应参数及含义如下:
“B2”: 为第一个参数lookup_value, 是要查找的数值 , 这里就是要求查找的姓名。
“人员信息! A2:D16”: 为第二个参数table_array, 是要查找的范围, 表示要在“人员信息“工作表的A2?到D16区域内查找。
“4”: 为第3个参数col_index_num, 表示要返回的就是第二个参数指定范围内第4列的值, 即“建行账号”。
“FLASE”: 为第四个参数range_lookup, 这里务必要用精确查找, 因为姓名和账号应该是唯一对应的。
通过上述公式的展示和运算, 有两个错误信息 (阴影部分) 需要说明:
第一, 可以看到图2中的运算结果出现了错误提示, 这里就是由于未用“$”来固定查找范围的缘故。通过下拉公式, A2:D16会依次显示为A3:D17、A4:D18、A5:D19等, 显然, 当查找“金贝莎”的银行账号时, 其查找范围在“人员信息! A3:D17”, 而这个人不在这个查找范围内。
第二, 是一个隐性的错误。本例中要给E部门身份证号为 ******198205292155的“刘涛”发工资, 而查找结果返回的银行账号却是6111******320202, 从人员信息表中可以看到, 这是A部门身份证号为 ******197503010511的“刘涛”的银行账号。这是由于参照对象单元格内容与查找范围内第 一列单元格内容不一致导致的。在人员工资发放遇到同名同姓的情况应注意区分, 否则会导致误查账号, 把钱发到别人的账号上去, 造成不好的影响。
针对第二点人员重名的问题, 首先处理一下人员信息表的基础数据。如图3所示。
本例中用姓名加后缀的方式加以区分, 只有公式中B列数值格式及内容与查找范围内第一列即人员信息表中A列格式及内容一致, 查找结果才是对应的唯一值。如果不区分, 则查找结果会显示查找中从上到下遇到的第一个刘涛的银行账号, 就会出现图2中错发账号的情况。
查找结果唯一的正确的函数公式应表达为:=VLOOKUP (B2,人员信息! $A$2:$D$16,4,FALSE)
VLOOKUP函数运用正确的结果如图4所示。
另外值得注意的是, 公式单元格格式应为“常规”, 否则会出错。
3 结语
VLOOKUP的应用 篇9
1 问题的提出
每到学期结束学校各部门都需要统计教师的工作量, 笔者所在学校教师的工作量分两块, 一个是由教务部门统计的课堂教学工作量, 另一个是由其他部门如学工处、团委统计的非课堂教学工作量。有些教师既有课堂教学工作量, 又有非课堂教学工作量, 而有些教师只有课堂教学工作量或者只有非课堂教学工作量 (如图1和图2所示) , 现在需要将两张表合并成一张表。由于两张表中的教师相互有交叉, 直接复制粘贴行不通。此时如果使用VLOOKUP函数进行查找引用, 结合IF函数就可轻松完成任务。下面我们将介绍要用到的两个函数。
2 函数介绍
2.1 VLOOKUP函数
函数功能:VLOOKUP是按列查找, 最终返回该列所需查询列序所对应的值;Vlookup函数在Excel中广泛运用, 特别是在做报表、登记数据和查找数据等方面。
函数格式:VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
其中:
Lookup_value:需要在其中查找匹配数据的开始单元格
Table_array:两列或多列数据 (用绝对地址)
Col_index_num:为table_array中待返回的匹配值的列序号。值为1时, 返回table_array第一列中的数值;值为2时, 返回table_array第二列中的数值, 以此类推。
Range_lookup:为逻辑值, 指定希望VLOOKUP查找精确的匹配值还是近似匹配值 (如果为TRUE或省略, 则返回精确匹配值或近似匹配值。此时第一列中的值必须以升序排序;否则VLOOKUP可能无法返回正确的值 (可以事先对工作表按升序进行排序) ;如果为FALSE, VLOOKUP将只寻找精确匹配值。在此情况下, 第一列的值不需要排序。)
2.2 IF函数
函数功能:IF函数用于执行真假值判断后, 根据逻辑测试的真假值返回不同的结果, 因此If函数也称之为条件函数。它的应用很广泛, 可以使用函数IF对数值和公式进行条件检测。
函数格式:IF (logical_test, value_if_true, value_if_false) 。
其中:Logical_test是一个计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符。
Value_if_true是在logical_test为TRUE时返回的值, Value_if_true也可以是其他公式。Value_if_false logical_test为FALSE时返回的值。Value_if_false也可以是其他公式。
总之, IF函数的第一个参数的结果为真的话, 则将第二个参数Value_if_true的值作为函数的返回值, 如果为假则将第三个参数Value_if_false的值作为函数的返回值。IF函数可以嵌套七层, 用value_if_false及value_if_true参数可以构造复杂的检测条件。
3 解决方法
第一步:引用工作量统计表2中的数据填充工作量统计表1中相应单元格。为此, 在工作量统计表1的C2单元格输入以下内容:“=VLOOKUP (A2, 工作量统计表2!$A$2:$C$21, 3, FALSE) ”。含义是在工作量统计表2的A2:C21单元格区域中查找与A2值相同的那行所对应的第3列的值填充到工作量统计表1的C2单元格。然后按住填充柄向下拖拉到C16单元格释放鼠标。此时可以看到由于两个工作表中的人员并不全部相同, 工作量统计表1中部分单元格的值在工作量统计表2中找不到, 显示的内容为“#N/A”, 如图3所示。
第二步:引用工作量统计表1中的数据填充工作量统计表2的相应单元格。在工作量统计表2的D2单元格输入以下内容:“=VLOOKUP (A2, 工作量统计表1!$A$2:$D$16, 4, FALSE) ”。意思是在工作量统计表1的A2:D16单元格区域中查找与A2值相同的那行所对应的第4列的值填充到工作量统计表2的D2单元格。按住填充柄向下拖拉到D16单元格, 同样也会有部分单元格显示为“#N/A”。
第三步:将工作量统计表1的A1:D16单元格区域复制并选择性粘贴到一个新工作表中 (在选择性粘贴时选择“数值”) , 再将工作量统计表2的A2:D21单元格区域也选择性粘贴到该工作表, 使两张工作表的数据合并在一起。
第四步:在新工作表中选择C2:D36单元格区域, 使用查找替换功能将“#N/A”删除。
第五步:在新工作表中按工号作升序排序, 可以看到由于两张表中的人员有部分重复, 所以此时新工作表中有部分人员出现了两次, 因此要想办法删除重复记录。
第六步:在新工作表的E1和F1单元格分别输入“重复否1”和“重复否2”, 然后在E2单元格输入以下内容:“=IF (A3=A2, “是”, “否”) ”, 拖拉填充柄到E36。选择E2:E36单元格区域, 选择性粘贴到F列 (选择性粘贴时选择“数值”) , 如图4所示。
第七步:在新工作表中删除“重复否1”列, 然后按“重复否2”字段作升序排序, 可以看到从第29行开始到36行都是重复的记录 (如图5所示) , 删除这些记录, 再删除“重复否2”列, 并将工作重命名为“合并”, 至此问题就解决了。