实验目的：
熟练掌握用Excel处理人事档案、工资的技术，并能用公式提高处理成绩的效率。以便毕业后能胜任工作需要。
实验内容：
1、 设置单元格和数据格式；
2、 公式的应用；
3、 图表的创建
实验步骤：
一、设置单元格和数据格式及公式的应用
1. 把工作簿重命名为“总表”。
2. 对总表自动填充职工编号，编号从C001开始。
3. 设置“基本工资”、“浮动奖金”的数据格式：保留两位小数，使用千分位分隔符，若数据为负数显示为红色。
4. 设置总表的单元格格式：字号设置位10，居中，单元格内允许自动换行，同时将“外框”和“内框”的线条设置为单线条。
5. 设置第一行的格式，使这一行与正文数据能明显区别：字体格式为：黑体，单元格图案颜色设置为浅绿色。
6. 将“核定工资总额”、“合计应发”、“应纳税额”、“个人所得税”、 “实发工资”几项的图案颜色设置为黄色。
7. 冻结窗口，要求将第一行的项目名称和职工姓名固定。
8. 计算“核定工资总额”，核定工资总额=基本工资+浮动奖金，计算结果保留两位小数，并用千分位分隔符隔开，若为负数显示为红色。（提示：可先用公式求出一个职员的核定工资总额，然后利用自动填充的功能，算出其他职员的核定工资总额）。
9. 计算“合计应发”，合计应发为核定工资总额减去各扣减项再加上格福利项的结果，由于本表中各扣减项用负数表示，所以合计应发=核定工资总额+交通/通讯等补助+迟到/旷工等扣减项+养老/医疗/失业保险。（提示：SUM）
10. 计算“应纳税额”，应纳税额=合计应发-免税基数。（假设免税基数为1600）
11. 使用公式或者VBA计算个人所得税。
a) 公式：个人所得税=应纳税额*该范围税率-扣除数。个人所得税的税率分为9级。如下所示：
表格 1 个人所得税税率
|
应纳税额
|
税率（%）
|
速算扣除数
|
|
<500
|
5
|
0
|
|
<2000
|
10
|
25
|
|
<5000
|
15
|
125
|
|
<20000
|
20
|
375
|
|
<40000
|
25
|
1375
|
|
<60000
|
30
|
3375
|
|
<80000
|
35
|
6375
|
|
<100000
|
40
|
10375
|
|
≥100000
|
45
|
15375
|
提示：利用VLOOKUP函数，先建立一个工作表，命名为“tax”，工作表如下所示：
|
|
A
|
B
|
C
|
D
|
E
|
|
1
|
级数
|
应纳税额
|
上一范围上限
|
税率
|
速算扣除数
|
|
2
|
1
|
不超过500的
|
0
|
5%
|
0
|
|
3
|
2
|
500元至2000元的部分
|
500
|
10%
|
25
|
|
4
|
3
|
2000元至5000元的部分
|
2000
|
15%
|
125
|
|
5
|
4
|
5000元至20000元的部分
|
5000
|
20%
|
375
|
|
6
|
5
|
20000元至40000元的部分
|
20000
|
25%
|
1375
|
|
7
|
6
|
40000元至60000元的部分
|
40000
|
30%
|
3375
|
|
8
|
7
|
60000元至80000元的部分
|
60000
|
35%
|
6375
|
|
9
|
8
|
80000元至100000元的部分
|
80000
|
40%
|
10375
|
|
10
|
9
|
超过100000元的部分
|
100000
|
45%
|
15375
|
b) 用VBA编程计算个人所得税的方法。
提示：1）工具－〉宏－〉Visual Basic编辑器，进入“Visual Basic编辑器”中点击左边VBAProject，选择“插入”|“模块”，在出现的“模块1”的代码窗口输入代码。2）输入完毕后，选择“文件”|“关闭并返回到Microsoft Excel”，在L2中输入公式：=tax（应纳税额，纳税基数）。
12. 对数据进行排序，“主关键字”选择“部门名称”，“次关键字”选择“核定工资总额”，并按升序排列。
13. 对工资额进行部门总汇。按照“部门名称”进行汇总，汇总方式选择“求和”，对“核定工资总额”、“合计应发”和“实发工资”三项进行汇总。汇总结果显示在数据下方。
14. 将分类汇总的结果单独存放在一张新工作表中，新的工作表命名为“各部门总汇表”。撤销在总表的分类汇总。
15. 制作自动设置格式的工资条，工资条包含职员编号，姓名，各工资细目。
16. 从身份证号码中自动提取性别与出生日期
a) 提示：
i. 15位身份证号码，若最后一位为奇数，表示男性，若为偶数，表示女性
ii. 18为身份证号码，若第17位为奇数，表示男性，若为偶属，表示女性。
iii. 15为身份证号码，7~8位为出生年份（2位），9~10位为出生月份，11~12位为出生日期。
iv. 18为身份证号码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期。
17. 计算职工工龄格式为：xx年xx月（提示：当前系统时间-参加工作时间，用到公式CANCATENATE，DATEDIF）
18. 根据总表的数据，利用公式，提取相关信息，自动生成员工简历表。包含姓名，性别，籍贯，出生时间，参加工作时间，职称，现任职务，学历，毕业学校及专业。
二、创建图形
19. 根据总汇表制作员工工资分离型三维饼图。要求：
数据区域系列产生在“行”
图表标题：各部门工资比例
图例显示在右上角
数据标志显示百分比
该图表作为对象插入。
20. 根据人事数据进行年龄、性别的统计并作出对比图。
1）建立新的工作表“年龄分布统计”，包含以下信息
年龄段（20岁以下，20-30，30-40，40-50，50以上）
女性人员对应年龄段的人数
女性人员对应年龄段的人数占总人数的比例
男性人员对应年龄段的人数
男性人员对应年龄段的人数占总人数的比例
2）制作对比图——条形图，要求：
竖线轴代表年龄段，左边为女性的比例值，右边为男性的比例值
数据区域系列产生在“列”
图例不显示
条形图作为对象插入
3）修饰对比图：
绘图区域背景填充色设置为白色
表示年龄段的数据放到最左边
表示百分比数据左边，数值应为正值
网格线设置为虚线，水平网格线颜色设置为蓝色
使表示数值的条形线之间没有空隙
在表示男性数值的区域的空白处标明“男性”，在表示女性数值的区域空白处标明“女性”
三、特殊图形绘制
某公司记录了一年12个月的销售数据，6月份的销售量比其他月销售总量还多，使用普通的图表显示不出其他数据点的变化趋势，要求查阅资料绘制断层图表。
现在位置： 
