《Excel公式、函数与图表案例实战从入门到精通 》 —3.1.2　制作员工资料查询表

3.1.2　制作员工资料查询表

下面介绍如何使用数据验证功能引用单元格内容，以及使用公式设置员工资料查询条件。通过制作完成的查询表，可以很方便地查询员工的相关资料信息。

步骤01

　创建员工资料查询表。新建工作表“员工资料查询表”，输入员工资料查询表相关项目内容，设置好表格格式，如下图所示。

步骤02

　定义名称。在工作表“员工资料表”中选中单元格区域A3:A22，在“公式”选项卡下的“定义的名称”组中单击“定义名称”按钮，如下图所示。

步骤03

　新建名称。弹出“新建名称”对话框，在“名称”文本框中输入“所属部门”，单击“确定”按钮，如下图所示。

步骤04

　新建名称。定义单元格区域B3:B22的名称为“员工编号”，单击“确定”按钮，如下图所示。

步骤05

　新建名称。定义单元格区域C3:C22的名称为“姓名”，单击“确定”按钮，如下图所示，完成名称的定义。

步骤06

　管理名称。切换到工作表“员工资料查询表”，在“公式”选项卡下单击“名称管理器”按钮，如下图所示。

步骤07

　查看已有名称。弹出“名称管理器”对话框，可以看到已定义的名称内容，如下图所示。查看后单击“关闭”按钮，返回工作表。

步骤08

　启用数据验证功能。在工作表“员工资料查询表”中选中单元格B2，在“数据”选项卡下的“数据工具”组中单击“数据验证”按钮，如下图所示。

步骤09

　设置数据验证条件。弹出“数据验证”对话框，在“设置”选项卡下的“验证条件”选项组中设置“允许”为“序列”，在“来源”文本框中输入“=员工编号”，如下图所示。设置完成后，单击“确定”按钮。

步骤10

　使用数据验证。返回工作表，单击单元格B2右侧的下三角按钮，在展开的列表中选择需要查询的员工编号，如“B001”，如下图所示。

步骤11

　插入函数。选中单元格D2，在“公式”选项卡下的“函数库”组中单击“查找与引用”按钮，在展开的列表中单击VLOOKUP选项，如下图所示。

步骤12

　设置函数参数。弹出“函数参数”对话框，分别设置函数的参数，如下图所示。设置完成后，单击“确定”按钮。

重点函数介绍：VLOOKUP函数

VLOOKUP函数用于搜索数据表首列满足条件的元素，确定该元素在区域中的行序号，再返回该行中指定列处单元格的值。其语法结构为VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。参数lookup_value指定需要在数据表首列进行搜索的值，可以是数值、引用或字符串；参数table_array指定需要在其中搜索数据的数据表，可以是对区域或区域名称的引用；参数col_index_num指定要返回的单元格在table_array中的列序号，首列序号为1；参数range_lookup指定在查找时要求精确匹配还是大致匹配，如果为FALSE，则为大致匹配，如果为TRUE或忽略，则为精确匹配。

步骤13

　返回员工姓名。返回工作表，在单元格D2中可看到已返回员工编号相应的员工姓名，并可在编辑栏中查看公式内容，如下图所示。

步骤14

　引用员工性别。在单元格D3中输入公式“=VLOOKUP(B2,员工资料表!B3:G22, 3)”，按下【Enter】键，计算公式结果，如下图所示。

步骤15

　引用出生日期。在单元格D4中输入公式“=VLOOKUP(B2,员工资料表!B3:G22, 5)”，按下【Enter】键，计算公式结果，并将其设置为短日期格式，如下图所示。

步骤16

　引用固定电话。在单元格D5中输入公式“=VLOOKUP(B2,员工资料表!B3:G22, 6)”，按下【Enter】键，计算公式结果，如下图所示。

步骤17

　引用职务。在单元格B4中输入公式“=VLOOKUP(B2,员工资料表!B3:G22,4)”，按下【Enter】键，计算公式结果，如下左图所示。

步骤18

　引用所属部门。在单元格B3中输入公式“=INDEX(所属部门,MATCH(员工资料查询表!B2,员工编号,0))”，按下【Enter】键，计算公式结果，如下右图所示。

步骤19

　选择编号。此时可以使用数据验证功能选择需要查询的员工编号，单击单元格B2右侧的下三角按钮，在展开的列表中单击C001选项，如下图所示。

步骤20

　显示查询结果。工作表中显示数据查询结果，即员工编号为C001的员工所对应的员工姓名、出生日期等相关信息，如下图所示。

重点函数介绍：INDEX函数

INDEX函数有两种形式：数组型和向量型。

数组型INDEX函数的功能是返回列表或数组中的元素值。其语法结构为INDEX(array,row_num,column_num)。参数array指定单元格区域或数组常量；参数row_num指定数组中要返回的行序号；参数column_num指定数组中要返回的列序号。

向量型INDEX函数的功能是在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。其语法结构为INDEX(reference,row_num,column_num,area_num)。参数reference指定对一个或多个单元格区域的引用；参数row_num指定目标单元格在引用区域中的行序号；参数column_

num指定目标单元格在引用区域中的列序号；参数area_num指定所要返回的行列交叉点位于引用区域组中的第几个区域，第一个区域为1，第二个区域为2，依次类推。

重点函数介绍：MATCH函数

MATCH函数用于返回符合特定值顺序的项在数组中的相对位置。其语法结构为MATCH(lookup_value,lookup_array,match_type)。参数lookup_value指定在数组中所要查找匹配的值，可以是数值、文本或逻辑值；参数lookup_array指定含有要查找值的连续单元格、一个数组或对某个数组的引用；参数match_type指定-1、0或1，即指定将lookup_value与lookup_array中的数值进行匹配的方式。

本实例中在计算员工所属部门时不使用VLOOKUP函数，是因为该函数只能对指定数据表首列数据进行查找。使用MATCH函数则是返回与指定值匹配的数组中元素的相应位置，可与INDEX函数结合使用，返回数据区域中相对应的值。