Excel教案完整版.pdf

《Excel教案完整版.pdf》由会员分享，可在线阅读，更多相关《Excel教案完整版.pdf（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=实 训 教 案实训名称实训地点职知识业目标技能力能目标训练素质目目标标2015 年 3 月 12 日第 2 周第 1、2 课时项目 1 制作员工基本信息表机房 305数据有效性的设置；函数 if、len、mod 和 mid 的使用；数据排序；分类汇总；工作的复制、修饰；会设置数据的有效性，会复制工作表，会使用函数if、len、mod 和 mid，对数据会进行分类汇总。熟练录入数据，提高录入速度；熟悉 if、len、mod 和 mid 函数的功能与应用。实实 训训 过过 程程 设设 计计实训步骤时间实 训 内 容学生活动

2、分配企业在发展的过程中，要对员工的基本情况进行统一的管理。学生听讲；将员工基本信息建立并建立起员工基本信息表，可方便随时掌握员工的基本信息，还可以随时对员工基本情况进行筛选、统计和分析。创建工作簿，重命名工作表5项目提出任务11、启动 Excel2003；新建工作簿，2、重命名“员工基本偏私息表”，并保存 D:案例行政篇。重 命 名 工 作操作3、将“员工基本信息工作表”中的 sheet1 重命名“员工基簿，工作表；步骤本信息”。任务2创建“员工基本信息”表基本框架31、输入表格标题字段；学 生 录 入 各2、输入“编号”；（使用填充句柄填充）项数据操作3、输入员工姓名；步骤提示：填充句柄，按

3、住鼠标左键拖其右下角的的填充句柄。任务3输入员工的“身份证号码”10-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=1、设置“身份证号码”的数据格式（设置为文本格式）（设置为文本格式）知识点：在 Excel 中，输入的数字长度超过 11 位时，系统将自动将该数字处理为“科学计数”格式，如“5.10E+17”。格式单元格数字分类文本小技巧：先输入英文状态下的的单引号，也可将单元格格式设置为“自定义”中的“”。操作2、设置“身份证号码”的数据有效性；步骤数据是唯一的。但输入时出错致使数据相同，很难发现，就必须通过“数据有效性”来防止重复输入。数据有

4、效性设置允许自定义公式输入“=countif($C$2:$C$26,$C2)=1”任务4输入员工的“部门”学 生 设 置 单元 格 格 式 为“文 本 格式”；学 生 分 别 练习 三 种 身 份证 不 同 的 输入方法。让学生练习，故 意 出 错 看有什么结果。8学 生 完 成 部门 序 列 的 设置与输入；1、为“部门”设置有效性序列对相对固定的一组数据，可以定义一组序列值，能提高输入效率。操作数据有效性允许序列（输入英文状态下的逗号）；步骤2、任务输入“入职时间”、“学历”、和“职称”5操作入职时间、学历和职称的录入；步骤任务根据员工的“身份证号码”提取员工的“性别”61、选单元格；2、

5、在选定的单元格内输入公式：“=if(mod(mid(C2,15,3),2)=1,“男”，“女”)知识点：操作MID、MOD 函数介绍步骤MID（text,start_num,num_chars）从文本字符串中指定的起始位置起，返回指定长度的字符。MOD（number,divisor）返回两数相除的余数。结果的正负号与除数相同。任务根据员工的“身份证号码”提取员工的“出生日期”71010老 师 介 绍 两个函数；学生按 老 师 的 要求输入公式。-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=1、选定单元格学 生 输 入 函2、输入公式：数，体会

6、函数“=-TEXT(IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8),“0的功能。操作-00-00)”老 师 讲 解 函步骤知识点：数的功能。文本函数（提取出的出生日期转换成文本型日期），减负运算（将提取出来的数据转换成真正的日期，即将文本型数据转换成数值）。任务导出“员工基本信息”表68操作文件另存为保存类型为“带格式文本文件（空格分隔）”学生练习；步骤导出数据，以备其他工作中需要时，不必重新输入数据。任务使用自动套用格式美化“员工基本信息”表59操作1、选中单元格学生练习；步骤2、格式自动套用格式，序列 2；任务使用手动方式格式美化“员工基本信息表”10101、插

7、入空行；学生录入，练操作2、制作表格标题；“利源公司员工基本信息表”，隶书，20习 设 置 表 格步骤磅；边框；3、设置表格边框：任务复制“员工基本信息”工作表2111、选中工作表；操作2、右单击，移动或复制工作表，选择 sheet2，建立副本；步骤小技巧：按住小技巧：按住 CtrCtr 键不放，拖曳到目标位置。键不放，拖曳到目标位置。任务统计各部门人数512操作数据排序主要关键字分类汇总分类字段“部门”计学生练习；步骤数选定汇总项。本项目通过制作“员工基本信息表”，主要介绍了工作簿的创建、重命名工作表、复制工作表、Excel 中数据的输入技巧、数据的有效性设置设置、利用 IF、LEN、教学M

8、OD、TEXT、MID 函数从身份证号码中提出员工性别、出生日期等信息。反思实 训 教 案实训名称实训地点2015 年 3 月 19 日第 3 周项目 2 制作公司车辆使用管理表305 机房-完整版学习资料分享-第 3、4 课时=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=职业技能训练目标知识目标能力目标素质目标自定义数据；查找和替换；乘法计算；if 函数应用；格式化表格；制作透视表会使用录入技巧及函数功能制作车辆使用管理表等相关表格培养学生解决实际问题的能力实实 训训 过过 程程 设设 计计实训步骤实 训 内 容学生活动时间分配任务本项目通过制作公司车辆使用管理

9、表，来介绍Excel提出软件在车辆使用管理方面的应用。创建工作簿，重命名工作表任务 1（1）启动 Excel 2003，新建一个空白工作簿。（2）将创建的工作薄以“公司车辆使用管理表”为名任务 1保存。步骤（3）将“公司车辆使用管理表”中的 Sheet1 工作表重命名为“车辆使用结算”。创建“车辆使用结算”表的基本框架任务 2（1）在“车辆使用结算”表中输入工作表标题“公司。任务 2车辆使用结算表”步骤（2）输入表格标题字段。输入“车号”的数据任务 3（1）选中 A3:A24 单元格区域。任务 3（2）自定义“车号”数据格式。步骤（3）输入车号。输入“使用原因”数据任务 43按照要求创建工作簿

10、，重命名工作表8创建“车辆使用结算”表的基本框架讲解自定义录入的方法使用自定义7功能录入车号讲解查找和5替换功能该项数据分为两种“公事”和“私事”。为实现快使用查找和任务 4速输入，这里，我们使用“查找”和“替换”的方法替换功能输步骤来输入该列数据。入数据-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=输入“公司车辆使用结算表”其它数据任务 58（1）输入“使用者”数据。输入数据（2）输入“所在部门”的数据。任务 5（3）输入“使用日期”、“发车时间”、“交车时间”和步骤“里程”数据。计算“车辆使用费”讲解演示乘10法公式任务 6这里，假设我们

11、按2 元/公里计算车辆使用费，即车辆使用费=里程2。（1）选中 I3 单元格。任务 6（2）输入公式“=H3*2”，按【Enter】键。步骤（3）拖动 I3 单元格的填充句柄至 I24，将公式复制到I4:I24 单元格区域中，计算出所有“车辆使用费”。计算“报销费”任务 7（1）选中 J3 单元格。（2）输入公式“=IF(D3=公事,I3,0)”任务 7（3）按【Enter】键，计算出 J3 中的“报销费”。步骤（4）拖动 J3 单元格的填充句柄至 J24 单元格，将公式复制到J4:J24单元格区域中，计算出所有“报销费”。计算“驾驶员补助费”按照正常的每天 8 小时工作时间算，若驾驶员出车时

12、间超过 8 小时，超过部分的出车时间则按 30 元/小时任务 8发给相应的补助，若超过时间不足半小时，则忽略不计；超过半小时，不足 1 小时的，按 1 小时计。（1）选中 K3 单元格。（2）选择【插入】菜单中的【函数命令】，打开【插入函数】对话框，选择 IF 函数。任务 8（3）在“Logical_test”框中输入条件表达式步骤“(G3-F3)*248”，在“Value_if_true”中 输 入“ROUND(G3-F3)*24-8),0)*30”，在“Value_if_false”中输入“0”。（4）单击【确定】按钮，计算出 K3 中的“驾驶员补-完整版学习资料分享-运用乘法公式计算“车

13、辆使用费”讲解 if 函数15利用函数计算报销费讲解要求10利用函数计算补助费=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=助费”。（5）拖动 K3 单元格的填充句柄至 K24 单元格，将公式复制到K4:K24单元格区域中，计算出所有“报销费”。格式化“车辆使用结算”表讲解格式设10置方法任务 9（1）设置工作表标题格式。（2）将标题字段的格式设置为宋体、12 磅、加粗、居中。（3）设置“使用者”、“所在部门”和“使用原因”所在列的数据居中对齐。任务 9（4）将“车辆使用费”、“报销费”和“驾驶员补助费”步骤几项数据设置为货币格式“￥”。（5）为“车辆使用结算”表

14、设置边框。（6）为“车辆使用结算”表设置底纹。（7）设置标题字段的字体颜色。制作各部门车辆使用费数据透视表任务 10（1）单击选定“车辆使用结算”工作表。（2）选中该工作表中的任一非空单元格。（3）选择【数据】菜单中的【数据透视表和数据透视图】命令，根据【数据透视表和数据透视图向导】对任务 10话框的提示进行操作。步骤（4）将“所在部门”作为“行”字段，将“使用原因”作为“列”字段，将“车辆使用费”作为“数据”项。（5）新生成的数据透视表工作表重命名为“各部门车辆使用费数据透视表”。设置打印页面任务 11（1）选中“车辆使用结算”工作表（2）设置纸张方向。任务 11（3）设置页边距。步骤（4）

15、打印预览。按照要求设置格式讲解数据透10视表制作方法动手制作数据透视表讲解打印设3置打印设置及预览-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=教学反思本项目我们使用数据透视表对公式车辆使用费进行了汇总分析。此外，通过页面设置，我们可对制作好的报表进行页边距、纸张的设置，以形成一份美观、实用的表格。实 训 教 案实训名称实训地点知识目标职业技能能力训练目标目标素质目标2015 年 3 月 26 日第 4 周项目 3制作会议室管理表305会进行工作表格式设置，条件格式的应用会使用 TODAY、NOW 函数来判断日期和时间会制作会议室管理表第 5

16、、6 课时实实 训训 过过 程程 设设 计计实训步骤实 训 内 容学生活动时间分配在企业的经营管理过程中，企业各部门之间经常需要利用会议室来及时沟通、协调处理相关事宜。为规范会议室的管理，提高会议室的使用效率，需要使用会任务议室的部门应该提前向行政部申请会议室，说明使用提出时间和需求，行政部则依次制定出相应的会议室使用安排。创建工作簿，重命名工作表任务 1（1）启动 Excel 2003，新建一个空白工作簿。（2）将创建的工作薄以“公司会议室管理表”为名保存。任务 1（3）将“公司会议室管理表”工作簿中的 Sheet1 工作步骤表重命名为“重大会议日程安排提醒表”。5-完整版学习资料分享-=W

17、ORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=创建“公司会议室管理表”任务 25（1）在“重大会议日程安排提醒表”中输入工作表标题。任务 2（2）输入表格标题字段。步骤输入会议室使用安排任务 38输入重大会议日程安排提醒表中输入会议室使用的相任务 3关信息。步骤合并单元格任务 44（1）将表格标题单元格合并及居中。（2）合并标题字段的 B2:D2 单元格区域。任务 4（3）分别合并A3:A5、A6:A8、A9:A10、A11:A12、步骤A13:A14、B4:B5、B7:B8 单元格区域。设置单元格时间格式任务 53（1）选中 C3:D14 单元格区域。任务 5（2）

18、将单元格数字格式设置为“时间”，选择【类型】步骤为“13:30”设置文本格式任务 6（1）设置表格的标题行字体格式。（2）设置表格标题字段的格式。任务 6（3）设置其余文本格式。步骤设置行高和列宽任务 7（1）设置行高。任务 7（2）设置列宽。步骤-完整版学习资料分享-52=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=设置表格边框任务 8为 A2:H14 单元格区域设置边框。任务 8步骤使用“条件格式”设置高亮提醒任务 936通过 Excel 的“条件格式”功能判断“日期”和“时间段”，即会议室的使用实际上是否已经超过了当前的日期和时间。任务 9若超过了则字体显示

19、蓝色加删除线区分，且单元格背步骤景显示粉红色；若未超过则单元格背景显示黄色。（1）设置“日期”高亮提醒（2）设置“时间段”高亮提醒。取消背景网格线任务 105Excel 默认情况下会显示灰色的网格线，而这个网格线会对显示效果产生很大的影响。若去掉网格线则会使人的视觉重点落到工作表的内容上。任务 10（1）单击选中“重大会议日程安排提醒表”。步骤（2）选择【工具】菜单中的【选项】命令。（3）单击【视图】选项卡，取消勾选【窗口选项】中“网格线”选项。教学反思本项目通过制作“会议室管理表”，主要介绍了工作簿的创建、工作表重命名、设置单元格格式、工作表格式化、条件格式。实 训 教 案2015 年 4

20、月 2日实训名称制作员工人事档案管理表制作员工人事档案管理表-完整版学习资料分享-第 5 周第 7、8 课时=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=实训地点职业技能知识训练目标目标能力目标素质目标实训室 305会进行员工人事档案的整理分类、汇总、分析和查询 1、熟练掌握高级筛选的方法；2、根据实际需求对数据清单进行筛选。本项目通过制作“员工人事档案管理表”，来学习 Excel 软件在人力资源管理中的应用。实实 训训 过过 程程 设设 计计实训步骤实 训 内 容学生活动时间分配人事档案管理是企业组织人事工作的重要组成部分，任务也是企业档案工作的重要组成部分。企

21、业人事档案管提出理主要负责对企业中员工的人事档案信息进行整理分类、查询等。任务 1创建工作簿，重命名工作表创建工作簿，重命名工作表（1 1）启动）启动 Excel 2003Excel 2003，新建一个空白工作簿。，新建一个空白工作簿。（2 2）将创建的工作薄以将创建的工作薄以“员工人事档案管理表员工人事档案管理表”为名保为名保存。存。（3 3）将）将“员工人事档案管理表员工人事档案管理表”工作簿中的工作簿中的 Sheet1Sheet1 工工作表重命名为作表重命名为“员工档案信息员工档案信息”。任务 1步骤动手操作2 分钟经过前面的学习，学生已经做得非常熟练任务 2导入导入“员工信息员工信息”

22、动手操作3 分钟-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=将行政篇中导出的将行政篇中导出的“员工信息员工信息”数据导入到当前的文数据导入到当前的文件中，轻松获取外部人事档案信息。件中，轻松获取外部人事档案信息。（1 1）选中）选中“员工人事档案管理表员工人事档案管理表”工作簿中的工作簿中的“员工档员工档案信息案信息”工作表。工作表。（2 2）选择【数据】菜单中【导入外部数据】命令的子）选择【数据】菜单中【导入外部数据】命令的子命令【导入数据】命令【导入数据】，打开【选取数据源】对话框。，打开【选取数据源】对话框。（3 3）选择数据源文件）

23、选择数据源文件“员工信息员工信息.prn.prn”，（4 4）从对话框的）从对话框的“原始数据类型原始数据类型”中选择中选择“固定长度固定长度”作为最合适的文件类型，在作为最合适的文件类型，在“导入起始行导入起始行”文本框中保文本框中保持默认值持默认值“1 1”，在文件原始格式下拉列表中选择在文件原始格式下拉列表中选择“936936：任务 2简体中文（简体中文（GB2312GB2312）”。步骤（5 5）按文本导入向导进行操作。按文本导入向导进行操作。在在“数据预览数据预览”区内设区内设置字段宽度，也就是列间隔；设置每列的数据类型。置字段宽度，也就是列间隔；设置每列的数据类型。（6 6）单击【

24、确定】按钮，完成数据导入。）单击【确定】按钮，完成数据导入。数据导入是原来学过的内容，教师可以先以提问的形式复习回顾，再让学生实操。注意点：添加分列线。创建创建“员工个人详细资料员工个人详细资料”链接链接任务 3（1 1）链接到已有文件。）链接到已有文件。（2 2）链接到新建文件。）链接到新建文件。动手操作3 分钟任务 3步骤任务 3 较为简单，提醒学生仔细认真，避免误操作。查阅查阅“员工个人详细资料员工个人详细资料”任务 4动手操作2 分钟当需要查阅某个员工的个人详细资料时，我们可通过当需要查阅某个员工的个人详细资料时，我们可通过查阅刚插入激活超链接进行访问。激活超链接进行访问。的链接任务

25、4步骤-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=任务 5复制复制“员工档案信息员工档案信息”工作表工作表动手操作2 分钟（1 1）选中）选中“员工档案信息员工档案信息”工作表。工作表。非常熟练的（2 2）将该工作表复制三份，并分别重命名为）将该工作表复制三份，并分别重命名为“女员工女员工内容，学生实档案信息档案信息”、“入职时间最早的入职时间最早的 5 5 名员工信息名员工信息”、“20052005操任务 5年年-2007-2007 年入职的员工年入职的员工”。步骤任务 6通过数据筛选查询女员工档案信息通过数据筛选查询女员工档案信息（1

26、1）选中“女员工档案信息”工作表。）选中“女员工档案信息”工作表。（2 2）将光标置于任一非空单元格，选择【数据】菜单）将光标置于任一非空单元格，选择【数据】菜单中【筛选】命令中的子命令【自动筛选】中【筛选】命令中的子命令【自动筛选】。任务 6（3 3）系统自动在每个标题字段上添加一个下拉按钮。）系统自动在每个标题字段上添加一个下拉按钮。步骤（4 4）设置筛选条件。单击【性别】右侧下拉按钮，从）设置筛选条件。单击【性别】右侧下拉按钮，从列表中选择“女”列表中选择“女”，单击【确定】，单击【确定】。通过数据筛选查询入职时间最早的通过数据筛选查询入职时间最早的 5 5 名员工信息名员工信息任务 7

27、（1 1）选中“入职时间最早的）选中“入职时间最早的 5 5 名员工信息”工作表。名员工信息”工作表。（2 2）将光标置于任一非空单元格，选择【数据】菜单）将光标置于任一非空单元格，选择【数据】菜单中【筛选】命令中的子命令【自动筛选】中【筛选】命令中的子命令【自动筛选】。任务 7（3 3）设置筛选条件。）设置筛选条件。步骤（4 4）单击【入职时间】右侧下拉按钮，从列表中选择）单击【入职时间】右侧下拉按钮，从列表中选择“（前（前 1010 个个.”选择“最小”选择“最小”、“5 5”、“项”“项”，单击【确，单击【确定】按钮。定】按钮。通过数据筛选查询通过数据筛选查询 20052005 年年-2

28、007-2007 年入职的员工年入职的员工任务 8（1 1）选中）选中“20052005 年年-2007-2007 年入职的员工年入职的员工”工作表。工作表。（2 2）将光标置于任一非空单元格，选择【数据】菜单）将光标置于任一非空单元格，选择【数据】菜单中【筛选】命令中的子命令【自动筛选】中【筛选】命令中的子命令【自动筛选】。任务 8（3 3）设置筛选条件。单击“入职时间”右侧的下拉按）设置筛选条件。单击“入职时间”右侧的下拉按步骤钮，从列表中“钮，从列表中“（自定义自定义”，打开【自定义自动筛选，打开【自定义自动筛选方式】对话框，设置筛选条件：方式】对话框，设置筛选条件：“大于或等于”“大于

29、或等于”、“2005-1-12005-1-1”与“小于或等于”与“小于或等于”、“2007-12-312007-12-31”。（4 4）单击【确定】按钮。）单击【确定】按钮。统计公司各部门员工人数统计公司各部门员工人数任务 9教师演示，然后学生实操4 分钟教师讲解并进行演示筛选方法，学生结合演示进行实操在任务 6 的基础上讨论如何设置筛选条件，并实操动手操作2 分钟在任务 6、7的基础上讨论如何设置筛选条件，并实操-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=（1 1）将）将 Sheet2Sheet2 工作表重命名为工作表重命名为“公司各部门

30、员工人公司各部门员工人数数”。（2 2）建立表格框架。）建立表格框架。（3 3）选中选中“公司各部门员工人数公司各部门员工人数”工作表的工作表的 B3B3 单元格。单元格。（4 4）输输 入入 公公 式式“=COUNTIF(=COUNTIF(员员 工工 档档 案案 信信任务 9息息!$D$2:$D$26,!$D$2:$D$26,行政部行政部)”，按【，按【EnterEnter】键，在】键，在 B3B3步骤中显示中显示“行政部行政部”人数。人数。（5 5）同样地，统计出其他部门的人数。）同样地，统计出其他部门的人数。数。数。任务 10创建公司各部门人员比例图创建公司各部门人员比例图（1 1）选中

31、选中“公司各部门员工人数公司各部门员工人数”工作表中的工作表中的 A2:B9A2:B9 单单元格区域。元格区域。（2 2）创建）创建“三位饼图三位饼图”。任务 10（3 3）设置图表的标题格式。）设置图表的标题格式。步骤教学反思本项目通过制作“员工人事档案管理表”，主要介绍了工作簿的创建、工作表重命名、导入外部数据创建员工档案信息表、利用超链接查阅员工个人详细资料、使用筛选查询员工档案信息、使用 COUNTIF 函数进行各部门人数统计。在此基础上，利用统计数据创建各部门人员比例图表。实 训 教 案2015 年 4 月 9 日实训名称实训地点知识目标职业技能能力训练目标目标素质目标第 6周时第

32、9、10课制作员工培训管理表305数据的有效性、成绩的统计和计算，美化工作表函数 AVERAGE、SUM、COUNTIF、RANK 的应用本项目通过制作“员工培训管理表”，来学习 Excel 软件在培训管理方面的应用。实实 训训 过过 程程 设设 计计-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=实训步骤任务提出实 训 内 容学生活动时间分配本项目通过制作“员工培训管理表”，来介绍Excel 软件在培训管理方面的应用。任务 1任务任务 1 1创建工作簿，重命名工作表创建工作簿，重命名工作表（1 1）启动）启动 Excel 2003Excel

33、2003，新建一个空白工作簿。，新建一个空白工作簿。（2 2）将创建的工作薄以）将创建的工作薄以“员工培训管理表员工培训管理表”为名保存。为名保存。任务 1（3 3）将）将“员工人事档案管理表员工人事档案管理表”工作簿中的工作簿中的 Sheet1Sheet1 工工步骤作表重命名为作表重命名为“员工培训成绩表员工培训成绩表”。任务任务 2 2创建创建“员工培训成绩表员工培训成绩表”框架框架任务 2（1 1）在）在“员工培训成绩表员工培训成绩表”中输入工作表标题。中输入工作表标题。任务 2（2 2）输入表格标题字段。）输入表格标题字段。步骤任务任务 3 3输入输入“序号序号”任务 3利用填充句柄，

34、输入序号。利用填充句柄，输入序号。任务 3步骤任务任务 4 4输入输入“姓名姓名”和和“部门部门”任务 4（1 1）输入员工）输入员工“姓名姓名”。任务 4（2 2）输入员工）输入员工“部门部门”。步骤任务任务 5 5设置各培训项目成绩的数据有效性设置各培训项目成绩的数据有效性任务 5-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=在输入数据的过程中，为尽量减小数据输入的错误，在输入数据的过程中，为尽量减小数据输入的错误，提高数据的有效性，可对要输入数据的单元格区域设提高数据的有效性，可对要输入数据的单元格区域设任务 5置有效性规则，使输入的数

35、据在规定的范围之内。置有效性规则，使输入的数据在规定的范围之内。步骤即输入的成绩范围在即输入的成绩范围在 0 0100100 之间。之间。任务任务 6 6统计统计“平均成绩平均成绩”和总成绩和总成绩任务 6任务任务 6 6统计统计“平均成绩平均成绩”和总成绩和总成绩任务 6步骤任务任务 7 7利用利用 RANKRANK 函数排列员工培训成绩的名次函数排列员工培训成绩的名次任务 7（1 1）选中）选中 I3I3 单元格。单元格。（2 2）输入公式）输入公式“=RANK(H3,$H$3:$H$21)=RANK(H3,$H$3:$H$21)”按【按【EnterEnter】键确认。键确认。任务 7（3

36、 3）选中）选中 I3I3 单元格，拖动填充句柄至单元格，拖动填充句柄至 I21I21 单元格，单元格，步骤将公式复制到将公式复制到 I4:H21I4:H21 单元格区域中，排列出所有名单元格区域中，排列出所有名次。次。任务任务 8 8统计各培训项目的平均成绩统计各培训项目的平均成绩任务 8（1 1）在）在 A22A22 单元格中输入单元格中输入“各培训项目平均成绩各培训项目平均成绩”。（2 2）选中选中 D22D22 单元格，单元格，输入公式输入公式“=AVERAGE(D3:D21)=AVERAGE(D3:D21)”，任务 8按【按【EnterEnter】键确认。】键确认。步骤（5 5）选中

37、）选中 D22D22 单元格，拖动填充句柄至单元格，拖动填充句柄至 F22F22 单元格，单元格，将公式复制到将公式复制到 E22:F22E22:F22 单元格区域中单元格区域中任务任务 9 9利用利用 COUNTIFCOUNTIF 函数统计考核不合格的员工人函数统计考核不合格的员工人数数任务 9这里，这里，假定员工的所有项目平均成绩低于假定员工的所有项目平均成绩低于 8080 分，分，则认则认定为不合格。定为不合格。（1 1）在）在 G22G22 单元格中输入单元格中输入“不合格人数不合格人数”。任务 9（2 2）选选 中中I22I22单单 元元 格格，输输 入入 公公 式式步骤“=COUN

38、TIF(G3:G21,80)=COUNTIF(G3:G21,80)”。（3 3）按【）按【EnterEnter】键确认。】键确认。-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=任务 10任务任务 1010设置单元格数据格式设置单元格数据格式将将“平均成绩平均成绩”和和“各培训项目平均成绩各培训项目平均成绩”的数据保留为的数据保留为任务 101 1 位小数。位小数。步骤教学反思本项目通过制作“员工培训管理表”，主要介绍了工作簿的创建、工作表重命名、数据有效性设置、函数AVERAGE、SUM、COUNTIF 和 RANK 的应用、美化工作表等。在

39、此基础上，利用培训成绩表中的数据，通过数据透视表和数据透视图对员工培训成绩进行汇总、分析，使表格中的数据更为直观、生动。实 训 教 案2015 年 4 月 16 日实训名称实训地点知识目标职业技能能力训练目标目标素质目标第 7周时第 11、12课制作员工工资管理表305会制作“员工工资管理表”，如基本工资、绩效工资、补贴、扣款等函数 YEAR、ROUND、VLOOKUP、IF、INDEX、ROU、COLUMN、INT 的使用会制作数据透视表和数据透视图实实 训训 过过 程程 设设 计计实训步骤实 训 内 容学生活动时间分配任务提出本项目通过制作“员工工资管理表”来介绍 Excel 软件在工资管

40、理方面的应用。-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=任务任务 1 1创建工作簿，重命名工作表创建工作簿，重命名工作表（1 1）启动）启动 Excel 2003Excel 2003，新建一个空白工作簿。，新建一个空白工作簿。（2 2）将创建的工作薄以）将创建的工作薄以“员工工资管理表员工工资管理表”为名保存。为名保存。任务 1（3 3）将）将“员工人事档案管理表员工人事档案管理表”工作簿中的工作簿中的 Sheet1Sheet1 工工步骤作表重命名为作表重命名为“工资基础信息表工资基础信息表”。任务 1任务任务 2 2导入导入“员工信息员

41、工信息”任务 2将行政篇中导出的将行政篇中导出的“员工信息员工信息”数据导入到当前工作表数据导入到当前工作表任务 2中，作为员工中，作为员工“工资基础信息表工资基础信息表”的数据。的数据。步骤任务任务 3 3创建创建“工资基础信息表工资基础信息表”（1 1）选中）选中“工资基础信息表工资基础信息表”工作表。工作表。任务 3（2 2）删除）删除“身份证号码身份证号码”、“学历学历”、“职称职称”、“性别性别”步骤和和“出生日期出生日期”列的数据。列的数据。任务任务 3 3创建创建“工资基础信息表工资基础信息表”任务 3（1 1）选中）选中“工资基础信息表工资基础信息表”工作表。工作表。（2 2）

42、删除）删除“身份证号码身份证号码”、“学历学历”、“职称职称”、“性别性别”和和“出生日期出生日期”列的数据。列的数据。任务 3步骤任务 3任务任务 4 4创建创建“加班费结算表加班费结算表”（1 1）复制复制“工资基础信息表工资基础信息表”，将复制后的工作表重命将复制后的工作表重命名为名为“加班费结算表加班费结算表”。（2 2）删除）删除“入职时间入职时间”、“绩效工资绩效工资”和和“工龄工资工龄工资”列。列。（3 3）在）在 E1E1、F1F1 单元格中分别输入标题单元格中分别输入标题“加班时间加班时间”和和任务 4“加班费加班费”。步骤（4 4）输入加班时间。）输入加班时间。（5 5）计

43、算机加班费。）计算机加班费。加加 班班 费费 的的 计计 算算 方方 法法 为为：加加 班班 费费=基基 本本 工工 资资/30/8*1.5*/30/8*1.5*加班时间加班时间任务任务 5 5创建创建“考勤扣款结算表考勤扣款结算表”任务 5任务 4-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=（1 1）复制复制“工资基础信息表工资基础信息表”，将复制后的工作表重命将复制后的工作表重命名为名为“考勤扣款结算表考勤扣款结算表”。（2 2）删除）删除“入职时间入职时间”、“绩效工资绩效工资”和和“工龄工资工龄工资”列。列。（3 3）在）在 E1:

44、K1E1:K1 单元格中分别输入标题单元格中分别输入标题“迟到迟到”、“迟到迟到扣款扣款”、“病假病假”、“病假扣款病假扣款”、“事假事假”、“事假扣款事假扣款”和和“扣款合计扣款合计”。（4 4）输入）输入“迟到迟到”、“病假病假”、“事假事假”的数据。的数据。任务 5（5 5）计算）计算“迟到扣款迟到扣款”。步骤（6 6）计算）计算“病假扣款病假扣款”。这里，假设每病假一天扣款为当日工资收入的这里，假设每病假一天扣款为当日工资收入的50%50%，即，即“病假扣款病假扣款=基本工资基本工资/30*0.5*/30*0.5*病假天数病假天数”。（7 7）计算）计算“事假扣款事假扣款”。这里，假设

45、每事假一天扣款为当日工资收入，即这里，假设每事假一天扣款为当日工资收入，即“事假扣款事假扣款=基本工资基本工资/30*/30*事假天数事假天数”。（8 8）计算）计算“扣款合计扣款合计”。任务任务 6 6创建创建“员工工资明细表员工工资明细表”任务 6（1 1）将）将 Sheet2Sheet2 工作表重命名为工作表重命名为“员工工资明细表员工工资明细表”。（2 2）参创建员工工资明细表的框架。）参创建员工工资明细表的框架。（3 3）填充）填充“编号编号”、“姓名姓名”和和“部门部门”数据。数据。（4 4）导入导入“基本工资基本工资”、“绩效工资绩效工资”、“工龄工资工龄工资”和和“加加班费班费

46、”数据。数据。选中选中 D3D3 单元格。单元格。选择【插入】菜单中的【函数】命令，从函数选择【插入】菜单中的【函数】命令，从函数列表中选择列表中选择“VLOOKUPVLOOKUP”函数，函数，打开打开【函数参数】【函数参数】对话框，对话框，设置参数。设置参数。单击【确定】按钮，导入相应的单击【确定】按钮，导入相应的“基本工资基本工资”数数任务 6据。据。步骤选中选中 D3D3 单元格，用鼠标拖动其填充句柄至单元格，用鼠标拖动其填充句柄至 D26D26单元格，单元格，将公式复制到将公式复制到 D3:D26D3:D26 单元格区域中，单元格区域中，可导入可导入所有员工的基本工资。所有员工的基本工

47、资。（5 5）同样地方式，分别导入）同样地方式，分别导入“绩效工资绩效工资”、“工龄工资工龄工资”和和“加班费加班费”数据。数据。（6 6）导入）导入“加班费加班费”数据。数据。（7 7）计算）计算“应发工资应发工资”。（8 8）填充）填充“养老保险养老保险”数据。数据。这里，养老保险的数据为个人缴纳部分，计算方这里，养老保险的数据为个人缴纳部分，计算方法为：养老保险法为：养老保险=（基本工资（基本工资+绩效工资）绩效工资）*8%*8%（9 9）填充）填充“医疗保险医疗保险”数据。数据。-完整版学习资料分享-=WORD=WORD 完整版完整版-可编辑可编辑-专业资料分享专业资料分享=任务 7这

48、里，医疗保险的数据为个人缴纳部分，计算方这里，医疗保险的数据为个人缴纳部分，计算方法为：医疗保险法为：医疗保险=（基本工资（基本工资+绩效工资）绩效工资）*2%*2%（1010）填充）填充“失业保险失业保险”数据。数据。这里，失业保险的数据为个人缴纳部分，计算方这里，失业保险的数据为个人缴纳部分，计算方法为：失业保险法为：失业保险=（基本工资（基本工资+绩效工资）绩效工资）*1%*1%（1111）导入）导入“考勤扣款考勤扣款”数据。数据。（1212）计算）计算“税前工资税前工资”。（1313）计算）计算“个人所得税个人所得税”。应交个人所得税额应交个人所得税额=应纳税所得额应纳税所得额*适用税

49、率适用税率-速速算扣除数，个人所得税税率见表算扣除数，个人所得税税率见表 2-12-1 所示。所示。（1313）计算）计算“实发工资实发工资”。任务任务 7 7格式化格式化“员工工资明细表员工工资明细表”（1 1）设置工作表的标题格式。）设置工作表的标题格式。（2 2）将表中所有的数据项格式设置为）将表中所有的数据项格式设置为“会计专用会计专用”格格式，保留式，保留 2 2 位小数，货币符号为位小数，货币符号为“无无”。任务 7（3 3）为表格设置内细外粗的蓝色边框。）为表格设置内细外粗的蓝色边框。步骤（4 4）为为“应发工资应发工资”和和“税前工资税前工资”两列的数据区域添加两列的数据区域添

50、加“淡蓝淡蓝”色底纹，为色底纹，为“实发工资实发工资”列数据区域添加列数据区域添加“天蓝天蓝”色底纹。色底纹。任务任务 8 8制作制作“工资条工资条”任务 8（1 1）将）将 Sheet3Sheet3 工作表重命名为工作表重命名为“工资条工资条”。（2 2）选中）选中“工资条工资条”工作表的工作表的 A1A1 单元格。单元格。（3 3）在）在“编辑栏编辑栏”中输入下面的公式，然后按【中输入下面的公式，然后按【EnterEnter】键确认。键确认。=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,员