Excel 工资条制作的两类方法汇总＊

刘师良，王淑艳

（河南医学高等专科学校，河南 郑州 451191）

在计算机二级的考试过程中，Excel 所占的比重是非常高的，而且难度相对来说也最大，尤其是对于非计算机专业的学生来说，理解Excel 中的公式和函数有一定的困难，所以对备考的学生来说，Excel 是学习的重中之重。而在这些Excel 题目中，工资条的制作是其中难度较大的，参考资料中给出的方法是函数法，在本文中会对函数法进行详细介绍，并对资料中的函数法进行改进，让其更为简便。另外，在本文中会介绍几种其他的非函数法解决方案。

在计算机二级MS OFFICE 高级应用26 套题中，需要考试利用工作表“12 月份工资”中的数据为每位员工制作如图1 所示工资条。

图1 工资条样例

本文所使用的方法分为两类：一是函数法。主要是利用函数来解决工资表的创建，通过函数不断从“12 月份工资”工作表中获取数据，并通过函数控制数据的位置来生成工资条。二是排序法。排序法主要是对编号进行排序，使得工资条标题行、数据行和空行按照图示的方式进行排序，以完成工资条的制作[1]。

1 函数法

在本文中所用到的函数法中使用了4 个函数，下面先对这四个函数的功能进行介绍[2]。

CHOOSE(index_num,value1,[value2]……)

CHOOSE 函数是根据索引值的内容，从参数中选出相对应的操作，也就是根据index_num 值的不同，选择不同的value 值。如果index_num 的值为1，则返回value1 的值或进入value1 相应的操作，如果index_num 的值为2 则返回value2 的值或进入相应的操作，以此类推。

ROW(reference)

ROW 函数的功能是得到reference 参数的行的值，也就是参数的行号。

MOD(number,divisor)

MOD 函数的功能是返回两个数字相除后的余数，number 是被除数，divisor 是除数。在本文的应用是MOD(ROW(),3)，也就是把行号除以3 取余数。以A2 单元格为例，经过MOD 函数处理后，得到的余数为2。

OFFSET(reference,rows,cols,[height],[width])

OFFSET 函数的功能是以参数reference 为起始位置，通过rows、cols、height 和width 等四个参数来计算出偏移量，从而得到新的引用位置。rows 表示偏移的行数，cols 表示偏移的列数，height 表示新引用区域的行数，width 表示新引用区域的列数。在本文的应用中，新的引用只是一个单元格，所以OFFSET 函数的最后两个函数缺省，只用到了前面三个参数。以工资表A2 单元格为例，其中的函数为OFFSET('12 月工资表'！A$3,ROW()/3,0)，其中的ROW()/3 的返回值为0，也就是说在此处的函数引用值应为12 月工资表'！A$3，没有偏移量，此行向后填充会以此引用“12 月份工资”表的表头行。在该函数中妙用了ROW()/3，可以保证每3 行引用一次“12 月份工资”表的表头行。A3 单元格则由于ROW()/3的返回值为1，会产生1 行的偏移量，正好是第一个员工的具体数据，随着公式的向下填充，ROW()/3 的返回值也会相应的增加，如图1 所示，第六行的A6单元格中，ROW()/3 的返回值为2，则OFFSET 函数会向下产生两行的偏移量，也就是把第二位员工的具体数据引用到此处。以此类推，不断向下偏移。

在上述四种函数的结合下，首先新建“工资条”工作表，在A1 单元格中输入如下函数：CHOOSE(MOD(ROW(),3)+1,OFFSET('12 月工资表'！A$3,ROW()/3,0),"",'12 月工资表'！A$3)，然后拖动“填充柄”依次向右向下填充，直至产生所有员工的工资条为止。在这个公式中，MOD(ROW(),3)+1 首先返回一个值，通过前面的介绍，以A2 单元格为例，则会返回3，也就是在A2 单元格会进入参数value3 的相应操作，也就是12 月工资表'！A$3。A3 单元格中，由于MOD(ROW(),3)+1 返回1，所有进入OFFSET('12 月工资表'！A$3,ROW()/3,0)相应的操作，根据前面的介绍，可以得到此处应出现第一位员工的工资数据。以此类推，可以很容易获取其他数据产生的原因。

2 改进后的函数法

在第一种函数法解决方案中，函数的应用有些复杂，特别是最后使用的公式复杂度更高，对于初学计算机基础的学生尤其是非计算机专业的学生而言，有一定的难度。针对这一问题，本文对上面的函数进行优化和简化，经过简化后的函数基本上只用了一个函数——VLOOKUP。具体操作是这样的。首先将“12 月份工资”表的表头行复制到“工资表”中，以A2 为起始单元格。然后在A3 单元格中键入“=OFFSET('12 月工资表'！A$3,ROW()/3,0)”，根据前面函数法的介绍中，读者很容易就可以得出这样的结论：在此键入此函数的目的是把“12 月份工资”表中的第一位员工的数据引用至此。然后在B3 单元格中键入“=VLOOKUP($A3,'12 月工资表'！$A$4:$M$71,COLUMN(工资条样例！B2),0)”，然后拖动B3 单元格的填充柄至M3，即可完成员工“刀白凤”的工资条数据的填充。接下来需要选中单元格区域A2:M4，拖动此区域的填充柄至M205，即可生成所有员工的工资条[3]。

VLOOPUP (lookup_value,table_array,Col_index_num,Range_lookup)

VLOOPUP 函数的功能是在table_array 的首列中搜索lookup_value 的值，找到后得到一个行号，在本文的应用中，是要在“12 月份工资”表的第一列查找A3 单元格中的员工编号，找到后得到一个行号，然后结合Col_index_num 的列号确定一个单元格，应用到B3 单元格。Range_lookup 确定匹配模式，在本文中要求精确匹配，所以第四个参数是0。在VLOOKUP 函数中嵌套了一个函数COLUMN (工资条样例！B2)，这个函数会返回个数字，也就是参数列的序列，在此处会得到2，也可也直接使用数字“2”，但是为了方便后面的填充时返回数值列的变化需要，在此处使用了这个函数，也可以使用数字“2，3，4，5，6，7……”。教师在讲解的时候也可先用数字，然后再使用函数来替换，这样更方便学生理解[4]。

3 排序法

根据图1 所示，员工的工资条分为三个部分，第一部分是表头部分，每位员工的工资条都有相同的表头。第二部分是数据部分，每位员工都拥有自己不同的工资数据。第三部分是空行，为了显示和裁剪方便，每位员工工资条数据的下方都有一行空行。根据这样的特点，工资条完全可以用排序法来完成[5]。

第一步现将“12 月份工资”表中“工资条”表中，以B2 为起始单元格。通过观察发现数据共68 行，接下来录入工资条中表头行，也可将“12 月份工资”表中的表头行进行复制，并填充68 行。第二步进行编号。在A 列进行编号，先将数据单元格填充以2为起始，步长为3 的序列，序列的最后一个数字是203。再将表头行填充以1 为起始，步长为3 的序列，序列的最后一个数字是202。最后在下面的空行处填充上以3 为起始，步长为3 的序列，序列的最后一个数字是204。最后一步是排序，以A2：A205区域内的数字为基准进行升序排列，将A 列删除后即可得到图1 所示工资条[6-8]。

上述的排序法最为直接简单，此方法正是利用了工资条中的规律，通过编辑数字序列并将序列进行排序得到的。

4 小结

以上是笔者在日常教学的过程中，针对工资条的制作总结的不同的方法，方法各有利弊，排序法最为简单，学生接受容易，但是应对变化能力不足。函数法虽然理解起来不易，但是，其变化多样，可以让学生更好地掌握公式和函数使用，是一个较好的复杂公式函数应用的案例。当然，Excel 的功能十分强大，还有很多其他的方法能够解决这一问题。