Excel综合案例+指导书(工资表)

Excel综合（工资表）

案例分析

本实例建立了一个工资表的电子表格，共有4个工作表：一个工作表为9月工资表，一个工作表为9月水电读数，一个工作表为应发工资统计图，一个工作表为数据透视表。

在工资表中，按以下规则计算职工的工资。

1、岗位津贴根据职务等级来计算：厅级职务津贴为3000，处级职务津贴为2000，科级职务津贴为1000，

办事员职务津贴为500。

2、应发工资为基本工资、岗位津贴和生活补贴之和。

3、电费为用电度数与电费单价的乘积，水费为用水度数与水费单价的乘积（数据来自书电表读数工作表）。

4、个人所得税根据应发工资计算：1000元以下不扣税，1000-2000元之间扣税5%，2000元以上扣税10%。

5、扣款合计为水费、电费和个人所得税的和。

6、实发工资为应发工资减去扣款合计。

设计步骤

1、工作表的管理

（1）修改工作表的名称

将sheet1工作表名改为“9月工资表”

（2）删除工作表

删除sheet2工作表

（3）复制工作表

将“水电表原始”工作簿的“9月水电读数”工作表复制到本工作簿文件中。

2、编辑“9月工资表”数据

（1）插入行

在第11行前插入一行：林志远，1974-2-8，长沙，财会部，科级，1430

（2）填充等差序列：在A3至A32单元格区域中填充工号1-30

（3）在多个单元格中输入相同的数据：在H3至H32单元格区域中输入相同的生活补贴220

（4）设置数据有效性：在G3到G32单元格区域设置数据有效性1000-4000

3、自定义序列

将9月工资表中的B3至B32单元格区域的数据导入为自定义序列，在9月水电度数的A2至A31单元格区域中填充此序列。

4、编辑公式

（1）计算岗位津贴：=IF(F3=”厅级”,3000,IF(F3=”处级”,2000,IF(F3=”科级”,1000,500)))

（2）计算应发工资：=G3+H3+I3或=sum(G3:I3)

（3）计算水费：=9月水电度数!B2*水电表读数!F$2

（4）计算电费：=9月水电度数!C2*水电表读数!F$3

（5）计算个人所得税：=IF(J3<1000,0,IF(J3<2000,(J3-1000)*0.05,1000*0.05+(J3-2000)*0.1))

（6）计算扣款合计：=K3+L3+M3或SUM(K3:M3)

（7）计算实发工资：=J3-N3

（8）填充公式

5、设置表格格式

（1）设置单元格的数字格式：设置C3至C32单元格区域的格式为“年月日”，J3至N32单元格区域的数据保留小数点后一位。

（2）格式化单元格：合并A1至O1单元格区域，设置单元格对齐方式为居中，字体为华文彩云，字号为

24，字形加粗，字体颜色为红色。

（3）设置单元格填充颜色：设置C3到F32单元格区域及K3到N32单元格区域的填充颜色为浅绿色，G3到J32单元格区域及O3到O32单元格区域的填充颜色为浅黄色。

（4）复制格式：选中A2到O2单元格区域，设置居中对齐，字形加粗，填充颜色为蓝色，字体颜色为白色，并将此格式复制到A3到B32单元格区域。

（5）设置边框：设置A2到O32单元格区域表格的外边框线为粗线，内边框为细线。

（6）设置列宽：设置表格各列为最合适的列宽。

6、查看数据

（1）改变显示比例：改变工作表的显示比例。

（2）隐藏列：隐藏K列到M列，再取消隐藏。

（3）冻结窗格：将工作表的底1、2行和第A、B列冻结起来。

7、数据处理

（1）数据排序：数据首先按职务等级从低到高排列，职务等级相同的按基本工资从高到低排列。

（2）自动筛选：筛选出实发工资最高的10名职工，再取消筛选；筛选出所有姓王和姓杨的职工，按姓名的升序排列。

（3）高级筛选：筛选出北京和长沙的所有员工及所有职务为厅级的员工，取消筛选。

（4）数据汇总：统计出各个分公司的人数，应发工资和实发工资的和。

（5）数据透视表：在新的工作表中建立数据透视表，以“分公司”为页字段，“部门”为行字段，“职务等级”为列字段，“姓名”作为数据项，统计出各部门各职务等级的人数。

8、图表处理

（1）插入图表：根据各位职工的姓名和应发工资生成数据点折线图，设置图表标题为“应发工资统计图”，图表放在新的工作表“工资图”中。

9、打印预览

将页面设置为横向，缩放比例为80%，页脚为制作人姓名、日期、页号，调整页边距，打印预览工作表。

10、保护工作簿和工作表

对工作簿和工作表设置密码。

样张1：