Excel 如何制作任选项目的查询系统

2024-11-29

　　给普通用户的数据查询系统，制作一个能按各种项目随意查询的亲和界面是必要的。巧用VLOOKUP和OFFSET函数，就能实现。

　　面向大众的员工记录、产品记录、合同记录、学生成绩列表记录等经常要查询的记录表，一般需要制作一个查询界面，以便于通过输入员工号、姓名、合同号、产品型号等简单文本来快速查询出所需记录内容。在Excel2010中通常大家都会使用VLOOKUP函数制作查询界面，不过VLOOKUP只能以记录表中的首列为依据进行查询，而在实际使用中由于已知的查询条件不同往往需要随时选择不同列进行查询。以员工记录来说，除了按员工编号进行查询外，有时也需要按姓名、身份证号码、联系电话进行查询。那么要如何才能做到按任选列进行查询呢?在此就以员工记录表的查询为例来介绍两种方法。

　　一、查询界面设置

　　不管用哪种方法实现，查询界面总是一样的，我们就先统一介绍一下查询界面的设置吧。

　　用Excel2010打开“员工记录”工作表，新建一个“查询”工作表并按需要设计好查询界面，在此我们设计在B2单元格输入查询关键词，A2单元格则用于输入要查询的列标题，查询结果则显示在A4:D10单元格区域。选中A2单元格，切换到“数据”选项卡，单击“数据有效性”。在“数据有效性”窗口中单击“允许”的下拉列表选择“序列”，并输入来源为“=员工记录!1:1”即记录工作表的标题行(图1)，确定完成设置。这样我们不仅能方便地从A2的下拉列表中选择要查询的记录列标题，还可有效避免因在A2中输入不存在的列标题出现的查询错误。设置好后先在A2选择输入一个列标题“姓名”，并输入一个正确姓名，以免后面输入公式时显示#N/A错误。

Excel 如何制作任选项目的查询系统(图1)

　　再来选中B7右击选择“设置单元格格式”，在“数字”选项卡中选择“文本”格式，以确保能正常显示身份证号码。同样对D5、D6也要分别设置相应的日期才能显示为正常的日期。其它有特殊格式要求的单元格都得逐一设置过以确保正确显示查询结果。

　　二、实现任选列查询

　　在Excel中用VLOOKUP和OFFSET函数都能轻易实现任选列查询。在此分别介绍一下两种函数的实现方法，实际操作中大家只要任选一种即可。

　　方法一、OFFSET函数

　　用OFFSET函数需要先在员工记录表中为各列数据定义名称后，方可实现任选列查询效果，操作比较简单，不会影响到原人员记录表布局。

　　切换到“员工记录”工作表，选中所有数据列(A:L)，在“公式”选项卡的“定义的名称”组中单击“根据所选内容创建”。在“以选定区域创建名称”窗口中只选中“首行”复选项(图2)，单击确定即可把各列分别按列标题定义名称。切换到“查询”工作表，选中B4单元格输入公式=OFFSET(记录!$A$1,MATCH($B$2,INDIRECT($A$2),0),0)。同样在B4:B10、D4:D8单元格中都输入这个公式，不过要把公式中最后那个0顺次改成1、2、3……11以分别显示相应列的内容。

Excel 如何制作任选项目的查询系统(图2)

　　OK，现在你只要在“查询”工作表中选中A2单元格，单击其后的下拉按钮从下拉列表中选择要查询的列标题为“联系电话”，再输入查询内容“13605076742”，即可查询到联络电话是13605076742的陈桂鑫个人记录(图3)。

Excel 如何制作任选项目的查询系统(图3)

　　注：若要查询的是全数字的身份证号，输入时必需在证号前面加一个半角的单引号，例如“'350621197602232010”，这样身份证号才能正常显示查询。否则输入的身份证号不能正常显示将无法查询出结果。不要事先把B2单元格数值设置为文本格式，虽然设为文本格式也能显示身份证号，但会让输入的电话号、编号、日期等数值变成文本，导致输入电话、编号、日期查询时出错。