办公小技巧:Excel中轻松制作员工档案查询

CFan 电脑爱好者 2018-12-25 09:07应用 标签:档案查询 员工 技巧 办公 制作

要建立员工电子档案并能实现对员工信息的查询,如果用“查找”的方法,在众多的档案项目中,有些在查询结果中不需要显示的项目也会显示,那样就显得有些眼花缭乱,所以就得另辟蹊径。如果利用控件、VLOOKUP函数等制作员工档案查询界面,将档案信息显示在相应单元格中,这样就能清晰明了、别具一格(图1)。

1801A-DACX-1

1. 构建员工信息表

员工信息表就是在Excel工作表中录入所需要员工的档案信息,没什么难度。要说稍微有些难点的地方,就是在工作表中批量插入员工的照片到相应的单元格中。这里向大家介绍两种方法,无论哪种方法,照片名一般都和信息表中的姓名相对应,当然也可和表中其他字段相对应,只要保证其唯一性就可以。

第一种方法是利用公式,在员工档案信息表N2单元格输入公式“="

"”,向下拖动填充到相应单元格。接下来,选中图片列公式内容(从N2开始),复制,返回桌面,新建一个文本文件,并打开,粘贴(图2)。

1801A-DACX-2

小提示:

公式中的“D:\photo”为存放员工照片的文件夹,A2为照片名称,176和220分别是照片的宽度和高度,单位是像素,实际应用时大家可以根据实际情况调整。

照片所在列的行高、列宽调整好后,复制记事本中的内容,右键单击员工档案信息表N2单元格,选择“选择性粘贴”,在弹出窗口选择“Unicode文本”,确定后照片就插入到相应单元格中了(图3)。

1801A-DACX-3

第二种方法,即替换定位法。首先,在一个空白单元格中输入半角双引号(如O2单元格),然后在另一单元格Q2输入公式“=$O$2&C2&".jpg"&$O$2&" "”,并向下填充,将生成的内容全选复制、粘贴到文本文件中,再从文本文件中复制文本,把复制的名称粘贴到另一个空白单元格中,本例中放在了R2单元格中。选中R2单元格,调出查找、替换窗口,在“查找内容”里输入Ctrl+J或者Ctrl+Enter,表示要替换的是回车符,而“替换为”处输入英文状态下的空格,点击“替换”以后,就会出现加了以空格为分隔符的信息了(图4)。

1801A-DACX-4

小提示:

为什么要使用替换功能呢?因为Excel导入图片,如果要批量的话,名称之间必须用空格间隔开。

接下来,选中R2单元复制,通过工具栏中的插入功能,进行图片的插入。找到准备好的图片素材所在路径,然后把复制的信息粘贴到文件夹名中,点击“插入”后就在工作表里插入相应图片了(图5)。

1801A-DACX-5

图片插入后,通过工具栏中的“编辑”,选择“定位”,然后选择“定位条件”。在定位条件中,选择“对象”,因为图片是以对象的形式插入的,这样就可以选中所有的图片。右击图片选择“设置对象格式”,在窗口中设置好图片大小。将最底层的图片拖放到照片的第1个单元格,将最顶层的图片拖放到照片的最后单元格,再次利用对象定位选中所有图片,通过对齐中的左对齐和纵向分布将所有图片放置到相应单元格中。图片都是对应Excel的排列顺序而排列的,所以用户不需要担心会对应错误(图6)。

1801A-DACX-6

2. 插入并设置下拉菜单

档案信息表构建完成后,新建一个工作表,设计好档案界面,调整好显示照片单元格的大小。点击“公式”选项卡中的“名称管理器”,新建名称“图片”,引用位置为“=INDEX(Sheet1!$N:$N,MATCH(INDEX(Sheet1!$B2:$B16,Sheet3!$A$1,),Sheet1!$B:$B))”。选择“开发工具”选项卡,点击“插入→表单控件”下的“组合框”,在姓名位置画个组合控件,右击这个控件,选择“设置控件格式”,在弹出窗口数据源区域选择信息表中的C2:C16,链接单元格选择A1(当然也可选择其他空白单元格),这样就形成了下拉列表菜单(图7)。

1801A-DACX-7

3. 根据选择显示所需员工信息

在H6(即所要显示工号单元格)单元格中输入公式“=VLOOKUP(INDEX(Sheet1!$B$2: $B$16, $A$1,),Sheet1! $B$2: $N$16,1,FALSE)”,这样就能获取到当前所选人的工号;在J6单元格输入公式“=VLOOKUP(INDEX(Sheet1!B2:B16,A1,),Sheet1!B2:N16,9,FALSE)”,这样就能获取到当前所选人的籍贯;其他所要显示的信息公式依此类推。最后,先在界面照片位置插入一个图片,调整好其大小,在公式栏输入“=图片”。

经过上述设置,当输入的姓名发生变化时,其他信息及照片也发生相应变化,即可实现信息按设定的格式查询显示了。