谈用Excel做数据分析

1、用Exccel做做数据分分析回归分分析20066-122-044 144:022作者：大鸟 原创出出处：天天极软件件责任编编辑：sstilll-在数数据分析析中，对对于成对对成组数数据的拟拟合是经经常遇到到的，涉涉及到的的任务有有线性描描述，趋趋势预测测和残差差分析等等等。很很多专业业读者遇遇见此类类问题时时往往寻寻求专业业软件，比如在在化工中中经常用用到的OOriggin和和数学中中常见的的MATTLABB等等。它们虽虽很专业业，但其其实使用用Exccel就就完全够够用了。我们已已经知道道在Exxcell自带的的数据库库中已有有线性拟拟合工具具，但是是它还稍稍显单薄薄，今天天我们来来尝试使使

2、用较为为专业的的拟合工工具来对对此类数数据进行行处理。 HYPERLINK 点这里看看专题：用Exxcell完成专专业化数数据统计计、分析析工作注：本功能能需要使使用Exxcell扩展功功能，如如果您的的Exccel尚尚未安装装数据分分析，请请依次选选择“工工具”-“加载载宏”，在安装装光盘支支持下加加载“分分析数据据库”。加载成成功后，可以在在“工具具”下拉拉菜单中中看到“数据分分析”选选项实例 某某溶液浓浓度正比比对应于于色谱仪仪器中的的峰面积积，现欲欲建立不不同浓度度下对应应峰面积积的标准准曲线以以供测试试未知样样品的实实际浓度度。已知知8组对对应数据据，建立立标准曲曲线，并并且对此此曲

3、线进进行评价价，给出出残差等等分析数数据。这是是一个很很典型的的线性拟拟合问题题，手工工计算就就是采用用最小二二乘法求求出拟合合直线的的待定参参数，同同时可以以得出RR的值，也就是是相关系系数的大大小。在在Exccel中中，可以以采用先先绘图再再添加趋趋势线的的方法完完成前两两步的要要求。选择择成对的的数据列列，将它它们使用用“X、Y散点点图”制制成散点点图。在数数据点上上单击右右键，选选择“添添加趋势势线”-“线性性”，并并在选项项标签中中要求给给出公式式和相关关系数等等，可以以得到拟拟合的直直线。由图图中可知知，拟合合的直线线是y=156620 xx+66606.1，RR2的值值为0.99

4、994。因为为R2 0.99，所以这这是一个个线性特特征非常常明显的的实验模模型，即即说明拟拟合直线线能够以以大于999.999%地地解释、涵盖了了实测数数据，具具有很好好的一般般性，可可以作为为标准工工作曲线线用于其其他未知知浓度溶溶液的测测量。为了了进一步步使用更更多的指指标来描描述这一一个模型型，我们们使用数数据分析析中的“回归”工具来来详细分分析这组组数据。在选选项卡中中显然详详细多了了，注意意选择XX、Y对对应的数数据列。“常数数为零”就是指指明该模模型是严严格的正正比例模模型，本本例确实实是这样样，因为为在浓度度为零时时相应峰峰面积肯肯定为零零。先前前得出的的回归方方程虽然然拟合程

5、程度相当当高，但但是在xx=0时时，仍然然有对应应的数值值，这显显然是一一个可笑笑的结论论。所以以我们选选择“常常数为零零”。“回回归”工工具为我我们提供供了三张张图，分分别是残残差图、线性拟拟合图和和正态概概率图。重点来来看残差差图和线线性拟合合图。在线线性拟合合图中可可以看到到，不但但有根据据要求生生成的数数据点，而且还还有经过过拟和处处理的预预测数据据点，拟拟合直线线的参数数会在数数据表格格中详细细显示。本实例例旨在提提供更多多信息以以起到抛抛砖引玉玉的作用用，由于于涉及到到过多的的专业术术语，请请各位读读者根据据实际，在具体体使用中中另行参参考各项项参数，此不再再对更多多细节作作进一步

6、步解释。残差差图是有有关于世世纪之与与预测值值之间差差距的图图表，如如果残差差图中的的散点在在中州上上下两侧侧零乱分分布，那那么拟合合直线就就是合理理的，否否则就需需要重新新处理。更多多的信息息在生成成的表格格中，详详细的参参数项目目完全可可以满足足回归分分析的各各项要求求。下图图提供的的是拟合合直线的的得回归归分析中中方差、标准差差等各项项信息。用Exccel做做数据分分析相关系系数与协协方差20066-111-155 055:244作者：大鸟 原创出出处：天天极软件件责任编编辑：sstilll-化学学合成实实验中经经常需要要考察压压力随温温度的变变化情况况。某次次实验在在两个不不同的反反应

7、器中中进行同同一条件件下实验验得到两两组温度度与压力力相关数数据，试试分析它它们与温温度的关关联关系系，并对对在不同同反应器器内进行行同一条条件下反反应的可可靠性给给出依据据。 HYPERLINK 点这里看看专题：用Exxcell完成专专业化数数据统计计、分析析工作相关关系数是是描述两两个测量量值变量量之间的的离散程程度的指指标。用用于判断断两个测测量值变变量的变变化是否否相关，即，一一个变量量的较大大值是否否与另一一个变量量的较大大值相关关联(正正相关)；或者者一个变变量的较较小值是是否与另另一个变变量的较较大值相相关联(负相关关)；还还是两个个变量中中的值互互不关联联(相关关系数近近似于零

8、零)。设设(X,Y)为为二元随随机变量量，那么么:为随随机变量量X与YY的相关关系数。p是度度量随机机变量XX与Y之之间线性性相关密密切程度度的数字字特征。注:本功能能需要使使用Exxcell扩展功功能，如如果您的的Exccel尚尚未安装装数据分分析，请请依次选选择“工工具”-“加载载宏”，在安装装光盘中中加载“分析数数据库”。加载载成功后后，可以以在“工工具”下下拉菜单单中看到到“数据据分析”选项。操作作步骤1. 打开开原始数数据表格格，制作作本实例例的原始始数据需需要满足足两组或或两组以以上的数数据，结结果将给给出其中中任意两两项的相相关系数数。2. 选择择“工具具”-“数据分分析”-“描

9、述述统计”后，出出现属性性设置框框，依次次选择:输入入区域:选择数数据区域域，注意意需要满满足至少少两组数数据。如如果有数数据标志志，注意意同时勾勾选下方方“标志志位于第第一行”；分组组方式:指示输输入区域域中的数数据是按按行还是是按列考考虑，请请根据原原数据格格式选择择；输出出区域可可以选择择本表、新工作作表组或或是新工工作簿；3.点击“确定”即可看看到生成成的报表表。可以以看到，在相应应区域生生成了一一个33的矩矩阵，数数据项目目的交叉叉处就是是其相关关系数。显然，数据与与本身是是完全相相关的，相关系系数在对对角线上上显示为为1；两两组数据据间在矩矩阵上有有两个位位置，它它们是相相同的，故

10、右上上侧重复复部分不不显示数数据。左左下侧相相应位置置分别是是温度与与压力AA、B和和两组压压力数据据间的相相关系数数。从数数据统计计结论可可以看出出，温度度与压力力A、BB的相关关性分别别达到了了0.995和00.944，这说说明它们们呈现良良好的正正相关性性，而两两组压力力数据间间的相关关性达到到了0.9988，这说说明在不不同反应应器内的的相同条条件下反反应一致致性很好好，可以以忽略因因为更换换反应器器造成的的系统误误差。协方方差的统统计与相相关系数数的活的的方法相相似，统统计结果果同样返返回一个个输出表表和一个个矩阵，分别表表示每对对测量值值变量之之间的相相关系数数和协方方差。不不同之

11、处处在于相相关系数数的取值值在 -1 和和 +11 之间间，而协协方差没没有限定定的取值值范围。相关系系数和协协方差都都是描述述两个变变量离散散程度的的指标。用Exccel做做数据分分析抽样分分析工具具20066-111-177 055:299作者：大鸟 原创出出处：天天极软件件责任编编辑：sstilll-省教教育厅派派专家组组进行某某校检查查学生考考试试卷卷，专家家组拟对对总体进进行抽样样调查，对学校校某班的的全体同同学随机机抽取225名作作为调查查样本。为了保保证结果果的非人人为性，采用EExceel帮助助专家组组做出抽抽查的结结果。 HYPERLINK 点这里看看专题：用Exxcell完

12、成专专业化数数据统计计、分析析工作抽样样分析工工具以数数据源区区域为总总体，从从而为其其创建一一个样本本。当总总体太大大而不能能进行处处理或绘绘制时，可以选选用具有有代表性性的样本本。如果果确认数数据源区区域中的的数据是是周期性性的，还还可以对对一个周周期中特特定时间间段中的的数值进进行采样样。也可可以采用用随机抽抽样，满满足用户户保证抽抽样的代代表性的的要求。注：本功能能需要使使用Exxcell扩展功功能，如如果您的的Exccel尚尚未安装装数据分分析，请请依次选选择“工工具”-“加载载宏”，在安装装光盘的的支持下下加载“数据分分析库”。加载载成功后后，可以以在工具具的下拉拉菜单中中看到“数

13、据分分析”选选项。操作作步骤：1. 打开开原始数数据表格格，制作作本实例例的原始始数据无无特殊要要求，只只要满足足行或列列中为同同一属性性数值即即可。实实例中显显示的是是学生学学号。2. 选择择“工具具”“数据分分析”“抽样样”后，出现对对话框，依次选选择：输入入区域：把原始始总体数数据放在在此区域域中，数数据类型型不限，数值型型或者文文本型均均可；抽样样方法：有间隔隔和随机机两种。间隔抽抽样需要要输入周周期间隔隔，输入入区域中中位于间间隔点处处的数值值以及此此后每一一个间隔隔点处的的数值将将被复制制到输出出列中。当到达达输入区区域的末末尾时，抽样将将停止。(在本本例题中中没有采采用)；随机抽

14、抽样是指指直接输输入样本本数，电电脑自行行进行抽抽样，不不用受间间隔的规规律限制制；样本本数：在在此输入入需要在在输出列列中显示示需要抽抽取总体体中数据据的个数数。每个个数值是是从输入入区域中中的随机机位置上上抽取出出来的，请注意意：任何何数值都都可以被被多次抽抽取!所所以抽样样所得数数据实际际上会有有可能小小于所需需数量。本文末末尾给出出了一种种处理方方法；输出出区域：在此输输入对输输出表左左上角单单元格的的引用。所有数数据均将将写在该该单元格格下方的的单列里里。如果果选择的的是“周周期”，则输出出表中数数值的个个数等于于输入区区域中数数值的个个数除以以“间隔隔”。如如果选择择的是“随机”，

15、则输输出表中中数值的的个数等等于“样样本数”；3.然后单单击确定定就可以以显示结结果了(这是电电脑自行行随机抽抽样的结结果)。 HYPERLINK 原始数据据文档在在这里下下载 HYPERLINK 操作结结果文档档在这里里下载需要要说明的的情况：由于于随机抽抽样时总总体中的的每个数数据都可可以被多多次抽取取，所以以在样本本中的数数据一般般都会有有重复现现象，解解决此问问题有待待于程序序的完善善。可以以使用“筛选”功能对对所得数数据进行行筛选。选中中样本数数据列，依次执执行“数数据”-“筛选选”-“高级筛筛选”，如下图图所示。最后后的样本本结果如如下图所所示，请请您根据据经验适适当调整整在数据据

16、样本选选取时的的数量设设置，以以使最终终所得样样本数量量不少于于所需数数量。如有有问题可可以发信信至： wanngbiigbiird1633.coom 与与我交流流。用Exccel做做数据分分析移动平平均20066-111-188 066:011作者：大鸟 原创出出处：天天极软件件责任编编辑：sstilll-某化化工反应应过程，每隔22分钟对对系统测测取一次次压力数数据。由由于反应应的特殊殊性，需需要考察察每8分分钟的压压力平均均值，如如果该压压力平均均值高于于15MMPa，则认为为自属于于该平均均值计算算范围内内的第一一个压力力数据出出现时进进入反应应阶段，请使用用Exccel给给出反应应阶

17、段时时间的区区间。 HYPERLINK 点这里看看专题：用Exxcell完成专专业化数数据统计计、分析析工作移动动平均就就是对一一系列变变化的数数据按照照指定的的数据数数量依次次求取平平均，并并以此作作为数据据变化的的趋势供供分析人人员参考考。移动动平均在在生活中中也不乏乏见，气气象意义义上的四四季界定定就是移移动平均均最好的的应用。注:本功能能需要使使用Exxcell扩展功功能，如如果您的的Exccel尚尚未安装装数据分分析，请请依次选选择“工工具”-“加载载宏”，在安装装光盘支支持下加加载“分分析数据据库”。加载成成功后，可以在在“工具具”下拉拉菜单中中看到“数据分分析”选选项。操作作步骤

18、1.打开原原始数据据表格，制作本本实例的的原始数数据要求求单列，请确认认数据的的类型。本实例例为压力力随时间间变化成成对数据据，在数数据分析析时仅采采用压力力数据列列。需要要注意的的是，因因为平均均值的求求取需要要一定的的数据量量，那么么就要求求原始数数据量不不少于求求取平均均值的个个数，在在Exccel中中规定数数据量不不少于44。2.选择“工具”-“数数据分析析”-“直方图图”后，出现属属性设置置框，依依次选择择:输入入区域:原始数数据区域域;如果果有数据据标签可可以选择择“标志志位于第第一行”;输出出区域:移动平平均数值值显示区区域;间隔隔:指定定使用几几组数据据来得出出平均值值;图表表

19、输出;原始数数据和移移动平均均数值会会以图表表的形式式来显示示，以供供比较;标准准误差:实际数数据与预预测数据据(移动动平均数数据)的的标准差差，用以以显示预预测与实实际值的的差距。数字越越小则表表明预测测情况越越好。3.输入完完毕后，则可立立即生成成相应的的数据和和图表。从生生成的图图表上可可以看出出很多信信息。根据据要求，生成的的移动平平均数值值在9:02时时已经达达到了115.555MPPa，也也就是说说，包含含本次数数据在内内的四个个数据前前就已经经达到了了15MMPa，那么说说明在88分钟前前，也就就是8:56时时，系统统进入反反应阶段段;采用用同样的的分析方方法可以以知道，反映阶阶

20、段结束束于9:10，反应阶阶段时间间区间为为8:556-99:100，共持持续144分钟。单击击其中一一个单元元格“DD6”，可以看看出它是是“B33-B66”的平平均值，而单元元格“EE11”则是“SQRRT(SSUMXXMY22(B66:B99,D66:D99)/44)”，它的意意思是BB6-BB9，DD6-DD9对应应数据的的差的平平方的平平均值再再取平方方根，也也就是数数组的标标准差。我们已经经知道在在Exccel自自带的数数据库中中已有线线性拟合合工具，但是它它还稍显显单薄，今天我我们来尝尝试使用用较为专专业的拟拟合工具具来对此此类数据据进行处处理。 在数数据分析析中，对对于成对对成

21、组数数据的拟拟合是经经常遇到到的，涉涉及到的的任务有有线性描描述，趋趋势预测测和残差差分析等等等。很很多专业业读者遇遇见此类类问题时时往往寻寻求专业业软件，比如在在化工中中经常用用到的OOriggin和和数学中中常见的的MATTLABB等等。它们虽虽很专业业，但其其实使用用Exccel就就完全够够用了。我们已已经知道道在Exxcell自带的的数据库库中已有有线性拟拟合工具具，但是是它还稍稍显单薄薄，今天天我们来来尝试使使用较为为专业的的拟合工工具来对对此类数数据进行行处理。 注：本功能能需要使使用Exxcell扩展功功能，如如果您的的Exccel尚尚未安装装数据分分析，请请依次选选择“工工具”

22、-“加载载宏”，在安装装光盘支支持下加加载“分分析数据据库”。加载成成功后，可以在在“工具具”下拉拉菜单中中看到“数据分分析”选选项 实例例 某溶溶液浓度度正比对对应于色色谱仪器器中的峰峰面积，现欲建建立不同同浓度下下对应峰峰面积的的标准曲曲线以供供测试未未知样品品的实际际浓度。已知88组对应应数据，建立标标准曲线线，并且且对此曲曲线进行行评价，给出残残差等分分析数据据。 这是是一个很很典型的的线性拟拟合问题题，手工工计算就就是采用用最小二二乘法求求出拟合合直线的的待定参参数，同同时可以以得出RR的值，也就是是相关系系数的大大小。在在Exccel中中，可以以采用先先绘图再再添加趋趋势线的的方法

23、完完成前两两步的要要求。 选择择成对的的数据列列，将它它们使用用“X、Y散点点图”制制成散点点图。 在数据点点上单击击右键，选择“添加趋趋势线”-“线线性”，并在选选项标签签中要求求给出公公式和相相关系数数等，可可以得到到拟合的的直线。 由图图中可知知，拟合合的直线线是y=156620 xx+66606.1，RR2的值值为0.99994。 因为为R2 0.99，所以这这是一个个线性特特征非常常明显的的实验模模型，即即说明拟拟合直线线能够以以大于999.999%地地解释、涵盖了了实测数数据，具具有很好好的一般般性，可可以作为为标准工工作曲线线用于其其他未知知浓度溶溶液的测测量。 为了了进一步步使

24、用更更多的指指标来描描述这一一个模型型，我们们使用数数据分析析中的“回归”工具来来详细分分析这组组数据。 在选选项卡中中显然详详细多了了，注意意选择XX、Y对对应的数数据列。“常数数为零”就是指指明该模模型是严严格的正正比例模模型，本本例确实实是这样样，因为为在浓度度为零时时相应峰峰面积肯肯定为零零。先前前得出的的回归方方程虽然然拟合程程度相当当高，但但是在xx=0时时，仍然然有对应应的数值值，这显显然是一一个可笑笑的结论论。所以以我们选选择“常常数为零零”。 “回回归”工工具为我我们提供供了三张张图，分分别是残残差图、线性拟拟合图和和正态概概率图。重点来来看残差差图和线线性拟合合图。 HYP

25、ERLINK t _blank 点这里查查看更多多Exccel教教程在线性性拟合图图中可以以看到，不但有有根据要要求生成成的数据据点，而而且还有有经过拟拟和处理理的预测测数据点点，拟合合直线的的参数会会在数据据表格中中详细显显示。本本实例旨旨在提供供更多信信息以起起到抛砖砖引玉的的作用，由于涉涉及到过过多的专专业术语语，请各各位读者者根据实实际，在在具体使使用中另另行参考考各项参参数，此此不再对对更多细细节作进进一步解解释。 残差差图是有有关于世世纪之与与预测值值之间差差距的图图表，如如果残差差图中的的散点在在中州上上下两侧侧零乱分分布，那那么拟合合直线就就是合理理的，否否则就需需要重新新处理

26、。 更多多的信息息在生成成的表格格中，详详细的参参数项目目完全可可以满足足回归分分析的各各项要求求。下图图提供的的是拟合合直线的的得回归归分析中中方差、标准差差等各项项信息。 EXCEEL的话话，刚看看了一下下，是要要先加载载宏：在在工具菜菜单下点点加载宏宏，选择择分析工工具库（在该复复选框打打钩，点点确定）。再点点工具菜菜单发现现多了“数据分分析”这这一项，点这个个，自行行选择分分析工具具就好了了，有方方差分析析、回归归等等。还还可以直直接作图图。用EXCCEL函函数进行行多元线线性回归归分析示例 计算柯柯布-道道格拉斯斯生产 HYPERLINK javascript:; 函函数（CCobb

27、b-Doougllas ）： lnQQ = lnAA + a llnL + bb lnnK 式中QQ为产出出，L和和K分别别表示劳劳动和资资本投入入量，AA表示平平均生产产技术水水平， aa和b分分别是QQ相对于于L和KK的弹性性。使用用下表的的统计数数据， HYPERLINK javascript:; 线线性回归归参数AA, aa, bb。ABCD1年份产出投入万人投入亿元21984733.691531.2351.331985985.11701.4376.4419861330.81800.6459.3519871603.611852.5501.6619881959.421899.4565.3719892169.481773.4675.28819901947.581716.7717.5919912284.781783.3792.31019923298.71961.2792.631119935498.352156.7865.521219947684.36244