Excel 如何制作任选项目的查询系统-英雄云拓展知识分享

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

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

　　一、查询界面设置

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

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

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

　　2、实现任选列查询

　　在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以分别显示相应列的内容。

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

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