制作员工工资管理表课件

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,11/7/2009,#,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,4,章 制作员工工资管理表,4.1,获取外部数据,Excel 2003,可以获取的外部数据库文件类型有很多种，如：,Access,、,FoxPro,、,dBase,、,SQL Server,、,Lotus,、,Oracle,、,HTML,文件、,Web,档案、,XML,文件和文本数据等。对于这些文件，,Excel,都能访问，并能将这些文件转化为,Excel,中的表格形式。,步骤,1,启动,Excel 2003,后，单击“常用”工具栏上的的“打开”按钮“”，或选择“文件”,|“,打开”命令。,步骤,2,弹出“打开”对话框，在“查找范围”下拉列表中选择“员工基本情况表,.xls”,文件位置，如图所示。选中要导入的文件后，单击“打开”按钮，即可获取所要的数据记录。,4.2,快速准确录入数据,步骤,1,在,Excel,中打开“员工基本情况表,.txt”,，在“职务”列右边插入“性别”列。如图所示。,步骤,2,鼠标右击,E3,单元格，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“自定义”选项，然后在右侧的“类型”文本框中输入“,=1“,男”；,=2“,女”（英文状态下输入符号），如图所示。,步骤,3,单击“确定”按钮，返回工作表中，复制,E3,单元格，再选中“,E4,：,E22”,单元格区域，右键单击该区域，在弹出的快捷菜单中选择“选择性粘贴”命令，在出现的“选择性粘贴”对话框中“粘贴”选项下单击“格式”单选按钮，如图所示。再单击“确定”按钮即可完成在“,E4,：,E22”,单元格区域复制公式。,步骤,4,在“性别”列的单元格中输入“,1”,或者“,2”,，按下“,Enter”,回车键即可输入“男”或“女”，如图所示。,步骤,5,将“性别”列数据输入完成后，再将“底薪”列、“工龄工资”列、“岗位工资”列和“基本工资”列的数据统一设置为“货币型”，并保留,2,位小数，并以“员工基本情况表,.xls”,保存在指定位置。效果图如图所示。,4.3,使用公式和函数,公式是单元格中一系列值、单元格的引用、名称或运算符的组合，可生成新的值。可利用公式对工作表的数据进行加、减、乘、除等运算。在公式中，不但可以引用同一个工作表中的不同单元格，也可以引用同一工作簿中的不同工作表中的单元格，还能引用其他工作簿任意工作表中的单元格。,使用公式求基本工资,Excel,中的公式由等号、单元格和运算符号三部分组成，以“,=”,开头，公式中也可以出现函数。公式实质上也是单元格数据，,Excel,会将输入单元格中的以等号开头的数据自动认为是公式。接下来介绍以简单公式求出“基本工资记录表”中的基本工资项，具体操作步骤如下。,（薪酬福利）,步骤,1,打开“员工基本情况表,.xls”,，在“基本工资”列中的单元格,K3,中输入公式“,=H3+I3+J3”,，如图所示。,步骤,2,按编辑栏中的“ ”输入按钮输入公式，或直接按“,Enter”,回车键确认公式输入，此时单元格,K3,中的数据如图所示。,步骤,3,拖动,K3,单元格右下角的填充柄，用自动填充功能计算出所有员工的基本工资，如图所示。,引用函数求年龄,函数是,Excel 2003,内置的一种复杂公式，其功能是完成某些特定计算。所有的函数都包括一个函数名和一系列参数，其格式为“函数名（参数值,1,，参数值,2,）”。函数名表示将执行的操作，参数是函数中用来执行操作或计算的数值。合理地使用函数，可以大大节省用户的输入时间，简化公式的输入。,步骤,1,打开“员工基本情况表,.xls”,，单击“年龄”列中的单元格,G3,，在其中输入公式“,=YEAR,（,TODAY,（）,-YRAR,（,F3,）”公式，如图所示。按“,Enter”,回车键确认公式输入，则单元格,G3,显示“,1900,年,2,月”。,步骤,2,右键单击“,G3”,单元格，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“数字”选项或“常规”选项，再单击“确定”按钮。返回工作表中，拖动,G3,单元格右下角的填充柄，用自动填充功能计算出所有员工的年龄，如图所示。,4.4,创建员工考勤表,每个单位都有其考勤制度，如：,（,1,）迟到、早退与旷工制度。员工迟到或早退在,1,小时以内，扣当月工资的,1%,。,（,2,）旷工。员工迟到、早退,1,小时以上，记旷工一次，扣当月工资,10%,。,（,3,）请假制度。请假的时间按小时计算，若一个月累计请假时间不超过,8,小时，则不扣工资。超过,8,小时以外的，,4,小时以内按,0.5,天计算，,4,小时以外的按,1,天计算。,（,4,）事假：员工因私人原因请假，事假期间不支付薪水。,（,5,）病假：员工因病请假，支付,80%,的薪水。,（,6,）对出全勤的员工，全勤奖为当月工资的,10%,。,国家法定假均支付全额薪水，因工作需要加班，加班费,100/,天。,创建考勤表,步骤,1,在,Excel,中打开“员工基本情况表,.xls”,，将其中的“,Sheet2”,工作表重命名为“,12,月份考勤表”，然后输入表头字段“姓名”、“部门”和“日期”。,步骤,2,打开“员工基本工资表”工作表，选中,B3,：,C22,单元格区域，将其内容复制到“,12,月份考勤表”中。,步骤,3,在,D1,单元格中输入“,12,月,1,日”，拖动,D1,单元格右下角的填充柄填充日期，单击“自动填充选项”按钮，从展开的下拉列表中单击“以工作日填充”选项，如图所示。此时系统会自动将周六和周日去掉，不计入考勤日期。,步骤,4,鼠标右击选定的,D1,：,Y1,单元格区域，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“自定义”选项，然后在右侧的“类型”文本框中输入“,d“,日”（英文状态下输入符号），单击“确定”按钮，返回工作表中，设置日期格式后效果如图所示。,录入考勤记录,步骤,1,在“,12,月考勤表”中的“日期”列输入每名员工的“上班”和“下班”标题，然后选中,D2,：,Z41,区域，鼠标右击,D2,：,Z41,区域，从弹出的快捷菜选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“时间”选项，然后在右侧的“类型”列表框中单击“,13,：,00”,选项，如图所示。,步骤,2,单击“确定”按钮，返回工作表，在,D2,：,Z41,单元格区域内输入每位员工的上下班时间，如图所示。,引用函数统计出勤情况,单位考勤制度规定上下班时间分别为,8,：,00,和,17,：,30,；若大于,8,：,00,小于,9,：,00,上班的，则视为迟到，或大于,16,：,30,小于,17,：,30,，则视为早退。若大于,9,：,00,上班或小于,16,：,30,下班则视为旷工。（薪酬管理）,步骤,1,在日期行之后的单元格中依次输入“迟到”、“早退”、“旷工”、“病假”和“事假”等需要统计的列标题。并将这几列单元格上下两两合并，对应于姓名行。如图所示。,步骤,2,统计员工“迟到”结果。单击,AA2,单元格，在其中输入公式：,=SUMPRODUCT(C2,：,Z2TIMEVALUE(”8:00”)*(C2,：,Z2TIMEVALUE(”16:30”)*(C3:Z3TIMEVALUE(”9:00”)*(C2:Z30,H2*0.01*C2)+IF(D20,H2*0.01*D2)+IF(E20,H2*0.1*E2)+IF(F20,H2/22*0.2*F2)+IF(G20,H2/22*G2),，按,Enter,键即可得到第一名员工应扣的工资，利用自动填充功能填充该列的其他单元格，并设置数字格式为数值，保留小数点,2,位。,步骤,7,根据全勤奖制度，应奖励全勤员工当月工资的,10%,作奖金。单击,J2,单元格，在其中输入公式：,=IF(I2=0,H2*0.1,0),，使用自动填充功能完成该列数据的填充。数据并保留,2,位小数位。,4.6,创建并编辑员工工资管理表,创建员工工资管理表,步骤,1,在“员工基本情况表”中插入一新工作表，将其标签名改为“,12,月份工资管理表”，然后在工作表中输入标题“员工工资管理表”，在第,2,行输入表头字段：月份、员工编号、姓名、部门、基本工资、住房补贴、交通补贴、全勤奖、考勤扣款、社保金、应发工资、应扣所得税和实发工资。,步骤,2,引用数据。使用引用公式引用“员工基本工资表”中“员工编号”、“姓名”、“部门”、“基本工资”等列数据。,步骤,3,输入“月份”列数据。先选中,A3,：,A22,单元格区域，设置单元格式格式如下图所示。然后在,A3,单元格中输入“,2008-12-31”,，再按住,Ctrl,键把月份复制到相应的单元格。,步骤,4,打开第,4,章建立的“员工福利表,.xls”,，返回“,12,月份工资管理表”，单击,F3,单元格，在其中输入引用公式：,=,员工福利表,.xlsSheet1!D3,，在“员工工资管理表”中“住房补贴”列中引用“员工福利表,.xls”Sheet1,中相对应的数据。同理，在“员工工资管理表”中“交通补贴”和“社保金”列中引用“员工福利表,.xls”Sheet1,中相对应的数据，并填充完所选列数据。,步骤,5,引用“,12,月份考勤统计表”中“应扣工资”和“全勤奖”两列数据。在“,12,月份工资管理表”中分别单击,H3,和,I3,单元格，在其中分别输入引用公式：,=12,月份考勤统计表,!J2,和,=12,月份考勤统计表,!I2,，再选中,H3,：,I3,单元格区域，利用填充柄填充完其余数据。,步骤,6,设置数据格式。在“员工工资管理表”中，右键单击选中的,E3,：,M22,单元格区域，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“货币”选项，选择样式并保留小数位,2,位。,公式和函数运用,一般工资表中都包含所得税一项，而所得税是根据员工的应发工资进行计算。而“应发工资”是利用“员工工资管理表”中的“基本工资”、“住房补贴”、“交通补贴”、“全勤奖”等计算得出的。“实发工资”是“应发工资”减去“所得税”、“考勤扣款”、“社保金”得出来的。,在“员工工资管理表”中至少要建立,3,个公式，一个是计算“所得税”，一个是计算“应发工资”，一个是计算“实发工资”。,表,5-1,所得税税率及扣除金额（工资、薪金所得适用）,步骤,1,在“应发工资”一列中,K3,单元格中输入公式：,=E3+F3+G3+H3-I3-J3,，或用鼠标单击引用相应的单元格，按下,Enter,键，将根据公式计算出相应的值。拖动,K3,单元格右下角的填充柄，用自动填充功能计算出所有员工的应发工资。,步骤,2,计算“所得税”。在“应发工资”一列中,L3,单元格中输入公式：,=IF(K32000,0,IF(K3-2000)500,(K3-2000)*0.05,IF(K3-2000)2000,(K3-2000)*0.1-25,IF(K3-2000)TIMEVALUE(”8:00”)*(C2：Z2TIMEVALUE(”16:30”)*(C3:Z3TIMEVALUE(”17:30”)。单击AD2单元格，在其中输入公式：=COUNTIF（C2：Z3，”病假”），按下“Enter”回车键，即可统计出员工请病假的结果，如图所示,