马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
讲到人员信息管理，我们会想到用 Microsoft Office 中的 Access 数据库办公软件，但多数情况下，我们并不需要太复杂，只想用一张表储存数据，再制作一个查询窗口，方便读取内容即可。 通过简单的尝试，发现其实电子表格软件 Excel 就能完成这个任务。我以 Windows 10 + Office 2016 环境下为例讲解。 首先，建立一张人员信息表。新建一个“学员信息表”工作表，把我们需要用到的项目都创建起来，第一行是标题，第二行以下是数据。这步是比较简单的，不多赘述。重点讲下如何做好下拉选项，比如性别一栏，可选择“女”或“男”，而无需手动键入，方便输入。 选中需要进行下拉框菜单选项的第一个单元格，切换到“数据”选项卡，单击“数据验证”。在“数据验证”窗口中单击“允许”的下拉列表选择“序列”，勾选“提供下拉箭头”，在“来源”处输入你想要提供的下拉选项，各选择项用小写逗号隔开。
设置好后点击确认，这样第一个单元格就已经有了下拉选项了。再将鼠标放在第一个单元格右下角，出现小十字的时候按住鼠标左键下拉，选择复制单元格。这样这一列都有了下拉菜单。 如果下拉菜单过于多，我们可以直接在表格中先做好，然后选择该区域。如“鞋子尺码”段这里要输入的比较多，在数据来源中选择对应的表格就可以了。当然前提是我们在对应的列中做好数据了，我新建“数据表”工作表，创建“鞋子尺码”项，列出所有尺码值，在“来源”中输入 =数据表!$A2:A18。
之后同样的下拉复制单元格，完成后表格就是这样子的。
其次，创建一个查询界面。新建一个“查询”工作表，并按需要设计好查询界面。在此我们设计在B2单元格输入查询关键词，A2单元格则用于输入要查询的列标题，查询结果则显示在A4:F15单元格区域。
选中A2单元格，切换到“数据”选项卡，单击“数据验证”。在“数据验证”窗口中单击“允许”的下拉列表选择“序列”，并输入来源为“=学员信息表!1:1”即记录工作表的标题行，确定完成设置。
这样我们不仅能方便地从A2的下拉列表中选择要查询的记录列标题，还可有效避免因在A2中输入不存在的列标题出现的查询错误。设置好后先在A2选择输入一个列标题“姓名”，并输入一个正确姓名，以免后面输入公式时显示#N/A错误。 再来选中D6右击选择“设置单元格格式”，在“数字”选项卡中选择“文本”格式，以确保能正常显示手机号码。同样对F4、B7等也要分别设置相应的日期才能显示为正常的日期。其它有特殊格式要求的单元格都得逐一设置过以确保正确显示查询结果。 再次，实现任选列查询。在Excel中用VLOOKUP和OFFSET函数都能轻易实现任选列查询，我是用了 OFFSET函数。 用OFFSET函数需要先在“学员信息表”中为各列数据定义名称后，方可实现任选列查询效果，操作比较简单，不会影响到原人员记录表布局。 切换到“学员信息表”工作表，选中所有数据列(A:AJ)，在“公式”选项卡的“定义名称”组中单击“根据所选内容创建定义的名称”。在“根据下列内容中的值创建名称”窗口中只选中“首行”复选项，单击确定即可把各列分别按列标题定义名称。
切换到“查询”工作表，选中B4单元格输入公式 =OFFSET(学员信息表!$A$1,MATCH($B$2,INDIRECT($A$2),0),0)。同样在其他单元格中都输入这个公式，不过要把公式中最后那个0顺次改成1、2、3……11以分别显示相应列的内容。 OK，现在你只要在“查询”工作表中选中A2单元格，单击其后的下拉按钮从下拉列表中选择要查询的列标题为“姓名”，再输入查询内容例“詹颖”，即可查询到詹颖的个人信息。 最后，美化查询窗口的空记录显示。EXCEL用公式OFFSET后，数据表中原是空白部分，查询窗口会自动填写为 0，日期格式的单元格就会显示日期起始值，如何设置空白部分为空白，不为 0，从而美化查询窗口呢？ 只需在外加一层 IF 循环控制就行。＝IF(A=0,””,A)，具体到上面的公式就变成：=IF(OFFSET(学员信息表!$A$1,MATCH($B$2,INDIRECT($A$2),0),0)=0,””,OFFSET(学员信息表!$A$1,MATCH($B$2,INDIRECT($A$2),0),0)) 如此，一个简单易用的人员信息管理系统就建成了，可以便捷地查询某个员工的个人信息，而不用拖动长长的表格来查看。
| 
发表于 2020-6-26 09:30:01
置顶卡
变色卡
千斤顶
照妖镜
鄂公网安备 42080402000288号