分类：图表设计篇

当数据源中有多项数据时，能否在图表中任意切换数据，从而让图表可以快速调整汇总对象？

解题步骤

只有使用数据透视图才能快速地随意调整图表内容。

以图11.51所示的数据为例，生成透视图的步骤如下。

1.选择A1单元格，然后单击功能区的“插入”→“数据透视图”，在弹出的“创建数据透视图”对话框中选择“现有工作表”，并将位置设置为“G1”，然后单击“确定”按钮创建透视图。图11.52为选项设置界面，图11.53为生成的空白透视表与空白透视图。

2.在右方的任务窗格中将姓名拖到“轴（类别）”字段中，将销量拖到“值”字段中，然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”，此时透视图效果如图11.54所示。

图11.54是按姓名比较销量的，假设需要按组别比较销量之和，仅需两个步骤：在右方的任务窗格中将组别拖到“轴（类别）”字段中，将原本已经在“轴（类别）”字段中的姓名拖到单元格中从而删除它；然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”，此时透视图将变成图11.55所示的效果。

如果要按组别比较平均返修率，那么需要执行三个步骤：在右方的任务窗格中将返修率拖到“值”字段中，将原本已经在“值”字段中的姓名拖到单元格中从而删除它；然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”；再右键单击 H5 单元格，从右键菜单中依次单击“值汇总依据”→“平均值”，此时图表将变成图11.56所示的效果。

如果要求按性别比较平均销量，同样只需要三个步骤：在右方的任务窗格中将销量拖到“值”字段中，将原本已经在“值”字段中的返修率拖到单元格中从而删除它；接着将性别拖到“轴（类别）”字段中，将原本在“轴（类别）”字段中的组别拖到单元格中从而删除它；最后右键单击H4单元格，从右键菜单中依次单击“值汇总依据”→“平均值”，此时透视表将变成图11.57所示的效果。

如果要求同时按组别比较总销量和平均返修率，那么可按以下方式操作。

1.在右方的任务窗格中将销量拖到“值”字段中，接着将组别拖到“轴（类别）”字段中，将原本在“轴（类别）”字段中的性别拖到单元格中从而删除它，此时图表会显示为图 11.58 所示的效果。

2.选中透视图，然后从“格式”选项卡左上角的“图表元素”列表中选择“系列”求和项：返修率＂”，然后单击功区的“设计”→“更改图表类型”，在“更改图表类型”对话框中将“求和项：返修率”的图表类型由簇状柱形图修改为折线图，同时选择右边的“次坐标轴”复选框，最后单击“确定”按钮保存设置。图11.59为图表系列的设置界面。

3.选择I2：I4区域，按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框，并将数字格式设置为“百分比”，此时图表的最终效果如图11.60所示。

从图11.60中可以看出B组问题比较严重，销量最低，返修率却最高。

如果不使用图表直接看数据源，则不容易看出此问题。

知识扩展

1.数据透视表可以随心所欲地改变计算对象和计算方式，而数据透视图是建立在数据透视表的基础上的，必须搭配透视表使用，因此透视图拥有透视表的一切优点。

2.透视表和普通图表的数据刷新方式有所区别，普通图表会在单元格的数据变化后立即更新，而透视图不会随单元格的数值变化而变化，必须手工单击功能区中的“分析”选项卡下的“刷新”菜单才会更新图表内容。

图11.43中包含公司所有业务经理的本月业绩，能否用图表将它们展示出来，同时标示一条平均线，便于快速判断谁在平均线以上、谁在平均线以下？

解题步骤

Excel图表原本是不支持平均线的，但是可以变通一下思路，在图表中创建一个辅助系列，辅助系列的图表类型采用折线图，系列值使用AVERAGE函数计算而来。具体操作步骤如下。

1.选择 A1 单元格，然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”，此时Excel会生成图11.44所示的图表。

2.按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框，然后单击“新建”按钮，弹出“新建名称”对话框。

3.将名称设置为“平均线”，将引用位置设置为以下公式：

此公式可以生成8个业绩平均值，即一个数组中包含8个等值的元素。设置界面如图11.45所示。

4.选择图表，然后单击功能区的“设计”→“选择数据”，然后在“选择数据源”对话框中单击“添加”按钮，在弹出的对话框中将系列名称设置为“平均线”，将系列值设置为“=Sheet1！平均线”，设置界面如图11.46所示。

5.单击“确定”按钮返回“选择数据源”对话框，再次单击“确定”按钮返回工作表界面，此时图表中会新增一个系列，效果如图11.47所示。

6.从“格式”选项卡左上角的“图表元素”列表中选择“系列＂平均线＂”，然后单击功能区的“设计”→“更改图表类型”，在“更改图表类型”对话框的下方找到系列名称为“平均线”的系列，并将它的图表类型修改为“折线图”，操作界面如图11.48所示。

7.单击“确定”按钮返回工作表界面，此时图表中原本显示为柱形的“平均线”系列将会变成一条横线，效果如图11.49所示。

8.从“格式”选项卡左上角的“图表元素”列表中选择“系列 ＂业绩＂”，然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”。

9.单击功能区的“设计”→“添加图表元素”→“图表标题”→“图表上方”，然后将默认的图表标题修改为“业绩比较图”，图表最终效果如图11.50所示。

图11.50中的横线表示平均线，可以从图中看到有4人的业绩高于平均线、4人的业绩低于平均线。

知识扩展

1.为图表添加辅助系列时必须提供数据来源，数据源包含三种形式：其一是在工作表中添加辅助列，将数据放在该区域中，然后在图表的辅助系列中引用此区域的值，此方法最简单，但是不太美观；其二是定义名称，在名称中引用数据，在图表的辅助系列中引用名称；其三是直接在“添加数据系列”对话框中输入数组公式，此方法不支持函数和运算符，因此适应范围很小。

2.利用本例的思路可以在图表中添加业绩的最大值或最小值，也可以同时添加最大值和最小值的标示线。

3.如果选择图表中的图表系列以外的组件，然后对图表添加数据标签，那么图表中的所有系列都会显示数据标签，如果只选择单个图表系列再添加数据标签，则Excel只对当前选中的图表系列添加数据标签。

图11.37是某卖场的7大品牌手机销量，其中三星、苹果和小米的销量远远高于其他品牌，将所有品牌放在一起比较意义不大。

应该用何种方法实现既在同一个图表中比较所有品牌，又能将销量相近的品牌单独比较呢？

解题步骤

Excel提供了复合饼图和复合条饼图来展示图11.37所示的这种数据，以复合条饼图为例，具体操作步骤如下。

1.选择A1单元格，然后单击功能区的“插入”→“插入饼图或圆环图”→“复合条饼图”，图表效果如图11.38所示。

2.双击图表系列中的任意点，在工作表右方会出现“设计数据系列格式”任务窗格，然后在此窗格中将“第二绘区中的值”选项的值由“3”修改为“4”，并将“第二绘图区大小”的百分比调整为100%。设置界面如图11.39所示，图11.40则是调整后的图表效果。

3.单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”。

4.选择图表中的数据标签，然后取消选择右方的“设置数据标签”任务窗格中的“值”复选框，选择“百分比”复选框。设置界面如图11.41所示，而图11.42则是调整后的最终效果。

知识扩展

1.复合饼图或复合条饼图的特点是一个图表中显示一个主图和一个副图，主图总是包含副图中的数据，副图用于细化数据源中所有符合某个条件的数据。例如，据源中包含很多数据，其中部分数据拥有一个共同特点，那么可在副图中单独比较这部分有共同特点的数据。

复合图的优势在于既能整体比较又可局部比较。

本例中，华为、魅族、中兴、金立四家的销量较少，因此将它们作为一个整体在主图中参与比较，同时又在副图中额外比较一次。

2.生成图表时 Excel 会自动分配部分数据产生在副图中，绝大多数时候会分配有误，无法与用户的预期效果一致。当分配有误时，可在“设置系列点格式”任务窗格中调整。Excel 提供了4种调整方式，具体使用哪一种方式由数据的规律来决定。4种调整方式的功能说明见表11.1。

图11.32中的数据是8个运动员的跳远成绩，能否使用图表形象化地展示出这些数据？

解题步骤

跳远成绩宜用直方图来展示，具体操作步骤如下。

1.选择 A1 单元格，然后单击功能区的“插入”→“插入柱形图或条形图”→“簇状条形图”，默认生成的图表效果如图11.33所示。

2.双击图表系列（本图表的系列“成绩（米）”由8个系列点组成，双击任意点皆可），从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将分类间距修改为5%，此时图表将显示为图11.34所示的效果。

4.从“格式”选项卡左上角的“图表元素”列表中选择“水平 （值） 轴主要网格线”，然后按下Delete键删除网格线。

5.双击水平轴，然后在工作表右方的任务窗体中将最小值修改为0，将最大值修改为7，设置界面如图11.35所示。

6.选择图表的系列，然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”，此时图表将显示为图11.36所示的效果。

知识扩展

1.直方图和柱形图的区别在于方向不同，其他方面都没有区别。

2.当图表中只有单个系列时，图表不会生成图例。尽管可以手工将图例调用出来，但是在这种前提下图例已失去了存在价值，可以不用显示图例。

3.图表系列的默认分类间距修改为5%后，系列点变得更宽，从而使图表更美观，同时保留了5%的间距，使图表系列看起来像一条一条跑道，配合“跳远”这个主题，使图表更加形象化。

图11.25中包含目标数据与实际的业务数据，现要求使用柱形图表将它们表现出来，同时以百分比的形式标示出达成率。

解题步骤

柱形图或折线图本身无法生成包含百分比的数据标签，本例的思路是创建一个辅助列，在辅助列中计算每月的达成率，然后将达成率插入图表中，但是隐藏达成率的图表系列，仅保留其数据标签作参考，具体步骤如下。

1.选择 A1 单元格，然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”，图表效果如图11.26所示。

2.在“格式”选项卡左上角的“图表元素”列表中选择“系列 ＂实际＂”，然后单击下方的“设置所选内容格式”，从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将系列重叠值修改为50%，将分类间距修改为10%。

4.选择图表标题，将原文修改为“目标、实际与达成率”，然后将图表下方的图例移到右上角，按住绘图区下方的控制点向下拉，从而将绘图区拉伸至填满下方的图表区。此时图表将显示为图11.27所示的效果。

5.在D1单元格中输入“达成率”，然后在D2单元格中输入公式“=C2/B2”，并将公式向下填充到D11。

6.选择 D2：D11 区域，然后选择图表并按组合键＜Ctrl+V＞，粘贴数据到图表中，此时图表将显示为图11.28所示的效果。

7.在“格式”选项卡左上角的“图表元素”列表中选择“系列3”，然后单击下方的“设置所选内容格式”，从而在工作表右方产生“设置数据系列格式”任务窗格。

8.在任务窗格中单击“次坐标轴”，表示将图表的第3个系列放在次坐标轴中，设置界面如图11.29所示，而设置完成后的图表则将变为图11.30所示的效果。

9.从“格式”选项卡左上角的“图表元素”列表中选择“系列3”，然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“居中”。

10.从“格式”选项卡左上角的“图表元素”列表中选择“系列3”，然后单击功能区的“格式”→“形状填充”→“无填充颜色”，接着依次单击功能区的“格式”→“形状轮廓”→“无填充颜色”，此时图表中的“系列 3”没有轮廓、没有填充色，相当于被隐藏起来，只能看到目标数据和实际数据两个系列和“系列3”的数据标签，图表的具体显示效果如图11.31所示。

11.选择图例，然后单击图例中的“系列3”，按下Delete键删除。

知识扩展

1.Excel的图表支持次坐标轴，允许次坐标轴与主坐标轴使用不同的刻度值，因此设计复杂的图表往往需要用到次坐标轴。

次坐标轴的功能是让多个数值差异较大的图表系列在同一个图表中不起冲突，让两者都清晰地显现出来。假设本例不使次坐标轴，生成的图表就会像图11.28那样，由于“系列3”的数值太小，整个系列都显示在水平轴旁边，完全看不到图表系列的起伏变化，甚至会让人误以为图表中只有两个系列。

2.将“系列3”放到坐次标轴后，它会覆盖“目标”和“实际”两个系列。当为“系列3”添加数据标签之后不能删除“系列3”，因为删除系列的同时系列的数据标签也会一并消失，因此只能通过设置“无填充颜色”的方法隐藏“系列3”。

3.假设要求表示达成率的数据标签必须显示在“实际”系列的末端，那么只能手工逐个移动数据标签的位置，Excel不提供批量移动数据标签的工具。

图11.16中包含目标数据与实际的业务数据，现要求使用图表将它们表现出来，需要同时展现两者的差异。

解题步骤

突显差异值的直接解决方法是使用误差线，具体操作步骤如下。

1.选择 A1 单元格，然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”，图表效果如图11.17所示。

2.选中图表的任意位置，然后打开功能区的“格式”选项卡，在选项卡左上角有一个名为“图表元素”的组合键，单击其倒三角按钮会弹出一个列表，从列表中选择“系列 ＂实际＂”。然后单击下方的“设置所选内容格式”，从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将系列重叠值修改为50%，将分类间距修改为10%，设置界面如图11.18所示，而设置后的图表将显示为如图11.19所示的效果：

百分比与间距百分比

4.选择图表标题，将原文本修改为“目标与实际比较图”，然后将图表下方的图例移到右上角，按住绘图区下方的控制点向下拉，从而将绘图区拉伸至填满下方的图表区。此时图表将显示为图11.20所示的效果。

通常，没有特殊要求时，图11.20已经是一个完整的图表，它将目标数据和实际数据两个系列半重叠显示，目的是告知图表查看者它们是一组数据、存在某种关联，吸引查看者去关注两者的差异。尽管在本质上此图11.20和图11.17没有区别，但是调整两个系列的重叠百分比之后有助于强调两者的关系，同时方便比较。

本例还要求标示出差值，因此还需要继续以下步骤。

5.在D1单元格输入“差值”，在D2单元格输入公式“=B2-C2”，然后将公式向下填充到D13。

6.从“格式”选项卡中的“图表元素”列表中选择“系列＂目标＂”，然后依次单击功能区的“设计”→“添加图表元素”→“误差线”→“其他误差线”选项。

7.在右边的“设置误差线格式”窗格中将误差线的方向设置为“负偏差”，将末端样式设置为“线端”，将误差量设置为“自定义”，最后单击“指定值”，弹出“自定义错误栏”对话框。

8.在“负错误值”栏中输入“=Sheet1！D2：D13”，然后单击“确定”按钮返回工作表界面。图11.21是误差线选项设置界面，图11.22则用于指定负错误值的来源。

9.从“格式”选项卡中的“图表元素”列表中选择“系列 ＂实际＂”，依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签内”；接着从“格式”选项卡中的“图表元素”列表中选择“系列 ＂目标＂”，并依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”，此时图表的最终效果如图11.23所示。

知识扩展

1.误差值的原本作用并不是标示两个系列之间的差异，它属于统计学中的一个概念，用于标注当前系列的取值范围，允许上下浮动一定范围。由于Excel允许自定义误差值，因此它也可以用于连接两个系列或标注两个系列的数值差异。

2.当两个系列的大小相近时，应该将一个系列的标签放在靠上的位置，另一个系列的标签放在靠下的位置，从而避免标签重叠，影响美观，同时也可以提升查看图表的效率。

3.如果要求在图表中标注误差值，那么只能采用手工修改的方式来完成。例如，对系列“实际”添加数据标签，然后逐个选中数据标签，并修改为误差值，修改后的效果如图11.24所示。

图11.24中的标签207表明实际数据与目标数据还差207，-60则表示超过了目标值60个单元格。

图 11.1 是某学生一年级到六年级的语文成绩和数学成绩，查看文字很难瞬间判断成绩升降趋势，能否将数字图形化，从而瞬间判断数据的变化趋势呢？

解题步骤

折线图可以展示数据的升降趋势，设计折线图的步骤如下。

1.选择 A1 单元格，打开“插入”选项卡，然后单击“插入折线图或面积图”右方的倒三角按钮，最后从列表中选择“折线图”。图11.2展示了“折线图”的位置，而图11.3则为单击菜单后生成的默认样式的折线图。

2.单击图表的标题，然后输入“成绩走势图”。

3.选择图例，并将它移到图表的右上角，然后将绘图区向下拉大，从而让绘图区填满下方的图表区。图11.4所示的箭头表示在该处按下鼠标拖动即可调整绘图区大小，图11.5是调整后的效果。

查看图表时的重点在于垂直轴的刻度、图例和图表系列，其中图例的功能是通过颜色来区分图表系列，图表系列则是图表的重中之重，它用于展示数据的大小和发展趋势。

图 11.6 展示了一个图表的基本组件，其中浮在网格线上方的两条折线即为图表系列，它们展示了语文和数学成绩的发展趋势。

从图11.6中可以看到语文成绩和数学成绩都在上下波动，但是整体上语文成绩呈上升趋势、数学成绩呈下降趋势。

如果要让图表系列显示数据，应按以下方式操作。

1.单击系列“语文”，然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“上方”。

2.以同样的方式对系列“数学”添加数据标签，最终效果如图11.7所示。

知识扩展

1.使用图表展示数据时有一个基本规则：如果没有特别指定图表的种类，那么当数据源不存在时间递进关系时，应使用柱形图或条形图，反之采用折线图。

本例中的数据来自六个年级的成绩，而这六个年级存在时间递进关系，因此宜用折线图展示数据的发展变化趋势。对于图11.8这种数据，则宜用柱状图或条形图，图表效果如图11.9所示。

线折图通常展示数据的变化趋势，柱状图则用于比较大小。当然也可以在柱状图中添加趋势线表明趋势，或者直接在一个图表中同时呈现柱形和折线，效果如图11.10所示。

图11.10的设计过程不再讲解，但是会提供一个已经设计好的成品供读者下载学习。

2.如果要突出显示数据的变化、差异，将数值轴（也称垂直轴）的最大值和最小值的差值缩小即可。操作方法是双击数值轴，然后在工作表右方的任务窗格中将最小值由 0 修改为 50，将最大值由100修改为95。图11.11是数值轴设置界面，图11.12则是调整后的效果。

3.如果需要让图表显示效果更美观，如将图表区调整为圆角、阴影，那么可以双击图表靠边框的空白区域，然后在右方的任务窗格中依次单击“填充与线条”→“边框”→“圆角”，然后再依次单击“效果”→“阴影”→“右下斜偏移”。圆角与阴影的设置界面见图11.13和图11.14，而图11.15则是设置完成后的图表效果。