公司的人力部门经常要根据工资表生成工资条。Excel中有很多方法可以完成这项工作。 本文和大家分享用CHOOSE函数制作工资条。使用CHOOSE函数的好处是可以生成制作工资条的模板,以后只要将新的工资数据粘贴到模板上,就可以自动生成更新数据后的工资条。 一、案例 如下图所示,工作表“工资明细”的A1:E8单元格为公司工资明细数据。 现在要求在工作表“工资条”生成各员工的工资条,效果如下图所示: 二、操作步骤 1、生成工资条数据 在工作表“工资条”A1单元格输入公式 =CHOOSE(MOD(ROW(),3)+1,"",工资明细!A$1, INDEX(工资明细!A:A,ROUNDUP((ROW()/3),0)+1)) 拖动填充柄向右向下复制公式。 公式解析: (1)ROW()返回公式单元格所在行号。MOD返回余数,例如MOD(4,3)=1。在A1、A2、A3单元格,MOD(ROW(),3)+1分别返回2、3、1。在A4、A5、A6单元格,MOD(ROW(),3)+1依然返回2、3、1。每隔三行,依次返回2、3、1。 (2)CHOOSE函数可以根据给定的索引值对应参数串中的值,例如CHOOSE(2,10,100,1000)返回100。在A1单元格,MOD(ROW(),3)+1=2,CHOOSE函数返回“工资明细!A$1”,即“工号”。由于A1属于相对引用,当向右复制公式时,依次取得“姓名”、“工资”等数据。 (3)在A2单元格,MOD(ROW(),3)+1=3,CHOOSE函数返回 INDEX(工资明细!A:A,ROUNDUP((ROW()/3),0)+1)的值。ROUNDUP((ROW()/3),0)+1=2,INDEX(工资明细!A:A,ROUNDUP((ROW()/3),0)+1)= INDEX(工资明细!A:A,2),返回工作表“工资明细”单元格A2的数据。 (4)在A3单元格,MOD(ROW(),3)+1=1,CHOOSE函数返回空值。 2、使用条件格式为标题行设置边框和填充色 选中A1:E20单元格区域,单击【开始】-【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】,输入公式 =mod(row(),3)=1。单击【格式】,设置边框和填充色。 公式解析:当mod(row(),3)=1时,该行是工资条的标题行。公式 =mod(row(),3)=1返回True,为该行应用设置的条件格式(即蓝色填充色,边框)。 点击确定,效果如下: 3、使用条件格式为明细数据设置边框 选中A1:E20单元格区域,单击【开始】-【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】,输入公式 =mod(row(),3)=2。单击【格式】,设置边框。 公式解析:当mod(row(),3)=2时,该行是工资条的明细数据。公式 =mod(row(),3)=2返回True,为该行应用设置的条件格式(即边框)。 单击确定后,效果如下: |
|