谈用Excel做数据分析38658.docx

《谈用Excel做数据分析38658.docx》由会员分享，可在线阅读，更多相关《谈用Excel做数据分析38658.docx（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、所所得样本本数量不不少于所所需数量量。如有问问题可以以发信至至： wwanggbiggbirrdm 与我我交流。用Exceel做数数据分析析移移动平均均2006-11-18 06:01作作者：大大鸟 原原创出处处：天极极软件责责任编辑辑：sttilll-某化工工反应过过程，每每隔2分分钟对系系统测取取一次压压力数据据。由于于反应的的特殊性性，需要要考察每每8分钟钟的压力力平均值值，如果果该压力力平均值值高于115MPPa，则则认为自自属于该该平均值值计算范范围内的的第一个个压力数数据出现现时进入入反应阶阶段，请请使用EExceel给出出反应阶阶段时间间的区间间。点这里里看专题题：用EExcee

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

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

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

20、区间间为8:56-9:110，共共持续114分钟钟。单击其其中一个个单元格格“D66”，可可以看出出它是“BB3-BB6”的平平均值，而而单元格格“E111”则则是“SSQRTT(SUUMXMMY2(B6:B9,D6:D9)/4)”，它它的意思思是B66-B99，D66-D99对应数数据的差差的平方方的平均均值再取取平方根根，也就就是数组组的标准准差。我们已经知知道在EExceel自带带的数据据库中已已有线性性拟合工工具，但但是它还还稍显单单薄，今今天我们们来尝试试使用较较为专业业的拟合合工具来来对此类类数据进进行处理理。 在数据据分析中中，对于于成对成成组数据据的拟合合是经常常遇到的的，涉及

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

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

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

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

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

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

27、lnKK 式式中Q为为产出，LL和K分分别表示示劳动和和资本投投入量，AA表示平平均生产产技术水水平， aa和b分分别是QQ相对于于L和KK的弹性性。使用用下表的的统计数数据，线线性回归归参数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.3

28、792.31019923298.71961.2792.631119935498.352156.7865.521219947684.362448.8906.4813199595052511.91152.3414199611579.152992.31610.8615199712462.572804.61773.3816199813740.692778.91875.8817199915151.462765.72066.1918200016780.962740.92255.0919200120009.82872.82690.7620回归值：210.857762.437547-16.212220.170

29、320.48303692.66302230.979730.1648699#N/A24362.48815#N/A2519.70640.4077314#N/A1、定义名名称：将将A2:A199定义为为Y，将将B2:D199定义为为X2、在A221中输输入公式式：=LLINEEST(LN(Y),LN(X),TRUUE,TTRUEE)3、将返回回值以数数组形式式显示：选中AA21:C255，按FF2，再再按Cttrl + SShifft + Ennterr 回归计计算得 lnYY = -166.2112 + 2.43775 llnL + 00.85578 lnKK三、返回回值的其其它操作作如果只只想要

30、返返回值中中的指定定项，则则可以iindeex函数数。、索索引取值值函数IINDEEX.语语法格式式：INNDEXX（单元元格区域域或数组组常量，行行序号，列列序号）；功能：使用索索引从单单元格区区域或数数组中选选取值。可可用该函函数在LLINEEST函函数返回回系数序序列数组组表中根根据所需需数据所所处的行行列位置置定位选选取。、四舍舍五入函函数ROOUNDD.语法法格式：ROUUND（数数字，小小数位数数）；功功能：将将数字四四舍五入入到指定定的小数数位数。由由于LIINESST函数数的返回回值为66位小数数，用此此函数指指定保留留的小数数位数。 在在需要的的位置输输入：=ROUUND（IINDEEX（LLINEEST（YYX，TTRUEE，TRRUE），11，4），22）。iindeex的后后两个参参数是指指向liinesst返回回的数组组的行列列号。