excel建立数据透视表

87
实验八 数据透视表
一、实验目的
1. 学习数据库的数据透视表功能。

2．掌握建立数据透视表的方法。

二、实验内容
打开保存在D 盘EXCEL 文件夹中的ex3.xls 工作簿文件，在工作表Sheet3中完成下列操作后存盘。

（一）利用工作表Sheet3中的数据清单，建立一个按性别统计人数的两行的数据透视表，如图5-11所示。

其中第一行为性别，第二行为该性别对应的人数。

图 5-11 按性别统计人数
1．选定数据库清单中的任一单元格。

2．选择菜单[数据]→[数据透视表和数据透视图]项，弹出“数据透视表和数据透视图”向导对话框。

3．在步骤1中，数据源类型选择“Microsoft Excel 数据清单或数据库”，报表类型选择“数据透视表”，按[下一步]按钮。

4．在步骤2中，指定数据区域为A2:K14（光标自动选择此区域），按[下一步]按钮。

5．在步骤3中，选择显示位置为“现有工作表”的B18单元格，按[布局]按钮。

弹出如图5-12所示的“布局”对话框。

图 5-12 数据透视表的“布局”对话框
6．“布局”对话框中，将“性别”字段拖拽到“列(C)”标志区，“性别”字段拖拽到“数据(D)”标志区，按[确定]按钮。

最后按[完成]按钮，结束操作。

（二）利用数据清单，建立一个按职称求实发工资最大值的两列的数据透视表，如图5-13所示。

其中第一列为职称，第二列为相应职称的实发工资最大值。

图 5-13 按职称统计工资的最高值
1．前面的步骤相同于（一）中的1、2、3、4。

2．在步骤3中，选择显示位置为“现有工作表”的G18单元格，按[布局]按钮。

3．“布局”对话框中，将“职称”字段拖拽到“行(C)”标志区，“实发工资”字段拖拽到“数据(D)”标志区。

4．用鼠标双击“数据(D)”区中的[求和项:实发工资]按钮，在弹出的“数据透视表字段”对话框中，设置“汇总方式”为“平均值”。

按[确定]按钮，最后按[完成]按钮，结束操作。

（三）利用数据清单，建立一个按部门分别求出基本工资、补贴和扣款总和的数据透视表，如图5-13所示。

其中第一行为部门，下面三行为相应部门的求和数据。

图 5-14 按部门统计工资的总和
1．前面的步骤相同于（一）中的1、2、3、4。

2．在步骤3中，选择显示位置为“新建工作表”，按[布局]按钮。

3．“布局”对话框中，将“部门”字段拖拽到“列(C)”标志区，“基本工资”、“补贴”和“扣款”三个字段拖拽到“数据(D)”标志区。

4．按[确定]按钮，再按[完成]按钮。

则在一个新的工作表中建立上图所示的数据透视表。

（四）修改数据透视表的某个统计字段，例如：将图5-14中的“求和项:基本工资”改成求基本工资的最小值。

1．选中“求和项:基本工资”所在的单元格，单击右键，在快捷菜单中选[字段设置]项。

2．在弹出的对话框中，将“汇总方式”改为“平均值”,按[确定]按钮。

（五）保存工作簿文件ex3.xls到D盘EXCEL文件夹中，退出Excel应用程序。

三、思考题
88
1．与数据库统计函数相比较，使用数据透视表来统计数据的优点是什么？
2．在建立行方向或列方向的数据透视表时，重要的步骤是什么？
89
实验九创建图表
一、实验目的
1．学习图表向导的使用。

2．掌握各种类型图表的建立和编辑方法。

二、实验内容
图表是工作表数据的图形表示。

可以使用图表向导来创建图表或修改已有的图表，并根据不同的数据类型可以选择不同的图表类型，如柱形图、条形图、折线图、饼图等。

新建Excel工作簿，以文件名ex4.xls保存到D盘EXCEL文件夹中，并完成下面操作。

（一）图表数据源的输入
1．在工作表Sheet1中，输入如图5-15所示的数据表。

图 5-15 Sheet1表的数据源 5-16 Sheet2表的数据源2．在工作表Sheet2中，输入如图5-16所示的数据表。

（二）柱形图和条形图的绘制
柱形图和条形图适合于比较不同项目的数值或不同时间的数值。

使用图表向导，在工作表Sheet1中绘制下列图形。

1．根据工作表中所给的数据，建立一个如图5-17所示的按系别分类统计各类人员人数的柱形图。

（1）选定区域A2:D7作为图表的数据源。

（2）选择菜单[插入]→[图表]项，弹出“图表向导”对话框。

（3）在步骤之1 “图表类型”对话框中，选择图表类型为柱形图的第1个子类型“簇状柱形图”，按[下一步]按钮。

（4）在步骤之2“图表数据源”对话框中，选择“数据区域”标签，查看选定的数据区域是否正确；并选择“系列产生在”项中的“列”，按[下一步]按钮。

（5）在步骤之3“图表选项”对话框中，选择“标题”标签，输入三个标题；图表标题
90
“人员分布图表”、分类轴标题“系别”、数值标题“人数”，按[下一步]按钮。

（6）在步骤之4“图表位置”对话框中，指定“作为其中的对象插入”，按[完成]按钮。

图5-17 按系别的柱形图
2．根据工作表中的数据，绘制如图5-18所示的按“教师”和“其它”分类统计各系人员人数的条形图，
（1）选定区域A2:B7和D2:D7作为图表的数据源。

（2）单击工具栏中的[图表向导]按钮“”，弹出“图表向导”对话框。

（3）选择图表类型为“条形图”的第1个子类型“簇状条形图”。

（4）在图表数据源的“数据区域”标签中，选择系列产生在“行”。

（5）在图表选项的“标题”标签中，输入如图5-8所示的三个标题。

（6）选定图表位置为“作为新工作表插入”，按[完成]按钮。

图5-18 按人员分类的条形图
（三）折线图和饼图的绘制
折线图常用来显示随时间或类别而变化的趋势线。

饼图虽然只能画出一个数据系列的图形，但能表示出每个数据点的相对关系及其与整体的关系。

在工作表Sheet2中绘制下列图形。

1．根据工作表中给出的数据，建立如图5-19示的不同季度各城市的下雨情况折线图。

（1）选定区域A2:D6作为图表的数据源。

91
（2）选择图表类型为“折线图”的第4个子类型“数据点折线图”。

（3）选择图表数据源的“数据区域”标签中的系列产生在“列”。

（4）输入如图5-19所示的三个标题，选定图表位置为“作为其中的对象插入”。

图5-19 天气情况折线图
2．根据工作表中的数据，绘制如图5-20所示的不同季度北京下雨天数的三维饼图。

（1）选定区域A2:B6（或选定A2:C6试一试）。

（2）选择图表类型为“饼图”中的“三维饼图”子类型。

（3）选择图表数据源的“数据区域”标签中的系列产生在“列”。

（4）选定图表位置为“作为其中的对象插入”。

图5-20 天气情况饼图
（四）保存工作簿文件ex4.xls到D盘EXCEL文件夹中，关闭Excel应用程序。

三、思考题
1．嵌入式图表与图表工作表有什么区别？
2．绘制饼图时，如果选定多个数据系列，能将它们画出来吗？
3．将图表中的数据系列产生在“列”改成在“行”，应该使用右键快捷菜单的哪一项？
92
实验十编辑和格式化图表
一、实验目的
1．熟悉图表的修改方法
2. 了解图表数据与数据源的关系
2．了解图表中的各图表元素及其格式属性
3．熟练掌握图表格式化方法
二、实验内容
打开保存在D盘EXCEL文件夹中的ex4.xls工作簿文件，完成下列操作后存盘。

（一）编辑图表
1．复制、移动和删除图表
图表的复制、移动和删除操作较简单,直接用鼠标拖拽或用复制/粘贴方式就可以完成。

（1）在工作表Sheet2中，用鼠标选定折线图和饼图图表，单击鼠标右键，在快捷菜单中选择[复制]项。

（2）选定工作表Sheet3，将光标定位在某个单元格单击鼠标右键，在弹出的菜单中选择[粘贴]项，将图表复制到Sheet3工作表中。

（3）在工作表Sheet3中，用鼠标选定折线图表拖动到任意位置松开鼠标，则可移动图表。

（4）在工作表Sheet3中，用鼠标选中饼图图表，按Delete键就可删除图表。

2．修改图表
修改图表主要是修改图表的类型、数据源和图表的其它选项等。

在工作表Sheet3中，对折线图表进行修改编辑，完成操作后的样图如5-21所示。

图5-21 天气情况的面积图
（1）将折线图改成面积图。

①在图表区的空白处单击鼠标右键，从快捷菜单中选择[图表类型]项。

93
②在弹出的对话框中，选择图表类型是面积图的第2个子类型，按[确定]按钮。

（2）将图表的数据系列产生在“列”改为数据系列产生在“行”。

①在绘图区的空白处单击鼠标右键，从快捷菜单中选择[数据源]项。

②在弹出的对话框中，将“系列产生在”选项的“列”改为“行”，按[确定]按钮。

（3）将图表标题改为引用单元格Sheet2!A1的内容，
要修改图表标题或坐标轴标题，只需选中标题，输入新内容，直接进行修改编辑。

此外，图表标题也可以引用工作表数据区中某个单元格的内容。

①选中图表标题，将光标定位在编辑栏上，单击鼠标左键。

②在编辑栏中输入等号“=”，再用鼠标单击选定Sheet2表中的A1单元格，按Enter键。

③回到Sheet3表，观察编辑栏中的公式：=Sheet2!A1 。

④按Enter键，查看图表标题的内容。

⑤修改Sheet2表中A1单元格的内容，观察图表标题是否随A1单元格的内容而变化。

3．更改图表数据系列
更改图表的数据系列包括增加或减少数据系列、修改数据系列的值等。

对Sheet1表中的柱形图作如下修改。

（1）在柱形图表中删除数据系列。

①用鼠标单击“学生”数据系列上的任意一个数据点，选中该系列。

②按[Delete]键或鼠标右键快捷菜单的[清除]项，可将该数据系列从图表中删除。

（2）增加数据系列到图表。

①选定工作表Sheet1中的区域C2:C6（学生系列），单击常用工具栏中的[复制]按钮。

②选中柱形图表, 单击工具栏上的[粘贴]按钮，将“学生”数据系列增加到图表中（成为最后一个系列）。

增加图表中的数据系列，也可以直接用鼠标拖拽的方式来完成。

只要用鼠标选定工作表中的某列数据如C2:C6，将其拖拽到图表上松开鼠标，就可将该数据系列添加到图表中。

（二）图表格式化
格式化图表实际是格式化图表元素，每个图表元素都有自己的格式属性。

图表区和绘图区是两个不同的区域，在这两个区域上分别单击鼠标右键，可以设置图表区和绘图区的格式。

在图表中的各图表元素上单击鼠标右键，弹出不同的快捷菜单，可设置这些图表元素的格式。

1．柱形图格式化
将工作表Sheet1中的柱形图按下列要求进行格式设置，完成后的图表如图5-22所示。

（1）将图表标题设置成隶书、粗斜体、12号、红色字。

①在图表标题上单击鼠标右键，从快捷菜单中选择[图表标题格式]项。

②在弹出的对话框中选择“字体”标签，设置字体、字号、颜色等选项。

（2）给整个图表加上兰色、粗线边框，并将图表区背景颜色设置成浅绿色。

①在图表区(不是绘图区)空白处单击鼠标右键，选择快捷菜单中的[图表区格式]项⑼
②在对话框中选择“图案”标签，在“边框”组中自定义边框颜色为兰色、粗线。

94
③在对话框的“区域”组中选择背景颜色为浅绿色。

（3）去掉网格线
①在图表区单击鼠标右键，选择快捷菜单中的[图表选项]项。

②在弹出的对话框中选择“网格线”标签，去掉各种网格线的选中标记。

（4）显示“学生”系列的数值标志，并移动学生系列为第二个系列。

①在“学生”数据系列上单击鼠标右键，从快捷菜单中选择[数据系列格式]项。

②从弹出的对话框中选择“数据标志”标签，选中复选框“值”。

③选择“系列次序”标签，在系列次序列表中选中“学生”，单击右边的[上移]按钮，学生数据系列上一个次序。

（5）将分类轴标注文字设置成竖排。

①在分类轴（X轴）上单击鼠标右键，从快捷菜单中选择[坐标轴格式]项。

②从弹出的对话框中选择“对齐”标签，再选择方向组中的竖排文字。

图 5-22 格式化后的柱形图
2．折线图格式化
将工作表Sheet2中的折线图按下列要求进行格式设置，完成后的图表如图5-23所示。

（1）设置绘图区的背景色为浅紫色。

①在绘图区中单击鼠标右键，从快捷菜单中选择[绘图区格式]项。

②在弹出的对话框中，指定“区域”组中的颜色为浅紫色。

（2）给折线添加数据标志值。

①在图表区上单击鼠标右键，从快捷菜单中选择[图表区格式]项。

②在弹出的对话框中选择“数据标志”标签，选中复选框“值”。

（3）将数据系列“广州”的折线设置成深绿色、粗线，数据标记图案改为红色的“ ”标记，大小为11磅。

①在“广州”的折线上单击鼠标右键，从快捷菜单中选择[数据系列格式]项。

②在对话框中选择“图案”标签，在左边的“线形”组中设置单线、深绿色、粗线。

③在右边的“数据标记”组中设置样式为“ ”、前景色是“红色”、背景色为“无颜色”、大小是11磅。

95
（4）设置分类轴上数据点的起始位置，即将折线图的第一个数据点置于Y轴的零点上。

①选中分类轴（X轴）单击鼠标右键，从快捷菜单中的选择[坐标轴格式]项。

②在对话框中选择“刻度”标签，去掉复选项“数值(Y)轴置于分类之间”的选中标记。

（5）改变数值轴刻度标注，设置间隔为15。

①在数值轴（Y轴）上单击鼠标右键，选择快捷菜单中的[坐标轴格式]
②在弹出的对话框中选择“刻度”标签，在“主要刻度单位”文本框中输入数值15。

图5-23 格式化后的折线图
3．饼图格式化
将工作表Sheet2中的饼图按下列要求进行格式设置，完成后的图表如图5-24所示。

（1）设置图表区背景颜色为浅黄色，边框设置为加阴影、圆角。

①在图表区单击右键，从快捷菜单中选择[图表区格式]项。

②在“图案”标签中的“区域”组中选择浅黄色，“边框”组中选择“阴影”、“圆角”。

（2）显示饼图中百分比和数据标志。

①在饼图上单击鼠标右键，选择快捷菜单中的[数据系列格式]项。

②在对话框的“数据标志”标签中，选中“类别名称”和“百分比”两个复选框。

（3）将饼图的上下仰角调整为30度、左右转角设置成15度。

①在图表区单击鼠标右键，，选择快捷菜单中的[设置三维视图格式]项。

②在对话框中设置上下仰角为30度、左右转角为15度。

（4）将饼图中的一季度、二季度这两个部分与整个饼图分离开来。

将该块拖离中心位置。

先单击鼠标选中整个饼图，再单击鼠标选中要分离的数据系列块，
96
图5-24 格式化后的三维饼图
（三）保存工作簿文件ex4.xls到D盘EXCEL文件夹中，关闭Excel应用程序。

三、思考题
1．给图表添加数据系列的方法有哪几种？
2．要改变图例文字的字体，应如何操作？
97。