信息技术基础 教案-教学设计-2.4销售统计表制作

《信息技术基础》教学设计案例名称销售统计表制作学时4案例简介本项目案例是对商品销售数据进行分析，主要利用Excel中的分类汇总、图表和数据透视表功能实现。知识目标（1）了解数据分析的工具。（2）理解图表和数据透视表的应用。能力目标（1）能选择合适的公式或函数参与运算。（2）能排序Excel电子表格的数据。（3）能分类汇总Excel电子表格的数据。（4）能创建与编辑Excel电子表格的图表。（5）能制作Excel电子表格的数据透视表。素养目标（1）具备数据分析的能力，具有主人翁意识和竞争精神。（2）养成简化数据表达的能力，增强数据可视化表达能力。教学内容教学过程【问题导入】用什么样的方法可以更加直观地分析数据？（目的：引导学生进入课程，从整体上把握本单元将要讲述的核心问题；教学手段：提问与讲解相结合）【案例分析】2023年6月初，公司根据销售部门5月份的某卖场终端机所传送的销售数据表，对销售的各类商品在该卖场5月份的销售额进行汇总分析，并制作图表直观反映销售情况，利用数据透视表分析销售情况，通过分析确定后半年的销售策略。案例分析：在Excel中要完成该项目案例需要首先完善工作表的数据，通过对销售数据排序、分类汇总、图表创建、数据透视表等制作。其具体操作可分为5个任务：完善工作表数据、销售数据排序、分类汇总数据、高级筛选和创建数据透视表。任务一完善数据【任务分析】根据“商品销售情况表”中的数据。计算各商品的降价幅度，降价幅度=（市场定价-成交单价）/市场定价（数值百分比格式，保留两位小数）；计算各商品的销售额置于“销售额(万元)”列，销售额(万元)=数量*成交单价/10000。利用RANK.EQ函数销售额排名由高到低的排名。利用IF函数给出“业绩表现”列的内容:如果销售额大于50万，在相应单元格内填入“业绩优秀”，如果销售额大于30万，在相应单元格内填入“业绩良好”，如果销售额大于20万，在相应单元格内填入"业绩合格"否则在相应单元格内填入"业绩差"。【任务讲演】1．计算各商品的降价幅度。将光标定位到H3单元格→在编辑栏输入公式“=(E3-G3)/E3”→回车→将光标移动到H3单元格右下角，待光标变为黑色十字架时，双击即可；选中H3:H34，单击“开始”→“数字”→“%”（或者Ctrl+Shift+%）,再单击“增加小数位数”两次。2．计算各商品销售额（万元）。将光标定位到J3单元格→在编辑栏输入“=G3*I3/10000”→回车→将光标移动到J3单元格右下角，待光标变为黑色十字架时，双击即可。选中J3:J34设置小数位数两位。3．计算销售额排名。将光标定位到K3单元格→在编辑栏输入“=RANK.EQ(J3,$J$3:$J$34,0)”→回车→将光标移动到K3单元格右下角，待光标变为黑色十字架时，双击即可。4．利用if函数，根据销售额计算业绩表现。将光标定位到L3单元格→在编辑栏输入“=IF(J3>50,"业绩优秀",IF(J3>30,"业绩优良",IF(J3>20,"业绩合格","业绩差")))”→回车→将光标移动到L3单元格右下角，待光标变为黑色十字架时，双击即可。5．复制“商品销售情况表”（移至最后）4次，依次命名为“销售数据排序”、“分类汇总数据”、“高级筛选”、“数据透视表”。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】为什么在RANK函数参数J3和J34单元格的列标和行号前都加上一个“$”符号呢？【教师提示】这是Excel中的绝对引用，其形式为在行号和列标的前面加上“$”。可以通过选中单元格地址后按【F4】键在相对地址、绝对地址和混合地址之间自动转换。单元格的绝对引用不会随着公式的变化而变化。这里是为了在利用填充柄进行公式的填充时使J3:J34单元格的位置保持不变。若此时使用相对引用（J3:J34），在利用填充柄进行公式的填充时将会得到错误的计算结果，或是错误提示。任务二排序数据【任务分析】在“销售数据排序”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序。本任务是对商品销售数据进行排序。【任务讲演】1．在“销售数据排序”工作表中，选中A2:L34或单击选中其中任意一个单元格，选择“数据”→“排序和筛选”→“排序”命令，在弹出的“排序”对话框中设置“主要关键字”为“销售部门”→“排序依据”默认数值→“次序”→“自定义序列”→弹出“自定义序列”对话框，输入“销售一部”→回车，输入“销售二部”→回车，输入“销售三部”→单击“添加(A)”按钮→单击“确定”按钮→返回到“排序”对话框。2．在上面的“排序”对话框中，单击“添加条件(A)”按钮→“次要关键字”右侧的下拉小三角按钮→选择“类别”→“排序依据”默认数值→“次序”→“降序”→单击“确定”按钮。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。任务三分类汇总【任务分析】在对数据进行分析时，可以用Excel提供的“分类汇总”功能，按要求进行汇总。在“分类汇总数据”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序，目的是完成数据分类，然后按照要求进行数据分类汇总。本任务是对“分类汇总数据”工作表中的数据按“销售部门”对“销售额（万元）”进行“求和”汇总，并对汇总数据进行数据图表分析。【任务讲演】1．按照任务2在“分类汇总数据”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序，完成数据分类。2．将光标置于数据区域内，选择“数据”→“分级显示”→“分类汇总”命令，弹出“分类汇总”对话框。3．在“分类字段”下拉列表中选择“销售部门”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”下拉列表中勾选“销售额(万元)”复选框，单击“确定”按钮，完成分类汇总。4．单击工作表的左上方符号“2”，则可以显示各个“销售部门”的“销售额（万元）”汇总，而将其他的数据隐藏；单击工作表的左上方符号“1”，则只显示“销售部门”的总计，“销售额（万元）”汇总，而将其他的数据隐藏。5．若要去掉分类汇总的显示信息，再次选择“数据”→“分级显示”→“分类汇总”命令，在弹出的“分类汇总”对话框中单击“全部删除”按钮。6.插入图表分析数据。单击工作表的左上方符号“2”，按住【Ctrl】键的同时选中“销售部门”数据和“销售额（万元）”的汇总结果，按【Alt】+【；】组合键。7.选择“插入”→“图表”→“柱形图”→“簇状柱形图”命令，即可生成图表8.修改图表标题。单击图表标题区，将“销售额（万元）”更改为“各销售部门销售额对比”。9.添加数据标签。“图表工具/设计”选项卡→“图表布局”组→点击“添加图表元素”→点击”数据标签”→选择”数据标签外”，在数据点结尾之外添加数据标签。10.添加主要纵坐标标题“单位（万元）”，【图表工具/设计】选项卡【图表布局】组→点击【添加图表元素】→点击“轴标题(A)”→选择“主要纵坐标(V)”,修改“轴坐标标题”为“单位（万元）”。11.选中图表，选择“图表工具-设计”→“数据”→“选择数据”命令，在弹出的“选择数据源”对话框中单击“水平（分类）轴标签”的“编辑”按钮。在弹出的“轴标签”对话框的“轴标签区域”文本框中输入“销售一部,销售二部,销售三部”，（注意：分隔符逗号必须是英文半角状态）。单击“确定”按钮，将水平（分类）轴标签修改为“销售一部”“销售二部”“销售三部”。12.设置绘图区填充效果为“花束”的纹理填充。单击“图表工具/格式”选项卡→“当前所选内容”组→点击“图表元素”下拉按钮→选择“绘图区”→点击“设置所选内容格式”→填充选项卡选中“图片或纹理填充”→点击“纹理”下拉按钮→选择“花束”→点击【关闭】。13.将图表插入到“分类汇总数据”工作表的“B40:F55”单元格区域内。调整图表大小并移动到B40:F55单元格区域内。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】分类汇总有哪些类型？【教师提示】分类汇总包括以下几种。1）简单分类汇总：对数据表中的某一列以一种汇总方式进行分类汇总。2）多重分类汇总：对工作表中的某列数据按照两种或两种以上的汇总方式或汇总项进行汇总。多重分类汇总每次的分类字段总是相同的，汇总方式或汇总项则不同。3）嵌套分类汇总：在一个已经建立了分类汇总的工作表中再进行另外一种分类汇总，两次分类汇总的分类字段是不同的。在建立嵌套分类汇总前同样要先对工作表中需要进行分类汇总的字段进行排序，排序的主要关键字应该是第1级汇总关键字，排序时的次要关键字应该是第2级汇总关键字，其他的依此类推。任务四设置高级筛选【任务分析】高级筛选无疑是针对自动筛选而言的，高级筛选具有效率高，方便，易操作等特点。本任务是在“高级筛选"工作表中，对工作表内数据清单的内容进行高级筛选(在数据清单前插入四行，条件区域设在A1∶L3单元格区域，请在对应字段列内输入条件)，条件是:销售部门为“销售一部”或“销售三部”且销售额排名在10（包含10）到20（包含20）之间，筛选结果复制到A42开始的单元格区域，工作表名不变，保存“销售数据表.xlsx工作薄。【任务讲演】1．切换到“高级筛选'工作表→选中A1:A4单元格区域，并单击鼠标右键→选择“插入…”→在插入对话框中→选择“整行”→单击确定按钮，在数据清单前插入四行。2．选中B6单元格→按"Ctrl+C"组合键复制→单击B1单元格→按”Ctrl+V"组合键进行粘贴→在B2单元格中输入“销售一部”，在B3单元格中输入“销售三部”。3．选中K6单元格→按”Ctrl+C"组合键复制→选中J1:K1单元格区域→按”Ctrl+V"组合键进行粘贴→在J2，J3单元格中分别输入“>=10”，在K2，K3单元格中分别输入“<=20”。4．在“数据”功能区→“排序和筛选”组中→单击“高级”按钮→弹出“高级筛选”对话框→设置“列表区域”→$A$6:$L$38→设置“条件区域”为“$B$1:$K$3”→→复制到（T）→高级筛选!“$A$42:$K$3”→单击【确定】按钮。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】什么情况下必需使用高级筛选而不能使用自动筛选？【教师提示】自动筛选和高级筛选都是用来筛选表格中的数据和文字,但是区别是自动筛选:只是表格中的数据在筛选出来后,还是在原来的表格上中。但是高级筛选是把表格中的数据筛选出来后,把数据复制到空白的单元格中。利用“自动筛选”查找合乎准则的记录既方便又快速，但该命令的查找条件不能太复杂，如果要执行比较复杂的查找，就必须使用高级筛选命令。【课后思考】怎样准确设置高级筛选的条件区域？任务五创建数据透视表【任务分析】数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。在EXCEL中可以动态地改变它们的版面布置，以便按照不同方式分析数据，如果原始数据发生更改，则可以更新数据透视表。选择“数据透视表”工作表，对工作表内数据清单的内容建立数据透视表，筛选器为“销售部门”，按行为“类别”，数据为“销售额(万元)”求和布局，利用“数据透视表样式浅色9"修饰图表，添加“镶边行”和“镶边列”将数据透视表置于现有工作表的N2单元格。【任务讲演】1．选择“数据透视表”工作表，将光标置于数据区域中，选择“插入”→“表格”→“数据透视表”→“数据透视表”命令，弹出“创建数据透视表”对话框，“选择放置数据透视表的位置”，点击“现有工作表”，位置里单击选择“N2”单元格，单击“确定”按钮。2．拖动“数据透视表字段列表”中的“销售部门”字段拖到下方“筛选器”中。按照同样的方法，分别将“类别”字段拖到“行标签”区域，将“销售额”拖动2次到“数值”区域。3.修改数据透视表标题行名称。双击“行标签”，修改为“类别”。单击“数据透视表字段列表”中的第一个值“求和项：销售额（万元）”右侧下拉小三角，选择“值字段设置”，打开“值字段设置”，修改值字段名称为：“商品销售额（万元）”；修改第二个值字段名称为“商品销售额占比”，“值显示显示方式”为“列汇总的百分比”。4.选中N3:P7，单击选择“数据透视表工具”→“设计”选项卡→“数据透视表样式”组→选择“数据透视表样式浅色9"修饰图表→并在“数据透视表样式选项”组中，并勾选“镶边行”和“镶边列"。5.选中N3:P6单元格区域，单击选择“数据透视表工具”→“分析”选项卡→“工具”组→“数据透视图”命令→选择“图表类型”→“簇状柱形图”→单击“确定”按钮。6.选中“图表”，单击选择“数据透视图工具”→“设计”选项卡→“类型”组→“修改图表类型”命令。7.打开“更改图表类型”对话框→选择“组合”图表类型→单击“商品销售占比”右侧的下拉小三角→选择图表类型为“带数据标记的折线图”→单击选中“次坐标”前的复选框。8.将“数据透视图”插入到“数据透视表”工作表的“N9:R24”单元格区域内。调整图表大小并移动到N9:R24单元格区域内。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】确保数据可用于数据透视表？【教师提示】1）在源表中删除所有空行或空列。2）在源表中删除所有自动小计。3）确保第1行只包含各列的描述性标题（列标题）。4）确保各列只包含一种类