EXCEL基本操作方法Word格式.docx

EXCEL基本操作方法Word格式.docx

《EXCEL基本操作方法Word格式.docx》由会员分享，可在线阅读，更多相关《EXCEL基本操作方法Word格式.docx（10页珍藏版）》请在冰豆网上搜索。

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

五、项目难点

●表格格式化；

六、教学方法

项目教学法

七、教学过程

根据项目内容进行介绍（教师）

分析、指出各项任务（教师）

简单演示讲解项目中各个任务点相关操作（根据项目规模分阶段讲解）

学生完成项目任务、教师指导（学生分阶段实施）

学生提交项目

教师验收、记录登记学生项目效果成绩

八、时间安排

制定项目、资料收集

项目完成过程

项目验收评分

项目内容分析及演示讲解任务一二

学生实施任务一二

项目内容分析及演示讲解任务三四

学生实施任务三四

课外时间

20分钟

70分钟

15分钟

75分钟

九、项目要求

根据已给的初始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所示。

图11

（三）操作步骤

任务一具体步骤：

1.双击Excel原始数据文件“员工档案及工资表.xlsx”，在“员工编号”所在A列A3单元格中输入第一个序号“DF001”，把光标移至A3单元格右下处，待光标变成实心黑色“十”字形（填充柄），按住鼠标向下拉至A22，完成自动填充员工编号。

O1单元格，把标题居中，设置单元格标题样式。

（1）选中A1：

O1单元格区域，单击“开始”选项卡下的“对齐方式”组中“合并后居中”按钮；

（2）选中A1单元格，单击“开始”选项卡下的“样式”组中“单元格样式”按钮，选择“60%强调文字颜色6”和“标题”。

3.选中A2：

O22单元格区域，单击右键，选中“设置单元格格式”按钮，选择“对齐”选项卡，水平对齐和垂直对齐选择都“居中”。

选中2到22行，设置行高为18。

4.选中A2：

O22单元格区域，单击右键，选中“设置单元格格式”按钮，选择“边框”选项卡，在“线条>

样式”中，单击“粗线”，在预置中单击“外边框”，在“线条>

样式”中，单击“细线”，在预置中单击“内边框”。

选中A2：

O22单元格区域，单击“开始”选项卡下的“样式”组中“套用表格样式”按钮，选择“表样式中等浅色13”（也可选其它）。

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

6.条件格式的设置：

选中“基本工资”列J3：

J22，单击“开始”选项卡下的“样式”组中“条件格式”按钮，选择“突出显示单元格规则”，选择“大于”，在对话框中输入“10000”，设置为“浅红填充色深红色文本”（也可选其它）。

任务二具体步骤：

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

2.在“出生日期”列的G3单元格中单击，然后输入嵌套函数“=CONCATENATE（MID（F3,7,4）,"

）”或者输入公式“=MID（F4,7,4）&

”，按enter键确认（公式必须以等号“=”开始，公式中的运算符以西文半角字符输入），然后向下填充公式到最后一个员工。

3.在“年龄”列的H3单元格中输入函数“=INT（（TODAY（）-G3）/365）”，按enter键确认，然后向下填充公式到最后一个员工。

4.在“工资总额”列的L3单元格中输入函数“=SUM（J3:

K3）”或者输入公式“=J3+K3”，按enter键确认，然后向下填充公式到最后一个员工。

5.在“交税”列的M3单元格中输入函数“=IF（L3>

10000,L3-基础数据!

$F$7,0）*基础数据!

$F$5”，按enter键确认，然后向下填充公式到最后一个员工。

6.在“公积金”列的N3单元格中输入公式“=J3*10%”，按enter键确认，然后向下填充公式到最后一个员工。

7.在“实发工资”列的O3单元格中输入公式“=L3-M3-N3”，按enter键确认，然后向下填充公式到最后一个员工。

任务三具体步骤：

1.选定“员工档案及工资表”工作表的单元格区域B2：

B22，按住CTRL键选定J2：

J22单元格区域；

单击“插入”选项卡下“图表”组中“柱形图”按钮；

弹出“二维柱形图”对话框，单击“二维柱形图>

簇状柱形图”。

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

3.单击“图表工具>

布局”中“标签”组中的“坐标轴标题”按钮；

弹出的对话框中选择“主要横坐标轴按钮”，单击“坐标轴下方标题”，在图表中下方的横坐标标题框中输入“姓名”；

单击“图表工具>

弹出的对话框中选择“主要纵坐标轴按钮”，单击“竖排标题”，在图表中下方的纵坐标标题框中输入“工资”。

任务四具体步骤：

（1）双击Excel数据文件“员工档案及工资表.xlsx”；

插入一张新工作表，命名为“排序与筛选表”。

（2）选定“员工档案及工资表”工作表的单元格区域A1：

O22，单击鼠标右键，单击“复制”，单击“排序与筛选表”工作表，选中A1后单击鼠标右键，单击“粘贴选项”下的“保留原格式”按钮。

（3）将光标停留在有效数据区域内，单击“开始”选项卡下的“编辑”组中的“排序和筛选”按钮，在弹出的对话框中单击“自定义排序”，弹出“排序”对话框，在“主关键字”列表框中选择“实发工资”，在“次序”列表框中选择“降序”，单击“确定”按钮，结果如图4所示：

图4

（1）将光标停留在有效数据区域内，单击“开始”选项卡下的“编辑”组中的“排序和筛选”按钮，在弹出的对话框中单击“筛选”；

在每个字段的右侧出现“筛选”按钮，点击“部门”字段的筛选按钮，在出现的对话框中只勾选“研发”，单击“确认”按钮，点击“实发工资”字段的筛选按钮，在出现的对话框中选择“数字筛选”，单击“大于或等于”，在对话框中“大于或等于”后面填入“6000”。

结果如图5所示：

图5

（2）单击“开始”选项卡下的“编辑”组中的“排序和筛选”按钮，在弹出的对话框中单击“筛选”，取消自动筛选。

在空白单元格内输入筛选条件，如图6所示：

图6

然后将光标停留在有效数据区域内，单击“数据”选项卡“排序和筛选”组中“高级”按钮，弹出“高级筛选”对话框，在“方式”栏中选择“将筛选结果复制到其他位置”，“列表区域”编辑框保持不动，单击“条件区域”编辑框，选中图6的条件区，单击“复制到”编辑框，选中A30。

如图7所示：

单击“确认”按钮即可。

图7

3.数据汇总

（1）双击Excel数据文件“员工档案及工资表.xlsx”。

插入一张新工作表，命名为“部门实发工资分类汇总表”。

（2）选定“员工档案及工资表”工作表的单元格区域A2：

O22，单击鼠标右键，单击“复制”，单击“部门实发工资分类汇总表”工作表，选中A1后单击鼠标右键，单击“粘贴选项”下的“值”按钮（不带格式复制）。

（3）光标定位在“部门”列的任一有效数据单元格中，单击“开始”选项卡中“编辑”组中的“排序和筛选”按钮，单击“升序”或“降序”，将相同的记录放在一起。

然后将光标定位在数据区域内，单击“数据”选项卡中“分级显示”组中的“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”中选择“部门”，在“汇总方式”下拉列表框中选择“平均值”，在“选定项汇总”列表框中选择“实发工资”，如图8所示：

单击“确定”按钮即可。

图8

4．数据透视表的创建

（1）双击Excel数据文件“员工档案及工资表.xlsx”，将光标定位在“员工档案及工资表”工作表的数据区内，单击“插入”选项卡中“表格”组中“数据透视表”按钮，选择“数据透视表”。

（2）弹出“创建数据透视表”对话框，选择数据透视表的显示位置为“新工作表”，单击“确定”按钮。

（3）在“数据透视表字段列表”中勾选“姓名”、“性别”、“部门”、“实发工资”。

（4）把“姓名”拖入“报表筛选”栏中，“性别”拖入“列标签”栏中，效果如图11所示：

十一、项目验收

教师根据学生的现场操作和提交的项目文件（参照“员工档案及工资表（最终操作）.xlsx”）进行验收评分，并在成绩登分册记录实验效果成绩。

感谢下载!

欢迎您的下载，资料仅供参考