人事管理人员避免不了的经常与员工档案打交道，查询变更等都是家常便饭，如果没有较好的管理方法，那将费时费力……今天我们来学习利用“自定义名称”和函数公式组合技巧来实现员工照片等信息的动态查询技巧。

一、效果展示。

从实际效果中我们可以看出，只需要选择相应的员工工号，即可查询其它相关信息，包括照片信息……该如何去实现了？

二、实现方法。

1、准备员工信息表。

在其中的一个工作簿中准备带照片的员工信息表，表格内容可以根据实际情况自定义。

2、获取【工号】下拉列表框内容。

方法：

1、选中目标单元格。

2、【数据】-【数据验证】，选取【允许】下的【序列】，单击【来源】右侧的箭头，单击【员工信息表】并选取【工号】列的内容。再次单击箭头，并【确定】。（或者在来源中直接输入公式：=员工信息表!$B$3:$B$9即可）。

3、获取【照片】等内容。

方法：

1、单击目标单元格。

2、【公式】-【定义名称】，在【名称】对话框中输入名称，例如【照片】。

3、在【引用位置】对话框中输入公式：=INDEX(员工信息表!$G:$G,MATCH(员工档案!$B$2,员工信息表!$B:$B,))并【确定】。

4、打开【员工信息表】工作簿，复制任意一张照片，并粘贴到【员工档案】工作簿的目标单元格中，调整大小等。（文本内容可省略此步骤）

5、在地址栏中输入公式：=照片（等号后面的名称必须和第2步名称对话框中输入的内容一致）。

6、单击下拉列表查询内容。

重点解读：

公式：=INDEX(员工信息表!$G:$G,MATCH(员工档案!$B$2,员工信息表!$B:$B,))是实现此功能的重要内容。INDEX函数是根据位置筛选相应的内容，而MATCH函数是定位函数。首先利用MATCH函数来定位“员工档案!$B$2”内容在“员工信息表”中的位置，然后利用INDEX提取其内容并予以显示。

点击关注上方蓝字保研班↑↑↑

上次跟大家讲了，如何用VLOOKUP函数建立《员工档案管理表》。VLOOKUP函数虽然很强大，但其基本用法中只能作正向查找，而不能反向查找，即根据前列查找后列的内容，而不能根据后列查找前列内容。

如我用VLOOKUP函数，可以根据工号来查姓名，但是反过来不行。但如果我用INDEX+MATCH函数组合，可实现双向查找，可顺可逆。

接下来，我们还是以《员工档案管理表》举例，如何根据姓名来查员工信息。

1设置数据有效性

关键步骤：

数据―数据有效性―允许（序列）―来源（框选数据源）

2INDEX函数

INDEX翻译成中文的意思是“索引”，INDEX函数是指返回指定区域、指定位置的数值。

INDEX的语法：

INDEX(array,row_num,column_num)，指返回数组中指定的单元格或单元格数组的数值。

INDEX(reference,row_num,column_num,area_num)，指返回引用中指定单元格或单元格区域的引用。

简单点说就是 INDEX(区域，第几行，第几列)，返回区域第几行第几列的数值。

那么，如何用INDEX函数来找出李四的身份证号呢？

关键步骤：

输入INDEX―框选查找区域―李四的身份证号在第3行第3列

做到这一步，很多人会直接往右边复制公式，结果看似和源数据一样，但是换个姓名后面的信息并没有随之变化，所以这样是不可取的。

3COLUMN函数

身份证号是在源数据的第3列，出生日期是在源数据的第4列，以此类推……

这个时候，我们可以用COLUMN函数来表达列标，即COLUMN()是指查看所选择的某一个单元格所在第几列,即它是第几列。

4MATCH函数

COLUMN函数解决了列标的问题，行号我们要用到MATCH函数来表示。

MATCH函数的意思是 返回目标值在查找区域中的位置， 语法为：

MATCH（lookup_value,lookuparray,match-type）

l ookup_value： 表示查询的指定内容；

lookuparray：表示查询的指定区域；

match-type：表示查询的指定方式，用数字-1、0或者1。

match_type=0（精确查找）

查找精确等于lookup_value的第一个数值，lookup_array按任意顺序排列。一般只使用精确查找。

match_type=1

查找小于或等于lookup_value（目标值）的最大数值在lookup_array（查找区域）中的位置，lookup_array必须按升序排列。

match_type=-1

查找大于或等于lookup_value（目标值）的最小数值在lookup_array（查找区域）中的位置，lookup_array必须按降序排列。

如果我们要查找李四在源数据第几行，可以怎样表示？

输入=INDEX―框选李四（需查询内容）―框选源数据姓名列（查询的区域）―0-（精确匹配）

5绝对引用

（错误做法）

很多人以为这样就完了，直接向右复制发现公式不适用，那是因为我们没有采取绝对引用。关于绝对引用、相对引用和混合引用这三者的区别，我们举例来说明：

1、相对引用,复制公式时地址跟着发生变化,如C1单元格有公式：=A1+B1

当将公式复制到C2单元格时变为：=A2+B2

当将公式复制到D1单元格时变为：=B1+C1

2、绝对引用,复制公式时地址不会跟着发生变化,如C1单元格有公式：=$A$1+$B$1

当将公式复制到C2单元格时仍为：=$A$1+$B$1

当将公式复制到D1单元格时仍为：=$A$1+$B$1

3、混合引用,复制公式时地址的部分内容跟着发生变化,如C1单元格有公式：=$A1+B$1

当将公式复制到C2单元格时变为：=$A2+B$1

当将公式复制到D1单元格时变为：=$A1+C$1

规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化；没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化；混合引用时部分地址发生变化。注意：工作薄和工作表都是绝对引用，没有相对引用。

具体操作很简单，选中后按一下F4即可切换到绝对引用。（其他几种引用木模式切换自己可以多按几下F4试试）

6最后效果

图中可以看到，我们直接搜姓名，相关的员工信息就能显示出来。无论是VLOOKUP，还是INDEX+MATACH函数组合，合适自己需求的才是最好的。技多不压身，多学也无妨！

-End-

关注保研班请长按

保险极客旗下 HR 服务平台

「保险极客：员工保险 放心之选」

投稿邮箱

zhipeng.cao@insgeek

Excel实用工具往期回顾

【第十期】 数据校对怕什么？三个Excel技巧让你告别加班！

【第九期】 面试官说，不会VlookUp数据筛选，直接PASS掉！

【第八期】不会用Excel做数据筛选，老板叼的你没话说！

【第七期】别人通宵做的员工档案管理表，我两个小时就搞定

【第六期】Excel动态图表让数据动起来，还有这种操作？

应用场景：

前两天分享的教程中，介绍了HR创建带照片的员工档案信息表的方法，有小伙伴在问，有没有办法创建一个查询表，随时动态查询员工的基本信息和照片呢？艳子今天就分享一个Excel动态查询员工基本信息和照片的方法。

效果一：通过下拉列表查询（适用于人数或类别少的情况）

效果二：输入姓名或类别查询（适用于人数和类别较多的情况）

具体操作步骤：

1．建立带图片的员工基本情况表。可用往期教程分享的方法创建（《EXCEL插入3000名员工一一对应的照片，原来只要2钟？》可关注百家号“EXCEL学习微课堂”查看）

2、设计美化查询表。

3．在D3单元格建立下拉列表（效果二不用做此步）。

选D3单元格→数据→数据验证→序列→=员工基本情况表!$A$2:$A$8

4. 在F3单元格输入公式：=VLOOKUP($D$3,员工基本情况表!$A:$G,MATCH(E3,员工基本情况表!$A$1:$G$1,0),0)，因为E3用的相对引用，其他的是绝对引用，所以可以直接复制公式到D4D5F4F5单元格）

公式说明：

①MATCH (目标值，查找区域，精确匹配0)，通过MATCH函数来查找E3单元格“员工编号”在员工基本情况表!$A$1:$G$1的位置，即第几列。

②VLOOKUP（找谁，在哪找，返回第几列，0精确匹配），通过$D$3单元格的姓名查询相应的信息。

5. 定义名称“图片”。公式→定义名称→图片→引用位置中录入公式=INDIRECT("员工基本情况表!H"&MATCH(查询表!$D$3,员工基本情况表!$A:$A,0))

公式说明：

①"员工基本情况表!H"&MATCH(查询表!$D$3,员工基本情况表!$A:$A,0)：即用"员工基本情况表!H"连接MATCH函数查询D3单元格姓名所在的行号，结果为员工基本情况表的某个单元格，比如D3为美女1时，公式返回的是员工基本情况表的H3单元格。

②INDIRECT函数是创建对命名区域的引用，如果查询出来的是H3，即为引用H3单元格。

③引用图片时需用定义名称的方法。

④比如D3为美女1时，通俗地讲就是G3单元格=员工基本情况表的H3单元格。

6. 粘贴一张图片到G3单元格，调整图片大小与单元格大小相同，选中图片，在编辑栏输入=图片，回车就大功能告成了，点选姓名检查下。

扩展应用：查询仓库物资、查询设备情况、查询产品信息等。

如果我的分享对您有帮助，欢迎点赞、收藏、评论、转发，您的支持是我坚持的动力，更多的EXCEL技能，可以关注百家号“EXCEL学习微课堂”。

与本教程相关的往期教程有：

1、《Excel插入3000名员工一一对应的照片到单元格，原来只要2分钟？》http://.baidu/builder/preview/s?id=1608241034449118412

2、《比Vlookup好用10倍的自定义函数VLOOKUPS，轻松解决VLOOKUP难题》http://.baidu/builder/preview/s?id=1605702288894014773

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板，

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板，

有需要模板的，关注我后，收藏转发下文章，可以私信我。免费下载，

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板。

1、要保证有专人(专职或兼职)管理、有制度规范、有场地存放，即做到有人熟悉档案管理工作，档案保管要保密安全，档案管理工作有制度约束、规范及保障。

2、要确保所存档资料：复印件和原件相符;资料是原始且有价值的;以蓝色或蓝黑钢笔或碳素笔填写;名字、日期等内容填写规范;存档所用纸张规范工整。

3、存档资料要分类按顺序存放。例如可分为基本情况类、合同协议类、证书证件类、培训学习类、考核考察类、岗位变动类、奖惩类、薪酬类、其它及离职类等。顺序可以按照时间顺序或自定顺序来存放。

4、档案一人一档，用档案盒按员工编号存放，不建议用档案袋和档案夹存放，不建议按部门存放。

5、电子档案要与纸质档案同步更新，不用每次查询资料都需要翻纸质档案。注意：电子档案并非是将纸质档案扫描，而是将纸质档案信息保存在一些软件工具中，如E-HR系统、EXCEL表格等，如果有时间和精力再进行扫描的工作。

6、其它档案与员工档案资料有重叠的，原则上员工档案保存原始资料。

原文链接：http://cdhsjy/news/6826.shtml