图表与图形
　　

　　
　　
　　
　　在Excel中，能够方便地制作多种形式的统计图表，使表达的数据更加直观。本章通过若干案例介绍Excel图表与图形的应用技术。
　　主要包括以下内容：
　　（1）图表的创建和编辑，图表属性的设置，函数图像的绘制。
　　（2）迷你图、工程进度图的创建和编辑。
　　（3）名称的使用，数据的筛选、分列。
　　（4）图片的使用，数据透视表和数据透视图的制作。
　　（5）工作簿函数OFFSET、MATCH的应用。
3.1  两城市日照时间对比图表
　　本节制作一个图表，用来对比“四平”和“大连”两个城市的日照时间。
　　首先创建一个工作簿，保存为“两城市日照时间对比图表.xlsx”。
　　1. 制作表格
　　在Sheet1工作表中，设计一个表格，在网上搜集相关数据，把两城市24个节气的日出时间和日落时间输入到表格中，得到图3-1所示的结果。
　　在D2单元格输入公式“=C2?B2”并向下填充到D25，在G2单元格输入公式“=F2?          E2”并向下填充到G25，求出两城市24节气的日照时长，得到图3-2所示的内容完整的        表格。
　　2. 创建图表
　　选择A1:G25单元格区域，在“插入”选项卡的“图表”选项组中单击“插入折线图或面积图”按钮，找到并选择“带数据标记的折线图”，在当前工作表中创建一个图表。
　　用鼠标将图表拖动到适当位置，拖动图表外边框上的尺寸控点适当改变大小，得到图?3-3所示的图表。
　　3. 编辑图表
　　选定图表，在“图表工具>格式”选项卡的“当前所选内容”选项组的下拉列表中选择“垂直（值）轴”，再单击“设置所选内容格式”按钮。在“设置坐标轴格式”任务窗格的“坐标轴选项”中的“边界”内，设置“最小值”为“0.15”、“最大值”为“0.85”（去除图中空白部分）。


图3-1  两城市24个节气日出、日落时间表

图3-2  两城市24个节气日出、日落、日照时间表

图3-3  新建的图表
　　在“图表工具>设计”选项卡的“图表布局”选项组中单击“添加图表元素”按钮，选择“网格线>主轴主要垂直网格线”命令，分别单击图表中“主轴主要网格线（H）”和“主轴主要垂直网格线（V）”，设置网格线颜色为“黑色”。
　　单击“垂直（值）轴”，在“设置坐标轴格式”对话框中将“坐标轴选项>刻度线”设为“外部”；单击“水平（类别）轴”，在“设置坐标轴格式”对话框中将“坐标轴选项>刻度线”设为“外部”。
　　在“图表工具>设计”选项卡的“图表布局”选项组中单击“添加图表元素”按钮，选择“图表标题>图表上方”命令，设置图表标题。
　　最后得到图3-4所示的图表。


图3-4  编辑后的图表
　　从图表中可以看出，两个城市在“冬至”时节的日出时间大致相同（只差2分钟），而大连的日落时间晚于四平25分钟，大连的日照时间比四平长27分钟；两个城市在“夏至”时节的日落时间大致相同（也只差2分钟），而大连的日出时间晚于四平26分钟，大连的日照时间比四平短28分钟；两个城市在“春分”和“秋分”时节的日照时间大致相同，但大连的日出、日落时间均晚于四平。
3.2  迷你图与工程进度图
　　本节先介绍迷你图的创建和编辑方法，然后给出一种制作工程进度图的方法。
　　1. 创建和编辑迷你图
　　迷你图是一种可以插入到单元格中的微型图表，可显示一系列数值的趋势（例如，季节性增加或减少、经济周期），还可以突出显示最大值和最小值。
　　在图3-5所示的工作表中创建一个表格并输入相关数据，得到一个模拟的公司历年利润表（单位：万元）。


图3-5  公司历年利润表
　　在“插入”选项卡的“迷你图”选项组中单击“折线图”按钮。
　　在“创建迷你图”对话框中，指定“数据范围”为A3:E3、“位置范围”为F3，单击“确定”按钮，得到图3-6所示的迷你图效果。


图3-6  迷你图效果
　　当在工作表中选择某个已创建的迷你图时，功能区中将会出现“迷你图工具>设计”选项卡。通过该选项卡，可以对迷你图进行编辑数据、更改类型和样式、显示或隐藏数据点等操作。
　　2. 制作工程进度图
　　下面通过一个实例来介绍用堆积条形图实现工程进度图的方法。
　　（1）在Excel工作表中创建一个图3-7所示的工程进度表，填入工程每个阶段的起始     日期和所用天数。结束日期可以用公式填写，在D2单元格输入公式“=B2+C2”，并向下填充到D9单元格。
　　（2）选择A1:C9单元格区域，在“插入”选项卡的“图表”选项组中单击“插入柱形图或条形图”按钮，再选择“二维条形图>堆积条形图”命令，在当前工作表中插入一个      图表。
　　（3）选择图表中的“开始日”数据系列，在“图表工具>格式”选项卡的“当前所选内容”选项组中单击“设置所选内容格式”按钮，打开“设置数据系列格式”任务窗格。在任务窗格的“系列选项”选项组中设置“分类间距”为0%，在“填充”选项中设置“无填充”。
　　（4）选择“水平（值）轴”，在“图表工具>格式”选项卡的“当前所选内容”选项组中单击“设置所选内容格式”按钮，打开“设置坐标轴格式”任务窗格。在任务窗格的“坐标轴选项”选项组中设置“最小值”为“2015/5/10”、“最大值”为“2015/8/1”，在“数字”选项组中设置“日期”的“类型”为“3/14”。
　　（5）选择“垂直（类别）轴”，在“图表工具>格式”选项卡的“当前所选内容”选项组中单击“设置所选内容格式”按钮，打开“设置坐标轴格式”任务窗格。在任务窗格的“坐标轴选项”选项组中，选中“逆序类别”复选框，使垂直轴的分类标签与表格的顺序      一致。
　　（6）选择“图表区”，在“图表工具>设计”选项卡的“图表布局”选项组中单击“添加图表元素”按钮，选择“图表标题>图表上方”命令，设置图表标题为“工程进度图”。
　　（7）删除图例，适当调整图表格式，得到图3-8所示的工程进度图。


图3-8  工程进度图


3.3  绘制函数图像
　　下面以绘制y=sin(x)的曲线为例，介绍一种函数图像制作的具体方法。
　　创建一个Excel工作簿，保存为“绘制函数图像.xlsx”。在Sheet1工作表的A1单元格输入标题“x”，用来表示自变量。用序列数据自动填充的方法，在A2:A10单元格中从小到大输入0～360?、间隔45°的角度值。在B1单元格输入标题“y=sin(x)”，用来表示函数值。在B2单元格输入“=SIN(3.14*A2/180)”，按Enter键后得到计算结果0。将B2单元格的公式向下拖动，一直复制到?B10?单元格，得到各自变量值所对应的函数值。结果如图?3-9          所示。
　　选中A1:B10区域，在“插入”选项卡的“图表”选项组中单击“插入散点图(X、Y)或气泡图”按钮，选择“带平滑线的散点图”，得到图3-10所示的图表。




图3-9  工作表中的自变量值与对应的函数值
图3-10  最初的图表
　　选中图表区右侧的“图例”，按Delete键将其删除。右击“垂直（值）轴 主要网格线”，在弹出的快捷菜单中选择“设置网格线格式”命令。在“设置主要网格线格式”任务窗格中设置短画线类型为“方点”。右击“水平（值）轴”，在弹出的快捷菜单中选择“设置坐标轴格式”命令。在“设置坐标轴格式”任务窗格中设置坐标轴选项“边界”的“最小值”为0、“最大值”为360、“单位”的“主要”为45。最后得到图3-11所示的图表。


图3-11  加工后的图表
　　用类似的方法，可以绘制其他三角函数、对数函数、指数函数等图像。


3.4  制作动态图表
　　创建一个Excel工作簿，保存为“制作动态图表.xlsx”。在Sheet1工作表设计一个数据表格，输入一些用于测试的数据，如图3-12所示。
　　选中A列，在“数据”选项卡的“排序和筛选”选项组中单击“筛选”按钮，在A列启用筛选。
　　选中A1:D7单元格区域，在“插入”选项卡的“图表”选项组中单击“插入柱形图或条形图”按钮，选择“二维柱形图>簇状柱形图”，得到图3-13所示的图表。




图3-12  工作表中的数据
图3-13  与数据区对应的图表
　　此时，在A列筛选不同的年份，图表将随之发生变化。
　　若要对商品类别进行筛选，也就是对数据列进行筛选，可以采用如下方法：
　　（1）选中F1单元格，在“数据”选项卡的“数据工具”选项组中单击“数据验证”按钮。在“数据验证”对话框中选择“设置”选项卡，在“允许”下拉列表中选择“序列”，在来源编辑框中输入“=$B$1:$D$1”，如图3-14所示。然后单击“确定”按钮。


图3-14 “数据验证”对话框
　　（2）在F2单元格输入以下公式。
=OFFSET(A2,0,MATCH($F$1,$B$1:$D$1,0))
　　并将公式向下填充到F7单元格。这时，只要在F1单元格中选择一个商品类别名，该类商品的数据就会自动复制到F2:F7区域。
　　（3）同时选中A1:A7和F1:F7区域，在“插入”选项卡的“图表”选项组中单击“插入柱形图或条形图”按钮，选择“二维柱形图>簇状柱形图”命令，创建一个图表。删除图例，得到图3-15所示的结果。


图3-15  与A1:A7、F1:F7区域对应的图表
　　当在F1单元格选择不同的商品类别名时，图表将随之发生变化。
3.5  图表背景分割
　　下面制作一个具有背景分割效果的图表，用来直观地显示不同区间的数据。
　　在图3-16所示的工作表中，B2:C11区域是数据源，表示某商店前三季度各个月份的销售额（单位：万元）。E2:E11、F2:F11、G2:G11是3个辅助数据区，分别表示销售业绩较差、一般、良好的区段，即，销售额在60以内的为较差，销售额在60～80区间的为一般，销售额在80～100区间的为良好。
　　制作带有分割背景的图表步骤如下：
　　（1）选中B2:C11区域，在“插入”选项卡的“图表”选项组中单击“插入柱形图或条形图”按钮，选择“二维柱形图>堆积柱形图”命令，在当前工作表中生成一个图表，如图3-17所示。
　　（2）选中E2:G11单元格区域，按Ctrl+C快捷键复制，再选中图表，按Ctrl+V快捷键粘贴，将辅助区域的3个系列添加到图表中，得到图3-18所示的图表。



图3-17  生成的图表
图3-18  添加3个辅助系列后的图表
　　（3）选择“销售额”系列，右击，在弹出的快捷菜单中选择“更改系列图表类型”命令，进入“更改图表类型”对话框中的“所有图表”选项卡，其中左侧已选择“组合图”，将右侧“销售额”系列的图表类型设置为“折线图”，单击“确定”按钮，得到图3-19所示的结果。
　　（4）选中“一般”系列，右击，在弹出的快捷菜单中选择“设置数据系列格式”命令。在“设置数据系列格式”任务窗格的“系列选项”选项组中设置“分类间距”为“0%”，“填充”格式为“无填充”。用同样方法设置另外两个系列“较差”和“良好”的填充颜色分别为“浅绿”和“浅蓝”，得到图3-20所示的结果。



图3-19  将“销售额”系列的图表类型
    改为“折线图”后的图表
图3-20  设置3个辅助系列颜色后的图表
　　（5）选中图表区的图例，按Delete键将其删除。选中“垂直（值）轴”，右击，在弹出的快捷菜单中选择“设置坐标轴格式”命令，在“设置坐标轴格式”任务窗格的“坐标轴选项”选项组中设置“最大值”为100、“最小值”为50。选中图表，在“图表工具>格式”选项卡的“当前所选内容”选项组的下拉列表中选择“垂直（值）轴 主要网格线”，单击“设置所选内容格式”按钮，在“设置主要网格线格式”任务窗格中设置“线条颜色”为“无线条”。最终得到如图3-21所示的图表。


3.6  人民币对欧元汇率动态图表
　　本节将按以下要求制作一个人民币对欧元汇率动态图表。
　　（1）将银行网站近几年人民币对欧元的汇率数据复制到Excel工作表。保留每日一条记录。数据项包括“年”“月”“日期”和“中间价”。
　　（2）能够按年、月对数据进行筛选。
　　（3）根据筛选结果生成对应的图表。
　　例如，对2018年8月的数据进行筛选，得到图3-22所示的结果。对应的图表如图3-23所示。


图3-22  2018年8月外汇牌价

图3-23  对应的图表
　　具体实现方法如下。
　　1．工作表设计与数据导入
　　创建一个Excel工作簿，保存为“人民币对欧元汇率动态图表.xlsx”。
　　在Sheet1工作表中，选中所有单元格，将背景颜色填充为“白色”，将字体设置为“宋体”及10号字。选中A～D列，设置虚线边框。选中C列，将单元格的数字设置为日期格式，水平右对齐。选中D列，将单元格的数字设置为“常规”格式，水平右对齐。将A1:D1区域的背景颜色填充为“浅青绿”（R:204, G:255, B:255），设置水平居中对齐方式。输入标题文字“年”“月”“日期”和“中间价”。
　　登录银行网站，打开往日外汇牌价网页，搜索指定日期范围的欧元汇率信息。将“日期”数据复制到当前工作表C列，对应的“中间价”数据复制到当前工作表D列。
　　在A2单元格输入公式“=YEAR(C2)”，并将公式向下填充，填写每个日期当中的年份。在B2单元格输入公式“=MONTH(C2)”，并将公式向下填充，填写每个日期当中的月份。以便按年、月进行筛选。
　　选中A1单元格，在“数据”选项卡的“排序和筛选”选项组中单击“筛选”按钮，对数据区启用筛选。
　　这时，在“年”下拉列表中选择2018，在“月”下拉列表中选择8，就会得到图3-22所示的筛选结果。
　　2．图表设计与动态刷新
　　打开“人民币对欧元汇率动态图表”工作簿，在“公式”选项卡的“定义的名称”选项组中单击“定义名称”按钮，打开“新建名称”对话框。
　　在“新建名称”对话框中，定义名称n的引用位置为“=COUNTA(Sheet1!$C:$C)”。相当于用变量n表示C列非空单元格的个数，即有效数据的行数。定义名称v的引用位置为“=OFFSET(Sheet1!$D$1,1,0,n?1)”。相当于用变量v表示D列从第2行到有效数据最后一行所对应的区域。定义名称x的引用位置为“=OFFSET(Sheet1!$C$1,1,0,n?1)”。相当于用变量x表示C列从第2行到有效数据最后一行所对应的区域。
　　将名称v和x用于数据系列“值”和“水平（分类）轴标签”，图表会随数据区的变化自动调整，达到动态刷新目的。
　　在Sheet1工作表中，选中C、D列，在“插入”选项卡的“图表”选项组中单击“插入折线图或面积图”按钮，选择二维“折线图”命令，得到图3-24所示的图表。
　　选中图表区右侧的“图例”，按Delete键将其删除。
　　右击图表区，在弹出的快捷菜单中选择“设置图表区域格式”命令。在“设置图表区域格式”任务窗格的“属性”选项组中选择“大小和位置均固定”单选按钮。
　　选中图表标题，在编辑区内将标题改为“1欧元对人民币中间价”。
　　选中系列“中间价”，在Excel编辑栏中输入以下公式。
　　=SERIES(Sheet1!$D$1,人民币对欧元汇率动态图表.xlsx!x,人民币对欧元汇率动态图表.xlsx!v,1)
　　该公式的作用是设置数据系列的“值”为“=人民币对欧元汇率动态图表.xlsx!v”，设置“水平（分类）轴标签”为“=人民币对欧元汇率动态图表.xlsx!x”。此处引用了名称v和x。


图3-24  最初的图表
　　公式中用到了SERIES函数，该函数的第1个参数是显示在图例中的名称，第2个参数是显示在水平（分类）轴上的标签，第3个参数是数据系列的值，第4个参数是系列的顺序。由于此图表只有一个系列，因此顺序参数为 1。
　　最后得到图3-25所示的图表。


图3-25  最终图表样式
　　此后，在工作表的数据区中，不论是添加、删除数据，还是对数据进行筛选，图表都会自动刷新。
3.7  图片自动更新
　　在Excel中，使用动态名称与ActiveX控件，能够实现工作表中的图片自动更新。下面以制作职员的资料表为例，说明如何让照片随姓名的改变而改变。
　　1. 设计工作表
　　创建一个Excel工作簿，保存为“图片自动更新.xlsx”。
　　在工作簿中设计两张工作表，“资料表”工作表用于显示职员的资料，“图片库”工作表用于存放每个职员的照片。两张工作表的结构和内容如图3-26所示。

    
图3-26 “资料表”和“图片库”工作表
　　在“图片库”工作表中添加照片的方法如下：
　　（1）选中B1单元格，在“插入”选项卡的“插图”选项组中单击“图片”按钮。在“插入图片”对话框中选择相应的照片文件，单击“插入”按钮。
　　（2）单击照片，然后把光标移动到右下角的圆圈上，当光标变成一个斜向双箭头时，拖动鼠标，调整大小，直至单元格能容纳整张照片。
　　（3）右击照片，在弹出的快捷菜单中选择“设置图片格式”命令。在“设置图片格式”对话框的“属性”选项组中选择“大小固定，位置随单元格而变”单选按钮。
　　用同样方法插入其他职员的照片。
　　2. 定义名称
　　（1）在“资料表”工作表中选中B1单元格，在“公式”选项卡的“定义的名称”选项组中单击“定义名称”按钮，打开“新建名称”对话框，将当前单元格的名称定义为name。
　　（2）再次打开“新建名称”对话框，定义名称pic的引用位置为

     =OFFSET(图片库!$B$1,MATCH(name,图片库!$A$1:图片库!$A$4,0)-1,0)

　　名称pic的值是一个单元格，该单元格在“图片库”工作表中，以B1为基准，行偏移量为表达式“MATCH(name,图片库!$A$1:图片库!$A$4,0)?1”的值，列偏移量为0。
　　在MATCH函数中，参数name为要查找的姓名，参数“图片库!$A$1:图片库!$A$4”为查找区域，参数0表示查找等于name的第一个值，区域内容可以按任何顺序排列。函数的返回值是与指定姓名匹配的单元格行号。
　　3. 在“资料表”中显示图片
　　选中“资料表”工作表的B3单元格，插入任意一张图片，调整图片的大小，使之与单元格匹配。在Excel编辑栏中输入公式“=pic”。这时，在B1单元格内输入不同职员的姓名，B3中就能够自动显示其照片，达到图片自动更新的目的。结果如图3-27所示。
　　将B1单元格的数据验证条件设置为允许“序列”，来源设为“图片库”工作表的A1:A4区域，可以在下拉列表中选择职员姓名。


图3-27 “资料表”中的照片
3.8  数据透视表和数据透视图
　　数据透视表是一种交叉式表格，可以实现对数据的快速汇总、筛选、排序，可以按行与列进行组合。
　　1．创建数据透视表
　　创建一个Excel工作簿，保存为“数据透视表与透视图.xlsx”。在其中的一个工作表中输入图3-28所示的数据。
　　选中E3单元格，在“插入”选项卡的“表格”选项组中单击“数据透视表”按钮，打开“创建数据透视表”对话框。在对话框中选择A2:C14区域，将“选择放置数据透视表的位置”设为“现有工作表”的E3单元格，单击“确定”按钮。
　　在“数据透视表字段”任务窗格中，将“年份”作为列标签、“月份”作为行标签、“电话费”作为数值求和项拖动到特定的位置，得到图3-29所示的数据透视表。


图3-29  数据透视表
　　2．数据透视表图表化
　　选中数据透视表区域的任意单元格。在“数据透视表工具>分析”选项卡的“工具”选项组中单击“数据透视图”按钮，打开“插入图表”对话框。在对话框中选择“柱形图”中的“簇状柱形图”，单击“确定”按钮，在当前工作表中插入一个图3-30所示的图表。


图3-30  数据透视图
　　右击图表的“绘图区”，在弹出的快捷菜单中选择“设置绘图区格式”命令，打开“设置绘图区格式”任务窗格。可以根据需要设置区域颜色、边框等属性。
　　右击“垂直（值）轴 主要网格线”，在弹出的快捷菜单中选择“设置网格线格式”命令，打开“设置主要网格线格式”任务窗格。可以设置线条颜色、线型等属性。
　　通过图表区中的字段筛选器，可更改图表中显示的数据。
　　选中数据透视图，Excel功能区会出现“数据透视图工具”中的“分析”“设计”“格式”3个选项卡。通过这3个选项卡，可以对透视图进行修饰和设置。
上机练习
　　1. 在Excel工作表中，创建图3-31所示的表格并输入数据。选定“消费类别”和“消费金额”两列数据，创建一个三维饼图，标题为“居民年均消费情况”，图例放在底部，显示两位小数百分比。结果如图3-32所示。





图3-31  工作表中的表格和数据
图3-32  三维饼图
　　2. 在Excel工作表中，创建图3-33所示的表格。然后，根据三门课成绩和学生姓名创建图表，放在当前工作表中。图表标题为“成绩统计图”，图表类型为“三维簇状柱形图”，三维视图格式的X、Y转角均为30度。结果如图3-34所示。


图3-33  工作表中的表格和数据

图3-34  三维簇状柱形图
　　
　　
　　
　　
　　
66
Excel 2016高级应用案例教程

53
第3章  图表与图形