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-28.6-1案例图表实现第1步

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

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

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

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

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

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

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

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

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

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

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

其他

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

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

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