教你使用Excel做数据分析之回归分析方法

教你使用Excel做数据分析之回归分析方法在数据分析中，对于成对成组数据拟合是经常碰到，包括到任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见这类问题时往往寻求专业软件，比如在化工中经惯用到Origin和数学中常见MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带数据库中已经有线性拟合工具，不过它还稍显薄弱，今天我们来尝试使用较为专业拟合工具来对这类数据进行处理。注：本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。加载成功后，能够在“工具”下拉菜单中看到“数据分析”选项实例某溶液浓度正比对应于色谱仪器中峰面积，现欲建立不一样浓度下对应峰面积标准曲线以供测试未知样品实际浓度。已知8组对应数据，建立标准曲线，而且对此曲线进行评价，给出残差等分析数据。这是一个很经典线性拟合问题，手工计算就是采取最小二乘法求出拟合直线待定参数，同时能够得出R值，也就是相关系数大小。在Excel中，能够采取先绘图再添加趋势线方法完成前两步要求。选择成正确数据列，将它们使用“X、Y散点图”制成散点图。X、Y散点图在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，能够得到拟合直线。给出公式和相关系数由图中可知，拟合直线是y=15620x+6606.1，R2值为0.9994。因为R2>0.99，所以这是一个线性特征非常显著试验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具备很好通常性，能够作为标准工作曲线用于其余未知浓度溶液测量。为了深入使用更多指标来描述这一个模型，我们使用数据分析中“回归”工具来详细分析这组数据。回归工具在选项卡中显然详细多了，注意选择X、Y对应数据列。“常数为零”就是指明该模型是严格正百分比模型，本例确实是这么，因为在浓度为零时对应峰面积必定为零。先前得出回归方程即使拟合程度相当高，不过在x=0时，依然有对应数值，这显然是一个可笑结论。所以我们选择“常数为零”。“回归”工具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。残差图、线性拟合图和正态概率图在线性拟合图中能够看到，不但有依照要求生成数据点，而且还有经过拟和处理预测数据点，拟合直线参数会在数据表格中详细显示。本实例意在提供更多信息以起到抛砖引玉作用，因为包括到过多专业术语，请各位读者依照实际，在详细使用中另行参考各项参数，此不再对更多细节作深入解释。残差图是关于于世纪之与预测值之间差距图表，假如残差图中散点在中州上下两侧零乱分布，那么拟合直线就是合理，不然就需要重新处理。图表更多信息在生成表格中，详细参数项目完全能够满足回归分析各项要求。下列图提供是拟合直线得回归分析中方差、标准差等各项信息。表格在数据分析中，对于成对成组数据拟合是经常碰到，包括到任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见这类问题时往往寻求专业软件，比如在化工中经惯用到Origin和数学中常见MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带数据库中已经有线性拟合工具，不过它还稍显薄弱，今天我们来尝试使用较为专业拟合工具来对这类数据进行处理。点这里看专题：用Excel完成专业化数据统计、分析工作注：本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。加载成功后，能够在“工具”下拉菜单中看到“数据分析”选项实例某溶液浓度正比对应于色谱仪器中峰面积，现欲建立不一样浓度下对应峰面积标准曲线以供测试未知样品实际浓度。已知8组对应数据，建立标准曲线，而且对此曲线进行评价，给出残差等分析数据。这是一个很经典线性拟合问题，手工计算就是采取最小二乘法求出拟合直线待定参数，同时能够得出R值，也就是相关系数大小。在Excel中，能够采取先绘图再添加趋势线方法完成前两步要求。选择成正确数据列，将它们使用“X、Y散点图”制成散点图。在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，能够得到拟合直线。由图中可知，拟合直线是y=15620x+6606.1，R2值为0.9994。因为R2>0.99，所以这是一个线性特征非常显著试验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具备很好通常性，能够作为标准工作曲线用于其余未知浓度溶液测量。为了深入使用更多指标来描述这一个模型，我们使用数据分析中“回归”工具来详细分析这组数据。在选项卡中显然详细多了，注意选择X、Y对应数据列。“常数为零”就是指明该模型是严格正百分比模型，本例确实是这么，因为在浓度为零时对应峰面积必定为零。先前得出回归方程即使拟合程度相当高，不过在x=0时，依然有对应数值，这显然是一个可笑结论。所以我们选择“常数为零”。“回归”工具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。在线性拟合图中能够看到，不但有依照要求生成数据点，而且还有经过拟和处理预测数据点，拟合直线参数会在数据表格中详细显示。本实例意在提供更多信息以起到抛砖引玉作用，因为包括到过多专业术语，请各位读者依照实际，在详细使用中另行参考各项参数，此不再对更多细节作深入解释。残差图是关于于世纪之与预测值之间差距图表，假如残差图中散点在中州上下两侧零乱分布，那么拟合直线就是合理，不然就需要重新处理。更多信息在生成表格中，详细参数项目完全能够满足回归分析各项要求。下列图提供是拟合直线得回归分析中方差、标准差等各项信息。移动平均某化工反应过程，每隔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对应数据差平方平均值再取平方根，也就是数组标准差。抽样分析省教育厅派教授组进行某校检验学生考试试卷，教授组拟对总体进行抽样调查，对学校某班全体同学随机抽取25名作为调查样本。为了确保结果非人为性，采取Excel帮助教授组做出抽查结果。点这里看专题：用Excel完成专业化数据统计、分析工作抽样分析工具以数据源区域为总体，从而为其创建一个样本。当总体太大而不能进行处理或绘制时，能够选取具备代表性样本。假如确认数据源区域中数据是周期性，还能够对一个周期中特定时间段中数值进行采样。也能够采取随机抽样，满足用户确保抽样代表性要求。注：本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“数据分析库”。加载成功后，能够在工具下拉菜单中看到“数据分析”选项。操作步骤：1.打开原始数据表格，制作本实例原始数据无特殊要求，只要满足行或列中为同一属性数值即可。实例中显示是学生学号。2.选择“工具”—“数据分析”—“抽样”后，出现对话框，依次选择：输入区域：把原始总体数据放在此区域中，数据类型不限，数值型或者文本型均可；抽样方法：有间隔和随机两种。间隔抽样需要输入周期间隔，输入区域中位于间隔点处数值以及今后每一个间隔点处数值将被复制到输出列中。当抵达输入区域末尾时，抽样将停顿。(在本例题中没有采取)；随机抽样是指直接输入样本数，电脑自行进行抽样，不用受间隔规律限制；样本数：在此输入需要在输出列中显示需要抽取总体中数据个数。每个数值是从输入区域中随机位置上抽取出来，请注意：任何数值都能够被数次抽取!所以抽样所得数据实际上会有可能小于所需数量。本文末尾给出了一个处理方法；输出区域：在此输入对输出表左上角单元格引用。全部数据均将写在该单元格下方单列里。假如选择是“周期”，则输出表中数值个数等于输入区域中数值个数除以“间隔”。假如选择是“随机”，则输出表中数值个数等于“样本数”；3.然后单击确定就能够显示结果了(这是电脑自行随机抽样结果)。原始数据文档在这里下载>>操作结果文档在这里下载>>需要说明情况：因为随机抽样时总体中每个数据都能够被数次抽取，所以在样本中数据通常都会有重复现象，处理此问题有待于程序完善。能够使用“筛选”功效对所得数据进行筛选。选中样本数据列，依次执行“数据”-“筛选”-“高级筛选”，以下列图所表示。最终样本结果以下列图所表示，请您依照经验适当调整在数据样本选取时数量设置，以使最终所得样本数量不少于所需数量。如有问题能够发信至：与我交流。相关系数与协方差化学合成试验中经常需要考查压力随温度改变情况。某次试验在两个不一样反应器中进行同一条件下试验得到两组温度与压力相关数据，试分析它们与温度关联关系，并对在不一样反应器内进行同一条件下反应可靠性给出依据。点这里看专题：用Excel完成专业化数据统计、分析工作相关系数是描述两个测量值变量之间离散程度指标。用于判断两个测量值变量改变是否相关，即，一个变量较大值是否与另一个变量较大值相关联(正相关)；或者一个变量较小值是否与另一个变量较大值相关联(负相关)；还是两个变量中值互不关联(相关系数近似于零)。设(X,Y)为二元随机变量，那么:为随机变量X与Y相关系数。p是度量随机变量X与Y之间线性相关亲密程度数字特征。注:本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，能够在“工具”下拉菜单中看到“数据分析”选项。操作步骤1.打开原始数据表格，制作本实例原始数据需要满足两组或两组以上数据，结果将给出其中任意两项相关系数。2.选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择:输入区域:选择数据区域，注意需要满足最少两组数据。假如有数据标志，注意同时勾选下方“标志位于第一行”；分组方式:指示输入区域中数据是按行还是按列考虑，请依照原数据格式选择；输出区域能够选择本表、新工作表组或是新工作簿；3.点击“确定”即可看到生成报表。能够看到，在对应区域生成了一个3×3矩阵，数据项目标交叉处就是其相关系数。显然，数据与本身是完全相关，相关系数在对角线上显示为1；两组数据间在矩阵上有两个位置，它们是相同，故右上侧重复部分不显示数据。左下侧对应位置分别是温度与压力A、B和两组压力数据间相关系数。从数据统计结论能够看出，温度与压力A、B相关性分别达成了0.95和0.94，这说明它们展现良好正相关性，而两组压力数据间相关性达成了0.998，这说明在不一样反应器内相同条件下反应一致性很好，能够忽略因为更换反应器造成系统误差。协方差统计与相关系数活方法相同，统计结果一样返回一个输出表和一个矩阵，分别表示每对测量值变量之间相关系数和协方差。不一样之处于于相关系数取值在-1和+1之间，而协方差没有限定取值范围。相关系数和协方差都是描述两个变量离散程度指标。排位与百分比排位某班级期中考试进行后，按照要求仅公布成绩，但学生及家长要求知道排名。故欲公布成绩排名，学生能够经过成绩查询到自己排名，并同时得到该成绩位于班级百分比排名(即该同学是排名位于前“X%”学生)。点这里看专题：用Excel完成专业化数据统计、分析工作排序操作是Excel基本操作，Excel“数据分析”中“排位与百分比排位”能够使这个工作简化，直接输出报表。注:本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，能够在“工具”下拉菜单中看到“数据分析”选项。操作步骤1.打开原始数据表格，制作本实例原始数据无特殊要求，只要满足行或列中为同一属性数值即可。2.选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择；输入区域:选择数据区域，假如有数据标志，注意同时勾选下方“标志位于第一行”；分组方式:指示输入区域中数据是按行还是按列考虑，请依照原数据格式选择；输出区域能够选择本表、新工作表组或是新工作簿。3.点击“确定”即可看到生成报表。能够看到，此时生成一个四列新表格，其中“点”是指排序后原数据序数，在本实例中对应与学号，这也是很实用一个序列；“成绩”即为排序后数据系列；“排位”采取重复数据占用同一位置统计方法；“百分比”是按照降序排列，为了得到真正“百分比排位”，还需要稍微作一下调整。4.在“百分比”列下一列输入“百分排名”，在第一个单元格中输入公式“=1-G3(对应于‘百分排名’)”，回车。选中该单元格，向下拖动直至填充完成。这么就达成了显示百分比排名目标。完成报表实例以下列图所表示。描述统计某班级期中考试进行后，需要统计成绩平均值、区间，以及给出班级内部学生成绩差异量化标准，借此来作为处理班与班之间学生成绩参差不齐依据。要求得到标准差等统计数值。点这里看专题：用Excel完成专业化数据统计、分析工作样本数据分布区间、标准差等都是描述样本数据范围及波动大小统计量，统计标准差需要得到样本均值，计算较为繁琐。这些都是描述样本数据惯用变量，使用Excel数据分析中“描述统计”即可一次完成。注：本功效需要使用Excel扩展功效，假如您Excel还未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，能够在“工具”下拉菜单中看到“数据分析”选项。操作步骤1.打开原始数据表格，制作本实例原始数据无特殊要求，只要满足行或列中为同一属性数值即可。2.选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择：输入区域：原始数据区域，能够选中多个行或列，注意选择对应分组方式；假如数据有标志，注意勾选“标志位于第一行”；假如输入区域没有标志项，该复选框将被去除，Excel将在输出表中生成适宜数据标志；输出区域能够选择本表、新工作表或是新工作簿；汇总统计：包含有平均值、标准误差(相对于平均值)、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大值、最小值和置信度等相关项目。其中：中值：排序后位于中间数据值；众数：出现次数最多值；峰值：衡量数据分布起伏改变指标，以正态分布为基准，比其平缓时值为正，反之则为负；偏斜度：衡量数据峰值偏移指数，依照峰值在均值左侧或者右侧分别为正值或负值；极差：最大值与最小值差。第K大(小)值：输出表某一行中包含每个数据区域中第k个最大(小)值。平均数置信度：数值95%可用来计算在显著性水平为5%时平均值置信度。结果示例以下(本实例演示了双列数据描述统计结果)：成绩学习时间平均78.64285714平均62.91428571标准误差2.标准误差1.中位数85中位数68众数98众数78.4标准差18.02163202标准差14.41730562方差324.7792208方差207.8587013峰度1.峰度1.偏度-1.偏度-1.13055151区域85区域68最小值15最小值12最大值100最大值80求和4404求和3523.2观察数56观察数56最大(1)100最大(1)80最小(1)15最小(1)12置信度(95.0%)4.置信度(95.0%)3.直方图使用Excel自带数据分析功效能够完成很多专业软件才有数据统计、分析，这其中包含：直方图、相关系数、协方差、各种概率分布、抽样与动态模拟