实验4.6 数据分类汇总-大学计算机应用基础实验指导教程

中的第２列（图书名称）。 图４３６　 设置ＶＬＯＯＫＵＰ函数的参数（１） ②利用ＶＬＯＯＫＵＰ函数自动填充“单价”。选中Ｄ３单元格，单击编辑栏中的“插入函 数”按钮，在弹出的“插入函数”对话框中找到ＶＬＯＯＫＵＰ函数并设置各个参数，如图４３７ 所示。 Ｌｏｏｋｕｐ＿ｖａｌｕｅ：该参数用于设置要在表区域中搜索的值，此处为工作表中Ｃ３单元格 对应的图书编号，即“ＢＫ６５０２１”。 Ｔａｂｌｅ＿ａｒｒａｙ：该参数用于设置要查找数据所在的单元格区域，该区域为“图书单 价”工作表的Ａ２：Ｂ５区域，由于在公式复制过程中查找数据所在的单元格区域不会发生 变化，因此在设置此参数时需在数据区域的行号和列号前添加绝对应用符号“ ! ”，即 ! Ａ ! ２： ! Ｂ ! ５。 Ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ：该参数用于设置最终返回数据所在的列号，即!Ａ!２：!Ｂ!５区域中 的第２列（单价）。 图４３７　 设置ＶＬＯＯＫＵＰ函数的参数（２） （６）利用公式计算“小计”列，选中Ｆ３单元格，在编辑栏中输入公式“＝［＠单价］［＠ 销量］”。 实验４ ６　 数据分类汇总 １．实验目的 （１）掌握数据表的排序操作。 （２）学会数据表的分类汇总方法。 （３）掌握图表的创建与编辑方法。 ７６ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 ２．实验内容 （１）按要求对销售数据进行分类汇总，结果如图４３８所示。 图４３８　 分类汇总效果 （２）根据分类汇总后的数据创建图表。 ３．实验要求 （１）打开“销售数据． ｘｌｓｘ”文件，将Ｓｈｅｅｔ１工作表中的内容复制到Ｓｈｅｅｔ２工作表中备用。 （２）以“部门”为分类字段，对“订单金额”进行求和汇总。 （３）根据分类汇总后的数据创建饼形图表并进行编辑，效果如图４３９所示。 图４３９　 饼形图表效果 ８６ ◆大学计算机应用基础实验指导教程◆ （４）以“销售人员”为分类字段，对“订单金额”进行求和汇总。 （５）根据分类汇总后的数据创建条形图表并进行编辑，效果如图４４０所示。 图４４０　 条形图表效果 ４．实验步骤 （１）启动Ｅｘｃｅｌ ２０１０，按“Ｃｔｒｌ＋Ｏ”键，打开“素材”文件夹中的“销售数据． ｘｌｓｘ”，如图 ４４１所示。 图４４１　 打开的销售统计表 　 　 图４４２　 排序结果 ９６ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 　 　 （２）按“Ｃｔｒｌ＋Ａ”键，全选Ｓｈｅｅｔ１中的数据，再按“Ｃｔｒｌ＋Ｃ”键复制数据；切换到Ｓｈｅｅｔ２工 作表中，按“Ｃｔｒｌ＋Ｖ”键粘贴复制的数据。 （３）切换到Ｓｈｅｅｔ １中，在“部门”一列中单击鼠标，在“数据”选项卡的“排序和筛选”组 中单击按钮，对“部门”一列按升序排序，如图４４２所示。 （４）在“数据”选项卡的“分级显示”组中，单击“分类汇总”按钮，如图４４３所示，打开 “分类汇总”对话框，在“分类字段”中选择“部门”，在“汇总方式”中选择“求和”，在“选定 汇总项”中选择“订单金额”，如图４４４所示。   图４４３　 分类汇总按钮的位置 图４４４　 “分类汇总”对话框 （５）单击“确定”按钮，则以“部门”为分类字段，对“订单金额”进行求和汇总，单击 按钮，隐藏细节数据，只显示汇总结果，如图４４５所示。 图４４５　 汇总结果 ０７ ◆大学计算机应用基础实验指导教程◆ （６）同时选择两个销售部门的订单金额汇总数据（Ｂ１３和Ｂ２４单元格），在“插入”选项 卡的“图表”组中单击“饼图”按钮，在下拉列表中选择“分离型三维饼图”，则生成了一个 图表。 （７）在“设计”选项卡的“图表布局”组中单击右下角的 按钮，在打开的下拉列表中 选择“布局６”，图表效果如图４４６所示。 图４４６　 图表效果 （８）在图表上单击鼠标右键，在弹出的快捷菜单中选择“选择数据”命令，弹出“选择数 据源”对话框，如图４４７所示。 图４４７　 “选择数据源”对话框 （９）单击“图例项（系列）”列表区中的“编辑”按钮，弹出“编辑数据系列”对话框，选择 汇总表中的Ａ１单元格；然后单击“确定”按钮，返回“选择数据源”对话框，则完成了图表标 题的编辑。 （１０）在“水平（分类）轴标签”列表区中单击“编辑”按钮，参照上一步的操作步骤，选 择汇总表中的Ｄ１３和Ｄ２４单元格，完成图表图例的设置。 （１１）单击“确定”按钮，图表效果如图４４８所示。 １７ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 图４４８　 图表效果 　 　 （１２）在饼形图上单击鼠标右键，在弹出的快捷菜单中选择“设置数据系列格式”命令， 在弹出的“设置数据系列格式”对话框中设置“饼图分离程度”为６％，如图４４９所示，单击 “确定”按钮，即可降低饼图分离程度。 图４４９　 “设置数据系列格式”对话框 （１３）切换到Ｓｈｅｅｔ２中，参照前面的操作方法，对“销售人员”一列进行升序排序，并对“订 单金额”进行求和汇总，如图４５０所示；然后隐藏细节数据，只显示汇总结果，如图４５１所示。 图４５０　 “分类汇总”对话框 图４５１　 汇总结果 ２７ ◆大学计算机应用基础实验指导教程◆ （１４）同时选择４位销售人员的订单金额汇总数据（Ｂ８、Ｂ１４、Ｂ２０、Ｂ２６单元格），在 “插入”选项卡的“图表”组中单击“条形图”按钮，在下拉列表中选择第１个二维条形图， 生成一个图表。 （１５）在“设计”选项卡的“图表布局”组中单击右下角的按钮，在打开的下拉列表中 选择“布局５”，图表效果如图４５２所示。 图４５２　 图表效果 （１６）在图表上单击鼠标右键，在弹出的快捷菜单中选择“选择数据”命令，弹出“选择 数据源”对话框，参照上一步的步骤，选择汇总表中的Ａ１单元格作为图表标题，选择４位销 售人员名字作为水平（分类）轴标签，如图４５３所示。 图４５３　 “选择数据源”对话框 （１７）单击“确定”按钮，则完成图表的编辑。 （１８）将工作簿另存为“实验四：学生成绩表的排序和筛选． ｘｌｓｘ”。 櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 殾 殾殾 殾知识拓展 如何清除分类汇总？ ①单击分类汇总数据中的任意单元格； ３７ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 殾 殾殾 殾 　 　 ②在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，则弹出“分类汇总” 对话框； ③在对话框中单击“全部删除”按钮，即可完成操作。 实验４ ７　 数据透视表 １．实验目的 （１）掌握格式化工作表的方法。 （２）掌握ＶＬＯＯＫＵＰ函数的使用。 （３）掌握数据透视表的创建。 （４）掌握图表的创建。 ２．实验内容 格式化工作表，利用ＶＬＯＯＫＵＰ函数计算商品销售额，为销售数据创建一个数据透视 表，要求针对各类商品比较各个门店每个季度的销售额，并为数据透视表创建一个簇状柱 形图。 ３．实验要求 （１）将Ｓｈｅｅｔ１工作表命名为“销售情况”，将Ｓｈｅｅｔ２工作表命名为“平均单价”。 （２）格式化工作表。 ①合并单元格Ａ１：Ｅ１。 ②调整工作表格式字体为楷体、字号为１２。 ③适当调整工作表的列宽，调整为１２。 ④将对齐方式设置为居中对齐。 ⑤将销售额数据列设置为“数值”格式并保留２位小数。 （３）将工作表“平均单价”中的区域Ｂ３：Ｃ５定义名称为“商品均价”。 （４）运用公司计算工作表“销售情况”中Ｆ列的销售额，要求使用ＶＬＯＯＫＵＰ函数 自动在“平均单价”工作表中查找相关商品的单价，并在公式中引用所定义的名称“商 品均价”。 （５）为工作表“销售情况”中的销售数据创建一个数据透视表，并将数据透视表放置在 ４７ ◆大学计算机应用基础实验指导教程◆