Excel数据分析（加载宏）.pdf

Excel数据分析（加载宏） ⽤Excel做数据分析——回归分析 实例某溶液浓度正⽐对应于⾊谱仪器中的峰⾯积，现欲建⽴不同浓度下对应峰⾯积的标准曲线以供测试未知样品的实际浓度。 已知8组对应数据，建⽴标准曲线，并且对此曲线进⾏评价，给出残差等分析数据。 这是⼀个很典型的线性拟合问题，⼿⼯计算就是采⽤最⼩⼆乘法求出拟合直线的待定参数，同时可以得出R的值，也就是相关 系数的⼤⼩。在Excel中，可以采⽤先绘图再添加趋势线的⽅法完成前两步的要求。 选择成对的数据列，将它们使⽤“X、Y散点图”制成散点图。 在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，可以得到拟合的直线。 由图中可知，拟合的直线是y=15620x+6606.1，R2的值为0.9994。 因为R2 >0.99，所以这是⼀个线性特征⾮常明显的实验模型，即说明拟合直线能够以⼤于99.99%地解释、涵盖了实测数据， 具有很好的⼀般性，可以作为标准⼯作曲线⽤于其他未知浓度溶液的测量。 为了进⼀步使⽤更多的指标来描述这⼀个模型，我们使⽤数据分析中的“回归”⼯具来详细分析这组数据。 在选项卡中显然详细多了，注意选择X、Y对应的数据列。“常数为零”就是指明该模型是严格的正⽐例模型，本例确实是这样， 因为在浓度为零时相应峰⾯积肯定为零。先前得出的回归⽅程虽然拟合程度相当⾼，但是在x=0时，仍然有对应的数值，这显 然是⼀个可笑的结论。所以我们选择“常数为零”。 “回归”⼯具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。 在线性拟合图中可以看到，不但有根据要求⽣成的数据点，⽽且还有经过拟和处理的预测数据点，拟合直线的参数会在数据表 格中详细显⽰。本实例旨在提供更多信息以起到抛砖 引⽟的作⽤，由于涉及到过多的专业术语，请各位读者根据实际，在具体使⽤中另⾏参考各项参数，此不再对更多细节作进⼀ 步解释。 残差图是有关于世纪之与预测值之间差距的图表，如果残差图中的散点在中州上下两侧零乱分布，那么拟合直线就是合理的， 否则就需要重新处理。 更多的信息在⽣成的表格中，详细的参数项⽬完全可以满⾜回归分析的各项要求。下图提供的是拟合直线的得回归分析中⽅ 差、标准差等各项信息。 ⽤Excel做数据分析——移动平均 某化⼯反应过程，每隔2分钟对系统测取⼀次压⼒数据。由于反应的特殊性，需要考察每8分钟的压⼒平均值，如果该压⼒平 均值⾼于15MPa，则认为⾃属于该平均值计算范围内的第⼀个压⼒数据出现时进⼊反应阶段，请使⽤Excel给出反应阶段时间 的区间。 点这⾥看专题：⽤Excel完成专业化数据统计、分析⼯作移动平均就是对⼀系列变化的数据按照指定的数据数量依次求取平 均，并以此作为数据变化的趋势供分析⼈员参考。移动平均在⽣活中也不乏见，⽓象意义上的四季界定就是移动平均最好的应 ⽤。 注:本功能需要使⽤Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“⼯具”-“加载宏”，在安装光盘⽀持下加 载“分析数据库”。加载成功后，可以在“⼯具”下拉菜单中看到“数据分析”选项。 操作步骤 1.打开原始数据表格，制作本实例的原始数据要求单列，请确认数据的类型。本实例为压⼒随时间变化成对数据，在数据分析 时仅采⽤压⼒数据列。 需要注意的是，因为平均值的求取需要⼀定的数据量，那么就要求原始数据量不少于求取平均值的个数，在Excel中规定数据 量不少于4。 2.选择“⼯具”-“数据分析”-“直⽅图”后，出现属性设置框，依次选择: 输⼊区域:原始数据区域;如果有数据标签可以选择“标志位于第⼀⾏”; 输出区域:移动平均数值显⽰区域; 间隔:指定使⽤⼏组数据来得出平均值; 图表输出;原始数据和移动平均数值会以图表的形式来显⽰，以供⽐较; 标准误差:实际数据与预测数据(移动平均数据)的标准差，⽤以显⽰预测与实际值的差距。数字越⼩则表明预测情况越好。 3.输⼊完毕后，则可⽴即⽣成相应的数据和图表。 从⽣成的图表上可以看出很多信息。 根据要求，⽣成的移动平均数值在9:02时已经达到了15.55MPa，也就是说，包含本次数据在内的四个数据前就已经达到了 15MPa，那么说明在8分钟前，也就是8:56时，系统进⼊反应阶段;采⽤同样的分析⽅法可以知道，反映阶段结束于9:10，反应 阶段时间区间为8:56-9:10，共持续14分钟。 单击其中⼀个单元格“D6”，可以看出它是“B3-B6”的平均值，⽽单元格“E11”则是“SQRT(SUMXMY2(B6:B9,D6:D9)/4)”，它的 意思是B6-B9，D6-D9对应数据的差的平 ⽅的平均值再取平⽅根