制作员工工资管理表.

Presentation on theme: "制作员工工资管理表."— Presentation transcript:

1 制作员工工资管理表

2 4.1 获取外部数据 Excel 可以获取的外部数据库文件类型有很多种，如：Access、FoxPro、dBase、SQL Server、Lotus、Oracle、HTML文件、Web档案、XML文件和文本数据等。对于这些文件，Excel都能访问，并能将这些文件转化为Excel中的表格形式。

3

4 4.2 快速准确录入数据 步骤1 在Excel中打开“员工基本情况表.txt”，在“职务”列右边插入“性别”列。如图所示。

5 步骤2 鼠标右击E3单元格，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“自定义”选项，然后在右侧的“类型”文本框中输入 [=1]“男”；[=2]“女” ，如下图。

6 步骤3 单击“确定”按钮，返回工作表中，复制E3单元格，再选中“E4：E22”单元格区域，右键单击该区域，在弹出的快捷菜单中选择“选择性粘贴”命令，在出现的“选择性粘贴”对话框中“粘贴”选项下单击“格式”单选按钮，如图。再单击“确定”按钮即可完成在“E4：E22”单元格区域复制公式。

7 步骤4 在“性别”列的单元格中输入“1”或者“2”，按下“Enter”键即可输入“男”或“女”，如图。

8 步骤5 将“性别”列数据输入完成后，再将“底薪”列、“工龄工资”列、“岗位工资”列和“基本工资”列的数据统一设置为“货币型”，并保留2位小数，并以“员工基本情况表.xls”保存在指定位置。

9 4.3 使用公式和函数 公式是单元格中一系列值、单元格的引用、名称或运算符的组合，可生成新的值。
4.3 使用公式和函数 公式是单元格中一系列值、单元格的引用、名称或运算符的组合，可生成新的值。 可利用公式对工作表的数据进行加、减、乘、除等运算。 在公式中，不但可以引用同一个工作表中的不同单元格，也可以引用同一工作簿中的不同工作表中的单元格，还能引用其他工作簿任意工作表中的单元格。

10 4.3.1　使用公式求基本工资 Excel中的公式由等号、单元格和运算符号三部分组成，以“=”开头，公式中也可以出现函数。公式实质上也是单元格数据，Excel会将输入单元格中的以等号开头的数据自动认为是公式。 接下来求“基本工资记录表”中的基本工资项，操作步骤如下

11 步骤1　打开“员工基本情况表.xls”，在“基本工资”列中的单元格K3中输入公式“=H3+I3+J3”，如图

12 步骤2　点击编辑栏中的“ ”输入按钮输入公式，或直接按“Enter”回车键确认公式输入，此时单元格K3中的数据如图

13 步骤3　拖动K3单元格右下角的填充柄，用自动填充功能计算出所有员工的基本工资

14 4.3.2　引用函数求年龄 函数是Excel内置的一种复杂公式，其功能是完成某些特定计算。所有的函数都包括一个函数名和一系列参数，其格式为“函数名（参数值1，参数值2…）”。函数名表示将执行的操作，参数是函数中用来执行操作或计算的数值。合理地使用函数，可以大大节省用户的输入时间，简化公式的输入。

15 步骤1 打开“员工基本情况表.xls”，单击“年龄”列中的单元格G3，在其中输入公式“=YEAR(TODAY( )) - YEAR(F3)”，按“Enter”键确认公式输入，则单元格G3显示“1900年2月”。 尝试使用公式 =(TODAY( )-F3)/365

16 步骤2 右击“G3”单元格，选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“数字”选项或“常规”选项，单击“确定” 返回工作表，拖动G3单元格右下角的填充柄，用自动填充功能计算出所有员工的年龄，如图

17 4.4 创建员工考勤表 每个单位都有其考勤制度，如： （1）迟到、早退与旷工制度。员工迟到或早退在1小时以内，扣当月工资的1%。 （2）旷工。员工迟到、早退1小时以上，记旷工一次，扣当月工资10%。 （3）请假制度。请假的时间按小时计算，若一个月累计请假时间不超过8小时，则不扣工资。超过8小时以外的，4小时以内按0.5天计算，4小时以外的按1天计算。 （4）事假：员工因私人原因请假，事假期间不支付薪水。 （5）病假：员工因病请假，支付80%的薪水。 （6）对出全勤的员工，全勤奖为当月工资的10%。 国家法定假均支付全额薪水，因工作需要加班，加班费100/天。

18 4.4.1　创建考勤表 步骤1 在Excel中打开“员工基本情况表.xls”，将其中的“Sheet2”工作表重命名为“12月份考勤表”，然后输入表头字段“姓名”、“部门”和“日期”。 步骤2 打开“员工基本工资表”工作表，选中B3：C22单元格区域，将其内容复制到“12月份考勤表”中。

19 步骤3 在D1单元格中输入“12月1日”，拖动D1单元格右下角的填充柄填充日期，单击“自动填充选项”按钮，从展开的下拉列表中单击“以工作日填充”选项，如图。此时系统会自动将周六和周日去掉，不计入考勤日期。

20 步骤4 右击选定的D1：Y1单元格区域，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“自定义”选项，然后在右侧的“类型”文本框中输入d“日”，单击“确定”按钮，返回工作表中，设置日期格式后效果如下图。

21 4.4.2　录入考勤记录 步骤1 在“12月考勤表”中的“日期”列输入每名员工的“上班”和“下班”标题，然后选中D2：Z41区域，鼠标右击D2：Z41区域，从弹出的快捷菜选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“时间”选项，然后在右侧的“类型”列表框中单击“13：00”选项，如图

22 步骤2 单击“确定”按钮，返回工作表，在D2：Z41单元格区域内输入每位员工的上下班时间，如图

23 4.4.3 引用函数统计出勤情况 单位考勤制度规定上下班时间分别为8:00和17:30 若大于8:00小于9:00上班的，则视为迟到
4.4.3　引用函数统计出勤情况 单位考勤制度规定上下班时间分别为8:00和17:30 若大于8:00小于9:00上班的，则视为迟到 或大于16:30小于17:30，则视为早退 若大于9:00上班或小于16:30下班则视为旷工

24 步骤1 在日期行之后的单元格中依次输入“迟到”、“早退”、“旷工”、“病假”和“事假”等需要统计的列标题。并将这几列单元格上下两两合并，对应于姓名行。如图

25 步骤2 统计员工“迟到”结果。单击AA2单元格，在其中输入公式： =SUMPRODUCT( (D2:Z2>TIMEVALUE("8:00")) * (D2:Z2<TIMEVALUE("9:00")) )。 按下“Enter”回车键，统计出迟到的结果，如图

26 SUMPRODUCT( ) 此处使用SUMPRODUCT进行多条件计数
语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n)) 作用：统计同时满足条件1、条件2到条件n的记录的个数。 实例：=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")) 公式解释：统计性别为男性且职称为中级职称的职工的人数

27 步骤3 统计员工“早退”结果。单击AB2单元格，在其中输入公式： =SUMPRODUCT( (C3:Z3>TIMEVALUE("16:30")) * (C3:Z3<TIMEVALUE("17:30")) )。 按下“Enter”回车键，统计出早退的结果，如图

28 步骤4 统计员工“旷工”结果。单击AC2单元格，在其中输入公式：=SUMPRODUCT( (C2:Z3>TIMEVALUE("9:00")) * (C2:Z3<TIMEVALUE("16:30")) )。按下“Enter”回车键，统计出早退的结果，如图

29 步骤5 统计员工“病假”结果。单击AD2单元格，在其中输入公式：=COUNTIF(C2:Z3, "病假")，按下“Enter”回车键，即可统计出员工请病假的结果，如图

30 步骤6 统计员工“事假”结果。单击AE2单元格，在其中输入公式：=COUNTIF(C2:Z3, "事假")，按下“Enter”回车键，即可统计出员工请事假的结果，如图

31 步骤7 选中AA2：AE2单元格区域，然后将鼠标移至AE2单元格右下角处，当指针变为实心十字形时，按住鼠标左键拖至AE41时，释放鼠标左键，即可完成其他员工的考勤统计工作，如图。

32 4.5 创建出勤统计表 4.5.1　创建考勤表 步骤1 在“员工基本情况表”中插入一新工作表，将其标签名改为“12月份考勤统计表”，然后在工作表中输入标题“2008年12月份考勤统计表”，在第2行输入表头字段：姓名、部门、迟到次数、早退次数、旷工、病假天数、事假天数、本月工资、应扣工资和全勤奖。

33 步骤2 使用引用公式引用“员工基本工资表”工作表“姓名”数据。单击“12月份考勤统计表”A2单元格，在其中输入公式“=员工基本工资表
步骤2 使用引用公式引用“员工基本工资表”工作表“姓名”数据。单击“12月份考勤统计表”A2单元格，在其中输入公式“=员工基本工资表!B3”；或只输入“=”，再单击“员工基本工资表”中对应的单元格如B3，按“Enter”回车键确认输入，如图

34 步骤3 利用自动填充功能填充该列中的其他单元格。
在“12月份考勤统计表”中用同样方式引用“员工基本工资表”中的“部门”和“基本工资”列数据；

35 4.5.2　不同工作表利用函数统计数据 VLOOKUP( )函数的作用是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列的数值。 格式： VLOOKUP (lookup_value, table_array, column_index_num, range_lookup)

36 步骤1 单击C2单元格，在其中输入公式：=VLOOKUP(A3,'12月份考勤表'
步骤1 单击C2单元格，在其中输入公式：=VLOOKUP(A3,'12月份考勤表'!$A$1:$AE$41,27,0)，按Enter键即可得到该员工迟到的次数，并利用自动填充功能填充该列的其他单元格，如图

37 步骤2 单击D2单元格，在其中输入公式： =VLOOKUP(A3, '12月份考勤表'
步骤2 单击D2单元格，在其中输入公式： =VLOOKUP(A3, '12月份考勤表'!$A$1:$AE$41,28,0)，按Enter键即可得到该员工早退的次数，并利用自动填充功能填充该列的其他单元格。 步骤3 单击E2单元格，在其中输入公式：=VLOOKUP(A3,'12月份考勤表'!$A$1:$AE$41,29,0)，按Enter键即可得到该员工旷工的次数，并利用自动填充功能填充该列的其他单元格。

38 步骤4 单击F2单元格，在其中输入公式： =VLOOKUP(A3, '12月份考勤表'
步骤4 单击F2单元格，在其中输入公式： =VLOOKUP(A3, '12月份考勤表'!$A$1:$AE$41,30,0)，按Enter键即可得到该员工病假天数，并利用自动填充功能填充该列的其他单元格。 步骤5 单击E2单元格，在其中输入公式： =VLOOKUP(A3, '12月份考勤表'!$A$1:$AE$41,31,0)，按Enter键即可得到该员工事假天数，并利用自动填充功能填充该列的其他单元格。效果如图。

39 按Enter键即可得到第一名员工应扣的工资，利用自动填充功能填充该列的其他单元格，并设置数字格式为数值，保留小数点2位。
步骤6 计算应扣工资。制度中规定： 迟到和早退扣工资的1%/次； 旷工扣工资的10%/次； 病假扣当天工资的20%； 事假扣当天工资。 单击I3单元格，在其中输入公式： =IF(C3>0,H3*0.01*C3)+ IF(D3>0,H3*0.01*D3)+ IF(E3>0,H3*0.1*E3)+ IF(F3>0,H3/22*0.2*F3)+ IF(G3>0,H3/22*G3)， 按Enter键即可得到第一名员工应扣的工资，利用自动填充功能填充该列的其他单元格，并设置数字格式为数值，保留小数点2位。

40 步骤7 根据全勤奖制度，应奖励全勤员工当月工资的10%作奖金。单击J3单元格，在其中输入公式：=IF(I3=0,H3

41 4.6 创建并编辑员工工资管理表 4.6.1 创建员工工资管理表
4.6 创建并编辑员工工资管理表 4.6.1　创建员工工资管理表 步骤1 在“员工基本情况表”中插入一新工作表，将其标签名改为“12月份工资管理表”，然后在工作表中输入标题“员工工资管理表”，在第2行输入表头字段：月份、员工编号、姓名、部门、基本工资、住房补贴、交通补贴、全勤奖、考勤扣款、社保金、应发工资、应扣所得税和实发工资。

42 步骤2 引用数据。使用引用公式引用“员工基本工资表”中“员工编号”、“姓名”、“部门”、“基本工资”等列数据。

43 步骤3 输入“月份”列数据。先选中A3：A22单元格区域，设置单元格式格式如下图所示。然后在A3单元格中输入“2008-12-31”，再按住Ctrl键把月份复制到相应的单元格。

44 步骤4 打开第4章建立的“员工福利表. xls”，返回“12月份工资管理表”，单击F3单元格，在其中输入引用公式：=[员工福利表
步骤4 打开第4章建立的“员工福利表.xls”，返回“12月份工资管理表”，单击F3单元格，在其中输入引用公式：=[员工福利表.xls]Sheet1!D3，在“员工工资管理表”中“住房补贴”列中引用“员工福利表.xls”Sheet1中相对应的数据。同理，在“员工工资管理表”中“交通补贴”和“社保金”列中引用“员工福利表.xls”Sheet1中相对应的数据，并填充完所选列数据。

45 步骤5 引用“12月份考勤统计表”中“应扣工资”和“全勤奖”两列数据。在“12月份工资管理表”中分别单击H3和I3单元格，在其中分别输入引用公式：=‘12月份考勤统计表’!J3和=‘12月份考勤统计表’!I3，再选中H3：I3单元格区域，利用填充柄填充完其余数据。

46 步骤6 设置数据格式。 在“员工工资管理表”中，右键单击选中的E3：M22单元格区域，从弹出的快捷菜选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中单击“货币”选项，选择样式并保留小数位2位。

47 4.6.2　公式和函数运用 一般工资表中都包含所得税一项，而所得税是根据员工的应发工资进行计算。而“应发工资”是利用“员工工资管理表”中的“基本工资”、“住房补贴”、“交通补贴”、“全勤奖”等计算得出的。“实发工资”是“应发工资”减去“所得税”、“考勤扣款”、“社保金”得出来的。 在“员工工资管理表”中至少要建立3个公式，一个是计算“所得税”，一个是计算“应发工资”，一个是计算“实发工资”。

48 表5-1 所得税税率及扣除金额（工资、薪金所得适用）

49 新个税税率表(2011年9月1日起实行) 起征点是3500元 级数 应纳税所得额 税率 速算扣除数 1 不超过1500元的部分 3% 2
2 超过1500元至4500元的部分 10% 105 3 超过4500元至9000元的部分 20% 555 4 超过9000元至35000元的部分 25% 1005 5 超过35000元至55000元的部分 30% 2755 6 超过55000元至80000元的部分 35% 5505 7 超过80000的部分 45% 13505

50 工资的起征点变成3500 公式是 比如说按照新的个税计算方法 4500元工资算： （工资 - 起征点）× 对应税率 - 速算扣除数
( ) x3%-0=30元

51 步骤1 在“应发工资”一列中K3单元格中输入公式：=E3+F3+G3+H3-I3-J3，或用鼠标单击引用相应的单元格，按下Enter键，将根据公式计算出相应的值。拖动K3单元格右下角的填充柄，用自动填充功能计算出所有员工的应发工资。

52 步骤2 计算“所得税”。在“应发工资”一列中L3单元格中输入公式：
=IF(K3<2000,0, IF((K3-2000)<500,(K3-2000)*0.05, IF((K3-2000)<2000,(K3-2000)*0.1-25, IF((K3-2000)<5000,(K3-2000)* , (K3-2000)* )))) 用填充柄填充“应扣所得税”列其余数据。 旧的所得税税率及扣除金额（工资、薪金所得适用）

53 工资的起征点变成3500的税率 =IF( K3<3500,0, IF( (K3-3500)<1500,(K3-3500)*0.03, IF( (K3-3500)<4500,(K3-3500)* , IF( (K3-3500)<9000,(K3-3500)* , (K3-3500)* ) ) ) )

54 步骤3 计算实发工资。在单元格L3中输入公式：= K3-L3。按回车键确认公式输入，然后利用自动句柄填充功能填充数据。

55 4.8 自动筛选和排序功能 4.8.1　应用自动筛选 步骤1 选择命令。单击工作表中任意含有数据的单元格，单击菜单栏“数据”|“筛选”|“自动筛选”命令，字段行的所有单元格右侧均会出现一个下拉按钮，如图所示。

56 步骤2 筛选数据。单击“部门”右侧的下拉按钮，从展开的下拉列表中单击“财务处”选项，单击“确定”按钮，将筛选出满足条件的记录，如图所示。
步骤3 取消筛选。若要取消自动筛选功能，只需再选择“数据”|“筛选”|“自动筛选”命令，取消勾选“自动筛选”复选框即可。

57 小贴士： 若要筛选出基本工资大于3000元，小于4000元的记录，就要使用“自定义”选项了。具体操作是：单击“基本工资”右侧的下拉按钮，在出现的下拉列表中选择“自定义”选项，在打开的“自定义自动筛选方式”对话框中设置筛选方式，如图所示。并单击“确定”按钮，即可显示筛选出的记录。

58 4.8.2　应用排序功能 步骤1 选择命令。选定工作表中含有数据的任意单元格，单击菜单栏中“数据”|“排序”命令，在出现的“排序”对话框中的“主要关键字”下拉列表框中选择“实发工资”项，再单击其右侧“降序”单选按钮。在“次要关键字”中选择“员工编号”项，再单击右侧“升序”。如图所示。

59 步骤2 设置对话框。单击“确定”按钮，此时系统弹出如图左所示的对话框，提示用户将按照文本或数字进行排序，保持默认设置
步骤3 完成排序设置。再单击“确定”按钮返回工作表中，可看到排序后的效果，如图右所示。

60 4.9 数据透视表的应用 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，可以快速合并和比较大量数据，具有透视和筛选功能和极强的数据分析功能，它能转换行或列以查看数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。

61 步骤1　选定单元格。单击“员工工资管理表”中任意含有数据单元格。
步骤2　选择命令。单击菜单栏上“数据”|“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导”对话框，如图所示。

62 步骤3　选定区域。在“请指定待分析数据的数据源类型”栏中选择“Microsoft Excel数据列表或数据库”选项，然后在“所需创建的报表类型”栏中选择“数据透视表”，单击下一步按钮，打开如图左所示的对话框。 步骤4　选择透视表显示位置。单击“下一步”按钮，打开如图右所示的对话框，选择“新建工作表”选项。

63 步骤5　透视表布局。单击“布局”选项，打开如图左所示的对话框。在“数据透视表布局”对话框中，可以设计数据透视表的布局。将数据清单中的字段名拖至页、行、列或数据区域。例如要将“住房补贴”、“交通补贴”、“基本工资”、“应发工资”和“实发工资”按部门汇总的操作是：把“月份”拖到“页”，把“部门”拖至“行”，把“住房补贴”、“交通补贴”、“基本工资”、“应发工资”和“实发工资”等字段名拖至“数据”区域，如图右所示。

64 步骤6　完成透视表设置。单击“确定”按钮，生成的数据透视表
当员工基本工资表的数据发生变化时，生成的透视表中的数据也随之变化。在数据透视表中只能对数据进行查看而不能进行修改。

65 4.10 保护工资表 步骤1 选择命令并设置密码。单击菜单栏上“工具”|“保护”|“保护工作表”命令，打开“保护工作表”对话框。在“取消工作表保护时使用的密码”文本框中输入要设置的密码；在“允许此工作表的所有用户进行”列表框中设置允许用户进行的操作，如勾选“排序”、“使用自动筛选”复选框等。如图所示。

66 步骤2 确认密码。单击“确定”按钮，系统将弹出如图左所示的“确认密码”对话框，再次输入密码，单击“确定”按钮。
步骤3 完成工作薄保护。当试图清除工资表中某个员工的记录时，系统将弹出如图右所示的提示信息。提示如果要清除该内容，必须要选取对工作表的保护。

67 步骤4 取消工作薄保护。如果要撤消对工作表的保护，可单击菜单栏上“工具”|“保护”|“撤消工作表保护”命令。在出现的“撤消工作表保护”对话框中输入正确的密码，再单击“确定”按钮即可完成撤消工作表保护功能的操作。 小贴士： 在如图所示的选项中，“允许用户编辑区域”可以让用户编辑指定的区域，实现方法是为不同的区域设置不同的操作密码；“保护工作薄”可以对整个工作薄起到保护作用，还可以根据需要选择只保护工作薄的“结构”或者“窗口”；“保护并共享工作薄”可以共享工作薄，提供以追踪修订方式共享，从而避免丢失修订信息。