打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
用EXCEL制作工资条方法

新插入一个工作表

    在A1输入以下公式(可复制以下内容)

      =IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,INDEX(清单!$A:$GH,INT((ROW()+4)/3),COLUMN())))

如果有两行项目可以使用下面公式:

     =IF(MOD(ROW(),4)=0,"",IF(MOD(ROW(),4)=1,清单!A$1,IF(MOD(ROW(),4)=2,清单!A$2,INDEX(清单!$A:$GF,INT((ROW()+9)/4),COLUMN()))))

然后向右向下拖拉复制公式。

   其中"清单"为工资表的表名,可在公式里修改。

   工资条间距离需要自己设置

    本工资簿包含两张工资表。

       第1张工资表就是工资清单,称为"清单"。它第一行为标题行包括职工姓名、各工资细目。

       第2张工作表就是供打印的表,称为"工资条"。它应设置为每三行一组,每组第一行为标题,第二为姓名和各项工资数据,第三行为空白行。就是说整张表被3除余1的行为标题行,被3除余2的行为包括职工姓名、各项工资数据的行,能被3整除的行为为空行。

在某一单元格输入套用函数"=MOD(ROW(),3)",它的值就是该单元格所在行被3除的余数。因此用此函数能判别该行是标题行、数据行还是空行。

       在A1单元格输入公式"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,"value-if-false"))"并往下填充,从A1单元格开始在A列各单元格的值分别为清单A1单元格的值即姓名、value-if-false、空白,姓名、value-if-false、空白,……。其中value-if-false表示MOD(ROW(),3)既不等于0又不等于1时,即它等于2时应取的值。它可用如下函数来赋值:"INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"。INDEX()为一查找函数它的格式为:INDEX(reference,row-num,col-num),其中reference为查找的区域,本例中为清单表中的A到G列,即函数中的"清单!$A:$G",row-num为被查找区域中的行序数即函数中的INT((ROW()+4)/3),col-num为被查找区域中的列序数即函数中的COLUMN()。第2、5、8…….行的行号代入INT((ROW()+4)/3)正好是2、3、4……,COLUMN()在A列为1。因此公式"=INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"输入A列后,A2、A5、A8……单元格的值正好是清单A2、A3、A4……,单元格的值。这样,表的完整的公式应为"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())))"。把此公式输入A1单元格,然后向下向右填充得到了完整的工资条表。

       为了表格的美观还应对格式进行设置,一般习惯包括标题、姓名等文字在单元格中要取中,数字要右置,数字小数点位数也应一致,还有根据个人的爱好设置边框。本表格只需对一至三行的单元格进行设置,然后通过选择性格式设置完成全表的设置。

本工作簿的特点是1、不对清单表进行操作保持清单工作表的完整,2、全工作表只有一个公式通过填充得到全表十分方便

详解:

    一般来说,工资表的标题及其表的抬头部分都要占几行,所以工资数据的起始行并不是表的第一行,这里假定工资表的表名为GZ,列数有A~P共16列,工资项目在第三行,四至十三行是具体的工资数据,十五行为合计数。在工资表里,一行对应一个人的工资数据,而在工资条中则是两行对应一个人的工资数据,即在每个人的工资数据前要插入工资项目。这样我们只要设置一些简单的取数公式就行了。对于工资项目,固定去取第三行的数据,对于工资数据,则关心的是目标行与其源始数据行的对应关系。

源数据行 1 2 3 4 5 6 7 8 9 10

目标行 2 4 6 8 10 12 14 16 18 20

     其对应关系计算公式如下:

  源数据行号=INT((目标行号+1)/2)

  清楚了上述关系后,我们就可以来制作工资条了。制作工资条即可在原工作表中做,也可新建工作表。为简单起见,这里只针对新建工作表进行说明。

  新建工作表,在新表的A1单元格中输入:

IF(MOD(ROW(),2)=0,INDEX(GZ!$A$4:$P$13,INT(((ROW()+1)/2)),COLUMN()),GZ!A$3)

  公式中首先进行奇偶行判断,若是奇数行,直接取工资表的A3单元格数据。若是偶数行,则用INDEX()函数来取数。该函数的第一个参数是指定工资表中的一个取数区域,表中的第四行在此区域中就成为第一行,这样就取到了工资表中对应行当前列的单元格数据。

  接下来的工作就简单了,我们只要使用填充柄将此公式填充到其它单元格,列向填充到P列,行填充到20行就大功告成。

  但这样的公式还存在一定的问题,就是在公式中使用了“GZ!$A$4:$P$13”来对所取数据的坐标区域进行了限制。如果工资表的行列有所增加或者减少,则要修改此坐标区域以适应这个变化。通常情况下列是固定不变的,而行的变动则有可能经常发生。我们就重点解决行的变化所带来的问题。

  其实解决这个问题也很简单,只要取消行的限制即可。即将公式改成:

IF(MOD(ROW(),2)=0,INDEX(GZ!$A:$P,INT(((ROW()+1)/2))+3,COLUMN()),GZ!A$3)

  这里行号加上3是因为取消行的限制后,其行号就要从第四行开始计数了。

  这样,我们只要根据行的增减变动对工资条工作表进行简单地复制或者删除操作,就能适应这种变动。

  关于多行标题的:

工资项目是两行时怎么办?

    和单行项目比起来,无非是从原来的一行对应两行变成现在的一行对应三行了。可以说,公式结构没有变化,只要调整一下计算参数,增加一个判断多取一行数据就成了。比如,针对结构,公式可相应地变为(例假定到U):

=IF(MOD(ROW(),3)=0,INDEX(工资B!$A:$U,INT((ROW()/3))+3,COLUMN()),IF(MOD(ROW(),3)=1,工资B!A$2,工资B!A$3))

  但是,在单行工资条的公式中,因为只需一个判断函数IF(),所以结构相对简单,效率也不存在问题。而在这里,公式结构变复杂了,如果使用CHOOSE()函数,能使公式简化。

=CHOOSE(MOD(ROW(),3)+1,INDEX(工资B!$A:$U,INT((ROW()/3))+3,COLUMN()),工资B!A$2,工资B!A$3)

  对比这两个公式,其实大同小异,只是CHOOSE()的计数是以1为起点的,所以需要加个1。

  但是,多行项目其项目行往往有结构的要求,用公式只能将其数据取出,对结构来说却是无能为力的。那怎么办呢?

  这个问题实际也很好办,不过需要多操作两步罢了。选择2~4行,单击格式刷,再到工资条表格中单击A1单元格,立即再次单击格式刷,然后将余下的工资条都选中,美观的工资条就出来了。

  以上是两行项目的,再看工资项目变三行的。

  针对这个工资表。公式有何变化呢?

=CHOOSE(MOD(ROW(),4)+1,INDEX(工资C!$A:$U,INT((ROW()/3))+4,COLUMN()),工资C!A$2,工资C!A$3,工资C!A$4)

  呵呵,CHOOSE()函数的优势在这里体现出来了。与上一个公式比较一下,变化不大吧?如果还有更加特别的,如四行、五行项目的,照此处理就行了。

  工资数据竟然也占两行。还好,有了上面的基础,只需做些简单的变换就成了(这个表的列到R列)。

=CHOOSE(MOD(ROW(),4)+1,INDEX(工资D!$A:$R,INT((ROW()-1)/4)*2+4,COLUMN()),工资D!A$1,工资D!A$2,INDEX(工资D!$A:$R,INT((ROW()-1)/4)*2+3,COLUMN())

重新排序法

     在Excel2000做成的工资表,只有第一个人有工资条的条头(如编号、姓名、岗位工资,年限工资……),想输出成工资条的形式。怎么做?
  解决方法:
  (1)、假设原工资表为“表1”,先复制“表1”到某新空白表中,命名为“表2”,删去“表2”中的多余行,使工资条头位于第一行,职工工资数据依次紧排在条头之后。:
  (2)、在“表2”工资数据的后一列H中,增加“标注”栏,依次向下填充入1,2,3…等步长为1的序列数。
  (3)、选中“表2”工资条头下(工资条头不选)第2至最后一行,右键:插入,表2中将出现许多新增空行,将条头数据填入这些新增空行中(方法:选中A1:G1,右键“复制”,然后选中所有新增空行,右键“粘贴”即可,也可用拖拽填充法)。然后在其“标注”栏中依次填上1.5,2.5,3.5…。等步长为1的序列数。操作结果示意如下:
  (4)、再次选中“表2”第2行至最后一行,点击菜单:数据-排序-按“标注”列(递增)排序,点击确定。再选中H“标注”列,右键:“隐藏”或“删除”,这样,工资条即基本制作完毕。
  (5)、制作完成的工资条裁开后即可分发给同事了。您如果觉得此表格过于拥挤,还可用类似的方法在每个人之间插入空行

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
HR和财务,不收藏就后悔!双行表头工资条,这个函数公式一步到位帅到爆燃...
用EXCEL如何制作工资条
看着生气擦屁屁太细!
一条公式,工资表转成工资条,单双表头都可以!
execl 工资条
Excel函数应用实例
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服