工资查询(三大步完成工资查询系统)-成都富士康在线报名

EXCEL进阶课堂 · 案例析 开始推出！在工作当中，我们会遇到各种具体的问题，有许多问题具有典型代表性。进阶君把这些案例提炼出来，通过清晰明了的分析思路和操作步骤，试图给小伙伴们带来启发和思考，更好地解决工作任务，提高工作效率，不再做不停加班的表哥，表姐。欢迎各位小伙伴转发、点赞、讨论，更欢迎私信获取练习素材，刻意练习才能学有收获。

这是 EXCEL进阶课堂 · 案例析 的第1篇教程。

01 案例引入

工资查询，是一个比较常见且具有典型代表性的任务。

工资查询，到底要完成怎样的一个功能呢？有小伙伴一定会说：很简单呀，就是从一定量的工资数据中，按照查找项，找到相关人员的信息显示出来。如果只是从函数应用的方法来看，这个回答是没有问题的，但是，如果从一个查询系统层面来看，有五个重要问题需要思考：

（1）所有人的工资信息，能不能让人随意看？如何处理？

（2）查询页面，任何地方都可以输入内容吗？如何处理？

（3）查询项可以随便选择吗？选择标准是什么？

（4）如何查询？查询不到怎么办？

（5）查询公式能让人随意查看吗？如何处理？

这些问题，很多小伙伴以前是没有考虑过的。进阶君和大家一起进行案例分析。

02 案例分析

虽然这是一个简单的查询系统，但是它的安全性、容错性、合理性是需要认真思考和规划的。

（一）查询系统安全性

（1）必须隐藏员工工资信息

分析：员工工资信息是非常隐私的数据，不能是任意人都能随意查看的。工资查询系统是面向所有人的，所以必须对员工工资信息作隐藏处理，而且为了防止人为破坏和篡改数据，还需要对数据进行保护操作。

关键技术：工作表隐藏+工作表保护

（2）查询页面内容输入区域限制

分析：查询页面（工作表）中，需要完成输入内容并显示查询结果。输入内容由用户完成的，但是输入的区域（单元格）必须限制，即只能在这里输入，其它区域不允许输入。查询结果只能浏览，不能修改、删除等。

关键技术：允许编辑区域设定+工作表保护

（二）查询系统合理性

（1）查询项唯一准则

分析：查询系统就是根据某个查询项去查找相关的记录。选择查询项的合理性和科学性，直接影响到查询结果。在选择查询项时，以查询项唯一性为准则进行选择。如：在姓名和身份证号两个选项中，姓名有可能出现同名，而身份证号是唯一的，所以选择身份证号为查询项是合理的。再如：在姓名和工号两个选项中，选择工号为查询项是合理的。

（2）公式隐藏原则

分析：查询系统中，除了使用VBA技术外，自然会用到查询函数形成公式，而这个公式如果能在查询页面当中被看到，既影响美观效果，也非常不合格。

关键技术：单元格保护+工作表保护

（三）查询系统容错性

所谓容错性，就是指对出现的各种非正常结果进行必要的处理，使系统能够给出各种合理的提示信息等。

（1）查询无结果

分析：在查询系统中，经常会遇到查无结果的情况，对于这种情况需要必要的处理，比如显示“查无此人”。

关键技术：IFERROR函数

（2）查询项无输入

分析：在查询项无输入时，不应该进行查询，否则会显得很诡异。

关键技术：IF函数

03 案例解决

进阶君将与各位小伙伴一起，不用VBA代码，纯函数来实现工资查询系统，抛砖引玉，供大家举一反三参考使用。

第一步：规划数据表和设计查询页面

（1）规划工资数据表

以“员工工资信息”为名，建立工作表，规划和存放工资数据表。效果如下图所示：

（2）设计查询页面

在单独工作表中，设计查询页面，决定输入项，查找显示项等信息。根据前面的所讲的原则，将工号设定为查询项，与工号、姓名和与工资相关的数据形成查找显示项。效果如下图所示：

第二步：完成查询公式设定

（1）基本公式设定

在一张工表中输入内容，然后再另一张工表当中去查找，找到以后将结果返回到前表中，这是典型的查找应用。实现这一功能的函数有很多，我们在这里采用vlookup函数来实现。

在“工资查询”工作表中，选中B5单元格，然后在里面输入公式：

公式的结果为：#N/A。原因有两个：其一，查询工号 没有输入；其二，查询工号在没有输入的情况下不可能查询到结果。

（2）第一次升级公式：排除没有输入情况

在“查询工号”没有输入的情况下，不应该去进行查询动作，这需要对公式进行升级处理。如何处理呢？用if函数进行处理。修改B5单元格里面的公式：

公式修改后，可以使没有输入的情况不进行查询。可是如果有输入，在没有找到相应记录的情况下，vlookup函数仍然回返回#N/A，这个符号对于一般用户而言非常费解。这种情况需处理。

（3）第二次升级公式：处理查无结果的情况

在C2单元格输入查询工号后，出现查无结果的时，会得到#N/A这个符号，它在EXCEL中会被认为是一种错误。于是我们可以用iferror函数来进行处理。修改B5单元格里面的公式：

到此为止，B5单元里面的公式已经比较完美了。但是，C5单元格至K5单元格仍需要输入查询公式，如果每个都去写，那么效率很低，如果能够进行公式复制就会大大提高效率。

（4）第三次升级公式：解决公式拖动复制问题

在vlookup函数中，返回查询结果是需要指定的，B5返回的是第1列，C5就应该返回第2列，如此类推。这样的指定如何用公式解决呢？大家观察，B5单元格所在的列是第2列，要返回的是查询区域的第1列，C5单元格所在的列是第3列，要返回的是查询区域的第2列。于是，我们可以总结出，要返回的查询区域的列数比单元格所在列数少1。修改B5单元格里面的公式：

将查找区域改成绝对引用方式后，采取拖动复制的方式，将C5单元格至K5单元格填充好公式。具体效果如下动图所示。

第三步：完善后续设定

（1）隐藏“工资查询”工作表

（2）设定C2单元格为可编辑区域

通过审阅菜单下面的允许用户编辑区域进行设定。

具体操作及效果如下动图所示。

（3）隐藏单元格公式设定

对单元格里面公式进行隐藏需要通过两步完成，这是第一步，需要与下一步进行配合才能真正实现。具体操作及效果如下动图所示。

（4）对工作表和工作簿进行保护

要使得可编辑区域和隐藏单元格公式起作用，需要对工作表进行保护。

要使得隐藏的工作表不能被取消隐藏，需要对工作簿进行保护。

具体操作过程及效果如下动图所示。

04 总结思考

本案例以工资查询系统为蓝本，按系统规划的思路，采取最基本的函数操作方法，完成了整个系统的实现。小伙伴可以举一反三，结合工作实际进行具体查询实现。

为方便小伙伴们学习，进阶君将原始素材共享出来，获取素材的方法：

第一步：关注 Excel进阶课堂。

第二步：私信 Excel进阶课堂，因为设定的是自动回复，所以内容一定要准确

私信内容：练一练

第三步：根据得到信息打开网盘，找到 案例01 工资查询系统工作簿自行下载