制作任选项目的查询系统

PLA007

给普通用户的数据查询系统，制作一个能按各种项目随意查询的亲和界面是必要的。巧用VLOOKUP和OFFSET函数，就能实现。
面向大众的员工记录、产品记录、合同记录、学生成绩列表记录等经常要查询的记录表，一般需要制作一个查询界面，以便于通过输入员工号、姓名、合同号、产品型号等简单文本来快速查询出所需记录内容。在Excel2010中通常大家都会使用VLOOKUP函数制作查询界面，不过VLOOKUP只能以记录表中的首列为依据进行查询，而在实际使用中由于已知的查询条件不同往往需要随时选择不同列进行查询。以员工记录来说，除了按员工编号进行查询外，有时也需要按姓名、身份证号码、联系电话进行查询。那么要如何才能做到按任选列进行查询呢？在此就以员工记录表的查询为例来介绍两种方法。
一、查询界面设置
不管用哪种方法实现，查询界面总是一样的，我们就先统一介绍一下查询界面的设置吧。
用Excel2010打开“员工记录”工作表，新建一个“查询”工作表并按需要设计好查询界面，在此我们设计在B2单元格输入查询关键词，A2单元格则用于输入要查询的列标题，查询结果则显示在A4:D10单元格区域。选中A2单元格，切换到“数据”选项卡，单击“数据有效性”。在“数据有效性”窗口中单击“允许”的下拉列表选择“序列”，并输入来源为“=员工记录!1:1”即记录工作表的标题行（图1），确定完成设置。这样我们不仅能方便地从A2的下拉列表中选择要查询的记录列标题，还可有效避免因在A2中输入不存在的列标题出现的查询错误。设置好后先在A2选择输入一个列标题“姓名”，并输入一个正确姓名，以免后面输入公式时显示#N/A错误。

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

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

注：若要查询的是全数字的身份证号，输入时必需在证号前面加一个半角的单引号，例如“'350621197602232010”，这样身份证号才能正常显示查询。否则输入的身份证号不能正常显示将无法查询出结果。不要事先把B2单元格数值设置为文本格式，虽然设为文本格式也能显示身份证号，但会让输入的电话号、编号、日期等数值变成文本，导致输入电话、编号、日期查询时出错。
方法二、VLOOKUP函数
大家应该比较习惯于使用VLOOKUP查询吧？虽然VLOOKUP只能按首列查询，但这不是问题。我们只要在员工记录表前面增加一列辅助列来实时显示要查询列的内容，同样可以轻易实现任选列查询。
在“员工记录”工作表中，选中A列右击选择“插入”在A列前插入一个空列。在A1单元格输入公式=HLOOKUP(查询!A$2,B:M,ROW(),FALSE)。选中A1:A2000区域按Ctrl+D组合键把A1公式复制填充到A1:A2000单元格，即可在A列显示要查询列的内容。由于我们在查询工作表的A2输入的是姓名所以现在显示的是姓名列（图4）。至于具体需要向下填充到第几行请参照记录表准备存储的记录数考虑。此外，若不想影响原表视图可在设置好后选中A列右击选择“隐藏”隐藏A列。

回到“查询”工作表，按一般VLOOKUP函数的用法，在B4输入公式=VLOOKUP($B$2,员工记录!$A:$M,2,FALSE)。同样在B5:B10、D4:D8单元格中都输入这个公式，不过要把公式中的2顺次改成3、4……13以分别显示相应列的内容（图5）。现在试试在A2选择列标题，输入查询内容，也一样能找到记录吧。

总结，用VLOOKUP缺点是需要添加辅助列，当增加记录行时得把辅助列的公式复制下来才能查询到。优点是当列数、列标题变化时无需再做任何修改仍可正常使用。OFFSET优点是不影响人员记录表，增加记录行无需做任何修改。缺点是当用于查询的列标题改变时得选中列重新定义名称后方可正确查询。两种方法各有优劣，大家可按表格特点、个人习惯选择适合的方法。
注：现在虽然可以在A2单元格中选择任何一列进行查询，不过除员工编号、姓名、身份证号、联系电话外，对其它列的查询符合的可能不只一项，而不管是VLOOKUP还是OFFSET函数都只能显示第1条符合的记录，其它记录就找不到了。对此，可以限制只能按员工编号、姓名、身份证号、联系电话等具有唯一性的列进行查询。方法是在查询界面中为B2设置“数据有效性”时，把输入的来源改为“员工编号,姓名,身份证号,联系电话”即可（不含引号，各选项间以半角的逗号分隔）。