【原】秒杀VLOOKUP！华为HR用这个函数公式做《员工信息查询表》，任正非看了都得鼓掌

Excel教程平台 2021-09-14

展开全文

★

编按

★

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

案例来源

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

案例分析

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

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

扫码入群，下载Excel图表练习文件，同步操作

具体步骤

① 建立基础数据表。合并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键确认输入。

公式说明

在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单元格选择的列。

拓展练习

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

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

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

在“员工档案”工作表中同时选择A、B和D这三列，在【公式】选项卡中单击“根据所选内容创建”按钮，在打开的对话框中选中“首行”复选框，单击“确定”按钮，创建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单元格中输入工号、姓名、手机号的任意内容，即可得到所需要的结果。