用Excel管理销售数据信息教师工作页.doc

江苏省太仓职业教育中心校计算机应用基础教师工作页课题名称用Excel管理销售数据信息教师周建秋日期2007-09-18任务周期6 课时案例资源50. 教学准备下发相关素材教学目标知识与技能：1. 操作技能掌握以下Excel操作技能 数据表的快速创建 跨表引用汇总多表数据 对数据信息进行批注 函数的用法 分类汇总的使用过程与方法：体验用Excel管理销售数据信息，在引导、自主与协作中积累经验。情感、态度、价值观：培养学生细致认真、务实、求精的工作态度和价值观。教学重点对数据信息进行批注函数的用法分类汇总的使用教学难点单元格的相对引用地址、绝对引用地址和混合引用地址分类汇总教 学 过 程教学环节教学组织情境创设阶段【教学准备】分发文件原稿Excel文件【情境创设，提出任务】通过创设工作过程导向，对于企业或销售部门来说，销售数据信息有很多种，包括产品销售统计汇总表、销售客户信息表和销售人员销售额统计等。对这些日常工作中的数据进行有效的管理是企业很重要的一个基本任务。 采用分组讨论形式，由教师进一步启发分析课题的意义及课题实现的要素，实现明确课题任务分解。分解提示阶段【任务分解】共分五个任务任务一：产品销售统计汇总表在产品汇总时，常见的有两种情况：一种是将每月的总额直接填写到汇总表进行汇总查看：另一种是为每个分表的具体产品销售额进行汇总计算，再把结果填写到汇总表。要求：用工作组高效数据相似表案例实现：步骤1打开Excel并新建一个文档，默认有三个空白Excel工作表“Sheet1”至“Sheet3”。下面来创建两个表。首先选中“Sheet2”工作表，然后按住键盘“Shift”键的同时再单击“Sheet3”工作表标签，就可以将“Sheet2”和“Sheet3”两个表同时选中。步骤2单击“插入/工作表”菜单命令，在“Sheet2” 工作表左侧便创建了“Sheet4”和“Sheet5”工作表。步骤3下面为所有的工作表更改名称。用鼠标左键双击“Sheet1”工作表标签，“Sheet1”工作表的名称会变成编辑状态，直接用键盘输入“一月”后回车确认，便可以将工作表的名称进行改变。依次类推，将每个工作表的标签名称进行更改，更改后如下：步骤4为了今后查询方便，可以将当前默认的“工作表标签颜色”进行更改。先选中“一月”工作表，然后按住键盘“Shift”键的同时单击“三月”工作表标签，将“一月”、“二月”和“三月”这3个表同时选中。步骤5用鼠标右键单击这3个表中任意一个表的标签，在弹出的快捷菜单中选择“工作表标签颜色”命令，在随后打开的“设置工作表标签颜色”对话框中选择浅绿颜色。步骤6单击“确定”按钮后这3个工作表的标签会变成浅绿色，利用同样的方法将后两个“汇总”工作表的标签设置成深蓝色，设置完成步骤7下面就该利用“工作组”来对这5个工作表绘制相同的表格了。组成“工作表组”的方法就是将所有的工作表同时选中。先选择“一月”工作表，然后按住键盘“Shift”键的同时单击最后一个“汇总2”工作表标签，这样就将所有的工作表组成了一个“工作表组”。可以在Excel窗口的标题栏中看到“工作组”字样。步骤8将所有工作表组成“工作组”后，在当前的 一个表中绘制所需的表格框线，并输入表标题（先按照“一月”工作表内容填，待会再修改），再填写表“标题行”和“标题列”信息，并进行必要的格式美化。步骤9由于是组成“工作组”后绘制并进行的表格美化，所以现在这5个表的内容和格式会完全一致。单击任意一个工作表标签可以解散“工作组”还原成单表工作状态，然后再根据实际情况，修改刚才统一添加的文字。任务二：跨表引用汇总多表数据要求：1、直接跨表引用生成“汇总1” 表数据 2、通过函数中的“相对地址引用”来计算“汇总2”表一、直接跨表引用生成“汇总1” 表数据步骤1打开“销售统计汇总表”工作簿，将“一月”、“二月”和“三月”3个工作表组成“工作组”，然后选择所有的数据单元格（B4：E7），包含横向的“合计”与纵向的“合计”单元格。步骤2单击“常用”工具栏中的“自动求和” 按钮，会自动将横向的各个“产品”合计与纵向的各个“部门”合计值计算出来。步骤3选择“汇总1”工作表，选择B4单元格，用键盘输入“=”，然后用鼠标单击“一月”工作表标签，切换到“一月”工作表后再选择“部门1”合计单元格（B7），此时在“编辑栏”中会出现公式“= 一月！B7”。步骤4按键盘的“Enter”键后返回“汇总1”工作表，在B4单元格中自动将“一月”工作表B7单元格内的数据引用了过来。由于地址公式中采用的是“相对地址引用”，所以可以通过鼠标指针向右拖曳到B4单元格右下角“填充柄”的方法来自动填充“部门2”和“部门3”的“合计”数据，步骤5 2月合计和3月合计中的数据引用步骤同上。二、通过函数中的“相对地址引用”来计算“汇总2”表步骤1打开“汇总2”工作表，选择“部门1”中“产品1”单元格（B4单元格）。步骤2用鼠标单击“常用”工具栏中的自动求和” 按钮，在B4单元格和“编辑栏”中出现公式“=SUM()”。步骤3用鼠标单击“一月”工作表标签，再按住键盘“Shift”键的同时单击“三月”工作表标签，当3个分表被全选之后再单击B4单元格，这样3个分表B4单元格数据设置为SUM求和函数的参数。步骤4按键盘的“Enter”键后返回“汇总2”工作表，B4单元格也就是“部门1”的“产品1”数据已经汇总计算完成。步骤5由于公式中采用的是“相对地址引用”，所以可以通过鼠标指针向右拖曳到B4单元格右下角“填充柄”，将公式向右拖曳至D4单元格，将所有部门的“产品1”汇总计算完成。步骤6计算完成后，B4到D4单元格还是被选中状态，再用鼠标指针对准D4单元格的“填充柄”向下将公式拖曳至D7单元格，便可将所有的分表数据全部汇总完成。步骤7全部计算完成后，将所有数据选中，包含后面的“合计”列与下方的“合计”行，然后用鼠标单击“常用”工具栏中的“自动求和” 按钮，将合计结果计算完成。任务三：用批注对数据进行人性化的提示要求： 1、用户信息更改2、添加批注一、用户信息更改【工具】【选项】菜单命令，打开【选项】对话框的【常规】选项卡。在对话框下方【用户名】后面的文本框中确认信息是否正确，进行修改。二、添加批注步骤1选中进行批注的单元格（B4），单击鼠标右键（或选择“插入”菜单），在弹出的快捷菜单中选择“插入批注”命令，会立即在该单元格右侧显示“批注文本框”。步骤2在其中输入批注信息，完成“批注”填写。以后只会在单元格右上角显示一个红色的小标，当用鼠标指向该单元格时，“批注”信息会自动出现。步骤3用同样的方法为所有需要“批注”的单元格（C10和I6）创建各自的批注信息。步骤4由于B17单元格中的“批注”信息与刚刚设置的B4单元格批注内容完全一致，所以可以不采用重新创建的方法。对B4单元格进行复制操作，然后再用鼠标右键单击B17单元格，在弹出的快捷菜单中选择“选择性粘贴”命令，打开“选择性粘贴”对话框。步骤5选择“粘贴”中的“批注”选项，也就是说虽然复制了B4单元格，但是B17中只粘贴了B4中的“批注”内容，其他的数据、格式等信息并不要。 步骤6单击“确定”按钮，完成“批注”信息的创建操作。任务四：用函数快速统计销售数据和奖金要求：1、计算“订单数”2、计算“订单总额”3、计算“销售奖金”金额1、计算“数”（使用COUNTIF函数）步骤1将光标先定位在H3“Jarry”的“订单数”单元格。步骤2在单元格中输入函数公式“=COUNTIF（D3：D17，G3）。步骤3按键盘的“Enter”键得出计算的结果4，然后利用这个单元格右下角的“填充柄”向下填充复制公式，计算得出每位销售人员的“订单数”。2、计算“订单总额”步骤1将光标定位在I3“Jarry”的“订单总额” 单元格。步骤2在单元格中输入函数公式“=SUMIF（D3：D17，G3，C3：C17）”。步骤3按键盘的“Enter”键得出计算的结果，然后利用这个单元格右下角的“填充柄”向下填充复制公式，计算得出每位销售人员的“订单总额”。3、计算“销售奖金”金额步骤1将光标定位在J3“Jarry”的“销售奖金”单元格。步骤2在单元格中输入函数公式“=IF(I33000000,I3*0.05,I3*0.08)步骤3按键盘的“Enter”键得出计算的结果，然后利用这个单元格右下角的“填充柄”向下填充复制公式，计算得出每位销售人员的“销售奖金”金额。任务五：用分类汇总快速统计销售数据要求：1、分类汇总“订单号”的计数2、分类汇总订单金额的总和一、分类汇总“订单号”的计数步骤1将光标定位在左侧表“销售人员”一列数据中任意一个单元格内，然后单击“常用”工具栏中的“升序”按钮，Excel立即会以“销售人员”为依据对数据进行排序，并将相同的人员名单放在一起，做出分类效果。步骤2选择“数据/分类汇总”菜单命令，打开“分类汇总”对话框。在对话框“分类字段”中选择分好类的“销售人员”，在“汇总方式”中选择“计数”方式，在“选顶汇总项”中选择“订单号”选项。步骤3单击确定按钮返回Excel工作表，在每个销售人员下方多出一行汇总计数行，在订单号一列分别显示出了各自的订单号个数，并在表最后一行显示出了所有人员总的订单号计数。步骤4同时，工作表列号前显示出了1、2、3的级别符号，当前的这种效果是级别3，若单击级别2则会隐藏每个销售人员的具体订单内容，只显示分类汇总的计数结果。步骤5查看完成后，可以再次选择数据/分类汇总菜单命令，打开分类汇总对话框，选择左下角的全部删除按钮将分类汇总的结果删除，恢复原来的效果。二、分类汇总订单金额的总和步骤1将光标定位在数据表中任意一个单元格内，选择数据分类汇总菜单命令，打开分类汇总对话框。在对话框分类字段中选择分好类的销售人员在汇总方式中选择求和方式，在选择汇总选项选择订单金额选项。步骤2单击确定按钮返回Excel工作表，在每个销售人员下方多出一汇总行，在订单金额一列分别显示出了各自的订单金额汇总，并在表最后一行显示出了所有人员的订单金额汇总。步骤3同样会在工作表中显示出了1、2、3级别符号，单击级别号2则可以察看每个销售人员的分类汇总结果。步骤4查看完成后，可以再次选择数据/分类汇总菜单命令，打开分类汇总对话框，选择左下角的全部删除按钮将分类汇总的结果删除，恢复原来的效果。【操作提示】采用教师演示或指导学生演示的方法相对地址引用A：单击“常用”工具栏中的自动求和” 按钮，在B4单元格和“编辑栏”中出现公式“=SUM()”。批注的插入A：选择要进行批注的单元格（B4），然后单击鼠标右键（或选择“插入”菜单），在弹出的快捷菜单中选择“插入批注”命令，会立即在该单元格右侧显示“批注文本框”。分类汇总A：先排序分类汇总 选择“数据/分类汇总”菜单命令，打开“分类汇总”对话框。在对话框“分类字段”中选择分好类的项目，在“汇总方式”中选择方式，在“选顶汇总项”中选择选项。任务一重点引导思考和提示：l 如何创建新工作表l 工作表如何重命名l 工作表标签颜色如何设置l 怎样将几张工作表组成工作表组l 如何对工作表数据进行统一删除或将一个表的数据填充到其他同组工作簿中任务一完成后，组长检查并反馈。填写反馈表。任务二重点引导思考和提示：l 怎样进行跨表引用地址任务二完成后，组长检查并反馈。填写反馈表。任务三重点引导思考和提示：l 如何对用户信息进行设置l 批注的设置、删除和隐藏l 怎样对重复数据进行标志任务三完成后，组长检查并反馈。填写反馈表。任务四重点引导思考和提示：l 如何进行底纹背景的填充l 怎样去除工作表中多余的“虚框”l 如何进行条件计数函数COUNTIF的计算l 条件求和函数SUMIF的计算怎样使用？任务四完成后，组长检查并反馈。填写反馈表。任务五重点引导思考和提示：l 分类汇总前为何要先进行排序？l 使用分类汇总有什么注意点？l 怎样将已经分类汇总的工作表恢复到原效果？任务五完成后，组长检查并反馈。填写反馈表。任务实施阶段采用教师引导通过学生分组自主学习和探究，让学生掌握预期的操作技能，完成规定任务。期间通过组长协助检查、学生任务反馈评价表相结合的方式，把控每一个任务教学中学生的状态。展示交流阶段 通过展示学生作品，并结合典型问题对课题的重难点进行讲评，帮助学生全面把握课题知识技能点，为后一阶段的迁移拓展打好扎实的基础。【作品展示】按组选取部分作品通过多媒体教室转播展示。1、 【疑难交流】可通过学生间互相问答的方式实现，提问和作答均有加分奖励。什么是单元格的相对引用地址和绝对引用地址？在公式移动或复制时，相对引用与绝对引用有何不同？答：相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。 具体情况举例说明： 1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2 当将公式复制到D1单元格时变为：=B1+C1 2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 当将公式复制到D1单元格时仍为：=$A$1+$B$1 3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$