实验4.7 数据透视表-大学计算机应用基础实验指导教程

櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐櫐 櫐 櫐 櫐 殾 殾殾 殾 　 　 ②在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，则弹出“分类汇总” 对话框； ③在对话框中单击“全部删除”按钮，即可完成操作。 实验４ ７　 数据透视表 １．实验目的 （１）掌握格式化工作表的方法。 （２）掌握ＶＬＯＯＫＵＰ函数的使用。 （３）掌握数据透视表的创建。 （４）掌握图表的创建。 ２．实验内容 格式化工作表，利用ＶＬＯＯＫＵＰ函数计算商品销售额，为销售数据创建一个数据透视 表，要求针对各类商品比较各个门店每个季度的销售额，并为数据透视表创建一个簇状柱 形图。 ３．实验要求 （１）将Ｓｈｅｅｔ１工作表命名为“销售情况”，将Ｓｈｅｅｔ２工作表命名为“平均单价”。 （２）格式化工作表。 ①合并单元格Ａ１：Ｅ１。 ②调整工作表格式字体为楷体、字号为１２。 ③适当调整工作表的列宽，调整为１２。 ④将对齐方式设置为居中对齐。 ⑤将销售额数据列设置为“数值”格式并保留２位小数。 （３）将工作表“平均单价”中的区域Ｂ３：Ｃ５定义名称为“商品均价”。 （４）运用公司计算工作表“销售情况”中Ｆ列的销售额，要求使用ＶＬＯＯＫＵＰ函数 自动在“平均单价”工作表中查找相关商品的单价，并在公式中引用所定义的名称“商 品均价”。 （５）为工作表“销售情况”中的销售数据创建一个数据透视表，并将数据透视表放置在 ４７ ◆大学计算机应用基础实验指导教程◆ 新的工作表中，其中商品名称为报表筛选字段，店铺为行标签，季度为列标签，并对销售额 求和。 （６）对数据透视表进行格式设置，使其更加美观。 （７）根据数据透视表创建一个簇状柱形图，图表中仅对各个门店４个季度电视的销售 情况进行比较。 （８）保存。 ４．实验步骤 （１）启动Ｅｘｃｅｌ ２０１０，按“Ｃｔｒｌ＋Ｏ”键，打开“素材”文件夹中的“甲公司产品全年销售情 况． ｘｌｓｘ”，如图４５４所示。 + 图４５４　 甲公司产品全年销售情况 　 　 图４５５　 设置工作表名 （２）将光标移向Ｓｈｅｅｔ１工作表标签上并双击，将Ｓｈｅｅｔ１工作表命名为“销售情况”；将 光标移向Ｓｈｅｅｔ２工作表标签上并双击，将Ｓｈｅｅｔ ２工作表命名为“平均单价”，如图４５５ 所示。 （３）切换到“销售情况”工作表中，同时选择Ａ１：Ｅ１单元格，在“开始”选项卡的“对齐 方式”组中单击“合并后居中”按钮，将其合并为一个单元格，并使内容居中。 （４）选中“销售情况”工作表的Ａ３：Ｅ３５单元格，在“开始”选项卡中将字体设置为“楷 体”，字号设置为１２。 （５）选中“销售情况”工作表ＡＥ列，单击右键在弹出的快捷菜单中选择“列宽”，将列 宽调整为１２。 ５７ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 （６）选中“销售情况”工作表的Ａ３：Ｅ３５单元格，在“开始”选项卡的“段落”组中将对齐 方式设置为居中对齐。 （７）选中“销售情况”工作表的“销售额”数据列，单击右键，在弹出的快捷菜单中选择 “设置单元格格式”命令，在“设置单元格格式”对话框中将销售额数据列设置为“数值”格 式并保留２位小数，如图４５６所示。 图４５６　 “设置单元格格式”对话框 （８）选中“平均单价”工作表的Ｂ３：Ｃ６区域，单击鼠标右键，在弹出的快捷菜单中选择 “定义名称”命令，将该区域的名称定义为“商品均价”，如图４５７所示。 图４５７　 定义名称 （９）选中“销售情况”工作表的Ｅ４单元格，使用ＶＬＯＯＫＵＰ函数计算销售额（销售额＝ 单价销售量）。 ①选中“销售情况”工作表的Ｅ４单元格，单击编辑栏中的“插入函数”按钮，在弹出的 ６７ ◆大学计算机应用基础实验指导教程◆ “插入函数”对话框中找到ＶＬＯＯＫＵＰ函数并设置各个参数，如图４５８所示。 图４５８　 设置ＶＬＯＯＫＵＰ函数参数 Ｌｏｏｋｕｐ＿ｖａｌｕｅ：该参数用于设置要在表区域中搜索的值，此处为工作表中Ｃ３单元格 对应的商品名称，即“电视”。 Ｔａｂｌｅ＿ａｒｒａｙ：该参数用于设置要查找数据所在的单元格区域，该区域为“平均单价” 的Ｂ３：Ｃ６区域，由于在上一步中将Ｂ３：Ｃ５区域的名称定义为“商品均价”，因此在Ｔａｂｌｅ＿ ａｒｒａｙ参数中其值同样为“商品均价”。 Ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ：该参数用于设置最终返回数据所在的列号，即Ｂ３：Ｃ５区域中的第２ 列（平均单价）。 Ｒａｎｇｅ＿ｌｏｏｋｕｐ：该参数用于指定在查找时是要求精确比配还是大致匹配。 ②利用ＶＬＯＯＫＵＰ函数得到商品的平均单价后，单击编辑栏，使用销售额＝单价销售 量公式计算出各个商品的销售额，如图４５９所示。  图４５９　 计算销售额公式 （１０）创建数据透视表。 ①在“插入”选项卡的“表格”组中单击“数据透视表”，在展开的菜单中选择“数据透视 表”，如图４６０所示。在弹出的“创建数据透视表”对话框中设置要分析的数据区域和放置 数据透视表的位置，如图４６１所示。  图４６０　 数据透视表 　 　 　 　 图４６１　 “创建数据透视表”对话框 ７７ 项目四　 电子表格软件Ｅｘｃｅｌ ２０１０的应用 ②在数据透视表的“数据透视表字段列表”中，选择“商品名称”，按住鼠标左键将其拖 到下方的“报表筛选”，再依次将“店铺”拖到下方的“行标签”，“季度”拖到下方的“列标 签”，“销售额”拖到下方的“数值”，如图４６２所示。 J M 图４６２　 设置数据透视表字段 （１１）对数据透视表进行格式设置，使其更加美观。 （１２）根据数据透视表创建一个簇状柱形图，图表中仅对各个门店４个季度电视的销售 情况进行比较。 ①将数据透视表第一行的商品名称设置为“电视”。 ②鼠标选中Ａ４：Ｅ６单元格，在“插入”选项卡的“图表”中单击“柱形图”，在展开的下 一级子菜单中选择“簇状柱形图”，生成的图表如图４６３所示。 图４６３　 柱形图 （１３）保存文档。 ８７ ◆大学计算机应用基础实验指导教程◆