二维码 购物车
部落窝在线教育欢迎您!

做一张极其方便的员工信息查询表

 

作者:赋春风来源:部落窝教育发布时间:2021-09-14 10:26:37点击:176

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

Hello各位小伙伴们,Excel中的查找、引用函数公式,不只是简单的查找数据,而是能让数据具有更强的可移植性,也能让公式具有更强大的数据处理能力。对于HR来说,公司员工信息太多,查找起来不方便?今天我们就用常用的几个查找引用函数做一个员工信息查询表。下面这个员工信息查询表如果你能学会,那么你对于查找与引用函数的学习就进入了高级层次了。

 

1、案例来源

 

我们公司拥有很多员工,为方便公司管理,老板让我制作一个表格,可以选择根据员工的工号、姓名、手机号三个具有身份属性的字段,找到对应的员工的所有信息。

 

 

2、案例分析

 

首先,自主选择查询的目标字段(工号、姓名、手机号)。这就要求我们要首先进行数据有效性设置。数据有效性是在工作表中输入数据时,对于符合条件的数据,允许输入,对不符合条件的数据禁止输入。

 

然后再根据输入的值查找并返回对应的内容。在表格中查找目标字段,需要公式做多次查找和判断。可以使用INDEX()函数返回目标所在行的内容,使用MATCH()函数取得行号,使用CHOOSE()函数和MATCH()函数嵌套来获得外层MATCH()函数的查找的区域,最终得到需要的结果。

 

3、具体步骤

 

① 建立基础数据表。合并A2:G2单元格,在单元格中输入表格标题“员工资料查询表”;合并A3:B3单元格,在单元格中输入“查询条件:”;合并C3:E3单元格,在F3单元格中输入“查询内容”。

 

A4:G4单元格分别输入“工号”、“姓名”、“性别”、“手机号”、“入职时间”、“籍贯”、“工资”,并美化单元格。

 

 

② 选中查询条件”后面的合并单元格,单击【数据】选项卡“数据工具”选项组中的“数据验证”按钮,弹出“数据验证”对话框。

 

在 “允许”的下拉列表中选择“序列”。在“来源”,文本框中输入“工号,姓名,手机号”,单击“确定”按钮。

 

返回工作表中,单击设置了有效验证条件的单元格,在单元格右侧会出现一个下拉按钮,单击该按钮可选择指定序列内容。注意,在输入序列内容时,以半角的逗号隔开不同的内容项。

 

 

 

③ 选择A5:G5单元格区域,输入公式“=IFERROR(INDEX(员工档案!$A$2:$G$85,MATCH(资料查询!$G$3,CHOOSE(MATCH(资料查询!$C$3,{"工号","姓名","手机号"},0),员工档案!$A:$A,员工档案!$B:$B,员工档案!$D:$D),0)-1,),"-")”摁Ctrl+Shift+Enter键确认输入。

 

④ 选择“查询条件”后面的合并单元格,单击右侧的下拉按钮,选择三个选项中的任意一个,如选择“姓名”选项。选择G3单元格,输入任意要查询的姓名,摁Enter键确认输入。

 

 

4、公式说明


A5:G5单元格区域的公式很复杂,咱们来详细说明下。总体来说,公式“=IFERROR(INDEX(员工档案!$A$2:$G$85,MATCH(资料查询!$G$3,CHOOSE(MATCH(资料查询!$C$3,{"工号","姓名","手机号"},0),员工档案!$A:$A,员工档案!$B:$B,员工档案!$D:$D),0)-1,),"-")”的计算过程可以分为三个步骤。

 

首先,通过MATCH()函数查找查询条件后面合并单元格中的值所在数组{"工号","姓名","手机号"}中的位置序号,并根据该序号利用CHOOSE()函数返回3个不同的单元格区域。

 

其次,通过外层的MATCH()函数,在返回的单元格区域中查找G3单元格的值,并返回其在单元格区域中的序号上一行,因为第一行为标题,并将此序号作为INDEX()函数的参数,借以返回该行号对应的整行数据。

 

最后通过IFERROR()函数检测公式是否产生错误,如果是,则返回文本“-”,表示未找到符合条件的记录。否则返回查找的内容。

 

未找到符合条件的记录的原因可能有两点:(1)用户输入的内容在记录中不存在。(2)用户输入的内容不在C3单元格选择的列。

 

 

 

5、拓展练习

由于用户必须要选择正确的查询字段,并输入该字段对应的内容才可以查询到正确的结果,现在老板想要输入3个字段中的任意内容,无须手动选择要查询的字段即可获得正确的值,我们也可以通IFERROR()函数的嵌套来完成。

 

 

在这之前可以先定义一些单元格名称,以方便公式使用。大家可能不太了解单元格的名称定义应该是什么?实际上Excel表格中,每一个单元格都具有一个默认的名字,命名规则是列标和行标,比如我们所说的:A1,表示的就是第一行第一列的单元格。

 

我们可以为我们的单元格重新命名,甚至是为单元格区域进行名称的定义,可以使用定义的名称进行导航和代替公式中的单元格地址,使工作表更容易理解和更新。

 

在“员工档案”工作表中同时选择ABD这三列,在【公式】选项卡中单击“根据所选内容创建”按钮,在打开的对话框中选中“首行”复选框,单击“确定”按钮,创建3个单元格名称。

 

 

切换到“信息查询”工作表中,选择A5:G5单元格区域,在编辑栏输入公式“=IFERROR(INDEX(员工档案!$A$2:$G$85,IFERROR(MATCH($G$3,工号,0),IFERROR(MATCH($G$3,姓名,0),(MATCH($G$3,手机号,0)))),),"-"),摁Ctrl+Shift+Enter键确认输入。在G3单元格中输入工号、姓名、手机号的任意内容,即可得到所需要的结果。

 

 

OK,今天春风通过工作中遇到的实际案例,为小伙伴们介绍了一张信息查询表,同时也接触了MATCH()函数、CHOOSE()函数、INDEX()函数,我们下期再见~

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者赋春风;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。