任务十一工资信息表制作情境教案.doc

情境三 Excel操作
学习情境： 轿车全车线路的基本检查

学习任务11
工资信息表的制作
计划学时
2
教学目的
使学生掌握工作薄的建立、数据的自动填充、行与列的插入及设立，掌握VLOOKUP函数和HLOOKUP函数的使用，掌握数据的排序以及数据的分类汇总。
职业能力目的
专业能力
方法能力
社会能力
1掌握表格设计能力及快速输入数据的能力；
2. 掌握设立单元格格式的能力；
3. 灵活运用相关函数的能力；
4. 掌握数据排序的应用能力；
5.掌握数据分类汇总的能力
1.数据分析、归纳能力；
2.数据录入能力；
3.高级函数应用能力；
4. 排序应用能力；
5.分类汇总应用能力；

1.鼓励学生动手、动脑，培养勤于动手、积极探究的精神；
2.培养学生运用信息技术解决实际问题的能力；
3.提高学生整理、解决信息的能力，促进学生信息素养的形成；
4.熟悉Excel的办公能力；
5.培养学生的职业素养
学习情境描述
设计并输入工资信息表后，运用Excel的函数进行相关计算，得到实发工资，并对部门进行排序后进行按部门汇总工资，还按税法规定，分别制作不同扣税规定的工资表。
教学环境
课堂教学、案例分析
教学方法
讲授法、示范法、案例分析、分组动手实践
教学组织
课堂教学完毕后，上机完毕相关案例，巩固所学知识，理论转化为实践
实行环节一
员工工资表(20分)

员工工资表重要由标题、员工信息、员工工资和实发工资组成。
本范例的最终效果如下：

一、 员工工资表的设计
1.在新建表格的单元格内输入数据，如图所示。

2.选中A1:I20单元格，将单元格中的文字对齐方式设立为“居中”。
3.选中单元格A1，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。
4.在弹出的“插入”对话框中选中“整行”单选按钮，单击“拟定”按钮，即可在行1上方插入一行空白单元格。
5.选中单元格A1，输入文字“五星电器员工工资表”。
6.选中单元格A1:I1，将其合并单元格并居中，并字体设立为“黑体”，字号为22。
二、自动求和数据
1.选中单元格A1：I21，复制所选的内容，单击工作表标签Sheet2，选中单元格A1，粘贴所复制的内容。
2.双击工作表标签Sheet2，并输入文字“工资表”。
3.选中单元格I3，单击工具栏上的自动求和按钮，单元格会自动显示求和公式。
4.按【Enter】键确认，在单元格中I3中得到实发工资金额。
5.选中单元格I3，移动鼠标指针至单元格右下角，当鼠标指针呈十字形状时，按住鼠标左键并向下拖动至单元格I21，复制公式至其他单元格，如图所示。

6.选中单元格A1，单击格式菜单选择行高选项，弹出“行高”对话框，在“行高”文本框中输入40，单击“拟定”，调整单元格的行高。
7.选中单元格A2:I21，采用同样的方法，调整单元格的行高为15
8.保持单元格处在选中状态，用紫色的细线设立单元格的内外边框。
9.设立单元格的填充背景，如图所示。
三、分类汇总
1.双击工作表标签Sheet3，将其重命名为“部门工资汇总”。
2.选中所有单元格，复制所选内容，切换到“部门工资汇总”工作表，单击A1，粘贴复制的内容，并分别调整成之前的行高。
3.选中单元格D4，单击“数据”菜单上的“排序”选项，在排序对话框中，选择主关键字按部门升序排列，效果如图所示。
4．单击“数据”菜单上的“分类汇总”选项，弹出“分类汇总”对话框，在“分类字段”下拉列表框中选择“部门”选项，咱“汇总方式”下拉列表框中选择“求和”选项，在“选定汇总项”列表框中选中“基本工资”、“奖金”和“实发工资”复选框，并选中“替换当前分类汇总”和“汇总结果显示在数据下方”复选框，如图所示：
5.单击“拟定”按钮，工作表区域将显示按部门汇总的基本工资、奖金和实发工资，在工作表左侧显示分级目录数，如图所示。
6.移动鼠标指针至D列右侧单元格分隔线上，双击鼠标左键，自动调整为合适列宽，效果如图所示。

7.单击工作表左侧目录树上的数字2按钮，隐藏每位员工的汇总数据，只显示每个部门的汇总数据，如图所示。

8.选中单元格A2：I26，设立单元格的内外边框颜色为紫色。
9.选中单元格A1，将标题改为五星电器部门工资汇总”。

实行环节二
知识加油站—插入函数、填充数据与分类汇总（5分钟）

一、 插入函数的三种方法
函数是预定义的公式，是一种在需要时可以直接调用的表达式，使用函数可以简化公式、提高工作效率。
1.运用“函数”库选项板插入函数。选中要插入函数的单元格，单击工具栏上的fx按钮，弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择函数类别，在“选择函数”列表框中选择要使用的函数，单击“拟定”按钮弹出“函数参数”对话框，输入或选择参数后，单击“拟定”按钮即可。
2.单击插入菜单下的函数选项，其他同1。
3.手工输入函数。对于简朴函数，可以手工输入，即在单元格或编辑栏中直接输入函数。

二、填充数据的3种方法
选择单元格或单元格区域后会出现一个黑色边框的选区，该选区的右下角会出现一个控制柄，将鼠标移至其上，鼠标指针变为十字形状，通过拖曳这个控制柄可实现数据的快速填充，方法有3种：
1.拖曳控制柄填充相同数据。在起始单元格中输入起始数据，将鼠标指针移至该单元格右下角的控制柄上，按住鼠标左键并拖动至合适的位置，释放鼠标左键即可在单元格内填充相同的数据。
2.填充有规律的数据。在第一个和第二个单元格中输入数据，选择这两个单元格，在其右下角控制柄上按住鼠标左键并向下拖动，至所需位置后释放鼠标，即可在单元格中自动填充有规律的数据。
3.使用对话框填充数据。使用“序列”对话框可快速填充等差、等比、日期的特殊规律的数据，其操作环节如下：
一方面，在起始单元输入数据，并选择需要填充的单元格区域。
另一方面，单击“编辑”菜单下的填充选项，弹出“填充”下拉菜单，选择“系列选项”，弹出“序列”对话框。
最后，在对话框中可设立序列产生的位置，序列的类型、步长值等，单击“拟定”按钮完毕填充。

三、分类汇总
分类汇总是对数据清单进行数据分析的一种方法，它不仅可以对数据库中指定的字段进行分类，然后记录同一类记录的有关信息，记录的内容可以由用户指定，还可以记录同一类记录的条数，也可以对某些数值段进行求和、求平均值和求极值等。
1. 分类汇总的计算方法
分类会的计算方法有分类汇总、总计和自动重新计算等。
² 分类汇总：Excel使用如SUM或AVERAGE等汇总函数进行分类汇总计算，在一个数据清单中可以一次使用多种函数来显示分类汇总。
² 总计：总计值来自于源数据，而不是分类汇总行中的数据。
² 自动重新计算：在编辑明细数据时，Excel将自动重新计算相应的分类汇总和总计值。
2. 汇总报表和图表
将分类汇总添加到清单中时，清单会分级显示，这样可以查看其结构，通过单击分级显示符号可以隐藏明细数据而只显示汇总的数据，这样就形成了汇总报表。
大 大家可以创建一个图表，该图表仅使用包含分类汇总清单中的可见数据，显示或隐藏分级显示清单中的明细数据，图表也会随之更新以显示或隐藏这些数据。
3. 删除分类汇总
当需要取消分类汇总的显示结果，恢复到数据清单的原始状态时，可以选定分类汇总数据清单中的任意单元格，单击“数据”菜单上的分类汇总选项，在弹出的对话框中，单击“所有删除”按钮，即可取消“分类汇总”显示的结果。
实行环节三
税后工资表（25）
本范例的最终效果如图所示。

一、 税前工资表的设计

1.在新建工作表的单元格内输入数据，如图所示。

2.选中单元格A1:H1，将其合并单元格并居中，并字体设立为“黑体”，字号为22
3.选中单元格A1:H21,设立单元格的行高为15。
4.设立单元格的边框和填充颜色。
5．选中单元格G3，单击工具栏上的自动求和按钮，单元格G3会自动显示求和公式。
6.按【Enter】键确认，在单元格中G3中得到实发工资金额。
7.选中单元格G3，移动鼠标指针至单元格右下角，当鼠标指针呈十字形状时，按住鼠标左键并向下拖动至单元格G21，复制公式至其他单元格，如图所示。

8.双击工作表标签Sheet1，将其重新命名为“税前工资表”。

二、创建应交所得税计算表
1.单击工作表标签Sheet2，将其重新命名为“所得税扣除标准表”
，在该工作表中制作如下的表格，并设立其填充颜色和边框颜色。

2.单击工作表标签Sheet3，将其命名为“应交所得税计算表”，输入如图所示的文本，合并单元格并设计相应的行高和列宽。

3.将“税前工资表”工作表中的“编号”、“姓名”和“性别”信息复制到“应交所得税计算表”工作表中。
4.选中单元格D3，在编辑栏中输入函数公式“=VLOOKUP（A3，税前工资表！$A$3:$J$21，7,1）”,按【Enter】键得到该员工的税前工资总额。
5.复制该公式到各列其他单元格。
6.选中单元格D3:D21，设立单元格格式，选择数字选项卡中的“分类”列表中选择“数值”选项，设立“小数位数”为0，选中“使用千位分隔符”复选框，单击“拟定”按钮。
7.选中单元格E3，在编辑栏中输入公式“=HLOOKUP(D3,所得税扣除标准表!$b$3:$E$5,2,1)”,按【Enter】键确认，则可在单元格中得到该员工的税率。
8.复制该公式到各列其他单元格，效果如图所示。

9.选中单元格E3:E21，设立单元格的格式，在“数字”选项卡的“分类”列表中选择“比例”选项，设立“小数位数”为0，单击“拟定”按钮，选中的单元格的数值即为比例。
10.选中单元格F3，在编辑栏中输入函数公式“=HLOOKUP(D3,所得税扣除标准表!$B$3：$E$5,3,1)”，按【Enter】键确认，在单元格F3中得到该员工的扣除数。
11.复制该公式到各列其他单元格，得到其他员工的扣除数。
12.选中单元格G3，在编辑栏中输入公式“=D3*E3-F3”,按钮【Enter】键确认，在单元格G3中得到该员工的应扣所得税额。
13.为工作表设立填充颜色和边框颜色，效果如图所示。

三、创建税后工资计算表
1.单击工作表标签上的“插入工作表”按钮，新建一个空白工作表，重命名为“税后工资表”。
2.输入如图的文字，并相应地调整行高和列宽。

3.将“税前工资表”工作表中的“编号”、“姓名”和“性别”信息复制到税后工资表工作表中。
4.选中单元格D3，在编辑栏中输入“=VLOOKUP(A3,税前工资表!$A$3:$J$21,7,1)”，得到该员工的税前工资。

5.复制该公式到各列其他单元格，得到其他员工的税前工资。
6. 选中单元格E3，在编辑栏中输入“=VLOOKUP(A3,应交所得税计算表!$A$3:$G$21,7,1)”，得到该员工的应扣所得税额。

7.复制该公式到各列其他单元格，得到其他员工的应扣所得税额。
8.选中单元格F3，输入公式“=D3-E3”, 按【Enter】键盘确认，得到该员工的税后工资。
9．复制该公式到各列其他单元格，得到其他员工的税后工资。
10．在G列中输入员工缺勤扣款的金额。
11.选中单元格H3，输入公式“=F3-G3”，按【Enter】键确认，得到该员工的实发工资。
12.复制该公式到各列其他单元格，得到其他员工的实发工资。
13.选中单元格D3:H21，设立单元格的格式，选择“数值”选项，设立“小数位数”为0，选中“使用千位分隔符”复选框，单击“拟定”按钮，效果如图。

14.为工作表设立填充颜色和边框颜色。

实行环节四
学生综合实践（30分钟）
根据课堂教学内容，设计员工工资表，通过函数的计算完毕实发工资表的建立，分别制作不同规定的工资表，并可以对数据进行分类汇总。
实行环节五
检查、评估与反馈（10分钟）
根据学生提问及任务实行的检查情况分析学习中存在的问题，并进一步给予局限性的指导。
检查教学目的是否有效。