4.5制作商品采购表（课件）-《计算机应用基础》同步教学（人民邮电版）

第4章 电子表格处理软件Excel 2016 Excel 2016 是 Office 2016 套装软件中的电子表格处理软件。它的核心功能是表格处理，除了可以实现保存信息、数据计算处理、图表处理等基本功能外，它还可以为用户提供强大的数据分析和统计、预测以及 Web 和病毒检查功能等。Excel 由于具有十分友好的人机交互界面和强大的计算功能，因此深受广大办公、财务和统计人员的青睐。其默认的文件类型（扩展名）为“xlsx”。 Guidance to Chapter Five 本章导学 Learning Objectives 学习目标 掌握工作簿、工作表的基本操作。 掌握 Excel 工作表的格式化设置方法。 掌握单元格中公式与函数的应用。 掌握 Excel 表格中数据的管理方法。 掌握图表和数据透视表（图）的创建方法。 学习目标 Mind Map in This Chapter 思维导图 （1）使用函数填充采购表中的“单价”列。 （2）根据折扣表中的商品折扣率，使用函数计算采购表中的“折扣”列结果。 （3）对采购表的“合计”列进行计算。 （4）分别使用分类汇总功能和函数计算各类商品的采购总量和采购总金额。 （5）筛选出“采购数量”>350 或“折扣”>8%的产品。 （6）根据统计表创建一个各类商品采购总量的柱形图。 （7）根据“采购表”新建一个数据透视图 Chart1。 任务要求： 任务五 制作商品采购表 在 Excel 2016 中，数据清单实际上就是工作表中的一个区域，一列单元格就是一个字段，每行中的字段内容就是一条记录，用户可以通过在 Excel 2016 中创建数据清单来对数据进行管理。 在 Excel 2016 中，用户可以根据一列或多列的内容按升序或降序对数据清单进行排序。 下面对图 所示的“学生成绩表”按照“总分”值进行高低排名，并且“总分”如有相同，则按照“数学”值进行高低排名。 任务五 制作商品采购表 一、数据的排序 操作步骤如下。 （1）选定需要排序的数据清单。 （2）选择“数据”选项卡，然后单击“排序和筛选”选项组中的“排序”按钮，弹出“排序”对话框，参数设置如图1所示。 （3）将“主要关键字”设为“总分”，将“次要关键字”设为“数学”，均设置为“降序”次序。 （4）单击“确定”按钮，完成操作，结果如图2所示。 注意：在选定数据清单时，应勾选“数据包含标题”复选框，否则关键字会用列号代表；关键字的数据类型为字符型时，按字符的内码值进行大小比较。 任务五 制作商品采购表 一、数据的排序 分类汇总是对数据表中的某个字段进行分类，在分类过程中实现分类计算，在当前工作表中插入分类汇总行和总计行，并将计算结果分级显示出来。在分类汇总之前，必须对分类字段进行排序。下面对“学习成绩表”按性别进行分类汇总，操作步骤如下。 （1）对分类的字段“性别”进行排序，如图1所示。 （2）选定数据清单。 （3）选择“数据”选项卡，单击“分级显示”选项组中的“分类汇总”按钮，弹出“分类汇总”对话框，如图 2所示。 任务五 制作商品采购表 二、数据的分类汇总 1.基础知识 （4）在对话框中依次进行以下操作。 ①在“分类字段”下拉列表中选择“性别”作为分类排序字段。 ②在“汇总方式”下拉列表中选择“平均值”作为汇总计算方式。 ③在“选定汇总项”列表中选择需要的汇总项，如“英语”“数学”“语文”。 （5）单击“确定”按钮，完成操作，结果如图 所示。 注意：在分类汇总结果工作表中，单击屏幕左边的“-”按钮，可以仅显示小计、总计数，而隐藏原始数据，这时屏幕左边显示“+”按钮，如果再次单击“+”按钮，将恢复显示隐藏的数据。在分类汇总结果工作表的左上方有 3 个按钮，分别单击可以显示相应级别的汇总结果。若要删除分类汇总结果，在“分类汇总”对话框中单击“全部删除”按钮即可。 任务五 制作商品采购表 二、数据的分类汇总 1.基础知识 打开“商品采购表.xlsx”，按照要求完成操作后保存文件。 步骤 1 使用函数填充采购表中的“单价”列。 分析：VLOOKUP()的功能是在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。 （1）选择 Sheet1 工作表，将光标定位到 D11 单元格中。 （2）选择“公式”→“插入函数”命令，弹出“插入函数”对话框，找到“VLOOKUP”函数，单击“确定”按钮。 （3）弹出“函数参数”对话框，在“Lookup_value”文本框中输入“A11”，在“Table_array”文本框中输入“$F$3:$G$5”（此处为绝对引用），在“Col_index_num”文本框中输入“2”，在“Range_lookup”文本框中输入“0”，参数设置如图 所示。 （4）将鼠标移至D11单元格区域右下角出现填充柄后，左键按住“填充柄”向下拖动即可。 任务五 制作商品采购表 二、数据的分类汇总 2.操作步骤 步骤 2 根据折扣表中的商品折扣率，使用函数计算采购表中的“折扣”列结果。 分析：根据折扣表中的“说明”列，可以判断出此处需要使用 IF 嵌套函数；先根据采购数量判断是否满足小于 100 件（即 0～99 件），如果满足则给予 0%的折扣率；不满足则再次判断采购数量是否小于 200 件（即 100～199 件），满足则给予 6%的折扣率；不满足则再次判断采购数量是否小于 300 件（即 200～299 件），满足则给予 8%的折扣率，否则给予 10%的折扣率。 （1）将光标定位到 E11 单元格中。 （2）在编辑栏中输入函数“=IF(B11<100,0%,IF(B11<200,6%,IF(B11<300,8%,10%)))”，按“Enter”键。 （3）将鼠标指针移至 E11 单元格右下角，出现填充柄后，按住鼠标左键向下拖动填充柄即可。 任务五 制作商品采购表 二、数据的分类汇总 2.操作步骤 步骤 3 对采购表的“合计”列进行计算。 分析：根据“采购数量”“单价”“折扣”，可以计算出采购的合计金额，计算公式为单价*采购数量*（1-折扣率）。 （1）将光标定位到 F11 单元格中。 （2）在编辑栏中输入公式“=B11*D11*(1-E11)”，按“Enter”键。 （3）将鼠标指针移至 F11 单元格区域右下角，出现填充柄后，按住鼠标左键向下拖动填充柄即可。 步骤 4 使用分类汇总功能计算各类商品的采购总量和采购总金额。 （1）选中 Sheet1 工作表进行复制，得到 Sheet2 工作表。 （2）在 Sheet2 工作表中进行分类汇总计算，计算各种商品的采购总量和采购总金额。选中 A10:F43 单元格区域。 任务五 制作商品采购表 二、数据的分类汇总 2.操作步骤 （3）选择“数据”→“排序”命令，设置排序的主要关键字为“项目”，升序排序，单击“确定”按钮。 （4）选择“数据”→“分类汇总”命令，设置分类字段为“项目”，汇总方式为“求和”，汇总项包括“采购数量”和“合计金额”。 （5）单击“确定”按钮后，各类商品的采购总量和采购总金额在“汇总”项中显示，如图 所示。 步骤 5 使用函数计算各类商品的采购总量和采购总金额。 （1）在 Sheet1 工作表中使用函数计算各种商品的采购总量和采购总金额。选中 Sheet1 工作表中的 J12 单元格。 （2）选择“公式”→“插入函数”命令，弹出“插入函数”对话框，找到“SUMIF”函数，单击“确定”按钮。 任务五 制作商品采购表 二、数据的分类汇总 2.操作步骤 （3）在弹出的“函数参数”对话框中，Range 为用于条件判断的单元格区域；Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；Sum_range 是需要求和的实际单元格。 （4）在“Range”文本框中输入“$A$11:$A$43”，在“Criteria”文本框中输入“I12”，在“Sum_ range”文本框中输入“$B$11:$B$43”，如图1所示，单击“确定”按钮。 （5）将鼠标指针移至 J12 单元格右下角，出现填充柄后，按住鼠标左键向下拖动填充柄即可。 （6）用同样的方法在 K12 单元格中插入函数“=SUMIF($A$11:$A$43,I12,$F$11:$F$43)”，按“Enter”键。再使用填充方法填充其他数据，结果如图 2所示。 任务五 制作商品采购表 二、数据的分类汇总 2.操作步骤 （1）自动筛选 使用自动筛选功能可以创建按列表值、按格式和按条件 3 种筛选类型。对于每个单元格区域或列而言，这 3 种筛选类型都是互斥的。具体操作步骤如下。 ①在进行筛选的数据清单中选择任意单元格，然后选择“数据”选项卡，单击“排序和筛选”选项组中的“筛选”按钮，这时在数据标题行字段的右侧出现了下拉按钮，如图所示。 任务五 制作商品采购表 三、数据的筛选 1.基础知识 ②单击标题行字段右侧的下拉按钮，会弹出相应的下拉列表，在其中可对数据进行各种方式的自动筛选，例如，单击“英语”字段右侧的下拉按钮，然后选择“数字筛选”→“高于平均值”选项，如图 1 所示。 ③ 将自动筛选出高于平均分的项目，如图 2所示。 任务五 制作商品采购表 三、数据的筛选 1.基础知识 （2）使用自定义自动筛选 使用自动筛选方法筛选记录时，不能筛选出满足“或”“与”条件的数据，如想筛选出“总分”成绩为 240～250 分的学生信息，可以使用自定义自动筛选。 操作步骤如下。 ①选中“总分”列。 ②选择“数据”选项卡，单击“排序和筛选”选项组中的“筛选”按钮，此时“总分”字段右侧显示出了下拉按钮，即为筛选器箭头，如图 1所示。 ③单击“总分”字段右侧的下拉按钮，然后选择“数字筛选”→“自定义筛选”选项，则显示出图 2所示的对话框。 任务五 制作商品采购表 三、数据的筛选 1.基础知识 ④在“自定义自动筛选方式”对话框中，依次输入筛选的条件（筛选出“总分”成绩为240～250 分的学生信息）。 ⑤单击“确定”按钮，使用“自定义自动筛选”功能筛选出来的数据如图 所示。 如果不需要自动筛选的结果，而需要恢复原来的数据清单，可以选择“数据”选项卡，单击“排序和筛选”选项组中的“清除”按钮。如果要撤销对数据清单的筛选，再次选择“数据”选项卡，单击“排序和筛选”选项组中的“筛选”按钮，即可取消筛选操作。 注意：自动筛选可以对若干字段进行多次使用，以达到多级筛选的效果，即在前一字段的筛选基础上再对另一字段的筛选，如“语文”和“英语”都大于80分的筛选，也就是说实现字段之间“与”关系的筛选，而不能实现字段之间“或”关系的筛选。 任务五 制作商品采购表 三、数据的筛选 1.基础知识 （3）使用高级筛选 高级筛选与先前的两种筛选方法相比更加灵活和实用，能实现复杂条件的筛选。高级筛选的条件由用户自己设置，这样提高了筛选的灵活度。例如，使用高级筛选筛选出成绩表中英语≥80 分或数学≥80 分的所有学生信息。 操作步骤如下。 ①在工作表的空白处输入筛选条件（条件区），如英语≥80 分或数学≥80 分，如图1所示（倘若条件处在同一行，则两者是一种“与”的关系）。 ②单击 A1:G13 单元格区域中的任一单元格，选择“数据”选项卡，单击“排序和筛选”选项组中的“高级”按钮，弹出图2所示的“高级筛选”对话框，进行相关设置。 任务五 制作商品采购表 三、数据的筛选 1.基础知识 ③对筛选“方式”的确定，对话框中有两种选择，两者之间的区别在于是否将筛选结果显示在原有区域。这里选择“将筛选结果复制到其他位置”。选择数据区域为“$A$1:$G$13”，条件区域为“$A$15:$B$17”，将结果复制到“$A$19”单元格中，单击“确定”按钮，显示出图 所示的结果。 注意：高级筛选关键是条件的设置。第一行写条件的关键字，以下各行写条件值，同行中的条件为“与”关系，不同行中的条件为“或”关系。下面举例加以说明： 数学＞80.and.英语＞70.or.英语<85 数学＞80.or.（英语＞70.and.英语<85） 任务五 制作商品采购表 三、数据的筛选 1.基础知识 筛选出“采购数量”>350 或“折扣”>8%的商品 （1）选择 Sheet1 工作表，将条件区域建立在 H24:I26 单元格区域中，如图 1所示。 （2）选择 Sheet1 工作表中的 A10:F43 单元格区域。 （3）选择“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框。 （4）设置筛选参数如图 2所示。 （5）单击“确定”按钮，生成图 3所示的筛选结果 任务五 制作商品采购表 三、数据的筛选 2.操作步骤 （1）创建图表 ①使用“F11”键创建图表 例如，将表格中的数据直接转换成图表，用户不必分析“学生成绩表”图1中的多个数据列，直接查看学生成绩图表2即可知道学生各门成绩的高低，能直观地看到每个学生 3 科成绩的情况，能很方便地进行比较。 选中数据所在的单元格区域（B1,D1:F13），按“F11”键即可生成图表。 任务五 制作商品采购表 四、图表 1.基础知识 Excel支持广泛的图表类型选择。把数据绘制到哪类图表中由数据和想要表述的目的决定。在这里向大家介绍几类图表类型供大家参考： 柱形图：用于显示一段时间内的数据变化或说明项目之间的比较结果。通过水平组织分类、垂直组织值可以强调说明一段时间内的变化情况。 条形图：显示了各个项目之间的比较情况。纵轴表示分类，横轴表示值，它主要强调各个值之间的比较而并不太关心时间。 折线图：显示了相同间隔内数据的预测趋势。 面积图：强调了随时间的变化幅度。由于也显示了绘制值的总和，因此面积图也可显示部分相对于整体的关系。 饼形图：显示了构成数据系列的项目相对于项目总和的比例大小。饼形图总是只显示一个数据序列；当用户希望强调某个重要元素时，饼形图就很有用。 XY 散点图XY散点图中的点一般不连,每一点代表了两个变量的数值, 用来分析两个变量之间是否相关。 任务五 制作商品采购表 四、图表 1.基础知识 ② 用图表工具创建图表 选择数据单元格区域，或者选择其中一个单元格。然后选择“插入”选项卡，在“图表”选项组中单击相应的图表类型下拉按钮，在弹出的下拉列表中选择需要的图表样式即可，例如，选择“柱形图”中的“二维簇状柱形图”图表样式，如图1所示。创建的图表效果如图2所示。 任务五 制作商品采购表 四、图表 1.基础知识 ③ 使用“插入图表”对话框创建图表 选择数据单元格区域，或者选择其中的一个单元格。单击“图表”选项组右侧的“对话框启动器”按钮，打开“插入图表”对话框，如图1所示。在对话框中选择“所有图表”选项卡，选择要插入的图表样式，然后单击“确定”按钮即可，如图 2所示。 任务五 制作商品采购表 四、图表 1.基础知识 （2）编辑图表 在对图表加以修饰前，首先要选择图表。如果是嵌入式图表，则单击图表；如果是图表工作表（独立的图表），则单击此工作表的标签。 ①设置图表区格式 右击图表空白区域，在弹出的快捷菜单中选择“设置图表区域格式”命令，在右侧打开“设置图表区格式”窗格，如图 1所示。 例如，在“设置图表区格式”窗格中选择“填充”选项卡，在“填充”下拉列表中选择“渐变填充”单选按钮，然后在“类型”中选择“矩形”类型，如图 2所示，得到的图表效果如图 3所示。 任务五 制作商品采购表 四、图表 1.基础知识 ②设置绘图区格式 在右侧打开的“设置图表区格式”窗格中，单击“图表选项”右侧的下拉按钮，在弹出的下拉列表中选择“绘图区”选项，如图1所示。可以切换到“设置绘图区格式”窗格，如图 2所示。 任务五 制作商品采购表 四、图表 1.基础知识 ③设置图例格式 使用同上的操作方法，在弹出的下拉列表中选择“图例”选项，可以将右侧打开的窗格切换为“设置图例格式”，如图 1所示。在此窗格中，前两项的功能与前面介绍的相似，只是增加了一个“图例”选项，如图2所示。 任务五 制作商品采购表 四、图表 1.基础知识 ④设置坐标轴格式 使用同上的操作方法，在弹出的下拉列表中选择“垂直轴”或“水平轴”选项，将右侧打开的窗格切换为“设置坐标轴格式”。在此窗格中可设置坐标轴的位置、数字、填充、刻度等，如图1所示。 ⑤设置数据系列格式 使用同上的操作方法，在弹出的下拉列表中选择“系列”值，将右侧打开的窗格切换为“设置数据系列格式”。在此窗格中可设置数据系列的填充、效果、系列选项等，如图2所示。 任务五 制作商品采购表 四、图表 1.基础知识 根据统计表创建一个各类商品采购总量的柱形图。 （1）选择 Sheet1 工作表中的 I11:J14 单元格区域。 （2）选择“插入”→“图表”命令，打开“图表向导”对话框。选择图表类型为“簇状柱形图”，系列产生在“列”上。 （3）设置图表标题为“各类商品总采购量”，x 轴标题为“项目”，在柱形图上显示数据的“值”，取消图中的所有网格线，图表的背景色为浅绿色。 （4）调整好图表格式后，将图放在 H28:M40单元格区域中，如图所示。 任务五 制作商品采购表 四、图表 2.操作步骤 （1）创建数据透视表 根据图 1 所示的数据清单创建数据透视表。 要创建数据透视表，首先要选择相应的单元格区域，然后选择“插入”选项卡下的“表格”选项组，单击“数据透视表”按钮，在打开的“创建数据透视表”对话框中进行相应的设置即可，如图 2所示。 例如，在“创建数据透视表”对话框中选择“新工作表”单选按钮，即可将数据透视表创建到新的工作表中，然后在“数据透视表字段”窗格中选择要添加到报表的字段，如图 3所示。 任务五 制作商品采购表 五、 数据透视表和数据透视图 1.基础知识 （2）创建数据透视图 选择包含数据的单元格区域，然后选择“插入”选项卡，单击“图表”选项组中的“数据透视图”下拉按钮，在弹出的下拉列表中选择“数据透视图”选项，然后在打开的对话框中进行设置，如图 1所示。 例如，在“创建数据透视图”对话框中选择“新工作表”单选按钮，即可将数据透视图创建到新的工作表中，然后在“数据透视图字段”窗格中选择要添加到报表的字段，如图2所示。 任务五 制作商品采购表 五、 数据透视表和数据透视图 1.基础知识 根据“采购表”新建一个数据透视图 Chart1。 要求：该图形显示每个采购时间点所采购的所有项目数量汇总情况；将 X 坐标设置为“采购时间”；求和项设置为“采购数量”；将对应的数据透视表保存在 Sheet3 工作表中。 （1）选择 Sheet1 工作表中的 A10:F43 单元格区域。 （2）选择“插入”→“图表”→“数据透视表”命令，打开“数据透视表”对话框。在对话框中选择“新工作表”单选按钮，即可将数据透视图创建到新的工作表中。 （3）将“采购时间”字段拖至“行”上，“项目”字段拖至“列”上，“采购数量”字段拖至“数据”中进行求和，单击“确定”按钮，调整好数据透视图的位置，效果如图 所示。 任务五 制作商品采购表 五、 数据透视表和数据透视图 2.操作步骤 $$