下载
加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 用Excel做数据分析回归分析

用Excel做数据分析回归分析.doc

用Excel做数据分析回归分析

Candy生姜
2017-10-07 0人阅读 举报 0 0 暂无简介

简介:本文档为《用Excel做数据分析回归分析doc》,可适用于高等教育领域

用Excel做数据分析回归分析用Excel做数据分析回归分析:作者:大鸟原创出处:天极软件责任编辑:still在数据分析中对于成对成组数据的拟合是经常遇到的涉及到的任务有线性描述趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具但是它还稍显单薄今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。点这里看专题:用Excel完成专业化数据统计、分析工作注:本功能需要使用Excel扩展功能如果您的Excel尚未安装数据分析请依次选择“工具”“加载宏”在安装光盘支持下加载“分析数据库”。加载成功后可以在“工具”下拉菜单中看到“数据分析”选项实例某溶液浓度正比对应于色谱仪器中的峰面积现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。已知组对应数据建立标准曲线并且对此曲线进行评价给出残差等分析数据。这是一个很典型的线性拟合问题手工计算就是采用最小二乘法求出拟合直线的待定参数同时可以得出R的值也就是相关系数的大小。在Excel中可以采用先绘图再添加趋势线的方法完成前两步的要求。选择成对的数据列将它们使用“X、Y散点图”制成散点图。在数据点上单击右键选择“添加趋势线”“线性”并在选项标签中要求给出公式和相关系数等可以得到拟合的直线。由图中可知拟合的直线是y=xR的值为。因为R>所以这是一个线性特征非常明显的实验模型即说明拟合直线能够以大于地解释、涵盖了实测数据具有很好的一般性可以作为标准工作曲线用于其他未知浓度溶液的测量。为了进一步使用更多的指标来描述这一个模型我们使用数据分析中的“回归”工具来详细分析这组数据。在选项卡中显然详细多了注意选择X、Y对应的数据列。“常数为零”就是指明该模型是严格的正比例模型本例确实是这样因为在浓度为零时相应峰面积肯定为零。先前得出的回归方程虽然拟合程度相当高但是在x=时仍然有对应的数值这显然是一个可笑的结论。所以我们选择“常数为零”。“回归”工具为我们提供了三张图分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。在线性拟合图中可以看到不但有根据要求生成的数据点而且还有经过拟和处理的预测数据点拟合直线的参数会在数据表格中详细显示。本实例旨在提供更多信息以起到抛砖引玉的作用由于涉及到过多的专业术语请各位读者根据实际在具体使用中另行参考各项参数此不再对更多细节作进一步解释。残差图是有关于世纪之与预测值之间差距的图表如果残差图中的散点在中州上下两侧零乱分布那么拟合直线就是合理的否则就需要重新处理。更多的信息在生成的表格中详细的参数项目完全可以满足回归分析的各项要求。下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。用Excel做数据分析相关系数与协方差:作者:大鸟原创出处:天极软件责任编辑:still化学合成实验中经常需要考察压力随温度的变化情况。某次实验在两个不同的反应器中进行同一条件下实验得到两组温度与压力相关数据试分析它们与温度的关联关系并对在不同反应器内进行同一条件下反应的可靠性给出依据。点这里看专题:用Excel完成专业化数据统计、分析工作相关系数是描述两个测量值变量之间的离散程度的指标。用于判断两个测量值变量的变化是否相关即一个变量的较大值是否与另一个变量的较大值相关联(正相关)或者一个变量的较小值是否与另一个变量的较大值相关联(负相关)还是两个变量中的值互不关联(相关系数近似于零)。设(X,Y)为二元随机变量那么:为随机变量X与Y的相关系数。p是度量随机变量X与Y之间线性相关密切程度的数字特征。注:本功能需要使用Excel扩展功能如果您的Excel尚未安装数据分析请依次选择“工具”“加载宏”在安装光盘中加载“分析数据库”。加载成功后可以在“工具”下拉菜单中看到“数据分析”选项。操作步骤打开原始数据表格制作本实例的原始数据需要满足两组或两组以上的数据结果将给出其中任意两项的相关系数。选择“工具”“数据分析”“描述统计”后出现属性设置框依次选择:输入区域:选择数据区域注意需要满足至少两组数据。如果有数据标志注意同时勾选下方“标志位于第一行”分组方式:指示输入区域中的数据是按行还是按列考虑请根据原数据格式选择输出区域可以选择本表、新工作表组或是新工作簿点击“确定”即可看到生成的报表。可以看到在相应区域生成了一个×的矩阵数据项目的交叉处就是其相关系数。显然数据与本身是完全相关的相关系数在对角线上显示为两组数据间在矩阵上有两个位置它们是相同的故右上侧重复部分不显示数据。左下侧相应位置分别是温度与压力A、B和两组压力数据间的相关系数。从数据统计结论可以看出温度与压力A、B的相关性分别达到了和这说明它们呈现良好的正相关性而两组压力数据间的相关性达到了这说明在不同反应器内的相同条件下反应一致性很好可以忽略因为更换反应器造成的系统误差。协方差的统计与相关系数的活的方法相似统计结果同样返回一个输出表和一个矩阵分别表示每对测量值变量之间的相关系数和协方差。不同之处在于相关系数的取值在和之间而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程度的指标。用Excel做数据分析抽样分析工具:作者:大鸟原创出处:天极软件责任编辑:still省教育厅派专家组进行某校检查学生考试试卷专家组拟对总体进行抽样调查对学校某班的全体同学随机抽取名作为调查样本。为了保证结果的非人为性采用Excel帮助专家组做出抽查的结果。点这里看专题:用Excel完成专业化数据统计、分析工作抽样分析工具以数据源区域为总体从而为其创建一个样本。当总体太大而不能进行处理或绘制时可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的还可以对一个周期中特定时间段中的数值进行采样。也可以采用随机抽样满足用户保证抽样的代表性的要求。注:本功能需要使用Excel扩展功能如果您的Excel尚未安装数据分析请依次选择“工具”“加载宏”在安装光盘的支持下加载“数据分析库”。加载成功后可以在工具的下拉菜单中看到“数据分析”选项。操作步骤:打开原始数据表格制作本实例的原始数据无特殊要求只要满足行或列中为同一属性数值即可。实例中显示的是学生学号。选择“工具”“数据分析”“抽样”后出现对话框依次选择:输入区域:把原始总体数据放在此区域中数据类型不限数值型或者文本型均可抽样方法:有间隔和随机两种。间隔抽样需要输入周期间隔输入区域中位于间隔点处的数值以及此后每一个间隔点处的数值将被复制到输出列中。当到达输入区域的末尾时抽样将停止。(在本例题中没有采用)随机抽样是指直接输入样本数电脑自行进行抽样不用受间隔的规律限制样本数:在此输入需要在输出列中显示需要抽取总体中数据的个数。每个数值是从输入区域中的随机位置上抽取出来的请注意:任何数值都可以被多次抽取!所以抽样所得数据实际上会有可能小于所需数量。本文末尾给出了一种处理方法输出区域:在此输入对输出表左上角单元格的引用。所有数据均将写在该单元格下方的单列里。如果选择的是“周期”则输出表中数值的个数等于输入区域中数值的个数除以“间隔”。如果选择的是“随机”则输出表中数值的个数等于“样本数”然后单击确定就可以显示结果了(这是电脑自行随机抽样的结果)。原始数据文档在这里下载>>操作结果文档在这里下载>>需要说明的情况:由于随机抽样时总体中的每个数据都可以被多次抽取所以在样本中的数据一般都会有重复现象解决此问题有待于程序的完善。可以使用“筛选”功能对所得数据进行筛选。选中样本数据列依次执行“数据”“筛选”“高级筛选”如下图所示。最后的样本结果如下图所示请您根据经验适当调整在数据样本选取时的数量设置以使最终所得样本数量不少于所需数量。如有问题可以发信至:wangbigbirdcom与我交流。用Excel做数据分析移动平均:作者:大鸟原创出处:天极软件责任编辑:still某化工反应过程每隔分钟对系统测取一次压力数据。由于反应的特殊性需要考察每分钟的压力平均值如果该压力平均值高于MPa则认为自属于该平均值计算范围内的第一个压力数据出现时进入反应阶段请使用Excel给出反应阶段时间的区间。点这里看专题:用Excel完成专业化数据统计、分析工作移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均并以此作为数据变化的趋势供分析人员参考。移动平均在生活中也不乏见气象意义上的四季界定就是移动平均最好的应用。注:本功能需要使用Excel扩展功能如果您的Excel尚未安装数据分析请依次选择“工具”“加载宏”在安装光盘支持下加载“分析数据库”。加载成功后可以在“工具”下拉菜单中看到“数据分析”选项。操作步骤打开原始数据表格制作本实例的原始数据要求单列请确认数据的类型。本实例为压力随时间变化成对数据在数据分析时仅采用压力数据列。需要注意的是因为平均值的求取需要一定的数据量那么就要求原始数据量不少于求取平均值的个数在Excel中规定数据量不少于。选择“工具”“数据分析”“直方图”后出现属性设置框依次选择:输入区域:原始数据区域如果有数据标签可以选择“标志位于第一行”输出区域:移动平均数值显示区域间隔:指定使用几组数据来得出平均值图表输出原始数据和移动平均数值会以图表的形式来显示以供比较标准误差:实际数据与预测数据(移动平均数据)的标准差用以显示预测与实际值的差距。数字越小则表明预测情况越好。输入完毕后则可立即生成相应的数据和图表。从生成的图表上可以看出很多信息。根据要求生成的移动平均数值在:时已经达到了MPa也就是说包含本次数据在内的四个数据前就已经达到了MPa那么说明在分钟前也就是:时系统进入反应阶段采用同样的分析方法可以知道反映阶段结束于:反应阶段时间区间为::共持续分钟。单击其中一个单元格“D”可以看出它是“BB”的平均值而单元格“E”则是“SQRT(SUMXMY(B:B,D:D))”它的意思是BBDD对应数据的差的平方的平均值再取平方根也就是数组的标准差。我们已经知道在Excel自带的数据库中已有线性拟合工具但是它还稍显单薄今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。在数据分析中对于成对成组数据的拟合是经常遇到的涉及到的任务有线性描述趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具但是它还稍显单薄今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。注:本功能需要使用Excel扩展功能如果您的Excel尚未安装数据分析请依次选择“工具”“加载宏”在安装光盘支持下加载“分析数据库”。加载成功后可以在“工具”下拉菜单中看到“数据分析”选项实例某溶液浓度正比对应于色谱仪器中的峰面积现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。已知组对应数据建立标准曲线并且对此曲线进行评价给出残差等分析数据。这是一个很典型的线性拟合问题手工计算就是采用最小二乘法求出拟合直线的待定参数同时可以得出R的值也就是相关系数的大小。在Excel中可以采用先绘图再添加趋势线的方法完成前两步的要求。选择成对的数据列将它们使用“X、Y散点图”制成散点图。在数据点上单击右键选择“添加趋势线”“线性”并在选项标签中要求给出公式和相关系数等可以得到拟合的直线。由图中可知拟合的直线是y=xR的值为。因为R>所以这是一个线性特征非常明显的实验模型即说明拟合直线能够以大于地解释、涵盖了实测数据具有很好的一般性可以作为标准工作曲线用于其他未知浓度溶液的测量。为了进一步使用更多的指标来描述这一个模型我们使用数据分析中的“回归”工具来详细分析这组数据。在选项卡中显然详细多了注意选择X、Y对应的数据列。“常数为零”就是指明该模型是严格的正比例模型本例确实是这样因为在浓度为零时相应峰面积肯定为零。先前得出的回归方程虽然拟合程度相当高但是在x=时仍然有对应的数值这显然是一个可笑的结论。所以我们选择“常数为零”。“回归”工具为我们提供了三张图分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。点这里查看更多Excel教程在线性拟合图中可以看到不但有根据要求生成的数据点而且还有经过拟和处理的预测数据点拟合直线的参数会在数据表格中详细显示。本实例旨在提供更多信息以起到抛砖引玉的作用由于涉及到过多的专业术语请各位读者根据实际在具体使用中另行参考各项参数此不再对更多细节作进一步解释。残差图是有关于世纪之与预测值之间差距的图表如果残差图中的散点在中州上下两侧零乱分布那么拟合直线就是合理的否则就需要重新处理。更多的信息在生成的表格中详细的参数项目完全可以满足回归分析的各项要求。下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。EXCEL的话刚看了一下是要先加载宏:在工具菜单下点加载宏选择分析工具库(在该复选框打钩点确定)。再点工具菜单发现多了“数据分析”这一项点这个自行选择分析工具就好了有方差分析、回归等等。。。还可以直接作图。用EXCEL函数进行多元线性回归分析示例计算柯布道格拉斯生产函数,CobbDouglas,:lnQ=lnAalnLblnK式中Q为产出~L和K分别表示劳动和资本投入量~A表示平均生产技术水平~a和b分别是Q相对于L和K的弹性。使用下表的统计数据~线性回归参数A,a,b。ABCD年份产出投入万人投入亿元回归值:#NA#NA#NA、定义名称:将A:A定义为Y~将B:D定义为X、在A中输入公式:=LINEST(LN(Y),LN(X),TRUE,TRUE)、将返回值以数组形式显示:选中A:C~按F~再按CtrlShiftEnter回归计算得lnY=lnLlnK三、返回值的其它操作如果只想要返回值中的指定项~则可以index函数。,、索引取值函数INDEX语法格式:INDEX,单元格区域或数组常量~行序号~列序号,,功能:使用索引从单元格区域或数组中选取值。可用该函数在LINEST函数返回系数序列数组表中根据所需数据所处的行列位置定位选取。,、四舍五入函数ROUND语法格式:ROUND,数字~小数位数,,功能:将数字四舍五入到指定的小数位数。由于LINEST函数的返回值为位小数~用此函数指定保留的小数位数。在需要的位置输入:=ROUND,INDEX,LINEST,YX~TRUE~TRUE,~~,~,。index的后两个参数是指向linest返回的数组的行列号。

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/21

用Excel做数据分析回归分析

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利