员工工资管理系统

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,8,章 员工工资管理系统,在一个企业中，每一个月人事部门都需要统计员工的工资。由于员工的工资涉及到员工的出勤量、福利以及奖金等数据，还需要对个人所得税等进行计算，所以，这个时候如果手工计算员工工资的工作量是非常大的。本章将介绍利用,Excel,来制作一个员工工资管理系统，并能够自动生成工资条。,1,创建员工出勤统计表,员工出勤统计表是用来记录企业员工出勤情况的表格，可以参考上一章内容创建。,2,创建员工福利表,“员工福利表”是记录员工福利情况的基本表格，主要应包括“员工编号”、“员工姓名”、“所在部门”、“住房补贴”、“车费补贴”和“保险金”等。其中，“员工编号”、“员工姓名”、和“所在部门”的字段值应与“员工出勤统计表”中的数据一致。,3,创建员工奖金表,“员工奖金表”是记录员工奖金情况的基本表格，主要应包括“员工姓名”、“所在部门”和“奖金”等。同样，这里的“员工姓名”和“所在部门”的字段值也要与“员工出勤统计表”和“员工福利表”中的数据保持一致。,4,创建员工工资表,“员工工资表”的作用就是汇总上述,3,个表格中的数据，然后计算出每一位员工最后应该获得的实际工资。因此，“员工工资表”应该包括“员工编号”、“员工姓名”、“所在部门”、“基本工资”、“奖金”、“住房补助”、“保险金”、“应发金额”、“扣税所得额”、“个人所得税”以及“实发工资”等字段。,根据上述内容创建“员工工资表”的具体步骤如下：,插入一个新工作表，并将其更名为“员工工资表”。,在“员工工资表”中输入相应的字段名称。,创建完“员工工资表”后，就需要输入数据了。首先，用自动填充功能输入员工编号。,1,输入出勤数据,因为包括“员工姓名”、“所在部门”、“请假扣款”在内的数据都在“员工出勤统计表”中，所以，可以直接引用其中的数据。但如果对每一组数据都进行搜索数据设置的话将非常麻烦，为此，可以使用,Excel,中提供的“定义名称”功能来简化操作。,定义名称的具体步骤如下：,切换到“员工出勤统计表”中，选择单元格区域“,A2,：,E22”,，然后单击,【,插入,】/【,名称,】/【,定义,】,菜单项，打开,【,定义名称,】,对话框。,在“在当前工作簿中的名称”文本框中输入“出勤表”,.,然后单击“添加”按钮即可完成数据名称的添加。,单击“关闭”按钮返回工作表中。,注：定义名称还有一种更简洁的方法：在工作表中选中单元格区域“,A2,：,M22”,，在名称框中输入“出勤表”，即可定义该名称。,下面利用定义,VLOOKUP,函数输入出勤数据，具体的操作步骤如下：,切换到“员工工资表”中，选中单元格,B3,，单击,【,插入,】/【,函数,】,菜单项打开“插入函数”对话框，然后在“或选择类别”下拉列表中选择“查找与引用”选项，在“选择函数”列表框中选择“,VLOOKUP”,选项,单击“确定”按钮打开“函数参数”对话框，然后在“,Lookup_value,”,文本框中输入“,A3”,，在“,Table_array,”,文本框中输入“出勤表”，在“,Col_index_num,”,文本框中输入“,2”,，在“,Range_lookup,”,文本框中输入“,0”.,单击“确定”按钮返回工作表中，此时在单元格,B3,中使会显示计算结果。,用同样的方法为单元格,C3,插入公式“,=VLOOKUP(A3,，出勤表，,4,，,0)”,，便会得到入“所在部门”的值。,用同样的方法为单元格,I3,插入公式“,=VLOOKUP(A3,，出勤表，,12,，,0)”,，便会得到“请假扣款”的值。,2,输入奖金数据,按照前面的方法为“员工奖金表”中的单元格区域定义区域名称“奖金表”。,在“员工工资表”中选中单元格,E3,，在其中输入公式“,=VLOOKUP(A3,，奖金表，,5,，,0)”,，便会得到“奖金”的值。,3,输入福利数据,按照前面的方法为“员工奖金表”中的单元格区域定义名称“福利表”。,在“员工工资表”中选中单元格,F3,，在其中输入公式“,=VLOOKUP(A3,，福利表，,4,，,0)”,，然后按下“回车”键，即可在单元格,F3,中显示“住房补助”的计算结果。,选中单元格,G3,，在其中输入公式“,=VLOOKUP(A3,，福利表，,5,，,0)”,，在单元格,G3,中便会得到“车费补助”列的值。,选中单元格,H3,，在其中输入公式“,=VLOOKUP(A3,，福利表，,6,，,0)”,，在单元格,H3,中便会得到“保险金”的值。,选择单元格区域“,B3,：,I3”,，然后利用自动填充功能将公式复制到其他的单元格区域即可得到结果,4,计算应发金额,员工工资应发金额的计算公式为：,应发金额,=,基本工资,+,奖金,+,住房补助,+,车费补助,-,保险金,-,请假扣款,根据上述公式，在单元格,J3,中输入公式“,=D3+E3+F3+G3-H3-I3”,即可计算出“应发金额”的值。然后利用自动填充功能将该公式填充到该列的其他单元格中得到结果。,5,计算扣税所得额,在工资表中对个人所得税的计算相对要复杂一些。不是所有的人都按照相同的税率来纳税，也不是全部的收入都要纳税。,“扣税所得额”就是需要纳税的那一部分收入。不同的“扣税所得额”有不同的税率和速算扣除数。,个人所得税计算表,扣税所得额,税率,速算扣除数（元）,500,5%,0,2000,10%,25,5000,15%,125,20000,20%,375,40000,25%,1375,60000,30%,3375,80000,35%,6375,=100000,45%,15375,“扣税所得额”为“应发工资”减去,1600,元个税起征点后超出的数额，如果这个值小于零（应发工资少于,1600,元）就不必交纳个人所得税，若这个值大于零（应发工资多于,1600,元）就要交纳个人所得税。,根据上述内容计算“扣税所得额”的具体步骤如下：,选中单元格,K3,，单击,【,插入,】/【,函数,】,菜单项，打开“插入函数”对话框,单击“确定”按钮打开“函数参数”对话框，然后在“,Logical_test,”,文本框中输入“,J31600”,，在“,Value_if_true,”,文本框中输入“,0”,，在“,Value_if_false,”,文本框中输入“,J3-1600”,，如图,8-10,所示。,单击“确定”按钮返回工作表中，此时在单元格,K3,中便会显示计算结果。,利用自动填充功能将公式复制到该列的其他单元格中。,6,计算个人所得税,计算出了“扣税所得额”，下面再来求“个人所得税”，即需要交纳的税款。,在单元格,L3,中输入如下公式：,=,IF(,K3=500,K3=2000,K3=5000,k320000),k3*20%-375),),),),7,计算实发金额,“实发金额”就是“应发金额”减去“个人所得税”。只要在单元格,M3,中输入“,=J3-L3”,，然后再对该列的其他单元格进行公式的复制即可。,DSUM,函数的作用是返回数据列表或者数据库的列中满足指定条件的数字的和。,DSUM,函数的语法结构如下：,DSUM(database,field,criteria,),各个参数的含义如下：,database,是构成列表或者数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。,field,用于指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。,field,可以是文本，即两端带引号的标志项；此外,field,也可以是代表列表中数据列位置的数字，其中,1,表示第一列，,2,表示第二列，依此类推。,criteria,为一组包含给定条件的单元格区域。可以为参数,criteria,指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,了解了函数的作用后，首先在工资表中的单元格区域“,C25,：,D29”,中输入如下图所示的数据，然后对该区域进行简单的格式化设置。,利用函数,DSUM,计算工资额在各个部门中的汇总的具体步骤如下：,选中单元格,D26,，单击,【,插入,】/【,函数,】,菜单项，打开“插入函数”对话框，然后在“或选择类别”下拉列表中选择“数据库”选项，在“选择函数”列表柜中选择“,DSUM”,选项，,单击“确定”按钮打开“函数参数”对话框，然后在“,Database”,文本框中输入“,C2,：,M22”,，在“,Field”,文本框中输入“,M2”,，在“,Criteria”,文本框中输入“,C25,：,C26”,，单击“确定”按钮返回工作表中，此时在单元格,D26,中便会显示计算结果。,在单元格,D27,中输入如下公式：,=SUM(DSUM(C2:M22,M2,C25:C27),-D26),然后按下“回车”键，在单元格,D27,中便会显示计算结果。,注：在上面的公式中，,DSUM,函数的设定条件为单元格区域“,C25,：,27”,，即计算了数据区域“,C2,：,M22”,中所有人事部和财务部员工的工资总和，因此要用,SUM,函数将人事部员工的工资总和减去后才能够得到财务部员工的工资总和。,同样的道理，在单元格,D28,中输入如下公式：,=SUM(DSUM(C2:M22,M2,C25:C28),-D26-D27),然后按下“回车”键，在单元格,D28,中便会显示“销售部”计算结果。,在单元格,D29,中输入如下公式：,=SUM(DSUM(C2:M22,M2,C25:C29),-D26-D27-D28),然后按下“回车”键，在单元格,D29,中便会显示“业务部”的计算结果。,制作工资条,1,INDEX,函数,该函数返回的是表或者区域中的值或值的引用。,INDEX,函数有两种形式：数组合引用。数组形式通常返回竖直或者数值数组，引用形式通常返回引用。当函数,INDEX,的第一个参数为数组常数时则使用数组形式。数组形式的,INDEX,函数的语法结构如下：,INDEX(array,row_num,column_num,),2,ROW,函数,ROW,函数的作用是返回引用的行号。该函数的语法如下：,ROW(reference,),3,COLUMN,函数,COLUMN,函数的作用是返回给定引用的列标。该函数的语法结构如下：,COLUMN(reference,),制作工资条的具体步骤如下：,插入一个新的工作表并将其重命名为“工资条”，然后在“工资条”中的单元格,Al,中输入如下公式：,=,IF(,MOD(ROW(),3)=0,IF(,MOD(ROW(),3)=1,员工工资表,!A$2,INDEX(,员工工资表,!$A:$M,(ROW()+4)/3+1,COLUMN(),),),),输入完后按下“回车”键，在单元格,A1,中就会显示计算结果。,分别在单元格“,B1,：,M1”,单元格中输入以下公式：,B1,：,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员工工资表,!B$2,INDEX(,员工工资表,!$A:$M,(ROW()+4)/3+1,COLUMN(),C1,：,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员工工资表,!C$2,INDEX(,员工工资表,!$A:$M,(ROW()+4)/3+1,COLUMN(),D1,：,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员工工资表,!D$2,INDEX(,员工工资表,!$A:$M,(ROW()+4)/3+1,COLUMN(),E1,：,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员工工资表,!E$2,INDEX(,员工工资表,!$A:$M,(ROW()+4)/3+1,COLUMN(),F1,：,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员工工资表,!F$