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

举报
华章计算机 发表于 2020/02/09 21:25:38 2020/02/09
【摘要】 本节书摘来自华章计算机《Excel公式、函数与图表案例实战从入门到精通 》 一书中第3章,第3.1.2节,作者是恒盛杰资讯 。

3.1.2 制作员工资料查询表

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

步骤01

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

步骤02

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

image.png

步骤03

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

步骤04

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

image.png

步骤05

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

步骤06

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

image.png

步骤07

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

步骤08

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

image.png

步骤09

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

步骤10

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

image.png

步骤11

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

步骤12

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

image.png

重点函数介绍: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或忽略,则为精确匹配。

image.png

步骤13

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

步骤14

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

image.png

步骤15

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

步骤16

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

image.png


步骤17

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

步骤18

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

image.png

步骤19

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

步骤20

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

image.png

重点函数介绍: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,依次类推。

image.png

重点函数介绍: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函数结合使用,返回数据区域中相对应的值。

image.png

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。