分类：Excel图表入坑

Excel图表仅支持使用两个数值坐标，当面对的数据是典型的工程技术类数据时，往往需要多个坐标系，故如何扩充坐标个数就是一个非常有价值的问题。图8.6-1所示的案例即是一个典型例子。

图8.6-1　数据归一化

案例介绍

图8.6-1所示案例描述了发酵工程研究所关注的：pH、残糖量、酶活、生物量、溶氧5个指标同发酵时间的关系，该图的最大特点是这5个指标分别使用完全不同的数量单位，每一个指标对应一个独立的数值纵轴。该图更多的作用是发现不同指标数据的交集点，以找到最佳方案。

学习思考

关于图8.6-1案例：

1）这个图表案例属工程技术类图表，工程技术类图表的使用范围有局限性，其只限于在所应用的行业内进行交流。切勿照猫画虎，生搬硬套到非工程技术类的应用中，此类不恰当的应用屡见不鲜。

2）学习该案例的普遍意义在于如何进行图表的坐标模拟。

案例分析

由于Excel的限制，图8.6-1案例的实现关键在于如何使具有不同数值大小和单位的数值，在同一个参考基准下进行数值转换，使所有数据变为同一类坐标下的数据，然后模拟这些进行了数据变换的对应坐标即可，该方法在数学中被称为“归一化”。

提示

关于归一化：

日常制作图表使用的归一化方法常常是线形函数的转换，除此之外常用的转换方法还包括对数函数转换和反正切函数转换，以下分别是转换算法：

1）一般线性函数转换y=(x-MinValue)/(MaxValue-MinValue)

注：x、y分别为转换前、后的值，MaxValue、MinValue分别为样本的最大值和最小值。

2）对数函数转换y=log10(x)

注：以10为底的对数函数转换。

3）反正切函数转换y=arctan(x)*2/PI

注：以三角函数转换。

案例实现

结合8.6.2节的分析，图8.6-1案例的具体实现方法说明如下：

1．将原始数据中的“残糖量”、“酶活”、“生物量”、“溶氧”列所在数据，使用函数进行线形归一化整理，整理后的效果如图8.6-2右侧所示。

图8.6-2　8.6-1案例图表实现第1步

注：本案例的归一化方案采用了线形类比法，是以“pH”为参考基准进行的数值转换。

2．选中“时间”、“pH”及整理后的“残糖量”、“酶活”、“生物量”、“溶氧”列所在数据，数据产生在列，图表类型为XY散点图，“时间”为图表的横轴源数据。具体效果如图8.6-3所示。

图8.6-3　8.6-1案例图表实现第2步

3．完成第2步之后，图表的整体效果就基本出来了，接下来需要模拟“残糖量”、“酶活”、“生物量”、“溶氧”的纵轴坐标，该过程其实就是将图表现有纵轴坐标刻度多次复制，在横轴上间隔模拟的过程。此案例纵轴坐标最大值锁定为10，主要刻度单位为2，具体的数值变换如图8.6-4左侧表格所示。然后将“Y”所在数据列复制，选择性粘贴到图表，并重复该过程4次。

图8.6-4　8.6-1案例图表实现第3步

4．依次修改第3步添加的4个图表系列SERIES公式中的系列名称和X值为辅助坐标区域的“残糖量”、“酶活”、“生物量”、“溶氧”所在列的数值，如图8.6-5所示。

图8.6-5　8.6-1案例图表实现第4步

5．添加模拟坐标标签，依次使用函数“=原始数据最大值／5*坐标序号”的方式完成“残糖量”、“酶活”、“生物量”、“溶氧”的模拟坐标刻度标签，如图8.6-6左侧表格所示。然后使用“＝”号依次将模拟坐标数据点标签和单元格数值一一对应，如图8.6-6所示。

图8.6-6　8.6-1案例图表实现第5步

6．修饰整理完成后的最终效果如图8.6-7所示。

图8.6-7　8.6-1案例图表最终实现

其他

图8.6-8的案例也是一个多轴图，该图同属于工程类图表，主要用于描述电机、发动机类的多指标性能曲线。此图依旧是一个使用在特殊领域的图表类型，并不适用于日常的办公管理、市场和财务领域。这个案例除数据的归一化方式和模拟坐标的处理稍有不同外，其具体实现和图8.6-1的过程类似。

图8.6-8　另类多轴图表案例

此外，在复杂的统计概率纸中多坐标使用也较为常见，概率纸中多坐标的意义在于：同组数据直接通过查看不同坐标系，即可获取转换后的数值结果，比如威布尔概率纸。

Excel图表中除XY散点和气泡具有两个数值坐标外，其他类型图表系列的数据点均具有强烈的绘制方向和次序限制。在正常状况下，每个坐标点相对应的数据点只出现1次，且数据点保持相等的步长间隔。问题是，当要制作如图8.4-1所示的案例时，这个特点就让人有些沮丧了，这个案例的横轴刻度步长间隔完全不同。

图8.4-1　充分利用时间刻度

案例介绍

图8.4-1的案例为一个财务成本分析，该图表由“变动成本”和“固定成本”两组数据共同组成，每组成本中又包含了若干细分成本，该图是一个堆积矩阵式分层细分图（Mekko Chart）。

名词解释：

■　Mekko Chart

Mekko是“Marimekko”的缩写，来自芬兰语“玛丽的小裙子”，意指图表色彩丰富，也被称为‘Market Map’或宽度可变条形图。Mekko Chart简单而言，就是一个可以将3个维度数据（例如：销售、竞争对手和市场份额）联系在一个二维图形中的图表。该图主要来自于商业咨询机构，因而使该图在市场和财务中被广泛应用。

■　TreeMap

矩阵式树状结构地图（TreeMap）派生自Mekko Chart，TreeMap是由资料结构中的多层树状结构而来，加上空间填充演算绘制，同时亦可结合热力地图（Heatmap）的色彩深度来表示4维度数据。该图是人机界面大师本·施奈德曼（Ben Shneiderman）发明的资料表示法，主要目的就是要在一张图内一目了然整体的状况，由各个元件量的大小决定图示大小，并具有群组功能。该图多应用在基于海量数据和Web的归类可视化，以及数据的交互式展示。详见下图：

资料来源：IBM Many Eyes TreeMap Example

案例分析

对于Excel直角坐标系中的面积类图表，要使图表横轴刻度步长间隔完全不同就显得较为棘手。当然可以采用求最大公倍数的方法，或使用百分比堆积柱形图来完成这个案例。除此之外在直角坐标系中，面积类图表可选的方案仅有面积图一项，但面积图的横轴正常为序列性质，横轴使用时间刻度可使面积图的横轴具有类似XY散点不等距绘制的特点，因为时间刻度可以使同一坐标刻度点上所对应的数据点多次出现，这是该图实现的关键。

案例实现

结合8.4.2节的分析，图8.4-1案例以百分比堆积面积图配合时间刻度来实现，就制作过程而言，实现该图的主要技巧在于数据的整理布局。具体实现方法如下：

1．将原始数据整理布局为如图8.4-2右侧所示的样式［注：此处并未使用函数来整理，完全是使用鼠标搬家］。

图8.4-2　8.4-1案例图表实现第1步

2．选中“细分成本”、“变动成本”、“固定成本”所在数据列，制作堆积面积图，采用数据产生在行，如图8.4-3所示。

图8.4-3　8.4-1案例图表实现第2步

3．在整理的数据表上方增加4个单元格，该单元格为图表横轴刻度数值；然后选中图表中的第一个系列，修改图表系列的SERIES公式，更改X值的引用位置［如图8.4-4所示SERIES公式的高亮部分］；最后修改横轴为“时间刻度”。整体实现效果如图8.4-4所示。

图8.4-4　8.4-1案例图表实现第3步

4．当第3步完成后，图表的基本轮廓已经实现，但横轴的刻度标签和图表完全是两回事，此时需要使用辅助数据在图表中添加XY散点来实现横轴坐标的模拟。在此案例中使用的辅助数据如图8.4-5左侧所示；坐标的刻度线采用了误差线来实现；刻度线标签则使用“＝”号和单元格关联的方法。具体的实现效果如图8.4-5右侧所示。

图8.4-5　8.4-1案例图表实现第4步

5．删除图表右侧的图例项，图表已经完成，此时勾选图表系列的数据标签选项，通过鼠标移动完成如图8.4-6右侧所示的效果。不过此方法标签的位置在Excel 2003中并不易控制，笔者推荐和第4步模拟坐标实现的方法一样，使用XY散点来固定标签位置，具体实现的辅助数据如图8.4-6左侧所示。

图8.4-6　8.4-1案例图表实现第5步

提示

上述第4和第5步，由于Excel2003与Excel2007版本存在细节操作差异，操作需注意：

1. Excel2003中“X轴”和“系列标签”两个系列的X值及误差线设置：需要根据坐标值*100。
2. Excel2007或Excel2010将“X轴”和“系列标签”两个系列置于次坐标系中更佳。
3. Excel2007或Excel2010中，系列的SERIES公式不可使用静态数组，否则无法强制为时间刻度。

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

图8.4-7　8.4-1案例图表最终实现

其他

在Excel时间刻度中，Excel仅支持以天、月、年类型的刻度，对于以秒、小时类型的刻度不支持，此类类型会以文本形式处理，如果强制使用，所有数据会被折叠显示。

图8.4-8左侧案例是一个通过系列在同一坐标系中不断层叠实现的不等宽面积图，右侧案例则是结合了非矩形和矩形的两个维度不等宽面积图，这两个案例的实现和8.4-1案例的实现并无区别，都利用了时间刻度和作图数据表的整理来实现。差别仅在该图使用的图表类型是普通面积图，每个系列的起始坐标均相同。

图8.4-8　时间刻度面积图两例

学习思考

关于Excel 2003的面积类图表系列标签：

图8.4-8中两个案例的系列标签均采用的是XY散点模拟的数据标签，没有直接采用系列的数据标签。因为在Excel图表中，标签的位置受显示比例影响严重，当调整显示比例时标签位置易会发生偏移。

图8.4-9案例是两个维度的不等宽柱形图，该案例的实现和8.4-8在制作方式上完全相同。通常柱形的高度为实际数值，柱形宽度代表其总量占比。

图8.4-9　不等宽柱形图案例

图8.4-10案例则是一个通过时间刻度实现的目标与实绩对比图表，该案例只使用了一个柱形图系列来实现。当然使用该方法来实现并不具有广泛的推广意义，因为制作过程比两个系列的柱形图表实现起来更烦琐，且不易于图表的重复使用。该案例的意义在于：在Excel图表的制作中，时间刻度使用的技巧并不仅限于面积图表。

图8.4-10　使用时间刻度处理一个系列的两层层叠柱形图案例

Excel图表中的大多数选项是针对整个图表或一个系列群组的设定，如果需要针对某一系列进行异于其他系列的设定，往往非常困难。图8.3-1就是这样一个案例。

图8.3-1　一组数据的分拆使用

案例介绍

图8.3-1的案例包含两个折线系列，该图表所引用的数据如图8.3-2所示，源数据中包含：

图8.3-2　原始数据表

1）公式引用结果为错误值；

2）包含有文本或空值。

图8.3-1的案例中：系列1需要将上述类型数值设置为“0”值；系列2则需要将上述类型的数值忽略，不在图表中显示。

提示

关于图8.3-1案例：

1）为了学习的方便，图8.3-1只是一个非常简单的案例，在真实的使用情况下，可能要比这个复杂许多。

2）本案例文档中附带了一个交替出现数值和非数值的案例。

案例分析

对于Excel图表中的点线类图表系列，空值可以使用“不绘制（留空距）”、“以零值代表”和“以内差值替换”选项来处理。该选项属全局性设定，即当选中其中任意选项，所有的点线类图表系列均会遵循；该选项也无法处理非空值，在多数情况下，往往面对非空值的处理要比空值概率更高。

在另外一些情况下，数据的来源可能是基于SQL的查询、数据透视表、外部数据等，无法直接使用NA()函数来强制内差值的替换。

案例实现

基于以上分析，图8.3-1案例的实现采用定义名称配合“以内差值替换”选项或NA()函数来实现，以下的实现过程以图8.3-2所示左侧数据列表为参考来实现。

注：以下方法只是为了契合本节内容，并非是实现图8.3-1案例最简单的做法。

1．直接选中“Test 1”和“Test 2”所在的数据列，制作折线图，如图8.3-3所示。

图8.3-3　8.3-1案例图表实现第1步

2．依次定义两个名称“DataA”和“DataB”，名称函数的结构类似：

=OFFSET（列标头,1,,COUNTIF（数据区域,”>0″）,1）

=OFFSET（列标头,COUNTIF（数据区域,”>0″）+1,1,数据个数-COUNTIF（数据区域,”>0″）,1）

提示

本案例中使用的定义名称采用了工作表级的定义名称。

Excel 2003定义时需在名称前加工作表名称，例如“Test1A!DataA”；Excel 2007/2010则直接选取即可。

3．选中图表的“Test2”系列，在编辑栏中使用小括号将上述定义名称录入到数值位置，之间加逗号进行隔离，需注意两个名称的录入格式，如图8.3-4所示。

图8.3-4　8.3-1案例图表实现第3步

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

图8.3-5　8.3-1案例图表最终实现

示例文档

本书所附案例文档8.3是上述案例的具体实现。

案例文档8.3中包含了针对非空值、空值以及交叉空值3种不同的实现；另外一个案例则完全采用辅助数据列来实现，该案例是最为简便的实现方法。本节未有详细述及的案例，请感兴趣的读者参照案例文档演练学习。

有时，当尝试利用现有数据去制作一个Excel图表时，直观感觉很简单，但实际操作起来却非常茫然，不知从何入手，如图8.2-1所示的案例即是一个这样的实例。

图8.2-1 抛开图表来制图

案例介绍

图8.2-1的案例是进行分类和区分的图表，由多个面积大小相同的矩形色块构成，根据代表数值的不同色块颜色各不相同，该图一般称为“马赛克图”。通常借助颜色的［以及色温的冷暖］梯度变化来表示数值大小的这类图表，统一称为“热力型图表（HeatMap）。”

在该案例中，图表的原始数据基本没有参与图表的绘制，每个数据点的数值仅是图表对应色块的数值标注。

学习思考

关于热力型图表HeatMap

1）此类图表主要有两种常见类型：

a）热力地图；

b）非地图类。

2）非地图类表现形式：

a）可以配合色块面积的大小表达两个维度的数据形态，比如使用气泡、饼图、矩形等；

b）也可仅使用单一色块的大小来表达数值的大小，如图7.5-8所示案例。

注：此类应用相对较少。

案例分析

Excel中可以将数据使用色阶梯度变化的图表，只有曲面图，但生成的曲面图根本无法实现面积大小相同的多个矩形色块。要实现这个效果，图表上的每个小色块均需要有大量数据，现有的数据是每个小色块仅有一个数据；通过变换原始数据，使用曲面图来实现就显得有些得不偿失。

在Excel图表中可以被用来实现面积相同的矩形色块的图表类型是堆积柱形、堆积条形和多系列三维柱形图。堆积柱形、堆积条形需要抛开现有数据，重新模拟作图数据；同理，诸如气泡图、折线、XY散点通过设置数据点填充或标记样式亦可实现，但同样需要重新模拟作图数据。唯有多系列三维柱形图可以使用现有数据直接实现，问题是这些图表均不支持使用色阶梯度变化来标注数据点的颜色。

案例实现

自Excel 2007起始，Excel对于条件格式的设置变得更加灵活和多变，尤其是单一单元格可以支持使用多达64个条件格式。这为图8.2-1案例的实现提供了抛开Excel图表来制图的最佳选择。

注：以下案例必须使用Excel 2007/2010来实现，Excel 2003单一单元格只支持3个条件格式。

1．将原始数据复制并整理为如图8.2-2所示的状态，规划好色阶设置，包括合理设置每个色阶间的数值步长。

图8.2-2　8.2-1案例图表实现第1步

提示

当然可以在原始数据处直接整理，但不推荐，这不是一个好的作图习惯。

1）如果操作失误，将丢失原始数据。

2）保留原始数据可以使图表的制作变得更加明晰。

2．选中数据区域，开始>条件格式>新建规则，在对话框中选择“只为包含以下内容的单元格设置格式”，条件设置为“介于”，依次按照步骤1的色阶规划来设置相应的数值及格式，如图8.2-3所示。

图8.2-3　8.2-1案例图表实现第2步

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

图8.2-4　8.2-1案例图表最终实现

其他

图8.2-5案例是一个热力型的中国陆地地图，该案例和8.2-1案例的实现方式相同，均利用了单元格填色。所不同的是该图使用的数据点非常多，由于Excel 2003限制单个单元格仅能设置3个条件格式，单元格填色采用VBA来完成，同时借助Excel照相机功能来实现。

图8.2-5　热力型中国陆地地图

注：使用该方法实现的热力型地图有一个非常糟糕的问题，图像的分辨率受单元格数量及Excel 2003的256列限制，所以不适合将图拉得太大，这会导致图像变得粗糙。当然使用Excel 2007/2010会有所改观，但由于引用数据增加的缘故，该文档操作起来异常缓慢。如果不需要使用较大的图像，也不失为一个好方法。

图8.2-6案例是一个工作任务分配甘特图，该案例也和8.2-1案例的实现方式相同，通过将原始数据使用数据透视表整合后，使用单元格条件格式来完成。不论Excel版本，使用图表来制作此图，将比使用单元格格式填色要复杂很多倍。

图8.2-6　工作任务分配甘特图

注：只要我们有足够的耐心，及喜好折腾的动手能力，使用类似方法可以实现非常便捷的甘特图模板，比使用Excel图表制作要更灵活、更方便。