首页 > Excel专区 > Excel技巧 >

excel 图表数据归一化[擒贼擒王]

Excel技巧 2023-01-08

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 另类多轴图表案例

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


Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.