excel 图表数据归一化［擒贼擒王］

Excel技巧 2021-11-25 12:25:52

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次。

数据归一化［擒贼擒王］