Excel中VLOOKUP和MATCH函数的应用
2019-03-15吴莹
吴莹
摘要:分析和处理Excel工作表中的数据,离不开公式和函数,函数是Excel预定义的内置公式,可以进行数学、文本、逻辑的运算或者查找工作表的信息,利用Excel函数应用技巧可以提高大家使用电子表格的效率、让你的工作更快、更高效。该文通过对查找函数的使用分析,从而理解查找函数具体的应用。
关键词:EXCEL;函数;VLOOKUP;MATCH;数据查询
中图分类号:TP3 文献标识码:A 文章编号:1009-3044(2019)02-0276-02
Excel是功能强大、技术先进、使用方便灵活的电子表格软件,可以用来制作电子表格,可以完成表格中复杂的算术运算,也可以进行数据的分析和预测,并且具有强大的制作图表功能和网络功能等。在我们日常生活或工作当中,会需要对数据进行查询调用。
1 Excel高級应用中常见的查找函数
1.1 VLOOKUP函数
VLOOKUP函数是Excel中的一个纵向查找函数,功能是搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。它的语法结构为:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。Lookup_value(数值、引用或文本字符串)需要在数据表首列进行搜索的值。Table_array为需要在其中搜索数据的数据表,可以是对区域或区域名称的引用。col_index_num为满足条件的单元格在数组区域table_array 中的列序号,首列序号为1。Range_lookup为指定在查找时是要求精确匹配,还是大致匹配。如果为FALSE,大致匹配。如果为TRUE或忽略,精确匹配。
1.2 MATCH 函数
MATCH函数是Excel中主要查找函数之一,最终返回符合特定值特定顺序的项在数组中的相对位置。它的语法结构为:MATCH(lookup_value, lookup_array, match_type),lookup_value:在数组中所要查找匹配的值,可以是数值、文本或逻辑值,或对数字、文本或逻辑值的单元格引用。lookup_array为含有要查找的值的连续单元格区域,区域是一个数组,或是对某数组的引用。match_type是表示查询的指定方式,用数字-1、0或者1表示,match_type 指定了Microsoft Excel将lookup_value与lookup_array 中数值进行匹配的方式。
2 如何应用VLOOKUP函数和MATCH函数查询数据
2.1应用实例一(VLOOKUP函数):根据学号查询姓名以及各科成绩
已知一学生成绩表,成绩表里记录了学生的学号、姓名和语文、数学和英语的成绩,现根据学号(G列)查询姓名H列以及语文(I列)、数学(J列)、英语(K列)的成绩,见图1。
(1)首先根据第一个学号1001查询出姓名,选中H2单元格,键入VLOOKUP函数,点击插入函数命令进入VLOOKUP函数框,提取参数内容,见图2。
(2)其次查找1001学号的语文成绩,选中I2单元格,这里只需将查找姓名里的VLOOKUP函数表达式中第三个参数col_index_num改为3即可,见图3,同样的方法在VLOOKUP函数表达式中第三个参数col_index_num改为4、5即可查询出相应的数学、英语成绩。
(3)利用VLOOKUP函数查询出学号1001的姓名以及三科的成绩之后,我们可以利用Excel数据填充的方法,把余下的数据填充完成即可查询出其余学号的姓名以及各科的成绩。如果图1中G列中的学号是杂乱排列的,用上面的方法查询出第一个学号的姓名以及各科的成绩,利用数据填充的方式填充完成按回车键之后,发现单元格里有返回错误值 #N/A,这时只需要把VLOOKUP函数表达式中第二个参数查找的区域设置成绝对引用即可,见图4。
2.2 应用实例二(VLOOKUP函数+MATCH函数):根据姓名和科目查询分数
实例一中描述的是VLOOKUP函数具体应用的方法以及技巧,查找的条件是单列条件的查找,在我们生活或工作当中有时候会碰到引用多列数据进行查找,那么我们该如何去实现呢?在Excel中,多个函数的嵌套使用可以弥补单个函数使用的局限性,接下来我们将讲述如何利用VLOOKUP函数和MATCH函数嵌套以高效引用多列数据。我们从上述MATCH函数的定义来看MATCH函数的返回值为单元格内容所在范围内的位置,刚好与VLOOKUP函数的第三个参数相同,因此我们可以使用MATCH函数作为VLOOKUP的第三个参数值。
已知一学生成绩表,工作表中A列为学生的姓名,B列为语文成绩,C列为数学成绩,D列为英语成绩,见图5,想要根据学生的姓名和科目查询相应的成绩,毋庸置疑,我们需要用VLOOKUP函数和MATCH函数嵌套来完成此查询工作。
(1)首先如图5所示在成绩表右侧创建姓名(F列),选中F2单元格,在其设置数据有效性,选项为成绩表中所有的姓名,点击【数据】-【数据有效性】,在数据有效性对话框里面“允许”选择“序列”,点击“来源”底下的文本框,选择成绩表里所有的姓名(A2:A11),见图6。
(2)创建科目(G列),选中G2单元格,同样在其设置数据有效性,选项为“语文、数学、英语。设置的方法同上,数据有效性对话框中“来源”底下编辑框中设置为=$B$1:$D$1。
(3)创建成绩(H列),选中H2单元格,键入VLOOKUP函数,点击插入函数命令进入VLOOKUP函数框,见图7。
(4)此时用鼠标点击在编辑栏的MATCH()处,切换到函数MATCH函数参数框,输入参数信息,见图8。
(5)完成MATCH函数的参数输入信息后,用鼠标点击编辑栏的VLOOKUP函数处,切换到VLOOKUP函数参数框,并完成参数输入信息,见图9,点击确定即可按姓名和科目查询相应的分数。
3 结论
Excel作为数据处理系统和报表制作的工具,经常会应用查找函数在数据量很大的工作表中查询各种信息,常用的查找函数除了本文中涉及的Vlookup、Match,还有hlookup、lookup、index、offset等,用户可以通过函数的帮助查看具体的功能,这些函数往往不是单独使用,可以与其他函数和Excel中的一些功能进行配合。
参考文献:
[1]VLOOKUP函数.百度百科. https://baike.baidu.com/item/VLOOKUP函数
[2]Excel函数 VLOOKUP+MATCH使用方法.百度经验. https://jingyan.baidu.com/article/0f5fb0993c6d1d6d8334eaa5.html