《函数Lookup和VLookup的使用》由会员分享,可在线阅读,更多相关《函数Lookup和VLookup的使用(8页珍藏版)》请在金锄头文库上搜索。
1、函数Lookup和VLookup的使用一、利用LOOKUP函数进行详细信息查询1、Lookup函数的语法语法:Lookup(lookup_value,lookup_vector,result_vector)说明:(1)参数lookup_value表示要在查找集合中查找的值。(2)参数lookup_vector表示要查找的单行或单列区域。(3)参数result_vector表示查找成功后显示的结果所在的单行或单列集合。例如:如下图所示,在C4单元格中有公式“=LOOKUP(C3,环保协会成员基本信息表!A2:A41, 环保协会成员基本信息表!B2:B41)”,其意义如下:解说:公式“=LOOKU
2、P(C3,环保协会成员基本信息表!A2:A41, 环保协会成员基本信息表!B2:B41)”表示在环保协会成员基本信息表的A1:A41区域中查找C3单元格中指定的协会编号,查找成功后,把环保协会成员基本信息表的B1:B41区域中的值显示在C4单元格中,即把C3单元格中编号对应的姓名字段的值显示在C4单元格中。二、利用VLOOKUP函数进行详细信息查询1、VLookup函数的语法语法:VLookup(lookup_value,Table_array,Col_index_num,Rang_lookup)说明:(1)参数Lookup_value表示需要在数据表首列进行搜索的值。(2)参数Table_a
3、rray表示需要在其中搜索数据的信息表。(3)参数Col_index_num表示满足条件的单元格在数组区域中的列序号,首列序号为1。(4)参数Range_lookup表示指定在查找时是要求大致匹配还是精确匹配,即模糊查找还是精确查找;如果为false表示模糊匹配,为true或省略表示精确匹配。例如:如下图所示,在E4单元格中有公式“=VLOOKUP(C3,成员通讯录!$A$2:$F$41,3,TRUE)”,其意义如下:解说:公式“=VLOOKUP(C3,成员通讯录!$A$2:$F$41,3,TRUE)”表示需要在“成员通讯录”工作表中首列(即“协会编号”列)查找C3单元格的值(即相应的“协会编
4、号”值),根据这个C3单元格的值在“成员通讯录”工作表的A2:F41区域中第3列(即“联系电话”列)匹配相应的值(即与某一个具体的C3值匹配的一个联系电话值),后面的true表示精确匹配。【综合】使用Lookup和VLookup函数完成“环保协会成员信息详情表”,如下图所示。其中“姓名、班级、身份证号、性别、入会年份、职务”等字段通过Lookup函数来实现;“联系电话、QQ号、住址、电子邮箱”等字段通过VLookup函数来实现。操作步骤:在“环保协会成员信息详情表”中,通过协会编号可以查看该成员的所有信息。(1)创建“成员信息详情表”的基本结构。步骤如下:修改sheet3工作表名称为“环保协会
5、成员信息详情表”。在“环保协会成员信息详情表”工作表中选择B2:E9区域,然后单击右键,选择快捷菜单中的“设置单元格格式”命令,选择对话框中的“边框”选项卡。在“样式”列表中选择“粗实线型”,在“颜色”下拉窗口中选择“深蓝,文字2”,单击“外边框”按钮。再在“样式”列表中选择“虚线型”,“颜色”不变,单击“内部”按钮,单击“确定”按钮,“环保协会成员信息详情表”的边框设置完成。在“视图”选项卡的“显示”组中,取消选中“网格线”复选框。在B2:E9单元格区域中,依次输入“环保协会成员信息详情表”、“协会编号”、“姓名”、“班级”、“身份证号”、“性别”、“入会年份”、“时间”等。在D3:D7单元
6、格区域中,依次输入“职务”、“联系电话”、“QQ号”、“住址”、“电子邮箱”等。选择B2:E2单元格区域,单击“开始”选项卡中“对齐方式”组中的“合并后居中”命令按钮,并将合并后单元格中的内容的字体设置为“华文行楷”、16号字。将B3:B7区域设置为左对齐、“华文楷体”、12号字。将B3:E9区域的单元格行高设置为20磅;B、D两列列宽设置为15磅,C、E两列列宽设置为18磅。2、利用LOOKUP函数查询成员基本信息。在使用LOOKUP函数之前需要首先对“环保协会成员基本信息表”的“协会编号”列进行排序。可以使用“排序”对话框,也可以使用升序或降序按钮来实现。(升序或降序均可)通过使用“数据有
7、效性”功能实现在C3单元格中显示“环保协会成员基本信息表”中的“协会编号”列中的所有选项。选择C3单元格,在“数据”选项卡的“数据工具”组中单击“数据有效性”按钮,在下拉菜单中选择“数据有效性”命令,打开对话框。在“允许”下拉菜单中选择“序列”,在“来源”文本框中,通过鼠标单击右侧的拾取按钮选择“环保协会成员基本信息表”内“协会编号“中A2:A41数据作为序列的数据来源,单击“确定”按钮完成设置,此时在C3单元格中会显示表中的所有协会编号。通过使用LOOKUP函数实现信息查询。选择C4单元(“姓名”字段),单击编辑栏前面的“插入函数”按钮,打开“插入函数”对话框。在对话框中找到LOOKUP函数
8、,单击“确定”按钮,在“选定参数”对话框中单击“确定”按钮,弹出“函数参数”对话框。在对话框中“lookup_value”后用鼠标通过拾取按钮选择C3单元格。在“lookup_vector”后用鼠标通过拾取按钮选择“环保协会成员基本信息表”中的A2:A41区域。在“result_vector”后选择“环保协会成员基本信息表”中的B2:B41区域,单击“确定”按钮。设置完成后,C4单元格中的公式如下:=LOOKUP(C3,环保协会成员基本信息表!A2:A41,环保协会成员基本信息表!B2:B41)使用相同的方法完成“班级”、“身份证号”、“性别”、“入会年份”、“职务”等字段的设置。3、使用VL
9、OOKUP函数完成“联系电话”、“QQ号”、“住址”、“电子邮箱”等字段的设置。选择E4单元格,单击“公式”中的“插入函数”按钮,打开“插入函数”对话框。选择“VLOOKUP”函数,在“函数参数”对话框中分别设置其四个参数的值:Lookup_value参数设置为C3,表示要查找的是某个协会编号。Table_array参数设置为“环保协会成员通讯录”中的$A$2:$F$41区域,表示要查找的数据区域。Col_index_num参数设置为3,表示要查找的数据在“环保协会成员通讯录”中第3列“联系电话”列中。Range_lookup参数设置为true,表示精确匹配。设置完成后,E4单元格中的公式为:
10、=VLOOKUP(C3,成员通讯录!$A$2:$F$41,3,TRUE)三、SUMIFS函数语法:SUMIFS(sum_range,criteria_range1,criteria1, criteria_range2,criteria2)功能:用于计算单元格区域或数组中符合多个指定条件的数字的总和。参数说明:(1)sum_range(必选参数)表示要求和的单元格区域。(2)criteria_range1(必选参数)表示要作为条件进行判断的第1个单元格区域。(3)criteria1(必选)表示要进行判断的第1个条件,形式可以为数文本或表达式。使用注意事项:(1)如果在SUMIFS函数中设置了多个
11、条件,那么只对参数sum_range中同时满足所有条件的单元格进行求和。(2)可以在参数criteria中使用通配符(*和?)。(3)参数sum_range中的单元格如果包含true则按1计算;如果包含false则按0计算。(4)与SUMIF函数不同的是,SUMIFS函数中的求和区域sum_range与条件区域criteria_range的大小和形状必须一致,否则公式出错。(5)SUMIFS是OFFICE2007/2010以上版本新增函数。【综合】在如图所示的工资表中,计算基本工资在2000(含)到8000(含)之间的员工基本工资的总和。解答:=SUMIFS(D2:D11,D2:D11,=2000,D2:D11,=8000)或:=SUMIF(D2:D11,=8000,D2:D11)-SUMIF(D2:D11,2000,D2:D11)其中,sumif()中第三个参数可以省略,即写成如下形式:=SUMIF(D2:D11,=8000)-SUMIF(D2:D11,2000)