EXCEL基本操作方法

EXCEL应用项目1

一、项目名称

**公司员工档案及工资表的编辑与管理

二、项目内容

EXCEL电子表格目前已经得到广泛应用，如财务部门的账务单、员工工资单、学生成绩单等。本项目要求学生运用Excel的基本功能完成**公司员工档案及工资表的编辑与管理，通过完成项目任务让学生掌握本软件的基本技能。

三、项目目标

●了解Excel表格中常用术语（工作簿工作表、单元格、名称框、编辑栏等）；

●掌握在Excel中输入数据的各类方法；

●掌握数据及表格结构的格式化方法；

●掌握公式与函数的应用；

●掌握图表的创建方法；

●熟练掌握数据排序、筛选、分类汇总；

●掌握数据透视表的使用。

四、项目重点

●Excel中数据的输入和表格格式化；

●公式与函数的应用；

●图表的创建方法；

●数据排序、筛选、分类汇总。

五、项目难点

●表格格式化；

●数据排序、筛选、分类汇总。

六、教学方法

项目教学法

八、时间安排

1该部分由毛德梅老师执笔。

九、项目要求

根据已给的初始Excel文件“员工档案及工资表.xlsx”，按照项目任务要求，完成工作表的重命名、删除、数据填充、格式化等操作，同时根据项目任务完成“排序与筛选表”、“部门实发工资分类汇总表”和“数据透视表”的制作。具体任务要求见“十、项目任务分析与实施”，通过该综合项目训练，使学生能够掌握Excel的基本操作技能。

十、项目任务分析与实施

（一）项目的需求分析

由教师授课时分析

（二）项目任务分解

任务一、工资表的编辑（20分，每小题3~4分）

1.打开“员工档案及工资表.xlsx”文件，在“员工编号”列A3单元格中输入序号“DF001”。使用自动填充功能将员工编号填充到A22。

2.合并A1：O1单元格，并设置为标题居中，设置单元格标题样式为“60%强调文字颜

色6”。

3.将A2：O22单元格区域设置为水平对齐和垂直对齐都为“居中”。将2到22行行高

设置为18。

4.将A2：O22单元格区域边框设置为：“外边框”为“粗线”，“内边框”为“细线”。将A2：O22单元格区域样式设置为：“表样式中等浅色13”。

5．将工作表“sheet1”改名为“员工档案及工资表”，标签颜色为红色，将“sheet2”的工作表名改为“基础数据”，将“sheet3”删除。

6．采用条件格式的设置方法，将“基本工资”列中大于“10000”的单元格设置为“浅红填充色深红色文本”。

任务二、工资表数据的计算（30分，2~7题各5分）

1. 打开Excel文件“员工档案及工资表.xlsx”。

2. 从身份证号中提取员工出生日期，并填充到“出生日期”列。工作表中的第F列已事先输入了员工的身份证号，身份证号的第7位~第14位为出生年月日。首先通过截取字符串函数MID依次提取出年月日，再通过文本合并函数CONCATENATE或连接运算符&将他们连接在一起形成出生日期。然后采用嵌套函数“=CONCATENATE(MID(F3,7,4),"年",MID(F3,11,2),"月",MID(F3,13,2),"日")”或者采用公式“=MID(F4,7,4)&"年"&MID(F4,11,2)&"月"&MID(F4,13,2)&"日"”。

3. 计算所有员工年龄：年龄列中需要填入员工的周岁，首先通过当前日期函数TODAY 获取当前日期，然后减去该员工的生日日期，余额除以365天得到年限，再通过向下取整函数INT向下取整，得到员工的周岁年龄。如计算某一个年龄：在“年龄”列的H3单元格中输入函数“=INT((TODAY()-G3)/365)”，按enter键确认。

4. 在L3单元格中计算工资总额。

5. 在交税列中计算交税数据。交税的标准是“工资总额”不超过（含）10000的不交税，超过10000的交超过部分的5%，个人所得税税率表及减除标准在“基础数据”工作表中。

6. “公积金”列中计算公积金：公积金扣除标准是“基本工资”的10%。

7. 计算实发工资：实发工资=工资总额-交税-公积金。

效果如图1所示：

图1

任务三、工资表图表的建立（10分，部分不对酌情减分）

1.根据“员工档案及工资表”中姓名和基本工资列建立二维簇状柱形图，

效果如图2所示：

图2

2. 将图表中“基本工资”标题框修改为“东方公司员工基本工资”。

3. 给图标添加上坐标轴，横坐标轴标题为“姓名”，纵坐标轴标题为“工资”。

效果如图3所示：

图3

任务四、工资表数据的分析（40分，每小题10分）

1. 数据排序

（1）在“员工档案及工资表.xlsx”中插入一张新工作表，命名为“排序与筛选表”。

（2）将“员工档案及工资表”工作表中的数据拷贝到“排序与筛选表”工作表中。

（3）根据“实发工资”列将“员工档案及工资表”降序排列。

2. 数据筛选

（1）利用“自动筛选”功能，筛选出“研发”部门“实发工资”大于或等于“6000”，所有人员的工资信息。

（2）利用“高级筛选”功能，筛选出“研发”部门“实发工资”大于或等于“6000”，所有人员的工资信息。

3.数据汇总

（1）在“员工档案及工资表.xlsx”中插入一张新工作表，命名为“部门实发工资分类汇总表”。

（2）将“员工档案及工资表”工作表中的数据拷贝（不带格式复制）到“部门实发工资分类汇总表”。

（3）根据“部门”汇总每个部门实发工资的平均值。

结果如图10所示：

图10

4.在一张新工作表中创建“数据透视表”，统计每个部门男女职工实发工资情况，效果如

图11所示。