EXCEL在工资管理中的运用
发布时间：2015-07-09 11:04
一、建立基础信息表
建立如表1所尔的基础信息表,在单元格B2中输入月份数,在单元格12中输入出勤天数。根据考勤表输入事假天数、病假天数、加班天数;根据上浮工资分配表输入浮动工资,根据后勤部门统计的房租输入房租。为防止输入数据引起银行卡号误差可以将“银行卡号”这一列隐藏。
基础信息工作表等
二、建立工资表
第一步,在单元格A1中输入“=”南通××公司2006年“&基础信息!B2&“月上资表””,并合单元格A1:Q1,如表2所示。表2中月份数据随“基础信息表”中月份变化而变化。
第二步,利用Excel不同表之间数据链接功能,在单元格A3、B3、G3、L3中分别输入“=基础信息!A4”、“=基础信息!B4”、“=基础信息!G4”、“=基础信息!H4”,基础信息表中的部门、姓名、浮动工资、房租将自动出现在工资表中。利用Excel“填充柄”将其它各员工的有关数据自动产生。
第三步,将工资卡片中各员工的基本工资、岗位工资、综合补贴等记录,分别输入单元格C3、D3、E3所在列。
第四步,根据公司加班政策,假设加一天班为20元,在单元格F3中输入“=基础信息!F4*20”;根据事假扣除标准“(基本工资+岗位工资+综合补贴)÷本月出勤天数×事假天数”,病假扣除标准“(基本工资+岗位工资+综合补贴)÷本月出勤天数×事似天数-病假天数×5”,分别在单元格H3、13中输入“=IF(基础信息!D4=”“,0,ROUND((C3+D3+E3)/基础信息!＄I＄2★基础信息!D4,2))”、“=IF(基础信息!E4=”“,0,ROUND((工资表!C3+工资表!D3+工资表!E3)/基础信息!＄I＄2*基础信息!E4-5*基础信息!E4,2))”,这里运用ROUND函数表示对事假或病假扣除金额四舍五入,IF函数表示如果没有请事假或病假则扣额为零,否则按事假或病假标准扣除。需注意公式中＄表示绝对引用符号不可省略,复制公式时引用范围不会发生变化。根据三金(养老保险、失业保险、医疗保险)扣除标准分别为基本工资、岗位工资、综合补贴之和的8%、1%、2%,在单元格J3中输入“=ROUND((C3+D3+E3)*11%,2)”。
第五步,为了方便利用函数计算个人所得税,增加“应税所得额”列,平时可将其隐藏。在单元格M3中输入“=IF((J3-K3-1600)O,J3-K3-1600,0)”表示应税所得额只有超过免征额1600元才征税,否则不征税。在单元格N3中输入“=ROUND(IF(M3=500,M3*0.05,IF(M3=2000,M3*10%-25,IF(M3=5000,M3*15%-125,IF(M3=20000,M3*20%-375,IF(M3=40000,M3*25%-1375,M3*30%-3375))),2)”,这里运用IF函数的层层嵌套计算个人所得税。
第六步,在单元格J3中输入“=C3+D3+E3+F3+G3-H3-13”,表示“应发工资=基本工资+岗位工资+综合补贴+加班+浮动工资-事假、病假扣除”;在单元格03中输入“=K3+L3+N3”表示扣除合计总额;在单元格P3中输入“=J3-03”表示实发工资总额;在单元格B12中输入“=SUBTOTAL(3,工资表!A3:A11)&“人””,这里运用了SUBTOTAL动态计数函数计算出人数;在单元格C12中输入“SUM(C3:C11)”表示对所有人员的基本工资求和。最后再利用“填充柄”,自动生成相应数据。
三、建立汇总表
建立如表3所示的工资汇总表。工资汇总表中主要运用了SUMIF( )求和函数,从其它工作表中获取数据。其功能是在满足指定的统计条件下,对给定数据区域中的某一栏目范围进行累计求和。在单元格B3中输入“=SUMIF(工资表!＄A＄3:＄A＄11,＄A3,工资表!＄C＄3:＄C＄11)”,表示将“部门”列中所有“生产”的行所对应的“基本工资”合计。 “生产”部门其余工资各项目汇总公式均参照单元格B3填写,只需将单元格B3中需要求和的对象“工资表!＄C＄3:＄C＄11”变动一下,其余均不变,如在单元格C3中输入“=SUMIF(工资表!＄A＄3:＄A＄11,＄A3,工资表!＄D＄3:＄D＄11)”。在单元格B7中输入“=SUM(B3:B6)”,表示对汇总数求和,再利用“填充柄”,生成相应汇总数及合计数。
四、建立银行表
建立如表4所示的银行表。在单元格A2中输入“=基础信息!B4”,利用“填充柄”,生成各员工姓名。“账号”、“实发工资”自动产生,主要运用了VLOOKUP()函数,其功能是搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。在单元格中B2中输入“=IF(A2:”“,”“,VLOOKUP(A2,基础信息!B4:C21,2,FALSE))”,表示如果姓名A2为空格,那么账号B2为空,否则账号B2为“基础信息”表中姓名和账号两列区域中第一列的数值等于单元格A2中的数值的同一行的第二列数值。同样在单元格C2中输入“=IF(B2=”“,”“,VLOOKUP(A2,工资表!B3:P11,15,FALSE))”,再利用“填充柄”,生成各员工的账号及实发工资。
五、创建“宏”生成工资条
为了提高工作效率,可以通过创建“宏”生成工资条。本文创建工资条通过录制交互操作过程逐步创建,此种方法相对简单,具体步骤如下:
第一步,启用宏。用鼠标单击[工具]-[宏]-[录制新宏],键入宏名“工资条”;在“保存在(I):”下拉框中,选择“当前工作簿”;在“说明(D):”编辑框中键入相应的文字;单击【确定】。
第二步,录制宏。按住CTRL健的同时,沿工作表标签行拖动“工资表”标签,建立副本,并双击将“工资表(2)”改名为“工资条”,然后将“工资条”中的数据编辑修改如表5所示。注意,此步骤中每一步都应小心谨慎操作,因为若在录制宏时出现失误,更正失误的操作也会记录在宏中。
第三步,单击“停止录制”按钮,结束宏录制。
第四步,创建“按钮”。用鼠标单击【按钮】图标,在“工资表”划一个小方框,选中小方框,右击鼠标→编辑文字→输入“工资条”→右击鼠标→单击“指定宏(N)…”→选择“工资条”→“确定”。以后每次要生成工资条时。只要将原“工资条”删除,按一下“工资条”按钮,就可以完成上述一系列操作,自动生成“工资条”。
工资汇总表等
一套工资核算模型创建完成,下月进行工资核算时,可以先复制一下该文件,然后只要将“基础信息”表中月份、本月出勤天数、事假天数、病假天数、加班天数、浮动工资、房租等数据输入,工资表、工资汇总表、银行表、工资条都将自动生成,大大提高了工作效率。另外在使用过程中应注意以下几点:(J)在输入数字时,小数点有时输成中文句号,可以选取“基础信息”表中的D、E、F、G、H五列,单击菜单[数据]-[有效性]-[输入法模式]中选取“关闭”。(2)为防止单元格中创建的公式被误删或更改,可以先选中有公式的单元格,在菜单[格式]-[单元格]-[保护]中的[锁定]前打勾,然后单击菜单[工具]-[保护]-[保护工作表],输入密码。(3)为限制不同用户查看和修改文件,在文件创建完成后,可以输入打开权限密码和修改权限密码,具体单击[文件]-[另存为]-[工具]-[常规选项],在[打开权限密码]和[修改权限密码]中输入不同的密码。
建立如表1所尔的基础信息表,在单元格B2中输入月份数,在单元格12中输入出勤天数。根据考勤表输入事假天数、病假天数、加班天数;根据上浮工资分配表输入浮动工资,根据后勤部门统计的房租输入房租。为防止输入数据引起银行卡号误差可以将“银行卡号”这一列隐藏。
基础信息工作表等
二、建立工资表
第一步,在单元格A1中输入“=”南通××公司2006年“&基础信息!B2&“月上资表””,并合单元格A1:Q1,如表2所示。表2中月份数据随“基础信息表”中月份变化而变化。
第二步,利用Excel不同表之间数据链接功能,在单元格A3、B3、G3、L3中分别输入“=基础信息!A4”、“=基础信息!B4”、“=基础信息!G4”、“=基础信息!H4”,基础信息表中的部门、姓名、浮动工资、房租将自动出现在工资表中。利用Excel“填充柄”将其它各员工的有关数据自动产生。
第三步,将工资卡片中各员工的基本工资、岗位工资、综合补贴等记录,分别输入单元格C3、D3、E3所在列。
第四步,根据公司加班政策,假设加一天班为20元,在单元格F3中输入“=基础信息!F4*20”;根据事假扣除标准“(基本工资+岗位工资+综合补贴)÷本月出勤天数×事假天数”,病假扣除标准“(基本工资+岗位工资+综合补贴)÷本月出勤天数×事似天数-病假天数×5”,分别在单元格H3、13中输入“=IF(基础信息!D4=”“,0,ROUND((C3+D3+E3)/基础信息!＄I＄2★基础信息!D4,2))”、“=IF(基础信息!E4=”“,0,ROUND((工资表!C3+工资表!D3+工资表!E3)/基础信息!＄I＄2*基础信息!E4-5*基础信息!E4,2))”,这里运用ROUND函数表示对事假或病假扣除金额四舍五入,IF函数表示如果没有请事假或病假则扣额为零,否则按事假或病假标准扣除。需注意公式中＄表示绝对引用符号不可省略,复制公式时引用范围不会发生变化。根据三金(养老保险、失业保险、医疗保险)扣除标准分别为基本工资、岗位工资、综合补贴之和的8%、1%、2%,在单元格J3中输入“=ROUND((C3+D3+E3)*11%,2)”。
第五步,为了方便利用函数计算个人所得税,增加“应税所得额”列,平时可将其隐藏。在单元格M3中输入“=IF((J3-K3-1600)O,J3-K3-1600,0)”表示应税所得额只有超过免征额1600元才征税,否则不征税。在单元格N3中输入“=ROUND(IF(M3=500,M3*0.05,IF(M3=2000,M3*10%-25,IF(M3=5000,M3*15%-125,IF(M3=20000,M3*20%-375,IF(M3=40000,M3*25%-1375,M3*30%-3375))),2)”,这里运用IF函数的层层嵌套计算个人所得税。
第六步,在单元格J3中输入“=C3+D3+E3+F3+G3-H3-13”,表示“应发工资=基本工资+岗位工资+综合补贴+加班+浮动工资-事假、病假扣除”;在单元格03中输入“=K3+L3+N3”表示扣除合计总额;在单元格P3中输入“=J3-03”表示实发工资总额;在单元格B12中输入“=SUBTOTAL(3,工资表!A3:A11)&“人””,这里运用了SUBTOTAL动态计数函数计算出人数;在单元格C12中输入“SUM(C3:C11)”表示对所有人员的基本工资求和。最后再利用“填充柄”,自动生成相应数据。
三、建立汇总表
建立如表3所示的工资汇总表。工资汇总表中主要运用了SUMIF( )求和函数,从其它工作表中获取数据。其功能是在满足指定的统计条件下,对给定数据区域中的某一栏目范围进行累计求和。在单元格B3中输入“=SUMIF(工资表!＄A＄3:＄A＄11,＄A3,工资表!＄C＄3:＄C＄11)”,表示将“部门”列中所有“生产”的行所对应的“基本工资”合计。 “生产”部门其余工资各项目汇总公式均参照单元格B3填写,只需将单元格B3中需要求和的对象“工资表!＄C＄3:＄C＄11”变动一下,其余均不变,如在单元格C3中输入“=SUMIF(工资表!＄A＄3:＄A＄11,＄A3,工资表!＄D＄3:＄D＄11)”。在单元格B7中输入“=SUM(B3:B6)”,表示对汇总数求和,再利用“填充柄”,生成相应汇总数及合计数。
四、建立银行表
建立如表4所示的银行表。在单元格A2中输入“=基础信息!B4”,利用“填充柄”,生成各员工姓名。“账号”、“实发工资”自动产生,主要运用了VLOOKUP()函数,其功能是搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。在单元格中B2中输入“=IF(A2:”“,”“,VLOOKUP(A2,基础信息!B4:C21,2,FALSE))”,表示如果姓名A2为空格,那么账号B2为空,否则账号B2为“基础信息”表中姓名和账号两列区域中第一列的数值等于单元格A2中的数值的同一行的第二列数值。同样在单元格C2中输入“=IF(B2=”“,”“,VLOOKUP(A2,工资表!B3:P11,15,FALSE))”,再利用“填充柄”,生成各员工的账号及实发工资。
五、创建“宏”生成工资条
为了提高工作效率,可以通过创建“宏”生成工资条。本文创建工资条通过录制交互操作过程逐步创建,此种方法相对简单,具体步骤如下:
第一步,启用宏。用鼠标单击[工具]-[宏]-[录制新宏],键入宏名“工资条”;在“保存在(I):”下拉框中,选择“当前工作簿”;在“说明(D):”编辑框中键入相应的文字;单击【确定】。
第二步,录制宏。按住CTRL健的同时,沿工作表标签行拖动“工资表”标签,建立副本,并双击将“工资表(2)”改名为“工资条”,然后将“工资条”中的数据编辑修改如表5所示。注意,此步骤中每一步都应小心谨慎操作,因为若在录制宏时出现失误,更正失误的操作也会记录在宏中。
第三步,单击“停止录制”按钮,结束宏录制。
第四步,创建“按钮”。用鼠标单击【按钮】图标,在“工资表”划一个小方框,选中小方框,右击鼠标→编辑文字→输入“工资条”→右击鼠标→单击“指定宏(N)…”→选择“工资条”→“确定”。以后每次要生成工资条时。只要将原“工资条”删除,按一下“工资条”按钮,就可以完成上述一系列操作,自动生成“工资条”。
工资汇总表等
一套工资核算模型创建完成,下月进行工资核算时,可以先复制一下该文件,然后只要将“基础信息”表中月份、本月出勤天数、事假天数、病假天数、加班天数、浮动工资、房租等数据输入,工资表、工资汇总表、银行表、工资条都将自动生成,大大提高了工作效率。另外在使用过程中应注意以下几点:(J)在输入数字时,小数点有时输成中文句号,可以选取“基础信息”表中的D、E、F、G、H五列,单击菜单[数据]-[有效性]-[输入法模式]中选取“关闭”。(2)为防止单元格中创建的公式被误删或更改,可以先选中有公式的单元格,在菜单[格式]-[单元格]-[保护]中的[锁定]前打勾,然后单击菜单[工具]-[保护]-[保护工作表],输入密码。(3)为限制不同用户查看和修改文件,在文件创建完成后,可以输入打开权限密码和修改权限密码,具体单击[文件]-[另存为]-[工具]-[常规选项],在[打开权限密码]和[修改权限密码]中输入不同的密码。
下一篇：ASP 3.0高级编程（一）