第9章-企业工资管理(课件).doc

第九章 企业工资管理(课件）
企业工资管理案例分析
小李是创艺公司的会计，负责管理公司员工的工资，每个月要计算出公司员工的基本工资、奖金和个人所得税。以前她都是用手工操作，由于基本工资、奖金以及个人所得税的计算繁琐、复杂，所以很容易出错。最近公司对员工进行了一次Office办公软件培训，通过学习她希望能用Excel对公司员工的工资进行管理、统计。
任务一 为“员工信息”工作表设置格式
Q 要求如下：
1． 合并A1:G1单元格并居中,字体设置为“楷体、14号、加粗"，行高为“30",浅绿色底纹:
2． 设置A2：G2单元格格式为： 对齐方式为“水平居中”,浅黄色底纹；
3． 设置A3：G167单元格格式为：对齐方式为“水平居中”,外部和内部边框均为实线。
任务二 用YEAR函数和TODAY函数计算员工的工龄
在“员工信息”工作表中用YEAR函数和TODAY函数计算员工的工龄
Q 操作如下：
1．用YEAR函数计算“员工信息"工作表中的“工龄"，算法如下：
工龄=当前年份－参加工作年份= YEAR(TODAY(）)-YEAR（工作日期)。
2．将“员工信息”工作表中的“工龄”列的单元格“数字”格式设置为“数值”.
任务三 用VLOOKUP函数,查找出“工龄"
在“基本工资及考核奖金”工作表中由员工编号，利用VLOOKUP函数查找出“工龄”
Q 操作如下：
1．在“员工信息”工作表中定义“员工信息”的区域。
2．在“基本工资及考核奖金”工作表中根据员工编号，用VLOOKUP函数在上面定义的“员工信息”数据区中查找员工编号对应的工龄。
任务四 用IF函数嵌套,计算“基本工资及考核奖金"工作表中的“工龄工资”
Q 操作如下:
根据“工资、奖金对照表”工作表给出的“工龄”与“工龄工资"对照表，用IF函数嵌套在“基本工资及考核奖金”工作表中计算员工的“工龄工资”。
任务五 用VLOOKUP函数及IF函数计算“基本工资及考核奖金”工作表中的职务工资和学历工资
Q 操作如下:
1．在“基本工资及考核奖金”工作表中,用VLOOKUP函数根据“员工编号”，在“员工信息"数据区中，查找出相应的“职务”.
2．再根据上面查找到的“职务”,用VLOOKUP函数在“工资、奖金对照表”工作表中定义的“职务工资"数据区中查找到该职务对应的“职务工资”.
3．用IF函数的嵌套，计算出“基本工资及考核奖金”工作表中的“学历工资”。
提示:职务工资、工龄工资和学历工资的发放标准如下图所示:
任务六 计算“基本工资及考核奖金”工作表中的“考核奖金”和“基本工资"
Q 操作如下：
在“考核情况"工作表中,根据“考核分数"以及“工资、奖金对照表”的对应关系，计算出“考核等级"。再根据“工资、奖金对照表”根据“考核等级"，计算出“考核奖金"。
在“基本工资及考核奖金”工作表中,用VLOOKUP函数根据“员工编号”，在“考核情况”工作表中，查找出相应的“考核奖金”。
“基本工资及考核奖金”工作表中，基本工资=工龄工资+职务工资+学历工资。
任务七 把“基本工资及考核奖金"工作表中“基本工资的货币单位设置为“￥”（人民币）
Q 操作如下：
1．在“基本工资及考核奖金”工作表中选中要设置格式的单元格(“基本工资”和“考核奖金”)。
2．打开“单元格格式”对话框.
3．在数字选项卡选择“货币”，设为为“￥”（人民币）.
P 本任务要点: 单元格货币格式的设置
任务八 用COUNTIF函数统计各部门的“人数”以及各工龄段的“人数”
Q 提示如下：1．统计各部门的“人数”:
第一个参数选择“员工信息”工作表中所有部门信息的单元格;
第二个参数是条件,此处为相等的情形，可以直接输入文字“财务部"或者选定文字“财务部”所在的单元格；
2．统计各工龄段的“人数”：
第一个参数选择“员工信息”工作表中所有工龄信息的单元格;
第二个参数是条件，此处为分数据段计数的情形，输入类似">=15"的条件，并利用各数 据段相减 ；
P 本任务要点： COUNTIF函数的用法 （相等的情形、分数据段计数的情形）
任务九 用分类汇总计算各部门的平均工龄
Q 操作如下： 复制“员工信息”工作表重命名为“分类汇总”，按“部门"分类，汇总“工龄”，汇总方式为平均值。
任务十 制作数据透视表
Q 要求如下： 利用“基本工资及考核奖金"工作表制作数据透视表， “职务”放在行，“学历”放在列，“基本工资”放在数据区，选项为“平均值"。
任务十一 利用“工资与工龄统计”工作表制作图表
Q 要求如下: 利用“工资与工龄统计”工作表左部的工资统计表制作图表:1）源数据为“部门”和“人数”；2）图表类型为“分离型三维饼图”；3）图表标题为“各部门人数”； 4）数据标志包括“值”；5）图标区填充效果为“预设”的“雨后春初晴"6）作为新工作表插入，并命名为“部门人数统计图"。
课后练习
问题：教务处李老师要根据“第9章素材\课后练习素材\学生成绩（素材)。xls"中的“学生成绩表”工作表对1班～５班各门课程考试成绩进行分析，为此，他要做如下几项工作：
1．从“学生成绩表”(如图9-34所示）得到“各科成绩表”（如图9-35所示).
图9—34 学生成绩表
图9-35 各科成绩表
2．统计出各班每门课的平均分.
3．用簇状柱形图表示出1班～５班英语和数学的平均分.
4．统计出1班～５班英语成绩优秀人数。
5．最后他还想做一个成绩查询系统，如图9-36所示.
图9-36 成绩查询系统
下面请你帮助李老师完成上面的各项工作，要求如下：
打开“第9章素材\课后练习素材\学生成绩(素材).xls”，将其另存为“学生成绩(学号姓名)。xls”。
1．从“学生成绩表”工作表得到“各班成绩表”工作表。
（1)根据“学生成绩表”生成数据透视表，并把生成的数据透视表命名为“成绩表”.
要求：把“学号”放到行上，“课程”放到列上，“分数”放到数据区中。
（2)根据“成绩表”中的数据填写“各科成绩表"中各门课的分数。
要求：用VLOOKUP函数完成。
2．统计出各班各门课的平均分。
(1）在“各科成绩表”中,为了正确统计出各班各门课的平均分,要把分数为0的单元格填写为“缺考”.
要求：用IF函数完成。
(2）建立“各科成绩表"的副本“各科成绩表(2）"，在“各科成绩表(2）”中用分类汇总统计出各班各门课的平均分。
要求：按照“班级”进行分类，汇总方式为“平均值"，汇总项为各门课分数,并且隐藏分类明细。
3．用簇状柱形图表示出1班～５班英语和数学的平均分。
利用“各科成绩表（2)”中“大学英语”和“计算机应用”2列的数据，制作1班～５班英语和数学平均分统计柱形图，要求：
(1）图表类型为“簇状柱形图”；
(2）数据系列产生在“列”;
（3）图表标题为“1班～５班英语和计算机应用平均分统计图"；
（4）将图表作为新工作表插入，新工作表名称为:英语和计算机应用平均分统计图.其中：“图表区域"的填充效果和“绘图区格式”的填充效果可自由选择。
4．统计出各班英语成绩优秀人数。
(1）在“各科成绩表"中,用IF函数把英语成绩大于等于90的记录的“英语优秀"列填写为1，其他情况为空白。
（2)在“成绩查询表”中用SUMIF函数计算出各班英语成绩优秀人数。
提示：利用(1）的结果。
5．实现成绩查询功能。
在“成绩查询表”中，请实现如下的功能:当选择学号和课程后,能查询到相应的分数，见样例.
提示:先找出“各科成绩表"中各门课程与其所在的列数的关系（可用VLOOKUP函数或者IF函数完成此步)。
·4·