分类：Excel图表入坑

图表中彼此独立分离的图形表达集合，在我们的视觉中一般会被顺理成章地认为是单独的图表系列。比如图7.6-1的案例，该图中的4条橙红色线条就是一个典型的实例。

图7.6-1将一个系列变为多个

案例介绍

如图7.6-1所示的案例是一个“井”字图，该图通过4条管制线将图表割裂为9个区域，符合既定条件的仅有中间区域的数据点，超出该区域的均属异常数据点。

该图原始管制线数据往往仅有4个，主诉求表达系列为两列数值数据，通过将两列数据交叉点绘制在直角坐标系上，实现双指标数据的同时管控。

学习思考

关于“井”字图：

1. UCL：Upper Control Limit，上管制线。
2. LCL：Lower Control Limit，下管制线。
3. 如果使用该图的目的是分析当前数据水平，UCL和LCL的使用往往必须基于统计算法得出，该部分的内容请参考网络关于SPC（Statistical Process Control，统计过程控制）的相关知识。
4. 如果使用该图的目的是通过UCL和LCL来预警和控制当前数据水平，UCL和LCL往往是给定的数值。
5. 由于此图的应用与实践的相关内容已经超出本书写作范畴，本节的内容将侧重于图表的实现过程。

注：该案例使用的数据以管控为目的，UCL和LCL为直接给定数据。

案例分析

制作图7.6-1案例的主要挑战来自在图表中如何实现这4条管制线。最为直接的方法莫过于使用XY散点的误差线来实现，首要的前提是根据4个管制线数据来布局XY散点的数据。

在Excel图表中，XY散点的数据必须成对存在，当数据点没有指明X值时，Excel以默认的序列数值给出，这个结果并不是制作该图的期望，所以将4个管制线数据变换为成对XY散点数据就是该图实现的关键。

案例实现

如果使用XY散点的误差线，“指标A”的管制线需使用X轴误差线，“指标B”的管制线需使用Y轴误差线，依不同情况同时设置会出现不需要的误差线，或是误差线长度精确设定等问题。故本案例的具体实现完全采用了XY散点，具体实现说明如下：

1．两点构成一条直线，每个点需要两个数据点，根据已给出的UCL和LCL数值，以及图表横轴和纵轴的最大、最小值构建如图7.6-2所示的“辅助X”和“辅助Y”数据列。

图7.6-2　图7.6-1案例实现第1步

注：在这个步骤中有两点需注意：首先是完成两对数据之后需空置一对数据，因为Excel图表默认“空单元格的绘制方式为”不绘制（留空距）；其次是“指标A”和“指标B”的管制线，在处理横轴和纵轴引用数值时需反置。

2．选中“辅助X”和“辅助Y”所在数据列，生成XY散点图；然后设置横轴和纵轴的最小、最大刻度值；最后依次设置XY散点图右侧和顶部4个数据点的数据标签，并手工修改为“LCL”或“UCL”，如图7.6-3所示。

图7.6-3　图7.6-1案例实现第2步

3．选中“指标A”和“指标B”所在数据列，为图表添加图表系列；然后设置系列的数据点标记大小和线形为无，如图7.6-4所示。

图7.6-4　图7.6-1案例实现第3步

4．修饰整理后的最终效果如图7.6-5所示。

图7.6-5　图7.6-1案例实现第4步

在很多情况下，我们需要对图表局部进行必要的格式设置。

1. 在图表中使用上标和下标。
2. 自定义图表图例项的排序，以及放置位置。
3. 自定义设置图表坐标轴标签：

a）使用多层坐标轴标签，及在指定间隔留空位；

b）错层放置标签；

c）文字中使用局部字体的格式化……

这些内容看似非常简单，在图表中实现却并非易事。以下是实现上述设置的技巧。

上标和下标

在Excel图表中，系列的数据标志、图表标题等鼠标可直接选取文本内容，进行标签元素的编辑，在字体格式对话框中设置上标和下标（如图7.4-1所示）；但坐标轴和图例的标签不可直接编辑，即便是对引用的单元格进行了相应设置也依旧不支持，这往往给图表中使用上标和下标带来了不便。

图7.4-1　可编辑标签的局部字体格式设置

要在坐标轴和图例的标签中使用上标和下标，找到图表对应的单元格引用，选中需要设定为上标或下标的文本位置，打开“符号”对话框，“字体”为Arial Unicode MS，“子集”为上标和下标。可以看到常用的上标和下标：0、4、5、6、7、8、9、-、+、=、（、），如图7.4-2所示。1、2、3的上标，Excel 2003需要在“子集：拉丁语−1”里选取，Excel 2007/2010需要在“子集：拉丁语－1增补”里选取。这样上标和下标就被插入到了需要的位置，此时图表相应的标签就变成了需要的样式，如图7.4-3所示。

图7.4-2　插入符号的上标和下标

图7.4-3　图表中设置上标和下标的效果

自定义图例

Excel图表不支持对图例项进行排序，或指定图例的单一项在图表中的位置。但是这不代表完全没有办法，我们可以使用系列的数据标签来实现与图例相同的视觉效果。方法是选中系列的最后一个数据点，在数据标志中分别选取“系列名称”和“图例项标示”，此时显示的图表系列数据点标签就和图例相差无几了，如图7.4-4所示。该方法自由灵活，但当整个系列的每个数据点均需进行标注时，操作起来有一定冲突。

图7.4-4　自定义“图例”的效果

自定义坐标轴标签

1．图7.4-5案例，系列的分类轴标志引用了两列单元格区域，是一个典型的多层坐标轴标签案例。此处也可以引用更多的列或行，一个好的建议是坐标标签不宜超过3层，尤其是条形图的坐标标签，因为文本的方向无法调整。

图7.4-5　多层坐标轴标签

注：当Excel 2003中图表系列的源数据既包括列引用又包括行引用时，多层坐标的支持并不理想，会出现如图7.4-6所示的问题。Excel 2007/2010在系列初始引用时指定为列引用，而后变更为行引用不会出现问题，而Excel 2003则不可以。

图7.4-6　Excel 2003图表中包括行和列引用时多层坐标轴标签的状况

2．在Excel图表中，必须要有一个数值坐标，且数值坐标不可以使用文本属性的标签，这使得处理文本坐标变得复杂。此时该问题的解决方法是使用辅助系列来模拟，可以借用XY散点模拟坐标来实现，也可以使用条形图来实现，如图7.4-7案例所示。

图7.4-7　数值坐标使用非数值文本标签

使用条形图来实现时有一个前提条件：条形图系列必须独占一个坐标系，如果当图表较为复杂时，没有可供独占的坐标系时，此操作将无法成功。

3．有时为了区分不同分组，使用空单元格来进行视觉区隔是个不错的方法，但当系列分类轴标志为多层结构时，Excel对多层坐标标签引用的空单元格解释并不是非常理想，此时就需要在引用的空单元格中键入空格键来实现占位目的，如图7.4-8案例所示。

图7.4-8　使用空格键对多层标签的妙用

4．当分类信息过多时，坐标标签密密麻麻挤成一堆，不便于阅读，此时将坐标标签错层分成两排是非常不错的方法。

如图7.4-9案例所示，该案例使用技巧是：光标移至引用单元格的第一个字符前，按下键盘Alt＋Enter组合键，使用强制单元格内换行来实现；也可以使用XY散点的数据标志来实现，该方法在本章内容中多次提及，本处不再赘述。

图7.4-9　坐标轴标签错层放置

5．除上述的坐标标签的设置技巧外，对于坐标标签也可使用“数字”格式来设置不同的颜色。图7.4-10案例使用了3种不同颜色来标注不同的数值区域，方法是选中数值纵坐标，坐标轴格式>数字>“自定义”：［颜色26］［>79］#,##0;［>59］#,##0;［红色］#,##0。

图7.4-10坐标轴标签的颜色标注

交叉引用

本书4.4节对使用Excel内置的8个标准色有详尽描述。

1）除此，在Excel中可使用［颜色N］的方式来引用调色板颜色，此处N为调色板颜色序号。

注：不论版本，Excel支持N的数值为1～56的整数。

2）使用［颜色N］方式定义的颜色，在Excel 2007/2010中的解释是完全遵循早期版本的Excel默认56色调色板，并不支持自定义56色调色板。

在Excel 2007/2010“选项>保存”中，可进行和早期版本自定义56色调色板相一致的设置。

示例文档

本书所附案例文档7.4是上述多个案例的实现。

本节内容相对简单，详细的案例实现方法请读者参照案例文档演练学习。

有时我们需要设定图表绘图区为长宽相等的正方形，但绘制在直角坐标系的图表并不予以支持，调整的方法往往是通过目视拖曳来设定，不够精准。图7.3-1所示即是一个典型案例。

图7.3-1有趣的正方形

案例介绍

图7.3-1所示的案例是一个波士顿矩阵图，该图表通过两组数据的交叉点在直角坐标系中进行定位描点，并4等分图表面积，将图表裂区分组为4个不同区域，每1个区域代表1类属性，然后根据落在不同区域的数据所属属性来进行分析。

注：此图多用于商业策划与分析，有关具体的应用详情请搜索互联网。

学习思考

关于波士顿矩阵图：

1. 在视觉上具有强烈的分割布局，分割的每个区域在约定俗成上有“问题”、“明星”、“瘦狗”、“奶牛”四个约定俗成的隐喻指定。
2. 通常在实际的应用中，由于不同指标间的关系不同，图表在制作时的发射方向并不相同。
3. 需要说明：使用强制绘图区为正方形，可以使图表在视觉上更加容易被阅读和理解，但这并非是一个强制使用的要求。

案例分析

图7.3-1案例在直观制作上似乎并不具有太大难度：

1. 两组数据的交叉点在直角坐标系中进行定位描点使用XY散点图即可实现；
2. 4等分图表面积可以使用绘图区背景来实现；
3. 如何使绘图区固定为正方形？在Excel图表中，极坐标系图表具有正方形绘图区特性，在图表中引入饼图、环形图、雷达图即可解决该问题。

不论版本，Excel均可使用上述方法来实现这个图表。但不幸的是：由于Excel自2007始重构的图表引擎和之前版本存在一定的兼容性问题，故使用上述方法来实现本案例时，使用Excel 2003绘制的图表，Excel 2007打开后会没有绘图区背景，反之亦然。所以当文档要在不同版本的Excel中打开时，就面临着如何使不同版本打开时均显示正常的问题。由此，4等分图表面积使用绘图区背景来实现不具有广泛意义，这带来了以下新的挑战：

1）4等分图表面积必须改用堆积柱形图来实现，而这会给两组数据的交叉点在直角坐标系中进行定位描点带来困难，因为二者具有不同的类型的横轴（X轴），堆积柱形图使用分类坐标，而XY散点使用数值坐标。

2）极坐标系图表必须独占图表的一个坐标系，这必须使堆积柱形图和XY散点共置一个绘图区。所以挑战1）只可以在同一坐标系的前提下解决。

案例实现

本案例中“指标A”为XY散点图的X值，“指标B”为XY散点图的Y值，变换XY散点图X值是图表实现的关键。当纵轴置于分类坐标之间时，分类坐标实际是自0.5起始、步长为1的序列数值。此处使用两个分类，故其最大数值为2.5，这就为XY散点图X值的转换提供了有价值的信息。具体实现说明如下：

1．添加辅助列“辅助背景A”和“辅助背景B”，各两个数据点，根据“指标A”的最大数值，此处数值设定为50，该辅助列的目的是将绘图区面积4等分。选中新添加的两列辅助列，制作堆积柱形图，数据产生在列，如图7.3-2所示。

图7.3-2　7.3-1案例实现第1步

2．调整主纵坐标轴最小刻度为0，最大刻度为100，主要刻度单位为10；然后依次将左侧图片复制粘贴到右侧图表对应的数据点中，将图例删除。注：此处请根据需要来设置填充，可以设置为相对应的填充颜色，如图7.3-3所示。

图7.3-3　7.3-1案例实现第2步

3．将“指标B”所在列数值复制并粘贴进图表，系列产生在列，如图7.3-4所示。

图7.3-4　7.3-1案例实现第3步

4．添加辅助列“辅助X”，目的是将“指标A”所在列数值进行转换，以保持和分类坐标最小值和最大值的一致性。此处使用的公式大致如下：

=当前数值／坐标最大值*2

然后修改第3步中新建的图表系列为XY散点图，并修改系列所引用的X值，将其指定为“辅助X”所在列的数据区域，如图7.3-5所示。

图7.3-5　7.3-1案例实现第4步

5．此时图表横轴标签和真实数据存在差异，需要进行调整。根据图表数值轴，添加辅助列“辅助X轴X”、“辅助X轴Y”和“辅助X轴标签”，此处使用10行，目的是依照纵轴样式来模拟图表横轴标签。

1. “辅助X轴X”列的数值为：自0.7起始，步长间隔0.2，依次到2.5结束的数值；
2. “坐标X轴Y”列的数值为空；
3. “辅助X轴标签”列的数值为：自10起始，步长间隔10，依次到100结束的数值。

提示

1）和2）均可在第1和第2单元格依上述内容填入数值后，然后选中第1和第2单元格，下拉拖曳来完成；也可使用函数公式实现。

使用“辅助X轴X”和“辅助X轴Y”所在列数据，在图表添加一个XY散点图系列，并使用“辅助X轴标签”所在列数据，通过在编辑栏使用“＝”将该系列的数据点标签进行一个个的动态引用。最后将坐标轴标签设为“无”，并选中图表，设置图表选项：空单元格以“零值代表”，如图7.3-6所示。

图7.3-6　7.3-1案例实现第5步

6．在图表中添加只有1个数据点的新图表系列，并修改该系列的图表类型为饼图，设置饼图系列无填充无边框。此时不管我们如何拖曳设置图表，绘图区将保持绝对的正方形，如图7.3-7所示。

图7.3-7　7.3-1案例实现第6步

7．修饰整理后的最终效果如图7.3-8所示。

图7.3-8　7.3-1案例最终实现

其他

图7.3-9所示的案例是一个SWOT分析矩阵，准确而言这个并非图表，看不到丝毫的数值数据，仅仅是个文本列表。该案例的大多数实现方法是使用单元格或自选图形，虽然文本的编辑非常方便，但糟糕的是如果进一步进行美化编辑时就显得非常烦琐，对齐、颜色等问题让人烦心。换个思路来试试，使用图7.3-1案例的实现方法，我们会发现原来Excel图表还可以这么使用，最经典的是，无论如何调整其都保持正方形外观。

图7.3-9　SWOT分析矩阵

图7.3-10所示的案例是一个风险矩阵图，这个案例和图7.3-1案例实现的方法不出左右，而且该案例配合绘图区正方形的格式，更加易于判断风险等级。

图7.3-10　风险矩阵Risk Matrix

名词解释：

■　SWOT分析矩阵：

SWOT分析中，S代表Strength（优势），W代表Weakness（弱势），O代表Opportunity（机会），T代表threat（威胁），其中，S、W是内部因素，O、T是外部因素。根据既定的内在条件，借助该工具进行分析，找出优势、劣势及核心竞争力之所在。

■　风险矩阵Risk Matrix：

风险矩阵常配合FMEA（Failure Mode and Effect Analysis，失效模式和效果分析）来使用，其目的主要是评价当前项目的整体风险状态，通过对高风险等级具体项实施预防性措施，降低整体项目的风险系数。该图表一般使用3*3的9宫格和5*5的25宫格来界定项目风险等级。使用发生概率和影响等级两组数值在直角坐标系中交叉描点，发生概率和影响等级使用定性打分方式给出，评价指标为1～9的整数。

示例文档

本书所附案例文档7.3是上述案例的实现。

考虑到使用绘图区背景的方法有兼容性问题，案例包含了两个版本，有兴趣的读者可以在不同版本Excel中交叉打开观察，这样可以更好地理解兼容性问题。

除本节详述的案例实现方法外，还可使用绘图区背景及其他不同类型案例的实现方法，感兴趣的读者可参照案例文档演练学习。

有时，在图表制作时，局部细节处理上的复杂化，其实可以更好地解决问题。问题的解决过程其实就是对我们固有思维的挑战，图7.2-1所示就是这样的案例。

图7.2-1有效利用柱形错层

案例介绍

图7.2-1所示的案例是一个帕累托图，这类图反映的是2/8原理，即“关键的少数和次要的多数”，在原因分析中使用较多。该图的数据构成为：

1. 根据数值大小由大到小排列的一组分类数据；
2. 根据占比依次累计的两组分类数据，其中分别使用线型和柱形来表达。

注：常规帕累托图一般不使用占比累计柱形，此处使用柱形的目的是为了更加直观地看到累计效果。累计占比最大数为100％。此图属格式化图表，在相应的国家和国际标准中都有相应的标准使用规范，有关详情请搜索互联网。

学习思考

关于帕累托图：

1. 在表达形式上具有明显次序：从左至右、由大到小依次排开。在视觉上强调最大和最小的数据关系。
2. 由于排序的缘故，在布局上非常好地处理了比例、发射、密集、分割的视觉关系。
3. 通常在实际应用中，有多种不同的布局形式，比如对累计比例80％点上分类的分割裂区，或多个图表间比较时的横式帕累托图。

案例分析

图7.2-2是制作该案例使用的原始数据，由于原始数据非常简单。要制作该图我们必须在利用原始数据的基础上进行必要的数据加工：

图7.2-2　原始数据

1）数据的排序；

2）完成累计占比的数据辅助计算。

对于以上两点可以使用的方法较多，可以利用筛选或函数来处理。此外，该图带来的挑战主要是云梯状柱形的实现。这一点可以使用堆积柱形图来实现，不过这需要使用大量的辅助系列和作图辅助数据来完成，在数据处理上花费的时间和工夫较多。

案例实现

本案例采用一个柱形系列和多个百分比堆积柱形系列实现，虽然在管理图表系列时显得有些复杂，但在数据的处理上则仅使用原始数据的排序整理。具体的实现细节说明如下：

1．选中数据区域，使用筛选功能，将“个数”所在列单元格进行降序排列；然后根据选中数据区域，图表类型选百分比堆积柱形图，系列产生在行，制作图表，如图7.2-3所示。

图7.2-3　7.2-1案例实现第1步

2．再次选中数据区域，复制粘贴到图表，需要说明的是此处使用系列产生在列，并将新建系列置于次坐标系中，同时变更系列图表类型为簇状柱形图。注：Excel 2007和2010中效果如图7.2-4所示，在Excel 2003中，显示效果和图7.2-4有差异。

图7.2-4　7.2-1案例实现第2步

3．调整主坐标系柱形系列的分类间距和重叠比例均为0，勾选显示次横坐标轴，如图7.2-5所示。

图7.2-5　7.2-1案例实现第3步

4．调整主坐标系纵轴与横轴的交叉为最大值，次坐标系纵轴与横轴的交叉为0，并设置次纵坐标轴最大值为“个数”所在列单元格数值之和，如图7.2-6所示。

图7.2-6　7.2-1案例实现第4步

5．调整主坐标系横轴与纵轴交叉为最大值，取消次坐标系横轴与纵轴的交叉最大值的设置；将次坐标系中的柱形系列设置分类间距为0；最后使用自选图形的直线和无边框矩形，依次设置对应颜色，复制并粘贴到主坐标系的系列中，如图7.2-7所示。

图7.2-7　7.2-1案例实现第5步

6．修饰整理、局部美化后的最终效果如图7.2-8所示。注：Excel 2007和2010图表中包含多系列，则会不支持依数据点分色的功能，此时需手工去设定。

图7.2-8　7.2-1案例最终实现

示例文档

本书所附案例文档7.2是上述案例的实现。

本节所述方法仅是实现该案例的一种方法，在案例中另附有3个不同的方法来实现最终的案例，感兴趣的读者可参照案例文档演练学习。除此之外，也可以使用XY散点的误差线、折线图表的涨跌柱线来实现，这些方法的局限是色彩设置不够灵活。